innodb buffer pool size:

mysql> SHOW VARIABLES LIKE '%buffer%';
+-------------------------------------+----------------+
| Variable_name | Value |
+-------------------------------------+----------------+
| bulk_insert_buffer_size | 8388608 |
| innodb_buffer_pool_chunk_size | 55574528 |
| 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_in_core_file | ON |
| innodb_buffer_pool_instances | 1 |
| innodb_buffer_pool_load_abort | OFF |
| innodb_buffer_pool_load_at_startup | ON |
| innodb_buffer_pool_load_now | OFF |
| innodb_buffer_pool_size | 55574528 |
| innodb_change_buffer_max_size | 25 |
| innodb_change_buffering | all |
| innodb_log_buffer_size | 16777216 |
| innodb_sort_buffer_size | 1048576 |
| join_buffer_size | 262144 |
| key_buffer_size | 8388608 |
| myisam_sort_buffer_size | 8388608 |
| net_buffer_length | 16384 |
| preload_buffer_size | 32768 |
| read_buffer_size | 131072 |
| read_rnd_buffer_size | 262144 |
| sort_buffer_size | 262144 |
| sql_buffer_result | OFF |
+-------------------------------------+----------------+
25 rows in set (0.02 sec)
I
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
Buffer pool reads / Read requests
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
innodb_additional_mem_pool_size removed in MySQL 5.7.
#
https://severalnines.com/database-blog/mysql-performance-cheat-sheet