Mysql - 服务器优化

yueerba126

已于 2023-10-02 11:11:10 修改

阅读量171

点赞数

分类专栏： Mysql 文章标签： mysql 服务器数据库

于 2023-10-01 13:00:48 首次发布

本文链接：https://blog.csdn.net/yueerba126/article/details/133460505

版权

Mysql 专栏收录该内容

20 篇文章 1 订阅

订阅专栏

👀 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效率。

yueerba126

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
打赏
0
评论
Mysql - 服务器优化

表高速缓存的大小，表示同时打开的表的个数。query_cache_type=2时，只有在查询语句中使用SQL_CACHE关键字，查询才会使用查询缓存区。query_cache_min_res_unit是在4.1版本以后引入的，它指定分配缓冲区空间的最小单位，缺省为4K。query_cache_type=1时，所有的查询都将使用查询缓存区，除非在查询语句中指定SQL_NO_CACHE。：当Innodb的内存分配过大，致使Swap占用严重时，可以适当的减小调整这个值，使达到Swap空间释放出来。
复制链接

扫一扫