👀 MySQL 服务器硬件优化指南
🔧 服务器硬件优化建议
-
🖥️ 内存配置:
- 📈 增加内存:推荐配置尽可能多的内存,特别是在处理大数据量的情况下。
- ⚙️ 参数调整:例如调整
innodb_buffer_pool_size
来最大化利用可用内存。
-
💾 磁盘系统:
- 🚀 SSD选择:选择高速的SSD磁盘,特别是对于高I/O的数据库系统。
- 🛠️ RAID配置:使用RAID配置来增加读写速度和数据冗余。
- 📂 分散I/O:为MySQL的数据目录、日志文件等使用不同的磁盘或分区。
-
🔥 多处理器:
- 🧠 多核心:选择多核心的处理器以支持MySQL的多线程运行。
- 🎚️ 多插槽:考虑使用多插槽服务器并配置多个物理处理器。
-
🌐 网络配置:
- 🚀 高速卡:使用高速的网络接口卡,例如10GbE。
- 🌏 带宽:为数据库服务器配置足够的带宽。
🔨 工具建议
- 🔍 sysbench:全面的性能测试工具。
- 📊 iibench-mysql:专为MySQL/Percona/MariaDB设计。
- 📈 tpcc-mysql:模拟真实世界负载。
🔥 MySQL 较重要的参数
- 高并发环境下的MySQL服务器调优参数的推荐和原因
🎯 优化最大连接数 max_connections
🔹 默认值:151
📝 推荐:基于预期的并发连接数调整。可以设置为500或更高,但要确保操作系统的文件描述符限制允许。
🔍 原因:允许更多客户端同时连接到数据库,增加并发处理能力。
📝 配置文件示例:
# /etc/my.cnf
[mysqld]
max_connections=1000
🛠 优化线程缓存大小 thread_cache_size
🔹 默认值:通常为8或9,取决于系统
📝 推荐:通常设置为预期的并发连接数的10%。
🔍 原因:减少线程创建和销毁的开销,从而快速响应新的连接请求。
📝 配置文件示例:
# /etc/my.cnf
[mysqld]
thread_cache_size=100
📊 优化表打开缓存 table_open_cache
🔹 默认值:2000
📝 推荐:基于打开表的数量调整。可以设置为2000或更高。
🔍 原因:减少了打开和关闭表的开销,特别是当有大量表被访问时。
📝 配置文件示例:
# /etc/my.cnf
[mysqld]
table_open_cache=2048
💾 优化InnoDB缓存 innodb_buffer_pool_size
🔹 默认值:128M
📝 推荐:设置为总物理内存的60-70%是常见的做法。确保留有足够的内存给其他进程。
🔍 原因:这是InnoDB存储引擎的中心缓存,用于缓存表数据和索引,使得读操作更快。
📝 配置文件示例:
# /etc/my.cnf
[mysqld]
innodb_buffer_pool_size=16G
🖥 优化InnoDB日志 innodb_log_file_size
和 innodb_log_buffer_size
🔹 默认值:innodb_log_file_size
: 48M, innodb_log_buffer_size
: 16M
📝 推荐:innodb_log_file_size
设置为缓冲池大小的10-25%。innodb_log_buffer_size
可以设置为16M或32M。
🔍 原因:大的日志文件和缓冲区可以更好地处理大量的写入操作,并减少I/O等待。
📝 配置文件示例:
# /etc/my.cnf
[mysqld]
innodb_log_file_size=4G
innodb_log_buffer_size=32M
🕐 优化查询缓存 query_cache_size
🔹 默认值:0 (缓存被禁用)
📝 推荐:如果启用,一般不超过256M。但在高并发写环境中,最好禁用。
🔍 原因:虽然查询缓存可以加速查询,但在高并发写的情况下,它可能导致性能下降。
📝 配置文件示例:
# /etc/my.cnf
[mysqld]
query_cache_size=0
🗂 优化排序和连接缓冲区 sort_buffer_size
和 join_buffer_size
🔹 默认值:sort_buffer_size
: 2M, join_buffer_size
: 256K
📝 推荐:
- sort_buffer_size
: 4M
- join_buffer_size
: 4M
🔍 原因:较大的排序和连接缓冲区可以加速复杂查询的处理,但过大的缓冲区可能会浪费内存。
📝 配置文件示例:
# /etc/my.cnf
[mysqld]
sort_buffer_size=4M
join_buffer_size=4M
🌐 优化等待连接的请求队列 back_log
🔹 默认值:50
📝 推荐:设置为预期的并发连接数的50%。
🔍 原因:允许MySQL接受更多的未连接请求,从而增加新连接的速度。
📝 配置文件示例:
# /etc/my.cnf
[mysqld]
back_log=500
📢 MySQL 服务器优化指南
🖥️ 内存优化
① innodb_buffer_pool_size
- 🎯 目的:缓存InnoDB表的索引、数据以及插入数据时的缓冲。
- 📌 建议:如果是专用DB服务器,该值可以设置为内存的70%-80%。对于较小数据量,可以设置为数据大小+10%。
- 📝 设置:
innodb_buffer_pool_size=4G
② innodb_additional_mem_pool_size
- 🎯 目的:存放InnoDB的内部目录信息。
- 📌 建议:默认16M通常足够,但如果表较多可以适当增大。
- 📝 设置:
innodb_additional_mem_pool_size=16M
📝 日志优化
① innodb_log_file_size
- 🎯 目的:指定在一个日志组中,每个log的大小。
- 📌 建议:一般取值为256M,以兼顾性能和恢复速度。
- 📝 设置:
innodb_log_file_size=256M
② innodb_log_files_in_group
- 🎯 目的:指定日志组的数量。
- 📌 建议:默认为2,但可以设置为2-3。
- 📝 设置:
innodb_log_files_in_group=3
③ innodb_log_buffer_size
- 🎯 目的:调整InnoDB存储引擎的事务日志缓冲区大小。
- 📌 建议:对于需要处理大量TEXT或BLOB字段的场景,可以增大该值。
- 📝 设置:
innodb_log_buffer_size=64M
④ innodb_flush_log_at_trx_commit
- 🎯 目的:控制事务的提交方式和日志刷新到磁盘的方式。
- 📌 建议:默认为1,但可以根据需要设置为0或2。
- 📝 设置:
innodb_flush_log_at_trx_commit=1
💽 文件IO与空间占用
① innodb_file_per_table
- 🎯 目的:为每个InnoDB表分配独立的表空间。
- 📌 建议:开启此选项,以便在删除文件后回收相应的空间。
- 📝 设置:
innodb_file_per_table=1
② innodb_read_io_threads
/ innodb_write_io_threads
- 🎯 目的:调整读/写请求的后台线程数量。
- 📝 设置:
innodb_read_io_threads=4 innodb_write_io_threads=4
③ innodb_open_files
- 🎯 目的:限制InnoDB可以打开的表文件数量。
- 📌 建议:默认为300,但如果有大量表则可以增加至1000。
- 📝 设置:
innodb_open_files=800
④ innodb_data_file_path
- 🎯 目的:指定表数据和索引存放的空间位置。
- 📝 设置:
innodb_data_file_path=ibdata1:1G;ibdata2:1G;ibdata3:1G;ibdata4:1G;ibdata5:1G;ibdata6:1G:autoextend
⑤ innodb_data_home_dir
- 🎯 目的:设置表空间数据的存放目录。
- 📝 设置:
innodb_data_home_dir=/home/MySQL
💡 MySQL其他参数调优
① innodb_max_dirty_pages_pct
-
🎯 作用:决定了 Buffer Pool 中可以容纳的 InnoDB 的脏页的百分比。
-
📝 另一个用处:当Innodb的内存分配过大,致使Swap占用严重时,可以适当的减小调整这个值,使达到Swap空间释放出来。
-
💡 建议:这个值最大建议在 90%
(也就是默认值)
,最小在 15%。如果设置得太大,缓存中的数据更新会导致过多的数据页替换;如果设置得太小,放入的数据页太少,可能会使更新操作变得很慢。 -
🔧 设置示例:
SET GLOBAL innodb_max_dirty_pages_pct=90;
② innodb_thread_concurrency
- 🎯 作用:决定了同时在 InnoDB 内核中处理的线程数量。
- 💡 建议:建议保持默认值,除非有特定的性能问题需要调整此参数。
- 🔧 设置示例:
SET GLOBAL innodb_thread_concurrency=16;
③ max_connections
- 🎯 作用:决定了可以连接到 MySQL 数据库的最大客户端连接数。表示允许临街到MySQL数据库的最大数量,默认值是151。如果状态变量connection_errors_max_connections不为零,并且一直增长,则说明不断有连接请求因数据库连接数已达到允许最大值而失败,这时可以考虑增大max_connections的值。
- 💡 建议: 较理想的设置是:max_user_connections / max_connections * 100% ≈ 85% 。最大连接数占上限连接数的85%左右,如果发现比例在10%以下,MySQL服务器连接数上限设置的过高了。
- 🔧 设置示例:
SET GLOBAL max_connections=200;
④ query_cache_size
- 🎯 作用:定义查询缓存的大小。它是用于存储 SELECT 语句及其结果集的空间。
- 💡 建议:如果数据库有高查询复用率,增加此值可能有助于提高性能。但如果数据更新非常频繁,高的查询缓存可能会变得低效。
- 🔧 设置示例:
SET GLOBAL query_cache_size=1048576;
相关配置
query_cache_type指定是否使用查询缓冲,可以设置为0、1、2,该变量是SESSION级的变量。
query_cache_type值为0时,所有的查询都不使用查询缓存区。但是query_cache_type=0并不会导致MySQL释放query_cache_size所配置的缓存区内存。
query_cache_type=1时,所有的查询都将使用查询缓存区,除非在查询语句中指定SQL_NO_CACHE。
query_cache_type=2时,只有在查询语句中使用SQL_CACHE关键字,查询才会使用查询缓存区。使用查询缓存区可以提高查询的速度,这种方式只适用于修改操作少且经常执行相同的查询操作的情况
query_cache_limit指定单个查询能够使用的缓冲区大小,缺省为1M。
query_cache_min_res_unit是在4.1版本以后引入的,它指定分配缓冲区空间的最小单位,缺省为4K。
⑤ sort_buffer_size
-
🎯 作用:为每个会话分配的排序缓冲区的大小。
-
💡 建议:如果您的查询需要大量排序,可以考虑增加此值。但不应该设置得过高,以免浪费内存。对于内存在4GB左右的服务器推荐设置为6-8M,如果有100个连接,那么实际分配的总共排序缓冲区大小:100*6=600MB。
-
🔧 设置示例:
SET GLOBAL sort_buffer_size=2097152;
⑥ read_buffer_size
- 🎯 作用:为顺序扫描每个表分配的缓冲区的大小。
- 💡 建议:如果您的查询进行了大量的全表扫描,可以考虑增加此值。但要注意不要过度分配内存。
- 🔧 设置示例:
SET GLOBAL read_buffer_size=131072;
⑦ max_allowed_packet
- 🎯 作用:定义了客户端和服务器之间可以传输的最大数据包大小。
- 💡 建议:如果您的应用需要传输大量数据,或者您收到有关数据包太大的错误消息,应增加此值。
- 🔧 设置示例:
SET GLOBAL max_allowed_packet=16777216; -- 设置为16MB
⑧ table_open_cache
- 🎯 作用:定义了服务器可以为打开表缓存的数量。
- 💡 建议:如果您的应用打开了大量的表,应增加此值,但要注意不要过度分配内存。
- 🔧 设置示例:
SET GLOBAL table_open_cache=2000;
⑨ thread_cache_size
- 🎯 作用:定义了线程缓存中可以存储的线程数量。
- 💡 建议:如果您的服务器有高并发,增加此值可能有助于减少线程创建的开销。
- 🔧 设置示例:
SET GLOBAL thread_cache_size=100;
⑩ key_buffer_size
- 🎯 作用:为 MyISAM 表定义了索引块的缓冲区大小。
- 💡 建议:如果您使用 MyISAM 表并需要优化读取速度,增加此值。
- 🔧 设置示例:
SET GLOBAL key_buffer_size=16777216; -- 设置为16MB
⑪ innodb_lock_wait_timeout
- 🎯 作用:定义了一个事务等待一个锁的最长时间。
- 💡 建议:在高并发环境中,考虑减少此值,以减少死锁等待时间。
- 🔧 设置示例:
SET GLOBAL innodb_lock_wait_timeout=50;
⑫ innodb_support_xa
- 🎯 作用:决定了 InnoDB 是否支持两阶段提交。
- 💡 建议:如果您需要XA事务支持,设置此值为1。否则,可以设置为0来提高性能。
- 🔧 设置示例:
SET GLOBAL innodb_support_xa=1;
⑬ innodb_io_capacity
- 🎯 作用:定义了InnoDB每秒可以执行的I/O操作数。
- 💡 建议:根据您的硬盘性能进行调整。对于SSD硬盘,可能需要更高的值。
- 🔧 设置示例:
SET GLOBAL innodb_io_capacity=2000;
⑭ innodb_use_native_aio
- 🎯 作用:决定了是否使用本地异步I/O子系统。
- 💡 建议:在支持异步I/O的系统上,设置此值为1以提高性能。
- 🔧 设置示例:
SET GLOBAL innodb_use_native_aio=1;
⑮ innodb_buffer_pool_instances
- 🎯 作用:定义了 InnoDB buffer pool 的实例数量。
- 💡 建议:对于具有多个CPU核心的系统,增加此值可能有助于提高并发性能。
- 🔧 设置示例:
SET GLOBAL innodb_buffer_pool_instances=8;
⑯ innodb_purge_threads
- 🎯 作用:指定InnoDB用于清除(删除)旧版本行的线程数。
- 💡 建议:根据系统的并发需求进行调整。较高的值可能有助于在高并发系统中清除旧版本的行。
- 🔧 设置示例:
SET GLOBAL innodb_purge_threads=4;
⑰ innodb_flush_method
- 🎯 作用:指定InnoDB如何刷新数据和日志。
- 💡 建议:对于大多数平台,推荐使用
O_DIRECT
以减少OS缓存的使用。 - 🔧 设置示例:
SET GLOBAL innodb_flush_method='O_DIRECT';
总结一下三者写数据方式:
FDATASYNC模式
:写数据时,write这一步并不需要真正写到磁盘才算完成(可能写入到操作系统buffer中就会返回完成),真正完成是flush操作,buffer交给操作系统去flush,并且文件的元数据信息也都需要更新到磁盘。
O_DSYNC模式
:写日志操作是在write这步完成,而数据文件的写入是在flush这步通过fsync完成
O_DIRECT模式
:数据文件的写入操作是直接从MySQL innodb
buffer到磁盘的,并不用通过操作系统的缓冲,而真正的完成也是在flush这步,日志还是要经过OS缓冲
⑱ back_log
- 🎯 作用:指定服务器在开始拒绝连接之前,多少个未连接的连接队列。
- 💡 建议:对于高并发的系统,增加此值。
- 🔧 设置示例:
SET GLOBAL back_log=1000;
⑲ interactive_timeout
- 🎯 作用:指定服务器等待交互式连接的秒数。
- 💡 建议:保持默认值或根据应用需求进行调整。
- 🔧 设置示例:
SET GLOBAL interactive_timeout=28800;
⑳ read_rnd_buffer_size
- 🎯 作用:每个客户端排序操作后的缓冲区大小。
- 💡 建议:如果进行了大量排序操作,考虑增加此值。
- 🔧 设置示例:
SET GLOBAL read_rnd_buffer_size=262144; -- 设置为256KB
㉑ join_buffer_size
- 🎯 作用:指定了在执行JOIN操作时,每个连接可用的缓冲区大小。
- 💡 建议:如果有大量的JOIN操作,考虑增加此值。
- 🔧 设置示例:
SET GLOBAL join_buffer_size=262144; -- 设置为256KB
㉒ table_cache
-
🎯 作用:指定服务器可以为打开表缓存的数量。
-
💡 建议:根据应用打开的表数量进行调整。表高速缓存的大小,表示同时打开的表的个数。这个值越大,能够同时打开的表的个数越多。物理内存越大,设置就越大。默认为2402,调到512-1024最佳。这个值不是越大越好,因为同时打开的表太多会影响操作系统的性能。
-
🔧 设置示例:
SET GLOBAL table_cache=2000;
㉓ max_heap_table_size
- 🎯 作用:指定用户可以创建的内存表的最大大小。
- 💡 建议:如果您的应用使用了大量的内存表,增加此值。
- 🔧 设置示例:
SET GLOBAL max_heap_table_size=16777216; -- 设置为16MB
㉔ tmp_table_size
- 🎯 作用:定义了内存临时表的最大大小。
- 💡 建议:如果您的查询需要大量的临时表,增加此值。
- 🔧 设置示例:
SET GLOBAL tmp_table_size=16777216; -- 设置为16MB
㉕ wait_timeout
- 🎯 作用:定义了非交互式连接在被认为是死连接之前的空闲时间。
- 💡 建议:根据应用的需求进行调整。
- 🔧 设置示例:
SET GLOBAL wait_timeout=28800;
㉖ skip_name_resolve
- 🎯 作用:决定是否跳过主机名解析。
- 💡 建议:如果您不需要基于主机名的权限检查,并希望提高连接速度,设置此值为1。
- 🔧 设置示例:
SET GLOBAL skip_name_resolve=1;
㉗ record_buffer
- 🎯 作用:已被弃用并被
read_buffer_size
替代。 - 💡 建议:不建议使用此参数。
📌 提醒:在调整参数之前,请确保在测试环境中进行评估,以确保不会对生产环境造成不良影响。
🚀 实践总结
① 查看线程使用情况
-
📜 命令:
show variables like '%thread%';
输出:
thread_cache_size 10 thread_handling one-thread-per-connection thread_stack 262144
show global status like 'Thread%';
输出:
Threads_cached 6 Threads_connected 4 Threads_created 306 Threads_running 1
-
🎯 作用:查看MySQL服务器的线程使用情况。
-
💡 建议:如果发现
Threads_created
值过大,表示MySQL服务器一直在创建线程,这是比较耗资源的,可以适当增加配置文件中thread_cache_size
的值。
② 查询缓存
-
📜 命令:
show global status like 'qcache%';
输出:
Qcache_free_blocks 1 Qcache_free_memory 1031872 Qcache_hits 0 Qcache_inserts 0 Qcache_lowmem_prunes 0 Qcache_not_cached 135148 Qcache_queries_in_cache 0 Qcache_total_blocks 1
-
🎯 作用:查看查询缓存的使用情况。
-
💡 建议:确保查询缓存利用率在25%-80%范围内。如果查询缓存碎片率超过20%,使用
FLUSH QUERY CACHE
整理缓存碎片,或减小query_cache_min_res_unit
。
③ 关于排序
-
📜 命令:
show global status like 'sort%';
输出:
Sort_merge_passes 0 Sort_range 119 Sort_rows 8366 Sort_scan 1210
-
🎯 作用:查看排序操作的统计数据。
-
💡 建议:增加
Sort_buffer_size
以减少Sort_merge_passes
和创建临时文件的次数。
④ 关于打开文件数
- 📜 命令:
show variables like 'open_files_limit'; show global status like 'open_files%';
- 💡 建议:确保
Open_files / open_files_limit * 100%
不超过75%。
⑤ 关于表锁
-
📜 命令:
show global status like 'table_locks%';
输出:
Table_locks_immediate 364 Table_locks_waited 0
-
💡 建议:如果
Table_locks_immediate / Table_locks_waited
大于5000,考虑使用InnoDB引擎。
⑥ 关于表扫描情况
-
📜 命令:
show global status like 'handler_read%';
输出:
Handler_read_first 24357 Handler_read_key 37614 Handler_read_last 1375 Handler_read_next 64846 Handler_read_prev 17505 Handler_read_rnd 8268 Handler_read_rnd_next 5513400
-
📜 命令:
show global status like 'com_select%';
-
💡 建议:如果表扫描率超过4000,考虑优化索引或增加
read_buffer_size
,但不要超过8MB。
📘 MySQL服务器调优参数配置参考
📁 MySQL配置文件参考:
[mysqld]
port = 3306 # MySQL监听的端口号
serverid = 1 # 服务器ID
socket = /tmp/mysql.sock # 用于本地客户端连接的UNIX套接字文件
skip-locking # 避免MySQL的外部锁定,增强稳定性
skip-name-resolve # 禁止DNS解析,需使用IP地址方式授权
back_log = 384 # 等待监听队列的最大长度
key_buffer_size = 256M # MyISAM表的键缓存大小
max_allowed_packet = 4M # 允许客户端和服务器之间传输的最大数据包大小
thread_stack = 256K # 每个线程的堆栈大小
table_cache = 128K # 打开表的数量
sort_buffer_size = 6M # 用于排序的缓冲区大小
read_buffer_size = 4M # 顺序扫描操作的缓冲区大小
read_rnd_buffer_size = 16M # 随机读取的缓冲区大小
join_buffer_size = 8M # 用于联接操作的缓冲区大小
myisam_sort_buffer_size = 64M # 用于MyISAM表的排序缓冲区大小
thread_cache_size = 64 # 缓存线程的数量
query_cache_size = 64M # 查询缓存大小
tmp_table_size = 256M # 内部临时表的最大大小
max_connections = 768 # 允许的最大并发连接数
max_connect_errors = 10000000 # 连接错误尝试的数量
wait_timeout = 10 # 无活动的非交互式连接的超时时间
thread_concurrency = 8 # 取值为服务器逻辑CPU数量*2
skip-networking # 若使用远程连接,不要开启
innodb_additional_mem_pool_size = 4M # InnoDB用于某些内部数据结构的内存池大小
innodb_flush_log_at_trx_commit = 1 # 控制日志写入和刷新的方式
innodb_log_buffer_size = 2M # InnoDB日志缓冲区大小
innodb_thread_concurrency = 8 # 与服务器CPU数量保持一致,表示InnoDB尝试创建的线程数
📌 注意事项:
- 上述配置只是一个参考,根据实际情况进行调整。
- 遇到CPU持续高升时,考虑调整
InnoDB_buffer_pool_size
和InnoDB_buffer_pool_instances
。 InnoDB_buffer_pool_instances
参数可以将InnoDB缓存区分成多个部分,提高系统并行处理能力,允许多个进程同时处理不同部分的缓存区,从而提高CPU效率。