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 issues.
Why does MySQL consume high memory?
- Application software like Python, PHP, and JAVA threads utilize more memory when configured improperly or un-optimized queries, complex coding, etc.
- Improper configuration of MySQL 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’s high memory usage –
- Optimization of MySQL settings –
MySQL uses memory in two ways:
- One is the memory permanently reserved for its use –
Known as the Global Buffers category. It is obtained from the operating system during server boot-up and isn’t released to another process.
- Requested dynamic memory:
Thread Buffers are used by MySQL which is the memory that’s requested from the operating system. As when a new query is processed. After the execution of the query, this memory is released back to the operating system.
This means that the 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 done in the buffer pool for InnoDB storage. The size of the buffer pool plays a key role in system performance. 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 the case of the MYISAM storage engine. This parameter helps in determining the caching and key_buffer_size needs to be set as per the RAM. Which is approximately 20% of the RAM size.
max_connections – To allocate the number of connections possible for MySQL at any instant of time. And to avoid over-loading the server by a single user, max_connections used. Each thread uses a RAM portion for buffers’ allocation. 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 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 stored by the query cache together with the corresponding result that was sent to the client. Therefore, this parameter 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 set to a minimal one of around 10MB.
- Blocking Resource Abusers –
There a possibility of an abnormally high number of connections getting established in a short time. When a website is under attack. The PROCESSLIST in MySQL 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 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.