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)
SHOW CREATE TABLE friends\G
4) Please postSHOW CREATE TABLE likes\G
5) Please postSHOW CREATE TABLE facesessions\G
SELECT SUM(index_length) FROM information_schema.tables WHERE engine='MyISAM';