Hey everyone

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

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 = '<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 TABLE `website_visits` (
  `visitId` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `websiteId` int(11) unsigned NOT NULL,
  `IP` varchar(40) DEFAULT NULL,
  `valid` int(2) DEFAULT '0',
  `skip` int(2) DEFAULT '0',
  `datetime` datetime DEFAULT NULL,
  `endtime` datetime DEFAULT NULL,
  `address` varchar(40) DEFAULT NULL,
  PRIMARY KEY (`visitId`),
  KEY `websiteId` (`websiteId`),
  KEY `address` (`address`),
  KEY `IP` (`IP`)
) ENGINE=InnoDB AUTO_INCREMENT=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 innodb_force_recover = 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?

_________________
Amateur, eager to learn.
My hematology, oncology, and chemotherapy regimen wiki

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.