Parameters of MySQL memory usage
There is only 2GB of memory available on my server, and I noticed that MySQL is occupying nearly 2.8GB, which is more than the available memory.
+------------------------------------------+--------------------+
| key_buffer_size | 8.000 MB |
| query_cache_size | 0.000 MB |
| innodb_buffer_pool_size | 128.000 MB |
| innodb_additional_mem_pool_size | 0.000 MB |
| innodb_log_buffer_size | 16.000 MB |
+------------------------------------------+--------------------+
| BASE MEMORY | 152.000 MB |
+------------------------------------------+--------------------+
| sort_buffer_size | 0.250 MB |
| read_buffer_size | 0.125 MB |
| read_rnd_buffer_size | 0.250 MB |
| join_buffer_size | 0.250 MB |
| thread_stack | 1.000 MB |
| binlog_cache_size | 0.031 MB |
| tmp_table_size | 16.000 MB |
+------------------------------------------+--------------------+
| MEMORY PER CONNECTION | 17.906 MB |
+------------------------------------------+--------------------+
| Max_used_connections | 2 |
| max_connections | 151 |
+------------------------------------------+--------------------+
| TOTAL (MIN) | 187.812 MB |
| TOTAL (MAX) | 2855.844 MB |
+------------------------------------------+--------------------+Find the config file of MySQL and reduce the sizes accordingly. In my server, it is under /etc/my.cnf.d
vi /etc/my.cnf.d/mysql-memory.cnf
````mysql-memory.cnf
[mysqld]
key_buffer_size = 16M
read_buffer_size = 60K
sort_buffer_size = 1M
innodb_buffer_pool_size = 64M
tmp_table_size = 8M
max_allowed_packet = 16M
thread_stack = 192K
thread_cache_size = 8
max_connections = 25
```Print the MySQL memory consumption again. The consumption decreased significantly.
+------------------------------------------+--------------------+
| key_buffer_size | 16.000 MB |
| query_cache_size | 0.000 MB |
| innodb_buffer_pool_size | 64.000 MB |
| innodb_additional_mem_pool_size | 0.000 MB |
| innodb_log_buffer_size | 16.000 MB |
+------------------------------------------+--------------------+
| BASE MEMORY | 96.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.250 MB |
| thread_stack | 0.188 MB |
| binlog_cache_size | 0.031 MB |
| tmp_table_size | 8.000 MB |
+------------------------------------------+--------------------+
| MEMORY PER CONNECTION | 9.777 MB |
+------------------------------------------+--------------------+
| Max_used_connections | 1 |
| max_connections | 25 |
+------------------------------------------+--------------------+
| TOTAL (MIN) | 105.777 MB |
| TOTAL (MAX) | 340.434 MB |
+------------------------------------------+--------------------+The scripts to print MySQL memory consumption:
#!/bin/sh
# Prompt for MySQL authentication
read -p "Enter MySQL username: " MYSQL_USER
read -s -p "Enter MySQL password: " MYSQL_PASSWORD
echo
# Execute MySQL queries with authentication
mysql -u "$MYSQL_USER" -p"$MYSQL_PASSWORD" -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_CONN
printf "+------------------------------------------+--------------------+\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"
}'Useful commands
top -o RES
```
This will display the processes sorted by the memory usage (RES field).ps aux --sort -rss
```
This will list all processes sorted by the resident set size (RSS).top -o %CPU
```
This will launch the top command and sort the processes by their CPU usage in descending order.free -h
df -h
```
Human readable memory and disk consumption