MySQL实践积累

有一位同学通过askdba来询问with rollup的用法

(2012-02-01 15:08:22):

 mysql中有这种用法select ... from table_name  group by a with rollup

"with rollup"是什么意思呢？

GROUP BY Modifiers  官方手册里面对这个rollup有一个专门的页面介绍 地址在这里，说得非常详细，我这里做一个简单的例子重现

建一个简单的表并插入几条简单的数据

CREATE TABLE `t` (

  `id` int(11) DEFAULT NULL,

  `id2` int(11) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=gbk

insert into t valeu(11,11),(12,12),(13,13);

先来做一个查询

root@test 03:44:32>select id,sum(id2),avg(id2) from t group by id with rollup;

+------+----------+----------+

| id   | sum(id2) | avg(id2) |

+------+----------+----------+

|   11 |       11 |  11.0000 |

|   12 |       12 |  12.0000 |

|   13 |       13 |  13.0000 |

| NULL |       36 |  12.0000 |

+------+----------+----------+

4 rows in set (0.00 sec)

我们可以看到，对于group by的列，with rollup将不会做任何的操作，而是返回一个NULL，而没有group by的列，则根据前面的avg函数和sum函数做了处理。

再来看另外一个语句，只对一个列做avg

root@test 03:44:36>select id,avg(id2) from t group by id with rollup;

+------+----------+

| id   | avg(id2) |

+------+----------+

|   11 |  11.0000 |

|   12 |  12.0000 |

|   13 |  13.0000 |

| NULL |  12.0000 |

+------+----------+

4 rows in set (0.00 sec)

以前从没留意到有这种用法，这次长见识了。

之前在做一个项目的MySQL数据库极限压测, 有部分场景是涉及到MySQL重启的,而这个项目使用的是InnoDB存储引擎。

重启完毕后,一开始十几分钟的性能是非常差的,原因是因为InnoDB有innodb buffer pool(简称ibf)的概念

和innodb buffer pool相关的参数innodb_buffer_pool_size,size越大,可以放到内存的数据越多,而大多数的项目都会有热点数据的存在,当热点数据经过LRU算法进入到buffer pool之后,读磁盘的次数减少,读的都是内存,速度是最快的

问题来了,数据库一重启,热点数据都被清空,bf里面都是空的.等待app的sql请求过来让bf填满数据是一个方法,但30分钟内很难把热点数据都装载进来.

这个时候,我们可以采取人工预热的办法来让bf满足我们的需求

MySQL 5.0的预热是最简单的

方法1 : 在MySQL重启后 执行 SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES,经过我在一台5.0MySQL的实验 在重启后查看show innodb status\G 和执行完这条语句后 Free buffers的页数会减少.

这个count语句有何作用呢?InnoDB的存储格式和MyISAM不一样, innodb会在mysql启动后的第一次访问表的时候,统计表的索引基数等相关信息,如果表很多的话,这也是一个巨大的开销.所以在正式提供服务之前,就把表打开,放入到bp里面

MySQL 5.1的数据预热

我在一台5.1.48的MySQL进行了重启,测试了同样的SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES 但发现free buffers的页数没有变少，情况和mysqlperformanceblog.com作者所说的一样–这个方法在5.1之后就无法达到我们的需求了

可以采用另外一种方法:获得数据库里面的库和对应的表，来进行预热，核心代码是这一句

SELECT table_schema, table_name FROM information_schema.tables

可以用perl或者python来获取库和表 然后执行 select * from db.table limit 1 来实现我们的方法

最后提一下,我们可以再my.cnf 加入init-file=/mysql/init.sql ，在每次mysql重启的时候就自动执行这个预热的sql  当然了 sql是要我们自己生成的哦

最近一个正在进行的项目，性能测试场景中包含MySQL 为较大压力的应用程序提供服务，Linux服务器CPU占用100% ，OS 不可用，我写了一个吃CPU的shell脚本来不断的跑，结果把服务器跑挂了，手动重启了服务器后，发现MySQL起来的时间需要非常长，为大家所不能接受

MySQL相关信息   5.0.67    InnoDB Buffer Pool Size为36G  ,innodb_flush_log_at_trx_commit = 1，innodb_log_file_size = 900M

，当时候的服务器情况比较糟糕，CPU占用100% ，ssh也登录不进去，维持了一段时间后才进行重启，所以没有收集到最重要的崩溃之前的MySQL内部信息   整个的恢复过程用了6个小时

这个时候，引发了我们的反思:

1. 我们的MySQL压测模型没有针对不同版本的MySQL进行崩溃恢复压测  所以出现这个崩溃恢复的问题，我们只能补回来这个测试，针对不同版本的MySQL(MySQL 5.0.67 和MySQL5.1.xx)，需要进行相同TPS的场景下进行kill -9  pid (mysqld)的测试，掌握一手的崩溃恢复时间，为将来线上出现MySQL崩溃，做最好的准备

2. 我自己对MySQL InnoDB崩溃恢复的过程不是非常了解,导致MySQL崩溃之前没有去采集有参考价值的数值，这点需要加把劲

这一篇是前言，下一篇，我会对MySQL InnoDB的崩溃恢复过程做详细的介绍

经过一段时间的测试,准备把MySQL 5.1 InnoDB Plugin部署到线上，今天发现MySQL 5.0 InnoDB 和MySQL5.1 InnoDB Plugin启动是有点区别的，这里做一个整理

5.0MySQL 的版本信息

mysql> show variables like ’%version%’;

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

| Variable_name           | Value                       |

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

| protocol_version        | 10                          |

| version                 | 5.0.38-Debian_3netease5-log |

| version_comment         | Debian etch distribution    |

| version_compile_machine | x86_64                      |

| version_compile_os      | pc-linux-gnu                |

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

5 rows in set (0.01 sec)

启动的日志信息

InnoDB: The first specified data file xxx//ibdata1 did not exist:

InnoDB: a new database to be created!

100609 19:18:50  InnoDB: Setting file xxx//ibdata1 size to 32 MB

InnoDB: Database physically writes the file full: wait…

100609 19:18:51  InnoDB: Log file xxx/ib_logfile0 did not exist: new to be created

InnoDB: Setting log file xxx/ib_logfile0 size to 128 MB

InnoDB: Database physically writes the file full: wait…

InnoDB: Progress in MB: 100

100609 19:18:53  InnoDB: Log file xxx/ib_logfile1 did not exist: new to be created

InnoDB: Setting log file xxxx/ib_logfile1 size to 128 MB

InnoDB: Database physically writes the file full: wait…

InnoDB: Progress in MB: 100

InnoDB: Doublewrite buffer not found: creating new

InnoDB: Doublewrite buffer created

InnoDB: Creating foreign key constraint system tables

InnoDB: Foreign key constraint system tables created

100609 19:18:57  InnoDB: Started; log sequence number 0 0

100609 19:18:57 [Note] /usr/sbin/mysqld: ready for connections.

Version: ’5.0.38-Debian_3netease5-log’  socket: xxx/mysqld.sock’  port: 4591  Debian etch distribution

MySQL 5.1 的版本信息

mysql>  show variables like ’%version%’;

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

| Variable_name           | Value               |

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

| innodb_version          | 1.0.6-unknown       | ##这个是InnoDB Plugin的新信息

| protocol_version        | 10                  |

| version                 | 5.1.45-log          |

| version_comment         | Source distribution |

| version_compile_machine | x86_64              |

| version_compile_os      | unknown-linux-gnu   |

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

MySQL5.1 InnoDB Plugin启动日志信息，多了很多内容，下面逐一介绍

InnoDB: The InnoDB memory heap is disabled

InnoDB: Mutexes and rw_locks use GCC atomic builtins

InnoDB: The first specified data file ./ibdata1 did not exist:

InnoDB: a new database to be created!

100609 17:17:11  InnoDB: Setting file ./ibdata1 size to 512 MB

InnoDB: Database physically writes the file full: wait…

InnoDB: Progress in MB: 100 200 300 400 500

100609 17:17:15  InnoDB: Log file xxx/ib_logfile0 did not exist: new to be created

InnoDB: Setting log file  xxx/ib_logfile0 size to 512 MB

InnoDB: Database physically writes the file full: wait…

InnoDB: Progress in MB: 100 200 300 400 500

100609 17:17:21  InnoDB: Log file xxx/ib_logfile1 did not exist: new to be created

InnoDB: Setting log file xxxx/ib_logfile1 size to 512 MB

InnoDB: Database physically writes the file full: wait…

InnoDB: Progress in MB: 100 200 300 400 500

InnoDB: Doublewrite buffer not found: creating new

InnoDB: Doublewrite buffer created

InnoDB: Creating foreign key constraint system tables

InnoDB: Foreign key constraint system tables created

100609 17:17:26 InnoDB Plugin 1.0.6-unknown started; log sequence number 0

100609 17:17:28 [Note] Event Scheduler: Loaded 0 events

100609 17:17:28 [Note] /usr/xtradb/libexec/mysqld: ready for connections.

Version: ’5.1.45-log’  socket: ’xxx/mysqld.sock’  port: 4331  Source distribution

InnoDB: The InnoDB memory heap is disabled

是因为使用了操作系统的内存分配器,所以就禁用了InnoDB的内置内存分配器

mysql> show variables like ’%malloc%’;

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

| Variable_name         | Value |

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

| innodb_use_sys_malloc | ON    |

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

1 row in set (0.00 sec)

而在5.0里面执行是为空结果的，关于这个innodb_use_sys_malloc，具体可以参阅InooDB的官方介绍

Mutexes and rw_locks use GCC atomic builtins

对比以往的版本,InnoDB Plugin 1.0.3之后的版本 使用了GCC atomic builtins来执行互斥和读写锁,性能会比以往使用的pthread_mutex_t要高效

参见 InnoDB Plugin: Enabling GCC atomic built-in functions for InnoDB rw-locks
InnoDB: The first specified data file ./ibdata1 did not exist:

InnoDB: a new database to be created!

第一次启动mysql，因为默认的是innodb引擎，所以会自动创建一个test

InnoDB: Doublewrite buffer not found: creating new

InnoDB: Doublewrite buffer created

什么是Doublewrite?

InnoDB将BP中的Dirty Page刷（flush）到磁盘上时，首先会将Page刷到InnoDB tablespace的一个区域中，我们称该区域为Double write Buffer。在向Double write Buffer写入成功后，再择机将数据拷贝到正在的数据文件对应的位置。

更多详细的信息可以看看performace blog 的对doublewrite的介绍

或者是苏普的对Doublewrite整理 InnoDB Double write

参考资料

InnoDB Plugin: Enabling GCC atomic built-in functions for InnoDB rw-locks

http://www.innodb.com/wp/support/tips/atomics/

InnoDB Plugin 1.0 for MySQL 5.1 User’s Guide

http://www.innodb.com/doc/innodb_plugin-1.0-doc-single/innodb-plugin.html#innodb-performance-use_sys_malloc

Innodb Double Write

http://www.mysqlperformanceblog.com/2006/08/04/innodb-double-write/#more-72

转载烦请保留原文连接 http://dbahacker.com/mysql/innodb-plugin-log-analyze

Master-Slave的一个read log event 错误处理

现在简单介绍一下几个节点的情况
B服务器上面有一个B1节点，是MySQL5.0版本，普通InnoDB的表
C服务器上面有两个测试节点（C1，C2）,
它们是A服务器上面的A1节点的slave,C1，C2，都是使用XtraDB压缩功能的表

也就是 A为Master,有3个Slave(B1,C1,C2)

因为某些原因,C2的slave 复制出现了问题,而C1是没有问题的，当时候采取了以下措施
1.停止C1,C2的slave,使用mysqladmin shutdown来关闭C1,C2
2.删掉C2的数据目录等，只留下my.cnf
3.把C1的数据目录等都拷贝到C2目录下
4.启动C1,C2

这时候发现C2的mysqd.log 里面有了这样的内容

100608 12:44:14 [Note] Slave: received end packet from server, apparent master shutdown:
100608 12:44:14 [Note] Slave I/O thread: Failed reading log event, reconnecting to retry, log ‘mysql-bin.007817′ at postion 86542498
100608 12:44:15 [Note] Slave I/O thread killed while reading event

同时在C2里面执行show slave status \G的时候,Seconds_behind_master = 会出现落后的秒数，但连续的执行show slave status 的时候就会看到有NULL的信息

mysqlperformanceblog有一篇文章是提到过是server-id的问题 http://www.mysqlperformanceblog.com/2008/06/04/confusing-mysql-replication-error-message/
但是我的3个slave的server-id都是不一样的，和master的server-id也是不一样的

最后的解决方法是尝试了一把把C2的server-id改成新的值，重启mysql,stop slave ;start slave;show slave status \G后发现复制正常了，不知道是否和我直接copy C1的数据库等文件过来是否有关

参考文献/文章

Confusing MySQL Replication Error Message http://www.mysqlperformanceblog.com/2008/06/04/confusing-mysql-replication-error-message/
Many Errors “Slave: received end packet from server” http://bugs.mysql.com/bug.php?id=9325

这其实是09年总结的一篇文章，今天被一位朋友问到InnoDB有什么好处，一下子讲不清楚，现在把在自己另外一个博客的文章在这里重发一遍，主要是讲InnoDB和MyISAM的对比，从中可以看到InnoDB的很多好处，比如并发插入的时候行级锁等

本 文主要整理了Mysql 两大常用的存储引擎MyISAM，InnoDB的六大常见区别，来源于Mysql手册以及互联网的资料

依稀记得是上周开始部署了mysql的节点监控程序到线上环境，设定的查询周期是90秒一次，超过10秒的查询和超过x个连接的时候就会发报警邮件给DBA。

这个在镜像库部署的时候，会经常发现开发人员查一下没用索引的语句或者没有limit n的语句，这些没语句会对数据库造成很大的影响，例如一个几千万条记录的大表要全部扫描，或者是不停的做filesort，对数据库和服务器造成io影响等。这是镜像库上面的情况。

而到了线上库，除了出现没有索引的语句，没有用limit的语句，还多了一个情况,mysql连接数过多的问题。说到这里，先来看看以前我们的监控做法

1. 部署zabbix等开源分布式监控系统，获取每天的数据库的io，cpu，连接数

2. 部署每周性能统计，包含数据增加量，iostat，vmstat，datasize的情况

3. Mysql slowlog收集，列出top 10

以前以为做了这些监控已经是很完美了，现在部署了mysql节点进程监控之后，才发现很多弊端

第一种做法的弊端: zabbix太庞大，而且不是在mysql内部做的监控，很多数据不是非常准备，现在一般都是用来查阅历史的数据情况

第二种做法的弊端：因为是每周只跑一次，很多情况没法发现和报警

第三种做法的弊端: 当节点的slowlog非常多的时候，top10就变得没意义了，而且很多时候会给出那些是一定要跑的定期任务语句给你。。参考的价值不大

最后总结一下节点监控的好处

1． 轻量级的监控，而且是实时的，还可以根据实际的情况来定制和修改

2． 设置了过滤程序，可以对那些一定要跑的语句进行过滤

3． 及时发现那些没有用索引，或者是不合法的查询，虽然这很耗时去处理那些慢语句，但这样可以避免数据库挂掉，还是值得的

4.  在数据库出现连接数过多的时候，程序会自动保存当前数据库的processlist，DBA进行原因查找的时候这可是利器

5.  使用mysqlbinlog 来分析的时候，可以得到明确的数据库状态异常的时间段

MySQL5.5亮点

InnoDB

1. 多个缓冲池实例
2. 多个回滚段
3. 扩展变化和清除缓冲调度
4. Improved Log Sys and Flush List mutex 改善日志系统
5. 改进锁策略
6. 改进InnoDB互斥，读写锁，线程和I/O操作的策略

改进win32，64平台下的性能/规模

扩展到32个内核

半同步复制

性能架构

SIGNAL/RESIGNAL (finally!)

新的增强分区

Configuring the heartbeat period

More than 10x improvement in recovery times

200% performance gain for MySQL 5.5 over 5.1.40

MySQL Cluster 7.1 (GA)

• NDBINFO – improved real time status and usage statistics
• MYSQL Cluster Manager (CGE only)
• Sub-second failover and self healing recovery
• Parallel multi-master architecture
• Low latency – real time responsiveness