3

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).

  • Set swappiness = 1 (or higher -- up to 10, do not use 0) and review the overcommit variables. Actually, you are not plenty of memory as free is only 734 MB free and only 678 MB of the total usage are cached. So, probably you are not plenty as you think or your db activity is higher than you expected :) – 3manuek May 17 '16 at 15:14
  • Did you change other settings? For example, query_cache_size should be either 0 or no more than 50M. – Rick James May 27 '16 at 20:38
  • If you would like more analysis, please provide SHOW VARIABLES; and SHOW GLOBAL STATUS; (perhaps via a postit via). – Rick James May 27 '16 at 20:39
  • Are other applications running on the same server? It looks like the buffer_pool is about 80% of RAM; recommend 70% of available RAM. – Rick James May 27 '16 at 20:41
1

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).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.