Forum mysql optimization
 

Hi,

I'm using mysql 5.1.47. Occasionally I hit the connection limit of 151. I typically have about 500-800 users online (guests and members). The forum has 250k threads and 5M posts. Hardware is dual E5520 with 6GB ram.

Slow queries log shows lots of stuff, most are relatively short, but some are in the range of 22 seconds, here's a few:

Code:
# Query_time: 22.790491 Lock_time: 0.000133 Rows_sent: 1 Rows_examined: 7083
SET timestamp=1283998166;
SELECT
(SELECT MAX(tr.readtime) from threadread AS tr where
tr.threadid = 110423 AND tr.userid = 20923) AS readtime,
MAX(p.dateline)FROM post AS p
WHERE p.threadid = 110423;
Code:
# Query_time: 10.093913 Lock_time: 0.000049 Rows_sent: 15 Rows_examined: 2373
SET timestamp=1283998165;
SELECT post.postid
FROM post AS post

WHERE post.threadid = 123660241
AND post.visible = 1


ORDER BY post.dateline
LIMIT 60, 15;
I know this isn't much to go on, but what variables should I override in my.cnf? So far I have:

Code:
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
set-variable=long_query_time=2
log-slow-queries=/tmp/log-slow-queries.log
max_connections=500
query_cache_size=1024M
query_cache_limit=64M
query_cache_type=1
tmp_table_size=256M

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid