I am trying to get a deep insight on how memory usage works in MySQL. My problem is related to Azure Database for MySQL which is a managed service by Microsoft. So unfortunately I can not reach the VM directly which hosts the MySQL server. This MySQL instance has 4vCores and 20GB of RAM, 400GB storage, version is 5.7.32. Only InnoDB storage engine is supported here.
I attach the global_variables, memoryinstruments (unfortunately just a few of them are enabled, and I can not enable any more because this is a managed MySQL service and I have no privilage for that), processlist, global_status, and innodb_status.
What I do not understand:
As I mentioned we have 20GB RAM. We only use 6GB for inno_db_buffer_pool (this buffer pool is full, of course). As you can see in the processlist we have 200-300 connections and almost all of them are idle. As you can see on the chart of the connections we have all the time 200-300 connections. Whenever I check the processlist I can see that most of the connections are idle.
When MySQL starts it allocates memory for the global buffers, but as far as I know the memory usage of each connections are adhoc. Meaning, after there is no activity on a given thread, the thread goes IDLE and the memory is released.
On the memory chart we can see that the memory usage is 90% all the time, maybe some kind of memory leak? This chart is provided by Micrososft and they have the following explanation about the chart. Let me quote them:
Would like to highlight about Azure database for MySQL Memory Consumptions comes from :
- MySQL Innodb buffer pool: this is expand with workload but will never shrink, which is normal behavior for MySQL Database.
- MySQL Memory tables and connections: those are adhoc memory will be released when the MySQL server don’t have connections or workload.
We have two different memory sets in Azure database for MySQL:
- ‘working set memory’: which is the memory usage you can observe on the chart and it stands for the actual usage for client activities, e.g. queries, connections, etc.
- ‘committed size memory’: which represents the memory portion an application (e.g. MySQL server) asked for OS to reserve including buffer pool size and memory consumed by other processes . Please note that this committed size could be either used fully or never touched by applications (e.g. MySQL) at all.
Noted that ‘working set memory’ does not include innodb_buffer_pool_free and innodb_buffer_pool_dirty, when server run out of memory (Committed size). There can be a free memory space in buffer pool but this part is already reserved by buffer pool and cannot be used for other purposes.
I am a new user here due to this the forum does not allow me to upload attachments. I would be happy to share with you directly.
Chart of the connections: