When your system is short of memory, MySQL will be killed by the system itself since it can use a lot of memory. This also causes MySQL servers to face high server load. Usually when MySQL is killed, we get the message “mysqld invoked oom-killer”. In such cases it is always advisable to restart the entire server and not just MySQL. The flow on effects may vary from the OOM condition & thus restarting the entire server has to be done.
With MySQL being one of the most used database in applications of web, it is also easy to use & completely free of cost. This is the sole reason most hosting providers use MySQL. To prevent high memory usage in MySQL, we need to understand the causes for High memory usage. Let’s quickly look at them .
- When MySQL is not configured properly, it ends up using a lot of memory in MySQL.
- Sometimes, the processors in the server requires higher memory than the ones present in the server.
- Coding Complexities, Un-optimized queries, and not configuring the application softwares like PHP, Java & Python properly causes high memory usage in MySQL.
What should be done?
I. Steps to optimize MySQL settings:
- Global Buffers is the memory that is permanently reserved for its use. During server boot-up, this memory is obtained from the operating system & is not released into another process.
- Thread buffers is the memory that is requested as and when a query is processed. It is released back once the query is executed.
Total memory usage in MySQL = Thread Buffers + Global Buffers + Allowed Connections
For dedicated database server, the value needs to be below 90% of the server memory. For shared server, the value needs to be below 50% of the server memory.
Innodb_buffer_pool_size — The size of the buffer pool plays a crucial role in the performance of the system & is assigned a value 50-70% of the RAM available. Either way, if it is too small, flushing of pages happen excessively & if large, swapping happens due to high competition for memory.
key_buffer_size – This factor helps in identifying the caching and key_buffer_size needs to be focused as per the RAM that is around 20% of RAM size.
Max_connections — It is used for the following
- To prevent a single user from overloading a server.
- To restrict the no. of connections at a given time
Formula: max_connections = (Available RAM – Global Buffers) / Thread Buffers
Query_cache_size: Used when you have tables that do not change often & for identical queries. It stores the text of the SELECT together with the corresponding result. This parameter is mostly used for application servers. Otherwise it is set to zero.
II. Upgrade your RAM & slow queries need to be taken care of:
You need to upgrade the speed of the RAM, if it retorts to swapping memory even after optimizing the database settings. Optimization is needed for queries that take a long time to execute. Slow queries result in high memory usage & once this is fixed, the server performance would be significantly increased.
III. Use the PROCESSLIST:
Identify the top-users & prevent access to abusive users using PROCESSLIST.
If you have any problems with your server management, you can always get in touch with us. Our experience and expertise in Linux / Windows server management keeps your IT infrastructure intact 24×7 and enables robust web presence for your enterprise, thus helping you save time, space and ultimately money.
To know more, you can visit: https://www.actsupport.com/server-management
