MySQL extremely high CPU usage?
So I have a single, fairly high traffic (~5,000 visitors a day, up to 100 online at any one time) and pretty database heavy website running off of a single linode 1024.
For quite a while now the mysql process has been running at ridiculously high %. Like between 300 and 700+ % CPU usage at most times.
At first I figured it was due to a bunch of slow queries. I had quite a few very inneficient queries running on the site, but I have since removed or vastly improved those, to the point where none of them are showing up on my slow query log anymore. However the CPU usage is still through the roof and I cant figure out why…
Many of the slow queries were ones that were accessing a table called 'payouts', which had about 3 million + rows. Everything from update to basic select to sum() functions were showing up in the slow log from this table. I figure its because of the large number of rows, but MySQL should be able to handle several million rows no problem.. no? I managed to truncate this table down to under 500,000 rows (most were not needed other than purely statistical purposes), which seems to have sped alot of those queries up, but yet again, MySQL running at 750% CPU….
This is my SHOW CREATE TABLE output for that payouts table:
CREATE TABLE `payouts` ( `payoutId` int(11) unsigned NOT NULL AUTO_INCREMENT, `websiteId` int(11) unsigned NOT NULL, `address` varchar(40) DEFAULT NULL, `amount` decimal(10,8) NOT NULL, `datetime` datetime DEFAULT NULL, `paid` int(2) DEFAULT '0', `isRef` int(2) DEFAULT '0', PRIMARY KEY (`payoutId`), KEY `websiteId` (`websiteId`), KEY `address` (`address`), KEY `paid` (`paid`), KEY `isRef` (`isRef`), KEY `address_2` (`address`) ) ENGINE=InnoDB AUTO_INCREMENT=3398985 DEFAULT CHARSET=latin1
also, here is the settings in my My.cnf configuration file:
[mysqld_safe] socket = /var/run/mysqld/mysqld.sock nice = 0 [mysqld] user = mysql pid-file = /var/run/mysqld/mysqld.pid socket = /var/run/mysqld/mysqld.sock port = 3306 basedir = /usr datadir = /var/lib/mysql tmpdir = /tmp lc-messages-dir = /usr/share/mysql skip-external-locking bind-address = 127.0.0.1 key_buffer = 32M max_allowed_packet = 16M thread_stack = 192K thread_cache_size = 8 myisam-recover = BACKUP max_connections = 200 query_cache_limit = 64M query_cache_size = 256M log_error = /var/log/mysql/error.log log_slow_queries = /var/log/mysql/mysql-slow.log expire_logs_days = 10 max_binlog_size = 100M innodb_read_io_threads = 8 innodb_write_io_threads = 8 innodb_thread_concurrency = 0 innodb_buffer_pool_size = 128M innodb_io_capacity = 1000 [mysqldump] quick quote-names max_allowed_packet = 16M [mysql] [isamchk] key_buffer = 32M !includedir /etc/mysql/conf.d/
Anyone have any idea what im doing wrong..? Or is my Linode 1024 simply not able to handle that much traffic? Linode isnt liking that im using so much CPU :p
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:
int(11) unsigned NOT NULL AUTO_INCREMENT,
int(11) unsigned NOT NULL,
varchar(40) DEFAULT NULL,
int(2) DEFAULT '0',
int(2) DEFAULT '0',
datetime DEFAULT NULL,
datetime DEFAULT NULL,
varchar(40) DEFAULT NULL,
PRIMARY KEY (visitId
) 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?
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
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.