Question posted in 
Earlier this year I moved a lightly traffic, data centric site from shared hosting onto a CentOs CloudVM. Performance was perfectly acceptable for 7+ months – now ~10% of queries per day are taking longer than 5 seconds to complete, many of those are taking considerably longer.
During those months more data was added but nothing changed within the codebase. Yet the performance has now gone off a cliff. I’ve tried restarting the instance, adjusting the my.cnf, mysqltuner, etc. but without any improvement…
Perhaps this issue is too instance specific but if anyone could offer some pointers on where to look for potential causes to this drastic performance lag it would be a great help
UPDATE
Looking at the top processes on the production server and typically MySQLD holds at negligible CPU% but will often spike to 100%+ before dropping back.
Having reviewed the slow queries they all use LIKE ‘%query text%’. However some run perfectly fast, others can take as long as 20+ seconds for nearly identical queries.
The mentioned data increase is in the region of an additional ~30k rows added to the existing ~340k.
I’m the only developer working on the codebase, and no changes have been made within the data query aspect of the application. However some environment changes were made relatively recently which introduced some instability for a period (an attempt to upgrade the cli version of PHP from 5.4 to 7.x). The instability has since been resolved by upgrading all packages, overwriting the ill-configured packages within the Apache setup.
Cloud Instance has 2GB RAM for both Web server & DB server
my.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 <[email protected]>
>> 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.
SHOW GLOBAL STATUS;
https://pastebin.com/U9gkg6fK
SHOW GLOBAL VARIABLES
https://pastebin.com/3n7NeEqa
3
Answers
One cliff that can occur is in the use of UUIDs when the table gets too big to be cached. Are you using such?
Presumably, you have identified a few of the 5-second queries? Perhaps with
SHOW FULL PROCESSLIST;? Or with the slowlog. Let’s see them, together withSHOW CREATE TABLE. That will give us some more clues.“Groupwise-max” type of queries slow down quadratically if you use the code given in the reference manual.
Also provide RAM size and the my.cnf settings.
I’ll partially disagree with O. Jones. For “point queries”, even a billion-row table is not much slower than a 10-row table. For a table scan, on the other hand, the speed is proportional to the table size. And that’s where indexes often “save the day”.
I have had major slowdowns in Mysql when the DNS was configured incorrectly. A query which should take a millisecond started to take up to 40 seconds. Fixing the /etc/resolv.conf file instantly brought back the expected speed.
Suggestions to consider for your my.cnf [mysqld] section Rate Per Second=RPS
for additional suggestions, view my profile, Network profile for contact info.