THL Posted January 21, 2010 Posted January 21, 2010 I've just got my host to install mysqltuner for me, they have added it to the /root/mysqltuner.p directory... My question being how does it work, does it make a daily log? Also how do I access its findings? Thanks guys...
THL Posted January 21, 2010 Author Posted January 21, 2010 Just found some information on the web and ran the script... Heres my results, can anyone offer any experienced advice from these findings... Its on a dedicated server AMD5000+ x2 plus 4GB DDR2 Ram >> MySQLTuner 1.0.1 - Major Hayden <major@mhtx.net> >> Bug reports, feature requests, and downloads at http://mysqltuner.com/ >> Run with '--help' for additional options and output filtering -------- General Statistics -------------------------------------------------- [--] Skipped version check for MySQLTuner script [!!] Your MySQL version 4.1.22-standard is EOL software! Upgrade soon! [!!] Switch to 64-bit OS - MySQL cannot currently use all of your RAM -------- Storage Engine Statistics ------------------------------------------- [--] Status: -Archive -BDB -Federated +InnoDB -ISAM -NDBCluster [--] Data in MyISAM tables: 1G (Tables: 399) [--] Data in InnoDB tables: 96K (Tables: 6) [--] Data in HEAP tables: 181K (Tables: 8) [!!] Total fragmented tables: 42 -------- Performance Metrics ------------------------------------------------- [--] Up for: 9d 15h 35m 54s (142M q [170.403 qps], 2M conn, TX: 509M, RX: 3B) [--] Reads / Writes: 10% / 90% [--] Total buffers: 474.0M global + 9.3M per thread (500 max threads) [!!] Allocating > 2GB RAM on 32-bit systems can cause system instability [!!] Maximum possible memory usage: 5.0G (127% of installed RAM) [OK] Slow queries: 0% (818/142M) [OK] Highest usage of available connections: 49% (245/500) [OK] Key buffer size / total MyISAM indexes: 384.0M/433.9M [OK] Key buffer hit rate: 99.9% (4B cached / 2M reads) [OK] Query cache efficiency: 48.6% (12M cached / 26M selects) [!!] Query cache prunes per day: 24428 [OK] Sorts requiring temporary tables: 0% (3K temp sorts / 890K sorts) [OK] Temporary tables created on disk: 7% (15K on disk / 205K total) [OK] Thread cache hit rate: 98% (41K created / 2M connections) [!!] Table cache hit rate: 1% (511 open / 32K opened) [OK] Open file limit used: 35% (893/2K) [!!] Table locks acquired immediately: 58% [OK] InnoDB data size / buffer pool: 96.0K/8.0M -------- Recommendations ----------------------------------------------------- General recommendations: Run OPTIMIZE TABLE to defragment tables for better performance Enable the slow query log to troubleshoot bad queries Increase table_cache gradually to avoid file descriptor limits Optimize queries and/or use InnoDB to reduce lock wait Variables to adjust: *** MySQL's maximum memory usage is dangerously high *** *** Add RAM before increasing MySQL buffer variables *** query_cache_size (> 64M) table_cache (> 512)
THL Posted January 21, 2010 Author Posted January 21, 2010 Heres some details from my.cnf at present... [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock # Default to using old password format for compatibility with mysql 3.x # clients (those using the mysqlclient10 compatibility package). old_passwords=1 max_connections = 500 safe-show-database skip-locking key_buffer = 384M max_allowed_packet = 3M table_cache = 512 sort_buffer_size = 3M read_buffer_size = 3M read_rnd_buffer_size = 3M myisam_sort_buffer_size = 64M thread_cache_size = 8 query_cache_size= 64M thread_concurrency = 8 wait_timeout = 30 [mysql.server] user=mysql basedir=/var/lib [mysqld_safe] err-log=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid [isamchk] key_buffer = 256M sort_buffer_size = 256M read_buffer = 4M write_buffer = 4M
THL Posted January 21, 2010 Author Posted January 21, 2010 Forums been running slow AGAIN :( , so I ran mysqltuner to see if it came up with anything else... what do you guys suggest as I'm a complete noob to this... :blush: >> MySQLTuner 1.0.1 - Major Hayden <major@mhtx.net> >> Bug reports, feature requests, and downloads at http://mysqltuner.com/ >> Run with '--help' for additional options and output filtering -------- General Statistics -------------------------------------------------- [--] Skipped version check for MySQLTuner script [!!] Your MySQL version 4.1.22-standard is EOL software! Upgrade soon! [!!] Switch to 64-bit OS - MySQL cannot currently use all of your RAM -------- Storage Engine Statistics ------------------------------------------- [--] Status: -Archive -BDB -Federated +InnoDB -ISAM -NDBCluster [--] Data in MyISAM tables: 1G (Tables: 399) [--] Data in InnoDB tables: 96K (Tables: 6) [--] Data in HEAP tables: 181K (Tables: 8) [!!] Total fragmented tables: 38 -------- Performance Metrics ------------------------------------------------- [--] Up for: 10d 1h 10m 43s (145M q [167.596 qps], 2M conn, TX: 376M, RX: 1B) [--] Reads / Writes: 11% / 89% [--] Total buffers: 474.0M global + 9.3M per thread (500 max threads) [!!] Allocating > 2GB RAM on 32-bit systems can cause system instability [!!] Maximum possible memory usage: 5.0G (127% of installed RAM) [OK] Slow queries: 0% (989/145M) [OK] Highest usage of available connections: 49% (245/500) [OK] Key buffer size / total MyISAM indexes: 384.0M/435.4M [OK] Key buffer hit rate: 99.9% (4B cached / 2M reads) [OK] Query cache efficiency: 48.3% (13M cached / 27M selects) [!!] Query cache prunes per day: 25388 [OK] Sorts requiring temporary tables: 0% (3K temp sorts / 924K sorts) [OK] Temporary tables created on disk: 7% (15K on disk / 206K total) [OK] Thread cache hit rate: 98% (42K created / 2M connections) [!!] Table cache hit rate: 1% (512 open / 33K opened) [OK] Open file limit used: 22% (559/2K) [!!] Table locks acquired immediately: 58% [OK] InnoDB data size / buffer pool: 96.0K/8.0M -------- Recommendations ----------------------------------------------------- General recommendations: Run OPTIMIZE TABLE to defragment tables for better performance Enable the slow query log to troubleshoot bad queries Increase table_cache gradually to avoid file descriptor limits Optimize queries and/or use InnoDB to reduce lock wait Variables to adjust: *** MySQL's maximum memory usage is dangerously high *** *** Add RAM before increasing MySQL buffer variables *** query_cache_size (> 64M) table_cache (> 512)
THL Posted January 21, 2010 Author Posted January 21, 2010 Speaking to my host they are suggesting increasing ram and moving to a 64bit OS , is this my only option ??? Heck IPB 3 uses more resources than IPB 2.3 :(
Guest Posted January 22, 2010 Posted January 22, 2010 Looking at the MySQL Tuner output, you've not followed the instructions it gave you the first time you ran it. Your host is probably right about switching to a 64bit OS. Can you give us some more information about your server, including the following:What processor does it have?How much RAM do you have?What OS do you run currently (e.g. Ubuntu, CentOS)? and what version? Also about the forum, how many members online at any given time, etc? It sounds like you could possibly do with a host that would manage the server for you.Edit: Sorry, I missed that you'd included the server specs already. Other questions still apply.
THL Posted January 22, 2010 Author Posted January 22, 2010 Hello Dan the forum is set to show users online within 30mins not 15mins and peak times seems to be 400-500 members and 600 guests which some are spiders within the 30min period.... The OS used is linux centos 5 I think??? The hosting is with liquidweb.
Recommended Posts
Archived
This topic is now archived and is closed to further replies.