MySQL Server 性能优化参数设置

查看 MySQL Server 参数

在 MySQL 中，可以使用以下命令查看服务器参数：

SHOW VARIABLES; 
-- 查看所有服务器参数SHOW VARIABLES LIKE 'parameter_name'; 
-- 查看特定参数，例如 SHOW VARIABLES LIKE 'key_buffer_size';

影响 MySQL 性能的重要参数及设置

Key_buffer_size

• 功能：此参数主要用于 MyISAM 存储引擎，决定了索引块的缓冲区大小。增大该值可以提高 MyISAM 表的索引处理速度。
• 设置建议：一般将该值设置为系统内存的 25% 左右，具体大小可根据服务器内存大小和 MyISAM 表的使用频率调整。例如，如果服务器内存为 8GB，可设置为 2GB：

SET GLOBAL key_buffer_size = 2 * 1024 * 1024 * 1024; 

• 注意事项：如果主要使用 InnoDB 存储引擎，该参数不用设置过大，因为 InnoDB 有自己的缓冲池。

table_cache

• 功能：该参数决定了能同时打开的表的数量。合理设置可减少文件打开和关闭的开销。
• 设置建议：根据系统的并发连接数和表的数量，设置为 max_connections * N，其中 N 是每个连接平均使用的表数量。例如，如果最大连接数为 100，每个连接平均使用 2 张表，可设置为：

SET GLOBAL table_cache = 200; 

• 注意事项：过高的设置可能导致内存浪费，过低会导致频繁的文件打开和关闭操作。

innodb_buffer_pool_size

• 功能：对于 InnoDB 存储引擎，这是最重要的参数，它决定了 InnoDB 存储引擎的缓冲池大小，存储数据和索引。
• 设置建议：通常设置为系统内存的 50%-80%，但也要考虑服务器上其他服务的内存占用。例如，对于 8GB 内存的服务器，可设置为 4GB：

SET GLOBAL innodb_buffer_pool_size = 4 * 1024 * 1024 * 1024; 

• 注意事项：合理的设置可以显著提高 InnoDB 表的性能，避免内存交换。

innodb_flush_log_at_trx_commit

• 功能：控制 InnoDB 事务日志的刷新策略，决定了事务提交时日志的刷新频率。
• 设置建议：
  • 值为 0：每秒刷新一次，可能丢失 1 秒内的数据，但性能最高。
  • 值为 1：每次事务提交都刷新，最安全但性能最差，是默认设置。
  • 值为 2：事务提交时只写日志文件，每秒刷新到磁盘，性能和安全性的平衡。

-- 为了性能考虑，可设置为 2
SET GLOBAL innodb_flush_log_at_trx_commit = 2; 

• 注意事项：根据业务对数据一致性和性能的要求进行调整。

innodb_lock_wait_timeout

• 功能：控制 InnoDB 事务等待锁的超时时间，避免长时间等待锁导致的性能问题。
• 设置建议：根据业务特点，设置为一个合理的时间，例如 50 秒：

SET GLOBAL innodb_lock_wait_timeout = 50; 

• 注意事项：设置过短可能导致事务频繁失败，过长可能导致事务长时间阻塞。

innodb_support_xa

• 功能：是否支持分布式事务的两阶段提交协议，开启时保证分布式事务的一致性。
• 设置建议：
  • 对于单机环境或不需要分布式事务的情况，可关闭以提高性能：

SET GLOBAL innodb_support_xa = 0; 

- 对于分布式环境，保持开启。

• 注意事项：关闭后可提高性能，但分布式事务可能出现不一致性。

innodb_log_buffer_size

• 功能：用于存储 InnoDB 事务日志的缓冲区大小，在事务提交前存储事务日志。
• 设置建议：一般设置为 8MB 到 16MB，例如：

SET GLOBAL innodb_log_buffer_size = 8 * 1024 * 1024; 

• 注意事项：对于大量事务并发的情况，可适当增大。

innodb_log_file_size

• 功能：决定了 InnoDB 事务日志文件的大小，影响日志文件的存储量和性能。
• 设置建议：一般设置为 256MB 到 1GB，根据服务器的写入负载和事务量调整，例如：

SET GLOBAL innodb_log_file_size = 256 * 1024 * 1024; 

• 注意事项：过小会导致频繁的日志切换，过大则会延长恢复时间。

小结 优化 MySQL Server 性能需要综合考虑多个参数，不同的存储引擎有不同的性能瓶颈和优化点。对于 MyISAM 主要关注 key_buffer_size 和 table_cache，而对于 InnoDB 主要考虑

innodb_buffer_pool_size、innodb_flush_log_at_trx_commit
等。在设置参数时，要根据服务器的硬件资源、业务需求和存储引擎的使用情况进行调整，避免过度设置导致的资源浪费或设置不足导致的性能问题。同时，需要在性能和数据一致性、安全性之间找到平衡，并且在修改重要参数时，应在测试环境充分测试，避免对生产环境造成严重影响。

磁盘 I/O 问题及相关优化策略

磁盘 I/O 问题

在数据库系统中，磁盘 I/O 往往是性能瓶颈之一。因为数据库操作需要频繁读写数据，而磁盘的读写速度相对于 CPU 和内存来说较慢，所以优化磁盘 I/O 性能对于提升整个数据库系统的性能至关重要。

SQL 使用磁盘阵列

使用磁盘阵列可以提升磁盘 I/O 性能和数据冗余性。磁盘阵列通过将多个物理磁盘组合成一个逻辑磁盘单元，提供了更高的存储容量和性能。

常见 RAID 级别及其特性

1. RAID 0（条带化）：
   • 特性：将数据分成块并分散存储在多个磁盘上，提供了较高的数据读写速度，因为可以并行处理多个磁盘的 I/O 操作。但不提供数据冗余，一旦有一个磁盘损坏，所有数据将丢失。
   • 适用场景：对性能要求极高且不考虑数据冗余的场景，如临时存储数据或存储非关键数据。

   -- 示例：创建 RAID 0 阵列，将多个磁盘组合在一起
   -- 实际操作通常在操作系统或硬件层面进行，以下为示意
   CREATE DISK_ARRAY RAID0 AS DISK1, DISK2, DISK3; 
   

2. RAID 1（镜像）：
   • 特性：将数据同时存储在两个或多个磁盘上，提供了数据冗余，读取性能较好，但写入性能受限于最慢的磁盘，因为数据要同时写入多个磁盘。
   • 适用场景：对数据可靠性要求高，允许一定的性能损失，例如存储关键业务数据。

   -- 示例：创建 RAID 1 阵列
   CREATE DISK_ARRAY RAID1 AS DISK1, DISK2; 
   

3. RAID 5（分布式奇偶校验）：
   • 特性：通过分布式奇偶校验提供数据冗余，在保证一定性能的同时，能够容忍一个磁盘故障。数据和奇偶校验信息分布在多个磁盘上，读取性能较好，写入性能稍逊于 RAID 0。
   • 适用场景：适用于对性能和数据冗余都有一定要求的情况，如企业文件服务器。

   -- 示例：创建 RAID 5 阵列
   CREATE DISK_ARRAY RAID5 AS DISK1, DISK2, DISK3, DISK4; 
   

4. RAID 10（镜像 + 条带化）：
   • 特性：结合了 RAID 0 和 RAID 1 的优点，提供了高数据冗余和较好的性能，但成本较高，需要至少 4 个磁盘。
   • 适用场景：对性能和数据冗余都要求很高的场景，如数据库服务器。

   -- 示例：创建 RAID 10 阵列
   CREATE DISK_ARRAY RAID10 AS DISK1, DISK2, DISK3, DISK4; 
   

如何选择 RAID 级别

• 性能优先：如果系统对读写速度要求极高，且不担心数据丢失的风险，可选择 RAID 0。
• 数据冗余优先：对数据安全至关重要，性能可适当妥协，可选择 RAID 1。
• 性能和冗余平衡：RAID 5 是一个不错的选择，适用于大多数通用场景。
• 高性能与高冗余：RAID 10 适用于对性能和数据安全都有极高要求的情况，如核心数据库服务器。

使用符号链接分布 I/O

• 原理：使用符号链接将数据库文件分布到不同的物理磁盘上，以平衡 I/O 负载。例如，将不同的数据库表空间或日志文件链接到不同的磁盘或分区。

-- 示例：创建符号链接
CREATE SYMBOLIC_LINK '/path/to/link' TO '/actual/path/of/file'; 

• 优点：可以手动控制文件的存储位置，实现 I/O 负载均衡，提高性能。
• 缺点：需要手动维护，操作相对复杂。

禁止操作系统更新文件的 atime 属性

• 原理：文件的 atime（访问时间）属性在文件被访问时会更新，这会导致额外的 I/O 操作。通过禁止更新 atime 属性，可以减少不必要的 I/O 操作。

-- 在 Linux 系统中，可以通过挂载文件系统时使用 noatime 选项来实现
mount -o remount,noatime /dev/sda1 /mnt 

• 优点：减少了 I/O 操作，尤其是在文件频繁读取的环境中，可显著提升性能。

用裸设备存放 InnoDB 的共享表空间

• 原理：将 InnoDB 的共享表空间存储在裸设备上，绕过文件系统，直接对磁盘进行操作，减少文件系统的开销，提高 I/O 性能。
• 操作步骤：
  1. 识别裸设备（通常是磁盘的块设备，如 /dev/sdb1）。
  2. 配置 MySQL 服务器将 InnoDB 共享表空间存储在裸设备上。

  -- 修改 InnoDB 的配置，将表空间存储在裸设备上
  innodb_data_file_path = /dev/sdb1:10G:autoextend 
  

• 优点：减少文件系统的开销，提高 I/O 性能，适用于对性能要求极高的数据库服务器。
• 缺点：管理相对复杂，不支持文件系统的一些功能，如文件权限管理等。

小结 优化磁盘 I/O 是提升数据库性能的重要环节。通过合理使用磁盘阵列（选择合适的 RAID 级别）、使用符号链接分布 I/O、禁止 atime 属性更新以及将 InnoDB 共享表空间存储在裸设备上，可以从不同方面提高磁盘 I/O

性能。在实际应用中，需要根据具体的业务需求、性能要求和硬件资源来选择合适的优化方法，同时要考虑到不同方法的优缺点和维护成本。