How to set memory limit in my.cnf file

Question

How to set memory limit in my.cnf file. I tried with memory_limit = 64M . But its showing error while restarting MYSQL server. Any one please help...

my.cnf

[mysqld]
datadir=/home/mysql/
tmpdir=/home/mysqltmp
#max_connections = 175 #was 175
max_connections = 80
#max_connect_errors = 350 #was 250
max_connect_errors = 250
safe-show-database
skip-locking
key_buffer = 1024M # was 128M
max_allowed_packet = 6M
myisam_sort_buffer_size = 64M

#old settings, for 900 ish max maxconn
#sort_buffer_size = 32M
#read_buffer_size = 32M
#read_rnd_buffer_size = 32M

sort_buffer_size = 5M
read_buffer_size = 5M
read_rnd_buffer_size = 5M

query_cache_size= 1024M
query_cache_limit= 16M
max_heap_table_size = 128M
tmp_table_size = 128M
thread_concurrency = 16
wait_timeout = 10
innodb_file_per_table
innodb_log_file_size = 10485760
open_files_limit = 8192
low_priority_updates = 1 
#log_slow_queries = /var/log/mysql_slow.log
#log_queries_not_using_indexes = 1
#slow_queries_log_file = /var/log/mysql_slow.log
memory_limit = 64M 
# who set these? these are NOT memory settings, but rather integer settings.
#table_cache = 1024M
#thread_cache_size = 8M

table_cache = 512
thread_cache_size = 8

[mysqldump]
quick
max_allowed_packet = 16M

[mysql]
no-auto-rehash

[isamchk]
key_buffer = 128M
sort_buffer_size = 128M
read_buffer = 2M
write_buffer = 2M

[myisamchk]
key_buffer = 128M
sort_buffer_size = 128M
read_buffer = 2M
write_buffer = 2M

Maybe I'm wrong, but isn't `memory_limit` a PHP configuration? More: `my.cnf` is about MySql, not SQL server... — Marco, Aug 24 '12 at 06:32
@Aaron Hathaway i am getting like this `mysql has failed, please contact the sysadmin (result was "mysql is not running")`. — Juice, Aug 24 '12 at 06:33
You should check this answer which covers your question as well : http://stackoverflow.com/questions/1178736/mysql-maximum-memory-usage — strnk, Aug 24 '12 at 06:39

score 28 · Accepted Answer · answered Aug 24 '12 at 06:37

28

There is no such variables like memory_limit in MySQL my.cnf file. You can add variables only from MySQL server system variables. Read this How Mysql uses memory

It depends on RAM size of your MySQL server. You can configure your my.cnf file accordingly based on following basic formula for MySQL memory requirement calculation:

key_buffer_size + (read_buffer_size + sort_buffer_size) * max_connections = K bytes of memory

you may need to configure these basic parameters.

Sample variables from my.cnf file:

#MyISAM
key_buffer_size = 8G
sort_buffer_size = 1M
read_buffer_size = 1M
read_rnd_buffer_size = 2M
myisam_sort_buffer_size = 2M
join_buffer_size = 2M

#Innodb
innodb_buffer_pool_size = 16G
innodb_additional_mem_pool_size = 2G
innodb_log_file_size = 1G
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 30
innodb_file_format=barracuda

answered Aug 24 '12 at 06:37

Omesh

27,801
6
42
51

1

Does your server have over 16G of RAM? – Alex W Mar 31 '15 at 21:43
"K bytes of memory" or "bytes of memory"? I thought all configuration values were in bytes – Daniel Cukier Feb 02 '19 at 22:08
Another variable to add to Innodb list would be "innodb_buffer_pool_instances". It creates multiple pools in the RAM. This is from the docs (https://dev.mysql.com/doc/refman/5.6/en/innodb-multiple-buffer-pools.html): For systems with buffer pools in the multi-gigabyte range, dividing the buffer pool into separate instances can improve concurrency, by reducing contention as different threads read and write to cached pages. This feature is typically intended for systems with a buffer pool size in the multi-gigabyte range. – vijayant May 26 '20 at 08:21
1

innodb_additional_mem_pool_size is deprecated – snh_nl Jul 24 '20 at 15:50

score 1 · Answer 2 · edited Feb 23 '17 at 10:05

There is no memory_limit limit setting in MySQL. The only way you can manage or limit the memory usage of MySQL is to lower the settings for cache, buffer and pool sizes(the exact settingnames depend on what storage engine you are using. Some settings that apply for the MyISAM engine (the default engine) is:

table_cache=1024
record_buffer=1M
sort_buffer_size=2M
read_buffer_size=2M
read_rnd_buffer_size=2M
myisam_sort_buffer_size=64M
thread_cache_size=128
query_cache_limit=1M
query_cache_size=64M
query_cache_type=1

For InnoDB you have:

innodb_buffer_pool_size = 256M
innodb_additional_mem_pool_size = 20M
innodb_log_file_size = 64M
innodb_log_buffer_size = 8M

If you are looking to change the memory limit in PHP that line(memory_limit = 64M) should be in the php.ini file, not my.cnf which is the configuration file for MySQL.

How to set memory limit in my.cnf file

2 Answers2

Linked