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 startedInnodb_buffer_pool_load_status Buffer pool(s) load completed at 200803 12:15:48Innodb_buffer_pool_resize_status Innodb_buffer_pool_pages_data 3087203Innodb_buffer_pool_bytes_data 50580733952Innodb_buffer_pool_pages_dirty 246073Innodb_buffer_pool_bytes_dirty 4031660032Innodb_buffer_pool_pages_flushed 477728287Innodb_buffer_pool_pages_free 8022Innodb_buffer_pool_pages_LRU_flushed 0Innodb_buffer_pool_pages_made_not_young 6136302331Innodb_buffer_pool_pages_made_young 316351852Innodb_buffer_pool_pages_misc 50119Innodb_buffer_pool_pages_old 1139448Innodb_buffer_pool_pages_total 3145344Innodb_buffer_pool_read_ahead_rnd 0Innodb_buffer_pool_read_ahead 19282439Innodb_buffer_pool_read_ahead_evicted 0Innodb_buffer_pool_read_requests 212866385618Innodb_buffer_pool_reads 254595335Innodb_buffer_pool_wait_free 2892Innodb_buffer_pool_write_requests 10311193118
SHOW VARIABLES LIKE ‘innodb_buffer_pool_%’;innodb_buffer_pool_chunk_size 134217728innodb_buffer_pool_dump_at_shutdown ONinnodb_buffer_pool_dump_now OFFinnodb_buffer_pool_dump_pct 25innodb_buffer_pool_filename ib_buffer_poolinnodb_buffer_pool_instances 8innodb_buffer_pool_load_abort OFFinnodb_buffer_pool_load_at_startup ONinnodb_buffer_pool_load_now OFFinnodb_buffer_pool_size 51539607552
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.
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.