外观
TDSQL 常用参数优化建议
参数优化原则
参数优化的重要性
- 合理的参数设置可以提高数据库性能
- 减少资源消耗，提高资源利用率
- 增强数据库的稳定性和可靠性
- 优化用户体验，提高业务响应速度
参数优化的核心原则
- 循序渐进：逐步调整参数，观察效果
- 根据业务场景：不同业务场景需要不同的参数设置
- 考虑硬件配置：参数设置应与硬件资源匹配
- 监控效果：调整后密切监控系统性能
- 备份配置：调整前备份当前配置
参数分类
主要参数类别
- 性能参数：影响数据库性能的参数
- 资源参数：控制资源使用的参数
- 安全参数：影响数据库安全的参数
- 日志参数：控制日志行为的参数
- 连接参数：控制连接管理的参数
性能参数优化
1. 查询缓存参数
sql
-- 启用查询缓存（注意：在高并发场景下可能影响性能）
query_cache_type = 0 -- 建议关闭，使用应用层缓存
query_cache_size = 0
query_cache_limit = 1M2. 排序和连接参数
sql
-- 排序缓冲大小
sort_buffer_size = 2M -- 建议：1M-4M
-- 连接缓冲大小
join_buffer_size = 2M -- 建议：1M-4M
-- 随机读缓冲大小
read_rnd_buffer_size = 2M -- 建议：1M-4M
-- 顺序读缓冲大小
read_buffer_size = 1M -- 建议：512K-2M3. 临时表参数
sql
-- 临时表大小
tmp_table_size = 64M -- 建议：32M-128M
-- 内存临时表大小
max_heap_table_size = 64M -- 建议与 tmp_table_size 一致
-- 临时文件目录
tmpdir = /tmp -- 建议使用高速存储资源参数优化
1. 内存参数
sql
-- 全局缓冲大小
key_buffer_size = 256M -- 建议：25%-30% 内存（MyISAM）
-- InnoDB 缓冲池大小
innodb_buffer_pool_size = 2G -- 建议：50%-70% 内存
-- InnoDB 缓冲池实例数
innodb_buffer_pool_instances = 4 -- 建议：4-8 个，根据 buffer_pool_size 调整
-- InnoDB 日志缓冲大小
innodb_log_buffer_size = 64M -- 建议：16M-128M2. 并发参数
sql
-- 最大连接数
max_connections = 1000 -- 建议：根据业务需求和硬件资源调整
-- 最大用户连接数
max_user_connections = 800 -- 建议：小于 max_connections
-- 线程缓存大小
thread_cache_size = 100 -- 建议：根据连接数调整
-- 打开文件数限制
open_files_limit = 65535 -- 建议：大于 max_connections * 53. InnoDB 相关参数
sql
-- InnoDB 数据文件路径
innodb_data_file_path = ibdata1:12M:autoextend
-- InnoDB 日志文件大小
innodb_log_file_size = 256M -- 建议：256M-1G
-- InnoDB 日志文件数量
innodb_log_files_in_group = 2 -- 建议：2-4 个
-- InnoDB 刷新日志策略
innodb_flush_log_at_trx_commit = 2 -- 建议：1（安全）或 2（性能）
-- InnoDB 刷新方法
innodb_flush_method = O_DIRECT -- 建议：O_DIRECT 或 fsync安全参数优化
1. 连接安全
sql
-- 禁止本地文件访问
local_infile = 0
-- 禁用符号链接
skip_symbolic_links = 1
-- 限制连接来源
-- 在配置文件中设置 bind-address 或使用防火墙2. 密码安全
sql
-- 密码验证插件
validate_password_policy = STRONG
-- 密码长度限制
validate_password_length = 8
-- 密码复杂度要求
validate_password_mixed_case_count = 1
validate_password_number_count = 1
validate_password_special_char_count = 1日志参数优化
1. 错误日志
sql
-- 错误日志路径
log_error = /var/log/mysql/error.log
-- 错误日志级别
log_error_verbosity = 3 -- 建议：2（警告和错误）或 3（所有）2. 慢查询日志
sql
-- 启用慢查询日志
slow_query_log = 1
-- 慢查询日志路径
slow_query_log_file = /var/log/mysql/slow.log
-- 慢查询阈值
long_query_time = 1 -- 建议：1-5 秒
-- 记录未使用索引的查询
log_queries_not_using_indexes = 1
-- 记录管理语句
log_slow_admin_statements = 13. 二进制日志
sql
-- 启用二进制日志
log_bin = mysql-bin
-- 二进制日志格式
binlog_format = ROW -- 建议：ROW（安全）或 MIXED（兼容）
-- 二进制日志过期时间
expire_logs_days = 7 -- 建议：7-30 天
-- 二进制日志缓存大小
binlog_cache_size = 32M -- 建议：8M-64M连接参数优化
1. 连接超时
sql
-- 连接超时时间
connect_timeout = 10 -- 建议：5-10 秒
-- 等待超时时间
wait_timeout = 28800 -- 建议：根据业务需求调整
-- 交互式连接超时
interactive_timeout = 28800 -- 建议：与 wait_timeout 一致2. 网络参数
sql
-- 最大允许数据包大小
max_allowed_packet = 64M -- 建议：16M-128M
-- 网络缓冲区大小
net_buffer_length = 16K -- 建议：16K参数优化最佳实践
1. 优化前准备
- 了解当前系统的硬件配置
- 分析业务场景和负载特征
- 监控当前系统的性能指标
- 备份当前配置文件
2. 优化步骤
- 确定需要优化的参数
- 查阅官方文档，了解参数含义和范围
- 在测试环境中进行调整和测试
- 观察测试结果，调整参数值
- 在生产环境中逐步实施
- 密切监控生产环境的性能变化
3. 监控与调整
- 监控系统性能指标（CPU、内存、磁盘 I/O、网络）
- 监控数据库性能指标（QPS、TPS、连接数、慢查询数）
- 根据监控结果调整参数
- 定期回顾和优化参数设置
4. 不同业务场景的参数调整
读密集型场景
- 增大 innodb_buffer_pool_size
- 优化缓存参数
- 调整查询缓存（如适用）
- 优化索引
写密集型场景
- 调整 innodb_flush_log_at_trx_commit
- 增大 innodb_log_buffer_size
- 优化 binlog 相关参数
- 调整临时表参数
高并发场景
- 增大 max_connections
- 优化线程缓存
- 调整连接超时参数
- 优化内存分配
常见问题（FAQ）
Q1: 如何确定需要优化的参数？
A1: 确定需要优化的参数的方法包括：
- 监控系统性能指标，找出瓶颈
- 分析慢查询日志，找出性能问题
- 查看数据库状态变量，识别异常
- 参考官方文档的推荐设置
- 根据业务场景和硬件资源调整
Q2: 如何备份和恢复数据库配置？
A2: 备份和恢复数据库配置的方法包括：
- 备份配置文件：
cp /etc/my.cnf /etc/my.cnf.bak - 恢复配置文件：
cp /etc/my.cnf.bak /etc/my.cnf - 使用命令行查看当前配置：
SHOW GLOBAL VARIABLES - 导出配置：
mysqladmin variables > variables.txt
Q3: 参数调整后是否需要重启数据库？
A3: 参数调整是否需要重启数据库取决于参数类型：
- 动态参数：不需要重启，使用
SET GLOBAL或SET SESSION命令即可生效 - 静态参数：需要修改配置文件并重启数据库才能生效
可以通过 SHOW VARIABLES 命令查看参数是否为动态参数。
Q4: 如何监控参数调整后的效果？
A4: 监控参数调整后效果的方法包括：
- 监控系统资源使用情况（CPU、内存、磁盘 I/O）
- 监控数据库性能指标（QPS、TPS、连接数、慢查询数）
- 分析慢查询日志，查看性能是否改善
- 观察业务响应时间是否缩短
- 检查错误日志，确保没有新的错误
Q5: 有没有通用的参数优化模板？
A5: 没有通用的参数优化模板，因为不同的业务场景、硬件配置和数据库版本需要不同的参数设置。建议：
- 参考官方文档的推荐设置
- 根据业务场景调整参数
- 逐步优化，观察效果
- 结合监控数据进行调整
- 定期回顾和优化参数设置