MySQL Tuning advice

[marklcfc]

When you say optimise database, is that the same as optimise tables in phpmyadmin?

My servers been better of late but did noticed this on my last run of mysqltuner.

[OK] Query cache efficiency: 48.2% (21M cached / 44M selects)
[!!] Query cache prunes per day: 1052

Is that high?

[Dmacleo]

very interesting topic.

[marklcfc]

Any thoughts here, servers been up for 20 days and been running well, page loads could be faster though.

[--] Up for: 20d 7h 6m 56s (74M q [42.342 qps], 3M conn, TX: 1553B, RX: 36B)
[--] Reads / Writes: 65% / 35%
[--] Total buffers: 1.4G global + 6.8M per thread (600 max threads)
[OK] Maximum possible memory usage: 5.3G (70% of installed RAM)
[OK] Slow queries: 0% (538/74M)
[OK] Highest usage of available connections: 19% (119/600)
[OK] Key buffer size / total MyISAM indexes: 1.0G/1.4G
[OK] Key buffer hit rate: 100.0% (3B cached / 681K reads)
[OK] Query cache efficiency: 46.4% (23M cached / 51M selects)
[!!] Query cache prunes per day: 2942
[OK] Sorts requiring temporary tables: 1% (4K temp sorts / 328K sorts)
[!!] Temporary tables created on disk: 32% (145K on disk / 441K total)
[OK] Thread cache hit rate: 99% (119 created / 3M connections)
[OK] Table cache hit rate: 33% (1K open / 4K opened)
[OK] Open file limit used: 27% (2K/7K)
[OK] Table locks acquired immediately: 99% (67M immediate / 67M locks)

-------- Recommendations -----------------------------------------------------
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
Enable the slow query log to troubleshoot bad queries
Temporary table size is already large - reduce result set size
Reduce your SELECT DISTINCT queries without LIMIT clauses
Variables to adjust:
query_cache_size (> 128M)

[marklcfc]

[--] Up for: 12d 18h 50m 56s (52M q [47.561 qps], 2M conn, TX: 1181B, RX: 23B)
[--] Reads / Writes: 65% / 35%
[--] Total buffers: 1.4G global + 6.8M per thread (500 max threads)
[OK] Maximum possible memory usage: 4.7G (62% of installed RAM)
[OK] Slow queries: 0% (65/52M)
[OK] Highest usage of available connections: 17% (87/500)
[OK] Key buffer size / total MyISAM indexes: 1.0G/873.9M
[OK] Key buffer hit rate: 100.0% (2B cached / 573K reads)
[OK] Query cache efficiency: 45.2% (16M cached / 36M selects)
[!!] Query cache prunes per day: 5782
[OK] Sorts requiring temporary tables: 2% (7K temp sorts / 281K sorts)
[!!] Joins performed without indexes: 4308
[!!] Temporary tables created on disk: 32% (122K on disk / 373K total)
[OK] Thread cache hit rate: 99% (87 created / 2M connections)
[OK] Table cache hit rate: 40% (1K open / 3K opened)
[OK] Open file limit used: 28% (2K/7K)
[OK] Table locks acquired immediately: 99% (48M immediate / 49M locks)

-------- 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
Temporary table size is already large - reduce result set size
Reduce your SELECT DISTINCT queries without LIMIT clauses
Variables to adjust:
query_cache_size (> 128M)
join_buffer_size (> 2.0M, or always use indexes with joins)


Any thoughts on this, what should I alter?