MySQL tuner

netomx

I have used mysqltuner.pl in the past, but I don't really know if the values that recommends me are good.

[--] Up for: 50d 4h 24m 5s (41M q [9.684 qps], 1M conn, TX: 147B, RX: 6B)
[--] Reads / Writes: 77% / 23%
[--] Total buffers: 688.0M global + 2.7M per thread (100 max threads)
[OK] Maximum possible memory usage: 956.8M (46% of installed RAM)
[OK] Slow queries: 0% (0/41M)
[OK] Highest usage of available connections: 19% (19/100)
[OK] Key buffer size / total MyISAM indexes: 16.0M/8.6M
[OK] Key buffer hit rate: 100.0% (31M cached / 2K reads)
[OK] Query cache efficiency: 93.4% (33M cached / 35M selects)
[!!] Query cache prunes per day: 18570
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 595K sorts)
[!!] Temporary tables created on disk: 26% (177K on disk / 660K total)
[OK] Thread cache hit rate: 99% (148 created / 1M connections)
[!!] Table cache hit rate: 19% (2K open / 10K opened)
[OK] Open file limit used: 25% (1K/4K)
[OK] Table locks acquired immediately: 99% (3M immediate / 3M locks)
[OK] InnoDB data size / buffer pool: 375.5M/512.0M

Suggestions:

-------- Recommendations -----------------------------------------------------
General recommendations:
    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 (> 128M)
    max_heap_table_size (> 128M)
    table_cache (> 2048)

don't you think the values are high enough?

thx

jar

It depends on your memory and how your application scales under pressure. It's a decent guide but the best tweaks are done by actual comparison of application load to MySQL performance.

netomx

@jarland thanks for your prompt answer. I haven't had any problems, indeed that 50 days of uptime were made bc I used mysqltuner. I have not so many hits, althought I have several customers hosting it. So, we can say it is well-tuned?

jar

@netomx said:
jarland thanks for your prompt answer. I haven't had any problems, indeed that 50 days of uptime were made bc I used mysqltuner. I have not so many hits, althought I have several customers hosting it. So, we can say it is well-tuned?

Yeah, in my opinion those numbers are fine. I've noticed mysqltuner has a tendency to make suggestions to you above your actual total memory. Sometimes I'll just use it to remind myself of what I'm forgetting though

TheLinuxBug

One thing I like to do for temp joins and such is I like to make the temp directory for MySQL /dev/shm if you have the available memory for this. It provides a nice boost for all those temporary table joins on disk you are seeing. Now of course if the server OOMs at some point you would lose those temp tables in memory, but usually this isn't a huge issue.

May or may not be for you, but something to check out.

Cheers!