Openstack 性能优化之MySQL(MariaDB)

对于openstack的性能优化有很多方面,这里只对mysql也就是mariadb进行简单优化。对于mysql的默认配置,用作小型站点尚且可以运作。但是对于数据中心的环境,使用mysql的默认配置绝对是不行的。我做了个测试,使用默认的配置,用benchmark测试读时,每秒是1600多个事务,但是优化之后,可以达到六千多个,速度提升六倍。文末或有测试数据。
这里只讨论几个核心的关键的参数,mysql的参数太多,完全掌握得话大量的时间和精力。

1, default_storage_engine

MySQL 以前的很多版本默认的存储引擎是MyISAM,但是MyISAM在设计上有几个问题,他设计时并没有考虑高并发,CPU的核数,以及RAID等,而且,他的扩展性也很低,不能弹性伸缩,只支持一种table级别的锁,所以当每次一个query发生时等好几秒来等锁。
MySQL还有一种存储引擎是InnoDB,可以说和MyISAM是表兄妹的关系,几乎同时诞生。在设计上支持高并发,考虑了性能以及弹性伸缩等。所以在MySQL 5.5.5以后默认的存储引擎就是他了。
所以第一个参数要配置的就是default_storage_engine = InnoDB。但是还有一个问题,就是有些旧程序他们在创建表的时候会默认添加 ENGINE=MyISAM参数在CREATE TABLE语句上,这就比较麻烦了,可以通过以下命令查看当前数据库中有没有MyISAM引擎的存储数据:

SELECT  engine, count(*) as TABLES, concat(round(sum(table_rows)/1000000,2),'M') rows, concat(round(sum(data_length)/(1024*1024*1024),2),'G') DATA, concat(round(sum(index_length)/(1024*1024*1024),2),'G') idx, concat(round(sum(data_length+index_length)/(1024*1024*1024),2),'G') total_size, round(sum(index_length)/sum(data_length),2) idxfrac FROM information_schema.TABLES WHERE table_schema not in ('mysql',   'performance_schema',   'information_schema') GROUP BY engine ORDER BY sum(data_length+index_length) DESC LIMIT 10;
结果如下表示没有。
+--------+--------+-------+-------+-------+------------+---------+
| engine | TABLES | rows  | DATA  | idx   | total_size | idxfrac |
+--------+--------+-------+-------+-------+------------+---------+
| InnoDB |    415 | 1.07M | 0.28G | 0.03G | 0.31G      |    0.09 |
+--------+--------+-------+-------+-------+------------+---------+
1 row in set (2.38 sec)

如果发现有MyISAM 引擎的数据在,可以在配置文件中加enforce_storage_engine=InnoDB参数,这样一些旧的程序创建的数据库默认也就使用InnoDB了。对于openstack的服务,目前所有的服务的数据库都是InnoDB引擎存储的。用默认的参数就可以。

2, innodb_buffer_pool_size

这是一个比较重要的参数了。MyISAM使用操作系统的文件缓存来缓存数据,而InnoDB则使用内存来自己管理缓存数据。innodb的buffer pool 都用来存什么数据呢,第一大用处就是数据缓存,还有索引缓存,脏数据缓存(修改过的数据)还有一些内部的数据结构,如AHI(Adaptive hash index)等。那这个参数该设置多大比较合适呢?这个得根据具体环境具体分析了。比如,我当前的有个刀片机就专门用作MySQL服务器,那官方的建议是该参数设置成物理内存的80%。网上有很多文章也是推荐这个数字。这个参数的最大值在32位系统上232-1, 64位系统上是264-1。那80%这个数字也不是凭空而出的,在已经有大量客户的系统上验证过的,如果你感觉当前系统还有多余的空闲内存,当然也可以再稍微调高点,但是有一点得注意,就是不要让系统发生内存交换,如果系统把innodb的内存交换到磁盘上,那一个query的时间由纳秒级直接降到毫秒级,那就事得其反了。
还有一种情况就是我当前的服务器不止运行数据库服务,还有一些其他的服务,那就不能配置80%的物理内存了。这个就得动态调整了。比如,当系统运行稳定时,可以用第一节给出的命令来查看当前数据库数据的大小,在基础上适当的扩大内存。可以使用以下命令简单测试下当前的size是不是合适,该命令会不停的输出,停止使用按Ctrl + C:

mysqladmin --host mysqlhostip --user=root --password=mypassword ext -ri1 |grep Innodb_buffer_pool_reads

理想情况下,输出的结果中除了第一行以外,都是0的话表示当前的size大小是比较合适的,所以这个数值可以动态调整,当业务繁忙时再检查下看看是否需要加大设置等。

3, innodb_log_file_size

简单介绍下innodb的Undo 和Redo log。当操作数据库时,如果我们修改一条记录,修改后的数据不会立即被写到磁盘文件中,在内存中,在innodb的buffer pool中,修改后的内容通常称之为dirty,同时修改之前的数据会被拷贝到一个磁盘文件中,叫回滚区。在未commit之前,如果有中断触发回滚操作,innodb只需要将回滚区的数据复原到内存中,并且记录一条回滚的日志就行。而当我们提交了commit操作时,innodb也并不会立即把数据刷新到磁盘文件中,而是在Redo log中写一条事务log,当redo空间占满时会刷新部分dirty page到磁盘中,然后释放部分的redo log。所以,如果在这期间数据库发生crash,在重启的时候,innodb会根据redo log中的数据进行恢复。当进行这个参数的配置时,应该明确,size大时write效率会提升,但是启动时recovery时间会变长,size小时write操作会变得很慢。当然这个参数也没有个非常精确的数值,通常是在业务繁忙时段,检查1-2个小时内redo log的大小。可使用下面的办法来确定:

MariaDB [(none)]> pager grep seq
PAGER set to 'grep seq'
MariaDB [(none)]> show engine innodb status\G
Log sequence number 8808823536
1 row in set (0.00 sec)

MariaDB [(none)]> show engine innodb status\G
Log sequence number 8809429708
1 row in set (0.00 sec)

MariaDB [(none)]> show engine innodb status\G select sleep(60); show engine innodb status\G
Log sequence number 8809544645
1 row in set (0.00 sec)

1 row in set (1 min 0.00 sec)

Log sequence number 8809799457
1 row in set (0.00 sec)

MariaDB [(none)]> nopager
PAGER set to stdout
MariaDB [(none)]> select (8809799457 - 8809544645)*60/1024/1024;
+----------------------------------------+
| (8809799457 - 8809544645)*60/1024/1024 |
+----------------------------------------+
|                            14.58045959 |
+----------------------------------------+
1 row in set (0.00 sec)

在上述例子中,一分钟之内,innodb写了不到15M的数据,。可以看出这个数据库的数据量是很小的。此时可以将该参数设置成20M左右。然而mysql默认的5M配置是远远不够的。
对于mysql 5.6之前的版本,修改这个参数时注意,先将my.cnf中的innodb_log_file_size 进行修改,然后停掉mysql server,将/var/lib/mysql/ib_logfile* 的文件改名或者移动到其他地方,然后再启动mysql server。
最后一点需要注意的是,对于mysql 5.6.2之前的版本,这个参数最多只支持2G大小,这个也确实是一个瓶颈所在。

4, innodb_flush_log_at_trx_commit

这个参数默认的值是1,也就是说innodb 每次有事务commit时都会flush 和 sync。如果我们使用autocommit(默认是开启的),每一个insert,update,和delete都会触发commit,然后就是flush和sync的阶段。这个参数还有其他两个值,0和2。如果设置成0,那innodb会将数据flush给操作系统,并不等待数据落到磁盘上,设置成2,那就不做flush操作了,更不会sync。建议将该值改成0.

5, sync_binlog

如果mysql 配置了主从模式或者备份,那么如果innodb_flush_log_at_trx_commit=1,那么,mysql还会将数据再往磁盘上写一次,叫binary log。所以这也是很耗时间的,可以设置为0.

6, innodb_flush_method

在linux 环境中确保 该值为O_DIRECT.

7, innodb_buffer_pool_instances

5.6 之前的可以将他设置为4,5.6之后可以设置到8-16.该值是为了减少buffer pool中内部所的竞争,而将buffer pool 划分成多个instance。在高并发下可以减少所的竞争。

8, innodb_thread_concurrency

innodb 内部会有一个线程并发的控制机制,当该参数为0(默认值),并发控制机制是关闭的,这样当同时的请求数小于CPU核数时,没问题,如果同时的请求数大于CPU核数时,在加上线程各自的锁的问题,性能也会大幅下降。这个参数可以在mysql运行时修改,起始值可以设为8,在业务繁忙时,执行

show engine innodb status\G

观察 ROW OPERATIONS 中的0 queries inside InnoDB, 0 queries in queue行,根据他的结果俩调整该参数。

9, skip_name_resolve

这个参数的意思很明显了。直接添加到my.conf 里。

10, innodb_io_capacity

这个值控制着innodb在刷新dirty数据时,每秒发多少个IO请求(IOPS)。
那么这个值到底该设置成多少呢,还是根据系统的硬件性能来设置。比如,815K的RAID10大概能做1000个随机写的IOPS,那么这个值可以设置为600-800. 便宜点的企业级SSD 大概是4000-10000个IOPS,那么可以设置为6000-8000,也许设置了改值你会感觉不到有多大变化,但是默认值200 肯定是不行的,用benchmark可以测出来。

11, innodb_stats_on_metadata

这个参数可以设置为OFF,对show table status 后者query from information_schema等会有大幅提升。可参考这里

下面来简单介绍下测试方法,我用sysbench工具来测试。ubuntu里用 apt-get install sysbench来安装。
装完之后,首先在数据库中创建一个test的数据库。
然后执行:

root@controller1:~# sysbench --test=oltp --oltp-table-size=1000000 --mysql-db=test --mysql-user=root --mysql-password=mysqlpassword  --db-driver=mysql --mysql-host=10.0.0.43 prepare

注意修改mysql的用户名,密码和host参数。

执行下面的命令来测试。以下分别是修改之前的数据和修改之后测试数据。

root@controller1:~# sysbench --test=oltp --oltp-table-size=1000000 --mysql-db=test --mysql-user=root --mysql-password=qydcos --max-time=60 --oltp-read-only=on --max-requests=0 --num-threads=8 run
sysbench 0.4.12:  multi-threaded system evaluation benchmark

No DB drivers specified, using mysql
Running the test with following options:
Number of threads: 8

Doing OLTP test.
Running mixed OLTP test
Doing read-only test
Using Special distribution (12 iterations,  1 pct of values are returned in 75 pct cases)
Using "BEGIN" for starting transactions
Using auto_inc on the id column
Threads started!
Time limit exceeded, exiting...
(last message repeated 7 times)
Done.

OLTP test statistics:
    queries performed:
        read:                            1414616
        write:                           0
        other:                           202088
        total:                           1616704
    transactions:                        101044 (1683.99 per sec.)
    deadlocks:                           0      (0.00 per sec.)
    read/write requests:                 1414616 (23575.92 per sec.)
    other operations:                    202088 (3367.99 per sec.)

Test execution summary:
    total time:                          60.0026s
    total number of events:              101044
    total time taken by event execution: 479.5584
    per-request statistics:
         min:                                  1.29ms
         avg:                                  4.75ms
         max:                                 25.45ms
         approx.  95 percentile:               6.74ms

Threads fairness:
    events (avg/stddev):           12630.5000/41.55
    execution time (avg/stddev):   59.9448/0.00


root@controller1:~# sysbench --test=oltp --oltp-table-size=1000000 --mysql-db=test --mysql-user=root --mysql-password=qydcos --max-time=60 --oltp-read-only=on --max-requests=0 --num-threads=8 run
sysbench 0.4.12:  multi-threaded system evaluation benchmark

No DB drivers specified, using mysql
Running the test with following options:
Number of threads: 8

Doing OLTP test.
Running mixed OLTP test
Doing read-only test
Using Special distribution (12 iterations,  1 pct of values are returned in 75 pct cases)
Using "BEGIN" for starting transactions
Using auto_inc on the id column
Threads started!
Time limit exceeded, exiting...
(last message repeated 7 times)
Done.

OLTP test statistics:
    queries performed:
        read:                            5182716
        write:                           0
        other:                           740388
        total:                           5923104
    transactions:                        370194 (6169.78 per sec.)
    deadlocks:                           0      (0.00 per sec.)
    read/write requests:                 5182716 (86376.88 per sec.)
    other operations:                    740388 (12339.55 per sec.)

Test execution summary:
    total time:                          60.0012s
    total number of events:              370194
    total time taken by event execution: 478.4186
    per-request statistics:
         min:                                  0.87ms
         avg:                                  1.29ms
         max:                                203.52ms
         approx.  95 percentile:               1.54ms

Threads fairness:
    events (avg/stddev):           46274.2500/733.90
    execution time (avg/stddev):   59.8023/0.00

root@controller1:~# 


One thought on “Openstack 性能优化之MySQL(MariaDB)

Leave a Reply

Your email address will not be published.