HostingRaja provide various types of hosting services including shared, VPS, cloud and Dedicated Servers. For each of the hosting service provides it facilitates various database options one of which is MySQL. When the memory usage exceeds the predefined values it experience the issues discussed below.
What is MySQL?
MySQL is the most popular database used in web applications. It’s free, easy to manage 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?
1) Website applications or software like PHP, HTML, Python and Java consuming more Memory due to un-optimized coding, improper configuration, complex coding etc.
2) Due to improper configuration causes insufficiency memory comsuption.
3) Less Memory required by the processes on the server.
In the Linux VPS the logs for each is provided with which you can come to know about the total resources spent. You can optimise your code to run out of the resources exceeded issue easily. If the issue can not be rectified from your end, you can always approach our support team.
Resolutions for MySQL high memory usage
1) MySQL setting Optimization
MySQL uses memory in two ways:
a) Global Buffers Memory permanently reserved – The ‘Global Buffers’ obtained from the operating system during server boot-up.
b) Thread Buffers Memory which is requested dynamically based on requests – MySQL uses “Thread Buffers“, which is memory requested from the operating system as and when a new query is processed. Once the query is executed, this memory is released back to the operating system.
innodb_buffer_pool_size – For InnoDB storage engine, caching is done in the buffer pool. The buffer pool size is important for system performance in MySQL and is assigned an exact value that is between 60-70% of available RAM.
key_buffer_size – For MyISAM storage engine, this parameter determines the caching and key_buffer_size has to be set according to the RAM, which is around 20% of RAM size.
max_connections – The max_connections is to the limit the number of connections possible for MySQL at any instance of time, to avoid a single user from overloading the server, max_connections is used. Each MySQL thread uses a portion of the RAM for allotting its buffers and hence limiting the maximum no of MySQL connections based on the RAM size is important.
Approx formula, max_connections = (Available RAM – Global Buffers) / Thread Buffers
query_cache_size – The query cache can be useful in an environment where you have tables that do not change very often and for which the web server receives many identical queries such as a blog or record lookup. The value is set a minimal one of around 10MB.
2) 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 Admin can be used to detect the top users and block access to abusive connections.
3) 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 mysql queries, leading to high memory and CPU usage, affecting the server performance.
4) Upgrading RAM
After optimizing tables settings, the server constantly routes to using swap memory, it’s necessary that you increase the RAM.
HostingRaja facilitates various types of web application to be hosted on it like Magento, Joomla, Wordpress and more. The issue can be occured in any one of them. Bringing awareness of the causes of the same could help you run out of this situation.