marklcfc Posted January 25, 2012 Author Posted January 25, 2012 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?
marklcfc Posted March 1, 2012 Author Posted March 1, 2012 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 Posted March 21, 2012 Author Posted March 21, 2012 [--] 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?
Recommended Posts
Archived
This topic is now archived and is closed to further replies.