Optimizing MySQL parameters

[Shalabh]

I have purchased a VPS with 2.43GHz CPU, 2GB RAM, 80GB Disk Space with a band width of 1500 GB per month. MySQL version is version: 5.1.70-cll.

I want to know whether my current MySQL parameters good enough for my website. I have developed an online marketplace for translators and their clients. Translators and clients create their profiles on website. Clients post translation jobs and translators bid on these jobs. Recently, while creating demo profiles for testing, some of MySQL tables got crashed which I got repaired from my hosting company. My hosting company also has asked me to optimize the MySQL parameters to avoid table crash.

current my.cnf file

[mysqld]
Port = *port number
socket = /var/lib/mysql/mysql.sock
skip-locking
key_buffer_size = 384M
max_allowed_packet = 1M
table_open_cache = 512
sort_buffer_size = 2M
read_buffer_size = 2M
read_rnd_buffer_size = 8M
myisam_sort_buffer_size = 64M
thread_cache_size = 8
query_cache_size = 32M
# Try number of CPU's*2 for thread_concurrency
thread_concurrency = 8

1. Please suggest what parameters should I change/add to my.cnf file
2. Will it be enough to avoid table crash in future?

 

[thinkbot]

Add this for automatic repair, in case of MyISAM table crash

myisam_sort_buffer_size = 256M
myisam_recover = BACKUP,FORCE

and lower those:
sort_buffer_size = 256K
read_buffer_size = 128K
read_rnd_buffer_size = 2M


thread_concurrency = 8 - this can be removed, no use

and add slow log

slow_query_log=1
slow_query_log_file=mysql-slow.log
long_query_time=0.1

and few basics:
max_connections = 100
wait_timeout = 60
connect_timeout = 2
myisam_use_mmap=1

if you dont use innodb database engine, put also after [mysqld]
skip-innodb

this will save you some RAM

 

[Shalabh]

Can I add table_cache (> 64)? Is it of any use?

 

[thinkbot]

its the same as table_open_cache, just different name of variable for compatibility

 

[cPanelMichael]

Hello

Please also review the following thread to get a better idea on the type of information you can provide to help us offer you advice on MySQL optimization:

mysqlmymonlite.sh server stats gathering tool for cPanel Server

Thank you.

 

[Shalabh]

After optimizing MySQL parameters, my CPU usage is showing between 6.5%-8%, earlier it was only .03%.

 

[thinkbot]

where do you read that ?
do you have munin plugin installed in WHM ? can you post your load/cpu/memory/mysql/apache graphs ?