Finding swap causes of MySQL

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

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

StackExchange Database Administrators Q#48659, answer score: 3