![Mysql Tuner 输出:需要帮助分析](https://linux22.com/image/743287/Mysql%20Tuner%20%E8%BE%93%E5%87%BA%EF%BC%9A%E9%9C%80%E8%A6%81%E5%B8%AE%E5%8A%A9%E5%88%86%E6%9E%90.png)
-------- Performance Metrics -----------------------------------------------------------------------
[--] Up for: 75d 21h 28m 49s (441M q [67.390 qps], 1M conn, TX: 5795G, RX: 281G)
[--] Reads / Writes: 44% / 56%
[--] Binary logging is enabled (GTID MODE: ON)
[--] Physical Memory : 62.8G
[--] Max MySQL memory : 71.7G
[--] Other process memory: 0B
[--] Total buffers: 38.3G global + 32.9M per thread (1000 max threads)
[--] P_S Max memory usage: 1G
[--] Galera GCache Max memory usage: 0B
[!!] Maximum reached memory usage: 65.1G (103.78% of installed RAM)
[!!] Maximum possible memory usage: 71.7G (114.32% of installed RAM)
[!!] Overall possible memory usage with other process exceeded memory
[OK] Slow queries: 0% (21K/441M)
[OK] Highest usage of available connections: 79% (794/1000)
[!!] Aborted connections: 12.77% (226078/1769806)
[!!] name resolution is active : a reverse name resolution is made for each new connection and can reduce performance
[OK] Query cache is disabled by default due to mutex contention on multiprocessor machines.
[OK] Sorts requiring temporary tables: 5% (26K temp sorts / 519K sorts)
[!!] Joins performed without indexes: 372233
[OK] Temporary tables created on disk: 11% (7M on disk / 62M total)
[OK] Thread cache hit rate: 99% (2K created / 1M connections)
[!!] Table cache hit rate: 0% (4K open / 118M opened)
[OK] Open file limit used: 0% (18/65K)
[OK] Table locks acquired immediately: 100% (1B immediate / 1B locks)
[OK] Binlog cache memory access: 99.06% (67689908 Memory / 68333632 Total)
[!!] Key buffer used: 18.2% (6M used / 33M cache)
[OK] Key buffer size / total MyISAM indexes: 32.0M/105.0K
[OK] Read Key buffer hit rate: 100.0% (1B cached / 231K reads)
[!!] Write Key buffer hit rate: 1.2% (364M cached / 4M writes)
[--] InnoDB is enabled.
[--] InnoDB Thread Concurrency: 8
[OK] InnoDB File per table is activated
[OK] InnoDB buffer pool / data size: 38.0G/35.9G
[!!] Ratio InnoDB log file size / InnoDB Buffer pool size (0.657894736842105 %): 128.0M * 2/38.0G should be equal to 25%
[!!] InnoDB buffer pool instances: 16
[--] InnoDB Buffer Pool Chunk Size not used or defined in your version
[OK] InnoDB Read buffer efficiency: 99.99% (65676094854 hits/ 65679543386 total)
[OK] InnoDB Write log efficiency: 99.27% (1055996396 hits/ 1063709557 total)
[OK] InnoDB log waits: 0.00% (0 waits / 7713161 writes)
max_connections configured =1000
we average around 500
CPU: 16 virtual
RAM: 64 GB
HDD: SAN(ISCSI)
我需要帮助来了解我们的服务器出了什么问题,有时数据库会挂起并且不处理任何请求,此时我看到数据库的连接数激增到 2000。
答案1
max_connections 配置为 1000,平均约为 500
每秒 67.390 笔
系统非常繁忙。但是,让 500 个连接争抢行动可能会导致它们相互干扰。如果可以,请限制客户端的连接数。
使用较低的值(例如 0.2)打开 slowlog long_query_time
。等待一段时间,然后使用 进行总结pt-query-digest
。更多详细信息: http://mysql.rjweb.org/doc.php/mysql_analysis#slow_queries_and_slowlog