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:
-
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?
-
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).
Best Answer
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 thethread_cache
.The
innodb_buffer_pool_size
can be tuned dynamically in 5.7, but it is a very invasive operation: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:
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:
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:I would recommend setting
temp_table_size
andmax_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 thetmpdir
on SSD.