Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.
Sign up to join this community
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
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 4KTo 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
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.
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