innodb buffer pool size:

Buffer pool utilization
-
Innodb_buffer_pool_pages_total Total number of pages in the buffer pool
-
Buffer pool utilization >> Ratio of used to total pages in the buffer pool
-
innodb_buffer_pool_read_requests >>>Requests made to the buffer pool
-
Innodb_buffer_pool_reads >> Requests the buffer pool could not fulfill
innodb_buffer_pool_read_requests = 4029033624
Performance = 91661 / 4029033624 * 100
InnoDB Performance = 0.0022750120389663. 100% from innodb buffer pool.
SHOW GLOBAL STATUS LIKE ‘innodb_buffer_pool_reads’;
mysql> SHOW GLOBAL STATUS LIKE ‘innodb_buffer_pool_reads’;
+————————–+——-+
| Variable_name | Value |
+————————–+——-+
| Innodb_buffer_pool_reads | 9037 |
+————————–+——-+
1 row in set (0.01 sec)
SHOW GLOBAL STATUS LIKE ‘innodb_buffer_pool_read_requests’;
SHOW GLOBAL STATUS LIKE ‘innodb_buffer_pool_read_requests’;
+———————————-+———–+
| Variable_name | Value |
+———————————-+———–+
| Innodb_buffer_pool_read_requests | 267939981 |
+———————————-+———–
SHOW GLOBAL STATUS LIKE ‘Innodb_buffer_pool_pages%’;
mysql bufferpool utilization total vs free pages
SHOW GLOBAL STATUS LIKE ‘Innodb_buffer_pool_pages%’;
+———————————-+———+
| Variable_name | Value |
+———————————-+———+
| Innodb_buffer_pool_pages_data | 11172 |
| Innodb_buffer_pool_pages_dirty | 0 |
| Innodb_buffer_pool_pages_flushed | 1074057 |
| Innodb_buffer_pool_pages_free | 21363 |
| Innodb_buffer_pool_pages_misc | 233 |
| Innodb_buffer_pool_pages_total | 32768 |
+———————————-+———+
6 rows in set (0.01 sec)
512MB – only 50% utilized 256 MB.
How to check mysql bufferpool memory usage/ allocation?
+————————-+———–+
| Variable_name | Value |
+————————-+———–+
| innodb_buffer_pool_size | 536870912 | //512M
+————————-+———–+
1 row in set (0.12 sec)
———————-
Total large memory allocated 549453824
Dictionary memory allocated 4098788
Buffer pool size 32768
Free buffers 21368
Database pages 11167
Old database pages 4102
mysql performance metrics
sort_buffer_size
read_buffer_size
read_rnd_buffer_size
join_buffer_size
max_heap_table_size
tmp_table_size
table_open_cache
table_open_cache_instances
table_definition_cache
max_allowed_packet
max_connections
thread_cache_size
query_cache_size //disabled
innodb_buffer_pool_size
innodb_buffer_pool_instances
innodb_log_file_size
innodb_log_buffer_size
innodb_flush_log_at_trx_commit
innodb_thread_concurrency 2x for cpu cores+disk
innodb_flush_method
innodb_file_per_table
innodb_stats_on_metadata
innodb_io_capacity
innodb_write_io_threads
innodb_adaptive_flushing
innodb_dedicated_server – automatic variables on dedidicated
myisam key_buffer_size
1% of RAM or 256 MiB because its now innodb.
slow_query_log
long_query_time
sync_binlog
Dump/Restore Buffer Pool
#
https://severalnines.com/database-blog/mysql-performance-cheat-sheet