"sending data" MySQL connection locks all others

Started by Mike Bobbitt, September 20, 2006, 10:39:59 AM

Previous topic - Next topic

Mike Bobbitt

Hi all,

Recently, I've noticed my system comes to a grinding halt on a regular basis, with the CPU load going as high as the 70's. (That's load, not CPU %!) I'm slowly tracing my way to the problem, and so far I've discovered it's a MySQL issue. That is, a single MySQL query gets stuck with a "sending data" status and all other queries are then locked. The hung connection can remain in this state for a long time (10 minutes before I kill it for example) and meanwhile, more pending connections queue up with a locked state.

The forums are then totally unresponsive and all the waiting processes continue to add up, contributing more to the problem. This seems to occur at least once a day, sometimes more.

Unfortunately because this happens suddenly and randomly, I've only been able to catch it once and neglected to write down the details, but I'm fairly certain the "sending data" query was stuck on smf_log_search_topics. If I catch it again I will take down all the details I can.

It *may* have been coincident with upgrading to 1.1 RC3, but I can't say for sure.

Any suggestions would be appreciated.


  • A link to your forum.
http://forums.army.ca

  • A link to your status.php
[GONE]
Note MySQL was restarted at about 10:10 today, so some metrics may not have "settled in" to their normal values yet.

  • a link to your phpinfo.php
[GONE]

  • Is this a dedicated server or shared host. If it is a dedicated server, what spec is the server and what else runs on the server? If it is a shared host post a link to the hosts site the package you are on
Dedicated: Fedora Core 5, Pentium 4 CPU 2.80GHz, 2 Gb RAM. Also runs a mail server and several smaller (very low traffic) sites.

  • The Number of posts your forum has.
434,258 Posts in 27,163 Topics

  • The Average number of users you have online during peak time and over what time period that is over, e.g 400 users over 15 mins.
200-300 over 30 minutes

  • A list of mods which you have installed
Oldiesmann's SMF+G2 mod (http://smf.oldiesmann.us/galleryproject/index.php)
Custom theme changes applied via mod

  • Do you still have performance issues if you use the default theme?
Yes

Went through the list. Selected tables have been InnoDB for a long time. No accelerator and caching is turned off. I tweaked MySQL settings a long time ago, but usage has changed and I'm no expert - they could probably use another update.

Ben_S

Liverpool FC Forum with 14 million+ posts.

Mike Bobbitt

Not sure, it was and update or insert to smf_log_topics as I recall, but I'll get the exact query the next time it happens.

Ben_S

Ok, will be a lot easier to diagnose with some info about the query.
Liverpool FC Forum with 14 million+ posts.

Christian A. Herrnboeck

When the load spikes, log into SSH, and go to the MySQL command line program.

Enter this query:

show processes;

And paste the output ;)


Regards,
Christian


Farmers:Producing food for the world!

Mike Bobbitt

Thanks Christian, I'll post the output here. Still waiting for it to recur, it seems that now that I've finally posted about it, it's taking a break. :)


Cheers
Mike

Christian A. Herrnboeck

Sounds like a tooth that hurts... till you go to the dentist. When you leave his office, it starts hurting again!

-Christian


Farmers:Producing food for the world!

Joshua Dickerson

Why don't you use an accelerator? You have a fairly large forum and there are many performance advantages from having an accelerator and caching. With 2 GB of RAM, you shouldn't be much worried about that ;)
Come work with me at Promenade Group



Need help? See the wiki. Want to help SMF? See the wiki!

Did you know you can help develop SMF? See us on Github.

How have you bettered the world today?

Mike Bobbitt

Still waiting for the php-eaccelerator RPM for PHP 5.1.6. :)

Mike Bobbitt

#9
Good news... (sort of)

The problem recurred today and I collected some actual info. Below is SHOW PROECSSLIST; from MySQL:

http://army.ca/deleteme/show_processlist.txt

Here's a static copy of the status.php from the same period:

[GONE]

I'm looking through it now, but if anyone has any comments or advice, I'd appreciate it. The first thing I noticed is that there are no locked connections as I previously noted... not sure if this is a different problem or if I just caught it at a different stage, but they were definitely locked when I witnessed this before.


Thanks!

Mike

Ben_S

Have you got automatically optimize tables enabled, if so turn it off. One of the tables is being optimized there and that probably isn't helping.
Liverpool FC Forum with 14 million+ posts.

Mike Bobbitt

It was on, is now off. Thanks! I'll see if it occurs again. It *does* look highly suspect since it is the connection that has been running the longest. (I.E. the problem started near that time.)

Joshua Dickerson

log_online appears to be getting backed up. A lot of queries for that taking quite a while. Is it InnoDB?
Come work with me at Promenade Group



Need help? See the wiki. Want to help SMF? See the wiki!

Did you know you can help develop SMF? See us on Github.

How have you bettered the world today?

Mike Bobbitt

Yep, it's InnoDB. Do you mean all the DELETE queries?

That table currently only has 281 entries, which is probably pretty representative of the load.

Joshua Dickerson

Come work with me at Promenade Group



Need help? See the wiki. Want to help SMF? See the wiki!

Did you know you can help develop SMF? See us on Github.

How have you bettered the world today?

Oldiesmann

Quote from: Mike Bobbitt on September 22, 2006, 11:02:31 AM
Still waiting for the php-eaccelerator RPM for PHP 5.1.6. :)

I'd suggest just going with APC instead. It works great with PHP 5.1.6 and did wonders for the server load at the Run 2 Roxette forums.

If you're interested let me know and I'll give you more instructions on installing it.
Michael Eshom
Christian Metal Fans

Mike Bobbitt


Mike Bobbitt

#17
Ok, here's another status page from when the server keeled over:

[GONE]

The "oldest" connection appears to be 517 seconds old and a fairly straightforward SELECT command... anyone see something I missed?

Hmm, just noticed that it's pretty much out of memory and draining swap pretty heavily too. :(

Ben_S

#18
What does an explain show on that?

EXPLAIN SELECT
   lo.ID_MEMBER, lo.logTime, mem.realName, mem.memberName, mem.showOnline,
   mg.onlineColor, mg.ID_GROUP
FROM `smf`.smf_log_online AS lo
   LEFT JOIN `smf`.smf_members AS mem ON (mem.ID_MEMBER = lo.ID_MEMBER)
   LEFT JOIN `smf`.smf_membergroups AS mg ON (mg.ID_GROUP = IF(mem.ID_GROUP = 0, mem.ID

Also yes, your swap is very high, will have a look at your real status.php.

Can't see anything in your MySQL config (although posting your my.cnf would help see) that would cause that memory usage, do you have any other apps running that may be a tad hungry?
Liverpool FC Forum with 14 million+ posts.

Mike Bobbitt

Thanks Ben. my.cnf:

[mysqld]
# EMERGENCY RECOVERY
#innodb_force_recovery = 4

default_character-set=utf8
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
innodb_data_file_path = ibdata1:10M:autoextend
#set-variable = innodb_additional_mem_pool_size=40M

# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1

# Added
query_cache_type=1
query_cache_limit=3M
query_cache_size=64M
thread_concurrency=4
max_connections=500
thread_cache_size=40
key_buffer=64M
key_buffer_size=256M
join_buffer=1M
max_allowed_packet=16M
table_cache=2048
sort_buffer_size=768K
read_buffer_size=512K
read_rnd_buffer_size=512K
myisam_sort_buffer_size=64M
skip-locking
skip-external-locking

# Turn off networking
skip-networking

[mysql.server]
user=mysql
basedir=/var/lib

# Turn off networking
skip-networking

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

# Turn off networking
skip-networking


EXPLAIN SELECT lo.ID_MEMBER, lo.logTime, mem.realName, mem.memberName, mem.showOnline, mg.onlineColor, mg.ID_GROUP
FROM `smf`.smf_log_online AS lo
LEFT JOIN `smf`.smf_members AS mem ON ( mem.ID_MEMBER = lo.ID_MEMBER )
LEFT JOIN `smf`.smf_membergroups AS mg ON ( mg.ID_GROUP = IF( mem.ID_GROUP =0, mem.ID_POST_GROUP, mem.ID_GROUP ) ) ;

results with:

id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE lo index NULL online 7 NULL 291 Using index
1 SIMPLE mem eq_ref PRIMARY PRIMARY 3 smf.lo.ID_MEMBER 1
1 SIMPLE mg eq_ref PRIMARY PRIMARY 2 func 1


No idea what that all means, I'm new to the EXPLAIN verb.


Thanks!
Mike

Ben_S

Are you running anything else, you are eating a lot of swap there for some reason but it doesn't seem to be being caused by MySQL.

The explain basically shows what indexs are used for the query, it looks fine to me.

Try running the actual query itself and see how long it takes

SELECT
   lo.ID_MEMBER, lo.logTime, mem.realName, mem.memberName, mem.showOnline,
   mg.onlineColor, mg.ID_GROUP
FROM `smf`.smf_log_online AS lo
   LEFT JOIN `smf`.smf_members AS mem ON (mem.ID_MEMBER = lo.ID_MEMBER)
   LEFT JOIN `smf`.smf_membergroups AS mg ON (mg.ID_GROUP = IF(mem.ID_GROUP = 0, mem.ID

I'd guess it will be fast though as I think the issue is somewhere else.
Liverpool FC Forum with 14 million+ posts.

Advertisement: