mysqltunner recommendations

[AlexJ]

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)

[AlexJ]

Anyone please?

[Bono]

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.

[AlexJ]

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.

[Bono]

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.

[AlexJ]

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?

[Bono]

Optimize specific database:
mysqlcheck -optimize dbname -p
Optimize all databases:
mysqlcheck -o --all-databases

I run those once a week.

[AlexJ]

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.

[Bono]

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.

[AlexJ]

[!!] Table cache hit rate: 0% (128 open / 419K opened)

Does that mean cache is not being used at all?