Issue with MySql optimization ?

[szk]

Hello,
We have issue with MySql db and I'll try to describe what happens
Our site reaches about 11.000 visits per day and handles 2 MySql data bases,
hosted on VPS :
Total processors: 10
Intel(R) Xeon(R) CPU E5620 @ 2.40GHz
Total memory: 2GB
mysql 5.1.56

Issue that happens is that in MySQL Process List we can see that it gets full with queries that needs more than
160 secs of time to execute (connections are not "Sleep").
That would be no problem if it is only one or couple of connections, but when that happen connections only accumulate and MySql stops responding.
Sometimes it needs 2 - 5 minutes to clear and resolve queries and sometimes it needs 30 - 60 minutes.
Usually we resolve this issue by restarting MySql service, but occasionally happens that restarting is not solving this issue because the same minute that MySql starts it gets full of processes again.
Interesting thing is that this issue is not happening when we have visitors peek, we use google analytics and wecan see that, when we have 200 visitors at the same time, everything runs smoothly, also we do not use permanent connections, and everything on the site is cashed for 60 minutes.
Here is our my.cnf

[mysqld]
thread_concurrency=4
max_connections=200
max_user_connections=10
key_buffer=256M
myisam_sort_buffer_size=64M
join_buffer_size=2M
read_buffer_size=1M
sort_buffer_size=1M
table_cache=2500
thread_cache_size=128
interactive_timeout=45
wait_timeout=20
connect_timeout=8
max_allowed_packet=16M
max_connect_errors=10
query_cache_limit=1M
query_cache_size=64M
query_cache_type=1
flush
flush_time=60
table_definition_cache = 512
max_heap_table_size = 32M
tmp_table_size = 32M

[mysql.server]
user=mysql

[mysqld_safe]
open_files_limit=8192

[mysqldump]
quick
max_allowed_packet=16M

[myisamchk]
key_buffer=64M
sort_buffer=64M
read_buffer=16M
write_buffer=16M

[mysqlhotcopy]
interactive-timeout

We appreciate any help and idea what might be wrong
Thank You

[cPanelTristan]

Have you checked the MySQL error log at /var/lib/mysql/hostname.err location where hostname is the fully qualified hostname of the machine (returned by the "hostname -f" command without the quotes in command line)?

For the existing /etc/my.cnf file, you'd want to remove the invalid entries and revise for the MySQL version you have:

[mysqld]
max_connections=200
max_user_connections=10
key_buffer_size=256M
myisam_sort_buffer_size=64M
join_buffer_size=2M
read_buffer_size=1M
sort_buffer_size=1M
table_open_cache=2500
thread_cache_size=128
interactive_timeout=45
wait_timeout=20
connect_timeout=8
max_allowed_packet=16M
max_connect_errors=10
query_cache_limit=1M
query_cache_size=64M
query_cache_type=1
#flush
#flush_time=60
table_definition_cache = 512
max_heap_table_size = 32M
tmp_table_size = 32M

[mysqld_safe]
open_files_limit=8192

[mysqldump]
max_allowed_packet=16M

[myisamchk]
key_buffer_size=64M
sort_buffer_size=64M
read_buffer_size=16M
write_buffer_size=16M

[mysqlhotcopy]
interactive-timeout

I've also commented out flush and flush_time:

http://dev.mysql.com/doc/refman/5.1/...l#sysvar_flush

I have no idea why you are using these in /etc/my.cnf file.

[szk]

Thank You for the instructions, we have applied changes to config as You wrote.
For now everything works and runs smoothly. In couple of days when we finish tests, we'll update with results.
Greetings and thanks again