PERF.67 Server: MySQL settings versus the data on the box: a full 1 GB buffer pool for 5.2 GB of data, table cache overflowing, slow log off

Measured: innodb_buffer_pool_size 1 GB with 1,024 free pages and wait_free 74,540 against 5.2 GB of data plus index across 56 schemas (cadcraft_db 1.23 GB; the two analytics tables alone are 1 GB); table_open_cache 4000 full with 110,482 overflows; Sort_merge_passes 1.1 M with a 256 KB sort buffer; innodb_io_capacity 200 on an SSD; slow_query_log OFF and long_query_time 10 (7 hits in 12 days; nothing below it is known). Also from the SQL track: 64,469 COMMITs at 2.76 ms (innodb_flush_log_at_trx_commit = 1, every autocommit an fsync) and SET time_zone run 1.28 M times = one per connection (no persistent connections). Changes: innodb_buffer_pool_size 4G (SET GLOBAL plus mysqld.cnf), table_open_cache 8000, sort_buffer_size 1M, innodb_io_capacity 1000 / max 4000, slow_query_log ON with long_query_time 0.5 and log_queries_not_using_indexes capped, init_connect = "SET time_zone='+00:00'" if persistent connections are wanted; flush_log_at_trx_commit 2 is a server-wide trade (a second of rows lost on a crash) the other sites would share: the analytics row removes most of the fsyncs anyway. Healthy: pool hit rate 99.9 %, 2 disk temp tables of 3.6 M, redo 430 MB a day, max_used_connections 44 of 151.