Mysql – Finding swap causes of MySQL

buffer-poolinnodbmemoryMySQL

In my centos 6.3 server I have a MySQL 5.5.33 database.
It has 17 tables (15 InnoDB, 2 MyISAM) and total records 6.7M rows.
I refactored my schemas and added indexes for my slow logs. My average query time is 20-30 ms. And my database performs well.

But I have some cron queries that runs every 3 hours. They don't use any index, they runs very slow and every query runs nearly 1500-2000 ms. I don't plan to add new indexes for them, because in that case I have to add many indexes and that queries run very rare.

When I restart my database server, -normally- swap is zero. After some time swapping becomes large gradually. After 13 days, I get 650MB swap of MySQL. I want to find what causes this swapping and try to reduce the swap without performance grade.

I want to be sure that the cause is cron queries or some other thing causes this swap size.

My top results: 

top - 13:33:01 up 13 days, 11:04,  1 user,  load average: 0.77, 1.02, 1.07
Tasks: 148 total,   1 running, 147 sleeping,   0 stopped,   0 zombie
Cpu(s): 27.4%us,  5.3%sy,  0.0%ni, 59.1%id,  7.8%wa,  0.0%hi,  0.3%si,  0.0%st
Mem:   1020564k total,   854184k used,   166380k free,    73040k buffers
Swap:  2097144k total,   643036k used,  1454108k free,    94000k cached

  PID USER        PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  SWAP COMMAND
 9573 mysql       20   0 2336m 328m 3668 S  7.3 33.0 349:14.25 554m mysqld
15347 examplecom  20   0  219m  32m  10m S  2.7  3.2   0:02.66    0 php-cgi
15343 examplecom  20   0  215m  28m  10m S 10.0  2.9   0:05.80    0 php-cgi
15348 examplecom  20   0  215m  28m  10m S 12.3  2.8   0:03.62    0 php-cgi
15346 examplecom  20   0  215m  28m  10m S  9.6  2.8   0:06.39    0 php-cgi
15350 examplecom  20   0  212m  25m  10m S 10.0  2.6   0:02.19    0 php-cgi
15345 examplecom  20   0  211m  24m  10m S  6.6  2.5   0:04.28    0 php-cgi
15349 examplecom  20   0  209m  22m  10m S  5.3  2.2   0:02.66    0 php-cgi
12771 apache      20   0  334m 5304 2396 S  0.0  0.5   0:02.53  10m httpd
12763 apache      20   0  335m 5224 2232 S  0.3  0.5   0:02.33  11m httpd

Edit: I restarted mysql server 2 days ago, so swap is low now. But with time passes it again will become bigger. When I make top I get this now:

top - 23:30:46 up 15 days, 21:01,  1 user,  load average: 0.35, 0.42, 0.42
Mem:   1020564k total,   931052k used,    89512k free,    76412k buffers
Swap:  2097144k total,   280528k used,  1816616k free,   233560k cached

  PID USER        PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  SWAP COMMAND
23088 mysql       20   0 1922m 311m 3440 S  1.0 31.3  50:04.53 143m mysqld
10081 examplecom  20   0  216m  28m  10m S  4.0  2.8   0:01.67    0 php-cgi
10069 examplecom  20   0  215m  27m  10m S  3.3  2.7   0:04.81    0 php-cgi
10070 examplecom  20   0  215m  26m  10m S  8.3  2.7   0:04.75    0 php-cgi
10062 examplecom  20   0  215m  26m  10m S  6.0  2.7   0:06.26    0 php-cgi
10060 examplecom  20   0  214m  25m  10m S  5.3  2.6   0:07.51    0 php-cgi
10074 examplecom  20   0  214m  25m  10m S  6.6  2.6   0:03.01    0 php-cgi
10080 examplecom  20   0  212m  23m  10m S  6.0  2.4   0:01.58    0 php-cgi

When I make free -m I get this:

             total       used       free     shared    buffers     cached
Mem:           996        927         68          0         76        219
-/+ buffers/cache:        631        364
Swap:         2047        273       1774

My /etc/my.cnf file contents:

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
symbolic-links=0
skip-name-resolve

slow_query_log=ON
long_query_time=1.2

innodb_file_per_table

max_allowed_packet=32M
thread_stack=256K
max_allowed_packet=32M
thread_stack=256K
max_connect_errors=100000000
max_connections=600

key_buffer=256M
sort_buffer_size=2M
read_buffer_size=2M
read_rnd_buffer_size=2M

thread_cache_size = 8
tmp_table_size=128M
max_heap_table_size=128M
query_cache_size = 209715200
query_cache_limit = 52428800
join_buffer_size=4M
table_cache=2400
low_priority_updates=1
tmpdir = /var/tmp

query_cache_type = 1

innodb_buffer_pool_size=256M
innodb_additional_mem_pool_size=512K
innodb_log_buffer_size=500K
innodb_thread_concurrency=8

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

Best Answer

You seem to over-allocate memory. You allow relatively large number of connections (600) along with larger session buffers (such as tmp_table_size=128M, max_heap_table_size=128M). So in worst case, when all the 600 parallel sessions use up the allowed 128M for MEMORY engine temporary tables, you need 600x128M= 77G memory. But even with a sort_buffer_size=2M there is a need for 1.2GB, however you have 1GB only.

Having swap used is not a surprise this case. Check status variable Max_used_connections to see if use up to 600 connections, decrease the max_connections variable. Check status vars Created_tmp_disk_tables and Created_tmp_tables, and you might safely decrease the tmp_table_size until Created_tmp_disk_tables is not increasing dramatically.

Also check whether kernel option vm.swappiness is not too high (such as 100), so that Linux starts swapping earlier, and more aggressively if getting out of memory

Related Question