Question about "Transparent Huge Pages (THP)" affecting mysql mentioned in blog article

MySQL & MariaDB
1

Hello! In the blog article posted at:
https://www.percona.com/blog/2016/05/03/best-practices-for-configuring-optimal-mysql-memory-usage/

It mentions

Another thing you need to account for is memory fragmentation. Depending on the memory allocation library you’re using (glibc, TCMalloc, jemalloc, etc.), the operating system settings such as Transparent Huge Pages (THP) and workload may show memory usage to grow over time (until it reaches some steady state). Memory fragmentation can also account for 10% or more of additional memory usage.

This is exactly what I am seeing on my server. On my dev server with almost no traffic besides my little testing, mysqld will slowly over time creep up to use about 510MB RAM. Upon restart it will drop back down to 380MB. Then over the course of a week or two it will grow until it hits 510MB and then stay there. Is this being caused by THP? If I disable THP, will mysqld get allocated the memory at startup and it stay consistent (besides whatever is used by connections as they increase)? I really hate the idea that mysqld grows by itself (with no real connections) until it hits some mystery amount of RAM. I would rather it set aside the memory for the buffer pool at the beginning and slowly fill it up as it warms up over time. The way it is now makes me feel uneasy since it’s growth cannot be documented or predicted.

Running Ubuntu 18, Mysql 8.0.22 with THP settings:
always [madvise] never
InnoDB buffer pool: 128.0MB

This is on a small 1GB RAM test server.

The comments in the blog article are closed and it said to post in here.

Thanks!

2

THP will not affect a server with 1GB of RAM.

You’ll only find that THP comes in to play is when you have lots more RAM on a machine, like 64GB+
THP is not a mechanism to pre-allocate memory to any process.
As you discovered, not all memory in MySQL is allocated at startup. This is expected and quite normal. The buffer pool is an exception to this; it is allocated at startup and filled over time. The additional memory you are seeing are internal buffers being allocated as queries are executed.

1 Like
3

Thanks for clarifying! Although, I’m a little disappointed as I thought that would be an easy fix to my confusion haha. I am trying to understand how the mechanics work before launching a site that hopefully requires upgrading to something with 64GB :slight_smile: But it makes sense that they could operate in different ways.

Not to ask too much of you, but from what I gather there is…

  1. memory allocated at startup that has a fixed upper limit (ex: buffer pool)
  2. memory allocated to each connection (grows based on # of current connections and or threads cached). not sure if this necessarily has a fixed upper limit per connection
  3. other buffers that grow over time (based on your answer)

I think what I’m missing is #3. What (specifically) are these things that grow over time and are they limited (by configuration file/etc)? How at any given point can you see what % of them is “full” or “used” and how high they might go? It is scary to me that something just grows until some arbitrary point/level. It would seem there has to be a limit if my server magically just tops out at 510MB based on little/no activity. You can see in the graph the restart and gradual increase and then leveling off. Or does mysql randomly figure it out based on machine size? If I were to resize my VM to 8GM RAM without changing any configs, would it still magically stop at 510MB based on configs?
I know this is slightly deviating from my original question, but I’ve search for days and can’t understand what is going on. Thanks!

server memory usage
server memory usage1136×410 25 KB

4

Here’s the best calculation function you can use:

SELECT CONCAT((@@key_buffer_size + @@query_cache_size + (@@innodb_buffer_pool_size * 1.05 + 20*1024*1024) + @@innodb_additional_mem_pool_size + @@innodb_log_buffer_size 
+ @@max_connections * (@@read_buffer_size + @@read_rnd_buffer_size + @@sort_buffer_size + @@join_buffer_size + @@binlog_cache_size + @@tmp_table_size
+ @@thread_stack)) / 1024/1024/1024, ' GB') AS "POTENTIAL MEMORY USAGE";

You can see there are several buffers allocated for each connection, so there is a limit based on max_connections. However, you can still grow beyond this if you had a really bad query which examined millions/billions of rows. Something like this would allocate multiple sort buffers.

If you increase the VM to 8GB, and changed absolutely nothing else, then you’d see memory behave the same. If your query load changed, then your memory might change as well.

1 Like
5

Thanks! I will research the documentation on how each of those components in the query are defined and used by the system. That will definitely help me start to break down my research into the different areas of memory consumption.

Just as a heads-up, I’m getting the following error:
ERROR 1193 (HY000): Unknown system variable ‘query_cache_size’

Quick googling seems to be that query_cache_size was removed in mysql 8. I’ll post back if I come up with a solution so that others might have it if they ever stumble on this thread.

Thanks!

1 Like
6

Yes it was, so you can simply remove that variable from the SQL.

1 Like
7

out of curiosity, did they rename/replace it with something else that should be included in the query? or is the concept gone entirely? dont need a detailed explanation, just want to make sure there aren’t any holes in the coverage/stats. THanks!

1 Like
8

Completely gone. It was badly implemented and innodb’s buffer pool is just as fast. If you need a real query-results cache, look at using ProxySQL.

1 Like