Mysql – Reducing memory usage of MySQL

databaseinnodbMySQLwindows-server-2008

Conclusion:

  • if you really want to use little memory out-of-the-box drop MySQL and use PostgreSQL: it has a ~30Mo memory footprint, i.e. more than 10x less than MySQL; the migration was quite easy be it the server side or application side (using NHibernate).

  • in real-life of course you will not be changing your database for saving 300Mo but as noticed in the comments you will instead buy some RAM or sign up for a bigger VM if you are hosted.

I have a new MySQL setup on a small Windows Server 2008 machine (less than 1GB RAM).

The mysqld process is consuming between 300MB and 400MB of memory causing the system to swap!

I've read this could help :

  • disabling InnoDB but I need it,
  • tuning InnoDB but my setup seems to use already low values (e.g. for innodb_buffer_pool_size).

For information here is the my.ini configuration file:

[client]
no-beep
port=3306
[mysql]
default-character-set=utf8
[mysqld]
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"
log-output=FILE
general-log=0
general_log_file="my-pc.log"
slow-query-log=1
slow_query_log_file="my-pc.log"
long_query_time=10
log-error="my-pc.err"
max_connections=100
query_cache_size=1M
table_open_cache=2000
tmp_table_size=3M
thread_cache_size=9
myisam_max_sort_file_size=100G
myisam_sort_buffer_size=5M
key_buffer_size=8M
read_buffer_size=0
read_rnd_buffer_size=0
sort_buffer_size=0
innodb_additional_mem_pool_size=2M
innodb_flush_log_at_trx_commit=1
innodb_log_buffer_size=1M
innodb_buffer_pool_size=8M
innodb_log_file_size=48M
innodb_thread_concurrency=8
innodb_autoextend_increment=64M
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=70
flush_time=0
join_buffer_size=256K
max_allowed_packet=4M
max_connect_errors=100
open_files_limit=4110
query_cache_type=1
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

This MySQL server could be really small: max 20 connections at the same time, no more than 2GB of data to store, no need for high performance…

Is there any way of reducing the memory footprint of this MySQL instance or is it already at the bare minimum?

Thanks in advance for any help.

Best Answer

The following my.ini for mysql 5.6 causes the "private bytes" to go from 630MB down to 20MB and the "working set" from 450MB down to 21MB on a Windows 7 64 bit machine. The performance of a mysql server configured like this is probably going to be very bad, but technically, if you want it to occupy as little memory as possible, then this is the answer.

# MySQL Server Instance Configuration File
[client]
no-beep
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
max_connections=20 
query_cache_size=0
table_open_cache=100
tmp_table_size=44M
thread_cache_size=0
myisam_max_sort_file_size=100G
myisam_sort_buffer_size=1M
key_buffer_size=8
read_buffer_size=8K
read_rnd_buffer_size=8K
sort_buffer_size=32K
# innodb_data_home_dir=0.0
# skip-innodb
innodb_additional_mem_pool_size=1M
innodb_flush_log_at_trx_commit=0
innodb_log_buffer_size=256K
innodb_buffer_pool_size=1M
innodb_log_file_size=48M
innodb_thread_concurrency=9
innodb_autoextend_increment=64
innodb_buffer_pool_instances=2
innodb_concurrency_tickets=10
innodb_old_blocks_time=1000
innodb_open_files=10
innodb_stats_on_metadata=0
innodb_file_per_table=1
innodb_checksum_algorithm=0
back_log=5
flush_time=10
join_buffer_size=16K
max_allowed_packet=1M
max_connect_errors=100
open_files_limit=100
query_cache_type=2
table_definition_cache=400
binlog_row_event_max_size=8K
sync_master_info=10000
sync_relay_log=10000
sync_relay_log_info=10000
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

Taken from here: michael.gr - Minimal MySQL Memory Footprint

Related Topic