MySQL configuration
I'm running a Linode 512, and after running mysqltuner and doing a bit of experimentation I've gotten to the following state with my my.cnf:
connect_timeout = 10
interactive_timeout = 25
join_buffer_size = 1M
key_buffer_size = 128M
query_cache_limit = 1M
query_cache_size = 64M
query_cache_type = 1
max_allowed_packet = 20M
max_connect_errors = 10
max_connections = 200
max_heap_table_size = 8M
myisam_sort_buffer_size = 16M
read_buffer_size = 1M
sort_buffer_size = 4M
table_cache = 3000
thread_cache_size = 20
tmp_table_size = 8M
wait_timeout = 300
Based on what I've been researching this is a somewhat suitable set-up, but I'm looking for maximum speed and scalability — don't want it to fall over when I'm hit with load.
I know I can tweak as times goes on, but if anyone can see anything strikingly awry could you let me know? I suspect I might not have calculated my memory usage right and could end up in a situation where I overwhelm my VPS but I'm not quite sure to be honest, there're a lot of memory-based options!
I'll be running a WordPress site and a phpBB forum primarily, so getting this right is fairly important. Any help would be much appreciated.
5 Replies
Run mysqltuner again after the conversion, because InnoDB uses a completely different set of configuration parameters. A properly tuned InnoDB database will perform much better under heavy write, and you won't need to worry about database corruption as much.
I'll give it some more time and see if it comes up with anything else.
-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.3.3-MariaDB-rc-mariadb108~lucid-log
[OK] Operating on 32-bit architecture with less than 2GB RAM
-------- Storage Engine Statistics -------------------------------------------
[--] Status: -Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in InnoDB tables: 1M (Tables: 11)
[OK] Total fragmented tables: 0
-------- Performance Metrics -------------------------------------------------
[--] Up for: 10h 46m 30s (5K q [0.153 qps], 263 conn, TX: 5M, RX: 686K)
[--] Reads / Writes: 94% / 6%
[--] Total buffers: 496.0M global + 7.4M per thread (100 max threads)
[!!] Maximum possible memory usage: 1.2G (249% of installed RAM)
[OK] Slow queries: 0% (0/5K)
[OK] Highest usage of available connections: 2% (2/100)
[OK] Key buffer size / total MyISAM indexes: 128.0M/91.0K
[OK] Query cache efficiency: 90.5% (4K cached / 4K selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 31 sorts)
[OK] Temporary tables created on disk: 16% (23 on disk / 140 total)
[OK] Thread cache hit rate: 99% (2 created / 263 connections)
[OK] Table cache hit rate: 45% (34 open / 75 opened)
[OK] Open file limit used: 4% (51/1K)
[OK] Table locks acquired immediately: 100% (335 immediate / 335 locks)
[OK] InnoDB data size / buffer pool: 1.4M/256.0M
-------- Recommendations -----------------------------------------------------
General recommendations:
MySQL started within last 24 hours - recommendations may be inaccurate
Reduce your overall MySQL memory footprint for system stability
Enable the slow query log to troubleshoot bad queries
I've just changed innodbbufferpoolsize to 32M as it was set to the quite high default of 256M. More appropriate for a Linode 512? I've also lowered myisamsortbuffersize to 1M and will leave it there unless for some unlikely reason I'm forced to use MYISAM in the future.