MySQL extremely high CPU usage?

What do the queries look like hitting that table? Your indexes are pretty simple, just single fields. If lots of the queries hit multiple fields, then more indexes would help.

I have gotten rid of most of the select queries on the payout table actually, almost all queries hitting that table are simple inserts or updates ("insert into payouts(websiteId, address, amount, datetime, paid, isRef …."). Strangely enough alot of these insert/updates are showing up in the slow query log…

Im really at a loss as to whats causing this. And apparantly upgrading your linode gives you no extra processing power, just higher priority and more ram. Tried upgrading to the next level and increasing my cache, and even lowered my max connections. still same problem, easily getting up to 700+ %

Tried dropping all indexes and foreign keys, running 'optimize table' on all tables, adding all the keys back in and then running mysqlcheck -A (I saw this suggested somewhere). That seemed to actually work somewhat… for like less than a day. Now back to the same problem.

Also tried doing a database dump, dropping the database and then completely reinstalling MySQL (made sure its version 5.5) and then loading up the DB again. still nothing.

The only other thing I can think of doing really is just exporting all the rows into JSON files or something, dropping the database, rebuilding it from scratch and then create a script to add all the rows back in. Im not even sure if that will work though… About a month ago I basically did just that, created a new version of the database, exported everything from the old version and re-imported it (I had this same CPU problem with the old database).

Here are some example queries that are showing up in my slow log now (for a little bit I seemed to have very few but now several are showing up again, and from other tables)

`INSERT INTO payouts(websiteId, address, amount, datetime, paid, isRef) VALUES('649', '`

<address withheld="">`', '3.0E-8', '2014-01-30 07:36:16', '0', '1');
Query time: 24 seconds

SET timestamp=1391092096;
Query time: 10 seconds (?????)

SELECT count(*) FROM website_visits WHERE websiteId = '457'

                                        AND (IP = '<ip withheld="">' OR address = '</ip>`

<address withheld="">`')

                                        AND valid = 1

                                        AND `datetime` > "2014-01-29 06:27:42";
query time: 23 seconds` 

That website visits table is another one besides payouts that I have had issues with, seemed to not cause any issues for a while until again just recently though. Here is the create table for it:

CREATE TABLEwebsitevisits( visitIdint(11) unsigned NOT NULL AUTO_INCREMENT, websiteIdint(11) unsigned NOT NULL, IPvarchar(40) DEFAULT NULL, validint(2) DEFAULT '0', skipint(2) DEFAULT '0', datetimedatetime DEFAULT NULL, endtimedatetime DEFAULT NULL, addressvarchar(40) DEFAULT NULL, PRIMARY KEY (visitId), KEYwebsiteId(websiteId¨C13Caddress¨C14Caddress¨C15CIP¨C16CIP`) ) ENGINE=InnoDB AUTOINCREMENT=261590 DEFAULT CHARSET=latin1
````

Any ideas?? Would having the database on its own dedicated linode (its shared with apache right now) work better maybe? There has to be something messed up or something im doing wrong here…

also, there have been a few times where MySQL has just crashed and then wont start up again (job start failed). Only way I have been able to get it back up when that happens is adding innodbforcerecover = 1, which lets it start up, then when I turn that off again and restart it appears to work fine. Could have something to do with it?

thanks

```

You definitely know more than me about MySQL, but just in case: I had improved performance on my site recently by caching relatively static pages rather than hitting the database for each load. When the pages are updated with new information, it refreshes the cache entry. I'm assuming that you've optimized things as much as possible to reduce the load on your DB?

@yaz:

You definitely know more than me about MySQL, but just in case: I had improved performance on my site recently by caching relatively static pages rather than hitting the database for each load. When the pages are updated with new information, it refreshes the cache entry. I'm assuming that you've optimized things as much as possible to reduce the load on your DB?

Thanks for the suggestion. The site is constantly updating and inserting new rows, so query cache isnt too effective since it frequently gets invalidated. I read that having a large query cache can have a pretty big impact on performance besides just ram usage, so I tried turning mine way down… however still having the same super high CPU issue

Not sure what you are running in terms of backend but in my case. I have two databases.. a main one with about 600k rows. and a data one with 2 million rows (this one is thrashed with updates all the time). 30-50 queries per second. I do have them indexed but caching with static is no go since this particular DB is real time.

This was consuming 20-40% CPU under apache2/php5 (mod_php, mysql, myisam tables)

I heard that InnoDB is better for tables that get writes so i changed my heavy areas all into InnoDB. Swapped out mod_php for fastcgi/php5-fpm. Increased my InnoDB cache size to around the size of my tables. Also dropped in memcached for caching. Granted it uses more memory but the whole thing zips through. CPU usage dropped from 20-40 to just 2-10%.

And Im on a Linode 1024 as well.