Not the answer you need?
Register and ask your own question!

Mysql database swapping issue

RkotRkot Current User Role Contributor
Hi
I am noticing swapping issue for our MYSQL 5.7 database occasionally. I would like to determine if i over allocated the memory and should reduce some amount of memory from the buffer pool and give it back to the system . Following is the configuration and the status of the variables .

SHOW GLOBAL STATUS LIKE 'innodb_buffer_pool%';
Innodb_buffer_pool_dump_status Dumping of buffer pool not started
Innodb_buffer_pool_load_status Buffer pool(s) load completed at 200803 12:15:48
Innodb_buffer_pool_resize_status
Innodb_buffer_pool_pages_data 3087203
Innodb_buffer_pool_bytes_data 50580733952
Innodb_buffer_pool_pages_dirty 246073
Innodb_buffer_pool_bytes_dirty 4031660032
Innodb_buffer_pool_pages_flushed 477728287
Innodb_buffer_pool_pages_free 8022
Innodb_buffer_pool_pages_LRU_flushed 0
Innodb_buffer_pool_pages_made_not_young 6136302331
Innodb_buffer_pool_pages_made_young 316351852
Innodb_buffer_pool_pages_misc 50119
Innodb_buffer_pool_pages_old 1139448
Innodb_buffer_pool_pages_total 3145344
Innodb_buffer_pool_read_ahead_rnd 0
Innodb_buffer_pool_read_ahead 19282439
Innodb_buffer_pool_read_ahead_evicted 0
Innodb_buffer_pool_read_requests 212866385618
Innodb_buffer_pool_reads 254595335
Innodb_buffer_pool_wait_free 2892
Innodb_buffer_pool_write_requests 10311193118

SHOW VARIABLES LIKE 'innodb_buffer_pool_%';
innodb_buffer_pool_chunk_size 134217728
innodb_buffer_pool_dump_at_shutdown ON
innodb_buffer_pool_dump_now OFF
innodb_buffer_pool_dump_pct 25
innodb_buffer_pool_filename ib_buffer_pool
innodb_buffer_pool_instances 8
innodb_buffer_pool_load_abort OFF
innodb_buffer_pool_load_at_startup ON
innodb_buffer_pool_load_now OFF
innodb_buffer_pool_size 51539607552

Answers

  • matthewbmatthewb Senior [email protected] Percona Staff Role
    Well, InnoDB buffer pool is 48G. I don't know how much RAM you have on this machine, but if you only have, say 50G, then InnoDB is configured to use too much. Start out by setting `innodb_buffer_pool_size` to 80% of your system RAM and see if you still swap.
  • firdausfirdaus Current User Role Contributor

    I just added a little regarding the calculation of ʻinnodb_buffer_pool_size`, usually we apply 50% - 60% of the total RAM for the size ʻinnodb_buffer_pool_size` because our assumption is that a normal system server will use about 1% to 5% RAM depending on how many services are running on the server.

Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.