MySQL innodb memory allocation & usage calculation

Table of Contents

innodb buffer pool size:

×
Parameter	MySQL Default	Your Value
key_buffer_size	MB	MB
+ query_cache_size	MB	MB
+ tmp_table_size	MB	MB
+ innodb_buffer_pool_size	MB	MB
+ innodb_additional_mem_pool_size	MB	MB
+ innodb_log_buffer_size	MB	MB
+ max_connections
sort_buffer_size	MB	MB
+ read_buffer_size	MB	MB
+ read_rnd_buffer_size	MB	MB
+ join_buffer_size	MB	MB
+ thread_stack	MB	MB
+ binlog_cache_size	MB	MB
Totals:	576.2 MB	635.2 MB

checking existing or default values in mysql

current allocated memory 1200mb 113.7M ion ram 3.83 ram stack. as per the above MySQL calui

cator allocated memory 684mb but mysql occupying 1138MB when its idle. its missing table open cache

after reducing open files limit mysql 10k to 2k memory reduced to 50% . mysql high memory usage.

my.cnf settings mysql configuration file

key_buffer_size = 0
innodb_buffer_pool_size = 256M

open_files_limit = 2000 # would be reasonable (these should be on memory rather thsn disk)
table_open_cache = 100 #2x of max connection or 2x of tables // no need less busy mysql server lests say 19 max used.

tmp_table_size = 32M #defaaulut 16MB
max_heap_table_size = 32M
thread_cache_size = 3

caches the data of tables & indexes in memory rather than disk so faster connection handling.

increased performance by reducing response time.

80% memory in dedicated, (more at mysql memory)

30-40% in LEMP*(check mysql connections /se before do this other wise memory will be wasted. You can measure /view buffer pool cache usage).

larger than database size (2x*)

1gb database ,

2core 8gb ram.

allocating 2gb good idea even 1gb instead of 128M default.

innodb_log_file_size = 256M # 25% of buffer pool size

innodb_buffer_pool_instances (=1)

innodb_buffer_pool_size = innodb_buffer_pool_chunk_size

* innodb_buffer_pool_instances

(It’s automatically calculated)

128M, set to 4G, then instances will increase.

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)

Buffer pool utilization

Innodb_buffer_pool_pages_total Total number of pages in the buffer pool

(Innodb_buffer_pool_pages_total – Innodb_buffer_pool_pages_free)

SHOW GLOBAL STATUS LIKE ‘Innodb_buffer_pool_pages%’;

+———————————-+——–+

| Variable_name | Value |

+———————————-+——–+

| Innodb_buffer_pool_pages_data | 40136 |

| Innodb_buffer_pool_pages_dirty | 41 |

| Innodb_buffer_pool_pages_flushed | 4218 |

| Innodb_buffer_pool_pages_free | 90032 |

| Innodb_buffer_pool_pages_misc | 888 |

| Innodb_buffer_pool_pages_total | 131056 |

+———————————-+——–+

(131056-90032)/131056=0.31302649249

0.31302649249*100=31.30%

(only 30% ram used remaining wasted)

innodb_page_size equals to buffer pool size.

Buffer pool efficiency

nnodb_buffer_pool_reads/innodb_buffer_pool_read_requests*100= 0.001

requests 14871553 |

| Innodb_buffer_pool_reads | 39678

39678/14871553*100=0.26680468408

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

mysql query cache retired try redis/memcached.

redis preferred.

Buffer pool reads / Read requests

Reds = from disk

Reads Request = from memory

innodb_buffer_pool_reads = 91661
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’;

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?

Note: mysql buffer pool only applies to innodb engine, for MYISAM e key_buffer_size

you can check mysql.cnf in /etc/mysql/mysql.cinf or /etc/mysql/conf.d/mysql.cnf (permanent settings)

command line settings automatically overdose if mysql server / service restarts.

first login to mysql server

mysql -u root -p

SHOW GLOBAL STATUS LIKE ‘Innodb_buffer_pool_pages%’;

temporary tables max_heap_table_size

MyISAM also allocates buffer for every concurrent threads

max_allowed_packet

mysql> SHOW ENGINE INNODB STATUS\G

SHOW ENGINE INNODB STATUS;

BUFFER POOL AND MEMORY
———————-
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

mysql> SHOW VARIABLES LIKE ‘%buffer%’;
+————————————-+—————-+
| Variable_name | Value |
+————————————-+—————-+
| bulk_insert_buffer_size | 8388608 |
| 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_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 | 268435456 |
| innodb_change_buffer_max_size | 25 |
| innodb_change_buffering | all |
| innodb_ddl_buffer_size | 1048576 |
| innodb_log_buffer_size | 16777216 |
| innodb_sort_buffer_size | 1048576 |
| join_buffer_size | 262144 |
| key_buffer_size | 16777216 |
| myisam_sort_buffer_size | 8388608 |
| net_buffer_length | 16384 |
| preload_buffer_size | 32768 |
| read_buffer_size | 131072 |
| read_rnd_buffer_size | 262144 |
| select_into_buffer_size | 131072 |
| sort_buffer_size | 262144 |
| sql_buffer_result | OFF |
+————————————-+—————-+

SHOW VARIABLES LIKE ‘%size%’;

mysql> SHOW VARIABLES LIKE ‘%size%’;
+———————————————————-+———————-+
| Variable_name | Value |
+———————————————————-+———————-+
| binlog_cache_size | 32768 |
| binlog_row_event_max_size | 8192 |
| binlog_stmt_cache_size | 32768 |
| binlog_transaction_dependency_history_size | 25000 |
| bulk_insert_buffer_size | 8388608 |
| connection_memory_chunk_size | 8912 |
| delayed_queue_size | 1000 |
| histogram_generation_max_mem_size | 20000000 |
| host_cache_size | 279 |
| innodb_buffer_pool_chunk_size | 134217728 |
| innodb_buffer_pool_size | 268435456 |
| innodb_change_buffer_max_size | 25 |
| innodb_ddl_buffer_size | 1048576 |
| innodb_doublewrite_batch_size | 0 |
| innodb_ft_cache_size | 8000000 |
| innodb_ft_max_token_size | 84 |
| innodb_ft_min_token_size | 3 |
| innodb_ft_total_cache_size | 640000000 |
| innodb_log_buffer_size | 16777216 |
| innodb_log_file_size | 50331648 |
| innodb_log_write_ahead_size | 8192 |
| innodb_max_undo_log_size | 1073741824 |
| innodb_online_alter_log_max_size | 134217728 |
| innodb_page_size | 16384 |
| innodb_purge_batch_size | 300 |
| innodb_sort_buffer_size | 1048576 |
| innodb_sync_array_size | 1 |
| join_buffer_size | 262144 |
| key_buffer_size | 16777216 |
| key_cache_block_size | 1024 |
| large_page_size | 0 |
| max_binlog_cache_size | 18446744073709547520 |
| max_binlog_size | 104857600 |
| max_binlog_stmt_cache_size | 18446744073709547520 |
| max_heap_table_size | 33554432 |
| max_join_size | 18446744073709551615 |
| max_relay_log_size | 0 |
| myisam_data_pointer_size | 6 |
| myisam_max_sort_file_size | 9223372036853727232 |
| myisam_mmap_size | 18446744073709551615 |
| myisam_sort_buffer_size | 8388608 |
| ngram_token_size | 2 |
| optimizer_trace_max_mem_size | 1048576 |
| parser_max_mem_size | 18446744073709551615 |
| performance_schema_accounts_size | -1 |
| performance_schema_digests_size | 5000 |
| performance_schema_error_size | 5288 |
| performance_schema_events_stages_history_long_size | 1000 |
| performance_schema_events_stages_history_size | 10 |
| performance_schema_events_statements_history_long_size | 1000 |
| performance_schema_events_statements_history_size | 10 |
| performance_schema_events_transactions_history_long_size | 1000 |
| performance_schema_events_transactions_history_size | 10 |
| performance_schema_events_waits_history_long_size | 1000 |
| performance_schema_events_waits_history_size | 10 |
| performance_schema_hosts_size | -1 |
| performance_schema_session_connect_attrs_size | 512 |
| performance_schema_setup_actors_size | -1 |
| performance_schema_setup_objects_size | -1 |
| performance_schema_users_size | -1 |
| preload_buffer_size | 32768 |
| profiling_history_size | 15 |
| query_alloc_block_size | 8192 |
| query_prealloc_size | 8192 |
| range_alloc_block_size | 4096 |
| range_optimizer_max_mem_size | 8388608 |
| read_buffer_size | 131072 |
| read_rnd_buffer_size | 262144 |
| replica_pending_jobs_size_max | 134217728 |
| rpl_read_size | 8192 |
| select_into_buffer_size | 131072 |
| slave_pending_jobs_size_max | 134217728 |
| sort_buffer_size | 262144 |
| thread_cache_size | 3 |
| tmp_table_size | 33554432 |
| transaction_alloc_block_size | 8192 |
| transaction_prealloc_size | 4096 |
+———————————————————-+———————-+
77 rows in set (0.00 sec)

mysql> SHOW VARIABLES LIKE ‘%innodb_additional_mem_pool_size%’;

mysql> SHOW VARIABLES LIKE ‘%innodb_additional_mem_pool_size%’;
Empty set (0.00 sec)

Note
innodb_use_sys_malloc and innodb_additional_mem_pool_size were deprecated in MySQL 5.6 and removed in MySQL 5.7.

query cacheremoved.