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

SQL queries causing slowdown and outages

Discussion in 'Troubleshooting and Problems' started by DRE, Mar 12, 2013.

  1. DRE

    DRE Well-Known Member

    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.
     
    Brandon Sheley likes this.
  2. SneakyDave

    SneakyDave Well-Known Member

    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.
     
  3. Lisa

    Lisa Well-Known Member

    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.
     
  4. DRE

    DRE Well-Known Member

    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.
     
    The Forum Heroes likes this.
  5. Kevin

    Kevin Well-Known Member

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

    Lisa Well-Known Member

    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 :(
     
  7. DRE

    DRE Well-Known Member

    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 |
     
  8. DRE

    DRE Well-Known Member

    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', ' |
     
  9. DRE

    DRE Well-Known Member

    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', ' |
     
  10. DRE

    DRE Well-Known Member

    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 [~]#
     
  11. Chris D

    Chris D XenForo Developer Staff Member

    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.
     
    Brandon Sheley and Lisa like this.
  12. Lisa

    Lisa Well-Known Member

    Chris, if you figure it out let me know because it's bugging me what was causing it too!
     
    Chris D likes this.
  13. SneakyDave

    SneakyDave Well-Known Member

    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?
     
  14. Chris D

    Chris D XenForo Developer Staff Member

    Well, if the query was:

    Code:
    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?
     
  15. SneakyDave

    SneakyDave Well-Known Member

    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?
     
  16. Syndol

    Syndol Guest

    Search all folders that belong to your add-ons for
    "post.*, thread.*, user.*
     
    HWS likes this.
  17. The Forum Heroes

    The Forum Heroes Well-Known Member


    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.
     
    euantor, Fuhrmann, Jeremy and 2 others like this.
  18. Andy.N

    Andy.N Well-Known Member

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

    Sheldon Well-Known Member

    Thanks for the help in clearing up the issue.
     
  20. Shelley

    Shelley Well-Known Member

    I think the only way to resolve these issues is by installing stewart's meme's #crazyadvice
     
    DRE and SneakyDave like this.

Share This Page