• This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn more.

XF 1.2 Site being hammered by queries.

ineedhelp

Well-known member
#1
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.
 

Mike

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

ineedhelp

Well-known member
#4
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 ~]#
--
 

Daniel Hood

Well-known member
#7
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:

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

Chris D

XenForo developer
Staff member
#9
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:

PHP:
                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-known member
#10
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:

PHP:
                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.