Select Page

How to fix MySQL high memory usage

How to fix MySQL high memory usage

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.


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

2 Comments

  1. 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
    • 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

Submit a Comment

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

Bobcares
Bobcares is a server management company that helps businesses deliver uninterrupted and secure online services. Our engineers manage close to 51,500 servers that include virtualized servers, cloud infrastructure, physical server clusters, and more.
MORE ABOUT BOBCARES

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

    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

    IDE, test_cookie, 1P_JAR, NID, DV, NID
    IDE, test_cookie
    1P_JAR, NID, DV
    NID