Hi
On http://capeinfo.com I have a time to First Byte of 2.309 secs. I’ve managed to get it down from nearly 4 secs!
I have a dedicated server at one of South Africa’s major hosting companies and asked them how the server can be improved. They ran a MySQL tuner which gave the following results:
-------- Storage Engine Statistics -------------------------------------------
[–] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MRG_MYISAM [–] Data in MyISAM tables: 1G (Tables: 2327) [–] Data in InnoDB tables: 954M (Tables: 2109) [–] Data in MEMORY tables: 0B (Tables: 21) [!!] Total fragmented tables: 2268
-------- Performance Metrics -------------------------------------------------
[–] Up for: 14d 20h 34m 18s (40M q [31.888 qps], 456K conn, TX: 287G, RX: 20G) [–] Reads / Writes: 83% / 17% [–] Binary logging is disabled [–] Total buffers: 192.0M global + 2.7M per thread (151 max threads) [OK] Maximum reached memory usage: 245.8M (3.14% of installed RAM) [OK] Maximum possible memory usage: 597.8M (7.63% of installed RAM) [OK] Slow queries: 0% (17/40M) [OK] Highest usage of available connections: 13% (20/151) [OK] Aborted connections: 0.20% (899/456254) [OK] Query cache efficiency: 76.1% (26M cached / 35M selects) [!!] Query cache prunes per day: 320480 [OK] Sorts requiring temporary tables: 0% (0 temp sorts / 1M sorts) [!!] Joins performed without indexes: 325517 [!!] Temporary tables created on disk: 33% (702K on disk / 2M total) [OK] Thread cache hit rate: 99% (477 created / 456K connections) [!!] Table cache hit rate: 0% (400 open / 302K opened) [OK] Open file limit used: 43% (449/1K) [OK] Table locks acquired immediately: 99% (18M immediate / 18M locks)
-------- MyISAM Metrics ------------------------------------------------------
[!!] Key buffer used: 36.4% (6M used / 16M cache) [OK] Key buffer size / total MyISAM indexes: 16.0M/185.0M [OK] Read Key buffer hit rate: 100.0% (1B cached / 382K reads) [!!] Write Key buffer hit rate: 82.1% (1M cached / 348K writes)
-------- InnoDB Metrics ------------------------------------------------------
[–] InnoDB is enabled.
[!!] InnoDB buffer pool / data size: 128.0M/954.0M [OK] InnoDB buffer pool instances: 1 [OK] InnoDB Used buffer: 99.99% (8190 used/ 8191 total) [OK] InnoDB Read buffer efficiency: 99.97% (3188607019 hits/ 3189484587 total) [OK] InnoDB Write log efficiency: 95.51% (18800919 hits/ 19684325 total) [OK] InnoDB log waits: 0.00% (0 waits / 883406 writes)
-------- Recommendations -----------------------------------------------------
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
Enable the slow query log to troubleshoot bad queries
Adjust your join queries to always utilize indexes
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: table_cache negative scalability - Percona Database Performance Blog
Beware that open_files_limit (1024) variable
should be greater than table_open_cache ( 400) Variables to adjust:
query_cache_size (> 16M)
join_buffer_size (> 128.0K, or always use indexes with joins)
tmp_table_size (> 16M)
max_heap_table_size (> 16M)
table_open_cache (> 400)
innodb_buffer_pool_size (>= 954M) if possible.
I optimised the tables, which is the limit of my abilities. Everything else in the report is Greek to me!
They have offered to make customisations I require.
What should I be asking for?
Thanks
Carl