今年早些时候,我将一个流量较小、以数据为中心的站点从共享主机转移到centos cloudvm上。性能在7个多月内完全可以接受—现在每天约有10%的查询需要5秒以上的时间才能完成,其中许多查询需要相当长的时间。
在这几个月里,添加了更多的数据,但代码库中没有任何变化。然而,这场演出现在已经跌入了悬崖。我尝试重新启动示例,调整my.cnf、mysqltuner等,但没有任何改进。。。
也许这个问题太具体了,但如果有人能提供一些关于在哪里寻找导致这种严重性能滞后的潜在原因的建议,那将是一个很大的帮助
更新查看 top
生产服务器和mysqld上的进程的cpu%可以忽略不计,但通常会在返回之前达到100%以上。
已经检查了它们都使用的慢速查询,如“%query text%”。但是,有些查询运行得非常快,而另一些查询可能需要20多秒才能完成几乎相同的查询。
上述数据的增加是在现有340k行的基础上再增加约30k行。
我是唯一一个开发代码库的开发人员,在应用程序的数据查询方面没有做任何更改。但是,最近进行了一些环境更改,这在一段时间内带来了一些不稳定性(试图将php的cli版本从5.4升级到7.x)。此后,通过升级所有包,覆盖apache安装程序中配置不当的包,解决了不稳定性问题。
云示例的web服务器和db服务器都有2gb的ram
我的.cnf-https://pastebin.com/dw4gpjfg
[mysqld]
bind-address = ::ffff:127.0.0.1
local-infile=0
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Added 07 Nov 2018
# https://support.plesk.com/hc/en-us/articles/213401729-Slow-performance-of-the-MySQL-on-Plesk-server
innodb_buffer_pool_size=756M
query_cache_size=64M
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
# Settings user and group are ignored when systemd is used.
# If you need to run mysqld under a different user or group,
# customize your systemd unit file for mariadb according to the
# instructions in http://fedoraproject.org/wiki/Systemd
[mysqld_safe]
log-error=/var/log/mariadb/mariadb.log
pid-file=/var/run/mariadb/mariadb.pid
slow_query_log = 1
log-slow-queries = /var/log/mariadb/mariadb-slow.log
long_query_time = 5s
#
# include all files from the config directory
#
!includedir /etc/my.cnf.d
mysqltuner-https://pastebin.com/rwu61pmg
>> MySQLTuner 1.7.5 - 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.5.60-MariaDB
[OK] Operating on 64-bit architecture
-------- Log file Recommendations ------------------------------------------------------------------
[--] Log file: /var/log/mariadb/mariadb.log(45K)
[OK] Log file /var/log/mariadb/mariadb.log exists
[OK] Log file /var/log/mariadb/mariadb.log is readable.
[OK] Log file /var/log/mariadb/mariadb.log is not empty
[OK] Log file /var/log/mariadb/mariadb.log is smaller than 32 Mb
[!!] /var/log/mariadb/mariadb.log contains 1 warning(s).
[!!] /var/log/mariadb/mariadb.log contains 16 error(s).
[--] 31 start(s) detected in /var/log/mariadb/mariadb.log
[--] 1) 181116 0:49:26 [Note] /usr/libexec/mysqld: ready for connections.
[--] 2) 181109 8:28:59 [Note] /usr/libexec/mysqld: ready for connections.
[--] 3) 181109 8:00:12 [Note] /usr/libexec/mysqld: ready for connections.
[--] 4) 181106 23:47:13 [Note] /usr/libexec/mysqld: ready for connections.
[--] 5) 181106 1:22:22 [Note] /usr/libexec/mysqld: ready for connections.
[--] 6) 181029 11:05:08 [Note] /usr/libexec/mysqld: ready for connections.
[--] 7) 181009 9:49:46 [Note] /usr/libexec/mysqld: ready for connections.
[--] 8) 180929 22:48:02 [Note] /usr/libexec/mysqld: ready for connections.
[--] 9) 180827 3:40:02 [Note] /usr/libexec/mysqld: ready for connections.
[--] 10) 180723 23:24:44 [Note] /usr/libexec/mysqld: ready for connections.
[--] 31 shutdown(s) detected in /var/log/mariadb/mariadb.log
[--] 1) 181116 0:48:02 [Note] /usr/libexec/mysqld: Shutdown complete
[--] 2) 181109 8:28:57 [Note] /usr/libexec/mysqld: Shutdown complete
[--] 3) 181109 8:00:09 [Note] /usr/libexec/mysqld: Shutdown complete
[--] 4) 181106 23:47:09 [Note] /usr/libexec/mysqld: Shutdown complete
[--] 5) 181106 1:21:09 [Note] /usr/libexec/mysqld: Shutdown complete
[--] 6) 181029 11:04:05 [Note] /usr/libexec/mysqld: Shutdown complete
[--] 7) 181009 9:48:37 [Note] /usr/libexec/mysqld: Shutdown complete
[--] 8) 180929 22:46:58 [Note] /usr/libexec/mysqld: Shutdown complete
[--] 9) 180827 3:40:00 [Note] /usr/libexec/mysqld: Shutdown complete
[--] 10) 180723 23:23:45 [Note] /usr/libexec/mysqld: Shutdown complete
-------- Storage Engine Statistics -----------------------------------------------------------------
[--] Status: +ARCHIVE +Aria +BLACKHOLE +CSV +FEDERATED +InnoDB +MEMORY +MRG_MYISAM +MyISAM +PERFORMANCE_SCHEMA
[--] Data in MyISAM tables: 836M (Tables: 100)
[--] Data in InnoDB tables: 28M (Tables: 356)
[OK] Total fragmented tables: 0
-------- Security Recommendations ------------------------------------------------------------------
[OK] There are no anonymous accounts for any database users
[OK] All database users have passwords assigned
[!!] There is no basic password file list!
-------- CVE Security Recommendations --------------------------------------------------------------
[--] Skipped due to --cvefile option undefined
-------- Performance Metrics -----------------------------------------------------------------------
[--] Up for: 3d 13h 42m 42s (528K q [1.714 qps], 143K conn, TX: 3G, RX: 67M)
[--] Reads / Writes: 97% / 3%
[--] Binary logging is disabled
[--] Physical Memory : 2.0G
[--] Max MySQL memory : 1.6G
[--] Other process memory: 400.6M
[--] Total buffers: 1.2G global + 2.8M per thread (151 max threads)
[--] P_S Max memory usage: 0B
[--] Galera GCache Max memory usage: 0B
[OK] Maximum reached memory usage: 1.2G (61.38% of installed RAM)
[OK] Maximum possible memory usage: 1.6G (81.28% of installed RAM)
[!!] Overall possible memory usage with other process exceeded memory
[OK] Slow queries: 0% (268/528K)
[OK] Highest usage of available connections: 5% (8/151)
[OK] Aborted connections: 0.00% (1/143266)
[!!] name resolution is active : a reverse name resolution is made for each new connection and can reduce performance
[!!] Query cache may be disabled by default due to mutex contention.
[OK] Query cache efficiency: 46.5% (202K cached / 436K selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 3% (57 temp sorts / 1K sorts)
[OK] No joins without indexes
[!!] Temporary tables created on disk: 70% (12K on disk / 17K total)
[OK] Thread cache hit rate: 99% (50 created / 143K connections)
[!!] Table cache hit rate: 9% (400 open / 4K opened)
[OK] Open file limit used: 21% (224/1K)
[OK] Table locks acquired immediately: 99% (63K immediate / 63K locks)
-------- Performance schema ------------------------------------------------------------------------
[--] Performance schema is disabled.
[--] Memory used by P_S: 0B
[--] Sys schema isn't installed.
-------- ThreadPool Metrics ------------------------------------------------------------------------
[--] ThreadPool stat is enabled.
[--] Thread Pool Size: 2 thread(s).
[--] Using default value is good enough for your version (5.5.60-MariaDB)
-------- MyISAM Metrics ----------------------------------------------------------------------------
[!!] Key buffer used: 75.7% (25M used / 33M cache)
[OK] Key buffer size / total MyISAM indexes: 32.0M/238.8M
[OK] Read Key buffer hit rate: 100.0% (1B cached / 79K reads)
[OK] Write Key buffer hit rate: 100.0% (18K cached / 18K writes)
-------- InnoDB Metrics ----------------------------------------------------------------------------
[--] InnoDB is enabled.
[--] InnoDB Thread Concurrency: 0
[!!] InnoDB File per table is not activated
[OK] InnoDB buffer pool / data size: 756.0M/28.3M
[!!] Ratio InnoDB log file size / InnoDB Buffer pool size (1.32275132275132 %): 5.0M * 2/756.0M should be equal 25%
[OK] InnoDB buffer pool instances: 1
[--] InnoDB Buffer Pool Chunk Size not used or defined in your version
[OK] InnoDB Read buffer efficiency: 99.91% (2333138 hits/ 2335267 total)
[!!] InnoDB Write Log efficiency: 2.25% (33 hits/ 1469 total)
[OK] InnoDB log waits: 0.00% (0 waits / 1436 writes)
-------- AriaDB Metrics ----------------------------------------------------------------------------
[--] AriaDB is enabled.
[OK] Aria pagecache size / total Aria indexes: 128.0M/1B
[OK] Aria pagecache hit rate: 99.6% (96K cached / 359 reads)
-------- TokuDB Metrics ----------------------------------------------------------------------------
[--] TokuDB is disabled.
-------- XtraDB Metrics ----------------------------------------------------------------------------
[--] XtraDB is disabled.
-------- RocksDB Metrics ---------------------------------------------------------------------------
[--] RocksDB is disabled.
-------- Spider Metrics ----------------------------------------------------------------------------
[--] Spider is disabled.
-------- Connect Metrics ---------------------------------------------------------------------------
[--] Connect 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:
Control warning line(s) into /var/log/mariadb/mariadb.log file
Control error line(s) into /var/log/mariadb/mariadb.log file
Dedicate this server to your database for highest performance.
Enable the slow query log to troubleshoot bad queries
Configure your accounts with ip or subnets only, then update your configuration with skip-name-resolve=1
When making adjustments, make tmp_table_size/max_heap_table_size equal
Reduce your SELECT DISTINCT queries which have no LIMIT clause
Increase table_open_cache gradually to avoid file descriptor limits
Read this before increasing table_open_cache over 64: (bit.ly removed)
Beware that open_files_limit (1024) variable
should be greater than table_open_cache (400)
Consider installing Sys schema from https://github.com/mysql/mysql-sys
Read this before changing innodb_log_file_size and/or innodb_log_files_in_group: (bit.ly removed)
Variables to adjust:
query_cache_size (=0)
query_cache_type (=0)
tmp_table_size (> 16M)
max_heap_table_size (> 16M)
table_open_cache (> 400)
innodb_file_per_table=ON
innodb_log_file_size should be (=94M) if possible, so InnoDB total log files size equals to 25% of buffer pool size.
显示全局状态;https://pastebin.com/u9gkg6fk
显示全局变量bleshttps://pastebin.com/3n7neeqa
3条答案
按热度按时间wnavrhmk1#
当dns配置不正确时,我在mysql中遇到了严重的问题。一个需要一毫秒的查询开始需要40秒。修复/etc/resolv.conf文件立即恢复了预期的速度。
stszievb2#
为您的my.cnf[mysqld]节每秒速率=rps考虑的建议
如需其他建议,请查看我的个人资料,联系信息的网络个人资料。
2lpgd9683#
当表变得太大而无法缓存时,可能出现的一个问题是uuid的使用。你在用这种药吗?
大概,您已经确定了几个5秒钟的查询?也许和
SHOW FULL PROCESSLIST;
? 或是用慢语。让我们一起看看他们SHOW CREATE TABLE
. 这会给我们更多的线索。如果您使用参考手册中给出的代码,那么“groupwise max”类型的查询会以二次方式减慢速度。
还提供ram大小和my.cnf设置。
我部分不同意o。琼斯。对于“点查询”,即使十亿行表也不会比十行表慢多少。另一方面,对于表格扫描,速度与表格大小成正比。这就是索引常常“省事”的地方。