Login / Register
Developer ZoneBugs HomeReport a bugStatisticsAdvanced searchSaved searchesTags
Bug #44841 MySQL hanged on long semaphore wait (X-lock on RW-latch)
Submitted: 13 May 2009 11:39 Modified: 8 Sep 2009 23:02
Reporter: Nickolay Vinogradov
Status: Open
Category: Server: InnoDB Severity: S2 (Serious)
Version: 5.1.34-0 OS: Linux (SUSE Linux Enterprise Server 10 SP2 (x86_64))
Assigned to: Target Version:
Tags: X-lock, RW-latch dict0dict.c
ViewAdd CommentFilesDeveloperEdit SubmissionView Progress Log
[13 May 2009 11:39] Nickolay Vinogradov
Description:
Every few days MySQL hanged with the same warnings:
InnoDB: Warning: a long semaphore wait:
--Thread 1173395776 has waited at row/row0mysql.c line 1720 for 241.00 seconds the
semaphore:
X-lock on RW-latch at 0xc73b20 created in file dict/dict0dict.c line 728
a writer (thread id 1173395776) has reserved it in mode wait exclusive
number of readers 1, waiters flag 1
Last time read locked in file row/row0mysql.c line 1685
Last time write locked in file row/row0mysql.c line 1720
InnoDB: ###### Starts InnoDB Monitor for 30 secs to print diagnostic info:
InnoDB: Pending preads 0, pwrites 0
Every time thread waited at "row/row0mysql.c line 1720" or at "row/row0mysql.c line
1685", and every time "S-lock" or "X-lock" on RW-latch at 0xc73b20 created in file
dict/dict0dict.c line 728.
How to repeat:
MySQL running under high load.
Uptime: 5322 Threads: 454 Questions: 11475628 Slow queries: 19839 Opens: 1342 Flush
tables: 1 Open tables: 1335 Queries per second avg: 2156.262
All tables are InnoDB.[13 May 2009 11:40] Nickolay Vinogradov
MySQL installed from MySQL-server-community-5.1.34-0.sles10.x86_64.rpm[13 May 2009 11:45] Nickolay Vinogradov
InnoDB Monitor OutputAttachment: innodb_monitor_output.log (application/octet-stream, text), 117.05 KiB.[13 May 2009 12:19] Valeriy Kravchuk
Please, send your my.cnf file content and the results of:
df -k
Here we have purge thread in conflict the thread doing rollback of:
INSERT INTO nagios_hoststatus SET instance_id='1', host_object_id='1',
status_update_time=FROM_UNIXTIME(1242201087), ...
Please, send the results of SHOW CREATE TABLE and SHOW TABLE STATUS for this
nagios_hoststatus table also.[13 May 2009 14:29] Nickolay Vinogradov
my.cnfAttachment: my.cnf (application/octet-stream, text), 20.56 KiB.[13 May 2009 14:30] Nickolay Vinogradov
nickolay@new_db:~> df -k
Filesystem 1K-blocks Used Available Use% Mounted on
/dev/sda1 10490040 6880800 3609240 66% /
udev 8220496 104 8220392 1% /dev
/tmpfs 1048576 8 1048568 1% /tmpfs
/dev/sda2 419267584 219350508 199917076 53% /mysql[13 May 2009 14:36] Nickolay Vinogradov
mysql> SHOW TABLE STATUS WHERE name LIKE 'nagios_hoststatus';
+-------------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-------------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------------------------------+
| Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length
| Max_data_length | Index_length | Data_free | Auto_increment | Create_time |
Update_time | Check_time | Collation | Checksum | Create_options | Comment
|
+-------------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-------------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------------------------------+
| nagios_hoststatus | InnoDB | 10 | Compact | 8 | 2048 | 16384
| 0 | 16384 | 22985834496 | 566410 | 2009-03-02 22:53:47 |
NULL | NULL | utf8_general_ci | NULL | | Current host
status information |
+-------------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-------------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------------------------------+[13 May 2009 16:34] Nickolay Vinogradov
Warning and InnoDB Monitor Output(Previous Hang)Attachment: innodb_monitor_output2.log (text/x-log), 140.53 KiB.[13 May 2009 16:39] Nickolay Vinogradov
Valeriy, please look at last attached file. It's from previous hang.
If I understand correctly, now the problem with thread doing rollback of:
INSERT INTO nagios_programstatus SET instance_id='1',
status_update_time=FROM_UNIXTIME(1241899442)...[13 May 2009 17:13] Valeriy Kravchuk
OK, so this is not related to particular table (do you know what is the reason for
rollback, by the way?), but more to transaction rate maybe.
Can you, please, try to set innodb_max_purge_lag = 1000000, for example, and check if it
will influence the rate of hangs related to purge vs rollback activity. You can set it
dynamically, with SET GLOBAL. Read
http://dev.mysql.com/doc/refman/5.1/en/innodb-parameters.html#sysvar_innodb_max_purge_lag
for the details.
Side note: I had noted memlock in your my.cnf. Please, send the results of:
free
Linux command.[13 May 2009 17:27] Nickolay Vinogradov
new_db:/home/nickolay # free
total used free shared buffers cached
Mem: 16440996 16346032 94964 0 99936 1825508
-/+ buffers/cache: 14420588 2020408[13 May 2009 17:43] Valeriy Kravchuk
Not that much of free memory left. Can you try also to remove memlock option and check if
this will influence the situation in any way?[18 May 2009 19:08] Nickolay Vinogradov
MySQL hanged again.
But now without "long semaphore error".
Let me to upload innodb monitor output.[18 May 2009 19:10] Nickolay Vinogradov
Innodb monitor outputAttachment: innodb_monitor_output3.txt (text/plain), 62.52 KiB.[7 Jun 2009 21:58] Nickolay Vinogradov
Problem repeated again and again even after we replace hardware.
We are delete memlock, and add innodb_max_purge_lag = 1000000, but it isn't help.
Any idea?[7 Jun 2009 22:00] Nickolay Vinogradov
Last innodb monitor outputAttachment: innodb.out.333 (application/octet-stream, text), 62.52 KiB.[30 Jun 2009 19:56] Valeriy Kravchuk
Bug #45813 was marked as a duplicate of this one. It contains newer error log and shows
that we get assertion failure eventually as a result of a long wait.[30 Jun 2009 20:01] Nickolay Vinogradov
I have increase innodb_max_purge_lag to 10000000, and decrease innodb_thread_concurrency
from 32 to 16. And problem has occured today.
Valeriy, do you think is the problem with thread concurrency?[30 Jun 2009 21:04] Nickolay Vinogradov
Problem happens again!
Even after I have decreased innodb_thread_concurrency to 8.
Valeriy, How are you going to fix this problem?[8 Sep 2009 22:32] James Day
Nickolay, it's just a symptom of heavy load. It's one of the ways that extreme heavy
disk-bound load shows up. In this case the main symptom is the data dictionary operations
that involve waiting for disk I/O.dict/dict0dict.c and row/row0mysql.c are those. InnoDB
doesn't expect to wait hundreds of seconds for disk I/O so when it does the watchdog
process notices and starts printing diagnostic output. Sometimes the wait gets so long
that it concludes that InnoDB must be hung and restarts the server.
The most common way to encounter data dictionary operations is creating and deleting
temporary tables but renaming and some other operations can also take the global data
dictionary mutex.
One way a thread can be waiting for itself is if it takes a lock and then another thread
takes a lock. If it then accesses the row again it's possible that the order of requests
in the list of threads waiting for locks can cause it to block on itself. It's rare and
not easy to fix so it probably won't be fixed in 5.1. In your case it's probably just
normal locking and high load, not this bug.
The purge thread is known to cause performance pain when it's busy. That's for two
reasons:
1. It uses a lot of disk I/O.
2. innodb_max_purge_lag is usually not set, so it can grow larger than can be cached in
the buffer pool and become even more disk-bound.
Setting innodb_max_purge_lag to a smaller value, perhaps 100,000, might help to give the
purge thread more of a chance to keep up.
Look at performance tuning and you should be able to solve the problem. You could also
try the 5.4 preview release. That has additional tuning settings and some improved
default capabilities that improve throughput. It's not officially released for production
use yet but those people who do use it tend to have good results.
James Day, MySQL Senior Support Engineer, Sun Microsystems, England.[8 Sep 2009 23:02] Nickolay Vinogradov
Hello James,
The problem resolved after downgrading to MySQL 5.0.x.© 1995-2008 MySQL AB, 2008-2010 Sun Microsystems, Inc.
Privacy PolicyContact Us
Portions of this website are copyright © 2001, 2002 The PHP Group.
Page generated in 0.126 sec. using MySQL 5.1.30-log
Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.
分享到:
相关推荐
MySQL Crash Course 原版CHM,PDF版本都是由这个CHM转换过去的。
mysql必知必会 MySQL.Crash.Course mysql领域的经典著作
基于docker搭建的《MySQL Crash Course》学习实操环境
我们生产环境有一组集群的多台MySQL服务器(MySQL 5.6.21),不定期的会crash,但error log中只记录了重启信息,未记录crash时的堆栈: mysqld_safe Number of processes running now: 0 mysqld_safe mysqld ...
mysql
(Safari Books Online_Sams Teach Yourself in 10 Minutes) Forta, Ben - MySQL crash course Includes index-Sams Publishing (2005).pdf
Sams.MySQL.Crash.Course.Dec.2005.chm
中文名《MySQL必知必会》,英文原版名《MySQL Crash Course》的配套源代码
Apache, MySQL, and PHP Weekend Crash Course.PDF
的并行 MTS多级主从多级主从Crash safe半同步架构半同步架构项目要求:3.Crash safe master&slave双主 mastera&mast
本书是MariaDB的作者亲自书写的MySQL数据库使用指南,条理十分清晰,也很容易看懂,适合入门的同学。
MySQL Crash Course, 2nd Edition 检索和排序数据 使用比较、正则表达式、全文搜索等过滤数据 连接关系数据 创建和更改表 插入、更新和删除数据 利用存储过程和触发器的强大功能 使用视图和游标 管理事务处理 创建...
3. Crash safe master&slave 1.使用中间件连接数据库 2.mastera出故障后能够自动故障转移
MySQL数据库系统监控 JDBC SQL日志记录工具P6spy P6spy的安装、配置(以Jboss3.2为例) DBMonster的安装和配置 DBMonster命令示例-提取Schema 在以源码方式安装的MySQL系统环境下 MySQL数据库运行情况测试 cd sql-...
MySQL 5.7 GTID MTS CrashSafeMySQL 5.7 GTID MTS CrashSafe 并发半同步复制并发半同步复制项目要求:3.Cr
Chapter 2 Storing and Retrieving Data Chapter 3 Using Arrays Chapter 4 String Manipulation and Regular Expressions Chapter 5 Reusing Code and Writing Functions Chapter 6 Object-Oriented PHP Chapter 7 ...
2 Storing and Retrieving Data 3 Using Arrays 4 String Manipulation and Regular Expressions 5 Reusing Code and Writing Functions 6 Object-Oriented PHP 7 Error and Exception Handling Part II: Using ...
MySQL RDS三节点企业版的高可用体系_包括(半同步复制下主库crash多事务)
6、Making MySQL XA transactionprocessing crash safe 7、面向云原生架构的数据库引擎 8、云计算成本优化-大数据降本增效实战 9、瑞幸咖啡数据库平台与架构实践 10、MySQL HeatWave Lakehouse 11、分布式数据库必须...