AlexJ Posted March 26, 2010 Share Posted March 26, 2010 I run for first time mysqltunner script and it showed me below output. Can someone please tell me what it means and what I need to fix? -------- General Statistics -------------------------------------------------- [--] Skipped version check for MySQLTuner script [OK] Currently running supported MySQL version 5.0.51a-24+lenny3 [OK] Operating on 64-bit architecture -------- Storage Engine Statistics ------------------------------------------- [--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster [--] Data in MyISAM tables: 106M (Tables: 311) [!!] InnoDB is enabled but isn't being used [!!] Total fragmented tables: 40 -------- Performance Metrics ------------------------------------------------- [--] Up for: 18d 3h 37m 4s (9M q [6.188 qps], 441K conn, TX: 375B, RX: 8B) [--] Reads / Writes: 54% / 46% [--] Total buffers: 58.0M global + 2.6M per thread (100 max threads) [OK] Maximum possible memory usage: 320.5M (4% of installed RAM) [OK] Slow queries: 0% (0/9M) [OK] Highest usage of available connections: 13% (13/100) [OK] Key buffer size / total MyISAM indexes: 16.0M/42.8M [OK] Key buffer hit rate: 99.5% (381M cached / 1M reads) [OK] Query cache efficiency: 59.2% (3M cached / 5M selects) [!!] Query cache prunes per day: 6192 [OK] Sorts requiring temporary tables: 0% (0 temp sorts / 205K sorts) [!!] Temporary tables created on disk: 41% (266K on disk / 641K total) [OK] Thread cache hit rate: 99% (260 created / 441K connections) [!!] Table cache hit rate: 0% (64 open / 483K opened) [OK] Open file limit used: 12% (128/1K) [OK] Table locks acquired immediately: 99% (6M immediate / 6M locks) -------- Recommendations ----------------------------------------------------- General recommendations: Add skip-innodb to MySQL configuration to disable InnoDB Run OPTIMIZE TABLE to defragment tables for better performance Enable the slow query log to troubleshoot bad queries When making adjustments, make tmp_table_size/max_heap_table_size equal Reduce your SELECT DISTINCT queries without LIMIT clauses Increase table_cache gradually to avoid file descriptor limits Variables to adjust: query_cache_size (> 16M) tmp_table_size (> 32M) max_heap_table_size (> 16M) table_cache (> 64) Link to comment Share on other sites More sharing options...
AlexJ Posted March 27, 2010 Author Share Posted March 27, 2010 Anyone please? Link to comment Share on other sites More sharing options...
Bono Posted March 28, 2010 Share Posted March 28, 2010 It is written in plain english so you shouldn't have any problems understanding it. Just ask what you do not understand and i'll reply. Link to comment Share on other sites More sharing options...
AlexJ Posted March 29, 2010 Author Share Posted March 29, 2010 It is written in plain english so you shouldn't have any problems understanding it. Just ask what you do not understand and i'll reply. Because in my sql config their is no such settings mentioned below: tmp_table_size (> 32M) max_heap_table_size (> 16M) Also, it says to use same value but google search indicates still it will make tables on disk. Also, i was not able to find any documentation on innoDB for IPB. You can find topics for it in Drupal or Joomla forums but not much in google for IPB. More or less I can sure play around with all values for 1 month but if some experience person says use this values for this reason from his experience, it's pretty good and hence the topic. Link to comment Share on other sites More sharing options...
Bono Posted March 29, 2010 Share Posted March 29, 2010 If you do not have those lines in your config, then default mysql settings are use. It is save to set values for those two to 64M, and as far as I saw IPB doesn't write much to tmp_table, Joomla 1.x writes a lot of queries to tmp. bfarber wrote in this section that he doesn't recommend innoDB so there isn't much talk about it, if i remember correctly he said you could offload your mysql server with sphinx search. Link to comment Share on other sites More sharing options...
AlexJ Posted March 30, 2010 Author Share Posted March 30, 2010 I will try to install sphinx when IPB 3.1 releases as it some great improvements. :) Do you know any good way to optimize table using shell commands? Link to comment Share on other sites More sharing options...
Bono Posted March 30, 2010 Share Posted March 30, 2010 Optimize specific database: mysqlcheck -optimize dbname -p Optimize all databases: mysqlcheck -o --all-databases I run those once a week. Link to comment Share on other sites More sharing options...
AlexJ Posted April 2, 2010 Author Share Posted April 2, 2010 Optimize specific database: mysqlcheck -optimize dbname -p Optimize all databases: mysqlcheck -o --all-databases I run those once a week. Do you close down forum when you do that? or you can run those while forums are online? Also, do you know how to make a cron job for that? Thank you. Link to comment Share on other sites More sharing options...
Bono Posted April 2, 2010 Share Posted April 2, 2010 I see you got small forum so you do not need to close it down while optimizing, this operation could take 10-20s so it is not a big deal. You can always run it when your board has little or no activity. .---------------- minute (0 - 59) | .------------- hour (0 - 23) | | .---------- day of month (1 - 31) | | | .------- month (1 - 12) OR jan,feb,mar,apr ... | | | | .---- day of week (0 - 7) (Sunday=0 or 7) OR sun,mon,tue,wed,thu,fri,sat | | | | | * * * * * command to be executed So adjust crontab to run at specific time and when you replaced some stars or all with numbers just add after that command I have gave you. Also maybe easier way is that I found in community projects that there is plugin which can run crontab inside ACP and it automatically closes board. So it's just matter of preference which option is more suitable for you. Link to comment Share on other sites More sharing options...
AlexJ Posted May 27, 2010 Author Share Posted May 27, 2010 [!!] Table cache hit rate: 0% (128 open / 419K opened) Does that mean cache is not being used at all? Link to comment Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.