High Mysql Load , over 700% CPU

Question

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)

1) What version of MySQL are you running ? 2) Is all your data InnoDB ? 3) Please post SHOW CREATE TABLE friends\G 4) Please post SHOW CREATE TABLE likes\G 5) Please post SHOW CREATE TABLE facesessions\G — RolandoMySQLDBA, Apr 20, 2013 at 21:45
@RolandoMySQLDBA i update my question for the details you looking for — iLinux85, Apr 20, 2013 at 22:03
I need something else. Run this please: SELECT SUM(index_length) FROM information_schema.tables WHERE engine='MyISAM'; — RolandoMySQLDBA, Apr 20, 2013 at 22:33

Community · Accepted Answer · 2017-04-13 12:42:52Z

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

Jun 01, 2012 : I've got 16GB of ram, how should I configure MySQL Server?
May 07, 2012 : MySQL Server Performance
Apr 26, 2012 : Is the CPU performance relevant for a database server?
Mar 16, 2012 : Using multiple cores for single MySQL queries on Debian
Oct 07, 2011 : Should I use a storage engine other than MyISAM to optimise these tables or should I get better disks?
Sep 20, 2011 : Multi cores and MySQL Performance
Sep 12, 2011 : Possible to make MySQL use more than one core?

Sam · Accepted Answer · 2013-04-21 19:13:59Z

One thing that I have noticed in your config that might cause you problems:

sort_buffer_size = 512M

When a session has a query that needs to do a sort the entire sort_buffer_size is allocated per session. This means for small queries you are allocating and freeing a lot of memory which can be expensive. If you need a large sort buffer for certain queries you can set it as a session variable. It is best to avoid doing this globally.

Reference: Mysql Manual

Stack Exchange Network

High Mysql Load , over 700% CPU

2 Answers 2

OBSERVATION #1

OBSERVATION #2

OBSERVATION #3 (Optional)

Your Answer

Not the answer you're looking for? Browse other questions tagged
mysql
myisam
load
or ask your own question.

Linked

Hot Network Questions

High Mysql Load , over 700% CPU

2 Answers 2

OBSERVATION #1

OBSERVATION #2

OBSERVATION #3 (Optional)

Your Answer

Sign up or log in

Post as a guest

Not the answer you're looking for? Browse other questions tagged mysqlmyisamload or ask your own question.

Linked

Related

Hot Network Questions

Not the answer you're looking for? Browse other questions tagged
mysql
myisam
load
or ask your own question.