| Bug #29821 | MySQL hangs forever while "sending data" | ||
|---|---|---|---|
| Submitted: | 16 Jul 2007 15:25 | Modified: | 17 Jan 2008 10:07 |
| Reporter: | Vadim Smelyansky | ||
| Status: | Closed | ||
| Category: | Server: General | Severity: | S2 (Serious) |
| Version: | 5.0.45 | OS: | Linux (CentOS5 x86_64) |
| Assigned to: | Target Version: | ||
| Tags: | regression | ||
[16 Jul 2007 19:31]
Miguel Solorzano
Thank you for the bug report. Could you please provide the dump file to populate the tables?. Thanks in advance.
[17 Jul 2007 9:44]
Vadim Smelyansky
Unfortunately I can not provide database content. After playing with MySQL settings a little I got this query working - now it takes about 50min current /etc/my.cnf following: ------------------------------------------------------------- [mysqld] datadir = /opt/mdb/mysql max_connections=250 connect_timeout=30 key_buffer = 128M max_allowed_packet = 64M table_cache = 400 sort_buffer_size = 4M tmp_table_size = 32M read_buffer_size = 4M read_rnd_buffer_size=4M myisam_sort_buffer_size = 64M open-files-limit=8192 net_buffer_length=4K net_read_timeout=300 net_write_timeout=600 table_lock_wait_timeout=500 thread_cache = 16 query_cache_size = 1M # Try number of CPU's*2 for thread_concurrency thread_concurrency = 8 big-tables innodb_flush_log_at_trx_commit=2 innodb_log_buffer_size=16M innodb_log_file_size=5242880 innodb_buffer_pool_size=1024M default-table-type=InnoDB # # Replication settings # server-id = 1275659283 log-bin = /var/log/mysql/bin.log log-bin-index = /var/log/mysql/log-bin.index log-error = /var/log/mysql/error.log log-slow-queries=/var/log/mysql/slow-queries.log long_query_time=30 log_queries_not_using_indexes=1 log_warning=2 relay-log = /var/log/mysql/relay.log relay-log-info-file = /var/log/mysql/relay-log.info relay-log-index = /var/log/mysql/relay-log.index expire_logs_days = 7 #Use compression on master/slave protocol. slave_compressed_protocol
[17 Jul 2007 11:09]
Valeriy Kravchuk
Please, send the EXPLAIN results for the query and SHOW TABLE STATUS results for all the tables used.
[17 Jul 2007 13:13]
Vadim Smelyansky
I did another experiment and copy database to next door server where version 5.0.41
installed - query took 16min which is big difference from 58min on 5.0.45
below is explain for this query:
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: adv_pages_free
type: index
possible_keys: NULL
key: randomized
key_len: 9
ref: NULL
rows: 1438285
Extra: Using index
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: adv_pages
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: lm_new.adv_pages_free.adv_page_id
rows: 1
Extra:
*************************** 3. row ***************************
id: 1
select_type: SIMPLE
table: adv_areas
type: ref
possible_keys: uniq_area_id,promoted_type
key: uniq_area_id
key_len: 4
ref: lm_new.adv_pages.adv_page_id
rows: 4
Extra:
*************************** 4. row ***************************
id: 1
select_type: SIMPLE
table: adv_hosts
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: lm_new.adv_pages.hostid
rows: 1
Extra:
----------------------------------------------
SHOW TABLE STATUS for each table in query following:
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: adv_pages_free
type: index
possible_keys: NULL
key: randomized
key_len: 9
ref: NULL
rows: 1438285
Extra: Using index
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: adv_pages
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: lm_new.adv_pages_free.adv_page_id
rows: 1
Extra:
*************************** 3. row ***************************
id: 1
select_type: SIMPLE
table: adv_areas
type: ref
possible_keys: uniq_area_id,promoted_type
key: uniq_area_id
key_len: 4
ref: lm_new.adv_pages.adv_page_id
rows: 4
Extra:
*************************** 4. row ***************************
id: 1
select_type: SIMPLE
table: adv_hosts
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: lm_new.adv_pages.hostid
rows: 1
Extra:
*************************** 1. row ***************************
Name: adv_pages
Engine: MyISAM
Version: 10
Row_format: Dynamic
Rows: 6186980
Avg_row_length: 104
Data_length: 646156000
Max_data_length: 281474976710655
Index_length: 509118464
Data_free: 0
Auto_increment: 21777936
Create_time: 2007-04-30 20:34:24
Update_time: 2007-07-17 10:20:09
Check_time: 2007-07-17 10:30:22
Collation: latin1_swedish_ci
Checksum: NULL
Create_options:
Comment:
*************************** 1. row ***************************
Name: adv_areas
Engine: MyISAM
Version: 10
Row_format: Dynamic
Rows: 21570410
Avg_row_length: 32
Data_length: 708646668
Max_data_length: 281474976710655
Index_length: 2921625600
Data_free: 0
Auto_increment: NULL
Create_time: 2007-06-14 10:45:33
Update_time: 2007-07-17 01:44:31
Check_time: 2007-07-17 10:31:24
Collation: latin1_swedish_ci
Checksum: NULL
Create_options:
Comment:
*************************** 1. row ***************************
Name: adv_hosts
Engine: MyISAM
Version: 10
Row_format: Dynamic
Rows: 29333
Avg_row_length: 105
Data_length: 3092392
Max_data_length: 281474976710655
Index_length: 2875392
Data_free: 0
Auto_increment: 100406
Create_time: 2007-07-15 10:24:18
Update_time: 2007-07-17 10:19:03
Check_time: 2007-07-17 10:31:24
Collation: latin1_swedish_ci
Checksum: NULL
Create_options:
Comment:
[25 Jul 2007 23:06]
Valeriy Kravchuk
Please, send the results of SHOW TABLE STATUS for the tables involved from both 5.0.45 and 5.0.41. Have you ran ANALYZE TABLE for tables involved on 5.0.45 recently? Please, send also query plan from 5.0.41 (where query runs faster).
[6 Aug 2007 18:00]
John Henry
I've experienced the bug where MySQL hangs forever in "sending data" but not deterministically. I run a single query tens of thousands of times before the actual error occurs, and the error seems to not happen on any one specific query. When I've experienced the error, MySQL has always had an uptime of more than a week on the same set of queries with no error. Unfortunately, when this error happens, the next update that occurs on the table that has a query stuck in "sending data" destroys the database because the update is locked and all subsequent selects cannot complete.
[20 Aug 2007 12:38]
Vadim Smelyansky
Unfortunately repro just gone after a few uninstall/install of different versions (5.0.41,5.0.45,i386,x86_64) of MySQL and linux packages update
[12 Nov 2007 11:55]
Valeriy Kravchuk
So, can anybody repeat similar behaviour for 5.0.45?
[13 Nov 2007 1:08]
Vadim Smelyansky
Current state on our server unstable, queries on big tables (millions of records) slow down in 10 times other application with short queries on small tables (23minutes instead 3 minutes). Sometime (once in 3-4 days) MySQL loack up the server and only reset button can help. That what happened today and 5 tables was lost after crash. thank god we have daily backup and binary logs to restore it. Our hosting provider suppose that instability caused by PAE linux kernel which we installed after RAM upgrade. So we planning to migrate this server to x86_64 bit Linux version.
[13 Nov 2007 5:41]
Valeriy Kravchuk
Please, inform about any results with x86_64. Upload SHOW INNODB STATUS results for one of this slowdown moments.
[14 Dec 2007 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".
[17 Jan 2008 10:07]
Vadim Smelyansky
Problem just disappeared after moving from Fedora6 i386 to CentOS5 x86_64 platform
Description: MySQL hangs forever while "sending data" on query with chained joins. Database contained about 3 million records joined tables contains about 200000 Looks like regression - same query was work on 5.0.37 (took around 1h) Running this query with LIMIT like LIMIT 600000,30 succeed to return data How to repeat: following query always stuck for infinity: SELECT adv_pages_free.adv_page_id,CONCAT('',randomized) as rnd, adv_areas.adv_page_id, adv_areas.promoted_type, adv_areas.area_id, adv_areas.promoted_id, adv_pages.industry_id, adv_pages.filename, adv_pages.hostid as hout, adv_hosts.ip as ipout, adv_hosts.classc as cout, adv_hosts.hostname, adv_hosts.g_known, adv_hosts.y_known, adv_hosts.m_known, adv_hosts.g_banned, adv_hosts.y_banned, adv_hosts.m_banned FROM adv_pages_free LEFT JOIN adv_pages ON adv_pages.adv_page_id=adv_pages_free.adv_page_id LEFT JOIN adv_areas ON adv_pages.adv_page_id=adv_areas.adv_page_id LEFT JOIN adv_hosts ON adv_pages.hostid=adv_hosts.hostid ORDER BY randomized,adv_pages_free.adv_page_id tables structure is: adv_pages_free | CREATE TABLE `adv_pages_free` ( `adv_page_id` int(11) NOT NULL default '0', `randomized` int(11) unsigned default NULL, PRIMARY KEY (`adv_page_id`), KEY `randomized` (`randomized`,`adv_page_id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 adv_pages | CREATE TABLE `adv_pages` ( `adv_page_id` int(11) NOT NULL auto_increment, `hostid` int(11) NOT NULL default '0', `uri` varchar(255) NOT NULL default '', `industry_id` smallint(4) NOT NULL default '0', `theme` varchar(255) default NULL, `filename` varchar(36) default NULL, `committed` timestamp NOT NULL default '0000-00-00 00:00:00', `commit_id` int(11) NOT NULL default '0', `nlinks` int(11) NOT NULL default '0', `keyword` text, PRIMARY KEY (`adv_page_id`), UNIQUE KEY `uniq_page_id` (`hostid`,`uri`), KEY `page_id1` (`hostid`,`adv_page_id`,`uri`,`industry_id`) ) ENGINE=MyISAM AUTO_INCREMENT=21777537 DEFAULT CHARSET=latin1 adv_areas | CREATE TABLE `adv_areas` ( `adv_page_id` int(11) NOT NULL default '0', `area_id` tinyint(4) NOT NULL default '1', `sentence_id` int(11) NOT NULL default '0', `anchor_text` varchar(255) NOT NULL default '', `promoted_id` int(11) NOT NULL default '0', `promoted_type` tinyint(1) NOT NULL default '2', `crawlMask` tinyint(4) NOT NULL default '0', UNIQUE KEY `uniq_area_id` (`adv_page_id`,`area_id`), KEY `promoted_id` (`promoted_type`,`promoted_id`), KEY `promoted_type_2` (`promoted_type`), KEY `promoted_type` (`adv_page_id`,`promoted_type`,`area_id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 adv_hosts | CREATE TABLE `adv_hosts` ( `hostid` int(11) NOT NULL auto_increment, `hostname` varchar(255) default NULL, `rev_hostname` varchar(255) default NULL, `port` smallint(6) NOT NULL default '80', `ip` varchar(50) default NULL, `classc` int(11) NOT NULL default '0', `oldip` varchar(50) default NULL, `link_industry_id` int(11) default '18', `g_known` tinyint(1) default '0', `y_known` tinyint(1) default '0', `m_known` tinyint(1) default '0', `g_banned` tinyint(1) default '0', `y_banned` tinyint(1) default '0', `m_banned` tinyint(1) default '0', `customized` tinyint(1) NOT NULL default '0', `modified` datetime default NULL, PRIMARY KEY (`hostid`), UNIQUE KEY `hostname` (`hostname`,`port`), KEY `ip` (`ip`), KEY `iphostid` (`ip`,`hostid`), KEY `rev_host_name` (`rev_hostname`) ) ENGINE=MyISAM AUTO_INCREMENT=100404 DEFAULT CHARSET=latin1