We're having a bit of a nightmare. We have three ubb.threads installations on a server. Two are fine, but the big and most trafficed board has a lot of issues.
We get very long query times, sometimes like 10 minutes to generate a page. But mostly the hold ups are about 60 seconds. Very sporadic too. I disabled search and had a period of tranquility, no issues at all. Then bham it did it again.
Sometimes, probably related, we get a ton of sleeping proceses, like it goes up to 600. IO wait shoots up, server load goes up and then apache often wigs out.
I would appreciate it very much if anyone could advise on mySQL settings. I'd like to perfect them as much as possible.
It's a 4 CPU machine. 2GB of RAM. The main board is highly trafficed, generally about 400 - 800 users using it any one time. We do run other sites on the server, so SQL can't be too greedy. But as you can see, it is important in terms of where the server load is lying.
I realize I don't really post here, despite being a member for a long time. I'm concious of being one of those people that comes in, gets what they want then goes. But I'm hoping you guys can help me out none the less.
(If I was more compentent, I'd contribute!)
These are the current numbers:
[mysqld]
socket=/home/virtual/FILESYSTEMTEMPLATE/.mysqlsock/mysql.sock
datadir=/var/lib/mysql
skip-locking
set-variable = key_buffer=640M
set-variable = max_allowed_packet=16M
set-variable = table_cache=1024
set-variable = sort_buffer=16M
set-variable = read_buffer_size=2M
set-variable = record_buffer=6M
set-variable = myisam_sort_buffer_size=128M
set-variable = thread_cache=16
# Try number of CPU's*2 for thread_concurrency
set-variable = thread_concurrency=8
set-variable = max_connections=512
set-variable=query_cache_type=1
set-variable=query_cache_size=16M
set-variable=wait_timeout=180
#log-slow-queries = /var/log/slow_queries.log
#long_query_time=10
