SMF made mysql stuck

[b3dm4n]

additional detail about our smf forum
version: 1.1.11
4519860 Posts in 22371 Topics by 140926 Members

Please apply for access to the Big Forum group. You can do that by going here.

I've requested to join that group, and still got aproval pending from the day you posted this until now

[b3dm4n]

If you restore from a backup and it works "fine" then you know it's your db. It would pretty much rule out "code" bugs, sql tuning, etc.

You could always restore the known good backup to a second db and test it.

I've been working on restoring since the day you suggested, it took 30 hours to restore our 14Gb db, we restore it into different name db, still no improve.

we still have "UPDATE smf_messages SET ID_TOPIC = XXXXX, ID_BOARD = XXXXX" locking the tables for more than 3000secs, I think this locking that stall our db.

How to work arround this "UPDATE smf_messages" queries from locking the tables too long? index? temp tables? innodb on smf_messages?

here are some script that queue behind the update smf_messages

Code Select Expand


Copying to tmp table 581s
SELECT COUNT(*) AS 'POSTCOUNT', smf_members.ID_MEMBER, smf_members.memberName, smf_membergroups.onlineColor
            FROM (smf_messages INNER JOIN smf_members ON smf_messages.ID_MEMBER = smf_members.ID_MEMBER) LEFT JOIN smf_membergroups ON smf_members.ID_GROUP = smf_membergroups.ID_GROUP
            WHERE (((smf_messages.ID_TOPIC)=401))
            GROUP BY smf_members.ID_MEMBER ORDER BY POSTCOUNT DESC


[b3dm4n]

today's query of the day:
Command: Query
State: Locked
Time : 2700 and still counting
Info: UPDATE smf_messages SET ID_MSG_MODIFIED = xxxxxxx WHERE ID_MSG = xxxxxxx

there are more at those time

[Ensiferous]

You should not focus on the locked ones. The important ones are those running. Run this query:

Code Select Expand

EXPLAIN SELECT COUNT(*) AS 'POSTCOUNT', smf_members.ID_MEMBER, smf_members.memberName, smf_membergroups.onlineColor
            FROM (smf_messages INNER JOIN smf_members ON smf_messages.ID_MEMBER = smf_members.ID_MEMBER) LEFT JOIN smf_membergroups ON smf_members.ID_GROUP = smf_membergroups.ID_GROUP
            WHERE (((smf_messages.ID_TOPIC)=401))
            GROUP BY smf_members.ID_MEMBER ORDER BY POSTCOUNT DESC

I fear this query is a full-table scan due to different GROUP BY and ORDER BY columns.

[b3dm4n]

You should not focus on the locked ones. The important ones are those running. Run this query:

Code Select Expand

EXPLAIN SELECT COUNT(*) AS 'POSTCOUNT', smf_members.ID_MEMBER, smf_members.memberName, smf_membergroups.onlineColor
            FROM (smf_messages INNER JOIN smf_members ON smf_messages.ID_MEMBER = smf_members.ID_MEMBER) LEFT JOIN smf_membergroups ON smf_members.ID_GROUP = smf_membergroups.ID_GROUP
            WHERE (((smf_messages.ID_TOPIC)=401))
            GROUP BY smf_members.ID_MEMBER ORDER BY POSTCOUNT DESC

I fear this query is a full-table scan due to different GROUP BY and ORDER BY columns.

I ran your query, here are the results

Code Select Expand


+----+-------------+------------------+--------+--------------------------------------------------+---------+---------+-------------------------------------+-------+---------------------------------+
| id | select_type | table            | type   | possible_keys                                    | key     | key_len | ref                                 | rows  | Extra                           |
+----+-------------+------------------+--------+--------------------------------------------------+---------+---------+-------------------------------------+-------+---------------------------------+
|  1 | SIMPLE      | smf_messages     | ref    | topic,ID_MEMBER,participation,showPosts,ID_TOPIC | topic   | 3       | const                               | 28082 | Using temporary; Using filesort |
|  1 | SIMPLE      | smf_members      | eq_ref | PRIMARY                                          | PRIMARY | 3       | c1lautan_bak.smf_messages.ID_MEMBER |     1 |                                 |
|  1 | SIMPLE      | smf_membergroups | eq_ref | PRIMARY                                          | PRIMARY | 2       | c1lautan_bak.smf_members.ID_GROUP   |     1 |                                 |
+----+-------------+------------------+--------+--------------------------------------------------+---------+---------+-------------------------------------+-------+---------------------------------+
3 rows in set (0.00 sec)


what are the result showed? could you enlight me?
before I ran your query, I made small changes in my.cnf config:
table_cache = 1400
key_buffer = 256M
join_buffer_size = 5M

and now (20 minutes later) the new config seems has kick in, the mtop showing the longest queue is no more than 10 secs, and the forum run pretty fast
I hope its not just because its a holiday in our country, and not much member joining in, hopefully this condition remain still for good 

try it ... http://www.lautanindonesia.com/forum (its in indonesian language)
and tell me how to optimize it

[b3dm4n]

ups, speak too soon .. 
today the problem raised again, with the similar query as my last post.
the queries has been locked for more than 2000secs
then I ran again ensiferous suggested query, it took 80 secs to complete.

[b3dm4n]

just wanna update what i found on mtop to night.

Command: Query
State: Locked
Time : 1200 and still counting up
Info: UPDATE smf_messages SET ID_MSG_MODIFIED = xxxxxxx WHERE ID_MSG = xxxxxxx

and this similar query (only the numbers are different) fill the top 30 line on mtop

what is happening? I'm desperate ... 

[b3dm4n]

no body have the same problem?

attached is result from status.php this morning
is it normal copying to temp take that long?

[Ensiferous]

No it's not. It's a poor query but 28,000 rows isn't too bad, but if it's run often then it could kill your hard drive performance or something similar.

I'm not sure exactly where this is run, it might be one of your mods or built in. So you pretty much have two options. Either disable your mods and see if it goes away, then slowly add them back in until it happens again. Or upgrade your forum to SMF 2.0 as that contains numerous performance improvements.

[b3dm4n]

after few days monitoring server performance, I think we can point out what causing our server problems.
It seems that after an hour running, we must restart mysql in order to make the forum access fast again. I almost convinced that the problem is in mysql configuration, something with cache I believe, I've googled our problem and tried many configuration but the problem still exist, or maybe I was looking at the wrong direction?

here is our latest my.cnf, can someone point me what to tweak ?

Code Select Expand


skip-external-locking
back_log                = 50
skip-locking
connect_timeout         = 10
interactive_timeout     = 20
join_buffer_size        = 3M
read_buffer_size        = 2M
sort_buffer_size        = 2M
key_buffer_size         = 200M
max_allowed_packet      = 16M
thread_stack            = 100K
thread_cache_size       = 200
read_rnd_buffer_size    = 524288
bulk_insert_buffer_size = 10M
myisam-recover          = BACKUP
myisam_sort_buffer_size = 100M
max_connections         = 200
max_user_connections    = 300
table_cache             = 3000
table_definition_cache  = 500
tmp_table_size          = 64M
max_heap_table_size     = 50M
max_allowed_packet      = 16M
max_connect_errors      = 10M
thread_concurrency      = 16
open_files_limit        = 2600
wait_timeout            = 6000
low_priority_updates    = 1
concurrent_insert       = 20

query_cache_limit       = 32M
query_cache_size        = 250M
query_cache_type        = 1
query_prealloc_size     = 65536
query_alloc_block_size  = 131072
query_cache_min_res_unit = 4K
default-storage-engine  = MyISAM


my server:
Dual Xeon Quad Core
4Gb Ram
512 SATA

[b3dm4n]

I notice a query of optimize tables running every hour, maybe its in smf script? coz its not in any cron that I have.
during the table optimizing other queries are queue, but after the optimizing finished, the next query in queue that have "UPDATE smf_messages SET ID_MSG_MODIFIED = xxxxxxx WHERE ID_MSG = xxxxxxx" seems not processing, stuck in status locked and never last

[chep]

In smf 2 you can run a scheduled task to optimize tables. It might be worth checking to see if that is turned off.

Admin -> Features and Options -> Maintenance -> Scheduled Tasks

[b3dm4n]

unfortunately we are still using smf 1.1.11 
we're thinking to upgrading the smf, we're studying how to upgrade and what problems could appear when we upgrade to smf 2 RC3

[b3dm4n]

I'm sorry for diging this topic up again, but we kinda found solution of this problem after struggling for almost a year, but still it raise other problem though

Maybe other who are facing this kinda problem can use our solution to their options.

After reformatting the server 3 times with different linux distros and still got the same problem, we finally loose the apache and change it with nginx server with php-fpm. the server instantly calm down, and the mysql query queue last no more than 70s secs.

after running for two weeks, we upgrade smf to 2.0 version, the upgrade process took 4 hours, and after the process finished, the forum run lightning fast. No queue list on the mysql query, all query finished less than 1 sec.

finally, our forum admins is smiling again :-)

but unfortunately our smf 2.0 RC 4 still have error in search section
it said

An Error Has Occurred!
The search API could not be found! Please contact the admin to check they have uploaded the correct files.

I've open a new topic for this problem : http://www.simplemachines.org/community/index.php?topic=418244.0

Thanks