128GB RAM, 32cores Xeon RAID 10 SSD.
Mysql version is the latest 5.5 for Ubuntu 12.04 .
We have a lot of queries - about 1800+ per second. During high MySQL load - 2700+% load on a 32 CPU system - Load is ~35-40 at times.
There is ~25Mb's the private interface from our App servers coming into MySQL/3306 localserver. User% cpu is only about 10-15%. Sys% cpu is around 60-70+
High IO but no IO wait time.
mysqadmin status:
Uptime: 400070 Threads: 289 Questions: 749345910 Slow queries: 41496 Opens: 26347 Flush tables: 1 Open tables: 812 Queries per second avg: 1873.036
Our datadir is large - over 200GB with a table that is 100GB and always writing and reading to and from. All running Innodb.
VMSTAT shows the following:
procs -----------memory---------- ---swap-- -----io---- -system-- ----cpu----
r b swpd free buff cache si so bi bo in cs us sy id wa
34 0 27864 583480 312336 49383856 0 0 23 87 1 1 3 11 86 0
31 0 27864 597048 312344 49373460 0 0 2009 9909 28862 47257 10 69 21 0
30 0 27864 588556 312348 49378836 0 0 558 6382 31442 53686 10 69 21 0
31 0 27864 578380 312368 49390556 0 0 898 8847 32546 55319 11 68 21 0
60 0 27864 588984 312380 49377356 0 0 1566 6759 30165 50549 11 67 22 0
21 0 27864 588464 312388 49375072 0 0 1078 7118 41080 71743 12 61 27 0
29 0 27864 584988 312392 49385136 0 0 1501 8888 33282 56455 12 65 24 0
23 0 27864 590256 312392 49378256 0 0 2189 9056 28318 46490 10 69 21 0
59 1 27864 592996 312384 49375496 0 0 2623 5188 36731 62825 11 62 27 0
33 0 27864 586972 312388 49385004 0 0 1714 6219 31556 52735 11 67 22 0
29 0 27864 593760 312388 49373856 0 0 760 4584 28084 46741 10 71 19 0
top:
Tasks: 243 total, 1 running, 241 sleeping, 1 stopped, 0 zombie
Cpu(s): 3.1%us, 10.9%sy, 0.0%ni, 85.9%id, 0.1%wa, 0.0%hi, 0.1%si, 0.0%st
Mem: 125439176k total, 124847688k used, 591488k free, 312612k buffers
Swap: 1959924k total, 27896k used, 1932028k free, 49374124k cached
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
4804 mysql 20 0 85.4g 68g 4796 S 2522 57.6 30926:01 mysqld
26911 root 20 0 0 0 0 S 2 0.0 0:52.54 kworker/4:1
28040 root 20 0 17468 1392 932 R 2 0.0 0:00.01 top
1 root 20 0 24460 2100 1200 S 0 0.0 0:05.88 init
my.cnf:
[client]
port = 3306
socket = /var/run/mysqld/mysqld.sock
[mysqld_safe]
socket = /var/run/mysqld/mysqld.sock
nice = 0
[mysqld]
skip-name-resolve
innodb_file_per_table
default_storage_engine=InnoDB
user = mysql
socket = /var/run/mysqld/mysqld.sock
port = 3306
basedir = /usr
datadir = /data/mysql
tmpdir = /tmp
skip-external-locking
key_buffer = 512M
max_allowed_packet = 128M
thread_stack = 192K
thread_cache_size = 64
# This replaces the startup script and checks MyISAM tables if needed
# the first time they are touched
myisam-recover = BACKUP
max_connections = 500
table_cache = 812
table_definition_cache = 812
join_buffer_size = 1M
innodb_additional_mem_pool_size = 20M
innodb_buffer_pool_size = 64G
#innodb_file_io_threads = 4
#innodb_thread_concurrency = 12
innodb_flush_log_at_trx_commit = 1
innodb_log_buffer_size = 8M
innodb_log_file_size = 1024M
innodb_log_files_in_group = 2
innodb_max_dirty_pages_pct = 90
innodb_lock_wait_timeout = 360
log_error = /var/log/mysql/error.log
long_query_time = 5
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slowlog.log
We have slow logging enabled and it does report quite a bit. But only basic queries, without joins, etc. Most 'delete from Table1' which is our 100GB table. We have dumped and restored the DB about a week ago so table corruption shouldn't be an issue.
slowlog:
# Query_time: 11.665333 Lock_time: 0.000156 Rows_sent: 0 Rows_examined: 191
SET timestamp=1358198706;
delete from Table1 where UID in (587983003, 587982443, 587983544, 587982446, 587982706, 587983781, 587982992, 587982710, 587983536, 587982439, 587984071, 587982460, 587983261, 587982702, 587982463, 587982689, 587982695, 587983794, 587983523, 587982981, 587983758, 587983517, 587983276, 587983038, 587983277, 460062786, 587983028, 587982678, 587983746, 587983291, 587982664, 587983503, 587982430, 587983022, 587982670, 587983495, 587982658, 587983014, 587982382, 587982377, 587983855, 587982651, 587983307, 587983601, 460062757, 587983842, 587984026, 587983061, 587982642, 587983604, 587983299, 587982396, 587984003, 460062782, 587983326, 587983595, 587982635, 587983050, 460062777, 587984011, 587982390, 587982629, 460062775, 587983318, 587984013, 587983041, 587982620, 587982351, 587983100, 460062732, 587983820, 587983339, 460062729, 587983098, 460062727, 587982612, 587982343, 587983812, 587982366, 460062751, 587983357, 587982607, 587982600, 587983567, 587984038, 587983083, 587983076, 587982359, 587983348, 587982593, 587983558, 587983344, 587983966, 587982560, 587983413, 587983653, 587982832, 587983106, 587982566, 587982290, 587983648, 587982869, 587983113, 587982842, 587982569, 587982840, 587982302, 460062620, 587983955, 587982296, 587982877, 587982849, 587983122, 587982817, 460062596, 587983664, 587982582, 587982284, 587983129, 587983679, 587982824, 587982586, 587983401, 587982862, 587982861, 587982531, 587982803, 460062640, 587983381, 587982323, 587983141, 587982902, 587983988, 587983391, 460062648, 587982542, 587982331, 587982910, 587983627, 587983149, 587983983, 587982309, 587982882, 460062629, 587982318, 587983973, 587983160, 587982552, 587982317, 587982892, 587982799, 587983368, 587982773, 587982502, 587982933, 587983717, 460062685, ........
Would a combination of very high (private) network traffic continuously hitting the MySQL server, along with very large tables both inserting,reading and deleting at a rate of 10-20MB/s reading and writing according to iotop causing the %sys CPU to be so high?
