Any help would be greatly appreciated
I'm currently hosting a popular forum at forums.weddingbells.ca and I just recently migrated and upgraded the whole site. I went from running it on a dedicated server to a virtual server.
I went from running it on ubb 6.5 to 7.5.3
I currently have ~32,000 uses, 120,000 topics and 1.7 million posts.
The server is on ubuntu 9.04 with 8G of RAM, but my load averages are up in the 50s when the site is busy.
It seems that mySQL is the bottleneck here. I ran mysqltuner to try to improve things, I followed the suggestions it made and I'll keep trying it.
Top shows
top - 22:13:02 up 2:10, 1 user, load average: 41.09, 31.77, 30.51
Tasks: 218 total, 2 running, 216 sleeping, 0 stopped, 0 zombie
Cpu(s): 11.6%us, 32.6%sy, 0.0%ni, 55.5%id, 0.2%wa, 0.0%hi, 0.0%si, 0.1%st
Mem: 8388828k total, 3691272k used, 4697556k free, 18708k buffers
Swap: 16777208k total, 0k used, 16777208k free, 1927184k cached
i started the slow query log and I noticed that there is one query that keeps appearing frequently, but I don't know where it's coming from
# User@Host: forums[forums] @ localhost []
# Query_time: 43 Lock_time: 0 Rows_sent: 1 Rows_examined: 369772
SELECT COUNT(p.POST_ID) AS POSTS, SUM(p.POST_IS_TOPIC) AS TOPICS, t.FORUM_ID
FROM ubbt_POSTS as p,
ubbt_TOPICS as t
WHERE (t.TOPIC_ID = p.TOPIC_ID) AND p.POST_IS_APPROVED = 1
AND (0
OR (t.FORUM_ID = 15 AND p.POST_POSTED_TIME > 0)) GROUP BY t.FORUM_ID;
#
At this point I'll take any suggestions.
