Why is mySQL constantly going into swap?

Question

MySQL

There is an Ubuntu 12.04 server with 8GB of memory. It has mysql 5.5.24-0ubuntu0.12.04.1-log The
config is like this

key_buffer              = 128M
thread_stack            = 256K
thread_cache_size       = 8
myisam-recover         = BACKUP
table_cache            = 8192
table_definition_cache = 8192
max_heap_table_size    = 256M
query_cache_limit       = 8M
query_cache_size        = 128M
open_files_limit=10000
innodb_buffer_pool_size = 2GB
innodb_additional_mem_pool_size = 80M
innodb_data_file_path = ibdata1:10M:autoextend
innodb_log_file_size = 256M
innodb_log_buffer_size=64M
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 50
innodb_file_per_table

expire_logs_days        = 10
max_binlog_size         = 100M
log-error = /var/log/mysql/mysqld.err

A few days after the restart, it goes into a swap, although according to the parameters of the config, the memory should be enough with a margin. There are about 1500 small databases running on the server.

Answer 1

Too much! We should try to reduce

table_cache            = 8192
table_definition_cache = 8192

query_cache_limit       = 8M уменьшите до 2M
innodb_buffer_pool_size - желательно ставить по объему страниц
max_heap_table_size    = 256M уменьшите до 64M

and also add

tmp-table-size = 64M

temporary tables - in memory

tmpdir = /dev/shm

Answer 2

InnoDB has its own table directory that it keeps in memory. It contains information about all opened tables, in the standard version of MySQL it is never cleared - hence the increase in memory.
Percona xtradb added the setting innodb_dict_size_limit against this
www.percona.com/doc/percona-server/5.5/management/innodb_dict_size_limit.html

Once a table is opened, it is never removed from the data dictionary unless you drop the table or you restart the server. In some cases, the data dictionary grows extremely large. If this consumes enough memory, the server will begin to use virtual memory. Use of virtual memory can cause swapping, and swapping can cause severe performance degradation. By providing a way to set an upper limit to the amount of memory the data dictionary can occupy, this feature provides users a way to create a more predictable and controllable situation.

Answer 3

Based on the Innodb_buffer_pool_pages_free = 0assumption that should be increased innodb_buffer_pool_size.

Answer 4

So what's your problem? Does MySQL allocate so much memory that the OS is forced to swap out some of it, or does MySQL consume as much as it should, just partly fall out into the swap? In the second case, you have to play with the kernel options for swappiness and things like that. The first is to see why so much memory is being consumed.

Answer 5

Use
mysqltuner.pl/mysqltuner.pl
or equivalents

Answer 6

One out of two.
1. Either the configuration of the memory parameters is too large, as is the load, for all this to fit into the RAM. Ches speaking, I look and think that it is unlikely.
2. Either the memory parameter configuration is too large, and the load for such a configuration is too small. Therefore, the memory is not used and the kernel throws it into swap for optimization.
Situation 2 may arise, for example, if your setting is innodb_buffer_pool_size (2G) and the total size of INNODB tables is 100M.
innodb_buffer_pool_size should be at the level of 130-200% of the planned volume of tables. The rest of the memory simply can not be used.