If only a few visitors make a high cpu load, then you need to root out the process which is causing this and optimize it's configuration.
I'd certainly recommend using mysqltuner to see if you can squeeze a bit more out of mysqld. How many queries/second are you doing?
Ok, but there's a problem: I can't find it...
Btw there's a lot of locked processes, like:
Locked UPDATE mybb_users SET lastactive = '1320220858',
timeonline = timeonline +36 WHERE uid = '302114'
Locked SELECT u . * , f . *
FROM mybb_users u
LEFT JOIN mybb_userfields f ON ( f.ufid = u.uid )
WHERE u.u
Locked SELECT u . * , u.username AS userusername, p . * , f . * , eu.username AS editusername
FROM mybb_posts p
I don't see any special, except these.
For the second question: I tried mysqltuner and it's suggested configurations, with no luck.
I never watch the queries, the only thing i know is: max. concurrent connections = 258
Look at what's locking your users table (possibly the 'last visited' update query), as well as your posts table.
Are there a lot of locks in your process list? If there's only those 3, it doesn't look like the source of your problems, but if there's a lot, then it probably is.
I just altered the users table to innodb again and I watched the process list for a long time.
Most of locks are gone, CPU load is same...
I still don't know what's the source of the problem but:
-I have more than 300k members
-a forum section (the most visited forum) with 100k threads (most of threads have prefixes)
What do you think about these?
If you're having locked tables then change to INNODB tables. I had the same problems a while back and once I switched my entire DB into INNODB I had no further problems.
If your table locks are user table related then you can easily just change that one table. But my guess is that you'll continue to see problems until you upgrade hardware and reconfigure your DB along with upgrading MySQL to 5.5 and use InnoDB.
Is your MySQL version 5.5? If not you'll see a big performance increase by upgrading. Huge difference.
http://www.vbum.com/2010/01/mybb-and-inc...rformance/
And again..join the big board group. We have threads discussing these changes.
(2011-11-03, 08:03 AM)Wiz01 Wrote: [ -> ]-I have more than 300k members
-a forum section (the most visited forum) with 100k threads (most of threads have prefixes)
What do you think about these?
Says very little about your problem.
You need to keep trying to track your problem. If you believe locks are no longer an issue, try some of these:
- stop the webserver and ensure there's no running queries on the database server, then try running the query - if it's still slow, then this is a query issue
- put "explain " before the query and run it
- check your I/O or disk load
I have hit this issue ... I think as part of the latest 1.6.9 upgrade. Forumdisplay is sorting through over 100K rows to display the forum. That sounds horribly wrong.
mysql> EXPLAIN SELECT t.*, t.username AS threadusername, u.username
-> FROM mybb_threads t
-> LEFT JOIN mybb_users u ON (u.uid = t.uid)
-> WHERE t.fid='439' AND t.visible='1'
-> ORDER BY t.sticky DESC, t.username asc
-> LIMIT 40950, 50;
Here's a pastebin with properly formated result:
http://pastebin.com/raw.php?i=FYJ0GHyL
This is showing up in mysql-slow.log with times up and above 2, 4, and 5 seconds. MySQL 5.5.22 InnoDB on all tables.
Could somebody issue a similar explain on their board to see if I'm missing an index or something?
mysql> EXPLAIN SELECT t.*, t.username AS threadusername, u.username FROM mybb_threads t LEFT JOIN mybb_users u ON (u.uid = t.uid) WHERE t.fid='25' AND t.visible='1' ORDER BY t.sticky DESC, t.username asc LIMIT 40950, 50;
+----+-------------+-------+--------+---------------+---------+---------+-----------------------+--------+-----------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+---------------+---------+---------+-----------------------+--------+-----------------------------+
| 1 | SIMPLE | t | ref | fid,visible | fid | 6 | const,const | 630628 | Using where; Using filesort |
| 1 | SIMPLE | u | eq_ref | PRIMARY | PRIMARY | 4 | hfaccount.t.uid | 1 | |
btw I ran that on my Lounge with 330k threads.
(2013-01-07, 09:41 PM)labrocca Wrote: [ -> ]mysql> EXPLAIN SELECT t.*, t.username AS threadusername, u.username FROM mybb_threads t LEFT JOIN mybb_users u ON (u.uid = t.uid) WHERE t.fid='25' AND t.visible='1' ORDER BY t.sticky DESC, t.username asc LIMIT 40950, 50;
+----+-------------+-------+--------+---------------+---------+---------+-----------------------+--------+-----------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+---------------+---------+---------+-----------------------+--------+-----------------------------+
| 1 | SIMPLE | t | ref | fid,visible | fid | 6 | const,const | 630628 | Using where; Using filesort |
| 1 | SIMPLE | u | eq_ref | PRIMARY | PRIMARY | 4 | hfaccount.t.uid | 1 | |
btw I ran that on my Lounge with 330k threads.
We've got all the same keys and such. Your first explain looks just as row hideous as some of my larger ones. Is this showing up in slow queries on your side? My forum still feels responsive. But I have dual quad core Xeons and MyBB is now eating ALL available CPU, and I haven't seen that before. MySQL was always hoving around 150-200% CPU and bursty to 300-400% It's constantly at 700%+ now.
Going to try a MySQL update.
Are you sure it's not something else pushing the CPU? Maybe a DDOS attack of some sort?
You also may have hit some limit in your resources. Can you give MySQL more memory?