| Bug #51153 | mysqld/I_S takes hours to finish after boot/restart | ||
|---|---|---|---|
| Submitted: | 12 Feb 2010 21:55 | Modified: | 4 Mar 2010 23:11 |
| Reporter: | P C | Email Updates: | |
| Status: | Duplicate | Impact on me: | |
| Category: | MySQL Server | Severity: | S2 (Serious) |
| Version: | 5.1.43-0.dotdeb.1 | OS: | Linux (2.6.31-19-generic #56-Ubuntu) |
| Assigned to: | CPU Architecture: | Any | |
[12 Feb 2010 22:05]
P C
I forgot to add that his is on a design (not live) server, and we are willing to take whatever action is necessary to fix this, even intrusive actions. Here is the contents of my.cnf ======================================== /home/dev# cat /etc/mysql/my.cnf | grep -v '#' [client] port = 3306 socket = /var/run/mysqld/mysqld.sock [mysqld_safe] socket = /var/run/mysqld/mysqld.sock nice = -5 open_files_limit = 40000 [mysqld] user = mysql pid-file = /var/run/mysqld/mysqld.pid socket = /var/run/mysqld/mysqld.sock port = 3306 basedir = /usr datadir = /var/lib/mysql tmpdir = /tmp skip-external-locking skip-locking skip-partition bind-address = 127.0.0.1 key_buffer = 5G max_allowed_packet = 16M thread_stack = 192K thread_cache_size = 12 max_connections = 500 table_cache = 8000 table_open_cache = 8000 max_tmp_tables = 10000 tmp_table_size = 1200M max_heap_table_size = 1200M sort_buffer = 140M myisam_sort_buffer_size = 196M read_rnd_buffer_size =196M bulk_insert_buffer_size =196M query_cache_limit = 4M query_cache_size = 1600M query_cache_type = 1 expire_logs_days = 10 max_binlog_size = 100M [mysqldump] quick quote-names max_allowed_packet = 16M [mysql] [isamchk] key_buffer = 164M sort_buffer = 164M read_buffer = 164M write_buffer = 164M
[13 Feb 2010 7:17]
Valeriy Kravchuk
Thank you for the problem report. Please, check if the same happens with recent version, 5.1.43 (MySQL binaries).
[15 Feb 2010 11:02]
P C
We will continue to attempt to install 5.1.43 but none attempts to install the binaries in Ubuntu have succeded. If you can provide a procedure other than the one in the 5.1 manual it would speed up the process.
[15 Feb 2010 20:52]
Sveta Smirnova
Thank you for the feedback. Which exact procedure do you use? Could you please provide a link? Have you tried to install from generic binaries as described at http://dev.mysql.com/doc/refman/5.1/en/installing-binary.html?
[15 Feb 2010 23:45]
P C
So far on 5.1.43 "SHOW /*!50002 FULL*/ TABLES" took 4300 seconds (1hr 11min) to finish. At present its checking tables for upgrade. I'll post other times over the next few hours as it comes up. mysql> select version(); +-------------------+ | version() | +-------------------+ | 5.1.43-0.dotdeb.1 | +-------------------+ 1 row in set (0.00 sec) mysql> SHOW STATUS LIKE '%uptime%'; +---------------------------+-------+ | Variable_name | Value | +---------------------------+-------+ | Uptime | 4411 | | Uptime_since_flush_status | 4411 | +---------------------------+-------+ 2 rows in set (0.00 sec) mysql> show processlist; +----+------------------+-----------+--------------+---------+------+----------------------+-----------------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+------------------+-----------+--------------+---------+------+----------------------+-----------------------------+ | 5 | root | localhost | NULL | Query | 0 | NULL | show processlist | | 6 | debian-sys-maint | localhost | <name_removed> | Query | 51 | checking permissions | SHOW /*!50002 FULL*/ TABLES | +----+------------------+-----------+--------------+---------+------+----------------------+-----------------------------+ 2 rows in set (0.00 sec)
[16 Feb 2010 3:25]
P C
3 Hours after starting mysqld, still not done with TABLE_SCHEMA.
mysql> show processlist;
+-----+------------------+-----------+------+---------+------+----------------------+------------------------------------------------------------------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+-----+------------------+-----------+------+---------+------+----------------------+------------------------------------------------------------------------------------------------------+
| 7 | root | localhost | NULL | Query | 0 | NULL | show processlist |
| 185 | debian-sys-maint | localhost | NULL | Query | 4394 | checking permissions | select concat('select count(*) into @discard from `',
TABLE_SCHEMA, '`.`', TABLE |
+-----+------------------+-----------+------+---------+------+----------------------+------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
mysql> SHOW STATUS LIKE '%uptime%';
+---------------------------+-------+
| Variable_name | Value |
+---------------------------+-------+
| Uptime | 18032 |
| Uptime_since_flush_status | 18032 |
+---------------------------+-------+
2 rows in set (0.00 sec)
mysql>
[16 Feb 2010 8:46]
P C
After 10.5 hours still not done coming up. mysql> SHOW STATUS LIKE '%uptime%'; +---------------------------+-------+ | Variable_name | Value | +---------------------------+-------+ | Uptime | 37350 | | Uptime_since_flush_status | 37350 | +---------------------------+-------+ 2 rows in set (0.00 sec) mysql> show processlist; +-----+------------------+-----------+----------+---------+-------+----------------+------------------------------------------------------------------------------------------------------+ | Id | User | Host | db | Command | Time | State | Info | +-----+------------------+-----------+----------+---------+-------+----------------+------------------------------------------------------------------------------------------------------+ | 185 | debian-sys-maint | localhost | NULL | Sleep | 23731 | | NULL | | 429 | debian-sys-maint | localhost | NULL | Query | 4090 | Opening tables | select count(*) into @discard from `information_schema`.`TRIGGERS` |
[17 Feb 2010 1:04]
Itamar Reis Peixoto
may be a duplicate of http://bugs.mysql.com/bug.php?id=19588
[4 Mar 2010 23:11]
Sveta Smirnova
Thank you for the feedback. Query > select count(*) into @discard from `information_schema`.`TRIGGERS` shows you are using Debian MySQL startup script which does extra work. So this is not related to MySQL server startup itself, but rather to slow work of INFORMATION_SCHEMA. INFORMATION_SCHEMA is known have performance issues, so this can be considered duplicate of bug #19588. MySQL did several improvements lately, some of them went into upcoming 5.5 version. See comments to bug #19588. You can also open bug in Debian, so maintainers of MySQL package can tune queries.
Description: Hi have a server with about 200k tables on a dual Xeon 5500 with 24G RAM and anytime mysqld is restarted the server is slow and almost un-responsive for 3 hours until all of the I_S is done. While this is going the CPU's are all at almost 0%, and the HDD has little to no activity. After all of the "select concat('select count(*) into @discard" have finished the server works 100% normal, it just takes hours to get there. Example: TABLE_SCHEMA has taken over 31 min, I have seen it take 50+ min. Then it will take roughly the same time for PARTITIONS even though I do not use partitioning and have it disabled. COLUMNS, TRIGGERS and the DB's go by faster at about 15 min each. mysql> show processlist; +----+------------------+-----------+------+---------+------+----------------------+------------------------------------------------------------------------------------------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+------------------+-----------+------+---------+------+----------------------+------------------------------------------------------------------------------------------------------+ | 5 | debian-sys-maint | localhost | NULL | Query | 1921 | checking permissions | select concat('select count(*) into @discard from `', TABLE_SCHEMA, '`.`', TABLE | How to repeat: sudo /etc/init.d/mysqld restart mysql> show processlist; Suggested fix: The server has near 0% CPU usage, as do the hard disks until this is done. Is there a way to multi thread it?