MySQL optimization for mapping service -- getting MySQL to use more memory?

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 >> 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)