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). [OK] InnoDB Used buffer: 87.50% (7167 used/ 8191 total) [OK] InnoDB Read buffer efficiency: 99.97% (88883891 hits/ 88912014 total) [OK] InnoDB Write log efficiency: 96.95% (869671 hits/ 897014 total) [OK] InnoDB log waits: 0.00% (0 waits / 27343 writes) -------- ThreadPool Metrics -------------------------------------------------- [--] ThreadPool stat is disabled. -------- AriaDB Metrics ------------------------------------------------------ [--] AriaDB is disabled. -------- TokuDB Metrics ------------------------------------------------------ [--] TokuDB is disabled. -------- Galera Metrics ------------------------------------------------------ [--] Galera is disabled. -------- Replication Metrics ------------------------------------------------- [--] No replication slave(s) for this server. [--] 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.