MySQL is using swap memory while I have plenty of free RAM

configurationinnodbmemoryMySQL

I have a 6 GB buffer pool size for my database; however, mysqld process is moving to swap while there is plenty of RAM available. I think pool size and pages along with pool of reads and pool of read requests should be tuned to avoid such situation.

Below is my current configuration:

[fuad@damra mysql]$ free -m
         total       used       free     shared    buffers     cached
Mem:          7872       7138        734          0        304        678
-/+ buffers/cache:       6155       1717
Swap:        32767        456      32311


mysql> SHOW GLOBAL STATUS LIKE 'innodb_buffer_pool%';
+---------------------------------------+-------------+
| Variable_name                         | Value       |
+---------------------------------------+-------------+
| Innodb_buffer_pool_dump_status        | not started |
| Innodb_buffer_pool_load_status        | not started |
| Innodb_buffer_pool_pages_data         | 316153      |
| Innodb_buffer_pool_bytes_data         | 5179850752  |
| Innodb_buffer_pool_pages_dirty        | 20          |
| Innodb_buffer_pool_bytes_dirty        | 327680      |
| Innodb_buffer_pool_pages_flushed      | 51645307    |
| Innodb_buffer_pool_pages_free         | 75279       |
| Innodb_buffer_pool_pages_misc         | 1776        |
| Innodb_buffer_pool_pages_total        | 393208      |
| Innodb_buffer_pool_read_ahead_rnd     | 0           |
| Innodb_buffer_pool_read_ahead         | 0           |
| Innodb_buffer_pool_read_ahead_evicted | 0           |
| Innodb_buffer_pool_read_requests      | 26268346860 |
| Innodb_buffer_pool_reads              | 496         |
| Innodb_buffer_pool_wait_free          | 0           |
| Innodb_buffer_pool_write_requests     | 207971337   |
+---------------------------------------+-------------+

mysql> SHOW VARIABLES LIKE 'innodb_buffer_pool_%';
+-------------------------------------+----------------+
| Variable_name                       | Value          |
+-------------------------------------+----------------+
| innodb_buffer_pool_dump_at_shutdown | OFF            |
| innodb_buffer_pool_dump_now         | OFF            |
| innodb_buffer_pool_filename         | ib_buffer_pool |
| innodb_buffer_pool_instances        | 8              |
| innodb_buffer_pool_load_abort       | OFF            |
| innodb_buffer_pool_load_at_startup  | OFF            |
| innodb_buffer_pool_load_now         | OFF            |
| innodb_buffer_pool_size             | 6442450944     |
+-------------------------------------+----------------+

Do I need to tune these values to achieve better results and to not let MySQL switch to swap disk unless RAM is fully utilized?

This is a dedicated database server holding two MySQL schemas. Both schemas are serving the same application (one schema is to store statistics).

I can't change swappiness as this is not an option for the VMware administrator. As for RAM, I have 734+678 (cached memory can be re-used).

I have Java installed on this server to serve the JDBC driver between my application and MySQL.

Red Hat Enterprise Linux Server release 6.7 (Santiago).

Best Answer

Innodb_buffer_pool_pages_free / Innodb_buffer_pool_pages_total shows about 15% free. Given the low number of dirty pages and the very low Innodb_buffer_pool_reads vs Innodb_buffer_pool_reads_requests you probably could trim your buffer_pool size.

Innodb_buffer_pool_pages_free == 0 is perfectly acceptable if you aren't reading from disk frequently (low Innodb_buffer_pool_reads).

Related Solutions

Mysql – Finding swap causes of MySQL

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

MySQL using swap during conversion to InnoDB

To switch of MySQL engine you could :

1 Make a ALTER TABLE myTable ENGINE=InnoDB

2 Make a mysqldump of your table, then edit the CREATE TABLE statement to replace MyISAM by InnoDB and restore the dump in the new table (i called it myTable_InnoDB):

DROP TABLE IF EXISTS `mytable`;
CREATE TABLE `mytable` (
  `id` int(11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `mytable_InnoDB`;
CREATE TABLE `mytable_InnoDB` (
  `id` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Best regards

Best Answer

Related Solutions

Mysql – Finding swap causes of MySQL

MySQL using swap during conversion to InnoDB

Related Question