maraidb优化思路（二）maraidb层面

hzcm1984

已于 2022-04-12 16:00:54 修改

阅读量1.8k

点赞数 1

分类专栏： mysql/mariadb学习 Linux 文章标签： linux mysql

于 2022-04-12 16:00:31 首次发布

本文链接：https://blog.csdn.net/hzcm1984/article/details/124126380

版权

Linux 同时被 2 个专栏收录

27 篇文章 1 订阅

订阅专栏

mysql/mariadb学习

20 篇文章 3 订阅

订阅专栏

写在前面

首先本人并非专职DBA，而是一个小小运维，如果这篇文章里有不对的地方，还希望各位大佬留言指出不正之处，小弟在此谢过！

mariadb层面

集群技术

mariadb的集群技术主要包括如下2种：

MariaDB Replication

Maradb复制技术，就是经常所说的主从复制和多源复制，允许将一个或者多个服务器（主服务器，从10.5开始已经改为_primary_ ）内容复制到一个或者多个服务器上（副本）
这样做的目的，就是对mariadb server进行读写分离，把查询分布到到多个Slave（从10.5开始已经改为_replica_ ），从而减轻主服务器的压力；
Maradb复制技术也分为很多种，常见架构如下：

Standard Replication-标准复制

一主多从，目前用的最多，可以无限扩展，主服务器出故障时，可以随时提升从服务器为主

Multi-Source Replication-多源复制

多源复制，这个技术使用的也挺多，从多个主服务器复制数据至从服务器
其他还有Ring Replication-环形复制、Star Replication-星型复制，都是基于主主复制，缺点时复制数据时容易存在冲突，无法保证数据一致性，并且一旦主服务器出问题时，复制即刻停止。
如果使用MariaDB Replication技术，可以使用Mariadb官方路由软件Maxscale。MariaDB MaxScale 是一种数据库代理中间件，可将请求转发到一个或多个数据库服务器，转发是maxscale基于数据库语句的语义理解（SELECT | UPDATE，DELETE，DROP，INSTERT）和数据库后端集群中服务器角色的规则执行的。
它主要如下功能：

透明代理，无需改动程序代码，MaxScale可以根据语句段进行自动转发到后台mariadb集群；
支持负载均衡和高可用；
支持不同协议和路由，并且由插件模块化实现；
使用了Linux的epoll事件，支持异步I/O

MariaDB Galera Cluster

MariaDB Galera Cluster是一种多主集群架构，并且节点之间数据是同步复制（并非完全同步复制，官方称为：虚拟同步），这样保证了数据的一致性。如果其中一个节点出现故障，不影响其他节点数据的准确性。详细的说明请查看尾部的来源；

innodb引擎优化

此优化只针对innodb引擎，优化的主要目的是提高innodb引起的内存使用量，尽量把数据加载到内存中，从而提高数据访问速度；
写这篇文档之前，我做了一个测试，用的8核/32G虚拟化服务器，使用SysBench进行了测试，优化后的每秒事务比优化前提高了50多个。

innodb_buffer_pool_size

innodb缓冲池大小：innodb缓冲池大小：默认大小为128M，官方建议大小设置为可用物理内存的**70%**，如果你的机器只跑mariadb一个服务，而且内存比较大，建议分的多一些。如果你的服务器跑的其他中间件过多，建议少设置一些，需要留给其他程序，比如redis/mongod/JAVA等，在10.4.4之前，分配的指定内存比建议值多10%。
查看innodb_buffer_pool_size大小：

show variables like 'innodb_buffer_pool_size';

如何计算使用率

MariaDB [(none)]> show status like  'Innodb_buffer_pool_%'; 
+---------------------------------------+--------------------------------------------------+
| Variable_name                         | Value                                            |
+---------------------------------------+--------------------------------------------------+
| Innodb_buffer_pool_dump_status        |                                                  |
| Innodb_buffer_pool_load_status        | Buffer pool(s) load completed at 220331 10:12:14 |
| Innodb_buffer_pool_resize_status      |                                                  |
| Innodb_buffer_pool_pages_data         | 98204                                            |
| Innodb_buffer_pool_bytes_data         | 1608974336                                       |
| Innodb_buffer_pool_pages_dirty        | 7445                                             |
| Innodb_buffer_pool_bytes_dirty        | 121978880                                        |
| Innodb_buffer_pool_pages_flushed      | 4555619                                          |
| Innodb_buffer_pool_pages_free         | 296130                                           |
| Innodb_buffer_pool_pages_misc         | 2242                                             |
| Innodb_buffer_pool_pages_total        | 396576                                           |
| Innodb_buffer_pool_read_ahead_rnd     | 0                                                |
| Innodb_buffer_pool_read_ahead         | 0                                                |
| Innodb_buffer_pool_read_ahead_evicted | 0                                                |
| Innodb_buffer_pool_read_requests      | 157673364                                        | # 1
| Innodb_buffer_pool_reads              | 51013                                            | # 2
| Innodb_buffer_pool_wait_free          | 0                                                | # 3
| Innodb_buffer_pool_write_requests     | 74448147                                         |
+---------------------------------------+--------------------------------------------------+
18 rows in set (0.00 sec)

Innodb预热到正常负载以后，在业务繁忙时段，以1分钟为单位，观察innodb_buffer_pool_read_requests 和 innodb_buffer_pool_reads 的值，innodb_buffer_pool_reads变化小于Innodb_buffer_pool_read_requests 变化的 1%，说明有了很好的使用量；
如果 innodb_buffer_pool_wait_free增加过多，说明没有足够的缓冲池（或者足够的刷新率）；
注意：innodb_buffer_pool_size 设置的越大，则启动时间就越久
如何设置
编辑my.cnf，在[mysqld]增加如下内容，以下所有的配置必须重启mariadb service才能生效

[mysqld]
# innodb缓冲池大小，官方建议设置为可用内存的70%~80%
innodb_buffer_pool_size = 10G

innodb_buffer_pool_instances

**innodb_buffer_pool_instances ：**将 innodb_buffer_pool_size划分为特定数量的实例。它根据 innodb_buffer_pool_size 进行变化，在10.0中默认数量为8，如果innodb_buffer_pool_size < 1GB，则innodb_buffer_pool_instances=1.
**官方建议每个实例大小至少为1G**，假如innodb_buffer_pool_size 为 4GB ，并且 innodb_buffer_pool_instances 数量为 4，则每个实例大小则为 1GB；
使用show variables like 'innodb_buffer_pool_instances';可以查看实例数量

如何设置
编辑my.cnf，在[mysqld]增加如下内容

[mysqld]
# 缓冲区实例数量，每个至少1G大小，假如innodb_buffer_pool_size为8G，innodb_buffer_pool_instances设置为8即可
innodb_buffer_pool_instances=10

innodb_flush_log_at_trx_commit

innodb_flush_log_at_trx_commit：设置日志缓冲区写入 InnoDB 重做日志的方式。共有4个选项，0,1,2,3 ，默认为1:

1：每次提交以后，都写入重做日志文件，这样最符合ACID要求，但是需要频繁写入磁盘，效率最低；
0：每次提交以后，什么都不做，而是mariadb每秒一次将日志缓冲区文件写入重做日志，性能比1好，但是服务器突然断电，坏掉，日志缓冲区文件又没写入重做日志，则有可能丢失断电前的最后1秒事务；
2：每次提交后，日志缓冲区都会写入 InnoDB 重做日志，而是mariadb每秒刷新一次（刷新到OS缓存），性能稍好一些，但操作系统或断电可能会导致最后一秒的事务丢失；
3：模拟 MariaDB 5.5 组提交（每个组提交 3 次同步），此选项自 10.2 以来一直无法正常工作，将来可能会被删除

从上面来看，2是折中的一个办法，既有不错的安全性，也没有频繁的写入磁盘

如何设置
编辑my.cnf，在[mysqld]增加如下内容

[mysqld]
# 设置事务写入写入redo-log方式
innodb_flush_log_at_trx_commit = 2

innodb-log-file-size

innodb-log-file-size：日志组中每个 InnoDB 重做日志文件的大小（以字节为单位）。组合大小不能超过 512GB。较大的值对 InnoDB 事务日志有好处，并且对于良好和稳定的写入性能至关重要。该值越大，缓冲池中需要的检查点刷新活动就越少，从而节省磁盘 I/O。但是，一旦你的数据库异常关闭（崩溃或终止，OOM 或意外），恢复过程会非常缓慢。
此外还有一个变量：innodb_log_files_in_group定义了redo-log的组数量（在10.4之前默认为2组），假如innodb_log_files_in_group=2，innodb-log-file-size=1G**，**那么innodb-log-file-size实际大小则为innodb-log-file-size=1G * innodb_log_files_in_group=2，等于2G，这一点要注意一下。测试中我分别使用了innodb-log-file-size=1G和2G，使用2G性能和1G没区别
如果要查看innodb_log_files_in_group数量，使用SHOW VARIABLES LIKE like 'innodb_log_files_in_group';查看group数量

如何设置
编辑my.cnf，在[mysqld]增加如下内容

[mysqld]
# 设置InnoDB Redo Log大小
# 假如innodb_log_files_in_group=2（10.4之前默认2），iinnodb_log_files_in_group= 1G * 2 = 2G，InnoDB Redo Log实际大小为2G
innodb-log-file-size=1G

innodb_log_buffer_size

innodb_log_buffer_size：用于将 InnoDB 重做日志文件写入磁盘的缓冲区大小（以字节为单位），有了缓冲区后事务无需将将更改直接写入磁盘，而是刷新至缓冲区，然后mariadb在将缓冲区内的脏页写入到物理磁盘（参考上面的innodb_flush_log_at_trx_commit）。如果你的程序经常有高频大事务，比如频繁（INSERT、UPDATE），使用大缓冲区更可以节省磁盘 I/O。
在MariaDB 10.1.8以及之前版本默认值为8M，在MariaDB 10.1.9以及以后版本默认值为16M
如何设置
编辑my.cnf，在[mysqld]增加如下内容

[mysqld]
# InnoDB Redo Log缓冲区大小，默认为16M
innodb_log_buffer_size=256M

innodb_data_file_path

innodb_data_file_path：开启innodb数据文件自动扩展，默认值10.0之前为8m,10.2之后为12m
如何设置
编辑my.cnf，在[mysqld]增加如下内容

[mysqld]
# 开启ibdata自动扩展
innodb_data_file_path=ibdata1:12M:autoextend

innodb_use_mtflush

innodb_use_mtflush：是否启用多线程刷新操作。InnoDB 的多线程刷新功能在 MariaDB 10.2.9 中已弃用，并从 MariaDB 10.3.2 中删除，在更改版本中使用innodb_page_cleaners来配置线程数
** innodb_page_cleaners**：设置清理脏页数量，默认为4或者配置为innodb_buffer_pool_instances数量，以最低者为准。MariaDB 10.2.2中启用了此参数，并在MariaDB 10.5.1弃用了此参数，在10.6中移除了此参数

如何设置
编辑my.cnf，在[mysqld]增加如下内容

[mysqld]
# 开启多线程刷新脏页
innodb_use_mtflush = ON
# 设置清理线程页面数，默认为4，可以配置为innodb_buffer_pool_instances数
 innodb_page_cleaners=8

max_connections

max_connections：服务器可以接受的最大连接数，默认值为151个（其中一个root专用，实际为150）。如果设置的过大，则占用过多的服务器内存，容易造成服务器oom，尤其是运行了各种中间的服务器，设置的过小则可能出现：Handling Too Many Connections，连接数不够用；

如何查看max_connections连接数

SHOW VARIABLES LIKE "max_connections";

查看自服务器启动以记录的来最大连接数（历史记录，只会升不会降）

SHOW STATUS WHERE variable_name = "max_used_connections";

查看当前打开的连接数

SHOW STATUS LIKE 'Threads_connected';

如何设置
建议在业务繁忙的时候查看SHOW STATUS LIKE 'Threads_connected';当前连接的熟虑，是否达到max_connections的85%，如果超过了85并且接近，说明max_connections过小，如果小于max_connections85%的值，说明设置的过大。
而设置max_connections则需要看max_used_connections记录的最大数量，并且考虑系统可用物理内存进行修改，可以SET GLOBAL动态更改连接数：

SET GLOBAL max_connections=1000;

也可以在my.cnf里添加，永久生效

[mysqld]
max_used_connections=1000

注：另外有公式计算max_connections内存使用量来确认max_connections的最大数量，我计算了一下，发现8核，32G的内存保持到合理使用量的情况下，max_connections最大只能到200多，太小了。

max_connections = (Available RAM - Global Buffers) / Thread Buffers

其中包含的具体项如下：
Global Buffers=[query_cache_size + (table_open_cache * table_open_cache_instances)
+ innodb_buffer_pool_size + innodb_log_file_size + innodb_buffer_pool_chunk_size + performance_schema.memory]

Thread Buffers=（sort_buffer_size + myisam_sort_buffer_size + read_buffer_size + join_buffer_size + read_rnd_buffer_size + thread_stack)

Thread Pool

mariadb传统上为每个客户端连接分配一个线程，随着并发用户数量的增长，服务器性能会逐渐下降，因为线程数量越多，上下文切换会更加频繁，从而导致CPU大部分时间在处理上下文切换上，而没有真正的去处理业务。所以在mariadb5.1增加了Thread Pool，线程池技术，不过最早的Thread Pool是静态线程池，Thread Pool理的线程数量是固定的，并不能实现动态的增加和减少。在mariadb5.5版时本对其修改，变成了真正的上的Thread Pool技术，支持动态/自适应，能够自动增加，回收线程。
Thread Pool对降低CPU使用率有很大的作用，强烈推荐使用

如何配置
编辑my.cnf，在[mysqld]增加如下内容

[mysqld]
# 开启线程池
thread_handling=pool-of-threads


# 设置线程池数量，默认为CPU核数，设置成和核数一致，如果有其他程序，建议降低一下值，留给其他应用一些
thread_pool_size=8

# 线程池内线程超时退出，默认为60s
thread_pool_idle_timeout=300

# 检测线程轮询时间，默认为500毫秒，防止有进程长时间占用线程池内的线程数量
# 如果你的程序内有长时间运行的查询，将其设置为更高的值有助于避免启动过多的并行线程。
# 设置为较低的值有助于防止死锁。
thread_pool_stall_limit=300

完整my.cnf设置

如果你已经mariadb中用上了上面的配置，的mariadb应该有很大的提升，另外还有一些其他方面的配置，一并贴了出来，已下为完整my.cnf文件

[client]
socket = /dev/shm/mysql.sock

[mysqld]
# 配置数据目录
datadir = /home/mysql

#配置mysql目录
basedir = /home/mysql

# 配置innodb缓冲池大小
innodb_buffer_pool_size = 10G

# 配置innodb缓冲区实例数量，instances每个至少1G
innodb_buffer_pool_instances=10

# 开启redo-log
innodb-log-file-size=1G

# 开启ibdata自动扩展
innodb_data_file_path=ibdata1:12M:autoextend

# 设置日志缓冲区写入 InnoDB 重做日志的方式
innodb_flush_log_at_trx_commit = 2

# 开启多线程刷新脏页
innodb_use_mtflush = ON

# 设置清理线程页面数，默认为4，可以配置为innodb_buffer_pool_instances数
innodb_page_cleaners=10

# 设置最大连接数
max_connections = 800

# 设置线程复用数量，如果未达到此限制，则当客户端断开连接时，其线程将被放入缓存中
# 如果在5分钟内在此连接无需新建连接，空闲超过5分钟则需要重新连接
# 数量不要超过max_connections，超过max_connections，则以max_connections配置的数量为准
thread_cache_size=1000

# 设置myisam INDEX缓冲区大小
key_buffer_size=5M

table_definition_cache=1000

# 开启线程池
thread_handling=pool-of-threads


# 设置线程池数量，默认为CPU核数，设置成和核数一致，如果有其他程序，建议降低一下值，留给其他应用一些
thread_pool_size=6

# 线程池内线程超时退出，默认为60s
thread_pool_idle_timeout=300

# 检测线程轮询时间，默认为500毫秒，防止有进程长时间占用线程池内的线程数量
# 如果你的程序内有长时间运行的查询，将其设置为更高的值有助于避免启动过多的并行线程。
# 设置为较低的值有助于防止死锁。
thread_pool_stall_limit=300

# 设置临时表大小
max_heap_table_size = 32M


# 开启大页内存支持，需要在Linux里配置好Hugepage
large-pages=ON

# 设置隔离级别为读-提交
transaction-isolation = READ-COMMITTED

# 开启慢查询日志，记录慢查询语句
slow_query_log

# 开启错误日志，记录mariadb错误
log-error

# 开启严格模式
innodb_strict_mode = ON

# 开启事件调度
event_scheduler = ON

# 设置记录bin-log方式
binlog_format = ROW

# 是否解析主机名
skip-name-resolve

# 设置mariadb字符集
character-set-server = utf8

# 统一日志名称，开启后所有的日志都会以master开头，包括bin-log,slow.log,err.log等
log-basename = master1

# 开启binlog
log-bin

# bin-log循环时间
expire_logs_days = 30

# 记录binlog的库
binlog-do-db = db1

# 配置socket目录
socket = /dev/shm/mysql.sock

table_open_cache = 20000

# 设置临时文件目录
tmpdir = /dev/shm

使用mysqltuner.pl调优工具

mysqltuner.pl使用perl语言写的高性能MySQL调优脚本，国外的很多DBA再用，甚至没有DBA经验的运维同学，也可以根据其建议修改合里的值，使用起来非常方便

下载地址：
github
https://github.com/major/MySQLTuner-perl
官方主页
http://mysqltuner.pl/
下载完成后，运行 prel mysqlturn.pl，输入root用户和密码即可开始自动进行检测，并给出详细的调优参数，非常方便：

[root@local-test user]# perl mysqlturn.pl

# 扫描后给的建议
General recommendations:
    You are using n unsupported version for production environments
    Upgrade as soon as possible to a supported version !
    Check warning line(s) in /home/mysql/err.log file
    Check error line(s) in /home/mysql/err.log file
    Restrict Host for 'root'@'%' to 'root'@LimitedIPRangeOrLocalhost
    RENAME USER 'root'@'%' TO 'root'@LimitedIPRangeOrLocalhost;
    Reduce your overall MySQL memory footprint for system stability
    Dedicate this server to your database for highest performance.
    Consider installing Sys schema from https://github.com/mysql/mysql-sys for MySQL
    Before changing innodb_log_file_size and/or innodb_log_files_in_group read this: https://bit.ly/2TcGgtU
    
# 需要修改的变量值
Variables to adjust:
  *** MySQL's maximum memory usage is dangerously high ***
  *** Add RAM before increasing MySQL buffer variables ***
    table_definition_cache(400) > 879 or -1 (autosizing if supported)
    key_buffer_size (~ 24M)
    innodb_buffer_pool_size (>= 8.1G) if possible.
    innodb_log_file_size should be (=32M) if possible, so InnoDB total log files size equals to 25% of buffer pool size.

总结

从以上来看mariadb调优主要还是围绕着innodb_buffer_pool_size进行，包括官方文档里也把innodb_buffer_pool_size优化排到了第一位，可见其重要性，而我通过实验后也确实innodb_buffer_pool_size优化后确实对tps，以及ops提升非常大，如果服务器可用内存确实多的话，建议多分一些innodb_buffer_pool_size；
使用mariadb Thread Pool技术，对于降低CPU负载帮助非常大，这个一定要添加进去，并且后续还有一些关于Thread Pool的一些其他参数，在mariadb官方文档里有确切描述，感兴趣的通过学可以去看尾部的参考链接进行查看；
如果毫无优化经验的同学，可以借助第三调优工具进行优化，在优化的过程中去查找这些优化参数是什么具体意思，孰能生巧，久了以后自然会理解；
mariadb优化并非一朝一夕，需要有大量的测试和数据支撑，才能达到巅峰，我们的目的是在调优中，学习关键参数的值起的具体作用，从而加强自己的理论知识，以及结合实验，去实践这些参数到底能给mariadb带来什么样的性能提升，或者下降，并且在实验的过程中进一步加强我们对这些参数的理解；

参考来源

mariadb官方文档：
https://mariadb.com/kb/en/optimization-and-tuning/
https://mariadb.com/kb/en/standard-replication/
https://mariadb.com/kb/en/galera-cluster/
https://mariadb.com/kb/en/mariadb-memory-allocation/
https://mariadb.com/kb/en/thread-pool-in-mariadb/
https://mariadb.com/kb/en/server-status-variables/#max_used_connections
percona文档
https://www.percona.com/blog/2007/11/01/innodb-performance-optimization-basics/
http://woshub.com/compress-defrag-optimize-mariadb-mysql-db/

hzcm1984

关注

1
点赞
踩
7

收藏

觉得还不错? 一键收藏
0
评论
maraidb优化思路（二）maraidb层面

写在前面首先本人并非专职DBA，而是一个小小运维，如果这篇文章里有不对的地方，还希望各位大佬留言指出不正之处，小弟在此谢过！mariadb层面集群技术mariadb的集群技术主要包括如下2种：MariaDB ReplicationMaradb复制技术，就是经常所说的主从复制和多源复制，允许将一个或者多个服务器（主服务器，从10.5开始已经改为_primary_ ）内容复制到一个或者多个服务器上（副本）这样做的目的，就是对mariadb server进行读写分离，把查询分布到到多个Slave（从
复制链接

扫一扫