Mysql – High Mysql Load , over 700% CPU

loadmyisamMySQL

I had high mysql load on server linux 64 bit , 24 G.B ram , Intel(R) Core(TM) i7 CPU 950 @ 3.07GHz ,

Alot of quiers in sending data mode

Here is mysql status 

+------+--------------+-----------+--------------+---------+------+----------------+---------------------------------------------------------------------------------------------+
| Id   | User         | Host      | db           | Command | Time | State          | Info                                                                                        |
+------+--------------+-----------+--------------+---------+------+----------------+---------------------------------------------------------------------------------------------+
| 1    | leechprotect | localhost | leechprotect | Sleep   | 507  |                |                                                                                             |
| 422  | face_book  | localhost | face_book  | Query   | 0    | Sending data   | SELECT * FROM likes WHERE userid='100002047302002' and pageid='113623891994626' Limit 1     |
| 440  | face_book  | localhost | face_book  | Query   | 0    | Sending data   | SELECT * FROM likes WHERE userid='100003610486105' and pageid='137067399778568' Limit 1     |
| 745  | face_book  | localhost | face_book  | Query   | 3    | Sending data   | SELECT * FROM likes WHERE userid='100000156154972' and pageid='259472294088694' Limit 1     |
| 813  | face_book  | localhost | face_book  | Query   | 1    | Sending data   | SELECT * FROM likes WHERE userid='100001079730269' and pageid='111612552199698' Limit 1     |
| 817  | face_book  | localhost | face_book  | Query   | 2    | Sending data   | SELECT * FROM likes WHERE userid='100003718366503' and pageid='105790599509795' Limit 1     |
| 888  | face_book  | localhost | face_book  | Query   | 0    | Sending data   | SELECT * FROM friends WHERE userid='100004673917946' and friendid='100004836366957' Limit 1 |
| 930  | face_book  | localhost | face_book  | Query   | 4    | Sending data   | SELECT * FROM likes WHERE userid='100001857826693' and pageid='379878825440539' Limit 1     |
| 940  | face_book  | localhost | face_book  | Query   | 3    | Sending data   | SELECT * FROM likes WHERE userid='100002525443288' and pageid='432454306781258' Limit 1     |
| 976  | face_book  | localhost | face_book  | Query   | 3    | Sending data   | SELECT * FROM likes WHERE userid='100001786746020' and pageid='266169045612' Limit 1        |
| 980  | face_book  | localhost | face_book  | Query   | 3    | Sending data   | SELECT * FROM likes WHERE userid='100000721604707' and pageid='188587591283392' Limit 1     |
| 999  | face_book  | localhost | face_book  | Query   | 1    | Sending data   | SELECT * FROM likes WHERE userid='100001661124181' and pageid='161323847303028' Limit 1     |
| 1033 | face_book  | localhost | face_book  | Query   | 2    | Sending data   | SELECT * FROM likes WHERE userid='100002273583367' and pageid='447287665321823' Limit 1     |
| 1064 | face_book  | localhost | face_book  | Query   | 2    | Sending data   | SELECT * FROM likes WHERE userid='100003902289965' and pageid='315361025251697' Limit 1     |
| 1100 | face_book  | localhost | face_book  | Query   | 2    | Sending data   | SELECT * FROM likes WHERE userid='100002720670629' and pageid='114370700612' Limit 1        |
| 1109 | face_book  | localhost | face_book  | Query   | 1    | Sending data   | SELECT * FROM friends WHERE userid='100002279885489' and friendid='100002385585461' Limit 1 |
| 1111 | face_book  | localhost | face_book  | Query   | 2    | Sending data   | SELECT * FROM friends WHERE userid='100001087100886' and friendid='100005456647732' Limit 1 |
| 1132 | face_book  | localhost | face_book  | Query   | 3    | Sending data   | SELECT * FROM likes WHERE userid='100005404566097' and pageid='225594034251253' Limit 1     |
| 1148 | face_book  | localhost | face_book  | Query   | 2    | Sending data   | SELECT * FROM likes WHERE userid='100005551654543' and pageid='104088939622341' Limit 1     |
| 1172 | face_book  | localhost | face_book  | Query   | 3    | Sending data   | SELECT * FROM likes WHERE userid='100000009185323' and pageid='110343285691930' Limit 1     |
| 1188 | face_book  | localhost | face_book  | Query   | 1    | Sending data   | SELECT * FROM likes WHERE userid='100003468150624' and pageid='182937471830173' Limit 1     |
| 1192 | face_book  | localhost | face_book  | Query   | 2    | Sending data   | SELECT * FROM likes WHERE userid='100000619411698' and pageid='527695070578211' Limit 1     |
| 1196 | face_book  | localhost | face_book  | Query   | 3    | Sending data   | SELECT * FROM likes WHERE userid='100002866966279' and pageid='244651575605946' Limit 1     |
| 1208 | face_book  | localhost | face_book  | Query   | 1    | Sending data   | SELECT * FROM friends WHERE userid='100001057034709' and friendid='1080136538' Limit 1      |
| 1230 | face_book  | localhost | face_book  | Query   | 1    | Sending data   | SELECT * FROM friends WHERE userid='100005246283878' and friendid='100002513789129' Limit 1 |
| 1240 | face_book  | localhost | face_book  | Query   | 2    | Sending data   | SELECT * FROM friends WHERE userid='100005028574119' and friendid='100001229276848' Limit 1 |
| 1241 | face_book  | localhost | face_book  | Query   | 2    | Sending data   | SELECT * FROM friends WHERE userid='1681467791' and friendid='1537753959' Limit 1           |
| 1242 | face_book  | localhost | face_book  | Query   | 0    | Sending data   | SELECT * FROM friends WHERE userid='100001845705855' and friendid='1668437534' Limit 1      |
| 1247 | face_book  | localhost | face_book  | Query   | 2    | Sending data   | SELECT * FROM friends WHERE userid='100003854517927' and friendid='100002322873932' Limit 1 |
| 1257 | face_book  | localhost | face_book  | Query   | 0    | Sorting result | SELECT lastpost,id FROM facesessions ORDER BY lastpost DESC                                 |
| 1276 | face_book  | localhost | face_book  | Query   | 3    | Sending data   | SELECT * FROM likes WHERE userid='100001579975664' and pageid='402312375509' Limit 1        |
| 1284 | face_book  | localhost | face_book  | Query   | 1    | Sending data   | SELECT * FROM likes WHERE userid='100001827038305' and pageid='254365179238' Limit 1        |
| 1291 | face_book  | localhost | face_book  | Query   | 3    | Sending data   | SELECT * FROM likes WHERE userid='1587203387' and pageid='197678701083' Limit 1             |
| 1309 | face_book  | localhost | face_book  | Query   | 1    | Sending data   | SELECT * FROM friends WHERE userid='100002156769339' and friendid='100001641695726' Limit 1 |
| 1318 | face_book  | localhost | face_book  | Query   | 3    | Sending data   | SELECT * FROM likes WHERE userid='100002230633105' and pageid='343669279056732' Limit 1     |
| 1325 | face_book  | localhost | face_book  | Sleep   | 8    |                |                                                                                             |
| 1333 | face_book  | localhost | face_book  | Sleep   | 2    |                |                                                                                             |
| 1338 | face_book  | localhost | face_book  | Sleep   | 0    |                |                                                                                             |
| 1339 | root         | localhost |              | Query   | 0    |                | show processlist                                                                            |
+------+--------------+-----------+--------------+---------+------+----------------+---------------------------------------------------------------------------------------------+
Uptime: 508  Threads: 38  Questions: 65938  Slow queries: 0  Opens: 51156  Flush tables: 1  Open tables: 34  Queries per second avg: 129.799

and here is /etc/my.cnf

[client]
socket=/var/lib/mysql/mysql.sock

[mysqld]
tmpdir=/mysqltmp

port = 3306
socket = /var/lib/mysql/mysql.sock
skip-networking
skip-external-locking
local-infile=0

back_log = 100
#skip-innodb
max_connections = 50
table_cache = 32
key_buffer_size = 12284M
myisam_sort_buffer_size = 512M
myisam_max_sort_file_size = 8192M
join_buffer_size = 512M
read_buffer_size = 512M
sort_buffer_size = 512M
read_rnd_buffer_size = 512M
table_definition_cache = 8000
table_open_cache = 8000
thread_cache_size = 4
wait_timeout = 360
interactive_timeout = 60
connect_timeout = 10
tmp_table_size = 256M
max_heap_table_size = 256M
max_allowed_packet = 512M
max_seeks_for_key = 1000
group_concat_max_len = 1024
max_length_for_sort_data = 1024
net_buffer_length = 16384
max_connect_errors = 100000
concurrent_insert = 2
bulk_insert_buffer_size = 8M
query_cache_limit = 1M
query_cache_size = 256M
query_cache_type = 1
query_prealloc_size = 262144
query_alloc_block_size = 65536
range_alloc_block_size = 4096
transaction_alloc_block_size = 8192
transaction_prealloc_size = 4096
default-storage-engine = MyISAM
max_write_lock_count = 8

innodb_open_files = 500
innodb_data_file_path= ibdata1:10M:autoextend
innodb_buffer_pool_size = 64M
innodb_additional_mem_pool_size = 32M

innodb_log_files_in_group = 2
innodb_log_file_size = 5M
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 2
innodb_thread_concurrency = 8

#slow_query_log=0
#long_query_time=1
#slow_query_log_file=/var/log/mysql/log-slow-queries.log

open_files_limit=50000
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
nice = -5
open-files-limit = 8192

[mysqldump]
quick
max_allowed_packet = 512M

[myisamchk]
key_buffer_size = 6400M
sort_buffer_size = 1M
read_buffer_size = 1M
write_buffer_size = 1M

[mysqlhotcopy]
interactive-timeout

i tried to optimize table and adjust my.cnf with mysqlreport still the same

i don't use InnoDB

mysql version 

# mysql -V
mysql  Ver 14.14 Distrib 5.1.68, for unknown-linux-gnu (x86_64) using readline 5.1

mysql> SHOW CREATE TABLE friends\G

*************************** 1. row ***************************
       Table: friends
Create Table: CREATE TABLE `friends` (
  `id` int(100) unsigned NOT NULL AUTO_INCREMENT,
  `userid` mediumtext COLLATE latin1_general_ci,
  `friendid` mediumtext COLLATE latin1_general_ci,
  `name` varchar(255) COLLATE latin1_general_ci NOT NULL,
  `dateline` varchar(255) COLLATE latin1_general_ci NOT NULL,
  `lastsend` varchar(255) COLLATE latin1_general_ci NOT NULL,
  `sendstatus` varchar(255) COLLATE latin1_general_ci NOT NULL,
  PRIMARY KEY (`id`),
  KEY `name` (`name`),
  KEY `lastsend` (`lastsend`),
  KEY `sendstatus` (`sendstatus`)
) ENGINE=MyISAM AUTO_INCREMENT=1079024 DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci
1 row in set (0.00 sec)

mysql> SHOW CREATE TABLE likes\G

*************************** 1. row ***************************
       Table: likes
Create Table: CREATE TABLE `likes` (
  `id` int(100) unsigned NOT NULL AUTO_INCREMENT,
  `userid` mediumtext COLLATE latin1_general_ci,
  `pageid` mediumtext COLLATE latin1_general_ci,
  `name` varchar(255) COLLATE latin1_general_ci NOT NULL,
  `link` varchar(255) COLLATE latin1_general_ci NOT NULL,
  `dateline` varchar(255) COLLATE latin1_general_ci NOT NULL,
  `lastsend` varchar(255) COLLATE latin1_general_ci NOT NULL,
  `sendstatus` varchar(255) COLLATE latin1_general_ci NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=2008744 DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci
1 row in set (0.00 sec)

mysql> SHOW CREATE TABLE facesessions\G

*************************** 1. row ***************************
       Table: facesessions
Create Table: CREATE TABLE `facesessions` (
  `id` int(100) unsigned NOT NULL AUTO_INCREMENT,
  `session_key` varchar(255) COLLATE latin1_general_ci NOT NULL,
  `uid` varchar(255) COLLATE latin1_general_ci NOT NULL,
  `expires` varchar(255) COLLATE latin1_general_ci NOT NULL,
  `secret` varchar(255) COLLATE latin1_general_ci NOT NULL,
  `access_token` varchar(255) COLLATE latin1_general_ci NOT NULL,
  `sig` varchar(255) COLLATE latin1_general_ci NOT NULL,
  `username` varchar(255) COLLATE latin1_general_ci NOT NULL,
  `lastposttime` varchar(255) COLLATE latin1_general_ci NOT NULL,
  `lastpost` varchar(255) COLLATE latin1_general_ci NOT NULL,
  `nextsend` varchar(50) COLLATE latin1_general_ci DEFAULT NULL,
  `lastpoststatus` varchar(50) COLLATE latin1_general_ci DEFAULT NULL,
  `gender` varchar(20) COLLATE latin1_general_ci DEFAULT NULL,
  `birthday` varchar(20) COLLATE latin1_general_ci DEFAULT NULL,
  `location` varchar(50) COLLATE latin1_general_ci DEFAULT NULL,
  `imported` int(10) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `uid` (`uid`),
  KEY `access_token` (`access_token`),
  KEY `sig` (`sig`),
  KEY `username` (`username`),
  KEY `lastposttime` (`lastposttime`),
  KEY `lastpost` (`lastpost`),
  KEY `nextsend` (`nextsend`),
  KEY `lastpoststatus` (`lastpoststatus`)
) ENGINE=MyISAM AUTO_INCREMENT=16238 DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci
1 row in set (0.00 sec)

mysql> SELECT SUM(index_length) FROM information_schema.tables WHERE engine='MyISAM';

+-------------------+
| SUM(index_length) |
+-------------------+
|         150684672 |
+-------------------+
1 row in set (0.01 sec)

Best Answer

OBSERVATION #1

Look at this query

SELECT * FROM likes WHERE userid='100002047302002' and pageid='113623891994626' Limit 1

The likes table have no indexes other than the PRIMARY KEY. Please run this:

ALTER TABLE likes ADD INDEX userid_pageid_ndx (userid,pageid);

Unfortunately, you cannot index likes because of the datatype (MEDIUMTYPE). Please consider changing the datatype of userid and friendtype.

Your should create a new version of the likes table.

CREATE TABLE likesnew LIKE likes;
ALTER TABLE likesnew MODIFY COLUMN userid CHAR(15) NOT NULL;
ALTER TABLE likesnew MODIFY COLUMN friendid CHAR(15) NOT NULL;
ALTER TABLE likesnew ADD INDEX userid_pageid_ndx (userid,pageid);
ALTER TABLE likesnew DISABLE KEYS;
INSERT INTO likesnew SELECT * FROM likes;
ALTER TABLE likesnew ENABLE KEYS;
ALTER TABLE likes RENAME likesbak;
ALTER TABLE likesnew RENAME likes;

Please implement the datatype suggested.

Looking at the other query

SELECT * FROM friends WHERE userid='100001087100886' and friendid='100005456647732' Limit 1

I find the same story. Please make the same changes

CREATE TABLE friendsnew LIKE friends;
ALTER TABLE friendsnew MODIFY COLUMN userid CHAR(15) NOT NULL;
ALTER TABLE friendsnew MODIFY COLUMN friendid CHAR(15) NOT NULL;
ALTER TABLE friendsnew ADD INDEX userid_pageid_ndx (userid,pageid);
ALTER TABLE friendsnew DISABLE KEYS;
INSERT INTO friendsnew SELECT * FROM friends;
ALTER TABLE friendsnew ENABLE KEYS;
ALTER TABLE friends RENAME friendsbak;
ALTER TABLE friendsnew RENAME friends;

If you ever want to revert back to the old tables, then do this:

CREATE TABLE likesnew LIKE likesbak;
INSERT INTO likesnew SELECT * FROM likes;
ALTER TABLE likes RENAME likeszap;
ALTER TABLE likesnew RENAME likes;
CREATE TABLE friendsnew LIKE friendsbak;
INSERT INTO friendsnew SELECT * FROM friends;
ALTER TABLE friends RENAME friendszap;
ALTER TABLE friendsnew RENAME friends;

OBSERVATION #2

Your key_buffer_size is way too big. Remember the query I asked you to run ?

mysql> SELECT SUM(index_length) FROM information_schema.tables WHERE engine='MyISAM';

+-------------------+
| SUM(index_length) |
+-------------------+
|         150684672 |
+-------------------+
1 row in set (0.01 sec)

That's about 150M. So, here is the deal:

If you do not implement OBSERVATION #1 set key_buffer_size to 150M

[mysqld]
key_buffer_size=150M

If you do implement OBSERVATION #1, after making the new indexes, rerun that query like this:

SELECT SUM(index_length)/power(1024,2) RKBS
FROM information_schema.tables WHERE engine='MyISAM';

Whatever number comes back, use that number to set the key_buffer_size.

OBSERVATION #3 (Optional)

Keep in mind that the MyISAM Storage Engine does not take advantage of Multiple CPUs/Core no matter how many CPUs/Cores are installed. You need to consider changing all the tables to InnoDB. Here are my past posts on MySQL 5.5 and activating multiple cores for InnoDB

Related Question