关于数据库系统的建立

在维护mysql数据库系统中碰到了以下问题。

对于线上数据库一定要用非常稳定的版本,对于数据库版本一定要稳定,而不要追新。而对于mysql之前的版本号来看,一旦过了50基本算是稳定的版本了。

mysql5.1的版本也已经发展到5.1.57了,算是非常稳定的版本了。而5.0居然都到了5.0.92了。 如果你现在开始建立一个新数据库系统的话就用这2个版本好了。

线上数据库版本必须保持一致,如果都是5.0.92那以后就都是5.0.92在同一个系统内,而对于DBA来说最好以后永远都是5.0.92。但是对于升级遵循的原则还是要到50以后才算是稳定版本。所以现在5.5的版本还不能运行到线上作为一个稳定的正式系统的后台数据库。当然你可以做实验,毕竟innodb1.1对于之前的改造还是相当大的。

关于mysql HA的方案,这个之前的文章说过,虽然现在有各种各样的双master方案,但是始终没有完全可靠的方案。首先是DRBD不可靠,双master的方案也不够可靠。cluster又觉得性能不行,所以这个只能将就下的,看CAP中你看重哪个了,然后根据不同的结果来决定用什么方案。

建立标准的my.cnf文件,这个主要是进行统一。一旦建立这个文件后需要测试环境和生产环境同时执行,这样才能保证正式上线后不会出现数据库层面的问题。而这样的话可以在测试期间尽量的发现存在的问题。

数据库账号管理,默认mysql安装完成后会有很多的默认账号。这些账号可都要进行删除。在建立账号的时候一定要限制好权限,千万不能进行ALL这样的授权,也尽量把host限制到固定IP。密码也尽量使用随机密码而不要图省事。这个最好建立一个自动化管理平台,省去了人工操作。

现在就想到这些,以后看看是否再进行增加

###########################################
Best regards
Timo Seven
blog:http://www.timoseven.com
twitter: http://twitter.com/zauc
Linux System Admin & MySQL DBA

Advertisements

mysql5.5安装

mysql 5.5 以后的tar包居然是用cmake来进行安装的,而不是之前的Autotools方式即(configure方式)

所以还是记录一下安装过程,以备后记。

首先是下载tar包,大概22M的样子,我自己下载的是5.5.10的版本。

然后是安装一些必备的工具

sudo apt-get install cmake libncurses5-dev g++

然后是通过cmake来进行编译

cmake -DCMAKE_INSTALL_PREFIX=/usr/local -DMYSQL_DATADIR=/home/server/mysqldata -DEXTRA_CHARSETS=all -DDEFAULT_CHARSET=utf8 -DDEFAULT_COLLATION=utf8_general_ci -DMYSQL_UNIX_ADDR=/tmp/mysqld.sock -DWITH_DEBUG=0 -DWITH_INNOBASE_STORAGE_ENGINE=1

具体可以参考http://forge.mysql.com/wiki/Autotools_to_CMake_Transition_Guide
这是mysql开发者写的一个autools转换岛对应的cmake的各种编译参数的对比资料。

make
sudo make install

这样安装就算完成了

后面的步骤就跟之前的一样了。

安装mysql库

mysql_install_db  --user=mysql --basedir=/usr/local --datadir=/home/server/mysqldata --no-defaults

这里一定要加上no-defaults参数,不然程序会自动载入配置文件。

拷贝启动文件和my.cnf文件

sudo cp /usr/local/support-files/mysql.server /etc/init.d/mysqld
sudo cp /usr/local/support-files/my-innodb-heavy-4G.cnf /etc/mysql/my.cnf

修改my.cnf文件

[mysqld]
basedir=/usr/local
datadir=/home/server/mysqldata
user=mysql

启动并加入启动项

/etc/init.d/mysqld start
chkconfig mysqld on

为了方便以后安装,最好将这个编译成deb或者rpm包

mysql-mmm配置

最近一周主要是对mysql的mmm进行的相关测试。

它的官方网站是mysql-mmm.org
mmm其实是一个针对mysql双master的一个套件,是完全使用perl来写的。主要作用是监控2个mysql的状态和在出现问题的情况下进行IP漂移切换。不能算是完全意义上的mysql高可用。只是在传统的双master基础上加个监控和漂移,这个完全可以自己写程序来实现。因为mmm在切换过程中也会导致当前连接的丢失,而且这种模式在innodb的情况下出现故障后恢复是一个非常麻烦的问题。
mmm的主要架构是这样的,一个是mmm
monitor服务器,它来负责监控和切换,所以它的配置文件主要是进行设置如何进行监控。2个mysql服务器是客户端。但是需要配置上虚IP,所以每个mysql服务器最少有3个IP,一个真实的IP,2个虚IP。mmm monitor就是进行切换虚IP的,而不是实际的IP
安装过程就不详细说了,主要是要安装一堆perl包,如果可以cpan的话完全可以用cpan来安装,不然就自己下tar包安装,最后做个rpm包来进行安装。
下面就是整个配置流程:
首先对2个mysql配置好了双master,就是互为主备的那种模式。
首先我们定义一下各个机器的IP,mmm monitor的IP是10.10.36.111, mysql
1的真实IP为10.10.36.112, 虚拟IP为10.10.36.201和10.10.36.202,mysql
2的真实IP为10.10.36.113,虚拟IP同样是10.10.36.201和10.10.36.202。
安装完mmm后首先要对mmm monitor进行配置
cat /etc/mysql-mmm/mmm_monitor.conf

include mmm_common.conf

check_period 1
trap_period 2
timeout 2


check_period 1
trap_period 2
timeout 2


ip                                      127.0.0.1
pid_path                                /var/run/mmm_mond.pid
bin_path                                /usr/lib/mysql-mmm/
status_path                             /var/lib/misc/mmm_mond.status
ping_ips                                10.10.36.113,10.10.36.112


monitor_user                    mmm_monitor
monitor_password                mmm_monitor_password

debug 0

接着是配置mmm_common.conf,这个文件在monitor和agent上都是一样的。
cat /etc/mysql-mmm/mmm_common.conf

active_master_role      writer

cluster_interface               eth0
pid_path /var/run/mmm_agentd.pid
bin_path /usr/lib/mysql-mmm/
replication_user mmm_repl
replication_password mmm_repl_password
agent_user mmm_agent
agent_password                  mmm_agent_password


ip                                      10.10.36.112
mode                                    master
peer                                    mysql_db2


ip                                      10.10.36.113
mode                                    master
peer                                    mysql_db1


hosts                                   mysql_db1,mysql_db2
ips                                     10.10.36.201
mode                                    exclusive


hosts                                   mysql_db1,mysql_db2
ips                                     10.10.36.202
mode                                    balanced

下面这个是10.10.36.112上的agent上的mmm_agent.conf的配置
cat /etc/mysql-mmm/mmm_agent.conf

include mmm_common.conf
this mysql_db1

下面是10.10.36.113上的agent的mmm_agent.conf的配置
cat /etc/mysql-mmm/mmm_agent.conf

include mmm_common.conf
this mysql_db2

这些都配置完成了,首先是启动2个mysql服务,同时都启动slave。然后各自启动mmm_agent服务。最后是启动mmm_monitor服务。
启动完成后mmm_monitor进行相关事务:
首先是启动监控2个agent服务,启动方式为mmm_contorl set_online mysql_db1以及mmm_control set_online mysql_db2
启动结果我们可以通过mmm_control show来进行展示,也可以检查监控的状态,mmm_contorl checks来检测现在监控的状态。 

###########################################

Best regards
Timo Seven
blog:http://www.timoseven.com
twitter: http://twitter.com/zauc
Linux System Admin  & MySQL DBA

mysql读写分离

Mysql读写分离


首先我们必须知道为什么要分离。这个一般是由于以下原因导致的。

  1. 性能,单台数据库实在是撑不住了
  2. HA,防止单台数据库挂掉造成应用不可用
  3. 扩展,由于业务新增了新的需求

以上几种是我们常见的要进行分离的原因。其中做多的可能是第一和第二种。这个主要还是涉及到钱和服务器的数量上。一般业务都会在前端部署更多的服务器,而在最后端的数据库服务器往往比较少。但是对于好多web2.0 UGC这样的业务的网站还是有必要重视后端的速度和稳定性。

 

分离的多种方案

  1. query proxy,这个一般是由单独的服务器来进行的,由它负责这个sql语句路由到哪个服务器上。这种proxy的话最好还是要建立HA方式,不然这个proxy就是一个单点故障。这个在之前人人的系统中就是如此,虽然只是负责发起查询的时候建立真实mysql服务器和应用服务器连接,但是还是比较危险的。
  2. Load balance, 后面一堆服务器,通过轮寻的方式来访问mysql服务器。这个很多时候是通过内部DNS来实现。

 

分离的多个原则

  1. 根据内容进行分离,针对某个表或者某个库的查询到哪个服务器上。这个其实就是数据库分区的概念。这个在之前的人人系统中我们也经历过从单台数据库服务器,最终分离出所有的应用到单独的数据库中,并且都有了单独的服务器。这样一旦服务器出现问题,也只会影响到单独的应用,而不会是全部的应用都不可用。
  2. 根据后端mysql服务器的状态,当第一台服务器到达某个标准状态时候再请求到第二台服务器上。这种原则容易造成第一台服务器长时间的高负载运行。
  3. 根据session分离,根据session和后端服务器的映射表来分离。这个对于proxy的要求比较高,需要在内部存储这样一个映射表,并且要可以实时进行更新映射表。

 

现有的问题

  1. 有多个写,这个现在有双MASTER的方案。多个分区的也会总有一个master实在忙不过来的时候,特别是web2.0网站的UGC内容。对于cms系统来说完全没有必要。
  2. 没有session隔离,这个有可能导致查询到的数据不准确。这个就是如何保证用户刚提交内容后马上看到提交后的结果。这个之前在人人某个网页游戏中发生过类似事情,在程序中update数据后立刻去读数据库。但是实际上是updatemaster数据库,但是select查询的是slave数据库。后面我说到seconds_behind_master这个时间并不准就是这里,虽然seconds_behind_master=0但是还是不能在master做了update后可以实时的传导到slave上啊。
  3. 需要用到内部DNS,或者hosts文件来进行调度。

 

这种分离导致的问题

  1. 导致问题的复杂,一个简单的update语句会导致所有的相关数据库服务器进行update
  2. 导致过多的读写,slave方案的最大问题就是在master服务器上的写同样会传导到slave服务器上,同时slave服务器还要支持读。

 

现在我们在mysql5.1以上的版本中使用行复制,但是这个现在还不是很成熟。

不要相信show slave status\G;中的seconds_behind_master,这个在实际中并不太准确。

 

之前我用到的几种query proxy

  1. 一种是比较山寨的方法,让程序员在在代码中嵌入,insert, update这种语句直接连接master服务器,而select直接连接到slave服务器。在多个slave服务器之前使用haproxy进行调度代理。这种方法的缺点是,当业务调整,或者服务器IP更改后还要去修改DNS服务器去,同时还得寄希望于程序中没有把insert这种语句没有一个指向slave服务器上。当然优点就是部署简单。
  2. mysql proxy: 这个貌似很官方的,但是配置语法较麻烦,容易弄错,自己没有实践过,周边同事实践后感觉不好。
  3. 最后一种是人人正在使用的。是完全自己做的,用的是ICE框架。直接一个单独的配置文件进行配置就可。配置文件的主要内容是instance名字,数据库名,数据库IP地址,读还是写,还是读写都可。这个东西使用方式对于程序员来说很简单,它只要知道连接哪个数据库用什么instance名字就可,不需要知道其他任何信息。其它信息(读写方式,数据库地址等等)都是靠这个中间层来确定的。同时对于这些信息都会缓存在应用程序本地,以后不用再次请求中间层,而是直接连接对应的数据库就可以了。第一次应用程序请求的时候会请求到中间层,中间层返回对应的数据库地址和名字以及数据库的用户名密码等信息,然后应用程序使用这些信息来连接数据库,而第二次请求的时候就直接连接数据库了。但是这个系统的问题是每个应用程序本地都要知道这个中间层的地址,每次修改中间层的配置文件后都要重新reload下通知所有应用程序下次请求都要先请求下中间层,无论修改的配置跟你这个应用程序有没有关系。

###########################################

Best regards
Timo Seven
blog:http://www.timoseven.com
twitter: http://twitter.com/zauc
Linux System Admin  & MySQL DBA

innodb1.1的新特性

翻译自2010年oreilly mysql user conference中《InnoDB Plugin: Performance Features and Benchmarks》一文

原作者:Jimmy Yang
John Russell
Calvin Sun

InnoDB1.1的新特性

  1. 多个buffer pool实例
  2. 提升恢复性能
  3. 扩展的InnoDB更改buffering
  4. 支持Linux原生的AIO(异步IO)
  5. 多个回滚段
  6. 分拆flush list互斥
  7. 改进purge调度
  8. 改进log_sys互斥
  9. 性能schema支持

多个buffer pool实例

buffer pool互斥是为了保护很多在buffer pool中的数据结构: LRU, Flush List, Free List, Page Hash Table

buffer pool互斥是一个热门互斥(可能不是最热门的)。在sysbench测试中,它能达到大约700k/s,花费大概50%的时间。

InnoDB性能schema同样可以证明:

5493764597_3c368fed2c_z.jpg

它的解决方法如下:

拆分一个buffer pool成为多个buffer pool实例。这种拆分的结果可以避免所有查询执行代码占用一个以上的buffer pool互斥,只有少数的查询代码会同时占用多个互斥。在16核的服务器上sysbench的读写测试可以提升10%,当然它也会提升只读的性能,在32核的CPU上提升的幅度更大。

使用 innodb_buffer_pool_instances=N,下面是测试结果

5493764599_d0fbcd2abb_z.jpg

改进恢复性能

InooDB的恢复是三段式的。分为: SCAN—REDO–UNDO

这个scan比较慢的,redo日志需要从磁盘上读入到buffer pool中的哈希表中。同时需要跟踪哈希表大小以避免耗尽整个buffer pool。问题是计算哈希表需要通过遍历已分配block的列表。大约有n次方的日志block会被扫描。

redo应用本身也比较慢。脏页需要被插入到flush_list中。列表是根据最早更新到页中的日志序列号(LSN)来排序的。根据在崩溃时候脏页的大数字,列表也可能是大的。而插入列表是根据线性查找的方式插入的。

5494372430_ece2bca41b.jpg

改进scan段的性能:一个简单而有效的方法,在日志头部缓存哈希表的大小。

改进redo段的性能:插入redo日志条目到根据LSN排序的红黑树中。这个方法只对恢复的时候有效,然后红黑树就会被丢弃。实时flush list仍然根据LSN排序的列表.。

5494372432_c4865a0ab9.jpg

这个恢复时间跟InnoDB1.0.6相比要快很多。下面这个是测试的结果:

60m sysbench OLTP的读写测试。参数是innodb-buffer-pool-size=18g, innodb-log-file-size=2047m,在运行20分钟直接杀死mysqld进程。更改的db页有1007907,redo 2.84GB内容。

5494372426_300a77b267.jpg

我们看到这个差距都是几十倍的提升。

接着我们还进行DBT2的测试。50个数据仓库,数据库大小为9800MB,innodb_log_file_siez=3900MB, buffer_pool=20GB,在测试5分钟后杀死进程进行崩溃测试,结果如下:

5493764611_b4e13e1132.jpg

这个提升速度同样是非常高的。

扩展了的更改缓存

插入buffering: InnoDB可以缓存不存在于buffer pool的插入数据到次要的索引。这个方法避免了由于插入炒作导致的额外I/O。

Delete和purge的buffering:在InnoDB1.1中这个功能是通过扩展insert来进行insert和delete。如果页没在buffer pool中的时候操作就会被缓存。

我们在I/O极限和有次要的索引以及有相当多的DML操作的时候就能看到它的优势。在1.1中我们通过修改innodb_change_buffering来实现,默认的值是all。

下面我们就来进行一下测试。表的数据是5百万行,6个次要索引,表的大小是3G,buffer pool是1G,我们删除10万行。

5493764601_155c313853_m.jpg

我们可以看到这之间的差距是上百倍的。

支持Linux上原生的AIO

除了windows平台,InnoDB已经使用模拟AIO来执行对应的IO操作。(在windows平台,InnoDB使用原生的AIO)。从操作系统的角度来看,模拟AIO仍旧是通过的IO,尽快它看上去从一个查询线程的上下文在一个查询和返回的队列是异步的。在InnoDB1.1版本中已经更改为真实的异步IO。

这是通过Linux的”libaio”库来实现的,它是Linux上内核中原生AIO的接口。它能够改进那些IO压力大的系统,通过”show engine innodb status\G;”我们很可能看到很多由于这个而导致的挂起的读写操作。更多的IO操作指令会在同时被发送到内核,如果OS本身能够服务更多的并发请求,那我们就能很好的利用它。

当然我也可以使用innodb_use_native_aio=0来退回到模拟AIO的状态。

多个回滚段

以前的InnoDB只使用一个回滚段,并且有1023并发的限制(1023指的是在回滚段头部的UNDO日志位置的数字)。而新版的InnoDB是有128个回滚段,而每个回滚段也同时支持1023个事务。

5493764603_73ca4c9c9f.jpg

你不必创建一个新的数据库来利用这个特性。只需要慢慢的关闭就可。这个特性更重要的功能是显著地减少了在回滚段互斥的争执,在前面其它特性中我们也看到一个热门的互斥会扩展成为多个的更改方式。所以这个既是一个性能的提升,同时又是一个扩展。

拆分flush list互斥

flush list是一个在buffer pool中的脏block的列表。在多个事务提交中如果有任何脏block,这些block都会被插入到flush list中。以前当发生这种情况时都会导致占用整个buffer pool互斥。而现在我们是让这个flush list有了它自己单独的名为flush_list_mutex的互斥。这个对于用户来说是透明的,不需要重新设置,同时有了更好的并行性。

更好的purge调度

首先我们了解下purge操作:当我们执行delete的SQL语句在数据库的,一条数据不会被物理的删除。只有当InnoDB丢弃掉这个delete操作的更新update日志的时候才会被物理的删除从数据库中对应的行和索引中。这个移除工作我们称作为purge。它是通过后台的主线程来完成的。

这种方式所导致的问题是在有很多事务的情况下,主线程会因为仅仅进行purge操作而被锁住很长时间。这导致了主线程不能很好的进行flush脏页的工作,也不能进行有规则的checkpoint操作。有可能purge线程会落后于delete操作。

我们的解决方法是分配更多的资源给purge线程所需的。使用单独的线程只进行purge。理论上它不能让purge显得更有效率,但是实际上我们解放了主线程来进行其它更重要的工作比如flush脏页等等。我们可以通过设置innodb_purge_threads=0来恢复到传统模式。innodb_purge_batch_size允许用户指定purge组的大小,它的有效范围是1到5000,默认是20。

5493764607_cd8e261da0_z.jpg

###########################################

Best regards
Timo Seven
blog:http://www.timoseven.com
twitter: http://twitter.com/zauc
Linux System Admin  & MySQL DBA

MySQL服务器的linux性能优化和扩展技巧

MySQL服务器的linux性能优化和扩展技巧

作者:Yoshinori Matsunbu

作者现在是DeNA公司的数据库和基础设施架构师。之前在SUN公司工作。他也是HandlerSocket的作者。这个是MySQL的NoSQL插件。

本文是根据他的PPT整理而成的,如有不正确敬请指教。

本文有可以直接点击下载:linux性能优化技巧

本文主要的内容有如下:

  1. 内存和SWAP空间管理
  2. 同步I/O,文件系统和I/O调度
  3. 有用的命令和工具:iostat, mpstat, oprofile, SystemTap, gdb

第一部分:内存和SWAP空间管理

内存也就是随机访问内存

内存是最重要的硬件部件对于RDBMS(relation database management system)。

内存的访问速度远远超过HDD(普通硬盘)/SSD(固态硬盘)

内存:60ns, 但是还没达到每秒10W

HDD:5ms

SSD:100-500us

他们之间的关系为:

1s = 1000ms

1ms = 1000us

1us = 1000ns

所以16GB-64GB对于现在是非常合适的。(好像之前在人人的时候都是72G)

热点应用的数据都需要缓存在内存中

当然最小化热点数据大小也是很重要的,主要有以下几种措施:

使用紧凑长度的数据类型(SMALLINT来替代VARCHAR/BIGINT, TIMESTAMP来替代DATETIME等等)

不要创建无用的索引

删除不必要的数据或者将这些数据移到存档表中,来保证热点的表尽量的小

下面这个测试就是针对不同内存大小服务器的一个测试,测试数据在20-25GB(200个数据仓库,运行一小时),使用的是DBT-2测试,这是一 种密集写的测试,服务器的配置为Nehalem 2.93 * 8 cores, MySQL 5.5.2, 4 RAID 1+0 HDDs

从上面这个表格中我们可以很明显看到巨大的差异当数据全部缓存到内存中。

内存大小会影响所有操作,不管是SELECT,还是INSERT/UPDATE/DELETE操作。

INSERT:当往一个随机排序的索引中插入数据的时候会造成随机的读/写

UPDATE/DELETE: 当更改数据的时候会导致磁盘的读/

还有一个提高性能的方法是使用直接I/O(Direct I/O)

从上图中我们可以看到Direct I/O就是直接跳过了文件系统的cache。

Direct I/O对于完全利用内存是非常重要的。我们可以通过设置innodb_flush_method=O_DIRECT来运行。

注:文件I/O必须是512byte为一个单位,同时O_DIRECT不能用在InnoDB日志文件,二进制日志文件,MyISAM引擎,PostgreSQL数据文件等等。

不要分配太多的内存

这个其实只要分配到足够其它应用程序使用,而不要最后导致系统没有内存可用。

上图中我们可以看到总共系统32G内存,而Mysqld已经使用了30G,而系统居然还只有150M可用,这样是非常危险。

当系统没有内存可用时会发生什么事情呢?

减少文件系统缓存来分配内存空间,这个文件系统缓存就是上图中cached部分

替换掉一些进行来分配内存空间。也就是将一些内存空间移动到SWAP

SWAP是坏的

进程空间会写入到磁盘上(swap out),而这些进程空间本应该是写入到内存中的。

当访问磁盘上的进程空间会导致磁盘读写(swap in)

同时会产生巨量的随机磁盘读写

那也许有些人会想到把swap大小设置为0,但是这样其实是非常危险的。

因为当内存和SWAP都不可用的时候的,OOM Killer(out of memory)就会被启用。OOM Killer会杀掉任何进程来分配内存空间。

最耗费内存的进程会被最先杀掉,在mysql服务器上这个一般是mysqld进程

mysqld会被中止关闭,而在重启时候会进行崩溃修复。

OOM Killer的策略是根据/proc/<pid>/oom_score来进行倒序排列,也就是oom_score最大的会被第一个干掉

通常mysqld会拥有最高的值,因为oom_score是根据内存大小,CPU时间,运行时间来判断。

OOM Killer杀死进程会花费很长的时间,而这期间我们不能干任何事情。

所以不要设置swap为0

上图中我们看到swap被设置为了0,而一旦没有内存可用OOM Killer就会被启用。

一些CPU核心会耗尽100%的系统资源。在上图中我们就看到的就是一个CPU核使用100%的CPU资源。而这个时候连接终端(SSH)就会断掉。

所以swap是不好的,但是OOM Killer更不好。

如果/proc/<PID>/oom_adj被设置为-17,OOM Killer就不会杀掉这个进程。所以给SSHD进程设置为-17是一个有效防止断线的方法。

echo -17 > /proc/<PID>/oom_adj

但是不要给mysqld设置为-17,因为如果最耗内存的进程没被杀死,linux依然没有任何可用的内存。而我们就会在很长很长很长的时间内没法干任何事情。

因此,对于一个生产环境的系统SWAP是必须的。但是我们同样不希望Mysql进行swap out。

我们就需要知道mysql中哪些东西耗费内存

RDBMS:主要的进程空间是被使用的(innodb_buffer_pool, key_buffer, sort_buffer等等),有时候文件系统的cache也会被使用(MyISAM引擎的文件等等)

管理操作:(备份等等),这个时候主要是文件系统cache会被使用

我们要让mysql在内存中,也不要分配大量的文件系统cache。

要特别注意备份操作

因为在备份的时候往往会拷贝大文件,而拷贝大文件就会使用到swap

这个时候我们可以设置/etc/sysctl.conf中vm.swappiness=o来避免这个,而默认值是60

我们看看下图就知道前后的区别了

我们看到,同样是拷贝大文件,下面这个swap才之用了216K

这是因为当物理内存耗尽的时候,linux内核会减少文件系统cache作为最高优先级(低优先级就增加那个值)

当文件系统cache也没有可用的时候,就会开始使用swap。而这个OOM Killer也不会被启用,因为还有足够的swap空间呢。这样才是安全的。

内存分配

mysqld使用malloc()/mmap()方法来进行内存分配

如果要使用更快更多并发的内存就要用tcmalloc()这样的方法

安装Google Perftools(tcmalloc被包含在了里面)

yum install libunwind

cd google-perftools-1.5 ; ./configure –enable-frame-pointers; make; make install

export LD_PRELOAD=/usr/local/lib/tcmalloc_minimal.so;

mysqld_safe &

而对于InnoDB来说它会使用它自己的内存分配器

这个可以在InnoDB Plugin中进行更改

如果innodb_use_sys_malloc=1(默认为1),InnoDB就会使用操作系统的内存分配器

这样tcmalloc通过设置LD_PRELOAD就会被使用。

下面这个是对2种不同的内存分配器进行测试,从中可以看到在内存越大时候,这个差距也越明显。平台还是Nehalem 2.93 * 8 cores, MySQL 5.5.2, 数据量也是20-25GB(200个仓库运行1个小时)

要个别注意分配给每个session的内存

不要分配超过需求过多的的内存大小(特别是针对每个session的内存)

因为分配2MB内存比分配128KB内存会花更多的时间。当分配内存小于等于512MBLinux malloc()方法内部会调用brk()方法,其它时候会调用mmap()。

在一些情况下,分配给每个session过多的内存回到反向的性能影响。

从上面我们可以很明显的看到差距。

在大部分情况都不要分配超过需要过多的内存,当然也有特别的场景(比如:MyISAM + LIMIT + FullScan)

第二部分:同步I/O,文件系统和I/O调度

文件i/O和同步写

RDBMS会经常调用fsync()方法(每一次事务提交,检查点等等)

确认使用RAID卡上的电池备份写缓存(BBWC Battery Backed up Write Cache)

10000+次的fsync()每秒,而不用BBWC会减少200次左右。这个都是在磁盘的情况下。

为了安全的原因需要关闭写缓存。

不要在文件系统中设置“写隔离”(在很多情况下默认都是打开的)

就算使用了BBWC,写入到磁盘还是会非常慢。这是因为一旦打开写隔离,那只有把所有数据写入到磁盘才会关闭隔离。

Ext3中通过mount -o barrier=0,在xfs中是mount -o nobarrier,而在drbd中是在drbd.conf文件中写入no-disk-barrier。

写隔离技术对于防止脏页是非常有作用的,但是在mysql服务器上我们可以关闭,因为都是内部通过事务来提交了。对于其它应用的服务器我们要审慎对待。

复写还是追加写

一些文件是复写的(固定文件大小的),其它的是追加写的(增长的文件长度的)

复写:InnoDB日志文件

追加写: 二进制日志文件

追加写+fsync()比复写+fsync()要慢的多,这是因为追加写每次都要分配文件需要的空间,同时元数据需要通过每个fsync()来刷新到磁盘上。

对于复写可以达到10000+每秒的fsync,而追加写只有3000次左右。

追加写的速度依赖于文件系统。

copy-on-write的文件系统如Solaris的ZFS就会对于追加写足够快,可以达到7000+次。

特别小心设置sync-binlog=1为二进制日志,设置为1的时候会每个事务写入一次就会自动同步硬盘一次。这样效率会非常差

这个时候可以考虑ZFS

检查“预分配二进制日志”的工作日志。Http://forge.mysql.com/worklog/task.php?id=4925

不要太频繁的更新文件

innodb_autoextend_increment=20(默认为8),这个表示表空间文件每次扩展空间都到20M

快速文件I/O健康检测

启用BBWC,并且写隔离是关闭的。

复写+fsync()测试:运行mysqlslap插入(InnoDB, 单线程, innodb_flush_log_at_trx_commit=1 log buffer每次事务提交都会写入log file,并且将数据刷新到磁盘中去);检查的qps超过了1000.

具体使用方法可以参考http://dev.mysql.com/doc/refman/5.1/en/mysqlslap.html

缓冲区和异步写

一些文件I/O操作既不是使用Direct I/O,也不是使用同步写,如:文件复制,MyISAM, mysqldump, innodb_flush_log_at_trx_commit=2等等

在文件系统缓存的脏页最终都要被刷新到磁盘上去。pdflush用作刷新到磁盘上的,它最大可以8个线程同时进行。

这个是高度依赖于vm.dirty_background_ratio和vm.dirty_ratio这2个系统参数的。当脏页数量达到 dirty_background_ratio(默认是10%,64GB内存的话就是当cache达到6.4GB)的时候就会开始刷新到磁盘上。

当达到dirty_ratio的时候就会强制进行刷新到磁盘,默认是40%

强制和粗鲁的脏页刷新是有问题的。当大幅增加传输延迟时间,会导致所有的buffer的写操作都变成了同步的。

过分的刷新脏页到磁盘

执行刷新,会产生大量的写操作

减少vm.dirty_background_ratio的值

升级内核到2.6.22或者更高版本

pdflush线程会给每个设备进行分配,刷新一个慢设备的时候不会阻碍其它设备的pdflush线程。

文件系统—EXT3

这是一种现在最广泛使用的文件系统,但是它明显不是最好的。

首先它在删除大文件的会花费很长的时间:在执行的时候内部会有很多随机的磁盘I/O(HDD)。而对于mysql来说,当执行DROP table的时候,所有open/lock表的客户端线程都会被block掉(LOCK_open mutex)。还有要特别注意使用MyISAM,使用innodb_file_per_table的InnoDB,以及PBXT引擎等。

写文件是串行化的

串行化是通过i-mutex(互斥),分配给每个inode

有时候它比分配单个大文件会快。

对于快速的存储设备缺少优化(如PCI-E接口的SSD)

使用“dir_index”来加快搜索文件,这个需要在文件系统中增加这个属性,tune2fs -O +dir_index/dev/hda5

关闭barrier。

文件系统—XFS/EXT2/BTRFS

xfs的特点

快速删除文件

当使用O_DIRECT可以进行并发写入到一个文件

在RHEL中没有官方支持

可以设置“nobarrier”来关闭写隔离

ext2

更快速的写,因为它不支持日志,所以出现问题不能进行恢复

fsck的时间很长

在active-active的冗余环境下使用(比如MySQL的replication)

在一些情况下,ext2拥有更好的性能

btrfs(开发中)

这是一种跟ZFS一样的copy-on-write的文件系统

支持事务(没有half-block更新)

snapshot备份无需额外的开销

下图就是ext3和xfs在不同的磁盘上的随机写的一个对比图。HDD就是普通磁盘,Intel应该是普通的SATA接口的SSD,而FUSION应该是pci-e接口的SSD

上面的HDD是4块SAS RAID1。

I/O调度器

注:RDBMS(特别是InnoDB)都会调度I/O请求,所以理论上Linux I/O调度器并不是必须的。

Linux的I/O调度器可以有效的控制I/O请求,I/O调度器类型和队列大小都是要考虑的问题。

Linux I/O调度器的类型(根据RHEL5,内核2.6.10)

noop:排序进入的I/O请求通过逻辑block地址,其实就是FIFO,先进先出。

Deadline:读请求(sync)的请求比写请求(async)拥有更高的优先级。其它的就是FIFO,这样就能避免I/O请求饥饿的问题。

cfg(默认):对于每个I/O线程公平的策略I/O,它会对所有的I/O请求进行逻辑block地址重新进行排序,这样减少了查找block地址的时间。

Anticipatory:在2.6.33内核中已经删除,所以请不要再进行使用了。

下面会并发运行2个压力测试程序

多线程的随机磁盘读(默认RDBMS读)

单线程的复写+fsync()(模拟redo日志写)

从上面图中我们可以很容易的看到cfq和noop的差距。操作为RHEL5.3和SUSE11,4 HDD的RAID 1+0。

在RDBMS中,写的IOPS通常都非常高,因为HDD写cache每秒需要控制成千上万的事务提交(write+fsync)

写入的IOPS会被调整为每个线程读IOPS,所以很明显的减少总的IOPS。

下面这个是4个不同的I/O策略的测试图,使用的DBT-2测试,引擎为InnoDB

可以看到noop和deadline差距还是很少的,但是比cfq还是高出30%的样子。

下面这个图是更改了I/O策略的队列大小后的对比图,所以用的MyISAM引擎的比较结果

queue size=N, I/O调度器就会排序N个请求来优化磁盘查找。

MyISAM引擎不会在内部优化I/O请求,它高度依赖OS和存储。当对索引进行插入会导致巨量的随机磁盘读写。

增加I/O队列大小可以减少磁盘查找的开销。Echo 100000 > /sys/block/sdX/queue/nr_requests

这种操作对于InnoDB没有影响,InnoDB会在内部进行排序I/O请求。

有用的命令和工具

iostat

mpstat

oprofile

SystemTap(stap)

gdp

作者讲了这5种命令和工具,但是我这边只说到前面3个命令和工具。

iostat

每个设备的详细的I/O统计数据,对于RDBMS非常重要,因为它经常成为I/O瓶颈。

Iostat -xm 10每10秒执行一次。主要注意r/s和w/s,svctm是平均服务时间(milliseconds),而util就是(r/s+w/s)*svctm

svctm越低意味着r/s和w/s越高。所以我们不要太相信util,我们主要关注的是r/s,w/s和svctm这几个值。如果你的IOPS是1000,那如果svctm是1ms的话,那util就是100。所以当svctm大于1的话就算是有问题了。

Mpstat

以前我一直用vmstat,但是vmstat是显示的所有CPU的一个平均数,后来发现mpstat是能显示每个CPU核的统计。经常会发现某个 CPU核会占满100%的资源,而其它CPU核却还空闲着。而如果你使用vmstat/top/iostat/sar你却无法发现难个CPU的瓶颈。

你也可以用mpstat来检查网络的瓶颈。

从上面VMSTAT的图中我们看CPU的空闲度达到了88%,但是通过MPSTAT图中发现是一个CPU满了,而其它CPU都完全空闲了,这个就是 CPU资源分配不均。这个在之前我们nginx cache服务器上也发现了类似的问题,最终解决后发现性能提升了30%以上。

Oprofile

oprofile是可以查看运行进程的CPU使用状况的概括。你可以很容易的确认那些方法用掉了这些CPU资源。这个工具同时支持系统空间和用户空 间。这个工具主要是用于数据库的内部开发者。如果发现有特别的方法占用了大部分的资源,程序员最好跳过这些方法的调用。对于检查低cpu活动,IO限制和 互斥等情况没有用处。

如何使用呢?

Opcontrol –start –no-vmlinux

benchmarking

opcontrol –dump

opcontrol –shutdown

opreport -l /usr/local/bin/mysqld

执行完如下结果

###########################################

Best regards
Timo Seven
blog:http://www.timoseven.com
twitter: http://twitter.com/zauc
Linux System Admin & MySQL DBA

mysql中group by的排序问题

今日写个小程序,需要用到在mysql中group by 之后排序,但是出现几个问题。数据如下:

但是如果你用group by order by xxx desc发现结果并不是这样的。其实这样只是对group by之后的结果再进行的order by。而mysql还不支持先order by再进行group by

发现group by的结果是根据id进行升序排列的。但是我实际上是需要反序进行排列。

有2个方法我自己找的,一个是通过alter table来解决,另外一个是子查询。 alter table的结果是以后只能进行降序排列了,但是好处是还是可以用到原来的索引,而用子查询基本是全扫描.

 

 

 

 

 

 

###########################################

Best regards
Timo Seven
blog: http://www.timoseven.com
twitter: http://twitter.com/zauc
Linux System Admin & MySQL DBA