Release MySQL Memory and Performance Tunning

Question

I am new to the MySQL database. I have recently installed MySQL 5.7 and configured the my.ini file. Please find the below copy of the file.

The server is a dedicated server for MySQL database and the configuration of the server is:

• OS = Windows 8.1 (64 bit),
• RAM = 16GB (this can be automatically extended up to 32GB),
• Processor = Intel Xenon 2.30 GHz (4 processors),
• Size of the MySQL database = approx. 100GB,
• Concurrent connections = Maximum up to 100

My questions are:

1. I have initially allocated 12GB RAM to Innodb buffer pool. I can allocate up to 24GB to Innodb buffer pool, so that, MySQL can use extra memory in case of heavy load. I want MySQL to release extra memory after completion of query executions/processes. Is there any way we can do it?

2. Do we need to make any changes in the my.ini file if any variable(s) are configured wrongly and also to improve the performance of the database?

my.ini file:

character-set-server=utf8, 
default-storage-engine=INNODB, 
sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION", 
max_connections=301, 
query_cache_size=0, 
table_open_cache=2000, 
tmp_table_size=333M, 
thread_cache_size=10, 
myisam_max_sort_file_size=100G, 
myisam_sort_buffer_size=4G, 
key_buffer_size=8M, 
read_buffer_size=64K, 
read_rnd_buffer_size=256K, 
innodb_flush_log_at_trx_commit=1, 
innodb_log_buffer_size=16M, 
innodb_buffer_pool_size=12G, 
innodb_io_capacity = 2000, 
innodb_read_io_threads = 64, 
innodb_log_file_size=128M, 
innodb_thread_concurrency=0, 
innodb_write_io_threads = 64, 
innodb_autoextend_increment=64, 
innodb_buffer_pool_instances=8, 
innodb_concurrency_tickets=5000, 
innodb_old_blocks_time=1000, 
innodb_open_files=300, 
innodb_stats_on_metadata=0, 
innodb_file_per_table=1, 
innodb_checksum_algorithm=0, 
back_log=80, 
flush_time=0, 
join_buffer_size=256K, 
max_allowed_packet=4M, 
max_connect_errors=100, 
open_files_limit=4161, 
query_cache_type=0, 
sort_buffer_size=256K, 
table_definition_cache=1400, 
binlog_row_event_max_size=8K, 
sync_master_info=10000, 
sync_relay_log=10000, 
sync_relay_log_info=10000, 
explicit_defaults_for_timestamp=1, 
innodb_lock_wait_timeout=120, 
transaction-isolation=READ-COMMITTED, 
innodb_support_xa=0, 
autocommit=1, 
event_scheduler=ON

---

Update:

We are on the Cloud Network. The default RAM on the server is 16 GB. There is a facility to increase (up to 32 GB RAM) or decrease the RAM automatically based on OS load.

For example, if I allocate 20GB RAM to INNODB_BUFFER_POOL_SIZE then MySQL server can use the memory up to 20GB during the heavy load/pick hours. However, I want MySQL to release the memory up to 16GB during the non-pick hours. So that, we cannot charged for 20GB RAM (i.e. for extra 4GB RAM during non-pick hours).

Answer 1

MySQL will not release global buffers after they are used (innodb_buffer_pool, key_buffer_size, query_cache_size, etc). It will release memory taken by per-thread buffers (join_buffer_size, sort_buffer_size, etc) but only after the thread is removed and not in the thread_cache.

The innodb_buffer_pool_size can be tuned dynamically in 5.7, but it is a very invasive operation:

When initiating a resizing operation, the operation does not start until all active transactions are completed. Once the resizing operation is in progress, new transactions and operations that require access to the buffer pool must wait until the resizing operation finishes. The exception to this rule is that concurrent access to the buffer pool is permitted while the buffer pool is defragmented and pages are withdrawn during an operation to decrease buffer pool size. A drawback of allowing concurrent access is that it could result in a temporary shortage of available pages while pages are being withdrawn.

Is the additional buffer pool worth it? This query will give you an idea of the current maximum memory that will be used by the InnoDB buffer pool:

SELECT ENGINE, SUM(DATA_LENGTH)/1024/1024/1024 as DATA_LENGTH_IN_GB,  
    SUM(INDEX_LENGTH)/1024/1024/1024 AS INDEX_LENGTH_IN_GB, 
    SUM(DATA_LENGTH+INDEX_LENGTH)/1024/1024/1024 as TOTAL_IN_GB
FROM information_schema.TABLES
WHERE ENGINE IN ("InnoDB") 
    AND TABLE_SCHEMA NOT IN ('mysql', 'information_schema', 'performance_schema')
GROUP BY ENGINE
ORDER BY DATA_LENGTH DESC;

However, that is the total InnoDB size of data+indexes. The working set is what is going to matter during peak times, so this will include your most active tables/rows. If your working set already fits in memory, allocating more is not going to buy much for you. If it doesn't fit in memory, you're going to want to increase the buffer pool regardless of peak/non-peak traffic.

I would recommend reading the internal details section of the MySQL documentation of the InnoDB Buffer Pool as a great primer.

As for the rest of your configuration, the only thing I will say is that your temp_table_size=333M is not doing what you think it is, and is likely too high.

First:

The maximum size for in-memory temporary tables is the minimum of the tmp_table_size and max_heap_table_size values.

This means, with max_heap_table_size defaulting to 16MB, the internal memory tables are going to be converted to on disk temporary files long before reaching 330MB.

Second, you might have increased temp_table_size to avoid temporary on disk table usage. But, there are scenarios where MySQL can never use in-memory temporary tables:

Some conditions prevent the use of an in-memory temporary table, in which case the server uses an on-disk table instead:

• Presence of a BLOB or TEXT column in the table

• Presence of any string column in a GROUP BY or DISTINCT clause larger than 512 bytes for binary strings or 512 characters for nonbinary strings. (Before MySQL 5.7.3, the limit is 512 bytes regardless of string type.)

• Presence of any string column with a maximum length larger than 512 (bytes for binary strings, characters for nonbinary strings) in the SELECT list, if UNION or UNION ALL is used

• The SHOW COLUMNS and the DESCRIBE statements use BLOB as the type for some columns, thus the temporary table used for the results is an on-disk table.

I would recommend setting temp_table_size and max_heap_table_size to 32M or 64M (max) and then examining queries for some of the conditions mentioned above. If you can't avoid on-disk temporary tables, hopefully your cloud provider puts the tmpdir on SSD.

Answer 2

As others have answered your first question, I will answer the second:

Do we need to make any changes in the my.ini file if any variable(s) are configured wrongly and also to improve the performance of the database?

In my opinion you are changing too many settings without evidence of benefit. In some cases you are also setting values to the default, which is fine - but it may be beneficial to just inherit the default so that if it changes you can take advantage when upgrading.

Specifics:

• tmp_table_size=333M looks too high (default is better)
• thread_cache_size=10 looks too low (default is better)
• innodb_checksum_algorithm=0 is not completely safe
• innodb_lock_wait_timeout=120 is higher than the default. Most people change this to lower than the default.
• innodb_support_xa=0 is deprecated and should not be used in MySQL 5.6+