MySQL Problems

[brianw21]

Issue

On occasion something triggers a "This Account has Exceeded Its CPU Quota".

Research

Support was less then helpfull. So I did some reasearch. I pulled the logs from /tmp/cpu_exceeded_logs. Here are the entries that are causing the trigger:

[ 2-14-06 ]
82.00 seconds of cpu time for [[mysql query]]
63.00 seconds of cpu time for [[mysql query]]
195.00 seconds of cpu time for [[mysql query]]

[ 2-10-06 ]
136.02 seconds of cpu time for HTTP Request: GET /phpBB2/viewtopic.php

[ 1-30-06 ]

3.00 seconds of cpu time for [[mysql query]]
30.00 seconds of cpu time for [[mysql query]]
32.00 seconds of cpu time for [[mysql query]]

I've checked my logs, and there was little to no activity during these incidents, they all happened at off-peak times for my site. Php.ini is set at a max of 5 seconds for scripts. This site is a PhpBB2 forum, running the latest security build.

My Conclusion

Since the site runs fine without any execution time problems 99% of the time, and there is no 'ad-hoc'. This makes me think that the problem lies on the MySQL side, or connectivity to it.

What would cause a query that runs fine 99% of the time to suddenly go from .1 seconds to 195 seconds for no apparent reason. The SQL statement is the same, the result set isn't drastically different.

I can run the SQL directly in phpMyAdmin and it executes in .1 seconds or less everytime.

Support

The gentleman on support, told me to "find another host". Flat out refused to look into it, and told me that he would be "happy" to refund/prorate my money.

My first reaction was just that, to switch hosts. But being an IT guy I hate to walk away from problems, especially when it's only 1% or less of the time.

Isn't there a way we can track this down? Are there not logs on the MySQL server? Can't we figure out why a reapeatable SQL query goes to the weeds every now and then?

http://www.idahostreetracing.com

Brian

[holdencn]

i find it interesting that in light of the massive push of how good their support is this guy tells you that you should find a new host.

I am having a similar problem however i think my issue is with reconnection after the crash on box20. i was running ioncube which is now corrupt and i was running a whole slew of admin logins through php and mysql which now dont work. i cant figure it out for the life of me. seems i will have to go back in and reconfigure the databases.

:mad:

[brianw21]

Does anyone know if the MySQL sits on each box, or is seperate?

[solarnexus]

i find it interesting that in light of the massive push of how good their support is this guy tells you that you should find a new host.I dont. You have to remember that bluehost are expanding everyday, and with expansion comes new hiring. Unfortunately, when companies like bluehost expand so fast, customer support quality descreases porportionally to their success. I have no doubt that bluehost, although perhaps letting the odd customer support personel who is less than enthusiastic about their job slip through the net, they will eventually weed out the bad ones. We have already heard many times how bluehost values customers.

In such cases, I'd just try again at another time.

[BenReilly]

I have the exactly same problem:


Sun Apr 23 10:43:18 2006: used 52.00 seconds of cpu time for [[mysql query]]
Sun Apr 23 10:43:19 2006: used 17.00 seconds of cpu time for [[mysql query]]

This happened just now, when few users were online. I'm at box42.

[areidmtm]

search the forums for indexing mysql tables. Many uses have had problems with phpbb and other forums porgrams not indexing the tables, and thus creating the CPU error.

[siguie]

Actually this isn't so much a problem with the MySQL server as it is with the phpBB script. The biggest problem occurs if you have "auto prune" turned on and when people are doing lots of searches of a large database or one that hasn't been optimized in a long time.

In short optimize your database with phpMyAdmin or a mod like "Fix Tables" from phpBB.org then turn off "auto prune". You should also update to phpBB 2.0.20 if you are still having issues there's a new search limit timer that will stop people from doing too many big searches back to back.

This is a really useful thread over at phpBB "phpBB tweaks for large forums (http://http://www.phpbb.com/phpBB/viewtopic.php?t=135383)".

Also keep in mind that bluehost is bargain priced for a reason and odds are good that they have the MySQL server on the same box to extend resources but large SQL databases can generate huge temp files when running certain querries.

[rando]

[[mysql query]] is a placeholder. I'm going to get it actually recording the bad queries, but that involves me rewriting the query log parser.

If you call customer service and get to a level 3 support dude, they can read the query log for you manually and try to find the queries that are being naughty.