PXC8.4.3 initial memory usage of mysqld process very high compared to PXC8.0.x

Hi Folks,

We’re moving up from PXC8.0 to PXC8.4 and have noticed that the initial memory usage of the mysqld process in PXC8.4 seems much higher compared to, say, PXC8.0.35.

Example running on a 32GB RAM RHEL8.10 server where we allow mysql pretty much 80% of system ram for innodb_buffer_pool - these are the only innodb settings we explicitly set in my.cnf:

# INNODB #
innodb-flush-method            = O_DIRECT
innodb-log-files-in-group      = 2
innodb-log-file-size           = 1024M
innodb-flush-log-at-trx-commit = 2
innodb-file-per-table          = 1
innodb-buffer-pool-size        = 25347M
innodb_buffer_pool_instances   = 3
innodb_autoinc_lock_mode       = 2

On startup in PXC8.0.35 (and with no connections or workload and essentially empty DB) the mysqld process consumes around 2.7GB RAM (as reported by top) but in PXC8.4.3 its using 22.8GB right off the bat.

We would find that, in PXC8.0.35, over time and under load the mysqld process would obviously consume up to and beyond 25GB RAM but I’m interested to know if anything obvious has changed in PXC8.4 to account for it seemingly grabbing the memory up front.

The “How MySQL Uses Memory” chapter in the official MySQL docs states that " InnoDB allocates memory for the entire buffer pool at server startup, using malloc() operations." in both the 8.0 and 8.4 variants.

Is it the case then that PXC8.0 is in the wrong here and should have been grabbing the memory straight away?

If its useful I did a pt-config-diff of the ‘innodb%’ variables from both versions:

[root@qatest1 ~]# pt-config-diff /tmp/pxc8035.cnf /tmp/pxc843.cnf
12 config differences
Variable                  /tmp/pxc8035.cnf /tmp/pxc843.cnf
========================= ================ ===============
innodb_adaptive_hash_i... ON               OFF
innodb_buffer_pool_in_... ON               OFF
innodb_change_buffering   all              none
innodb_doublewrite_files  6                2
innodb_doublewrite_pages  4                128
innodb_io_capacity        200              10000
innodb_io_capacity_max    2000             20000
innodb_log_buffer_size    16777216         67108864
innodb_numa_interleave    OFF              ON
innodb_purge_threads      4                1
innodb_use_fdatasync      OFF              ON
innodb_version            8.0.35-27        8.4.3-3

Thanks in advance for any pointers/wisdom.

Neil

@Neil_Billett

Yes, the buffer pool allocation should have done at startup and the same can be observed by using below PS query.

mysql> select * from memory_summary_global_by_event_name where event_name like '%buf_buf_pool%'\G;

I tried to do a small test and observed the same behaviour what you are telling. From the below outputs of both 8.4.3 vs 8.0.35 it appears the older 8.0.35 not resembling the full buffer pool memory in the RSS output.

8.4.3

mysql> select @@innodb_buffer_pool_size/1024/1024/1024;
+------------------------------------------+
| @@innodb_buffer_pool_size/1024/1024/1024 |
+------------------------------------------+
|                          16.000000000000 |
+------------------------------------------+
1 row in set (0.00 sec)
 mysql> select * from memory_summary_global_by_event_name where event_name like '%buf_buf_pool%'\G;
*************************** 1. row ***************************
                  EVENT_NAME: memory/innodb/buf_buf_pool
                 COUNT_ALLOC: 132
                  COUNT_FREE: 0
   **SUM_NUMBER_OF_BYTES_ALLOC: 18115215360**
    SUM_NUMBER_OF_BYTES_FREE: 0
              LOW_COUNT_USED: 0
          CURRENT_COUNT_USED: 132
             HIGH_COUNT_USED: 132
    LOW_NUMBER_OF_BYTES_USED: 0
CURRENT_NUMBER_OF_BYTES_USED: 18115215360
   HIGH_NUMBER_OF_BYTES_USED: 18115215360
1 row in set (0.01 sec)
[root@node0 /]# ps aux --sort=-rss
USER         PID %CPU %MEM    VSZ   RSS TTY      STAT START   TIME COMMAND
mysql       5783 13.4  9.6 21173948 **19136464** ?   Ssl  07:36   0:23 /usr/sbin/mysqld

VS

8.0.35

mysql> select * from memory_summary_global_by_event_name where event_name like '%buf_buf_pool%'\G;
*************************** 1. row ***************************
                  EVENT_NAME: memory/innodb/buf_buf_pool
                 COUNT_ALLOC: 128
                  COUNT_FREE: 0
   **SUM_NUMBER_OF_BYTES_ALLOC: 17566269440**
    SUM_NUMBER_OF_BYTES_FREE: 0
              LOW_COUNT_USED: 0
          CURRENT_COUNT_USED: 128
             HIGH_COUNT_USED: 128
    LOW_NUMBER_OF_BYTES_USED: 0
CURRENT_NUMBER_OF_BYTES_USED: 17566269440
   HIGH_NUMBER_OF_BYTES_USED: 17566269440
1 row in set (0.01 sec)

ps aux --sort=-rss
USER         PID %CPU %MEM    VSZ   **RSS** TTY      STAT START   TIME COMMAND
mysql       5689  4.1  0.8 20064892 **1734232** ?    Ssl  07:57   0:16 /usr/sbin/mysqld

Just to correlate what you seeing, would it be possible for you to share the similar output from your both the environments ?

Also, is your new 8.4.3 instance is loaded with the data or its just empty at the moment ?

Hi Anil,

Thank you for the response. I can confirm that we are talking about empty instances here.

So here’s the output you requested:

PXC 8.0.35

mysql> select * from performance_schema.memory_summary_global_by_event_name where event_name like '%buf_buf_pool%'\G
*************************** 1. row ***************************
                  EVENT_NAME: memory/innodb/buf_buf_pool
                 COUNT_ALLOC: 201
                  COUNT_FREE: 0
   SUM_NUMBER_OF_BYTES_ALLOC: 27584532480
    SUM_NUMBER_OF_BYTES_FREE: 0
              LOW_COUNT_USED: 0
          CURRENT_COUNT_USED: 201
             HIGH_COUNT_USED: 201
    LOW_NUMBER_OF_BYTES_USED: 0
CURRENT_NUMBER_OF_BYTES_USED: 27584532480
   HIGH_NUMBER_OF_BYTES_USED: 27584532480
1 row in set (0.00 sec)

[root@qatest1 ~]# ps aux --sort=-rss
USER         PID %CPU %MEM    VSZ   RSS TTY      STAT START   TIME COMMAND
mysql     195724  9.2  8.6 36232312 2792344 ?    Ssl  09:30   0:53 /usr/sbin/mysqld --wsrep_start_position=00000000-0000-0000-0000-000000000000:-1

top output:

    PID USER      PR  NI    VIRT    RES    SHR S  %CPU  %MEM     TIME+ COMMAND
 195724 mysql     20   0   34.6g   2.7g  42920 S   0.0   8.6   1:01.85 mysqld

PXC 8.4.3

mysql> select * from performance_schema.memory_summary_global_by_event_name where event_name like '%buf_buf_pool%'\G
*************************** 1. row ***************************
                  EVENT_NAME: memory/innodb/buf_buf_pool
                 COUNT_ALLOC: 201
                  COUNT_FREE: 0
   SUM_NUMBER_OF_BYTES_ALLOC: 27584532480
    SUM_NUMBER_OF_BYTES_FREE: 0
              LOW_COUNT_USED: 0
          CURRENT_COUNT_USED: 201
             HIGH_COUNT_USED: 201
    LOW_NUMBER_OF_BYTES_USED: 0
CURRENT_NUMBER_OF_BYTES_USED: 27584532480
   HIGH_NUMBER_OF_BYTES_USED: 27584532480
1 row in set (0.01 sec)

[root@qatest2 ~]# ps aux --sort=-rss
USER         PID %CPU %MEM    VSZ   RSS TTY      STAT START   TIME COMMAND
mysql     783334  1.0 74.8 36188928 24279804 ?   Ssl  09:31   0:06 /usr/sbin/mysqld --wsrep_start_position=00000000-0000-0000-0000-000000000000:-1

top output:

    PID USER      PR  NI    VIRT    RES    SHR S  %CPU  %MEM     TIME+ COMMAND
 783334 mysql     20   0   34.5g  23.1g  23636 S   0.0  74.8   0:06.50 mysqld

I have carried out some further investigations and it turns out to be the new default of ON for the innodb_numa_interleave property that is making the difference.

If I add this to my.cnf in PXC8.0.43:

innodb_numa_interleave = OFF

…and restart the node, the initial memory usage comes right down to match that of PXC8.0.35:

[root@qatest2 ~]# ps aux --sort=-rss
USER         PID %CPU %MEM    VSZ   RSS TTY      STAT START   TIME COMMAND
mysql     788660  4.8  8.7 35758744 2828164 ?    Ssl  09:44   0:01 /usr/sbin/mysqld --wsrep_start_position=a979befd-eb7f-11ef-a086-72c0b84441db:1661

top output:
    PID USER      PR  NI    VIRT    RES    SHR S  %CPU  %MEM     TIME+ COMMAND
 788660 mysql     20   0   34.1g   2.7g  46296 S   0.3   8.7   0:01.58 mysqld

Guess I’ve got to try and understand that property and why we might want to accept the new default now :slight_smile:

thanks,

Neil