MySQL – Optimization Techniques

MySQLmysqltuner

I'm running a dedicated server with about 20 websites active. About 6 of them are Magento installations. Running the server as a webserver (web, e-mail, and MySQL ).

This is a preemptive optimization. Currently, I don't seem to have any issues, but I feel that it's good to be proactive and optimize now to avoid any potential problems later.

The server is an 8 core CPU, 8Gb RAM, running CentOS 6. The 8GB of RAM is spread around the entire server, not just the database.
I'm trying to optimize my MySQL installation for the best possible performance and stability. Ran MySQLTuner (1.7.4) and getting these suggestions.

Would appreciate some help in optimizing the settings.

Thank you!

UPDATE: 11/28/2017 08:20PM EST

 - Added additional 8GB of RAM.  
 - Latest MySQLTuner results. Global Status, Variables, Innodb Status files updated.

Can view the 3 files requested here.
Latest info below:

-------- Storage Engine Statistics -----------------------------------------------------------------
[--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MEMORY +MRG_MYISAM +MyISAM +PERFORMANCE_SCHEMA
[--] Data in MyISAM tables: 90M (Tables: 300)
[--] Data in InnoDB tables: 923M (Tables: 3591)
[--] Data in MEMORY tables: 3M (Tables: 141)
[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: 13d 17h 16m 32s (29M q [25.279 qps], 860K conn, TX: 43G, RX: 9G)
[--] Reads / Writes: 68% / 32%
[--] Binary logging is disabled
[--] Physical Memory     : 7.7G
[--] Max MySQL memory    : 5.1G
[--] Other process memory: 964.7M
[--] 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 (65.37% of installed RAM)
[OK] Maximum possible memory usage: 5.1G (66.51% of installed RAM)
[OK] Overall possible memory usage with other process is compatible with memory available
[OK] Slow queries: 0% (35/29M)
[OK] Highest usage of available connections: 21% (21/100)
[OK] Aborted connections: 0.01%  (114/860297)
[!!] 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.8% (22M cached / 25M selects)
[!!] Query cache prunes per day: 43104
[OK] Sorts requiring temporary tables: 0% (38 temp sorts / 360K sorts)
[!!] Joins performed without indexes: 22932
[OK] Temporary tables created on disk: 23% (201K on disk / 844K total)
[OK] Thread cache hit rate: 99% (21 created / 860K connections)
[OK] Table cache hit rate: 47% (7K open / 16K opened)
[OK] Open file limit used: 4% (867/20K)
[OK] Table locks acquired immediately: 99% (5M immediate / 5M 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.5% (52M used / 268M cache)
[OK] Key buffer size / total MyISAM indexes: 256.0M/22.5M
[OK] Read Key buffer hit rate: 99.9% (3M cached / 3K reads)
[!!] Write Key buffer hit rate: 42.3% (124K cached / 52K 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/923.8M
[!!] 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: 99.99% (383379684 hits/ 383413960 total)
[!!] InnoDB Write Log efficiency: 72.46% (2434433 hits/ 3359563 total)
[OK] InnoDB log waits: 0.00% (0 waits / 925130 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:
    Control warning line(s) into /var/lib/mysql/ file
    Control error line(s) into /var/lib/mysql/ file
    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)
    Read this before changing innodb_log_file_size and/or innodb_log_files_in_group:
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)

Below are the settings currently in my config file.

[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

Best Answer

Your my.cnf includes 4 lines that should be removed, they are

sort_buffer_size read_buffer_size read_rnd_buffer_size join_buffer_size

these are all per connection RAM requirements and are driving your RAM footprint much higher than necessary. Let the DEFAULTS work for you to improve response time with room to breathe in RAM. Consider adjusting the following in your my.cnf, please

key_buffer_size = 512M  # from 1G because 200M is used now
innodb_buffer_pool_size = 4G  # to match RAM reported by MySQLTuner
max_connections = 100  # from 500, until more than 8 are used
thread_cache_size = 100  # from 192 MySQL v 8 recommends CAP of 100 to avoid overload
query_cache_limit     remove to allow default of 1M rather than 32M
query_cache_min_res_unit = 512  # from 4K to conserve qcache RAM used per RESULT
table_open_cache = 10000  # from 2000 to support the 28,000 opened by instance

After the above items have been configured, shutdown/restart have been completed and you have 7 (or more) consecutive days of uptime, please refresh your my.cnf and MySQLTuner report, THEN for another detailed analysis, add/refresh to OriginalPost the following, SHOW GLOBAL STATUS; SHOW GLOBAL VARIABLES; SHOW ENGINE INNODB STATUS; for up to five specific additional cfg recommendations, one per day to be applied, monitor.

Related Solutions

MySQL – Troubleshooting Startup Issues After Increasing innodb_buffer_pool_size

The two answers given from @RickJames and @drogart are essentially the remedies. (+1 for each).

Right from the error log you present, the last two lines say:

InnoDB: Error: log file ./ib_logfile0 is of different size 0 134217728 bytes

InnoDB: than specified in the .cnf file 0 268435456 bytes! `

At that point, it was evident that you set innodb_log_file_size to 256M (268435456) in my.cnf while the InnoDB Transaction Logs (ib_logfile0,ib_logfile1) were respectively 128M (134217728) each. Looking back at the link to my StackOverflow answer in your question, you had to do the following:

Step 01) Add this to my.cnf:

[mysqld]
innodb_buffer_pool_size=4G
innodb_log_file_size=1G

Step 02) Run these command in the OS

mysql -u... -p... -e"SET GLOBAL innodb_fast_shutdown = 1"
service mysql stop
rm -f /var/lib/mysql/ib_logfile*
service mysql start

So as to have confidence in what is happening, run tail -f against the error log. You will see message telling you when each innodb log file is being created.

Mysql – How to debug a db memory-leak causing thesql to go before it’s own limits

...even surpassing it's theorically maximum possible allocation.

[OK] Maximum possible memory usage: 7.3G (46% of installed RAM)

There is not actually a way to calculate maximum possible memory usage for MySQL, because there is no cap on the memory it can request from the system.

The calculation done by mysqltuner.pl is only an estimate, based on a formula that doesn't take into account all possible variables, because if all possible variables were taken into account, the answer would always be "infinite." It's unfortunate that it's labeled this way.

Here is my theory on what's contributing to your excessive memory usage:

thread_cache_size       = 128

Given that your max_connections is set to 200, the value of 128 for thread_cache_size seems far too high. Here's what makes me think this might be contributing to your problem:

When a thread is no longer needed, the memory allocated to it is released and returned to the system unless the thread goes back into the thread cache. In that case, the memory remains allocated.

^{http://dev.mysql.com/doc/refman/5.6/en/memory-use.html}

If your workload causes even an occasional client thread to require a large amount of memory, those threads may be holding onto that memory, then going back to the pool and sitting around, continuing to hold on to memory they don't technically "need" any more, on the premise that holding on to the memory is less costly than releasing it if you're likely to need it again.

I think it's worth a try to do the following, after first making a note of how much memory MySQL is using at the moment.

Note how many threads are currently cached:

mysql> show status like 'Threads_cached';
+----------------+-------+
| Variable_name  | Value |
+----------------+-------+
| Threads_cached | 9     |
+----------------+-------+
1 row in set (0.00 sec)

Next, disable the thread cache.

mysql> SET GLOBAL thread_cache_size = 0;

This disables the thread cache, but the cached threads will stay in the pool until they're used one more time. Disconnect from the server, then reconnect and repeat.

mysql> show status like 'Threads_cached';

Continue disconnecting, reconnecting, and checking until the counter reaches 0.

Then, see how much memory MySQL is holding.

You may see a decrease, possibly significant, and then again you may not. I tested this on one of my systems, which had 9 threads in the cache. Once those threads had all been cleared out of the cache, the total memory held by MySQL did decrease... not by much, but it does illustrate that threads in the cache do release at least some memory when they are destroyed.

If you see a significant decrease, you may have found your problem. If you don't, then there's one more thing that needs to happen, and how quickly it can happen depends on your environment.

If the theory holds that the other threads -- the ones currently servicing active client connections -- have significant memory allocated to them, either because of recent work in their current client session or because of work requiring a lot of memory that was done by another connection prior to them languishing in the pool, then you won't see all of the potential reduction in memory consumption until those threads are allowed to die and be destroyed. Presumably your application doesn't hold them forever, but how long it will take to know for sure whether there's a difference will depend on whether you have the option of cycling your application (dropping and reconnecting the client threads) or if you'll have to just wait for them to be dropped and reconnected over time on their own.

But... it seems like a worthwhile test. You should not see a substantial performance penalty by setting thread_cache_size to 0. Fortunately, thread_cache_size is a dynamic variable, so you can freely change it with the server running.

Best Answer

Related Solutions

MySQL – Troubleshooting Startup Issues After Increasing innodb_buffer_pool_size

Mysql – How to debug a db memory-leak causing thesql to go before it’s own limits

Related Question