SQL queries causing slowdown and outages

[DRE]

I had lots of downtime with xFHost.me so I left and when I did I was given a chance to have my site hosted at a VPS for a limited time. During my stay my site went down again and they gave me a list of what MySQL queries were slowing down my server and deleted my site. So I'm off to looking for another host again. I think 8thos will be down for longer period since I will have to find someone to look at this .txt file to see what's causing issues with my database. I don't know who does anything like that so I will have to take some time this weekend to research.

 

[SneakyDave]

What particular MySQL queries were slow? Were they custom queries or stock xF queries?

I think a lot of hosts focus on the MyISAM tables performance, but don't pay a lot of attention to the InnoDB tables xF uses almost religiously. At least that's what I've noticed in my own installation.

 

[Lisa]

I had lots of downtime with xFHost.me so I left and when I did I was given a chance to have my site hosted at a VPS for a limited time. During my stay my site went down again and they gave me a list of what MySQL queries were slowing down my server and deleted my site. So I'm off to looking for another host again. I think 8thos will be down for longer period since I will have to find someone to look at this .txt file to see what's causing issues with my database. I don't know who does anything like that so I will have to take some time this weekend to research.

DRE, it wasn't quite that gentle. You weren't slowing the server down, you killed it to death LOL. I've never had downtime like it before! I do hope someone can look at those queries though and get your site back online for you. Just wish I could have helped more.

 

[DRE]

DRE, it wasn't quite that gentle. You weren't slowing the server down, you killed it to death LOL. I've never had downtime like it before! I do hope someone can look at those queries though and get your site back online for you. Just wish I could have helped more.

If it was that bad, maybe that may explain why I was having so much downtime at xFHost.me. It probably affected other sites. It's good for them that I left then.

 

[Kevin]

Dre, have you posted the file with the queries anywhere yet?

 

[Lisa]

If it was that bad, maybe that may explain why I was having so much downtime at xFHost.me. It probably affected other sites. It's good for them that I left then.

Yeah, it was. Once the dns propogated and your site was live, it pulled down my entire server. Everything froze up, not a single site would load (not even my WHM or VPS PPP control panels). It took the server three hours to delete the account because it'd hit a certain point and the server would go into a meltdown. That's when my host did some digging and sent the query document. I hope it helps to sort things out and get your site back online. I do wish I could have helped you more

 

[DRE]

What particular MySQL queries were slow? Were they custom queries or stock xF queries?

I think a lot of hosts focus on the MyISAM tables performance, but don't pay a lot of attention to the InnoDB tables xF uses almost religiously. At least that's what I've noticed in my own installation.

k. I changed the name of my database name and user.

Part 1

root@host [~]# mysqladmin proc stat
+-------+----------------+-----------+---------------+----------------+------+----------------------------------+------------------------------------------------------------------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+-------+----------------+-----------+---------------+----------------+------+----------------------------------+------------------------------------------------------------------------------------------------------+
| 3491 | eximstats | localhost | eximstats | Sleep | 86 | | |
| 13507 | ethos_site | localhost | ethos_forum | Execute | 6462 | removing tmp table | SELECT post.*, thread.*, user.*
FROM xf_post AS post
LEFT JOIN xf_thread AS |
| 13850 | ethos_site | localhost | ethos_forum | Execute | 5937 | Sorting result | SELECT post.*, thread.*, user.*
FROM xf_post AS post
LEFT JOIN xf_thread AS |
| 13887 | ethos_site | localhost | ethos_forum | Execute | 5928 | Sorting result | SELECT post.*, thread.*, user.*
FROM xf_post AS post
LEFT JOIN xf_thread AS |
| 13971 | ethos_site | localhost | ethos_forum | Execute | 5807 | Sorting result | SELECT post.*, thread.*, user.*
FROM xf_post AS post
LEFT JOIN xf_thread AS |
| 14066 | ethos_site | localhost | ethos_forum | Execute | 5649 | Sorting result | SELECT post.*, thread.*, user.*
FROM xf_post AS post
LEFT JOIN xf_thread AS |
| 14114 | ethos_site | localhost | ethos_forum | Execute | 5574 | Sorting result | SELECT post.*, thread.*, user.*
FROM xf_post AS post
LEFT JOIN xf_thread AS |
| 14144 | ethos_site | localhost | ethos_forum | Execute | 5559 | Sorting result | SELECT post.*, thread.*, user.*
FROM xf_post AS post
LEFT JOIN xf_thread AS |
| 14174 | ethos_site | localhost | ethos_forum | Execute | 5455 | Sorting result | SELECT post.*, thread.*, user.*
FROM xf_post AS post
LEFT JOIN xf_thread AS |
| 14180 | ethos_site | localhost | ethos_forum | Execute | 5457 | Sorting result | SELECT post.*, thread.*, user.*
FROM xf_post AS post
LEFT JOIN xf_thread AS |
| 14198 | ethos_site | localhost | ethos_forum | Execute | 5455 | Sorting result | SELECT post.*, thread.*, user.*
FROM xf_post AS post
LEFT JOIN xf_thread AS |
| 14255 | ethos_site | localhost | ethos_forum | Execute | 5309 | Sorting result | SELECT post.*, thread.*, user.*
FROM xf_post AS post
LEFT JOIN xf_thread AS |
| 14271 | ethos_site | localhost | ethos_forum | Execute | 5320 | Sorting result | SELECT post.*, thread.*, user.*
FROM xf_post AS post
LEFT JOIN xf_thread AS |
| 14301 | ethos_site | localhost | ethos_forum | Execute | 5320 | Sorting result | SELECT post.*, thread.*, user.*
FROM xf_post AS post
LEFT JOIN xf_thread AS |
| 14389 | ethos_site | localhost | ethos_forum | Execute | 5133 | Sorting result | SELECT post.*, thread.*, user.*
FROM xf_post AS post
LEFT JOIN xf_thread AS |

 

[DRE]

Part 2

| 14390 | ethos_site | localhost | ethos_forum | Execute | 5133 | Sorting result | SELECT post.*, thread.*, user.*
FROM xf_post AS post
LEFT JOIN xf_thread AS |
| 14440 | ethos_site | localhost | ethos_forum | Execute | 5051 | Sorting result | SELECT post.*, thread.*, user.*
FROM xf_post AS post
LEFT JOIN xf_thread AS |
| 14445 | ethos_site | localhost | ethos_forum | Execute | 5050 | Sorting result | SELECT post.*, thread.*, user.*
FROM xf_post AS post
LEFT JOIN xf_thread AS |
| 14475 | ethos_site | localhost | ethos_forum | Execute | 5011 | Sorting result | SELECT post.*, thread.*, user.*
FROM xf_post AS post
LEFT JOIN xf_thread AS |
| 14610 | ethos_site | localhost | ethos_forum | Execute | 4795 | Sorting result | SELECT post.*, thread.*, user.*
FROM xf_post AS post
LEFT JOIN xf_thread AS |
| 14636 | ethos_site | localhost | ethos_forum | Execute | 4730 | Sorting result | SELECT post.*, thread.*, user.*
FROM xf_post AS post
LEFT JOIN xf_thread AS |
| 14650 | ethos_site | localhost | ethos_forum | Execute | 4717 | Sorting result | SELECT post.*, thread.*, user.*
FROM xf_post AS post
LEFT JOIN xf_thread AS |
| 14686 | ethos_site | localhost | ethos_forum | Execute | 4619 | Sorting result | SELECT post.*, thread.*, user.*
FROM xf_post AS post
LEFT JOIN xf_thread AS |
| 14808 | ethos_site | localhost | ethos_forum | Execute | 4458 | Sorting result | SELECT post.*, thread.*, user.*
FROM xf_post AS post
LEFT JOIN xf_thread AS |
| 14855 | ethos_site | localhost | ethos_forum | Execute | 4423 | Sorting result | SELECT post.*, thread.*, user.*
FROM xf_post AS post
LEFT JOIN xf_thread AS |
| 15035 | ethos_site | localhost | ethos_forum | Execute | 4208 | Sorting result | SELECT post.*, thread.*, user.*
FROM xf_post AS post
LEFT JOIN xf_thread AS |
| 15081 | ethos_site | localhost | ethos_forum | Execute | 4154 | Sorting result | SELECT post.*, thread.*, user.*
FROM xf_post AS post
LEFT JOIN xf_thread AS |
| 15126 | ethos_site | localhost | ethos_forum | Prepare | 4077 | Waiting for table metadata lock | SELECT page_name FROM EWRcarta_pages WHERE page_slug = 'index' |
| 15130 | ethos_site | localhost | ethos_forum | Prepare | 4072 | Waiting for table metadata lock | select value, hits
from dark_azucloud_terms_pages pages
left join dark_azucloud_terms terms |
| 15139 | root | localhost | | Query | 4096 | Waiting for table metadata lock | drop database `ethos_forum` |
| 15143 | ethos_site | localhost | ethos_forum | Prepare | 4093 | Waiting for table metadata lock | SELECT data_key, data_value
FROM xf_data_registry
WHERE data_key IN ('options', 'languages', ' |
| 15159 | ethos_site | localhost | ethos_forum | Prepare | 4072 | Waiting for table metadata lock | SELECT data_key, data_value
FROM xf_data_registry
WHERE data_key IN ('options', 'languages', ' |

 

[DRE]

Part 3

| 15162 | ethos_site | localhost | ethos_forum | Prepare | 4054 | Waiting for table metadata lock | SELECT data_key, data_value
FROM xf_data_registry
WHERE data_key IN ('options', 'languages', ' |
| 15164 | ethos_site | localhost | ethos_forum | Prepare | 4050 | Waiting for table metadata lock | SELECT data_key, data_value
FROM xf_data_registry
WHERE data_key IN ('options', 'languages', ' |
| 15165 | ethos_site | localhost | ethos_forum | Prepare | 4044 | Waiting for table metadata lock | SELECT data_key, data_value
FROM xf_data_registry
WHERE data_key IN ('options', 'languages', ' |
| 15171 | ethos_site | localhost | ethos_forum | Prepare | 4031 | Waiting for table metadata lock | SELECT data_key, data_value
FROM xf_data_registry
WHERE data_key IN ('options', 'languages', ' |
| 15174 | ethos_site | localhost | ethos_forum | Prepare | 4024 | Waiting for table metadata lock | SELECT data_key, data_value
FROM xf_data_registry
WHERE data_key IN ('options', 'languages', ' |
| 15182 | ethos_site | localhost | ethos_forum | Prepare | 4019 | Waiting for table metadata lock | SELECT data_key, data_value
FROM xf_data_registry
WHERE data_key IN ('options', 'languages', ' |
| 15190 | ethos_site | localhost | ethos_forum | Prepare | 4011 | Waiting for table metadata lock | SELECT data_key, data_value
FROM xf_data_registry
WHERE data_key IN ('options', 'languages', ' |
| 15191 | ethos_site | localhost | ethos_forum | Prepare | 4011 | Waiting for table metadata lock | SELECT data_key, data_value
FROM xf_data_registry
WHERE data_key IN ('options', 'languages', ' |
| 15196 | ethos_site | localhost | ethos_forum | Prepare | 4002 | Waiting for table metadata lock | SELECT data_key, data_value
FROM xf_data_registry
WHERE data_key IN ('options', 'languages', ' |
| 15197 | ethos_site | localhost | ethos_forum | Prepare | 4001 | Waiting for table metadata lock | SELECT data_key, data_value
FROM xf_data_registry
WHERE data_key IN ('options', 'languages', ' |
| 15198 | ethos_site | localhost | ethos_forum | Prepare | 3998 | Waiting for table metadata lock | SELECT data_key, data_value
FROM xf_data_registry
WHERE data_key IN ('options', 'languages', ' |
| 15203 | ethos_site | localhost | ethos_forum | Prepare | 3989 | Waiting for table metadata lock | SELECT data_key, data_value
FROM xf_data_registry
WHERE data_key IN ('options', 'languages', ' |
| 15206 | ethos_site | localhost | ethos_forum | Prepare | 3985 | Waiting for table metadata lock | SELECT data_key, data_value
FROM xf_data_registry
WHERE data_key IN ('options', 'languages', ' |

 

[DRE]

Part 4

| 15212 | ethos_site | localhost | ethos_forum | Prepare | 3973 | Waiting for table metadata lock | SELECT data_key, data_value
FROM xf_data_registry
WHERE data_key IN ('options', 'languages', ' |
| 15219 | ethos_site | localhost | ethos_forum | Prepare | 3960 | Waiting for table metadata lock | SELECT data_key, data_value
FROM xf_data_registry
WHERE data_key IN ('options', 'languages', ' |
| 15227 | ethos_site | localhost | ethos_forum | Prepare | 3949 | Waiting for table metadata lock | SELECT data_key, data_value
FROM xf_data_registry
WHERE data_key IN ('options', 'languages', ' |
| 15228 | ethos_site | localhost | ethos_forum | Prepare | 3949 | Waiting for table metadata lock | SELECT data_key, data_value
FROM xf_data_registry
WHERE data_key IN ('options', 'languages', ' |
| 15238 | ethos_site | localhost | ethos_forum | Prepare | 3936 | Waiting for table metadata lock | SELECT data_key, data_value
FROM xf_data_registry
WHERE data_key IN ('options', 'languages', ' |
| 15251 | ethos_site | localhost | ethos_forum | Prepare | 3924 | Waiting for table metadata lock | SELECT data_key, data_value
FROM xf_data_registry
WHERE data_key IN ('options', 'languages', ' |
| 15257 | ethos_site | localhost | ethos_forum | Prepare | 3910 | Waiting for table metadata lock | SELECT data_key, data_value
FROM xf_data_registry
WHERE data_key IN ('options', 'languages', ' |
| 15274 | ethos_site | localhost | ethos_forum | Prepare | 3902 | Waiting for table metadata lock | SELECT data_key, data_value
FROM xf_data_registry
WHERE data_key IN ('options', 'languages', ' |
| 15472 | root | localhost | | Query | 3539 | Waiting for schema metadata lock | drop database `ethos_forum` |
| 15964 | root | localhost | | Query | 2627 | Waiting for schema metadata lock | drop database `ethos_forum` |
| 16388 | leechprotect | localhost | leechprotect | Sleep | 1526 | | |
| 16693 | DELAYED | localhost | adminx_adxxf | Delayed insert | 56 | Waiting for INSERT | |
| 17027 | DELAYED | localhost | cliquega_main | Delayed insert | 186 | Waiting for INSERT | |
| 17049 | DELAYED | localhost | eximstats | Delayed insert | 86 | Waiting for INSERT | |
| 17090 | repowner_admin | localhost | repowner_xf | Sleep | 0 | | |
| 17091 | root | localhost | | Query | 0 | | show processlist |
+-------+----------------+-----------+---------------+----------------+------+----------------------------------+------------------------------------------------------------------------------------------------------+
Uptime: 31849 Threads: 61 Questions: 844021 Slow queries: 7284 Opens: 257609 Flush tables: 1 Open tables: 400 Queries per second avg: 26.500
root@host [~]#

 

[Chris D]

The thing that strikes me as odd is the queries don't seem to be complete queries for a start, but that just might be how they're logged, I don't know...

But also I'm not sure I can identify what is generating those queries...

It seems to be at first glance planning to join the entire thread, post and user tables together... that doesn't sound like a great idea.

DRE, when we get your backup uploaded I may download it and have a sneaky look through the files if that's ok... see if I can't identify the add-on generating them.

 

[Lisa]

Chris, if you figure it out let me know because it's bugging me what was causing it too!

 

[SneakyDave]

I've seen those "sorting results" run long if the tmp file space isn't very large, as the SORTing uses the temp file system to do it. Looks like it's taking 5 seconds just to do some sorting?

But you also have 3 'drop database' queries listed in there, or was that part of the removal of your site?

Obviously doesn't help, but it looks interesting.

Is there just one MySQL database running, and 8thos xF installation is on it?

 

[Chris D]

Well, if the query was:

SELECT post.*, thread.*, user.*
FROM xf_post AS post
LEFT JOIN xf_thread AS thread ON
(post.thread_id = thread.thread_id)
LEFT JOIN xf_user AS user ON
(thread.user_id = user.user_id)

That's... well... how is your server not on fire?

 

[SneakyDave]

I don't think that's the entire SQL, but if it was, yikes. The server has only been up about 8 hours, and there are already 7000+ slow queries logged?

 

[Syndol]

Search all folders that belong to your add-ons for
"post.*, thread.*, user.*

 

[Mike Edge]

If it was that bad, maybe that may explain why I was having so much downtime at xFHost.me. It probably affected other sites. It's good for them that I left then.

At least you are seeing now all the times you received errors and I told you it was add-on related, but insisted it was server related, you are now seeing otherwise as you had these issues before us and you had the issues on Lisa's VPS. Just like when we were still using Litespeed, all the 503 errors we were getting on all sites, I told you that it was your database causing it.

With the amount of heavy addons you run, unless you trim the fat off a lot, your going to have this issue at any host. I think the best solution for you is a dedicated server. Though you don't have a high load of traffic, your site is very resource intense.

 

[Andy.N]

I wonder what addons cause this? I think he runs a boatload of addons.

 

[Sheldon]

At least you are seeing now all the times you received errors and I told you it was add-on related, but insisted it was server related, you are now seeing otherwise as you had these issues before us and you had the issues on Lisa's VPS. Just like when we were still using Litespeed, all the 503 errors we were getting on all sites, I told you that it was your database causing it.

With the amount of heavy addons you run, unless you trim the fat off a lot, your going to have this issue at any host. I think the best solution for you is a dedicated server. Though you don't have a high load of traffic, your site is very resource intense.

Thanks for the help in clearing up the issue.

 

[Shelley]

I think the only way to resolve these issues is by installing stewart's meme's #crazyadvice