Had this question on Serverfault for a few days with no luck.
I've run mysqltuner.pl on a VPS and have a bunch of questions as to the suggestions on variables to change. I'm sure these are general questions with complex answers.
I'm not knowledgable enough to write queries and test them against the server, but am just trying to get a bit more performance out of the server that runs five WordPress sites with >200,000 page views/month.
I've optimized the database via phpmyadmin (and do that regularly), but the tuner still says there are fragmented tables. And because this is WordPress, I can't change queries in core code.
But how much should I increase the variables like query_cache_size and innodb_buffer_pool_size? What about the other innodb variables?
Some of the variables suggested don't exist in my.cnf, like table_cache, and are flagged in tuner report, etc. Can I add them to my.cnf?
(And why is this block duplicated in my.cnf? Can I delete the duplicate?)
set-variable = innodb_buffer_pool_size=2M set-variable = innodb_additional_mem_pool_size=500K set-variable = innodb_log_buffer_size=500K set-variable = innodb_thread_concurrency=2
Below is the my.cnf and the output of mysqltuner:
Contents of my.cnf:
query-cache-type = 1
query-cache-size = 8M
set-variable=local-infile=0
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
old_passwords=1
skip-bdb
set-variable = innodb_buffer_pool_size=2M
set-variable = innodb_additional_mem_pool_size=500K
set-variable = innodb_log_buffer_size=500K
set-variable = innodb_thread_concurrency=2
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
skip-bdb
set-variable = innodb_buffer_pool_size=2M
set-variable = innodb_additional_mem_pool_size=500K
set-variable = innodb_log_buffer_size=500K
set-variable = innodb_thread_concurr