Time to upgrade hardware? ... I'm not sure if there is much more I can do, spent a few days tweaking/googling -- before taking the upgrade plunge I thought I'd check here... to see if the well experienced DBAs might offer some further hints.
My server has 64GB of RAM and 4c/8t processors with SSD drives. It is dedicated to MySQL (Percona 5.7). The CPUs are nearly always maxed out, yet only about 15% of memory is ever utilized.
Disk IO seems always near max too... I've tweaked all the settings I could find to reduce CPU/and disk IO.
Our application is very active -- the backend scans approx 1.5Millon locations stored in the database for any changes to information about what might be at this location, and if there is change it updates the relative table in the db -- on the front end is a live map that queries this data and maintains a connection to update the map as the data updates. Most of my slow queries in the slow query log are related to queries getting this updated information to the map for the end user.
My tables are indexed. Losing data in a crash would not be a big deal.
MySQL status:
--------------
mysql Ver 14.14 Distrib 5.7.20-19, for debian-linux-gnu (x86_64) using 6.3
Server version: 5.7.20-19-log Percona Server (GPL), Release '19', Revision '3c5d3e5d53c'
Protocol version: 10
Connection: Localhost via UNIX socket
Server characterset: utf8mb4
Db characterset: utf8mb4
Client characterset: utf8mb4
Conn. characterset: utf8mb4
UNIX socket: /var/lib/mysql/mysql.sock
Uptime: 17 hours 53 min 27 sec
Threads: 748 Questions: 328898348 Slow queries: 980150 Opens: 7302 Flush tables: 1 Open tables: 1445 Queries per second avg: 5106.562
my.cnf
:
[client]
default-character-set = utf8mb4
[mysql]
default-character-set = utf8mb4
port = 3306
socket = /var/lib/mysql/mysql.sock
[mysqld]
# GENERAL #
user = mysql
default-storage-engine = InnoDB
socket = /var/lib/mysql/mysql.sock
pid-file = /var/lib/mysql/mysql.pid
character-set-client-handshake = FALSE
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci
port = 3306
performance-schema = false
# SAFETY #
max-allowed-packet = 16M
max-connect-errors = 1000000
skip-name-resolve
# DATA STORAGE #
datadir = /var/lib/mysql/
# CACHES AND LIMITS #
tmp-table-size = 32M
max-heap-table-size = 32M
query-cache-type = 0
query-cache-size = 0
max-connections = 1000
thread-cache-size = 100
thread_pool_size = 32
open-files-limit = 65535
table-definition-cache = 1024
table-open-cache = 2048
sync_binlog = 0
# INNODB #
innodb-flush-method = O_DIRECT
innodb-log-files-in-group = 2
innodb-log-file-size = 2G
innodb_log_buffer_size = 8M
innodb-flush-log-at-trx-commit = 2
innodb-file-per-table = 1
innodb-buffer-pool-size = 54G
innodb_buffer_pool_instances = 10
innodb_write_io_threads = 16
innodb_read_io_threads = 16
# LOGGING #
log-error = /var/lib/mysql/mysql-error.log
log-queries-not-using-indexes = 1
slow-query-log = 1
slow-query-log-file = /var/lib/mysql/mysql-slow.log
htop
:
iostat
:
avg-cpu: %user %nice %system %iowait %steal %idle
98.83 0.00 1.18 0.00 0.00 0.00
Device: rrqm/s wrqm/s r/s w/s rkB/s wkB/s avgrq-sz avgqu-sz await r_await w_await svctm %util
nvme0n1 0.00 13.40 0.00 58.20 0.00 1306.50 44.90 0.00 0.08 0.00 0.08 0.04 0.24
md2 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
md3 0.00 0.00 0.00 78.20 0.00 1290.40 33.00 0.00 0.00 0.00 0.00 0.00 0.00
nvme1n1 0.00 13.40 0.00 58.20 0.00 1306.50 44.90 0.00 0.03 0.00 0.03 0.01 0.08
avg-cpu: %user %nice %system %iowait %steal %idle
98.85 0.00 1.15 0.00 0.00 0.00
Device: rrqm/s wrqm/s r/s w/s rkB/s wkB/s avgrq-sz avgqu-sz await r_await w_await svctm %util
nvme0n1 0.00 11.40 0.00 58.40 0.00 1178.20 40.35 0.00 0.07 0.00 0.07 0.03 0.16
md2 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
md3 0.00 0.00 0.00 76.80 0.00 1162.40 30.27 0.00 0.00 0.00 0.00 0.00 0.00
nvme1n1 0.00 11.40 0.00 58.40 0.00 1178.20 40.35 0.00 0.08 0.00 0.08 0.03 0.16
avg-cpu: %user %nice %system %iowait %steal %idle
98.90 0.00 1.10 0.00 0.00 0.00
Device: rrqm/s wrqm/s r/s w/s rkB/s wkB/s avgrq-sz avgqu-sz await r_await w_await svctm %util
nvme0n1 0.00 10.40 0.00 52.80 0.00 1093.90 41.44 0.00 0.06 0.00 0.06 0.02 0.08
md2 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
md3 0.00 0.00 0.00 70.20 0.00 1078.40 30.72 0.00 0.00 0.00 0.00 0.00 0.00
nvme1n1 0.00 10.40 0.00 52.80 0.00 1093.90 41.44 0.00 0.06 0.00 0.06 0.03 0.16
avg-cpu: %user %nice %system %iowait %steal %idle
98.80 0.00 1.20 0.00 0.00 0.00
Device: rrqm/s wrqm/s r/s w/s rkB/s wkB/s avgrq-sz avgqu-sz await r_await w_await svctm %util
nvme0n1 0.00 8.80 0.00 50.80 0.00 1082.30 42.61 0.00 0.02 0.00 0.02 0.00 0.00
md2 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
md3 0.00 0.00 0.00 67.80 0.00 1066.40 31.46 0.00 0.00 0.00 0.00 0.00 0.00
nvme1n1 0.00 8.80 0.00 50.80 0.00 1082.30 42.61 0.00 0.02 0.00 0.02 0.00 0.00
mysqltuner.pl
:
>> 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.7.20-19-log
[OK] Operating on 64-bit architecture
-------- Performance Metrics -----------------------------------------------------------------------
[--] Up for: 19h 3m 15s (350M q [5K qps], 1M conn, TX: 156G, RX: 97G)
[--] Reads / Writes: 62% / 38%
[--] Binary logging is disabled
[--] Physical Memory : 62.6G
[--] Max MySQL memory : 56.1G
[--] Other process memory: 115.1M
[--] Total buffers: 55.0G global + 1.1M per thread (1000 max threads)
[--] P_S Max memory usage: 0B
[--] Galera GCache Max memory usage: 0B
[!!] Maximum reached memory usage: 55.9G (89.22% of installed RAM)
[!!] Maximum possible memory usage: 56.1G (89.65% of installed RAM)
[OK] Overall possible memory usage with other process is compatible with memory available
[OK] Slow queries: 0% (1M/350M)
[OK] Highest usage of available connections: 75% (753/1000)
[OK] Aborted connections: 0.00% (36/1607260)
[OK] Query cache is disabled by default due to mutex contention on multiprocessor machines.
[OK] Sorts requiring temporary tables: 0% (1K temp sorts / 18M sorts)
[!!] Joins performed without indexes: 6435
[OK] Temporary tables created on disk: 0% (65 on disk / 187K total)
[OK] Thread cache hit rate: 99% (1K created / 1M connections)
[!!] Table cache hit rate: 18% (1K open / 7K opened)
[OK] Open file limit used: 0% (45/1M)
[OK] Table locks acquired immediately: 100% (2K immediate / 2K locks)
-------- Performance schema ------------------------------------------------------------------------
[--] Performance schema is disabled.
[--] Memory used by P_S: 0B
[--] Sys schema is installed.
-------- ThreadPool Metrics ------------------------------------------------------------------------
[--] ThreadPool stat is enabled.
[--] Thread Pool Size: 32 thread(s).
[OK] thread_pool_size between 16 and 36 when using InnoDB storage engine.
-------- MyISAM Metrics ----------------------------------------------------------------------------
[!!] Key buffer used: 18.3% (1M used / 8M cache)
[OK] Key buffer size / total MyISAM indexes: 8.0M/44.0K
[!!] Read Key buffer hit rate: 93.8% (112 cached / 7 reads)
-------- InnoDB Metrics ----------------------------------------------------------------------------
[--] InnoDB is enabled.
[--] InnoDB Thread Concurrency: 0
[OK] InnoDB File per table is activated
[OK] InnoDB buffer pool / data size: 55.0G/1.8G
[!!] Ratio InnoDB log file size / InnoDB Buffer pool size (7.27272727272727 %): 2.0G * 2/55.0G should be equal 25%
[!!] InnoDB buffer pool instances: 10
[--] Number of InnoDB Buffer Pool Chunk : 440 for 10 Buffer Pool Instance(s)
[OK] Innodb_buffer_pool_size aligned with Innodb_buffer_pool_chunk_size & Innodb_buffer_pool_instances
[OK] InnoDB Read buffer efficiency: 100.00% (77730783334 hits/ 77730970038 total)
[!!] InnoDB Write Log efficiency: 48.21% (32580982 hits/ 67583255 total)
[OK] InnoDB log waits: 0.00% (0 waits / 35002273 writes)
-------- Recommendations ---------------------------------------------------------------------------
General recommendations:
Control warning line(s) into /var/lib/mysql/mysql-error.log file
Control error line(s) into /var/lib/mysql/mysql-error.log file
Restrict Host for user@% to user@SpecificDNSorIp
MySQL started within last 24 hours - recommendations may be inaccurate
Reduce your overall MySQL memory footprint for system stability
Adjust your join queries to always utilize indexes
Increase table_open_cache gradually to avoid file descriptor limits
Read this before increasing table_open_cache over 64:
Beware that open_files_limit (1048576) variable
should be greater than table_open_cache (2048)
Performance should be activated for better diagnostics
Read this before changing innodb_log_file_size and/or innodb_log_files_in_group:
Variables to adjust:
join_buffer_size (> 256.0K, or always use indexes with joins)
table_open_cache (> 2048)
performance_schema = ON enable PFS
innodb_log_file_size should be (=6G) if possible, so InnoDB total log files size equals to 25% of buffer pool size.
innodb_buffer_pool_instances(=55)