1

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:

enter image description here

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

Your data appears to be much smaller than the allocated buffer pool size, so this is not a question of having insufficient RAM.

[OK] InnoDB buffer pool / data size: 55.0G/1.8G

In spite of your broad statement that "the tables are indexed", it appears that you don't have the right indexes to support the queries you run.

[!!] Joins performed without indexes: 6435
...
Adjust your join queries to always utilize indexes

I would also guess that scanning millions of rows polling for changes is not a scalable way to design your application.

You should instead use a change-notification bus. Likely technologies include:

2
  • I'm a total novice here dealing with unexpected growth -- those are technologies I am unfamiliar with, thank you for the introduction ... I think the unindexed join queries are related to a cleanup cron job we uses that deletes records older than 2 hours creating a temp table in the process. Will take a closer look. – Kelly Jan 19 '18 at 19:20
  • 6435 non-indexed queries for 350M queries in total can't be a problem. Excessive RAM allocated to the innodb_buffer_pool could be passed to the connections for more efficient queries execution. I would set the innodb_buffer_pool to 8GB (approx 4x of data), and I would set the (join|sort|read|read_rnd)_buffer_size to 1MB each. – Kondybas Nov 4 '20 at 8:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.