Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

Tags:

php

mysql

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
like image 313
Juice Avatar asked Aug 24 '12 06:08

Juice


People also ask

Does MySQL have a memory limit?

This will give the Recommended Setting for MyISAM Key Cache (key_buffer_size) given your current data set (the query will cap the recommendation at 4G (4096M). For 32-bit OS, 4GB is the limit. For 64-bit, 8GB.

Why MySQL memory usage keeps increasing?

So as a database engine starts up, it'll use a moderate amount of memory. (Some will allocate a "minimum memory" just to have it ready for when they need it.) Then, as it runs, the memory usage will gradually increase, until it reaches either: an amount configured as the "maximum memory" within the DBMS, or.


2 Answers

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
like image 200
Omesh Avatar answered Oct 02 '22 20:10

Omesh


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.

like image 29
Pelmered Avatar answered Oct 02 '22 19:10

Pelmered