2015-03-04

Minimal MySQL Memory Footprint

The following image shows the mind-boggling amount of memory occupied by MySQL 5.6 server on Windows 7 64-bit.


(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