How to set memory limit in my.cnf file [closed]

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
– Marco, Commented Aug 24, 2012 at 6:32
@Aaron Hathaway i am getting like this mysql has failed, please contact the sysadmin (result was "mysql is not running"). — Juice
– Juice, Commented Aug 24, 2012 at 6:33
You should check this answer which covers your question as well : stackoverflow.com/questions/1178736/mysql-maximum-memory-usage — strnk
– strnk, Commented Aug 24, 2012 at 6:39

Omesh · Accepted Answer · 2012-08-24 06:37:02Z

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

"K bytes of memory" or "bytes of memory"? I thought all configuration values were in bytes
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 (dev.mysql.com/doc/refman/5.6/en/…): 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.

RobIII · Accepted Answer · 2017-02-23 10:05:26Z

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.

Collectives™ on Stack Overflow

How to set memory limit in my.cnf file [closed]

2 Answers 2

4 Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Linked

Related