Bug #37411 | Threads staying in the "Sending Data" for long periods of time | ||
---|---|---|---|
Submitted: | 14 Jun 2008 19:21 | Modified: | 19 Nov 2008 18:45 |
Reporter: | Robin McMillon | Email Updates: | |
Status: | No Feedback | Impact on me: | |
Category: | MySQL Server: InnoDB storage engine | Severity: | S2 (Serious) |
Version: | 5.0.51a | OS: | Solaris |
Assigned to: | Assigned Account | CPU Architecture: | Any |
[14 Jun 2008 19:21]
Robin McMillon
[15 Jun 2008 10:36]
Valeriy Kravchuk
Thank you for a problem report. How many CPUs/cores do you have? Please, send also the EXPLAIN results for the queries you mentioned. Looks like you already included all the other information in the .rtf file attached.
[16 Jun 2008 15:50]
Robin McMillon
CPUs/cores: We have 4 dual-core Intel Xeon 2.93GHz "Tigerton" CPUs I will attach the EXPLAIN results as a file since they are too long for the comment field.
[16 Jun 2008 15:51]
Robin McMillon
EXPLAIN results for queries mentioned in original report
Attachment: mysql_bug_report_attachment2.rtf (application/rtf, text), 9.10 KiB.
[17 Jun 2008 17:29]
Heikki Tuuri
Vasil, can you look at this performance problem? This could be 'thread thrashing'. 'Sending data' means that rows are read from one table to further processing. Under congestion, I think that state is a very common thread state. Regards, Heikki
[28 Jul 2008 15:00]
Knut Hellebø
I am seeing identical behaviour on a Solaris 8 system (V440). This happens when running FLEXnet Manager from Acresso along with MySQL. I currently run with innodb_thread_concurrency set to 0 and occasionally see very high load on the server. When setting this variable to 1, the load becomes OK after a while, but the processes are still running in "Sending Data" status inside MySQL. Here's OS and MySQL details: uname -a SunOS bgedsu12 5.8 Generic_117350-54 sun4u sparc SUNW,Sun-Fire-V440 +-------------------------+------------------------------+ | Variable_name | Value | +-------------------------+------------------------------+ | version | 5.0.45 | | version_comment | MySQL Community Server (GPL) | | version_compile_machine | sparc | | version_compile_os | sun-solaris2.8 | +-------------------------+------------------------------+ Knut Hellebø
[7 Aug 2008 0:42]
James Day
Knut, you should experiment to find the innodb_thread_concurrency value that produces the fastest improvement. That will be your high load optimal setting. It may be higher than 1, it's worth trying 2,4,6,8,16,32 and adjusting further from the one that is best.
[19 Oct 2008 18:01]
Jiri Hlinka
my.cnf from server. most variables are changed inside running mysql server as in my-innodb-heavy-4G.cnf
Attachment: my.cnf (application/octet-stream, text), 5.44 KiB.
[19 Oct 2008 18:07]
Jiri Hlinka
result of SHOW INNODB STATUS while server is in high load.
Attachment: show-innodb-status-high-load.dump (application/octet-stream, text), 8.18 KiB.
[19 Oct 2008 18:09]
Jiri Hlinka
SHOW MUTEX STATUS result
Attachment: show-mutex-status.dump (application/octet-stream, text), 472 bytes.
[19 Oct 2008 18:45]
Valeriy Kravchuk
As this is the most "waited" mutex: | buf0buf.c | 545 | 171362 | your problem is related to buf_pool->mutex. So, smaller number of concurrent InnoDB threads, smaller buffer pool, split_buf_pool_mutex patch (search for it) or 5.1.x may help you to some extent. Please, check if the problem is repeatable with 5.1.28.
[20 Oct 2008 5:34]
Jiri Hlinka
[19 Oct 20:45] Valeriy Kravchuk: Thank you for your reply. Is it possible to solve it by increasing innodb_buffer_pool_size as the problem starts when data dont fit into memory (as described here: http://aggregator.foolab.org/node/44598)?
[30 Oct 2008 14:44]
Heikki Tuuri
Jiri, http://bugs.mysql.com/file.php?id=10466 the printout shows no contention at all inside InnoDB. Also, the file I/O is small. It is processing several huge SELECT queries and InnoDB is doing 2 million record reads per second. Does MySQL optimize the queries properly? Regards, Heikki
[30 Oct 2008 14:49]
Heikki Tuuri
Robin McMillon, can you please print SHOW INNODB STATUS\G during the slowdown? Regards, Heikki
[20 Nov 2008 0:00]
Bugs System
No feedback was provided for this bug for over a month, so it is being suspended automatically. If you are able to provide the information that was originally requested, please do so and change the status of the bug back to "Open".