MariaDB教程FG005-MariaDB my.cnf核心参数官方详解与生产调优实战

本文档风哥主要介绍MariaDB的my.cnf核心参数配置与性能调优，包括配置文件结构、核心参数详解、调优方法和实战案例等内容。风哥教程参考MariaDB官方文档Performance Tuning内容，适合数据库管理员学习和实施。

Part01-基础概念与理论知识

1.1 my.cnf配置文件概述

my.cnf是MariaDB的主配置文件，用于设置数据库的各种参数。它的位置通常在：

/etc/my.cnf
/etc/mysql/my.cnf
~/.my.cnf
MariaDB安装目录下的my.cnf

1.2 核心参数分类

MariaDB核心参数主要分为以下几类：

基本配置参数
性能优化参数
存储引擎参数
安全配置参数
复制相关参数

1.3 调优原则

MariaDB调优的基本原则：

根据硬件配置调整参数
根据业务负载调整参数
循序渐进，逐步调整
监控调整效果
保持参数配置的一致性

更多视频教程www.fgedu.net.cn

Part02-生产环境规划与建议

2.1 参数规划

风哥提示：参数规划应根据服务器硬件配置、业务类型和负载情况进行定制。

2.2 硬件因素考虑

硬件因素对参数配置的影响：

内存大小：影响innodb_buffer_pool_size等参数
CPU核心数：影响innodb_thread_concurrency等参数
磁盘类型：影响innodb_flush_method等参数
网络带宽：影响连接数和复制相关参数

2.3 工作负载分析

工作负载分析包括：

查询类型：OLTP还是OLAP
并发连接数
数据量大小
读写比例
高峰时段负载

学习交流加群风哥微信: itpux-com

Part03-生产环境项目实施方案

3.1 my.cnf配置文件设置

更多学习教程公众号风哥教程itpux_com

# 基本配置结构
[root@fgedu.net.cn ~]# cat /etc/my.cnf
[mysqld]
# 基本参数
user = mysql
datadir = /mariadb/fgdata
socket = /tmp/mysql.sock
pid-file = /mariadb/fgdata/mysql.pid
# 性能参数
innodb_buffer_pool_size = 8G
innodb_log_file_size = 2G
max_connections = 2000
# 存储引擎参数
innodb_file_per_table = 1
innodb_flush_method = O_DIRECT
# 安全参数
bind-address = 0.0.0.0
skip-name-resolve
[client]
socket = /tmp/mysql.sock
[mysql]
prompt = MariaDB [\d]> \_
[mysqld_safe]
log-error = /mariadb/logs/mariadb.err
pid-file = /mariadb/fgdata/mysql.pid

3.2 性能参数调优

# 内存相关参数
innodb_buffer_pool_size = 8G # 通常设置为物理内存的50-70%
innodb_buffer_pool_instances = 8 # 每个实例不超过1G
innodb_log_buffer_size = 16M # 通常设置为16-64M
# 磁盘I/O参数
innodb_flush_method = O_DIRECT # 直接I/O，减少操作系统缓存
innodb_io_capacity = 2000 # 根据磁盘IOPS调整
innodb_io_capacity_max = 4000 # 最大IOPS
# 并发参数
innodb_thread_concurrency = 16 # 通常设置为CPU核心数的2倍
max_connections = 2000 # 根据业务需求调整
# 查询优化参数
tmp_table_size = 64M # 临时表大小
max_heap_table_size = 64M # 内存表大小
table_open_cache = 4096 # 表缓存大小
# 连接参数
wait_timeout = 300 # 连接超时时间
interactive_timeout = 300 # 交互式连接超时时间
# 日志参数
innodb_log_file_size = 2G # 通常设置为innodb_buffer_pool_size的25%
innodb_log_files_in_group = 2 # 通常设置为2
# 其他参数
skip_name_resolve = 1 # 跳过域名解析，提高连接速度
low_priority_updates = 1 # 降低更新操作优先级
concurrent_insert = 2 # 允许并发插入

3.3 监控与调整

# 监控参数
[root@fgedu.net.cn ~]# mysql -u root -p
Enter password:
# 查看全局状态
MariaDB [(none)]> SHOW GLOBAL STATUS;
# 查看特定状态
MariaDB [(none)]> SHOW GLOBAL STATUS LIKE ‘Innodb_buffer_pool_%’;
MariaDB [(none)]> SHOW GLOBAL STATUS LIKE ‘Threads_%’;
MariaDB [(none)]> SHOW GLOBAL STATUS LIKE ‘Connections’;
# 查看变量
MariaDB [(none)]> SHOW VARIABLES LIKE ‘innodb_buffer_pool_size’;
MariaDB [(none)]> SHOW VARIABLES LIKE ‘max_connections’;
# 监控工具
[root@fgedu.net.cn ~]# yum install -y mytop
[root@fgedu.net.cn ~]# mytop -u root -p password

学习交流加群风哥QQ113257174

Part04-生产案例与实战讲解

4.1 my.cnf配置实战

# 生产环境my.cnf示例
[root@fgedu.net.cn ~]# cat /etc/my.cnf
[mysqld]
# 基本配置
user = mysql
basedir = /usr/local/mariadb
datadir = /mariadb/fgdata
socket = /tmp/mysql.sock
pid-file = /mariadb/fgdata/mysql.pid
port = 3306
# 性能配置
innodb_buffer_pool_size = 16G
innodb_buffer_pool_instances = 16
innodb_log_file_size = 4G
innodb_log_files_in_group = 2
innodb_flush_method = O_DIRECT
innodb_file_per_table = 1
innodb_io_capacity = 4000
innodb_io_capacity_max = 8000
innodb_thread_concurrency = 32
# 连接配置
max_connections = 3000
max_connect_errors = 10000
wait_timeout = 300
interactive_timeout = 300
# 查询优化
tmp_table_size = 128M
max_heap_table_size = 128M
table_open_cache = 8192
table_definition_cache = 4096
sort_buffer_size = 4M
read_buffer_size = 4M
read_rnd_buffer_size = 16M
join_buffer_size = 16M
# 安全配置
bind-address = 0.0.0.0
skip-name-resolve
# 日志配置
log_error = /mariadb/logs/mariadb.err
slow_query_log = 1
slow_query_log_file = /mariadb/logs/slow.log
long_query_time = 1
# 复制配置
sync_binlog = 1
innodb_support_xa = 1
[client]
socket = /tmp/mysql.sock
[mysql]
prompt = MariaDB [\d]> \_
[mysqld_safe]
log-error = /mariadb/logs/mariadb.err
pid-file = /mariadb/fgdata/mysql.pid

4.2 参数调优实战

4.3 性能测试与验证

# 使用sysbench进行性能测试
[root@fgedu.net.cn ~]# yum install -y sysbench
# 准备测试数据
[root@fgedu.net.cn ~]# sysbench –db-driver=mysql –mysql-host=127.0.0.1 –mysql-port=3306 –mysql-user=root –mysql-password=password –mysql-db=fgedudb –table-size=1000000 –tables=10 prepare
# 运行OLTP测试
[root@fgedu.net.cn ~]# sysbench –db-driver=mysql –mysql-host=127.0.0.1 –mysql-port=3306 –mysql-user=root –mysql-password=password –mysql-db=fgedudb –table-size=1000000 –tables=10 –threads=16 –time=60 –report-interval=10 oltp_read_write run
# 测试结果示例
SQL statistics:
queries performed:
read: 183568
write: 52448
other: 26224
total: 262240
transactions:
total: 13112 (218.50 per sec.)
succeeded: 13112 (218.50 per sec.)
failed: 0 (0.00 per sec.)
execution time:
total: 60.011 s
System statistics:
total time: 60.015 s
total number of events: 13112
total time taken by event execution: 959.363
per-request statistics:
min: 4.84ms
avg: 73.17ms
max: 245.36ms
approx. 95 percentile: 135.67ms
Threads fairness:
events (avg/stddev): 819.5000/12.31
execution time (avg/stddev): 59.9602/0.01

风哥提示：安全开发是防止SQL注入的第一道防线

Part05-风哥经验总结与分享

5.1 调优常见问题与解决

内存不足：调整innodb_buffer_pool_size参数
连接数过多：调整max_connections参数，检查应用连接池配置
磁盘I/O瓶颈：调整innodb_io_capacity参数，使用SSD存储
慢查询：开启慢查询日志，优化SQL语句
锁等待：优化事务，减少锁持有时间

5.2 最佳实践

风哥提示：参数调优是一个持续的过程，需要根据业务变化和硬件升级不断调整。

5.3 故障排查

查看错误日志：/mariadb/logs/mariadb.err
查看慢查询日志：/mariadb/logs/slow.log
使用SHOW PROCESSLIST查看当前连接和查询
使用EXPLAIN分析SQL执行计划
使用Performance Schema监控性能

from MariaDB视频:www.itpux.com

本文由风哥教程整理发布,仅用于学习测试使用,转载注明出处:http://www.fgedu.net.cn/10327.html