Bobcares

How to fix MySQL high memory usage

by | Mar 25, 2016

MySQL is the most popular database used in web applications. It is supported by all hosting providers, is easy to administer, and free. However, MySQL servers often face high server load due to high memory usage.

What causes MySQL high memory usage?

Before we discuss how to fix MySQL high memory usage errors, let’s take a look at what causes such situations.

  1. Application software such as PHP, Python, Java threads taking up more memory due to improper configuration, unoptimized queries, complex coding, etc.
  2. Improper MySQL server configuration causing inefficient allocation or wastage of memory.
  3. Low memory availability in the server than required by the processes

Lack of memory causing server crash?

CLICK HERE FOR AN INSTANT FIX!

Fixing MySQL high memory usage

Fixes for MySQL High Memory usage

MySQL High Memory usage

Depending on what’s causing the memory bottleneck, the solution can vary. Here are the top resolutions for MySQL high memory usage.

1. MySQL settings optimization

MySQL uses memory in two ways:

  1. Memory permanently reserved for its use – This category of memory known as “Global Buffers” is obtained from the operating system during server boot-up and is not released to any other process.
  2. 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.

So, you can say the memory usage of MySQL is “Global Buffers + (Thread Buffers x maximum number of allowed connections)”.

This value should always be kept below 90% of server memory for a dedicated database server. If it is a shared server, it should be kept below 50%. Here are a few of the common MySQL settings that determines memory usage, and how you can decide it’s size.

  • innodb_buffer_pool_size – For InnoDB storage engine, caching is done in the buffer pool. The size of the buffer pool is important for system performance and is assigned a value that is between 50-70% of available RAM. Too small pool size can cause excessive flushing of pages and too large size can cause swapping due to competition for memory.
  • 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 – To limit the number of connections possible for MySQL at any instant of time, to avoid a single user from overloading the server, max_connections is used. Each thread uses a portion of the RAM for allotting its buffers and hence limiting the maximum no of 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 query cache stores the text of a SELECT statement together with the corresponding result that was sent to the client. So this parameter is used only for such application servers or otherwise disabled and set to 0 for other servers. To avoid resource contention, even if it is enabled, the value is set a minimal one of around 10MB.

2. Block resource abusers

When a website is under attack (like DoS, comment spamming, etc.), an abnormally high number of connections could be established in a short time. Use the “PROCESSLIST” in MySQL to identify the top users, and block access to the abusive connections.

[ Use your time to build your business. We’ll take care of your servers. Grab our Emergency server services at affordable pricing. ]

3. Fix slow queries

Identify queries that take a long time to execute – these queries require further optimization for better server performance and are identified from the slow-query log. Slow queries would cause more disk reads which require more memory and CPU usage, which in turn affects the server performance.

[ You don’t have to lose your sleep over high memory usage. Our expert server specialists are here to keep your MySQL server stable]

4. Upgrade RAM

If the server constantly resorts to using swap memory, even after optimizing the database settings, you might need to increase the server RAM.

Bobcares helps business websites of all sizes achieve world-class performance and uptime, using tried and tested website architectures. If you’d like to know how to make your website more reliable, we’d be happy to talk to you.

 

TROUBLED BY HIGH MEMORY USAGE?

Never again lose customers to an unstable server! Let us help you.

Contact Us once. Enjoy Peace Of Mind For Ever!

GET INSTANT SOLUTION FOR ALL MYSQL ISSUES

var google_conversion_label = "owonCMyG5nEQ0aD71QM";

3 Comments

  1. Umang

    I am using Cloud histing with 15 GB ram and 10 core cpu…what should be the value for these parameters??? Ram is utilised too much…

    Reply
    • Reeshma Mathews

      Hi Umang,

      There are no ‘one size fits all’ values for every server. The optimal values for your server can be suggested only after examining the current settings and the usage. Please contact us at https://bobcares.com/contact-us/ and our server specialists would suggest you the optimal parameters for your MySQL server.

      Reply
  2. Jamie

    Tip 4 says Upgrade Ram.
    Do you actually mean, increase Ram?

    Reply

Submit a Comment

Your email address will not be published. Required fields are marked *

Never again lose customers to poor
server speed! Let us help you.

Privacy Preference Center

Necessary

Necessary cookies help make a website usable by enabling basic functions like page navigation and access to secure areas of the website. The website cannot function properly without these cookies.

PHPSESSID - Preserves user session state across page requests.

gdpr[consent_types] - Used to store user consents.

gdpr[allowed_cookies] - Used to store user allowed cookies.

PHPSESSID, gdpr[consent_types], gdpr[allowed_cookies]
PHPSESSID
WHMCSpKDlPzh2chML

Statistics

Statistic cookies help website owners to understand how visitors interact with websites by collecting and reporting information anonymously.

_ga - Preserves user session state across page requests.

_gat - Used by Google Analytics to throttle request rate

_gid - Registers a unique ID that is used to generate statistical data on how you use the website.

smartlookCookie - Used to collect user device and location information of the site visitors to improve the websites User Experience.

_ga, _gat, _gid
_ga, _gat, _gid
smartlookCookie
_clck, _clsk, CLID, ANONCHK, MR, MUID, SM

Marketing

Marketing cookies are used to track visitors across websites. The intention is to display ads that are relevant and engaging for the individual user and thereby more valuable for publishers and third party advertisers.

IDE - Used by Google DoubleClick to register and report the website user's actions after viewing or clicking one of the advertiser's ads with the purpose of measuring the efficacy of an ad and to present targeted ads to the user.

test_cookie - Used to check if the user's browser supports cookies.

1P_JAR - Google cookie. These cookies are used to collect website statistics and track conversion rates.

NID - Registers a unique ID that identifies a returning user's device. The ID is used for serving ads that are most relevant to the user.

DV - Google ad personalisation

_reb2bgeo - The visitor's geographical location

_reb2bloaded - Whether or not the script loaded for the visitor

_reb2bref - The referring URL for the visit

_reb2bsessionID - The visitor's RB2B session ID

_reb2buid - The visitor's RB2B user ID

IDE, test_cookie, 1P_JAR, NID, DV, NID
IDE, test_cookie
1P_JAR, NID, DV
NID
hblid
_reb2bgeo, _reb2bloaded, _reb2bref, _reb2bsessionID, _reb2buid

Security

These are essential site cookies, used by the google reCAPTCHA. These cookies use an unique identifier to verify if a visitor is human or a bot.

SID, APISID, HSID, NID, PREF
SID, APISID, HSID, NID, PREF