6

I'm using MySQL 5.1 and want to upgrade to latest version 5.6.17 (from Windows Installer)

But when I installed, it used too much memory, about 420Mb although 5.1 vesion run only 8Mb memory. The setup type I choose:

  • Setup Type: Server Only

  • Config Type: Development Machine

I don't want it uses too much memory, how can I do, or what is lastest version of MySQL that use minimum memory like 5.1

I change config type to another type but It still uses than 400Mb ram

1
  • 2
    Can you paste the result of : show variables like "%buffer%"; Apr 28, 2014 at 10:02

2 Answers 2

7

I have an article on this here: http://www.tocker.ca/configuring-mysql-to-use-minimal-memory.html:

The MySQL defaults have to balance performance with what is considered reasonable for what may be a development system with other applications needing to run alongside MySQL. In many cases, this will mean 4-8GB, but on virtual machines (or in my case with 7 copies of mysqld running), there is a lot less available.

Obligatory warning: If you are running these settings on a machine with 1GB+ RAM, you should expect worse performance when compared to the defaults.

Setting                     Default      Minimum
innodb_buffer_pool_size     128M         5M
innodb_log_buffer_size      1M           256K
query_cache_size            1M           0
max_connections             151          1 (although 10 might be more reasonable)
key_buffer_size             8388608      8
thread_cache_size           (autosized)  0
host_cache_size             (autosized)  0
innodb_ft_cache_size        8000000      1600000
innodb_ft_total_cache_size  640000000    32000000
thread_stack    262144      131072
sort_buffer_size            262144       32K
read_buffer_size            131072       8200
read_rnd_buffer_size        262144       8200
max_heap_table_size         16777216     16K
tmp_table_size  16777216    1K
bulk_insert_buffer_size     8388608      0
join_buffer_size            262144       128
net_buffer_length           16384        1K
innodb_sort_buffer_size     1M           64K
binlog_cache_size           32K          4K
binlog_stmt_cache_size      32K          4K

To summarize these changes:

# /etc/my.cnf:
innodb_buffer_pool_size=5M
innodb_log_buffer_size=256K
query_cache_size=0
max_connections=10
key_buffer_size=8
thread_cache_size=0
host_cache_size=0
innodb_ft_cache_size=1600000
innodb_ft_total_cache_size=32000000

# per thread or per operation settings
thread_stack=131072
sort_buffer_size=32K
read_buffer_size=8200
read_rnd_buffer_size=8200
max_heap_table_size=16K
tmp_table_size=1K
bulk_insert_buffer_size=0
join_buffer_size=128
net_buffer_length=1K
innodb_sort_buffer_size=64K

#settings that relate to the binary log (if enabled)
binlog_cache_size=4K
binlog_stmt_cache_size=4K
2
3

Can you paste the result of

show variables like "%size%";
show variables like "%buffer%";
show variables like "%cache%";

There is one parameter doubtful for dev server in the default "Config Type: Development Machine", it's the table_definition_cache.

I'm sure that yours is set to 1400 no?

Comment it on your my.ini file or just set it to its default value : 400.

The table_definition_cache parameter is an dynamic variable, so you ca set it without restart MySQL with:

set global table_definition_cache=400;

Note that if you don't have many tables you can set this lower than 400.

Max.

1
  • 1
    The table_definition_cache is definitely the setting that lowers RAM most after you've tweaked the other obvious settings. For me, lowering table_definition_cache from 1400 to 400 reduced the MySQL process RAM usage (immediately after service start) from 500MB to 125MB. Aug 31, 2019 at 6:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.