MySQL Forums
Forum List  »  MyISAM

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;

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.

Options: ReplyQuote


Subject
Views
Written By
Posted
3592
October 10, 2011 01:02AM
2142
October 12, 2011 06:05AM
Re: is read block write?
1683
October 18, 2011 11:50PM
1718
October 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.