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?
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
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).