Well, one does not simply tells MySQL how much memory it should use.
The maximum memory usage comes from a lot of different settings, mostly buffer sizes but it appears that documentations don’t clearly state how to find or set this value.
You’ll have to read their post about memory usage, which I greatly recommend anyway.
Hopefully, a good-hearted man posted a very usefull shell script as a comment on this post (that you will find at the bottom of this document).
This script prints an approximation of MySQL’s potential memory usage based on the buffers and caches memory allocation.
Let’s not wait longer:
+------------------------------------------+--------------------+
| TOTAL (MIN) | 194.719 MB |
| TOTAL (MAX) | 3002.531 MB |
+------------------------------------------+--------------------+
This is the result of the script when testing on my own environment (just a part of it, the rest will be seen later).
The TOTAL (MAX) is the maximum memory MySQK can use with the current configuration.
With its default configuration, MySQL can potentially use 3GB of my server’s memory.
But what if I have only 500MB to allow ?
We’re going to see the 3 steps to reduce the potential memory usage to something more realistic.
In my case, the aim is about 350MB, what corresponds to 70% of my server’s total memory.
Lets get our hands dirty
1) Identify the parameters you will want to modify in order to restrict MySQL memory usage
Launch the script that you can find at the bottom of this tutorial.
Here is an example of display:
+------------------------------------------+--------------------+
| key_buffer_size | 16.000 MB |
| query_cache_size | 16.000 MB |
| innodb_buffer_pool_size | 128.000 MB |
| innodb_additional_mem_pool_size | 8.000 MB |
| innodb_log_buffer_size | 8.000 MB |
+------------------------------------------+--------------------+
| BASE MEMORY | 176.000 MB |
+------------------------------------------+--------------------+
| sort_buffer_size | 2.000 MB |
| read_buffer_size | 0.125 MB |
| read_rnd_buffer_size | 0.250 MB |
| join_buffer_size | 0.125 MB |
| thread_stack | 0.188 MB |
| binlog_cache_size | 0.031 MB |
| tmp_table_size | 16.000 MB |
+------------------------------------------+--------------------+
| MEMORY PER CONNECTION | 18.719 MB |
+------------------------------------------+--------------------+
| Max_used_connections | 1 |
| max_connections | 151 |
+------------------------------------------+--------------------+
| TOTAL (MIN) | 194.719 MB |
| TOTAL (MAX) | 3002.531 MB |
+------------------------------------------+--------------------+
The two things we can directly or indirectly influence are:
— the memory per connection — the maximum number of concurrent connections
You will want to read a bit about what each setting does in order to adapt your changes to your needs.
Just as a hint, these are the values I considered with the most attention:
— innodbbufferpoolsize — sortbuffersize — readbuffersize — tmptablesize — maxconnections
Once you figure out what you can gain memory on, go on to the next step.
2) Locate your my.cnf file
This is the file where you will overwrite mysql options. It is usually located in the /etc/mysql folder.
Here is what your my.cnf file should look like if you didn’t modify it
#
# * Fine Tuning
#
key_buffer = 16M
max_allowed_packet = 16M
thread_stack = 192K
thread_cache_size = 8
# This replaces the startup script and checks MyISAM tables if needed
# the first time they are touched
myisam-recover = BACKUP
# max_connections = 100
3) Make your changes
We will need to modify and add a few lines Here is what it looks like after some modifications.
I advice not to move the key_buffer size as it is mostly used in many different operations.
#
# * Fine Tuning
#
key_buffer = 16M
read_buffer = 60K
sort_buffer = 1M
innodb_buffer_pool_size = 64M
tmp_table = 8M
max_allowed_packet = 16M
thread_stack = 192K
thread_cache_size = 8
# This replaces the startup script and checks MyISAM tables if needed
# the first time they are touched
myisam-recover = BACKUP
max_connections = 25
And voila.
If I launch the script again:
$ sh print-mem.sh
+------------------------------------------+--------------------+
| key_buffer_size | 16.000 MB |
| query_cache_size | 16.000 MB |
| innodb_buffer_pool_size | 64.000 MB |
| innodb_additional_mem_pool_size | 8.000 MB |
| innodb_log_buffer_size | 8.000 MB |
+------------------------------------------+--------------------+
| BASE MEMORY | 112.000 MB |
+------------------------------------------+--------------------+
| sort_buffer_size | 1.000 MB |
| read_buffer_size | 0.059 MB |
| read_rnd_buffer_size | 0.250 MB |
| join_buffer_size | 0.125 MB |
| thread_stack | 0.188 MB |
| binlog_cache_size | 0.031 MB |
| tmp_table_size | 8.000 MB |
+------------------------------------------+--------------------+
| MEMORY PER CONNECTION | 9.652 MB |
+------------------------------------------+--------------------+
| Max_used_connections | 1 |
| max_connections | 25 |
+------------------------------------------+--------------------+
| TOTAL (MIN) | 121.652 MB |
| TOTAL (MAX) | 353.309 MB |
+------------------------------------------+--------------------+
With these simple steps, the potential memory usage has been reduced from 3GB to 350MB.
Once again, you should adapt your changes to something that fits you needs.
The script
(credits to Eduardo Franceschi)
#!/bin/sh# you might want to add some user authentication here
mysql -e "show variables; show status" | awk '
{
VAR[$1]=$2
}
END {
MAX_CONN = VAR["max_connections"]
MAX_USED_CONN = VAR["Max_used_connections"]
BASE_MEM=VAR["key_buffer_size"] + VAR["query_cache_size"] + VAR["innodb_buffer_pool_size"] + VAR["innodb_additional_mem_pool_size"] + VAR["innodb_log_buffer_size"]
MEM_PER_CONN=VAR["read_buffer_size"] + VAR["read_rnd_buffer_size"] + VAR["sort_buffer_size"] + VAR["join_buffer_size"] + VAR["binlog_cache_size"] + VAR["thread_stack"] + VAR["tmp_table_size"]
MEM_TOTAL_MIN=BASE_MEM + MEM_PER_CONN*MAX_USED_CONN
MEM_TOTAL_MAX=BASE_MEM + MEM_PER_CONN*MAX_CONNprintf "+------------------------------------------+--------------------+\n"
printf "| %40s | %15.3f MB |\n", "key_buffer_size", VAR["key_buffer_size"]/1048576
printf "| %40s | %15.3f MB |\n", "query_cache_size", VAR["query_cache_size"]/1048576
printf "| %40s | %15.3f MB |\n", "innodb_buffer_pool_size", VAR["innodb_buffer_pool_size"]/1048576
printf "| %40s | %15.3f MB |\n", "innodb_additional_mem_pool_size", VAR["innodb_additional_mem_pool_size"]/1048576
printf "| %40s | %15.3f MB |\n", "innodb_log_buffer_size", VAR["innodb_log_buffer_size"]/1048576
printf "+------------------------------------------+--------------------+\n"
printf "| %40s | %15.3f MB |\n", "BASE MEMORY", BASE_MEM/1048576
printf "+------------------------------------------+--------------------+\n"
printf "| %40s | %15.3f MB |\n", "sort_buffer_size", VAR["sort_buffer_size"]/1048576
printf "| %40s | %15.3f MB |\n", "read_buffer_size", VAR["read_buffer_size"]/1048576
printf "| %40s | %15.3f MB |\n", "read_rnd_buffer_size", VAR["read_rnd_buffer_size"]/1048576
printf "| %40s | %15.3f MB |\n", "join_buffer_size", VAR["join_buffer_size"]/1048576
printf "| %40s | %15.3f MB |\n", "thread_stack", VAR["thread_stack"]/1048576
printf "| %40s | %15.3f MB |\n", "binlog_cache_size", VAR["binlog_cache_size"]/1048576
printf "| %40s | %15.3f MB |\n", "tmp_table_size", VAR["tmp_table_size"]/1048576
printf "+------------------------------------------+--------------------+\n"
printf "| %40s | %15.3f MB |\n", "MEMORY PER CONNECTION", MEM_PER_CONN/1048576
printf "+------------------------------------------+--------------------+\n"
printf "| %40s | %18d |\n", "Max_used_connections", MAX_USED_CONN
printf "| %40s | %18d |\n", "max_connections", MAX_CONN
printf "+------------------------------------------+--------------------+\n"
printf "| %40s | %15.3f MB |\n", "TOTAL (MIN)", MEM_TOTAL_MIN/1048576
printf "| %40s | %15.3f MB |\n", "TOTAL (MAX)", MEM_TOTAL_MAX/1048576
printf "+------------------------------------------+--------------------+\n"
}'