Bug Query

[hornstar]

I have been getting told my site is really slow lately. So I asked Floren over at Axivo for some assistance.

This is the reply: http://www.axivo.com/community/threads/inquiry-and-assistance.116/#post-529

It appears that query is a dbtech thanks query.

What do you suggest?

 

[Fillip H.]

It's not really a bug, but I'll see if I can tweak that query in the future.

The first query isn't related to any DBTech mod, though, I don't think...

Could you please attach the file here as well?

 

[hornstar]

attached. Thanks.

 

[Fillip H.]

Alrighty, here's what I suggest you do:

Download all your forum files, then do a Ctrl+F in all files for the following strings:

SET timestamp

WHERE email LIKE

SELECT userid, username, email

In that last string, check for the following in any result you find:

WHERE email =

Finally, I suggest indexing the lastpost column in the thread table. The query mentioning lastpost was an InfoPanels query

Finding the source of the above queries + future optimisations to the APTL queries should solve all the slow queries


Out of curiosity, how did you enable such a pretty and informative slow query log? We're hosting another forum on this server, a fairly large one, and having this level of logging would be most helpful. Thus far I've been unsuccessful in following the instructions posted in Google

 

[hornstar]

How do I index the lastpost column in the thread table?

I will try and find the file which has that string now.

Also I installed this http://hackmysql.com/mysqlsla

However I might give this ago instead: http://code.google.com/p/maatkit/

 

[Fillip H.]

Open phpmyadmin, browse to the table, click the Index button (hover over the buttons to see what they do)

Cheers for the links, I'll check em out

 

[hornstar]

Open phpmyadmin, browse to the table, click the Index button (hover over the buttons to see what they do)

Cheers for the links, I'll check em out

An index has been added on lastpost ( Query took 6.4731 sec )
down the bottom:
The indexes lastpost and lastpost_3 seem to be equal and one of them could possibly be removed. Is this a problem?

Also just curious, what will this fix

 

[Fillip H.]

Oh there was already an index on lastpost? You can remove the _3 one then.

It was supposed to fix the InfoPanels slow query, but if there was already an index then I'm at a loss for that particular one. It's possible that the query was just slow because of other queries locking up (like the others mentioned in the log).

Any luck finding the source of the others?

 

[hornstar]

Oh there was already an index on lastpost? You can remove the _3 one then.

It was supposed to fix the InfoPanels slow query, but if there was already an index then I'm at a loss for that particular one. It's possible that the query was just slow because of other queries locking up (like the others mentioned in the log).

Any luck finding the source of the others?

nah not yet, but was wondering if there would be a quicker way? Maybe something I can type into SSH perhaps?
Edit: I am using grep -r -H “SET timestamp” * (I'll report back and see if that works ^^ )

Edit2:

root@server [/home/***/public_html/forums]# grep -r -H "SET timestamp" *
gallery/uploadphoto.php:                        ppmysql_query("UPDATE {$Globals[                                                                                                                               'pp_db_prefix']}cache SET timestamp=0 WHERE id=1", $link);
gallery/editphoto.php:            ppmysql_query("UPDATE {$Globals['pp_db_prefix'                                                                                                                               ]}cache SET timestamp=0 WHERE id=1", $link);
gallery/adm-photo.php:            ppmysql_query("UPDATE {$Globals['pp_db_prefix']}cache SET timestamp=0 WHERE id=1", $link);
gallery/adm-photo.php:                ppmysql_query("UPDATE {$Globals['pp_db_prefix']}cache SET timestamp=0 WHERE id=1", $link);
gallery/bulkupload.php:        ppmysql_query("UPDATE {$Globals['pp_db_prefix']}cache SET timestamp=0 WHERE id=1", $link);
gallery/bulkupload.php:        ppmysql_query("UPDATE {$Globals['pp_db_prefix']}cache SET timestamp=0 WHERE id=1", $link);
gallery/bulkupload.php:        ppmysql_query("UPDATE {$Globals['pp_db_prefix']}cache SET timestamp=0 WHERE id=1", $link);
gallery/bulkupload.php:            ppmysql_query("UPDATE {$Globals['pp_db_prefix']}cache SET timestamp=0 WHERE id=1", $link);
gallery/bulkupload.php:            ppmysql_query("UPDATE {$Globals['pp_db_prefix']}cache SET timestamp=0 WHERE id=1", $link);
gallery/bulkupload.php:    ppmysql_query("UPDATE {$Globals['pp_db_prefix']}cache SET timestamp=0 WHERE id=1", $link);
gallery/pp-inc.php:            ppmysql_query("UPDATE {$Globals['pp_db_prefix']}cache SET timestamp=$nextupdate,$localupd='$slashcache' WHERE id=1", $link);
gallery/adm-misc.php:        ppmysql_query("UPDATE {$Globals['pp_db_prefix']}cache SET timestamp=0 WHERE id=1", $link);
gallery/index.php:        ppmysql_query("UPDATE {$Globals['pp_db_prefix']}cache SET timestamp=$nextupdate,statscache='$slashcache' WHERE id=1", $link);
root@server [/home/***/public_html/forums]#

Looks like PhotoPost.

grep -r -H “WHERE email LIKE” * Found nothing.

and

root@server [/home/***/public_html/forums]# grep -r -H "SELECT userid, username, email" *
includes/cron/gameroom_nightly.php:             SELECT userid, username, email, lastvisit
includes/functions_login.php:                   if ($user = $vbulletin->db->query_first("SELECT userid, username, email, languageid FROM " . TABLE_PREFIX . "user WHERE username = '" . $vbulletin->db->escape_string($username) . "' AND usergroupid <> 3"))
includes/class_dm_user.php:                             SELECT userid, username, email
login.php:              SELECT userid, username, email, languageid
admincp/user.php:                SELECT userid, username, email, ipaddress
root@server [/home/***/public_html/forums]#

root@server [/home/***/public_html/forums]# grep -r -H "WHERE email =" *
includes/class_dm_user.php:                             WHERE email = '" . $this->dbobject->escape_string($email) . "'
login.php:              WHERE email = '" . $db->escape_string($vbulletin->GPC['email']) . "'
register.php:           WHERE email = '" . $db->escape_string($vbulletin->GPC['email']) . "'"
root@server [/home/***/public_html/forums]#

 

[Fillip H.]

I'm not familiar enough with SSH commands to answer that unfortunately

 

[hornstar]

I think you ninjaed me to my edit lol. look above

 

[Fillip H.]

Yeah I did notice that in vBulletin 4.1.3 (which also features a vB3 to vB4 skin converter, hint ) indexes the email column in the user table now

 

[hornstar]

Yeah I am going to index the email column on my 3.8 board (may have to turn it off first tho lol).