Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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?

share|improve this question
    
you probably need do more mysqld tweaking and it'be useful if you can provide query that causing this spike.. –  alexus Jan 14 '13 at 21:20
    
I have added the slowlog query. Most of it is 'delete from..' –  Tom Geee Jan 14 '13 at 21:28
    
11% is just not high. You're trying to "solve" something that isn't a problem. –  David Schwartz Jan 15 '13 at 9:15
    
69% more like it. That TOP output is only after the 1st second - I will paste another one as soon as the load spikes again. If you look at the vmstat output - you will see the sys% average 68 - which is the 3rd column from the right. –  Tom Geee Jan 15 '13 at 15:03
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.