Mysql optmization

Hello,

I have VPS XXL + Cpanel and the use is basically to host forums ( phpbb ) and blogs ( wordpress ).

What do you recommend me to do?

CPU usage is over 4.50 ( according to WHM )
 
My my.conf settings

Hi Stormrider,

I host mainly Drupal sites and 1 wordpress blog. Drupal sites are database hungry and run many queries to the db, so it is important to enable mysql cache. There are few other important things to look at like table_cache and max connections.

The default interactive_timeout, wait_timeout and connect_timeout should be adjusted depending on your vps accounts memory. I have around 588M on my account and the settings below made a huge performance difference as compared to the default, which are usually set for servers with at least a gig of ram.

Code:
skip-locking
skip-innodb
max_connections=200
key_buffer=32M
myisam_sort_buffer_size=64M
join_buffer=2M
record_buffer=1M
sort_buffer_size=3M
read_buffer_size=2M
table_cache=2048
thread_cache_size=286
interactive_timeout=25
wait_timeout=100
connect_timeout=10
query_cache_limit=2M
query_cache_size=32M
query_cache_type=1
max_allowed_packet=16M
max_connect_errors=999999
thread_concurrency=4
tmp_table_size = 32M

I would suggest that you customise the above to your servers requirements. Wordpress runs much less queries than Drupal.

A very useful tool is
http://www.day32.com/MySQL/tuning-primer.sh
this shell script which gives you suggestions on how to optimise your my.conf settings.
 
Top