What is MySQL?
MySQL is the most popular database used in web applications. It’s free, easy to administer and is supported by all hosting providers. There’s a common issue that MySQL servers face and that is high server load due to high memory issue.
Why does MySQL consume high memory?
- Application software like Python, PHP, JAVA threads utilize more memory when configured improperly or un-optimized queries, complex coding, etc.
- Due to improper configuration of MySQL, it leads to inefficient memory or wastage of memory.
- If the memory is lower than, required by the processes on the server.
Resolutions to fix MySQL high memory usage –
- Optimization of MySQL settings –
MySQL uses memory in two ways:
- One is the memory permanently reserved for its use –
Known as Global Buffers category, it is obtained from the operating system during server boot-up and isn’t released to another process.
- Requested dynamically memory:
Thread Buffers is used by MySQL which is the memory that’s requested from the operating system as and when a new query is processed. After the execution of the query, this memory is released back to the operating system.
This means that memory usage of MySQL is Global Buffers plus the Thread Buffers along with the maximum number of allowed connections.
This value needs to be kept below 90% of the server memory for a dedicated database server. In a case of a shared server, it should be kept below 50% of the server memory.
Check some MySQL settings that help in determining the memory usage which will help you to decide its size.
innodb_buffer_pool_size – Caching is done in the buffer pool for InnoDB storage. The size of the buffer pool plays a key role in system performance and is assigned a value between 50-70 % of the RAM available. If the pool size is too small, it will cause excessive flushing of pages and if it’s too large, it will lead to swapping due to competition for memory.
key_buffer_size – In a case of MYISAM storage engine, this parameter helps in determining the caching and key_buffer_size needs to be set as per the RAM that is approximately 20% of RAM size.
max_connections – To allocate the number of connections possible for MySQL at any instant of time and to avoid over-loading server by a single user, max_connections is used. Each thread uses a RAM’s portion for buffers’ allocation and hence it limits the maximum number of connections based on the size of the RAM.
Approx formula, max_connections = (Available RAM – Global Buffers) / Thread Buffers
query_cache_size – When tables that don’t change very often and for which the web server receives many identical queries like a record lookup or a blog, the query cache can be useful. The text of a SELECT statement is stored by the query cache together with the corresponding result that was sent to the client. Therefore, this parameter is only used for such application servers or otherwise disabled and set to zero for other servers. In order to avoid resource contention, though it’s enabled, the value is set to minimal one of around 10MB.
- Blocking Resource Abusers –
There’s a possibility of an abnormally high number of connections getting established in short time, when a website is under attack. The PROCESSLIST in MySQL can be used to detect the top users and block access to abusive connections.
- Fixing Slow Queries –
Find which queries take a long time to execute as these require further optimization for the servers to perform better and are identifiable through the server-query log. More disk reads are caused due to slow queries, leading to high memory and CPU usage, affecting the server performance.
- Upgrading RAM –
Though after optimizing database settings, the server constantly routes to using swap memory, it’s essential that you increase the RAM.