Social Engine Slow ? Mysql resources high ?

[Jay]

I responded to a similar topic but figured it better to start my own. In doing so we can dedicate this topic to debugging the problem.

Our company has been testing Social Engine in a Pre-Beta environment for about 3 weeks. (testing with 500 concurrent users)

I understand now what a lot of you are talking and complaining about. I would like to point out that you are not crazy! There is surely a problem with SocialEngine's relationship with the database. I am so tired of hearing responses that include "It's your hardware" or "Its this plugin" or "Reinstall it, you must have screwed it up"
Now to be fair, I have only used version 3.17 so my findings will be specifically pointed to this version.

To properly test we have decided to load 300,000+ accounts and push through the required optimizations. As I write this we are loading 2 accounts per second via our own custom import script and Mysql's 15 minute load is average 86%.

This import has finished after 5 long days so I will share some info and we will debug this together. Sound fair?


Our first 300,000 members have almost finished uploading. Mysql has stayed pretty constant at 85+% the whole time.

My first order of business will be to look at the se_users table and see if there is indexes.

Code: Select all

SHOW INDEXES FROM se_users;

We can see there is infact Indexes on the se_users table. I had assumed there would be none since the site response time was so slow and mysql usage was so high.
We have established there is in fact Indexes so now let us see how well they work. We can do this by using the EXPLAIN command in front of your query.

Code: Select all

EXPLAIN SELECT user_username FROM se_users WHERE LOWER(user_username)='debraone' LIMIT 1

We want to pay attention to the amount of rows Mysql had to search to get its answer. As you can see it had to search through EVERY row all 305,000 of them so that is surely depressing. This should report 1 row.
This query shows that although there are lower indexes for this table they are not being used.
Anyone like to hazard a guess as to why? I have an idea, so I will explore it, has something to do with the way those fields are stored in the database. I am guessing they are not being stored in lower format.

I am also going to place a ticket into socialengine.net and see what they would like to say about it. I will post there responses here as well.

[vessell]

Let me be the first to say, awesome Jay! Please keep us posted!

[Jay]

Just to point out, the query I used was an actual query from SocialEngine to the MySql Database.

We noticed that it searched almost 305,000 rows to get the answer lets see what happens if we remove the LOWER from the search.

EXPLAIN SELECT user_username FROM se_users WHERE (user_username)='debraone' LIMIT 1

WOW it appears we have actually got the indexes to work properly with this query by simply removing the LOWER from the query.

Now I am no professional coder, but I would bet that is gonna speed things up ALOT!!!!!

Feel free to post any thoughts on this, I would love to hear from some people that actually know how the SE code is supposed to work and explain this.

[Godrifle]

Same issues back in 2006 with Drupal: http://drupal.org/node/83738, and lower() definitely will cause an index to not be utilized. That's a *great* catch.

[vessell]

Jay, can you explain how you remove the LOWER from the database to those non-programmers like so many of us here?

Thanks

[Jay]

Sadly before I tell you to remove LOWER we have to make sure that will not cause problems anywhere else in the coding. This may be a way to speed the site up A LOT, but not sure of any side affects it may cause.

Anyone know enough about the SE coding/framework to speculate?

[Jay]

Just so we know the actual code we are talking about, here is the file where LOWER is used.
\include\class_user.php

Code: Select all

   
Line 118:        // SET USERNAME AND EMAIL TO LOWERCASE
Line 128:          $sql_array[] = "SELECT {$select_fields[0]} FROM se_users WHERE LOWER(user_username)='{$user_username}' LIMIT 1";

Line 131:          $sql_array[] = "SELECT {$select_fields[0]} FROM se_users WHERE LOWER(user_email)='{$user_email}' LIMIT 1";

Line 873:       $username_query = $database->database_query("SELECT user_username FROM se_users WHERE LOWER(user_username)='{$lowercase_username}' LIMIT 1");

Line 882:       $email_query = $database->database_query("SELECT user_email FROM se_users WHERE LOWER(user_email)='{$lowercase_email}' LIMIT 1"); 


As well as include\class_admin.php

Code: Select all

Line 406:      if( strtolower($this->admin_info['admin_username']) != $lowercase_username && $database->database_num_rows($database->database_query("SELECT admin_id FROM se_admins WHERE LOWER(admin_username)='{$lowercase_username}'")) )

Now removing this MAY speed your site up, but remember this is just an exploration of the problem. So if you want to test this PLEASE backup the files and you DB first.

[vessell]

Hmmm, ok, I think I better let you test things out first to make sure there aren't side effects before I jump in. But this does look promising though. Keep up the great work!

[Jay]

Keep in mind there is ALOT going on with regards to lowering. So lets not act in haste yet.
http://php.net/manual/en/function.strtolower.php is the link to the strtolower function if you would like to read a little about it.

Basically this function is supposed to strip all the uppercase from data being entered so the data can be stored in lowercase in the database and be properly indexed. If this is the case then "LOWER INDEXES" can be used. This of course is NOT happening.

I will try and explain better

Lets say for an example that you made an account with the email AAA@AAA.COM If you enter that into the database then it will not be indexed properly as LOWER. The strtolower function will turn AAA@AAA.COM into aaa@aaa.com Make sense?

I made an email like that to test and make sure the strtolower function is working and sure enough it is not. The database shows the email as AAA@AAA.COM.

Before I confuse myself...

Here is all the places in the code where the strtolower function is used. I would imagine that since it is included all over the code there is a good reason for it being there and the best course of action would be to find out why it isnt working.


\files\admin\admin_templates.php (4 hits)

Code: Select all

   Line 19: if(strpos(strtolower($filename), ".tpl") === FALSE && strpos(strtolower($filename), ".css") === FALSE) {
   Line 19: if(strpos(strtolower($filename), ".tpl") === FALSE && strpos(strtolower($filename), ".css") === FALSE) {
   Line 75:     if($file != "." && $file != ".." && strpos(strtolower($file), "admin_") === FALSE) {
   Line 77:       if(strpos(strtolower($file), "user_") !== FALSE) {


\files\admin\admin_viewadmins.php (2 hits)

Code: Select all

   Line 24:   $admin_username = strtolower($_POST['admin_username']);
   Line 57:   $admin_username = strtolower($_POST['admin_username']);


\files\include\cache\cache.php (1 hits)

Code: Select all

   Line 75:     $type = strtolower(preg_replace('/[^A-Z0-9_\.-]/i', '', $type));

\files\include\cache\storage.php (1 hits)

Code: Select all

   Line 47:       $handler = strtolower(preg_replace('/[^A-Z0-9_\.-]/i', '', $handler));

\files\include\class_admin.php (5 hits)

Code: Select all

   
Line 405:      $lowercase_username = strtolower($admin_username);
Line 405:      $lowercase_username = strtolower($admin_username);
Line 406:      if( strtolower($this->admin_info['admin_username']) != $lowercase_username && $database->database_num_rows($database->database_query("SELECT admin_id FROM se_admins WHERE LOWER(admin_username)='{$lowercase_username}'")) )


\files\include\class_inputfilter.php (14 hits)

Code: Select all

   Line 46:     // make sure user defined arrays are in lowercase
   Line 47:     for ($i = 0; $i < count($tagsArray); $i++) $tagsArray[$i] = strtolower($tagsArray[$i]);
   Line 48:     for ($i = 0; $i < count($attrArray); $i++) $attrArray[$i] = strtolower($attrArray[$i]);
   Line 151:       if ((!preg_match("/^[a-z][a-z0-9]*$/i",$tagName)) || (!$tagName) || ((in_array(strtolower($tagName), $this->tagBlacklist)) && ($this->xssAuto))) {             
   Line 184:       $tagFound = in_array(strtolower($tagName), $this->tagsArray);         
   Line 225:       if ((!eregi("^[a-z]*$",$attrSubSet[0])) || (($this->xssAuto) && ((in_array(strtolower($attrSubSet[0]), $this->attrBlacklist)) || (substr($attrSubSet[0], 0, 2) == 'on'))))
   Line 243:       if (   ((strpos(strtolower($attrSubSet[1]), 'expression') !== false) &&   (strtolower($attrSubSet[0]) == 'style')) ||
   Line 244:           (strpos(strtolower($attrSubSet[1]), 'javascript:') !== false) ||
   Line 245:           (strpos(strtolower($attrSubSet[1]), 'behaviour:') !== false) ||
   Line 246:           (strpos(strtolower($attrSubSet[1]), 'vbscript:') !== false) ||
   Line 247:           (strpos(strtolower($attrSubSet[1]), 'mocha:') !== false) ||
   Line 248:           (strpos(strtolower($attrSubSet[1]), 'livescript:') !== false)
   Line 252:       $attrFound = in_array(strtolower($attrSubSet[0]), $this->attrArray);


\files\include\class_upload.php (2 hits)

Code: Select all

   Line 54:      $this->file_type = strtolower($_FILES[$file]['type']);
   Line 58:      $this->file_ext = strtolower(str_replace(".", "", strrchr($this->file_name, ".")));


\files\include\class_user.php (22 hits)

Code: Select all

   Line 119:        $user_username = strtolower($user_unique_username);
   Line 120:        $user_email = strtolower($user_unique_email);
   Line 847:      $banned_usernames = explode(",", strtolower($setting['setting_banned_usernames']));
   Line 848:      if( in_array(strtolower($username), $banned_usernames) && trim($username) && $setting['setting_username'] )
   Line 856:      $banned_emails = explode(",", strtolower($setting['setting_banned_emails']));
   Line 857:      $wildcard_ban = "*".strstr(strtolower($email), "@");
   Line 859:     if( trim($email) && in_array(strtolower($email), $banned_emails) )
   Line 862:     if( trim($email) && in_array(strtolower($wildcard_ban), $banned_emails) )
   Line 870:      $lowercase_username = strtolower($username);
   Line 871:     if( $setting['setting_username'] && strtolower($this->user_info['user_username']) != $lowercase_username )
   Line 873:       $username_query = $database->database_query("SELECT user_username FROM se_users WHERE LOWER(user_username)='{$lowercase_username}' LIMIT 1");
   Line 879:      $lowercase_email = strtolower($email);
   Line 880:     if( strtolower($this->user_info['user_email']) != $lowercase_email )
   Line 882:       $email_query = $database->database_query("SELECT user_email FROM se_users WHERE LOWER(user_email)='{$lowercase_email}' LIMIT 1"); 
   Line 1216:      $file_exts = explode(",", str_replace(" ", "", strtolower($this->level_info['level_photo_exts'])));
   Line 1217:      $file_types = explode(",", str_replace(" ", "", strtolower("image/jpeg, image/jpg, image/jpe, image/pjpeg, image/pjpg, image/x-jpeg, x-jpg, image/gif, image/x-gif, image/png, image/x-png")));
   Line 2370:         if( strtolower($to_username)==strtolower($this->user_info['user_username']) ) continue;


\files\include\fckeditor\editor\filemanager\connectors\php\commands.php (1 hits)

Code: Select all

   Line 183:       $sExtension = strtolower( $sExtension ) ;

\files\include\fckeditor\editor\filemanager\connectors\php\util.php (2 hits)

Code: Select all

   Line 97:       $lcaseHtmlExtensions[$key] = strtolower( $val ) ;
   Line 124:    $chunk = strtolower( $chunk ) ;


\files\include\fckeditor\editor\filemanager\connectors\py\fckcommands.py (1 hits)

Code: Select all

   Line 139:          newFileExtension = getExtension(newFileName).lower()

\files\include\fckeditor\editor\filemanager\connectors\py\zope.py (1 hits)

Code: Select all

   Line 137:       fileExtension = self.getExtension(fileName).lower()

\files\include\fckeditor\editor\_source\classes\fckstyle.js (4 hits)

Code: Select all

   
        Line 902:          // Merge the upper <PRE> block's content into the lower <PRE> block.
   Line 1289:     * style with attributes in an element. All information in it is lowercased.
   Line 1326:     * it is lowercased.
   Line 1391:     * added by a "_length" property. All values are lowercased.


\files\include\fckeditor\editor\_source\internals\fckregexlib.js (1 hits)

Code: Select all

   Line 54: // Validate element names (it must be in lowercase).

\files\include\functions_stats.php (2 hits)

Code: Select all

   Line 61:    if(strpos(strtolower($referring_url), strtolower($_SERVER["HTTP_HOST"])) !== FALSE) { return; }


\files\include\jsonrpc\jsonrpc.inc (2 hits)

Code: Select all

   Line 238:    * @todo we should move to xmlrpc_defencoding and xmlrpc_internalencoding as predefined values, but it would make this even slower...
   Line 650:             $data = strtolower($data);


\files\include\jsonrpc\server.php (1 hits)

Code: Select all

   Line 403:                $key = ucfirst(str_replace('_', '-', strtolower(substr($key, 5))));

\files\include\language\language.php (2 hits)

Code: Select all

   Line 337:     if( !empty($info_key) && strtolower($info_key)=="charset" ) return $instance->_charset;
   Line 340:     if( !empty($info_key) && strtolower($info_key)=="indices" ) return $instance->_indices;

\files\include\language\storage\file.php (1 hits)

Code: Select all

   Line 310:           $key = strtolower($split[0]);

\files\include\language\storage.php (1 hits)

Code: Select all

   Line 35:       $type = strtolower(preg_replace('/[^A-Z0-9_\.-]/i', '', $type));

\files\include\sanity\helpers.php (1 hits)

Code: Select all

   Line 29:     if( strpos(strtolower($wserv), 'apache')===FALSE )

\files\include\sanity\sanity.php (1 hits)

Code: Select all

   Line 46:     if( !isset($options['lang_title']) ) $options['lang_title'] = ucfirst(strtolower($name));

\files\include\sanity\test\configuration.php (3 hits)

Code: Select all

   Line 21:     $this->name = 'configuration_'.strtolower(preg_replace('/[^A-Z0-9_\.-]/i', '', $this->directive));
   Line 93:       // When checking for less than, take the lower value
   Line 136:     $last = strtolower($val[strlen($val)-1]);


\files\include\sanity\test\extension.php (1 hits)

Code: Select all

   Line 21:     $this->name = 'extension_'.strtolower(preg_replace('/[^A-Z0-9_\.-]/i', '', $this->extension));

\files\include\sanity\test.php (1 hits)

Code: Select all

   Line 45:     $type = strtolower(preg_replace('/[^A-Z0-9_\.-]/i', '', $type));

\files\include\session\session.php (1 hits)

Code: Select all

   Line 190:          $name = strtolower(substr($file, 0, strrpos($file, '.')));

\files\include\session\storage.php (1 hits)

Code: Select all

   Line 25:       $type = strtolower(preg_replace('/[^A-Z0-9_\.-]/i', '', $type));

\files\include\smarty\internals\core.assign_smarty_interface.php (1 hits)

Code: Select all

   Line 31:         foreach (preg_split('!!', strtolower($smarty->request_vars_order)) as $_c) {

\files\include\smarty\plugins\function.html_select_time.php (1 hits)

Code: Select all

   Line 183:     'selected'      => strtolower(strftime('%p', $time)),

\files\include\smarty\plugins\modifier.lower.php (7 hits)

Code: Select all

   Line 10:  * Smarty lower modifier plugin
   Line 13:  * Name:     lower<br>
   Line 14:  * Purpose:  convert string to lowercase
   Line 15:  * @link http://smarty.php.net/manual/en/language.modifier.lower.php
   Line 16:  *          lower (Smarty online manual)
   Line 21: function smarty_modifier_lower($string)
   Line 23:     return strtolower($string);


\files\include\smarty\Smarty_Compiler.class.php (1 hits)

Code: Select all

   Line 1278:             switch (strtolower($token)) {

\files\include\xmlrpc\compat\is_a.php (1 hits)

Code: Select all

   Line 39:         if (get_class($object) == strtolower($class)) {

\files\include\xmlrpc\compat\is_callable.php (1 hits)

Code: Select all

   Line 41:                     return (bool)(is_array($methods) && in_array(strtolower($method), $methods));

\files\include\xmlrpc\xmlrpc.inc (8 hits)

Code: Select all

   Line 399:                $GLOBALS['_xh']['rt'] = strtolower($name);
   Line 596:                $GLOBALS['_xh']['vt']=strtolower($name);
   Line 697:                $GLOBALS['_xh']['vt']=strtolower($name);
   Line 2279:                   $header_name = strtolower(trim($arr[0]));
   Line 2515:          // makes the lib about 200% slower...
   Line 2647:       * @param string $type any valid xmlrpc type name (lowercase). If null, 'string' is assumed
   Line 2936:          // add check? slower, but helps to avoid recursion in serializing broken xmlrpcvals...
   Line 2948:          // add check? slower, but helps to avoid recursion in serializing broken xmlrpcvals...


\files\include\xmlrpc\xmlrpcs.inc (1 hits)

Code: Select all

   Line 904:             // makes the lib about 200% slower...

\files\include\xmlrpc\xmlrpc_wrappers.inc (6 hits)

Code: Select all

   Line 29:       switch(strtolower($phptype))
   Line 47:             return strtolower($phptype);
   Line 70:       switch(strtolower($xmlrpctype))
   Line 90:             return strtolower($xmlrpctype);
   Line 275:             if (isset($paramDocs[$i]['name']) && $paramDocs[$i]['name'] && strtolower($paramDocs[$i]['name']) != strtolower($param['name']))
   Line 275:             if (isset($paramDocs[$i]['name']) && $paramDocs[$i]['name'] && strtolower($paramDocs[$i]['name']) != strtolower($param['name']))


\files\misc_js.php (6 hits)

Code: Select all

   Line 64:   $input = strtolower( $_GET['input'] );
   Line 109:   $input = strtolower( $_GET['input'] );
   Line 154:   $input = strtolower( $_GET['input'] );
   Line 183:   $input = strtolower( $_GET['input'] );
   Line 204:   $input = strtolower( $_GET['input'] );
   Line 213:     if( strtolower(substr($option_value, 0, $len)) == $input )


[vessell]

The strtolower function will turn AAA@AAA.COM into aaa@aaa.com Make sense?

Yes, that makes perfect sense. Thanks for the explanation.