mysql中的Waiting for tables

接着上篇中遇到的mysql子查询,在问题的诊断中,丹臣注意到一个较为严重的问题,就是我们生产库中全部的数据库访问请求都处于Waiting for tables的状态,在将大查询kill掉后,所有的请求恢复正常;简单的理解为大查询阻塞了其他访问请求,但是这个理论是不可信,如果阻塞该表的DML还可以理解,但是把该数据库上的所有请求都阻塞了,这还是说不通的。那么我们就来看看所有的请求处于Waiting for tables这个状态是什么原因导致的:

The thread got a notification that the underlying structure for a table has changed and it needs to reopen the table to get the new structure. However, to reopen the table, it must wait until all other threads have closed the table in question.

This notification takes place if another thread has used FLUSH TABLES or one of the following statements on the table in question: FLUSH TABLES tbl_nameALTER TABLERENAME TABLEREPAIR TABLEANALYZE TABLE, orOPTIMIZE TABLE.

从文档上的解释来看,是主库做了一个flush tables的操作,导致所有的表都需要打开,但是由于在10-07号放假,应该不会有人在主库上执行flush tables,而且从日志中可以看到:

1044 system user Connect 27406 Flushing tables FLUSH TABLES

所以可以判断是系统自己执行了这个操作,那么既然不是主库上执行而来,那么这个flush tables操作是从slave上复制过来的(M-M结构),

那么备库的什么操作会有flush tables,真的百思不其解,我们备库在6点之前做的是什么,后端应用的dump?还是数据库的备份?是不是xtrabackup,很有可能是xtrabackup在备份的时候做的fulsh tables,查看备份脚本,应该轮到mysqldump做逻辑备份操作了,并不是xtrabackup,检查了mysqldump的备份脚本,脚本里:

-uroot -P$port –protocol=tcp –single-transaction –master-data=2是这样的

Single-transaction这个选项是加上了的,希望再一次被打破 >_<

最后想还是想到到官网上去看看,mysqldump+flush tables是否有bug,

唉,搜索了一下果然发现了蹊跷:

http://bugs.mysql.com/bug.php?id=35157

When using the –master-data option with mysqldump, mysqldump uses a FLUSH TABLES command. However, this statement got replicated to the slave(s), which caused the slave(s) to block unnecessarily while the FLUSH tables command completed.

在5.0存在的bug很好的解释了这个问题,在mysqldump加入了–master-data就会将flush tables记录到binglog中,然后在被同步到主库,主库执行binglog后,由于有一个大查询正在

执行,这个子查询由于执行了很长时间,阻塞了flush tables的操作,最后导致了雪崩,所有的请求都被阻塞:

The thread got a notification that the underlying structure for a table has changed and it needs to reopen the table to get the new structure. However, to reopen the table,

it must wait until all other threads have closed the table in question.

综合上篇的所写,Mysqldump的在5.0的bug加上数据库的低效子查询构成这次故障的原因。

峰回路转,山穷水尽,哈哈

^_^

Posted in mysql | Leave a comment

生产库中遇到mysql的子查询

使用过oracle或者其他关系数据库的DBA或者开发人员都有这样的经验,在子查询上都认为数据库已经做过优化,能够很好的选择驱动表执行,然后在把该经验移植到mysql数据库上,但是不幸的是,mysql在子查询的处理上有可能会让你大失所望,在我们的生产系统上就由于碰到了这个问题:

select  i_id, sum(i_sell) as i_sell

from table_data

where i_id in (select i_id from table_data where Gmt_create >= ’2011-10-07 00:00:00′)

group by i_id;

(备注:sql的业务逻辑可以打个比方:先查询出10-07号新卖出的100本书,然后在查询这新卖出的100本书在全年的销量情况)。

这条sql之所以出现的性能问题在于mysql优化器在处理子查询的弱点,mysql优化器在处理子查询的时候,会将将子查询改写。通常情况下,我们希望由内到外,先完成子查询的结果,然后在用子查询来驱动外查询的表,完成查询;但是mysql处理为将会先扫描外面表中的所有数据,每条数据将会传到子查询中与子查询关联,如果外表很大的话,那么性能上将会出现问题;

针对上面的查询,由于table_data这张表的数据有70W的数据,同时子查询中的数据较多,有大量是重复的,这样就需要关联近70W次,大量的关联导致这条sql执行了几个小时也没有执行完成,所以我们需要改写sql:

SELECT t2.i_id, SUM(t2.i_sell) AS sold

FROM (SELECT distinct i_id FROM table_data

WHERE gmt_create >= ’2011-10-07 00:00:00′) t1,  table_data t2

WHERE t1.i_id = t2.i_id GROUP BY t2.i_id;

我们将子查询改为了关联,同时在子查询中加上distinct,减少t1关联t2的次数;

改造后,sql的执行时间降到100ms以内。

 

 

 

 

 

 

 

Posted in database | Leave a comment

mysql explain 中key_len的计算

今天丁原问我mysql执行计划中的key_len是怎么计算得到的,当时还没有注意,在高性能的那本书讲到过这个值的计算,但是自己看执行计划的时候一直都没有太在意这个值,更不用说深讨这个值的计算了:

ken_len表示索引使用的字节数,根据这个值,就可以判断索引使用情况,特别是在组合索引的时候,判断所有的索引字段都被查询用到。

在查看官方文档的时候,也没有发现详细的key_len的计算介绍,后来做了一些测试,在咨询了丁奇关于变长数据类型的值计算的时候,突然想到innodb 行的格式,在这里的计算中有点类似,总结一下需要考虑到以下一些情况:

(1).索引字段的附加信息:可以分为变长和定长数据类型讨论,当索引字段为定长数据类型,比如char,int,datetime,需要有是否为空的标记,这个标记需要占用1个字节;对于变长数据类型,比如:varchar,除了是否为空的标记外,还需要有长度信息,需要占用2个字节;

(备注:当字段定义为非空的时候,是否为空的标记将不占用字节)

(2).同时还需要考虑表所使用的字符集,不同的字符集,gbk编码的为一个字符2个字节,utf8编码的一个字符3个字节;

 

先看定长数据类型的一个例子(编码为gbk):

root@test 07:32:39>create table test_char(id int not null ,name_1 char(20),name_2 char(20),

-> primary key(id),key ind_name(name_1,name_2))engine=innodb charset=gbk;

root@test 07:33:55>insert into test_char values(1,’xuancan’,'taobaodba’);

root@test 07:34:55>explain select * from test_char where name_1=’xuancan’\G;

*************************** 1. row ***************************

id: 1

select_type: SIMPLE

table: test_char

type: ref

possible_keys: ind_name

key: ind_name

key_len: 41

ref: const

rows: 1

Extra: Using where; Using index

key_len=41=20*2+1(备注:由于name_1为空,isnull的标记被打上,需要计算1个字节)

root@test 07:35:31>explain select * from test_char where name_1=’xuancan’ and name_2=’taobaodba’\G;

*************************** 1. row ***************************

id: 1

select_type: SIMPLE

table: test_char

type: ref

possible_keys: ind_name

key: ind_name

key_len: 82

ref: const,const

rows: 1

Extra: Using where; Using index

key_len=82=20*2+20*2+1+1(备注:由于name_1,name_2两列被使用到,但两列都为为空,需要计算2个字节)

变长数据类型(gbk编码):

root@test 08:30:51>create table test_varchar(id int not null ,name_1 varchar(20),name_2 varchar(20),

-> primary key(id),key ind_name(name_1,name_2))engine=innodb charset=gbk;

root@test 08:37:51>insert into test_varchar values(1,’xuancan’,'taobaodba’);

root@test 08:38:14>explain select * from test_varchar where name_1=’xuancan’\G;

*************************** 1. row ***************************

id: 1

select_type: SIMPLE

table: test_varchar

type: ref

possible_keys: ind_name

key: ind_name

key_len: 43

ref: const

rows: 1

Extra: Using where; Using index

key_len=43=20*2+1+2(备注:由于为name_1字段定义为空,所以需要计算1,;同时由于是变长字段varchar,所以需要加上2)

root@test 08:38:46>alter table test_varchar modify column name_1 varchar(20) not null;

Query OK, 1 row affected (0.52 sec)

Records: 1 Duplicates: 0 Warnings: 0

root@test 08:42:11>explain select * from test_varchar where name_1=’xuancan’\G;

*************************** 1. row ***************************

id: 1

select_type: SIMPLE

table: test_varchar

type: ref

possible_keys: ind_name

key: ind_name

key_len: 42

ref: const

rows: 1

Extra: Using where; Using index

key_len=42=20*2+2(备注由于name_1字段修改为not null之后,isnull的标记锁占用的字节释放掉,但是变长字段长度所占用的2个字节没有释放);

上面是测试gbk编码的测试,同时也可以测试一下其他编码的key_len计算。

 

Posted in database | Leave a comment

innodb 的预读

innodb在io的优化上有个比较重要的特性为预读,innodb以64个page为一个extent,那么innodb的预读是以page为单位还是以extent?

这样就进入了下面的话题:linear read-ahead和randomread-ahead;

为了区分这两种预读的方式,我们可以把linear预读放到以extent为单位,而random 预读放到以extent中的page为单位;

linear 预读着眼于将下一个extent提前读取到buffer pool中,

而random预读着眼于将当前extent中的剩余的page提前读取到buffer pool 中:

linear的预读方式有一个很重要的变量控制是否将下一个extent预读到buffer pool中: innodb_read_ahead_threshold:如果一个extent中的被顺序读取的page超过或者等于该参数变量的,innodb将会异步的将下一个extent读取到buffer pool中,比如该参数的值为30,那么当该extent中有30个pages 被 sequentially的读取,则会触发innodb linear预读,将下一个extent读到内存中;在没有该变量之前,当访问到extent的最后一个page的时候,innodb会决定是否将下一个extent放入到buffer pool中;

该参数可以动态的修改:

root@(none) 09:20:02>set global innodb_read_ahead_threshold=40;

Query OK, 0 rows affected (0.00 sec)

random的预读方式则是表示当同一个extent中的一些page在buffer pool中发现时,innodb会将该extent中的剩余page一并读到buffer pool中,由于random的预读方式给innodb code带来了一些不必要的复杂性,同时在性能也存在不稳定性,在5.5中已经将这种预读方式废弃。

在监控innodb的预读时候,我们可以通过show innodb status中的 Pages read ahead和evicted without access 两个值来观察预读的情况:

或者通过两个状态值:

Innodb_buffer_pool_read_ahead 和 Innodb_buffer_pool_read_ahead_evicted.

Innodb_buffer_pool_read_ahead:表示通过预读请求到buffer pool的pages;

Innodb_buffer_pool_read_ahead_evicted:表示由于请求到buffer pool中没有被访问,而驱逐出buffer pool的pages;

root@(none) 10:19:42>show global status like ‘%read_ahead%’;

+—————————————+———+

| Variable_name | Value |

+—————————————+———+

| Innodb_buffer_pool_read_ahead | 775378 |

| Innodb_buffer_pool_read_ahead_evicted | 1888537 |

而通过show innodb status得到的 Pages read ahead 和evicted without access 则表示每秒读入和读出的pages;

Pages read ahead 1.00/s, evicted without access 9.99/s.

ref:

Reintroducing Random Readahead in InnoDB

http://dev.mysql.com/doc/innodb/1.1/en/innodb-performance-read_ahead.html

Posted in database | Leave a comment

loose index scan 优化distinct

上篇中我们提到用伪loose index scan来优化max/min,这一篇我们将用伪loose index scan来优化distinct:

有这样的一个需求:select count(distinct nick) from user_access_xx_xx;

这条sql用于统计用户访问的uv,由于单表的数据量在10G以上,即使在user_access_xx_xx上加上nick的索引,

通过查看执行计划,也为全索引扫描,sql在执行的时候,会对整个服务器带来抖动;

root@db 09:00:12>select count(distinct nick) from user_access;

+———————-+

| count(distinct nick) |

+———————-+

|               806934 |

+———————-+

1 row in set (52.78 sec)

执行一次sql需要花费52.78s,已经非常的慢了

现在需要换一种思路来解决该问题:

我们知道索引的值是按照索引字段升序的,比如我们对(nick,other_column)两个字段做了索引,那么在索引中的则是按照nick,other_column的升序排列:

我们现在的sql:select count(distinct nick) from user_access;则是直接从nick1开始一条条扫描下来,直到扫描到最后一个nick_n,

那么中间过程会扫描很多重复的nick,如果我们能够跳过中间重复的nick,则性能会优化非常多(在oracle中,这种扫描技术为loose index scan,但在5.1的版本中,mysql中还不能直接支持这种优化技术):

 

 

 

 

 

 

所以需要通过改写sql来达到伪loose index scan:

root@db 09:41:30>select count(*) from ( select distinct(nick) from user_access)t ;

| count(*) |

+———-+

|   806934 |

1 row in set (5.81 sec)

Sql中先选出不同的nick,最后在外面套一层,就可以得到nick的distinct值总和;

最重要的是在子查询中:select distinct(nick) 实现了上图中的伪loose index scan,优化器在这个时候的执行计划为Using index for group-by ,

需要注意的是mysql把distinct优化为group by,它首先利用索引来分组,然后扫描索引,对需要的nick只扫描一次;

两个sql的执行计划分别为:

优化写法:

root@db 09:41:10>explain select distinct(nick) from user_access-> ;

+—-+————-+——————————+——-+—————+————-| id | select_type | table                        | type  | possible_keys | key                             | key_len | ref  | rows    | Extra                    |

+—-+————-+——————————+——-+—————+————-

|  1 | SIMPLE      | user_access | range | NULL          | ind_user_access_nick | 67      | NULL | 2124695 | Using index for group-by |

+—-+————-+——————————+——-+—————+————-

原始写法:

root@db 09:42:55>explain select count(distinct nick) from user_access;

+—-+————-+——————————+——-+—————+————-

| id | select_type | table                        | type  | possible_keys | key                        | key_len | ref  | rows     | Extra       |

+—-+————-+——————————+——-+—————+————-

|  1 | SIMPLE      | user_access | index | NULL          | ind_user_access | 177     | NULL | 19546123 | Using index |

Posted in database | Leave a comment

使用伪’loose index scan’优化max

有时候我们会遇到以下的应用场景:

SELECT MAX(log_time)

FROM log_table

WHERE log_machine IN ($machines)

CREATE TABLE log_table (

id INT NOT NULL PRIMARY KEY,

log_machine VARCHAR(20) NOT NULL,

log_time DATETIME NOT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE INDEX ix_log_machine_time ON log_table (log_machine, log_time);

我们建立的索引为:(log_machine,log_time),当我们传入单个machine的时候,速度很快,但是当我们传入多个machines的时候,查询速度会一下子就降下来;

首先我们看看下面的查询:

root@test 10:21:15>explain select max(log_time) from log_table;

+—-+————-+——-+——+—————+——+———+——+——+—–

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

+—-+————-+——-+——+—————+——+———+——+——+——————————+

| 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Select tables optimized away |

+—-+————-+——-+——+—————+——+———+——+——+—–

当然我们的索引时建立在log_time上的单列索引,这个时候优化器发现不用扫描所有的叶子节点,而直接到最右叶子节点的尾部就可以得到最大的log_time;

同理当我们在in list中传入单个值的时候索引为 (log_machine, log_time):

root@test 10:16:18>explain SELECT MAX(log_time)

-> FROM log_table

-> WHERE log_machine IN (‘Machine 1′)

-> ;

+—-+————-+——-+——+—————+——+———+——+——+—–

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

+—-+————-+——-+——+—————+——+———+——+——+—–

| 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Select tables optimized away |

+—-+————-+——-+——+—————+——+———+——+——+—–

从执行计划中我们中,我们可以看到端倪,优化器和上面一样能够直接找到满足条件的最大log_time,不过这次优化器需要首先在索引中定位到Machine 1,接着在

这一系列前缀值都为machine 1的记录中,在直接在定位到最后到一条记录;

在mysql 5.5以前,mysql对loose index scan并不支持,这就意味着mysql的索引扫描通常需要一个确定的起点和终点,即使查询只需要其中一些不连续的行,

mysql也会扫描起点和终点范围内的所有行;

那么由于不支持loose index scan,现在我们传入的是一个list,mysql不得不把所有满足在list中的machine的记录查询出来,然后在这些记录中得到最大的log_time;

root@test 10:47:10>explain SELECT log_machine, MAX(log_time)

-> FROM log_table

-> WHERE log_machine IN (‘Machine 1′,’Machine 2′,’Machine 3′,’Machine 4′);

+—-+————-+———–+——-+———————+———————+—-

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

+—-+————-+———–+——-+———————+———————+—-

| 1 | SIMPLE | log_table | range | ix_log_machine_time | ix_log_machine_time | 62 | NULL | 569160 | Using where; Using index |

+—-+————-+———–+——-+———————+———————+—-

root@test 10:47:15>SELECT log_machine, MAX(log_time)

-> FROM log_table

-> WHERE log_machine IN (‘Machine 1′,’Machine 2′,’Machine 3′,’Machine 4′);

+————-+———————+

| log_machine | MAX(log_time) |

+————-+———————+

| Machine 1 | 2010-05-07 23:59:13 |

1 row in set (0.65 sec)

我们看到有569160 行参加了运算;

这个时候如果我们换一种想法,把每个machine的最大log_time计算出来,然后在计算一次所有machine的中最大的log_time,这样不仅可以利用优化器能够直接得到每个machine的最大log_time的优化特点,而且还可大大减少参与计算的行,这样就可以明显提升性能:

root@test 10:47:17>SELECT log_machine, MAX(log_time)

-> FROM log_table

-> WHERE log_machine IN (‘Machine 1′,’Machine 2′,’Machine 3′,’Machine 4′)

-> group by log_machine;

+————-+———————+

| log_machine | MAX(log_time) |

+————-+———————+

| Machine 1 | 2010-05-07 23:59:13 |

| Machine 2 | 2010-05-07 23:58:42 |

| Machine 3 | 2010-05-07 23:58:41 |

| Machine 4 | 2010-05-07 23:56:29 |

+————-+———————+

4 rows in set (0.00 sec)

root@test 10:51:44>SELECT log_machine, MAX(log_time) max_log_time

-> FROM log_table

-> WHERE log_machine IN (‘Machine 1′,’Machine 2′,’Machine 3′,’Machine 4′)

-> group by log_machine

-> order by max_log_time desc

-> limit 1;

+————-+———————+

| log_machine | max_log_time |

+————-+———————+

| Machine 1 | 2010-05-07 23:59:13 |

+————-+———————+

1 row in set (0.00 sec)

root@test 10:52:21>explain SELECT log_machine, MAX(log_time) max_log_time

-> FROM log_table

-> WHERE log_machine IN (‘Machine 1′,’Machine 2′,’Machine 3′,’Machine 4′)

-> group by log_machine ;

+—-+————-+———–+——-+———————+———————+—-

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

+—-+————-+———–+——-+———————+———————+—-

| 1 | SIMPLE | log_table | range | ix_log_machine_time | ix_log_machine_time | 62 | NULL | 18 | Using where; Using index for group-by |

+—-+————-+———–+——-+———————+———————+—-

1 row in set (0.00 sec)

执行时间从0.65s降到了0.00s,这个优化速度是非常明显的;

从执行计划中我们可以清楚看到优化器使用到了using index for group by,这样就可以让优化器使用“伪松散索引扫描”,最终达到优化的目的;

ref:http://explainextended.com/2010/05/08/max-and-min-on-a-composite-index/

Posted in database | Leave a comment

mysql批量提交的优化

.背景

用户修改布局时,需要批量更新mysql的xxxx_layout_xxxx表。批量操作的数据量是2-30条/次。批量操作是这次项目在技术上比较关键的一个点,之前批量操作做过性能上的测试,mysql端问题不大,7000+tps,Java端的效率有些差,有优化空间。

对批量的性能进行了测试,优化。过程如下

经测试,批量更新30条记录的时间是35ms。由于数据在mysql服务端中会有内存缓存,批量更新30条的时间用了35ms,感觉有些长,试图找出原因。

使用截包工具(这里用的ethereal),抓取mysql的数据包,下面是一次批量更新的数据包:

可以看出,批量更新时,每条update语句都去mysql请求了一次。并没有打包发给mysql。这种批量的效率肯定不会高。同样方法试了下oracle数据库,oracle驱动做的就很好,一次批量是打包在同一个请求中,是真正的批量提交,效率自然比mysql高。

找了些资料,发现mysql默认情况确实是不支持batch。为了解决上面的问题,需要给JDBC连接加上参数rewriteBatchedStatements=true,并且jdbc driver需要升级到5.1.8以上才支持这个参数。

增加参数rewriteBatchedStatements=true,driver版本升到5.1.17后,再次测试,批量更新30条的时间从35ms降到了11ms。截包后,可以看出底层的机制,已经变成批量提交:

查看包的内容可以发现,这条请求里,封装了30条update语句

 

 

 

 

 

 

横坐标: 一次批量更新的条数。纵坐标:更新100次所用时间(ms)

可见,当批量条数增加时,rewriteBatchedStatements=true的性能有很大优势。即使数量少时,也还是有一定优势。

结论

使用rewriteBatchedStatements=true参数,对批量操作,性能有较大提高,从官方解释上看,对普通操作没有影响。 从网上资料和自己的测试上看,暂时没有发现rewriteBatchedStatements=true参数Driver版本5.1.17的问题。 因此,本项目中计划采取下面优化措施:

  • JDBC Driver版本从5.0.4升级到5.1.17。
  • 连接属性中加入rewriteBatchedStatements=true参数

附:

测试环境:

mysql JDBC 3.0.4/3.1.17。

客户端: 普通PC机。

连接池数: 1-10。

10线程并发,批量更新30条记录(索引有效),循环更新100次。

批量更新主要代码:

mmpSqlMapClient.startTransaction(); // 使用事务

mmpSqlMapClient.startBatch(); // 批量提交

for (ChannelLayoutDO channelLayout: userChannelLayoutList) {              mmpSqlMapClient.update(“UserChannelLayoutDAO.updateSort”, channelLayout);

}

mmpSqlMapClient.executeBatch();

mmpSqlMapClient.commitTransaction();

Posted in database, jdbc, mysql | Leave a comment

STRAIGHT_JOIN has no effect on query with subquery in FROM clause

上周的mysql官网上提交了一个bug,原因为在我们的一个分页sql中出现了全表扫描的情况,这对于我们来说是不可接受的:

Continue reading

Posted in mysql | Leave a comment

innodb compression原理以及性能压测

压缩算法:innodb plugin采用了 zlib library函式库的LZ77 的压缩算法来对数据进行压缩,这种算法已经很成熟,在cpu利用率,压缩比率在50%以上,更为重要的是无数据丢失;

innodb的数据存储方式:innodb在数据储存上采用聚簇的方式(B-树)来组织数据,叶子节点上存放了表中定义的所有列数据;第二索引同样也是B-树结构,在索引列的后面会加上聚簇键列,用于索引列到聚簇索引查找数据;

压缩B-树:由于对B-树的频繁的更新,进而导致B-树的页节点的分裂,不断的对数据进行解压,压缩,innodb面对这种情况,对其进行了优化:在B-树压缩页维护一些系统信息,来表明已经被更新,如对于删除来说,innodb采用打is_deleted的标记,不需要任何的压缩解压操作;当索引页的值发生变化的时候,为了避免不必要的解压和压缩,innodb在压缩页上维护了一个modification log用于记录页的所有改变,insert,update的值会记录到modification log中,而不用重构整个页,当该页modification log的空间不够时,innodb才会解压该页,然后应用log,再对该页重新压缩;当重压缩失败时,叶节点就会分裂,直到insert,update成功;

innodb在innodb buffer pool中怎样处理压缩页:

在一个采用了压缩特性的表中,每一个压缩页(1k,2k,4k,8k,16k)都对应了一个16k的非压缩页,当需要读取压缩页中的数据的时候,如果压缩页不在buffer中,则从磁盘上读到buffer中,然后在对该压缩页进行解压到16k的页;

为了减少i/o以及对页的解压,在buffer中同时存在压缩和未压缩的页;为了给其他需要空间的腾出空间,innodb会将未压缩的页写到磁盘上,保留压缩页在buffer中,也有可能需要将一些没有被访问的压缩页写到磁盘上,因此buffer pool中可能有压缩和未压缩的页,或者只有压缩页;buffer pool采用LRU算法来保证热点页尽量的存放在buffer中,同时为了该LRU算法会评估当前系统正在处于一种什么样的状态,是i/o瓶颈还是cpu瓶颈:当系统正处于i/o瓶颈的时候,innodb倾向于将未压缩页写到磁盘上,以此来腾出更多的空间用于其他的页存放到buffer;当系统正处于cpu瓶颈的时候,inondb倾向于将未压缩和压缩页都写到磁盘上,以此来让更多内存存放热点页,同时减少未压缩页的比例,让更多的压缩页在内存中;

innodb log file对于压缩的处理:

在将压缩页写到数据文件前,innodb会将该页写到redo log中,因此这样就导致了log file会变得很大,同时在进行压缩的时候,需要做一次检查点(log file的大小已经检查点的频率取决于压缩页重组已经重新压缩的次数)。

压测:采用sysbench压测了一下compress的性能,2.5kw数据,未压缩大小为5.7G,采用key_block_size=8压缩后为2.9G:

1GB(BP)  2GB(BP)   4GB(BP)   8GB(BP)   16GB(BP)

compress(2.9G) 487.17     424.85        657.35        694.5           664.32

nocompress(5.7G) 284.12  727.73         933.43     1024.17       1108.8

 

./sysbench –test=oltp –mysql-table-engine=innodb –num-threads=5 –oltp-table-size=25000000 –mysql-user=root –mysql-socket=/u01/mysql/run/mysql.sock run&

CREATE TABLE `sbtest` (

`id` int(10) unsigned NOT NULL AUTO_INCREMENT,

`k` int(10) unsigned NOT NULL DEFAULT ’0′,

`c` char(120) NOT NULL DEFAULT ”,

`pad` char(60) NOT NULL DEFAULT ”,

PRIMARY KEY (`id`),  KEY `k` (`k`)) ENGINE=InnoDB DEFAULT CHARSET=gbk ROW_FORMAT = COMPRESSED key_block_size=8k;

 


 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

ref:
innodb plugin 官方手册
innodb plugin 特性
innodb compression  woes
inodb compression:when more is lesss

 

Posted in database | Leave a comment

有点小激动-Index Condition Pushdown Optimization

今天有点激动,因为在5.6的官方文档中看到Index Condition Pushdown Optimization,简称ICP,这意味着什么,意味着mysql的优化器能够像oracle的优化器那样对索引列进行过滤,而不是像以前版本中只能使用前缀索引来过滤满足查询条件的行;

为了说明优化器是怎么利用ICP,可以使用下面查询来说明其优化:

SELECT * FROM people  WHERE zipcode=’95054′  AND lastname LIKE ‘%etrunia%’  AND address LIKE ‘%Main Street%’;

在没有ICP前,由于优化器只能只能使用前缀索引来过滤满足条件的查询,那么mysql只能够利用索引的第一个字段zipcode,来扫描people表满足ipcode=’95054′条件的记录,而后面的lastname和firstname由于使用了模糊查询,而不能在索引中继续过滤满足条件的记录,这个行为在引擎层完成,这样就导致了到服务器层对people的扫描增加了许多;

那么现在有了ICP,mysql在读取people表前,继续检查满足lastname和firstname条件的记录,

那么ICP的优化在引擎层就能够过滤掉大量的数据,这样无疑能够减少了对base table和mysql server的访问次数,我们可以看到mysql优化器在也越来越智能,这对mysql fans也该是一个好消;由于这个功能在5.6出现,很想迫不及待的测试一把,但官网上还没有提供下载5.6,上面的例子也是在文档上获得,没有具体的实践意义。

Posted in database | Leave a comment