Disable transparent huge pages after page buffers has been allocated

Many new OS releases has enabled transparent huge pages (THP) by default.

This task is to turn of THP for MariaDB server as it causes excessive usage of memory which can lead to (and has been proven to cause) out-of-memory (OOM) crashes.

There is a benefit of using huge pages for allocating big buffers, like InnoDB buffer pool, Aria pagecache etc. THP turn of should happen after these are allocated or alternatively disable THP at startup and use madvice() for these buffers.

THP can be turned of by an application with:

prctl(PR_SET_THP_DISABLE, 1, 0, 0, 0);

One can verify if THP is a likely cause of OOM m by executing:

cat /proc/32866/smaps | grep -i pss |  awk '{Total+=$2} END {print Total/1024" MB"}'

cat /proc/32866/smaps | grep ^Size | sort

If THP is causing problems, then the above commands will show extensive memory usage and a lot of pages with sizes of 128M, 256M, 512M. These does not fit with how MariaDB allocates and frees memory and currently the most likely cause of these memory blocks are THP.

We have tested turning of THP for a user with MariaDB memory problems and it solved the OOM issue.

The disadvantage of turning of THP is a possible performance loss of a few % (we have not been able to verify if this is the case for MariaDB), but this is better than getting crashes.

On can check on Linux if THP is enabled with:

cat /sys/kernel/mm/transparent_hugepage/enabled

[always] madvise never

'[always]' means that THP is enabled.

THP interferes with how memory is allocated and freed. It does not work well with databases or other services that has a long uptime and constantly allocates and frees memory. This is why Oracle, TokuDB, MongoDB and others recommends that one should turn of THP if their software is used.

Some links about huge pages:

https://docs.splunk.com/Documentation/Splunk/latest/ReleaseNotes/SplunkandTHP?_gl=1*23cvro*_ga*MTA3MTUzMTI1MC4xNzAxNDQ4MDE0*_ga_GS7YF8S63Y*MTcwMTQ0ODAxNi4xLjAuMTcwMTQ0ODAxNi42MC4wLjA.*_ga_5EPM2P39FV*MTcwMTQ0ODAxMy4xLjAuMTcwMTQ0ODAxNC4wLjAuMA..&_ga=2.204794320.192068637.1701448014-1071531250.1701448014

https://www.percona.com/blog/why-tokudb-hates-transparent-hugepages/

https://blogs.sap.com/2014/05/22/sap-iq-and-linux-hugepagestransparent-hugepages/

Oracle recommends one to not use transparent_huge_pages:
https://docs.oracle.com/en/database/oracle/oracle-database/19/ladbi/disabling-transparent-hugepages.html#GUID-02E9147D-D565-4AF8-B12A-8E6E9F74BEEA