MySQL Tuner Performance Advice

Question

My app works on LAMP 7.3, MySQL 5.7, Cloud VPS. The VPS has 2 GB RAM, 1 vCPU, 60 GB SSD.

MySQL Tuner is working for 5-6 days, I'll share the output.

-------- Storage Engine Statistics -----------------------------------------------------------------
[--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MEMORY +MRG_MYISAM +MyISAM +PERFORMANCE_SCHEMA
[--] Data in MyISAM tables: 35.5K (Tables: 6)
[--] Data in InnoDB tables: 1.3G (Tables: 307)
[OK] Total fragmented tables: 0

-------- Analysis Performance Metrics --------------------------------------------------------------
[--] innodb_stats_on_metadata: OFF
[OK] No stat updates during querying INFORMATION_SCHEMA.

-------- Security Recommendations ------------------------------------------------------------------
[!!] User 'DB_USR@%' does not specify hostname restrictions.
[!!] There is no basic password file list!

-------- CVE Security Recommendations --------------------------------------------------------------
[--] Skipped due to --cvefile option undefined

-------- Performance Metrics -----------------------------------------------------------------------
[--] Up for: 3d 15h 10m 22s (63M q [201.537 qps], 174K conn, TX: 87G, RX: 18G)
[--] Reads / Writes: 97% / 3%
[--] Binary logging is disabled



[--] Physical Memory     : 2.0G
[--] Max MySQL memory    : 6.0G
[--] Other process memory: 0B
[--] Total buffers: 234.0M global + 39.2M per thread (151 max threads)
[--] P_S Max memory usage: 0B
[--] Galera GCache Max memory usage: 0B
[!!] Maximum reached memory usage: 1.8G (92.22% of installed RAM)
[!!] Maximum possible memory usage: 6.0G (308.25% of installed RAM)
[!!] Overall possible memory usage with other process exceeded memory
[OK] Slow queries: 0% (0/63M)
[OK] Highest usage of available connections: 27% (41/151)
[OK] Aborted connections: 0.00%  (1/174710)
[!!] Query cache may be disabled by default due to mutex contention.
[OK] Query cache efficiency: 66.2% (41M cached / 62M selects)
[!!] Query cache prunes per day: 4956861
[OK] Sorts requiring temporary tables: 0% (8K temp sorts / 4M sorts)
[!!] Joins performed without indexes: 69476
[OK] Temporary tables created on disk: 9% (598K on disk / 6M total)
[OK] Thread cache hit rate: 99% (41 created / 174K connections)
[!!] Table cache hit rate: 2% (2K open / 85K opened)
[OK] table_definition_cache(1000) is upper than number of tables(592)
[OK] Open file limit used: 0% (10/5K)
[OK] Table locks acquired immediately: 100% (3K immediate / 3K locks)

-------- Performance schema ------------------------------------------------------------------------
[--] Performance schema is disabled.
[--] Memory used by P_S: 0B
[--] Sys schema is installed.

-------- ThreadPool Metrics ------------------------------------------------------------------------
[--] ThreadPool stat is disabled.

-------- MyISAM Metrics ----------------------------------------------------------------------------
[!!] Key buffer used: 18.3% (1M used / 8M cache)
[OK] Key buffer size / total MyISAM indexes: 8.0M/55.0K
[OK] Read Key buffer hit rate: 97.0% (2K cached / 81 reads)

-------- InnoDB Metrics ----------------------------------------------------------------------------
[--] InnoDB is enabled.


[--] InnoDB Thread Concurrency: 0
[OK] InnoDB File per table is activated
[!!] InnoDB buffer pool / data size: 128.0M/1.3G
[!!] Ratio InnoDB log file size / InnoDB Buffer pool size (75 %): 48.0M * 2/128.0M should be equal to 25%
[OK] InnoDB buffer pool instances: 1
[--] Number of InnoDB Buffer Pool Chunk : 1 for 1 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: 99.91% (5695188248 hits/ 5700073111 total)
[!!] InnoDB Write Log efficiency: 83.88% (2772762 hits/ 3305708 total)
[OK] InnoDB log waits: 0.00% (0 waits / 532946 writes)

-------- AriaDB Metrics ----------------------------------------------------------------------------
[--] AriaDB is disabled.

-------- TokuDB Metrics ----------------------------------------------------------------------------
[--] TokuDB is disabled.

-------- XtraDB Metrics ----------------------------------------------------------------------------
[--] XtraDB is disabled.

-------- Galera Metrics ----------------------------------------------------------------------------
[--] Galera is disabled.

-------- Replication Metrics -----------------------------------------------------------------------
[--] Galera Synchronous replication: NO
[--] No replication slave(s) for this server.
[--] Binlog format: ROW
[--] XA support enabled: ON
[--] Semi synchronous replication Master: Not Activated
[--] Semi synchronous replication Slave: Not Activated
[--] This is a standalone server

And there are general recommendations:

-------- Recommendations ---------------------------------------------------------------------------
    Control warning line(s) into /opt/bitnami/mysql/data/mysqld.log file
    Control error line(s) into /opt/bitnami/mysql/data/mysqld.log file
    Restrict Host for 'DB_USR'@% to DB_USR@SpecificDNSorIp
    UPDATE mysql.user SET host ='SpecificDNSorIp' WHERE user='DB_USR' AND host ='%'; FLUSH PRIVILEGES;
    Reduce your overall MySQL memory footprint for system stability
    Dedicate this server to your database for highest performance.
    We will suggest raising the 'join_buffer_size' until JOINs not using indexes are found.
             See https://dev.mysql.com/doc/internals/en/join-buffer-size.html
             (specially the conclusions at the bottom of the page).
    Increase table_open_cache gradually to avoid file descriptor limits
    Read this before increasing table_open_cache over 64: 
    Read this before increasing for MariaDB https://mariadb.com/kb/en/library/optimizing-table_open_cache/
    This is MyISAM only table_cache scalability problem, InnoDB not affected.
    See more details here: https://bugs.mysql.com/bug.php?id=49177
    This bug already fixed in MySQL 5.7.9 and newer MySQL versions.
    Beware that open_files_limit (5000) variable
    should be greater than table_open_cache (2100)
    Performance schema should be activated for better diagnostics
    Before changing innodb_log_file_size and/or innodb_log_files_in_group read this: 
Variables to adjust:
  *** MySQL's maximum memory usage is dangerously high ***
  *** Add RAM before increasing MySQL buffer variables ***
    query_cache_size (=0)
    query_cache_type (=0)
    query_cache_size (> 50M)
    join_buffer_size (> 2.0M, or always use indexes with JOINs)
    table_open_cache (> 2100)
    performance_schema = ON enable PFS
    innodb_buffer_pool_size (>= 1.3G) if possible.
    innodb_log_file_size should be (=16M) if possible, so InnoDB total log files size equals to 25% of buffer pool size.

And

atop -d output like this:

atop -d

It gives red alert.

SWP |  tot     0.0M |  free    0.0M  | | |  | | | |  vmcom   1.4G  | vmlim   1.0G  |

mytop output is

Cannot connect to MySQL server. Please check the:
* database you specified "" (default is "")
  * username you specified "root" (default is "root")
  * password you specified "" (default is "")
  * hostname you specified "localhost" (default is "localhost")
  * port you specified "3306" (default is 3306)
  * socket you specified "" (default is "")

The options my be specified on the command-line or in a ~/.mytop or
~/.my.cnf config file. See the manual (perldoc mytop) for details.

Here's the exact error from DBI. It might help you debug:

Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)

Would you comment to them please?

Thanks!

Answer 1

With available information, consider the following:

OS swap size of 8G would be helpful. When swapped, slow but not failing.

Suggestions to consider for your my.cnf [mysqld] section:

innodb_io_capacity=1900  # from 200 to enable use of more IOPS on your SSD device
max_connections=60  # from 151 since max used was 41 in 3 days
query_cache_size=0  # from 50M because you only have 2G to use TOTAL
table_open_cache=3500  # from 2100 to reduce opened_tables count

This is just the tip of the iceberg, many more Global Variables need to be adjusted.

For additional suggestions, post the additional info requested, view my profile, Network profile for FREE Utility Scripts to assist with performance tuning.