XF 1.2 Site being hammered by queries.

[ineedhelp]

My site has been running slow for somedays and my host has said the queries below is causing the slowness. Can someone tell me how to fix this asap and what could be causing it.

The server is slow as MySQL is being absolutely hammered by queries, please see those queries below:

[root@barfilounge-com ~]# mysqladmin -u admin -p`cat /etc/psa/.psa.shadow` processlist
+-------+-------------+-----------+------------------+----------------+------+--------------------+------------------------------------------------------------------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+-------+-------------+-----------+------------------+----------------+------+--------------------+------------------------------------------------------------------------------------------------------+
| 86449 | DELAYED | localhost | ubar_xxxxxx_0001 | Delayed insert | 20 | Waiting for INSERT | |
| 86701 | barfilounge | localhost | ubar_xxxxxx_0001 | Execute | 6 | Sorting result | SELECT n.*, t.*, p.* FROM xf_post p LEFT JOIN xf_thread t ON t.thread_id = p.thread_id LEFT JOIN xf_ |
| 86726 | barfilounge | localhost | ubar_xxxxxx_0001 | Execute | 7 | Sorting result | SELECT n.*, t.*, p.* FROM xf_post p LEFT JOIN xf_thread t ON t.thread_id = p.thread_id LEFT JOIN xf_ |
| 86740 | DELAYED | localhost | ubar_xxxxxx_0001 | Delayed insert | 72 | Waiting for INSERT | |
| 86765 | barfilounge | localhost | ubar_xxxxxx_0001 | Execute | 3 | Sorting result | SELECT n.*, t.*, p.* FROM xf_post p LEFT JOIN xf_thread t ON t.thread_id = p.thread_id LEFT JOIN xf_ |
| 86793 | barfilounge | localhost | ubar_xxxxxx_0001 | Execute | 7 | Sorting result | SELECT n.*, t.*, p.* FROM xf_post p LEFT JOIN xf_thread t ON t.thread_id = p.thread_id LEFT JOIN xf_ |
| 86820 | barfilounge | localhost | ubar_xxxxxx_0001 | Execute | 4 | Sorting result | SELECT n.*, t.*, p.* FROM xf_post p LEFT JOIN xf_thread t ON t.thread_id = p.thread_id LEFT JOIN xf_ |
| 86853 | barfilounge | localhost | ubar_xxxxxx_0001 | Execute | 7 | Sorting result | SELECT n.*, t.*, p.* FROM xf_post p LEFT JOIN xf_thread t ON t.thread_id = p.thread_id LEFT JOIN xf_ |
| 86882 | barfilounge | localhost | ubar_xxxxxx_0001 | Execute | 7 | Sorting result | SELECT n.*, t.*, p.* FROM xf_post p LEFT JOIN xf_thread t ON t.thread_id = p.thread_id LEFT JOIN xf_ |
| 86885 | admin | localhost | | Query | 0 | | show processlist |
+-------+-------------+-----------+------------------+----------------+------+--------------------+------------------------------------------------------------------------------------------------------+

Thanks.

 

[Paul B]

Disable all add-ons and test again.

 

[Mike]

Unfortunately they didn't run the "full" processlist so I can't confirm, but those queries don't look like standard ones.

 

[ineedhelp]

Unfortunately they didn't run the "full" processlist so I can't confirm, but those queries don't look like standard ones.

The host has now sent me this.... What do you make of it?

Here is that output:

[root@barfilounge-com ~]# mysqladmin -u admin -p processlist
Enter password:
+-------+-------------+-----------+------------------+----------------+------+--------------------+------------------------------------------------------------------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+-------+-------------+-----------+------------------+----------------+------+--------------------+------------------------------------------------------------------------------------------------------+
| 94914 | DELAYED | localhost | ubar_xxxxxx_0001 | Delayed insert | 20 | Waiting for INSERT | |
| 95085 | barfilounge | localhost | ubar_xxxxxx_0001 | Execute | 9 | Sorting result | SELECT n.*, t.*, p.* FROM xf_post p LEFT JOIN xf_thread t ON t.thread_id = p.thread_id LEFT JOIN xf_ |
| 95127 | barfilounge | localhost | ubar_xxxxxx_0001 | Execute | 3 | Sorting result | SELECT n.*, t.*, p.* FROM xf_post p LEFT JOIN xf_thread t ON t.thread_id = p.thread_id LEFT JOIN xf_ |
| 95203 | barfilounge | localhost | ubar_xxxxxx_0001 | Execute | 3 | Sorting result | SELECT n.*, t.*, p.* FROM xf_post p LEFT JOIN xf_thread t ON t.thread_id = p.thread_id LEFT JOIN xf_ |
| 95383 | barfilounge | localhost | ubar_xxxxxx_0001 | Execute | 3 | Sorting result | SELECT n.*, t.*, p.* FROM xf_post p LEFT JOIN xf_thread t ON t.thread_id = p.thread_id LEFT JOIN xf_ |
| 95420 | barfilounge | localhost | ubar_xxxxxx_0001 | Execute | 9 | Sorting result | SELECT n.*, t.*, p.* FROM xf_post p LEFT JOIN xf_thread t ON t.thread_id = p.thread_id LEFT JOIN xf_ |
| 95460 | DELAYED | localhost | ubar_xxxxxx_0001 | Delayed insert | 8 | Waiting for INSERT | |
| 95461 | barfilounge | localhost | ubar_xxxxxx_0001 | Execute | 3 | Sorting result | SELECT n.*, t.*, p.* FROM xf_post p LEFT JOIN xf_thread t ON t.thread_id = p.thread_id LEFT JOIN xf_ |
| 95494 | barfilounge | localhost | ubar_xxxxxx_0001 | Execute | 9 | Sorting result | SELECT n.*, t.*, p.* FROM xf_post p LEFT JOIN xf_thread t ON t.thread_id = p.thread_id LEFT JOIN xf_ |
| 95521 | barfilounge | localhost | ubar_xxxxxx_0001 | Execute | 12 | Sorting result | SELECT n.*, t.*, p.* FROM xf_post p LEFT JOIN xf_thread t ON t.thread_id = p.thread_id LEFT JOIN xf_ |
| 95583 | barfilounge | localhost | ubar_xxxxxx_0001 | Execute | 4 | Sorting result | SELECT n.*, t.*, p.* FROM xf_post p LEFT JOIN xf_thread t ON t.thread_id = p.thread_id LEFT JOIN xf_ |
| 95610 | barfilounge | localhost | ubar_xxxxxx_0001 | Execute | 0 | query end | UPDATE `xf_user` SET `alerts_unread` = ? WHERE (user_id = 653) |
| 95611 | admin | localhost | | Query | 0 | | show processlist |
+-------+-------------+-----------+------------------+----------------+------+--------------------+------------------------------------------------------------------------------------------------------+
[root@barfilounge-com ~]#
--

 

[Mike]

That's still not the full process list ( SHOW FULL PROCESSLIST; ).

Regardless, I'm 99.9% sure this is add-on related, so @Brogan's recommendation is correct.

 

[Adam Howard]

@ineedhelp Should you find which add-on that is causing this, please do share. Thanks

 

[Daniel Hood]

The start of the query looks a lot like my hashtags add on. It shouldn't be running that excessively as it only queries once and it's on a cron job. Unless someone made an add on with a similar query.

It doesn't make much sense though as numerous people run that add on with large databases.

Just to be clear I'm only referring to the select query in that list:

SELECT n.*, t.*, p.* FROM xf_post p LEFT JOIN xf_thread t ON t.thread_id = p.thread_id LEFT JOIN xf_

 

[ineedhelp]

This seems to be one of the main reasons my site has been slow:

http://xenforo.com/community/threads/webgurubb-daily-post-count-addon.34599/

I've disabled this and the site has picked up speed again.

 

[Chris D]

Well that's good news...

But to be clear: that add-on doesn't seem to be performing any of those queries you posted from your log.

I can understand why that add-on is causing issues, though. It's really not very well optimised at all and it uses no caching so it effectively just runs several queries like this on every page load that has a sidebar:

                SELECT COUNT( * ) AS posts, FROM_UNIXTIME( post_date,  '%Y-%m-%d' ) AS DATE
                FROM xf_post
                WHERE FROM_UNIXTIME( post_date,  '%Y-%m-%d' ) = CURDATE()

 

[DRE]

Well that's good news...

But to be clear: that add-on doesn't seem to be performing any of those queries you posted from your log.

I can understand why that add-on is causing issues, though. It's really not very well optimised at all and it uses no caching so it effectively just runs several queries like this on every page load that has a sidebar:

                SELECT COUNT( * ) AS posts, FROM_UNIXTIME( post_date,  '%Y-%m-%d' ) AS DATE
                FROM xf_post
                WHERE FROM_UNIXTIME( post_date,  '%Y-%m-%d' ) = CURDATE()

I'm wondering if this addon had something to do with it: http://xenforo.com/community/resources/default-avatars.1395/

This slowed my site down considerably.

On:
View attachment 58920

Off:
View attachment 58919

The Default Avatars addon seemed to be working fine until I accidentally deleted my Minify folder. I re-uploaded it but I noticed that the php file size of some files within Minify is still different than the ones I upload from the upgrade folder of Xenforo 1.2.2. Currently unable to login to my server for some reason to directly upload them. I have disabled and uninstalled Default Avatars until I can fix my Minify folder. After I disabled and uninstalled it, the queries stopped. I'm hoping this will be fixed after I re-upload my Minify folder properly.