Re: is read block write?
Posted by: h ch
Date: October 18, 2011 11:50PM
Date: October 18, 2011 11:50PM
thanks James
i read the doc,now i know why,a long running select on table trade,then sql from master come (change the data of table trade ),it was blocked by long running select
,and many quick select also come after the update sql,they all were blocked by the update sql,so bring a big compact .
here is the long running ,and i find index on pubDate is not helpfull
# Query_time: 23 Lock_time: 0 Rows_sent: 45 Rows_examined: 120460
SET timestamp=1319005681;
select id,product,cas_no,company,post_date,pubDate,mtype_en,poster,cate1 from sell where 1 and pubDate >= date_format(date_add(now(),interval -14 day),'%Y-%m-%d') order by pub
Date desc,mtype_en desc,post_date desc limit 0,45;
Edited 1 time(s). Last edit at 10/19/2011 01:48AM by h ch.
i read the doc,now i know why,a long running select on table trade,then sql from master come (change the data of table trade ),it was blocked by long running select
,and many quick select also come after the update sql,they all were blocked by the update sql,so bring a big compact .
here is the long running ,and i find index on pubDate is not helpfull
# Query_time: 23 Lock_time: 0 Rows_sent: 45 Rows_examined: 120460
SET timestamp=1319005681;
select id,product,cas_no,company,post_date,pubDate,mtype_en,poster,cate1 from sell where 1 and pubDate >= date_format(date_add(now(),interval -14 day),'%Y-%m-%d') order by pub
Date desc,mtype_en desc,post_date desc limit 0,45;
mysql> show create table trade\G *************************** 1. row *************************** Table: trade Create Table: CREATE TABLE `trade` ( `id` int(11) NOT NULL auto_increment, `sid` varchar(32) collate utf8_unicode_ci NOT NULL default '', `poster` varchar(20) collate utf8_unicode_ci default NULL, `editor` varchar(20) collate utf8_unicode_ci default NULL, `edit_date` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, `post_date` timestamp NOT NULL default '0000-00-00 00:00:00', `pubDate` date default NULL, `webSite` varchar(60) collate utf8_unicode_ci default NULL, `http` varchar(100) collate utf8_unicode_ci default NULL, `status` int(4) NOT NULL default '0' `rank` int(11) default '9950', `quality` int(11) default '0', `cate` varchar(50) collate utf8_unicode_ci NOT NULL default '', `cate1` varchar(50) collate utf8_unicode_ci default NULL, `cate2` varchar(50) collate utf8_unicode_ci default NULL, `cate4` varchar(50) collate utf8_unicode_ci default NULL, `cate6` varchar(50) collate utf8_unicode_ci default NULL, `cate7` varchar(50) collate utf8_unicode_ci default NULL, `tag` varchar(150) collate utf8_unicode_ci default NULL, `type` varchar(4) collate utf8_unicode_ci NOT NULL, `title` varchar(150) collate utf8_unicode_ci default NULL, `product` varchar(100) collate utf8_unicode_ci default NULL, `product_en` varchar(100) collate utf8_unicode_ci default NULL, `cas_no` varchar(20) collate utf8_unicode_ci default NULL, `spec` text collate utf8_unicode_ci, `link` varchar(255) collate utf8_unicode_ci default NULL, `category` varchar(20) collate utf8_unicode_ci default NULL, `subcategory` varchar(20) collate utf8_unicode_ci default NULL, `region` varchar(20) collate utf8_unicode_ci default NULL, `expiryDate` int(11) default '30', `price` varchar(200) collate utf8_unicode_ci default NULL, `units` varchar(10) collate utf8_unicode_ci default NULL, `packing` varchar(100) collate utf8_unicode_ci default NULL, `produce_area` varchar(100) collate utf8_unicode_ci default NULL, `provide_area` varchar(100) collate utf8_unicode_ci default NULL, `brand` varchar(100) collate utf8_unicode_ci default NULL, `company` varchar(100) collate utf8_unicode_ci default NULL, `property` varchar(20) collate utf8_unicode_ci default NULL, `state` varchar(20) collate utf8_unicode_ci default NULL, `intro` text collate utf8_unicode_ci, `contact` varchar(50) collate utf8_unicode_ci default NULL, `tel` varchar(50) collate utf8_unicode_ci default NULL, `fax` varchar(50) collate utf8_unicode_ci default NULL, `zip` varchar(20) collate utf8_unicode_ci default NULL, `mobile` varchar(50) collate utf8_unicode_ci default NULL, `email` varchar(50) collate utf8_unicode_ci default NULL, `address` varchar(100) collate utf8_unicode_ci default NULL, `company_http` varchar(50) collate utf8_unicode_ci default NULL, `keywords` varchar(100) collate utf8_unicode_ci default NULL, `pic_name` varchar(20) collate utf8_unicode_ci default NULL, `pic_name1` varchar(100) collate utf8_unicode_ci default NULL, `image` varchar(200) collate utf8_unicode_ci default NULL, `data_flag` tinyint(4) default '0', `ip_address` varchar(20) collate utf8_unicode_ci default NULL, `additional` text collate utf8_unicode_ci, `channel` varchar(20) collate utf8_unicode_ci default NULL, `hobby` varchar(25) collate utf8_unicode_ci default NULL, `tosmartmail` int(2) default NULL, `tomail_date` varchar(30) collate utf8_unicode_ci default NULL, `trade_flag` varchar(20) collate utf8_unicode_ci default NULL, `old_id` int(11) default NULL, `find_flag` int(4) default '0', `do_again_flag` smallint(2) NOT NULL default '0' , PRIMARY KEY (`id`), UNIQUE KEY `sid` (`sid`), KEY `category` (`category`), KEY `pubDate` (`pubDate`), KEY `poster` (`poster`), KEY `product` (`product`), KEY `post_date` (`post_date`), KEY `type` (`type`,`category`), KEY `state` (`state`), KEY `price` (`price`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci 1 row in set (0.01 sec) mysql> show table status like 'trade'\G *************************** 1. row *************************** Name: trade Engine: MyISAM Version: 10 Row_format: Dynamic Rows: 1785420 Avg_row_length: 2542 Data_length: 5959082744 Max_data_length: 281474976710655 Index_length: 426995712 Data_free: 1419788748 Auto_increment: 10226593 Create_time: 2009-12-17 12:55:40 Update_time: 2011-10-19 15:31:47 Check_time: 2011-04-20 11:00:59 Collation: utf8_unicode_ci Checksum: NULL Create_options: Comment: 1 row in set (0.00 sec) mysql> explain select id,product,cas_no,company,post_date,pubDate,mtype_en,poster,cate1 from sell where 1 and pubDate >= date_format(date_add(now(),interval -14 day),'%Y-%m-%d') order by pubDate desc,mtype_en desc,post_date desc limit 0,45; +----+-------------+-------+-------+---------------+---------+---------+------+--------+-----------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+---------------+---------+---------+------+--------+-----------------------------+ | 1 | SIMPLE | sell | range | pubDate | pubDate | 4 | NULL | 231653 | Using where; Using filesort | +----+-------------+-------+-------+---------------+---------+---------+------+--------+-----------------------------+ 1 row in set (0.00 sec) mysql> SHOW VARIABLES LIKE '%buffer%'; +-------------------------------+------------+ | Variable_name | Value | +-------------------------------+------------+ | bdb_log_buffer_size | 335872 | | bulk_insert_buffer_size | 8388608 | | innodb_buffer_pool_awe_mem_mb | 0 | | innodb_buffer_pool_size | 8388608 | | innodb_log_buffer_size | 1048576 | | join_buffer_size | 67104768 | | key_buffer_size | 1073741824 | | myisam_sort_buffer_size | 134217728 | | net_buffer_length | 16384 | | preload_buffer_size | 32768 | | read_buffer_size | 67104768 | | read_rnd_buffer_size | 262144 | | sort_buffer_size | 134217720 | +-------------------------------+------------+ mysql> \! free -m total used free shared buffers cached Mem: 8192 8007 184 0 21 6250 -/+ buffers/cache: 1735 6456 Swap: 1906 0 1906 mysql> show index from trade; +-------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +-------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ | trade | 0 | PRIMARY | 1 | id | A | 1785488 | NULL | NULL | | BTREE | NULL | | trade | 0 | sid | 1 | sid | A | 1785488 | NULL | NULL | | BTREE | NULL | | trade | 1 | category | 1 | category | A | 6516 | NULL | NULL | YES | BTREE | NULL | | trade | 1 | pubDate | 1 | pubDate | A | 920 | NULL | NULL | YES | BTREE | NULL | | trade | 1 | poster | 1 | poster | A | 63767 | NULL | NULL | YES | BTREE | NULL | | trade | 1 | product | 1 | product | A | 892744 | NULL | NULL | YES | BTREE | NULL | | trade | 1 | post_date | 1 | post_date | A | 892744 | NULL | NULL | | BTREE | NULL | | trade | 1 | type | 1 | type | A | 22 | NULL | NULL | | BTREE | NULL | | trade | 1 | type | 2 | category | A | 9651 | NULL | NULL | YES | BTREE | NULL | | trade | 1 | state | 1 | state | A | 105028 | NULL | NULL | YES | BTREE | NULL | | trade | 1 | price | 1 | price | A | 137345 | NULL | NULL | YES | BTREE | NULL | +-------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
Edited 1 time(s). Last edit at 10/19/2011 01:48AM by h ch.
Subject
Views
Written By
Posted
Sorry, you can't reply to this topic. It has been closed.
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.