(This is despite the fact that during installation I explicitly specified that this MySQL server is going to be used for development, not for production.)
A quick search on the web shows that this preposterous amount of memory can be reduced to something less preposterous by editing my.ini (usually found in some place like ProgramData\MySQL\MySQL Server 5.6\) and replacing the following line:
table_definition_cache=1400
with this line:
table_definition_cache=400
Unfortunately, even though the savings are huge, the memory footprint of mysql is still nothing short of gargantuan:
So, I tried to trim it down as much as possible by doing some more reading up on the subject and tweaking with more settings of my.ini, and this is what I finally managed to get it down to:
So, the "private bytes" went from 630MB down to 20MB and the "working set" went from 450MB down to 21MB. Not bad, eh?
The server is functional, too, meaning that I ran the tests of a medium-sized project, which do heavily hit the database, and they all completed as easily as they used to with the bloated out-of-the-box server.
This is the my.ini file that achieves these savings:
# MySQL Server Instance Configuration File [client] no-beep # pipe socket=0.0 port=3306 [mysql] default-character-set=utf8 # server_type=3 [mysqld] # skip-networking enable-named-pipe # shared-memory # shared-memory-base-name=MYSQL socket=MYSQL port=3306 # basedir="C:/Program Files/MySQL/MySQL Server 5.6/" datadir=C:/ProgramData/MySQL/MySQL Server 5.6/Data character-set-server=utf8 default-storage-engine=INNODB sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION" # plugin-load=authentication_windows.dll log-output=FILE general-log=0 general_log_file="PEGASUS.log" slow-query-log=1 slow_query_log_file="PEGASUS-slow.log" long_query_time=10 # log-bin log-error="PEGASUS.err" server-id=1 # *MB* original: 151 max_connections=20 query_cache_size=0 # *MB* original: table_open_cache=2000 table_open_cache=100 tmp_table_size=44M # *MB* original: 9 thread_cache_size=0 myisam_max_sort_file_size=100G # *MB* original: 80M myisam_sort_buffer_size=1M # *MB* original: 8M key_buffer_size=8 # *MB* original: read_buffer_size=64K # *MB* minimum appears to be 8K. read_buffer_size=8K # *MB* original: read_rnd_buffer_size=256K read_rnd_buffer_size=8K # *MB* original: sort_buffer_size=256K # *MB* minimum appears to be 32K sort_buffer_size=32K # innodb_data_home_dir=0.0 # skip-innodb # *MB*: If this setting is enabled, the server fails to start with message "this option is deprecated and will be removed in a future release." # *MB* original: innodb_additional_mem_pool_size=6M innodb_additional_mem_pool_size=1M # *MB* original: innodb_flush_log_at_trx_commit=1 innodb_flush_log_at_trx_commit=0 # *MB* original: innodb_log_buffer_size=4M # *MB* minimum appears to be 256K innodb_log_buffer_size=256K # *MB* original: innodb_buffer_pool_size=255M
# *MB* minimum appears to be 5M
innodb_buffer_pool_size=5M innodb_log_file_size=48M innodb_thread_concurrency=9 innodb_autoextend_increment=64 # *MB* original: innodb_buffer_pool_instances=8 innodb_buffer_pool_instances=2 # *MB* original: innodb_concurrency_tickets=5000 innodb_concurrency_tickets=10 innodb_old_blocks_time=1000 # The minimum value is 10. # *MB* original: innodb_open_files=300 innodb_open_files=10 innodb_stats_on_metadata=0 innodb_file_per_table=1 innodb_checksum_algorithm=0 # *MB* original: back_log=80 back_log=5 # *MB* original: flush_time=0 flush_time=10 # *MB* original: join_buffer_size=256K join_buffer_size=16K # *MB* original: max_allowed_packet=4M max_allowed_packet=1M max_connect_errors=100 # *MB* original: open_files_limit=4161 open_files_limit=100 # *MB* original: query_cache_type=0 query_cache_type=2 # The minimum and default values are both 400. # *MB* original: table_definition_cache=1400 table_definition_cache=400 # The value should be a multiple of 256. binlog_row_event_max_size=8K sync_master_info=10000 sync_relay_log=10000 sync_relay_log_info=10000 # additional hints from http://www.tocker.ca/2014/03/10/configuring-mysql-to-use-minimal-memory.html host_cache_size=0 thread_stack=128K max_heap_table_size=16K bulk_insert_buffer_size=0 net_buffer_length=1K innodb_sort_buffer_size=64K binlog_cache_size=4K binlog_stmt_cache_size=4K performance_schema=0
No comments:
Post a Comment