{"id":3122,"date":"2016-04-15T16:49:58","date_gmt":"2016-04-15T16:49:58","guid":{"rendered":"https:\/\/qbytes.cloud\/?p=3122"},"modified":"2016-04-15T16:49:58","modified_gmt":"2016-04-15T16:49:58","slug":"large-mysql-innodb_buffer_pool_size","status":"publish","type":"post","link":"https:\/\/www.qbytes.cloud\/index.php\/2016\/04\/15\/large-mysql-innodb_buffer_pool_size\/","title":{"rendered":"How large should be mysql innodb_buffer_pool_size?"},"content":{"rendered":"

First – run mysqltuner:<\/p>\n

\n]# .\/mysqltuner.pl\n >>  MySQLTuner 1.6.4 - Major Hayden <major@mhtx.net>\n >>  Bug reports, feature requests, and downloads at http:\/\/mysqltuner.com\/\n >>  Run with '--help' for additional options and output filtering\nPlease enter your MySQL administrative login: root\nPlease enter your MySQL administrative password:\n[--] Skipped version check for MySQLTuner script\n[OK] Currently running supported MySQL version 5.6.29-log\n[OK] Operating on 64-bit architecture\n\n-------- Storage Engine Statistics -------------------------------------------\n[--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MRG_MYISAM\n[--] Data in MyISAM tables: 445K (Tables: 9)\n[--] Data in InnoDB tables: 204M (Tables: 13)\n[!!] Total fragmented tables: 3\n\n-------- Security Recommendations  -------------------------------------------\n[OK] There are no anonymous accounts for any database users\n[OK] All database users have passwords assigned\n[!!] User 'sysadmin@%' hasn't specific host restriction.\n[!!] There is no basic password file list!\n\n-------- CVE Security Recommendations  ---------------------------------------\n[--] Skipped due to --cvefile option undefined\n\n-------- Performance Metrics -------------------------------------------------\n[--] Up for: 17d 19h 48m 54s (1M q [0.865 qps], 87K conn, TX: 39B, RX: 258M)\n[--] Reads \/ Writes: 94% \/ 6%\n[--] Binary logging is enabled (GTID MODE: OFF)\n[--] Total buffers: 432.0M global + 6.5M per thread (151 max threads)\n[OK] Maximum reached memory usage: 913.0M (49.61% of installed RAM)\n[OK] Maximum possible memory usage: 1.4G (76.81% of installed RAM)\n[OK] Slow queries: 0% (1\/1M)\n[OK] Highest usage of available connections: 49% (74\/151)\n[OK] Aborted connections: 2.80%  (2442\/87276)\n[!!] Query cache is disabled\n[OK] Sorts requiring temporary tables: 0% (0 temp sorts \/ 271K sorts)\n[!!] Temporary tables created on disk: 71% (80K on disk \/ 114K total)\n[OK] Thread cache hit rate: 94% (4K created \/ 87K connections)\n[!!] Table cache hit rate: 2% (115 open \/ 5K opened)\n[OK] Open file limit used: 9% (99\/1K)\n[OK] Table locks acquired immediately: 100% (1M immediate \/ 1M locks)\n[OK] Binlog cache memory access: 99.95% ( 52384 Memory \/ 52412 Total)\n\n-------- MyISAM Metrics ------------------------------------------------------\n[!!] Key buffer used: 18.3% (49M used \/ 268M cache)\n[OK] Key buffer size \/ total MyISAM indexes: 256.0M\/291.0K\n[OK] Read Key buffer hit rate: 100.0% (9M cached \/ 510 reads)\n[!!] Write Key buffer hit rate: 1.4% (1K cached \/ 1K writes)\n\n-------- InnoDB Metrics ------------------------------------------------------\n[--] InnoDB is enabled.\n[!!] InnoDB buffer pool \/ data size: 128.0M\/204.8M\n[!!] InnoDB buffer pool <= 1G and innodb_buffer_pool_instances(!=1).\n&#91;OK&#93; InnoDB Used buffer: 87.50% (7167 used\/ 8191 total)\n&#91;OK&#93; InnoDB Read buffer efficiency: 99.97% (88883891 hits\/ 88912014 total)\n&#91;OK&#93; InnoDB Write log efficiency: 96.95% (869671 hits\/ 897014 total)\n&#91;OK&#93; InnoDB log waits: 0.00% (0 waits \/ 27343 writes)\n\n-------- ThreadPool Metrics --------------------------------------------------\n&#91;--&#93; ThreadPool stat is disabled.\n\n-------- AriaDB Metrics ------------------------------------------------------\n&#91;--&#93; AriaDB is disabled.\n\n-------- TokuDB Metrics ------------------------------------------------------\n&#91;--&#93; TokuDB is disabled.\n\n-------- Galera Metrics ------------------------------------------------------\n&#91;--&#93; Galera is disabled.\n\n-------- Replication Metrics -------------------------------------------------\n&#91;--&#93; No replication slave(s) for this server.\n&#91;--&#93; This is a standalone server..\n\n-------- Recommendations -----------------------------------------------------\nGeneral recommendations:\n    Run OPTIMIZE TABLE to defragment tables for better performance\n    Restrict Host for user@% to user@SpecificDNSorIp\n    When making adjustments, make tmp_table_size\/max_heap_table_size equal\n    Reduce your SELECT DISTINCT queries which have no LIMIT clause\n    Increase table_open_cache gradually to avoid file descriptor limits\n    Read this before increasing table_open_cache over 64: http:\/\/bit.ly\/1mi7c4C\n    Beware that open_files_limit (1024) variable\n    should be greater than table_open_cache ( 256)\nVariables to adjust:\n    query_cache_type (=1)\n    tmp_table_size (> 16M)\n    max_heap_table_size (> 16M)\n    table_open_cache (> 256)\n    innodb_buffer_pool_size (>= 204M) if possible.\n    innodb_buffer_pool_instances (=1)\n\n<\/pre>\n
Here is what you should do. First run this query<\/p>\n
\n\n\nmysql> SELECT CEILING(Total_InnoDB_Bytes*1.6\/POWER(1024,3)) RIBPS FROM\n    -> (SELECT SUM(data_length+index_length) Total_InnoDB_Bytes\n    -> FROM information_schema.tables WHERE engine='InnoDB') A;\n+-------+\n| RIBPS |\n+-------+\n|     1 |\n+-------+\n1 row in set (0.08 sec)\n\n<\/pre>\n
This will give you the RIBPS, Recommended InnoDB Buffer Pool Size (in GB)based on all InnoDB Data and Indexes with an additional 60%.<\/p>\n
After the restart, run mysql for a week or two. Then, run this query:<\/p>\n
\n\nSELECT (PagesData*PageSize)\/POWER(1024,3) DataGB FROM\n(SELECT variable_value PagesData\nFROM information_schema.global_status\nWHERE variable_name='Innodb_buffer_pool_pages_data') A,\n(SELECT variable_value PageSize\nFROM information_schema.global_status\nWHERE variable_name='Innodb_page_size') B;\n\n<\/pre>\n
This will give you how many actual pages of InnoDB data reside in the InnoDB Buffer Pool.<\/p>\n","protected":false},"excerpt":{"rendered":"
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 … Read more<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[70],"tags":[],"class_list":["post-3122","post","type-post","status-publish","format-standard","hentry","category-mysql"],"_links":{"self":[{"href":"https:\/\/www.qbytes.cloud\/index.php\/wp-json\/wp\/v2\/posts\/3122","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.qbytes.cloud\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.qbytes.cloud\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.qbytes.cloud\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.qbytes.cloud\/index.php\/wp-json\/wp\/v2\/comments?post=3122"}],"version-history":[{"count":0,"href":"https:\/\/www.qbytes.cloud\/index.php\/wp-json\/wp\/v2\/posts\/3122\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.qbytes.cloud\/index.php\/wp-json\/wp\/v2\/media?parent=3122"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.qbytes.cloud\/index.php\/wp-json\/wp\/v2\/categories?post=3122"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.qbytes.cloud\/index.php\/wp-json\/wp\/v2\/tags?post=3122"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}