☰

通过配置 MySQL 来提高服务器性能

tag-icon • tag-icon mysql performance optimization
通过配置 MySQL 来提高服务器性能

我有一台具有 8GB 内存的服务器，运行 CentOS 5.4、php 5.2.9、mysql 5.0.77。

我有一个非常大的数据库，大约 20GB（InnoDB 和 MyIsam 表），网站也使用它。网站每天大约有 20000 名访客，每天大约有 100000 次页面浏览量。我的网站非常慢。有时打开一个页面大约需要 15-20 秒。

我询问您是否可以帮助我提高服务器的性能。

我认为问题出在 MySql 上。也许应用程序没有正确关闭连接。另外，我有大约 2000 - 3000 个 TIME_WAIT 进程。

这是 MySQl 的 my.cnf：

key_buffer = 1024M
table_cache = 1024
table_open_cache = 1024
sort_buffer_size = 10M
read_buffer_size = 10M read_rnd_buffer_size =
10M
myisam_sort_buffer_size = 128M
thread_cache_size = 16
query_cache_size= 64M
-# 尝试将 CPU 数量*2 用于 thread_concurrency
thread_concurrency = 8
innodb_buffer_pool_size = 1536M
innodb_additional_mem_pool_size = 128M
innodb_flush_log_at_trx_commit=0
set-variable=max_connections=1200

这是 mysqltuner 日志：

MySQLTuner 1.2.0 - 主要 Hayden
Bug 报告、功能请求和下载http://mysqltuner.com/
使用“--help”运行以获取更多选项和输出过滤
请输入您的 MySQL 管理登录名：root
请输入您的 MySQL 管理密码：

-------- 一般统计信息 --------------------------------------------------
[--] 跳过 MySQLTuner 脚本的版本检查
[OK] 当前运行支持的 MySQL 版本 5.0.77
[OK] 在 64 位架构上运行

-------- 存储引擎统计信息 -------------------------------------------
[--] 状态：-Archive +BDB -Federated +InnoDB -ISAM -NDBCluster
[--] MyISAM 表中的数据：8G（表：19）
[--] InnoDB 表中的数据：5G（表：27）
[!!] BDB 已启用但未被使用
[!!] 碎片表总数：3

-------- 安全建议 -------------------------------------------
[!!] 用户“@cl-t061-430cl.privatedns.com”未设置密码。
[!!] 用户“@localhost”未设置密码。
[!!] 用户“[电子邮件保护]'没有设置密码。

-------- 性能指标 -------------------------------------------------
[--] 运行时间：54 天 21 小时 14 分 44 秒（466M q [98.362 qps]、2M conn、TX：1103B、RX：112B）
[--] 读取/写入：78%/22%
[--] 总缓冲区：全局 2.7G + 每个线程 80.4M（最大线程数 1200 个）
[!!] 最大可能的内存使用量：96.9G（已安装 RAM 的 1240%）
[OK] 慢速查询：0%（46K/466M）
[OK] 可用连接的最高使用率：66%（801/1200）
[OK] 键缓冲区大小/总 MyISAM 索引：1.0G/10.8G
[OK] 键缓冲区命中率：100.0%（194B 缓存/1M 读取）
[OK] 查询缓存效率：73.7% (316M 缓存 / 429M 选择)
[!!] 每天查询缓存修剪次数：24969
[OK] 需要临时表的排序：0% (0 临时排序 / 77M 排序)
[!!] 未使用索引执行的连接：188318
[!!] 磁盘上创建的临时表：49% (磁盘上 69M / 总共 139M)
[OK] 线程缓存命中率：92% (创建 205K / 2M 连接)
[OK] 表缓存命中率：23% (打开 1K / 打开 4K)
[OK] 使用的打开文件限制：4% (269/6K)
[OK] 立即获取的表锁：99% (立即 346M / 锁 346M)
[!!] InnoDB 数据大小 / 缓冲池：5.7G/1.5G

-------- 建议 -----------------------------------------------------
一般建议：
将 skip-bdb 添加到 MySQL 配置以禁用 BDB
运行 OPTIMIZE TABLE 对表进行碎片整理，以获得更好的性能
减少整体 MySQL 内存占用以确保系统稳定性
启用慢查询日志来排除不良查询故障
调整连接查询以始终使用索引
进行调整时，使 tmp_table_size/max_heap_table_size 相等
减少没有 LIMIT 子句的 SELECT DISTINCT 查询
要调整的变量：
* MySQL 的最大内存使用量非常高
增加 MySQL 缓冲变量之前添加 RAM *
query_cache_size (> 64M)
join_buffer_size (> 128.0K，或始终使用带连接的索引)
tmp_table_size (> 32M)
max_heap_table_size (> 16M)
innodb_buffer_pool_size (>= 5G)

我会很感激任何想法和建议。非常感谢。

答案1

购买更多内存。在应用程序级别您可以做什么？您可以启用缓存、添加索引、切换引擎吗？切换到 mysql 5.1 后，我发现大表的性能有所提高

答案2

我不确定这是否可以定义为性能调整，但是，看在上帝的份上，使用 innodb_file_per_table 否则你迟早会在管理磁盘空间方面遇到麻烦 cfrhttp://lamp.mayavi.info/index.php/databases/innodb-per-table-tablespaces-file-for-each-innodb-table.html

答案3

一旦你有了更多的 RAM，一些唾手可得的成果将是：

  1. 增加 innodb_buffer_pool_size（按照 mysqltuner 的建议）
  2. 将 innodb_log_file_size 增加到合理的大小，例如 1024M
  3. 您似乎在磁盘上创建了很多临时表，您可以尝试优化查询或将 MySQL 的 tmpdir 放在 RAM 磁盘上

干杯

答案4

首先尝试启用慢查询日志，它有助于检查执行时间较长的查询。mysqldumpslow是 mysql 提供的用于分析慢查询日志的良好工具。

事实上，您也应该遵循 jasondbecker 关于内存使用的建议。

相关内容