MySQL innodb memory allocation & usage calculation

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)

I

 

Innodb_buffer_pool_pages_total Total number of pages in the buffer pool

(Innodb_buffer_pool_pages_total – Innodb_buffer_pool_pages_free)

innodb_page_size equals to buffer pool size.

1. Innodb_buffer_pool_pages_total Total number of pages in the buffer pool
2. Buffer pool utilization >> Ratio of used to total pages in the buffer pool
3. innodb_buffer_pool_read_requests >>>Requests made to the buffer pool
4. Innodb_buffer_pool_reads >> Requests the buffer pool could not fulfill

mysql query cache retired try redis/memcached.

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.

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%’;

SHOW VARIABLES LIKE ‘innodb_buffer_pool_size’;
+————————-+———–+
| Variable_name | Value |
+————————-+———–+
| innodb_buffer_pool_size | 536870912 | //512M
+————————-+———–+
1 row in set (0.12 sec)

MyISAM also allocates buffer for every concurrent threads

max_allowed_packet

mysql> SHOW ENGINE INNODB STATUS\G

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

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