How large should be mysql innodb_buffer_pool_size?

First – run mysqltuner:

]# ./mysqltuner.pl
 >>  MySQLTuner 1.6.4 - Major Hayden <major@mhtx.net>
 >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
 >>  Run with '--help' for additional options and output filtering
Please enter your MySQL administrative login: root
Please enter your MySQL administrative password:
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.6.29-log
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MRG_MYISAM
[--] Data in MyISAM tables: 445K (Tables: 9)
[--] Data in InnoDB tables: 204M (Tables: 13)
[!!] Total fragmented tables: 3

-------- Security Recommendations  -------------------------------------------
[OK] There are no anonymous accounts for any database users
[OK] All database users have passwords assigned
[!!] User 'sysadmin@%' hasn't specific host restriction.
[!!] There is no basic password file list!

-------- CVE Security Recommendations  ---------------------------------------
[--] Skipped due to --cvefile option undefined

-------- Performance Metrics -------------------------------------------------
[--] Up for: 17d 19h 48m 54s (1M q [0.865 qps], 87K conn, TX: 39B, RX: 258M)
[--] Reads / Writes: 94% / 6%
[--] Binary logging is enabled (GTID MODE: OFF)
[--] Total buffers: 432.0M global + 6.5M per thread (151 max threads)
[OK] Maximum reached memory usage: 913.0M (49.61% of installed RAM)
[OK] Maximum possible memory usage: 1.4G (76.81% of installed RAM)
[OK] Slow queries: 0% (1/1M)
[OK] Highest usage of available connections: 49% (74/151)
[OK] Aborted connections: 2.80%  (2442/87276)
[!!] Query cache is disabled
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 271K sorts)
[!!] Temporary tables created on disk: 71% (80K on disk / 114K total)
[OK] Thread cache hit rate: 94% (4K created / 87K connections)
[!!] Table cache hit rate: 2% (115 open / 5K opened)
[OK] Open file limit used: 9% (99/1K)
[OK] Table locks acquired immediately: 100% (1M immediate / 1M locks)
[OK] Binlog cache memory access: 99.95% ( 52384 Memory / 52412 Total)

-------- MyISAM Metrics ------------------------------------------------------
[!!] Key buffer used: 18.3% (49M used / 268M cache)
[OK] Key buffer size / total MyISAM indexes: 256.0M/291.0K
[OK] Read Key buffer hit rate: 100.0% (9M cached / 510 reads)
[!!] Write Key buffer hit rate: 1.4% (1K cached / 1K writes)

-------- InnoDB Metrics ------------------------------------------------------
[--] InnoDB is enabled.
[!!] InnoDB buffer pool / data size: 128.0M/204.8M
[!!] InnoDB buffer pool <= 1G and innodb_buffer_pool_instances(!=1).
&#91;OK&#93; InnoDB Used buffer: 87.50% (7167 used/ 8191 total)
&#91;OK&#93; InnoDB Read buffer efficiency: 99.97% (88883891 hits/ 88912014 total)
&#91;OK&#93; InnoDB Write log efficiency: 96.95% (869671 hits/ 897014 total)
&#91;OK&#93; InnoDB log waits: 0.00% (0 waits / 27343 writes)

-------- ThreadPool Metrics --------------------------------------------------
&#91;--&#93; ThreadPool stat is disabled.

-------- AriaDB Metrics ------------------------------------------------------
&#91;--&#93; AriaDB is disabled.

-------- TokuDB Metrics ------------------------------------------------------
&#91;--&#93; TokuDB is disabled.

-------- Galera Metrics ------------------------------------------------------
&#91;--&#93; Galera is disabled.

-------- Replication Metrics -------------------------------------------------
&#91;--&#93; No replication slave(s) for this server.
&#91;--&#93; This is a standalone server..

-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    Restrict Host for user@% to user@SpecificDNSorIp
    When making adjustments, make tmp_table_size/max_heap_table_size equal
    Reduce your SELECT DISTINCT queries which have no LIMIT clause
    Increase table_open_cache gradually to avoid file descriptor limits
    Read this before increasing table_open_cache over 64: http://bit.ly/1mi7c4C
    Beware that open_files_limit (1024) variable
    should be greater than table_open_cache ( 256)
Variables to adjust:
    query_cache_type (=1)
    tmp_table_size (> 16M)
    max_heap_table_size (> 16M)
    table_open_cache (> 256)
    innodb_buffer_pool_size (>= 204M) if possible.
    innodb_buffer_pool_instances (=1)

Here is what you should do. First run this query



mysql> SELECT CEILING(Total_InnoDB_Bytes*1.6/POWER(1024,3)) RIBPS FROM
    -> (SELECT SUM(data_length+index_length) Total_InnoDB_Bytes
    -> FROM information_schema.tables WHERE engine='InnoDB') A;
+-------+
| RIBPS |
+-------+
|     1 |
+-------+
1 row in set (0.08 sec)

This will give you the RIBPS, Recommended InnoDB Buffer Pool Size (in GB)based on all InnoDB Data and Indexes with an additional 60%.

After the restart, run mysql for a week or two. Then, run this query:


SELECT (PagesData*PageSize)/POWER(1024,3) DataGB FROM
(SELECT variable_value PagesData
FROM information_schema.global_status
WHERE variable_name='Innodb_buffer_pool_pages_data') A,
(SELECT variable_value PageSize
FROM information_schema.global_status
WHERE variable_name='Innodb_page_size') B;

This will give you how many actual pages of InnoDB data reside in the InnoDB Buffer Pool.

Leave a Comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.