mySQL Optimization for VPS

eglwolf

Well-Known Member
Jan 1, 2004
190
0
166
I have the following type of VPS:
CPU: 2500MHZ GUARANTEED
Memory: 2.5GB GUARANTEED
VPS Software: vePortal™ / OpenVZ™
cPanel running CentOS 5.5

I have a handful of sites that all run WordPress. One of my sites get 2-4K unique visitors a day. The load of the server spikes between 2-5. Burst (the Datacenter) suggested that it could be mySQL. I have included my current my.cnf file to see if anyone might have some suggestion or better settings to use with this type of vas server.

Any help you all can offer is much appreciated.

Code:
[mysqld]
max_connections=300
key_buffer = 32M
myisam_sort_buffer_size = 32M
join_buffer_size = 1M
read_buffer_size = 1M
sort_buffer_size = 2M
table_cache = 4000
thread_cache_size = 286
wait_timeout = 7000
connect_timeout = 10
max_allowed_packet = 16M
max_connect_errors = 10
query_cache_limit = 12M
query_cache_type = 1
tmp_table_size =16M
skip-innodb
[mysqld_safe]
open_files_limit = 8192
[mysqldump]
quick
max_allowed_packet = 16M
[isamchk]
key_buffer = 64M
sort_buffer_size = 64M
read_buffer = 16M
write_buffer = 16M
[mysqldhotcopy]
interactive-timeout
 

cPanelTristan

Quality Assurance Analyst
Staff member
Oct 2, 2010
7,607
40
248
somewhere over the rainbow
cPanel Access Level
Root Administrator
I did want to mention a couple of points for anyone reading this thread. First of all, whenever asking for MySQL optimization assistance, please always state your MySQL version. MySQL 5.0 has different variables over MySQL 5.1 for the /etc/my.cnf file for some settings. If you are unsure of your MySQL version, you can obtain that information using the following command:

Code:
mysql_config --version
For example, if you run MySQL 5.1, then table_cache should be changed to table_open_cache instead.

Next, key_buffer, read_buffer and write_buffer are all incorrect variable names. It should be key_buffer_size, read_buffer_size and write_buffer_size for each, respectively.