Hi guys,

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.

Yes. Convert all your tables to InnoDB.

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.

Hmm, looks like my tables are already InnoDB. All mysqltuner is doing right now is telling me to reduce my memory footprint to avoid running out of memory, but there're no specific parameters being recommended.

I'll give it some more time and see if it comes up with anything else.

Paste the output from mysqltuner here; it has its limits and won't always tell you what you need to do. Also, you probably don't need max_connections = 200

If all your tables are already InnoDB, some of your settings (such as myisam_sort_buffer_size) may need to be reduced because they don't apply to InnoDB. You'll also need to add some InnoDB-specific options: the most important thing is innodb_buffer_pool_size.

All right, here's the full mysqltuner output below. How many connections would you say is appropriate? I have Nginx set to 4 worker processes with 1024 connections each, and PHP-FPM set to use eight processes if that's relevant.

-------- 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 innodb_buffer_pool_size to 32M as it was set to the quite high default of 256M. More appropriate for a Linode 512? I've also lowered myisam_sort_buffer_size to 1M and will leave it there unless for some unlikely reason I'm forced to use MYISAM in the future.