预计使用量将大幅增加时如何调整 MySQL 服务器

预计使用量将大幅增加时如何调整 MySQL 服务器

我在尝试调整 MySQL 服务器时遇到了很大的问题。我预计在下午 5 点到 6 点之间购买电视广告后,我的网站上会有很多人,在此期间,我的网站上大约有 300 人。我尝试在 my.cnf 中进行大量调整,但每晚情况都越来越糟。我真的很感激有人能帮助我找出我的问题所在。我当前的基础设施如下:

我的网站使用一台服务器

  • apache2 已安装
  • 6c / 12t - 英特尔(R) 至强(R) CPU E5-1650 v2 @ 3.50GHz
  • 64GB 内存 - DDR3 ECC 1600MHz
  • 磁盘 - HardRaid+ 3x480 Go SSD

我的数据库使用一台服务器

  • Mysql 5.5.38就可以了
  • 4c / 8t - 英特尔(R) 至强(R) CPU E5-1620 v2 @ 3.70GHz
  • 32GB 内存 - DDR3 ECC 1600MHz
  • 磁盘 - SoftRaid 3x160 Go SSD

这是我当前的配置 my.cnf:

innodb_file_per_table           = 1
innodb_buffer_pool_instances    = 13
innodb_buffer_pool_size         = 13375M
innodb_open_files               = 300
innodb_thread_concurrency       = 0
#innodb_read_io_threads          = 8
#innodb_write_io_threads         = 8
#innodb_flush_method             = O_DIRECT
#join_buffer_size                               = 30M
#sort_buffer_size                               = 10M
#read_buffer_size                               = 10M

wait_timeout                    = 180
interactive_timeout             = 180

max_connections                 = 250
max_heap_table_size             = 256M
tmp_table_size                  = 256M
table_cache                     = 20000
table_definition_cache          = 20000
#table_open_cache               = 20000
query_cache_type                = 0

mysqltuner 结果:

 >>  MySQLTuner 1.6.9 - Major Hayden <[email protected]>
 >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
 >>  Run with '--help' for additional options and output filtering
[--] Performing tests on 127.0.0.1:3306
[OK] Logged in using credentials passed on the command line

[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.5.38-0+wheezy1-log
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -----------------------------------------------------------------
[--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MEMORY +MRG_MYISAM +MyISAM +PERFORMANCE_SCHEMA
[--] Data in MyISAM tables: 32M (Tables: 126)
[--] Data in InnoDB tables: 10G (Tables: 1503)
[!!] Total fragmented tables: 359

-------- Performance Metrics -----------------------------------------------------------------------
[--] Up for: 1m 59s (82K q [695.832 qps], 335 conn, TX: 538M, RX: 34M)
[--] Reads / Writes: 99% / 1%
[--] Binary logging is disabled
[--] Total buffers: 13.8G global + 2.7M per thread (300 max threads)
[--] P_S Max memory usage: 0B
[--] Galera GCache Max memory usage: 0B
[OK] Maximum reached memory usage: 13.9G (44.07% of installed RAM)
[OK] Maximum possible memory usage: 14.6G (46.49% of installed RAM)
[OK] Slow queries: 0% (1/82K)
[OK] Highest usage of available connections: 3% (11/300)
[OK] Aborted connections: 0.60%  (2/335)
[OK] Query cache is disabled by default due to mutex contention.
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 14K sorts)
[!!] Joins performed without indexes: 131
[OK] Temporary tables created on disk: 5% (927 on disk / 17K total)
[OK] Thread cache hit rate: 96% (11 created / 335 connections)
[OK] Table cache hit rate: 25% (1K open / 6K opened)
[OK] Open file limit used: 0% (301/40K)
[OK] Table locks acquired immediately: 100% (221K immediate / 221K locks)

-------- ThreadPool Metrics ------------------------------------------------------------------------
[--] ThreadPool stat is disabled.

-------- Performance schema ------------------------------------------------------------------------
[--] Performance schema is disabled.
[--] Memory used by P_S: 0B

-------- MyISAM Metrics ----------------------------------------------------------------------------
[!!] Key buffer used: 18.2% (763K used / 4M cache)
[OK] Key buffer size / total MyISAM indexes: 4.0M/10.6M
[OK] Read Key buffer hit rate: 100.0% (562K cached / 0 reads)
[OK] Write Key buffer hit rate: 100.0% (14K cached / 0 writes)

-------- AriaDB Metrics ----------------------------------------------------------------------------
[--] AriaDB is disabled.

-------- InnoDB Metrics ----------------------------------------------------------------------------
[--] InnoDB is enabled.
[OK] InnoDB buffer pool / data size: 13.1G/10.7G
[OK] InnoDB buffer pool instances: 13
[!!] InnoDB Used buffer: 5.30% (45409 used/ 855985 total)
[OK] InnoDB Read buffer efficiency: 99.95% (85810161 hits/ 85850600 total)
[!!] InnoDB Write Log efficiency: 61.42% (519 hits/ 845 total)
[OK] InnoDB log waits: 0.00% (0 waits / 326 writes)

-------- TokuDB Metrics ----------------------------------------------------------------------------
[--] TokuDB is disabled.

-------- Galera Metrics ----------------------------------------------------------------------------
[--] Galera is disabled.

-------- Replication Metrics -----------------------------------------------------------------------
[--] Galera Synchronous replication: NO
[--] No replication slave(s) for this server.
[--] This is a standalone server.

-------- Recommendations ---------------------------------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    MySQL started within last 24 hours - recommendations may be inaccurate
    Adjust your join queries to always utilize indexes
Variables to adjust:
    join_buffer_size (> 128.0K, or always use indexes with joins)
  • 当人们在网站上停留几秒钟时,CPU 负载相当低(介于 1 和 4 之间)。更糟糕的是,当很多人同时访问时,CPU 负载会达到 40 或 50,这是一个问题,我不知道我可以调整哪个参数来避免这种情况。

  • RAM:我认为这里的 RAM 足够了。服务器有 32GB,即使网站上的人越来越多,也不会使用更多。它保持在 15-20% 并且白天不会移动。我不明白为什么 RAM 不动,我的正确目标是将我的查询最大限度放在内存中以避免影响磁盘。

高峰时段,情况如下free -m

             total       used       free     shared    buffers     cached
Mem:         32202      25261       6940          0        917      18794
-/+ buffers/cache:       5549      26652
Swap:         1532         21       1511

我在网站上使用 Prestashop,我的表格非常大。这个周末我会存档并截断其中的大部分。例如:

SELECT COUNT(*) FROM ps_connections; => 1 330 373
SELECT COUNT(*) FROM ps_guest; => 6 970 248

如果你能提供一些建议就太好了!谢谢 Julien

注意:抱歉我的英语不好

答案1

虽然全面分析是一项复杂而困难的任务,但我可以根据您提供的信息和调谐器中的警告提出一些建议。

1) 您有 32GB RAM,但在专用机器上仅将 14GB 用于 MySQL。为什么不给它更多呢?建议专用主机上的 MySQL 应使用 75% 的 RAM(innodb_buffer_pool_size),但您应该先检查 top、vmstat 或任何长期监控,以确保有足够的空间用于其他服务或备份等偶尔发生的事件。注意 Linux 总是看起来像是“RAM 耗尽”,因为它动态地将任何备用 RAM 用于缓冲区和缓存 - 正如您的示例所示。

2) innodb_flush_method 是一个重要的性能因素,您的设置已被注释掉,因此我无法知道实际(默认)值。检查服务器变量以查看它是什么。但是 - 更改它会在发生中断时对您的完整性产生很大影响。

3) 您有许多碎片化的表。如果碎片化严重,这可能会导致性能损失。还有许多其他帖子[1] 讨论了如何查找表的碎片化程度以及是否需要碎片整理,但同样,由于存储引擎不同、每个文件一个表等原因,碎片整理有点复杂。要对它们进行碎片整理,您可以使用优化或修改来重建表(当然,这些操作会锁定表,因此您需要通过练习或安排停机来确保速度快)。

1[如何查找并修复碎片化的 MySQL 表

4)“没有索引的连接执行” - 虽然 131 听起来不是很高,但这可能是您最大的问题。

任何不使用或不能使用索引的查询都必须检查每条记录(表扫描),这对于较大的表来说速度很慢。如果您没有自己编写查询(即您使用此 Pretashop 产品),那么您可能无法处理这方面的问题。

但是,要解决这个问题,您需要找到查询,分析它们,然后相应地添加索引。要找到查询,我会从慢查询日志开始:您没有很多慢查询,所以我猜慢阈值设置得太高了?因此,如果您可以降低该阈值,那么就开始挑选慢查询并分析它们。使用 EXPLAIN 命令,您应该能够看到 JOINS 缺少索引的原因(尽管理解 EXPLAIN 是另一个主题)。然后添加索引很简单,但也有其优点和缺点(例如,插入可能运行得更慢,太多索引会对性能产生不利影响),此外索引过程会锁定表一段时间。

缓冲区指标中也存在问题,但这目前超出了我的知识范围(这就是让我想到这个问题的原因——我有同样的写日志效率问题,我还不明白)。

相关内容