I noticed that the memory usage for mysqld keeps growing over time. The sites hosted are quite low traffic, though they're mostly Magento installations (which is known to be a bit of a hog).
I tuned some settings from a separate question I asked a while ago which you can view here.
I feel that the biggest culprit is the slow queries (though it doesn't show in the mysqltuner report since I have it currently set quite high), joins performed without indexes, and/or save point rollbacks. It's just that I have no clue where to start in resolving it.
Would greatly appreciate some guidance.
I'm sorry if this is a silly question, but I'm not an expert in MySQL.
This is a dedicated server with about 15 sites and about half of them are Magento 1 installations. All sites are fairly low traffic. 16GB of total RAM is on the server which is used for all services (MySQL, httpd, mail, etc)
Requested files: zip-2017-02-05 | zip-2017-02-07
my.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/chroot/tmp/mysql.sock
userstat=1
ft_min_word_len=3
max_connections=100 # MAX CURRENT X 5
max_connect_errors=25
connect_timeout=10
interactive_timeout=20
wait_timeout=50
delayed_insert_timeout=10
#join_buffer_size=1M
max_allowed_packet=16M
myisam_sort_buffer_size=1M
#sort_buffer_size=1M
#read_buffer_size=1M
#read_rnd_buffer_size=2M
thread_cache_size=100 # from 192 MySQL v 8 recommends CAP of 100 to avoid overload
thread_concurrency=4
query_cache_size=100M # from 256M
#query_cache_limit=32M #remove to allow default of 1M rather than 32M
query_cache_type=1
query_cache_min_res_unit = 512 # from 4K to conserve qcache RAM used per RESULT
tmp_table_size=80M # from 512M
max_heap_table_size=80M # from 512M
max_tmp_tables=10
slow_query_log=1
long_query_time=10
slow-query-log-file = /var/lib/mysql/mysql-slow.log
table_open_cache = 10000 # 10000 from 2000 to support the 28,000 opened by instance
innodb-file-per-table=1
character-set-server=utf8
collation-server=utf8_general_ci
# Tweaking below
# 256M
innodb_buffer_pool_size = 4G # to match RAM reported by MySQLTuner
# 512 MB
key_buffer_size = 256M # from 512M used only my MyISAM
# 16MB
bulk_insert_buffer_size = 16M
innodb_thread_concurrency = 4
innodb_autoinc_lock_mode = 0
skip-external-locking
# Double write off :)
innodb_doublewrite = true
low_priority_updates = 1
#innodb_checksums = false
innodb_support_xa = false
max_write_lock_count = 10
innodb_flush_log_at_trx_commit = 2
innodb_max_dirty_pages_pct = 40
innodb_io_capacity = 400
innodb_write_io_threads = 4
innodb_read_io_threads = 4
innodb_adaptive_flushing = 1
innodb_flush_method = O_DIRECT
innodb_log_file_size=1G
innodb_print_all_deadlocks = 1 # to error log, if you ever have one, you will WISH you had the details.
innodb_buffer_pool_dump_at_shutdown=1 # from OFF to allow quick warmup
innodb_buffer_pool_load_at_startup=1 #from OFF to allow warmed buffer_pool
#skip-name-resolve=1
#log_error_verbosity=3
log_warnings = 2 # to record Aborted Connections in error.log
mysqltuner.pl (Updated 02/05/2018 05:13PM)
-------- Storage Engine Statistics -----------------------------------------------------------------
[--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MEMORY +MRG_MYISAM +MyISAM +PERFORMANCE_SCHEMA
[--] Data in MyISAM tables: 96M (Tables: 302)
[--] Data in InnoDB tables: 964M (Tables: 3988)
[--] Data in MEMORY tables: 3M (Tables: 158)
[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 are 612 basic passwords in the list.
-------- CVE Security Recommendations --------------------------------------------------------------
[OK] NO SECURITY CVE FOUND FOR YOUR VERSION
-------- Performance Metrics -----------------------------------------------------------------------
[--] Up for: 26d 6h 45m 47s (43M q [18.957 qps], 1M conn, TX: 59G, RX: 13G)
[--] Reads / Writes: 57% / 43%
[--] Binary logging is disabled
[--] Physical Memory : 15.5G
[--] Max MySQL memory : 5.1G
[--] Other process memory: 1.3G
[--] Total buffers: 4.4G global + 1.1M per thread (100 max threads)
[--] P_S Max memory usage: 555M
[--] Galera GCache Max memory usage: 0B
[OK] Maximum reached memory usage: 5.0G (32.22% of installed RAM)
[OK] Maximum possible memory usage: 5.1G (32.76% of installed RAM)
[OK] Overall possible memory usage with other process is compatible with memory available
[OK] Slow queries: 0% (4/43M)
[OK] Highest usage of available connections: 23% (23/100)
[OK] Aborted connections: 0.01% (201/1369212)
[!!] 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: 91.1% (31M cached / 34M selects)
[!!] Query cache prunes per day: 51145
[OK] Sorts requiring temporary tables: 0% (74 temp sorts / 528K sorts)
[!!] Joins performed without indexes: 32913
[OK] Temporary tables created on disk: 24% (315K on disk / 1M total)
[OK] Thread cache hit rate: 99% (23 created / 1M connections)
[OK] Table cache hit rate: 75% (8K open / 11K opened)
[OK] Open file limit used: 4% (812/20K)
[OK] Table locks acquired immediately: 99% (8M immediate / 8M locks)
-------- Performance schema ------------------------------------------------------------------------
[--] Memory used by P_S: 555.4M
[--] Sys schema isn't installed.
-------- ThreadPool Metrics ------------------------------------------------------------------------
[--] ThreadPool stat is enabled.
[--] Thread Pool Size: 8 thread(s).
[!!] thread_pool_size between 16 and 36 when using InnoDB storage engine.
-------- MyISAM Metrics ----------------------------------------------------------------------------
[!!] Key buffer used: 19.8% (53M used / 268M cache)
[OK] Key buffer size / total MyISAM indexes: 256.0M/23.2M
[OK] Read Key buffer hit rate: 99.9% (6M cached / 3K reads)
[!!] Write Key buffer hit rate: 69.2% (92K cached / 64K writes)
-------- InnoDB Metrics ----------------------------------------------------------------------------
[--] InnoDB is enabled.
[--] InnoDB Thread Concurrency: 4
[OK] InnoDB File per table is activated
[OK] InnoDB buffer pool / data size: 4.0G/964.1M
[!!] Ratio InnoDB log file size / InnoDB Buffer pool size (50 %): 1.0G * 2/4.0G should be equal 25%
[!!] InnoDB buffer pool instances: 8
[--] InnoDB Buffer Pool Chunk Size not used or defined in your version
[OK] InnoDB Read buffer efficiency: 100.00% (1171708542 hits/ 1171766170 total)
[!!] InnoDB Write Log efficiency: 55.47% (2772358 hits/ 4997589 total)
[OK] InnoDB log waits: 0.00% (0 waits / 2225231 writes)
-------- AriaDB Metrics ----------------------------------------------------------------------------
[--] AriaDB is disabled.
-------- 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:
Configure your accounts with ip or subnets only, then update your configuration with skip-name-resolve=1
Adjust your join queries to always utilize indexes
Consider installing Sys schema from https://github.com/mysql/mysql-sys
Thread pool size for InnoDB usage (8)
Variables to adjust:
query_cache_size (=0)
query_cache_type (=0)
query_cache_size (> 100M)
join_buffer_size (> 256.0K, or always use indexes with joins)
thread_pool_size between 16 and 36 for InnoDB usage
innodb_log_file_size should be (=512M) if possible, so InnoDB total log files size equals to 25% of buffer pool size.
innodb_buffer_pool_instances(=4)
mysqltuner.pl (Updated 02/07/2018 07:16PM)
-------- Storage Engine Statistics -----------------------------------------------------------------
[--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MEMORY +MRG_MYISAM +MyISAM +PERFORMANCE_SCHEMA
[--] Data in MyISAM tables: 96M (Tables: 302)
[--] Data in InnoDB tables: 969M (Tables: 3988)
[--] Data in MEMORY tables: 3M (Tables: 158)
[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 are 612 basic passwords in the list.
-------- CVE Security Recommendations --------------------------------------------------------------
[OK] NO SECURITY CVE FOUND FOR YOUR VERSION
-------- Performance Metrics -----------------------------------------------------------------------
[--] Up for: 28d 8h 50m 27s (46M q [18.909 qps], 1M conn, TX: 63G, RX: 14G)
[--] Reads / Writes: 58% / 42%
[--] Binary logging is disabled
[--] Physical Memory : 15.5G
[--] Max MySQL memory : 5.1G
[--] Other process memory: 1.4G
[--] Total buffers: 4.4G global + 1.1M per thread (100 max threads)
[--] P_S Max memory usage: 555M
[--] Galera GCache Max memory usage: 0B
[OK] Maximum reached memory usage: 5.0G (32.22% of installed RAM)
[OK] Maximum possible memory usage: 5.1G (32.76% of installed RAM)
[OK] Overall possible memory usage with other process is compatible with memory available
[OK] Slow queries: 0% (4/46M)
[OK] Highest usage of available connections: 23% (23/100)
[OK] Aborted connections: 0.02% (247/1484045)
[!!] 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: 90.9% (33M cached / 36M selects)
[!!] Query cache prunes per day: 52710
[OK] Sorts requiring temporary tables: 0% (79 temp sorts / 605K sorts)
[!!] Joins performed without indexes: 35942
[OK] Temporary tables created on disk: 24% (339K on disk / 1M total)
[OK] Thread cache hit rate: 99% (23 created / 1M connections)
[OK] Table cache hit rate: 74% (8K open / 11K opened)
[OK] Open file limit used: 4% (814/20K)
[OK] Table locks acquired immediately: 99% (9M immediate / 9M locks)
-------- Performance schema ------------------------------------------------------------------------
[--] Memory used by P_S: 555.4M
[--] Sys schema isn't installed.
-------- ThreadPool Metrics ------------------------------------------------------------------------
[--] ThreadPool stat is enabled.
[--] Thread Pool Size: 8 thread(s).
[!!] thread_pool_size between 16 and 36 when using InnoDB storage engine.
-------- MyISAM Metrics ----------------------------------------------------------------------------
[!!] Key buffer used: 19.8% (53M used / 268M cache)
[OK] Key buffer size / total MyISAM indexes: 256.0M/23.2M
[OK] Read Key buffer hit rate: 99.9% (7M cached / 3K reads)
[!!] Write Key buffer hit rate: 69.4% (99K cached / 68K writes)
-------- InnoDB Metrics ----------------------------------------------------------------------------
[--] InnoDB is enabled.
[--] InnoDB Thread Concurrency: 4
[OK] InnoDB File per table is activated
[OK] InnoDB buffer pool / data size: 4.0G/969.7M
[!!] Ratio InnoDB log file size / InnoDB Buffer pool size (50 %): 1.0G * 2/4.0G should be equal 25%
[!!] InnoDB buffer pool instances: 8
[--] InnoDB Buffer Pool Chunk Size not used or defined in your version
[OK] InnoDB Read buffer efficiency: 100.00% (1664350899 hits/ 1664408544 total)
[!!] InnoDB Write Log efficiency: 54.89% (2958719 hits/ 5390689 total)
[OK] InnoDB log waits: 0.00% (0 waits / 2431970 writes)
-------- AriaDB Metrics ----------------------------------------------------------------------------
[--] AriaDB is disabled.
-------- 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:
Configure your accounts with ip or subnets only, then update your configuration with skip-name-resolve=1
Adjust your join queries to always utilize indexes
Consider installing Sys schema from https://github.com/mysql/mysql-sys
Thread pool size for InnoDB usage (8)
Variables to adjust:
query_cache_size (=0)
query_cache_type (=0)
query_cache_size (> 100M)
join_buffer_size (> 256.0K, or always use indexes with joins)
thread_pool_size between 16 and 36 for InnoDB usage
innodb_log_file_size should be (=512M) if possible, so InnoDB total log files size equals to 25% of buffer pool size.
innodb_buffer_pool_instances(=4)
