How to increase MySQL memory limit?

The question – I have a lot of RAM on my machine. How can I increase the memory limits used by MySQL so that MySQL uses more memory and less virtual memory?

There are two MySQL variables that can be used to increase the memory limit. One is called key_buffer_size and the other is called query_cache_size. By increasing both of these variables, you will instruct MySQL to cache more indexes and records in RAM, thereby increasing its speed and performance. By increasing both, you will also reduce the number of major page faults that mysqld encounters.

key_buffer_size – This is the amount of memory used to cache frequently used indexes. This cache is global and accessed by all MySQL clients. The maximum allowed size is 4 GB on a 32 bit machine. MySQL recommends this value to be 25% of the total available RAM on a machine. On my Linux CentOS box, I have 2 GB of memory and I use a value of 256 mega bytes which is slightly conservative, but I have a lot of traffic which needs memory as well.

query_cache_size – This is the amount of memory that MySQL uses to cache the complete result sets of frequently used queries. The default value is 0 which means that query caching is disabled. On my Linux machine, I use a value of 16 MB which is a little less, but works fine in my case.

How to check memory limits of MySQL?

To check the values of the memory limit variables in MySQL, you can use either of the following three options:

a) Using the mysql command line admin tool

mysql -u databaseusername -p
This will give you a mysql command prompt.

To check the query cache size, use the following mysql session:

mysql> SHOW VARIABLES LIKE ‘query_cache_size’;
+——————+———-+
| Variable_name | Value |
+——————+———-+
| query_cache_size | 16777216 |
+——————+———-+
1 row in set (0.02 sec)
mysql>
To check the key_buffer_size, use the following syntax:

mysql> SHOW VARIABLES LIKE ‘key_buffer_size’;
+—————–+———–+
| Variable_name | Value |
+—————–+———–+
| key_buffer_size | 268435456 |
+—————–+———–+
1 row in set (0.00 sec)

mysql>
So this shows that I have 256 MB of key_buffer_size and 16 MB of query_cache_size.

b) Using the command line mysqladmin tool

mysqladmin -u databaseusername -p variables | grep key_buffer_size
AND

mysqladmin -u databaseusername -p variables | grep query_cache_size
This outputs:

| key_buffer_size | 268435456
| query_cache_size | 16777216
c) Using my.cnf

[~]# grep -i query_cache_size /etc/my.cnf
query_cache_size=16M
[~]#
[~]# grep -i key_buffer_size /etc/my.cnf
key_buffer_size=256M
[~]#
How to increase the memory limits of MySQL

To increase the memory limits of MySQL, you can chose to increase query_cache_size, or the key_buffer_size or both.

To increase either, first locate your my.cnf file:

root@server [/etc]# mysql –help | grep my.cnf
/etc/mysql/my.cnf /etc/my.cnf ~/.my.cnf
root@server [/etc]#
On my Linux box, my.cnf is located at /etc/my.cnf.

Open the file using your favorite editor:

vi /etc/my.cnf
Then add or change the following lines:

query_cache_size=16M
key_buffer_size=256M
Note that it is recommended that key_buffer_size be less than the 25% of available memory. Also, choose the query_cache_size wisely based on your available memory.

How to check free memory on a Linux box

To check free memory on your machine, use the free command:

free -mt
This outputs something like the following:

root@server [/etc]# free -mt
total used free shared buffers cached
Mem: 2048 2045 2 0 0 1431
-/+ buffers/cache: 614 1433
Swap: 2048 680 1367
Total: 4096 2725 1370
root@server [/etc]#
You should increase the query_cache_size or the key_buffer_size only if you have free memory that is unused. Note that the cached column in the free output above is considered free memory.

So play with the memory limits of MySQL and report your findings using the comments section below!