Re: Innodb_buffer_pool_pages_free = 0

[Douglas Nelson]

hey guys, what I am looking for is information on the interworkins of the Innodb engine and what the engine is doing when it's out of tune. But I'm open for any help I can get. I am making the following changes, and then iterate again once it perks for a day or so. This sysem has 450 tables, 1.4 TB data, 5 to 1 read to write ratio. and dogs out every now and again under load.

innodb_buffer_pool_size 23G - > 32G
max_connections 6500 -> 4000
query_cache_type on -> off
table_open_cache 2048 -> 4096
table_definition_cache 400 -> 600
sort_buffer_size 2M -> 256K
innodb_purge_batch_size 20 -> 300
innodb_old_blocks_time 0 -> 5000
innodb_buffer_pool_instances 1 -> 8
innodb_open_files 300 -> 550

Innodb_buffer_pool_pages_data | 1308078
Innodb_buffer_pool_pages_dirty | 52632
Innodb_buffer_pool_pages_flushed | 279943768
Innodb_buffer_pool_pages_free | 0
Innodb_buffer_pool_pages_misc | 133714
Innodb_buffer_pool_pages_total | 1441792
Innodb_buffer_pool_read_ahead_rnd | 0
Innodb_buffer_pool_read_ahead | 6925104
Innodb_buffer_pool_read_ahead_evicted | 144844167
Innodb_buffer_pool_read_requests | 319023645695
Innodb_buffer_pool_reads | 1077791394
Innodb_buffer_pool_wait_free | 0
Innodb_buffer_pool_write_requests | 3211932333
Innodb_data_fsyncs | 150799276
Innodb_data_pending_fsyncs | 2
Innodb_data_pending_reads | 0
Innodb_data_pending_writes | 0
Innodb_data_read | 17865730478080
Innodb_data_reads | 1081198389
Innodb_data_writes | 238375733
Innodb_data_written | 2815230893568
Innodb_dblwr_pages_written | 0
Innodb_dblwr_writes | 0
Innodb_have_atomic_builtins | ON
Innodb_log_waits | 0
Innodb_log_write_requests | 1102366100
Innodb_log_writes | 139476479
Innodb_os_log_fsyncs | 139816208
Innodb_os_log_pending_fsyncs | 1
Innodb_os_log_pending_writes | 0
Innodb_os_log_written | 521779409408
Innodb_page_size | 16384
Innodb_pages_created | 19336612
Innodb_pages_read | 1090438086
Innodb_pages_written | 139971884
Innodb_row_lock_current_waits | 0
Innodb_row_lock_time | 8817544
Innodb_row_lock_time_avg | 3380
Innodb_row_lock_time_max | 51941
Innodb_row_lock_waits | 2608
Innodb_rows_deleted | 103248679
Innodb_rows_inserted | 226868010
Innodb_rows_read | 92072208318
Innodb_rows_updated | 39079102
Innodb_truncated_status_writes | 16


Thanks Doug