MySQL数据库性能调优

Question

我已经开发了一个PHP和MySQL的web应用程序。最近，它已经显示出放缓的迹象。我在090516上运行了mysql-tuner，正常运行时间为12天。

服务器规格如下：

• Linux CentOS 6
• 48处理器
• 64 48内存

下面是090516的mysql-tuner日志输出，正常运行时间为12天：

root@layer1 [~/mysqltuner]# perl mysqltuner.pl --outputfile /~/mysqltuner/result_mysqltuner.txt
String found where operator expected at mysqltuner.pl line 3096, near
        "get_wsrep_option 'gcache.mem_size'" (#1)
    (S syntax) The Perl lexer knows whether to expect a term or an operator.
    If it sees what it knows to be a term when it was expecting to see an
    operator, it gives you this warning.  Usually it indicates that an
    operator or delimiter was omitted, such as a semicolon.

        (Do you need to predeclare get_wsrep_option?)
Use of implicit split to @_ is deprecated at mysqltuner.pl line 3179 (#2)
    (D deprecated, W syntax) It makes a lot of work for the compiler when you
    clobber a subroutine's argument list, so it's better if you assign the results
    of a split() explicitly to an array (or list).

 >>  MySQLTuner 1.6.10 - Major Hayden <major@mhtx.net>
 >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
 >>  Run with '--help' for additional options and output filtering

[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.6.31-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: 913M (Tables: 11)
[--] Data in InnoDB tables: 12M (Tables: 63)
[!!] Total fragmented tables: 11

-------- Security Recommendations ------------------------------------------------------------------
[OK] There are no anonymous accounts for any database users
[OK] All database users have passwords assigned
[!!] User 'munin@localhost' has user name as password.
[--] There are 605 basic passwords in the list.

-------- CVE Security Recommendations --------------------------------------------------------------
[OK] NO SECURITY CVE FOUND FOR YOUR VERSION
Argument "gcache.mem_size" isn't numeric in addition (+) at mysqltuner.pl line
        1965 (#3)
    (W numeric) The indicated string was fed as an argument to an operator
    that expected a numeric value instead.  If you're fortunate the message
    will identify which operator was so unfortunate.

Argument "gcache.mem_size" isn't numeric in addition (+) at mysqltuner.pl line
        1972 (#3)

-------- Performance Metrics -----------------------------------------------------------------------
[--] Up for: 12d 6h 21m 24s (2 q [0.000 qps], 1M conn, TX: 192B, RX: 240B)
[--] Reads / Writes: 100% / 0%
[--] Binary logging is disabled
[--] Physical Memory     : 62.8G
[--] Max MySQL memory    : 338.9M
[--] Other process memory: 505.0M
[--] Total buffers: 169.0M global + 1.1M per thread (151 max threads)
[--] P_S Max memory usage: 0B
Argument "*main::get_wsrep_option" isn't numeric in numeric ge (>=) at
        mysqltuner.pl line 285 (#3)
Argument "*main::get_wsrep_option" isn't numeric in numeric ge (>=) at
        mysqltuner.pl line 288 (#3)
Argument "*main::get_wsrep_option" isn't numeric in numeric ge (>=) at
        mysqltuner.pl line 291 (#3)
[--] Galera GCache Max memory usage: *main::get_wsrep_optionB
[OK] Maximum reached memory usage: 340.0M (0.53% of installed RAM)
[OK] Maximum possible memory usage: 338.9M (0.53% of installed RAM)
[OK] Overall possible memory usage with other process is compatible with memory available
[OK] Slow queries: 0% (0/2)
[!!] Highest connection usage: 100%  (152/151)
[OK] Aborted connections: 0.00%  (34/1098331)
[OK] Query cache is disabled by default due to mutex contention.
[OK] No Sort requiring temporary tables
[OK] No joins without indexes
[OK] No tmp tables created on disk
[OK] Thread cache hit rate: 98% (17K created / 1M connections)
[OK] Table cache hit rate: 100% (381 open / 0 opened)
[OK] Open file limit used: 2% (228/10K)
[!!] Table locks acquired immediately: 94%

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

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

-------- MyISAM Metrics ----------------------------------------------------------------------------
[OK] Key buffer used: 100.0% (8M used / 8M cache)
[!!] Key buffer size / total MyISAM indexes: 8.0M/147.7M
[!!] Read Key buffer hit rate: 87.6% (16M cached / 1M reads)
[!!] Write Key buffer hit rate: 56.8% (1M cached / 777K writes)

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

-------- InnoDB Metrics ----------------------------------------------------------------------------
[--] InnoDB is enabled.
[OK] InnoDB buffer pool / data size: 128.0M/12.1M
[!!] InnoDB buffer pool <= 1G and innodb_buffer_pool_instances(!=1).
[!!] InnoDB Used buffer: 17.82% (1460 used/ 8192 total)
[OK] InnoDB Read buffer efficiency: 100.00% (290756388 hits/ 290757478 total)
[!!] InnoDB Write Log efficiency: 12.12% (3691 hits/ 30446 total)
[OK] InnoDB log waits: 0.00% (0 waits / 34137 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
    Set up a Secure Password for user@host ( SET PASSWORD FOR 'user'@'SpecificDNSorIp' = PASSWORD('secure_password'); )
    Reduce or eliminate persistent connections to reduce connection usage
    Optimize queries and/or use InnoDB to reduce lock wait
Variables to adjust:
    max_connections (> 151)
    wait_timeout (< 28800)
    interactive_timeout (< 28800)
    key_buffer_size (> 147.7M)
    innodb_buffer_pool_instances (=1)

我几乎没有想到什么，但是我不是一个有经验的MySQL数据库管理员，因此我正在向堆栈社区寻求建议。请建议我应该在my.cnf中修改哪些设置，如果我需要在PHPMYADMIN中对表进行碎片整理，是否需要在非工作时间进行此操作。

我直到最近才注意到我的web应用程序变慢了，最近我确实在内核更新后重新启动了服务器。截止到本文，当前的正常运行时间是19小时。

**额外信息：** web应用有一个前端，用户可以在移动端登录；一个后端，办公室员工管理提交的内容(即表单)，并将其发送到不同的部门。

如果需要的话，我可以用munin图表来更新。我知道服务器严重超配(这意味着它的RAM和处理器远远超过了需要的，但我想要更多而不是足够的处理器)。我的问题是，我是否可以简单地调整一些my.cnf设置(如mysqltuner和/或phpmyadmin所建议的)来提高效率？

Answer 1

在接下来的十年中，MySQL不太可能以一种高效地使用四个以上处理器的方式进行重构。因此，您的配置浪费了44个处理器内核。这似乎太多了。而且，看起来MySQL占用的内存还不到1GiB。

通常，MySQL是构建的，而不是构建的。意思:大多数扩展基于MySQL的应用程序的人都是通过使用数据库的镜像来实现的。MariaDB的最新版本有一个多主镜像方案。

你遇到麻烦的唯一明显的地方是连接搅动。您可能应该编辑您的my.cnf文件以增加您使用的同时连接的数量。

您需要调查您的web应用程序是否使用连接池。web应用程序需要一段时间才能建立到数据库的连接，如果您要为每个web请求重新建立连接，那么打开连接的操作可能是一个瓶颈。您的统计数据显示超过一个新的连接，每秒24小时，乘以12天。除非您的工作负载在一天中的所有24小时内非常稳定地分布，否则这意味着您的web应用程序正在以非常高的新连接率达到峰值。这很可能是你速度减慢的原因。

您没有说您使用的是哪种web应用程序平台。大多数平台都有连接池操作。大多数web应用程序使用一组或两组DBMS凭据(用户名)来执行所有web应用程序操作，因此它们可以重用池中的连接。您的统计数据显示您在数据库上有600+不同的用户。对于一个web应用程序来说，这是一个很大的问题。

此外(这是违反直觉的)有时太多的web应用线程会损害性能。有时候，限制线程的数量，让web服务器对来自客户端的请求进行排队，而不是试图一次做太多的操作，这对用户体验更好。现代服务器上的TCP堆栈允许相当深的SYN请求队列，因此队列不会被注意到。

这是基于您展示的有限数据的观点:您的瓶颈不是MySQL。