Memory(buffer pool) not populating on mysql start on Percona 5.6.28

Question

We are testing Percona 5.6.28 at the moment, during our testing we found out even though the innodb_buffer_pool_populate is set to ON, it doesn't populate the memory(buffer pool) during mysql start. There is the new parameter that was added in 5.6.27, innodb_numa_interleave, even with that ON the buffer pool doesn't populate on mysql.

We even tried with enabling numa at hardware level, but still same results

Memory: 512G innodb_buffer_pool_size: 420G

We have a 420G innodb_buffer_pool_size, but following are the memory results after mysql start, it doesn't allocate the memory during mysql start

NUMA: Allocated memnodemap from d000 - d440
NUMA: Using 30 for the hash shift.
pci_bus 0000:00: on NUMA node 0
pci_bus 0000:20: on NUMA node 1
pci_bus 0000:40: on NUMA node 2
pci_bus 0000:60: on NUMA node 3
[root@db001 ~]# numactl --hardware
available: 4 nodes (0-3)
node 0 cpus: 0 1 2 3 4 5 6 7 32 33 34 35 36 37 38 39
node 0 size: 131037 MB
node 0 free: 104737 MB
node 1 cpus: 8 9 10 11 12 13 14 15 40 41 42 43 44 45 46 47
node 1 size: 131072 MB
node 1 free: 95004 MB
node 2 cpus: 16 17 18 19 20 21 22 23 48 49 50 51 52 53 54 55
node 2 size: 131072 MB
node 2 free: 115600 MB
node 3 cpus: 24 25 26 27 28 29 30 31 56 57 58 59 60 61 62 63
node 3 size: 131071 MB
node 3 free: 125262 MB
node distances:
node   0   1   2   3 
  0:  10  21  21  21 
  1:  21  10  21  21 
  2:  21  21  10  21 
  3:  21  21  21  10 

Around 74G used memory, 430G free and 6G in cache

Most of the memory is free and not allocated

innodb_buffer_pool_dump_at_shutdown anf innodb_buffer_pool_load_at startup are ON

    [root@db001 ~]# grep innodb_buffer_pool /etc/my.cnf
    innodb_buffer_pool_size        = 420G
    innodb_buffer_pool_dump_at_shutdown = ON
    innodb_buffer_pool_load_at_startup  = ON
    innodb_buffer_pool_instances   = 64

Buffer pool is fully loaded at the moment because the server has been up for almost 3 days now

    root@db001 [(none)]> show global status like 'innodb_buffer_pool_%';
    | Variable_name                           | Value                                              |
    | Innodb_buffer_pool_dump_status          | not started                                      |
    | Innodb_buffer_pool_load_status          | Buffer pool(s) load completed at 160603 16:10:29 |
    | Innodb_buffer_pool_pages_data           | 27381056                                         |
    | Innodb_buffer_pool_bytes_data           | 448611221504                                     |
    | Innodb_buffer_pool_pages_dirty          | 994204                                           |
    | Innodb_buffer_pool_bytes_dirty          | 16289038336                                      |
    | Innodb_buffer_pool_pages_flushed        | 105665319                                        |
    | Innodb_buffer_pool_pages_LRU_flushed    | 0                                                |
    | Innodb_buffer_pool_pages_free           | 65522                                            |
    | Innodb_buffer_pool_pages_made_not_young | 9919036                                          |
    | Innodb_buffer_pool_pages_made_young     | 4488881                                          |
    | Innodb_buffer_pool_pages_misc           | 78478                                            |
    | Innodb_buffer_pool_pages_old            | 5453509                                          |
    | Innodb_buffer_pool_pages_total          | 27525056                                         |
    | Innodb_buffer_pool_read_ahead_rnd       | 0                                                |
    | Innodb_buffer_pool_read_ahead           | 784                                              |
    | Innodb_buffer_pool_read_ahead_evicted   | 0                                                |
    | Innodb_buffer_pool_read_requests        | 10840103331                                      |
    | Innodb_buffer_pool_reads                | 17722410                                         |
    | Innodb_buffer_pool_wait_free            | 0                                                |
    | Innodb_buffer_pool_write_requests       | 1822727016                                       |

    root@db001 [(none)]> show global status like 'uptime';
    +---------------+--------+
    | Variable_name | Value  |
    +---------------+--------+
    | Uptime        | 256907 |
    +---------------+--------+
    1 row in set (0.00 sec)

    root@db001 [(none)]> set global innodb_buffer_pool_load_now=1;
    Query OK, 0 rows affected (0.03 sec)

    root@db001 [(none)]> show global status like 'innodb_buffer_pool_%';
    +-----------------------------------------+--------------------------------------------------+
    | Variable_name                           | Value                                            |
    +-----------------------------------------+--------------------------------------------------+
    | Innodb_buffer_pool_dump_status          | not started                                       |
    | Innodb_buffer_pool_load_status          | Buffer pool(s) load completed at 160606 15:25:12 |
    | Innodb_buffer_pool_pages_data           | 27381012                                         |
    | Innodb_buffer_pool_bytes_data           | 448610500608                                     |
    | Innodb_buffer_pool_pages_dirty          | 990362                                           |
    | Innodb_buffer_pool_bytes_dirty          | 16226091008                                      |
    | Innodb_buffer_pool_pages_flushed        | 105742542                                        |
    | Innodb_buffer_pool_pages_LRU_flushed    | 0                                                |
    | Innodb_buffer_pool_pages_free           | 65522                                            |
    | Innodb_buffer_pool_pages_made_not_young | 9930674                                          |
    | Innodb_buffer_pool_pages_made_young     | 4497569                                          |
    | Innodb_buffer_pool_pages_misc           | 78522                                            |
    | Innodb_buffer_pool_pages_old            | 5453502                                          |
    | Innodb_buffer_pool_pages_total          | 27525056                                         |
    | Innodb_buffer_pool_read_ahead_rnd       | 0                                                |
    | Innodb_buffer_pool_read_ahead           | 784                                              |
    | Innodb_buffer_pool_read_ahead_evicted   | 0                                                |
    | Innodb_buffer_pool_read_requests        | 10847012782                                      |
    | Innodb_buffer_pool_reads                | 17730031                                         |
    | Innodb_buffer_pool_wait_free            | 0                                                |
    | Innodb_buffer_pool_write_requests       | 1823928823                                       |
    +-----------------------------------------+--------------------------------------------------+

My question is more on populating the buffer pool on startup, with 5.6.19 the memory used on the server stayed close to our buffer pool size(around 420G) right after mysql start and it took around 7-8 minutes to start mysql. With 5.6.28, used memory after mysql start is close to 50G and then it starts loading with time and mysql starts in less than 30 seconds for a 420G buffer pool.

Answer 1

Amit, the problem is that this variable is set to reload a copy of the buffer pool dumped to disk during a stop/restart process. In order to achieve this you need to set innodb_buffer_pool_dump_at_shutdown to ON prior to restart your server, please check this link for further info: https://dev.mysql.com/doc/refman/5.6/en/innodb-preload-buffer-pool.html Regarding NUMA configuration this has nothing to do with buffer pool fill but how memory dedicated to buffer pool is balanced between CPU nodes (this is useful only for physical servers) so in modern hardware where you have more than one physical CPU you will avoid having swapping issues by balancing large portions of memory allocation (like buffer pool normally has) between nodes. Hope this helps.

Answer 2

According to release docs innodb_buffer_pool_populate was mapped to innodb_numa_interleave starting with Percona 5.6.27-75.0, so based on what you see, it appears it is no longer preallocating the buffer pool, only setting the numa policy. Regarding innodb_buffer_pool_dump_at_shutdown and load at startup, you might want to have a look at http://dev.mysql.com/doc/refman/5.6/en/innodb-preload-buffer-pool.html. Those parameters won't cause preallocation of the full size on restart, only any pages that were in the buffer pool before you shutdown the db will be restored.

Answer 3

NUMA is not unrelated to buffer pool preallocation. That is the reason why innodb_buffer_pool_populate was introduced in the first place. See https://blog.jcole.us/2012/04/16/a-brief-update-on-numa-and-mysql/

That is also the reason why it took around 7-8 minutes to start mysql with the older Percona release. Now the upstream implementation replaced innodb_buffer_pool_populate and the other numa related parameters with innodb_numa_interleave, which apparently only does numactl --interleave=all and does not do the preallocation. Newer Percona releases should also have this behaviour (see https://www.percona.com/doc/percona-server/5.6/performance/innodb_numa_support.html#version-specific-information)

Regarding your test, innodb_buffer_pool_load_at_startup = ON can take a lot of time depending on buffer pool size and io performance, so you should look for a message like InnoDB: Buffer pool(s) load completed at 5:29:10 in the error log before checking memory usage