| Bug #64873 | Error: semaphore wait has lasted > 600 seconds | ||
|---|---|---|---|
| Submitted: | 5 Apr 2012 10:51 | Modified: | 24 Jan 2014 14:31 |
| Reporter: | Ruben Gaspar | Email Updates: | |
| Status: | No Feedback | Impact on me: | |
| Category: | MySQL Server: InnoDB storage engine | Severity: | S3 (Non-critical) |
| Version: | 5.5.20 | OS: | Linux (rhel 5: 2.6.18-274.7.1.el5) |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | InnoDB: Assertion failure, long wait, smaphore, srv0srv.c, X-lock | ||
[5 Apr 2012 10:51]
Ruben Gaspar
[5 Apr 2012 10:54]
Ruben Gaspar
This is my.cnf file: [mysqld] port=5500 socket=/var/lib/mysql/mysql.sock datadir=/ORA/dbs03/DRUPAL/mysql log-bin=/ORA/dbs02/DRUPAL/mysql/binlog expire_logs_days=5 binlog_format=MIXED general-log-file=/ORA/dbs03/DRUPAL/mysql/mysql.log #Control when binary logs are rotated max_binlog_size=1073741824 #IO Power innodb_read_io_thread=4 innodb_write_io_threads=4 innodb_io_capacity=200 # server-id=2 - on the slave! server-id=1 # consider setting to 2 to improve performance, 0 for extreme (not ACIDcompliant): innodb_flush_log_at_trx_commit=1 sync_binlog=1 innodb_file_per_table innodb_log_file_size=16M # additionally on slave if needed: log-slave-updates innodb_flush_method=O_DIRECT innodb_buffer_pool_size=5G #enable performance schema performance_schema # logging log_output=FILE slow_query_log=1 slow_query_log_file=/ORA/dbs03/DRUPAL/mysql/slow_queries.log # tuning: max_connections=3000 max_user_connections=300 thread_cache_size=50 table_open_cache=6000 table_definition_cache=6000 query_cache_size=768M key_buffer_size=256M innodb_open_files=700 tmp_table_size=33554432 max_heap_table_size=33554432 max_allowed_packet=64M The tables involved in first crash: mysql> show create table stomanin_dev.cache_bootstrap; | Table | Create Table | cache_bootstrap | CREATE TABLE `cache_bootstrap` ( `cid` varchar(255) NOT NULL DEFAULT '' COMMENT 'Primary Key: Unique cache ID.', `data` longblob COMMENT 'A collection of data to cache.', `expire` int(11) NOT NULL DEFAULT '0' COMMENT 'A Unix timestamp indicating when the cache entry should expire, or 0 for never.', `created` int(11) NOT NULL DEFAULT '0' COMMENT 'A Unix timestamp indicating when the cache entry was created.', `serialized` smallint(6) NOT NULL DEFAULT '0' COMMENT 'A flag to indicate whether content is serialized (1) or not (0).', PRIMARY KEY (`cid`), KEY `expire` (`expire`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Cache table for data required to bootstrap Drupal, may be...' | mysql> show indexes from cache_bootstrap from stomanin_dev; +-----------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +-----------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | cache_bootstrap | 0 | PRIMARY | 1 | cid | A | 6 | NULL | NULL | | BTREE | | | | cache_bootstrap | 1 | expire | 1 | expire | A | 6 | NULL | NULL | | BTREE | | | +-----------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ 2 rows in set (0.00 sec) Different tables were involved in second crash though the symptoms are exactly the same. I upload my error file.
[9 Apr 2012 19:03]
Ruben Gaspar
I have just uploaded the error log file of my mysql sever: ftp> put mysqldrupal_dbvrtg4030_bug64873.zip 200 PORT command successful. Consider using PASV. 150 Ok to send data. 226 File receive OK. ftp: 1048695 bytes sent in 1.98Seconds 530.98Kbytes/sec. Thanks for your time.
[24 Dec 2013 14:31]
Sveta Smirnova
Thank you for the report. I am really sorry, but error log file got removed from our FTP server due to retention policy. Please re-upload and name file as described in the "Files" tab of the bug report page. Also please note that long semaphore wait can happen in normal operations if table is locked for too much time. This usually happens during operations, such as CHECK TABLE of large table, but can also be affected by other locks.
[25 Jan 2014 1: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".
