MySQL Forums :: MyISAM :: is read block write?
Re: is read block write?
Posted by: h ch ()
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 |
|---|---|---|---|
| is read block write? | 1634 | h ch | 10/10/2011 01:02AM |
| Re: is read block write? | 747 | Rick James | 10/12/2011 06:05AM |
| Re: is read block write? | 516 | h ch | 10/18/2011 11:50PM |
| Re: is read block write? | 624 | Rick James | 10/20/2011 10:54PM |
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.
© 2013, Oracle Corporation and/or its affiliates