How to reduce mysql memory used

Question

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

Answer 1

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

Answer 2

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.