0

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.

Improve this question
2
  • Any ideas or information will be helpful. Thanks! Commented Jun 6, 2016 at 16:38
  • Please run the following: SET GLOBAL innodb_buffer_pool_load_now=1; SHOW GLOBAL STATUS like 'innodb_buffer_pool%'; and post the output of the SHOW command into your question. Commented Jun 6, 2016 at 20:47

3 Answers 3

Reset to default
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.

Improve this answer
1
  • Hi Francisco, Thanks for replying. Both of those parameters are ON. The problem seems to be with populating the buffer pool during mysql start. I have updated the post with the results. Commented Jun 6, 2016 at 18:12
1

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.

Improve this answer
0

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

Improve this answer

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.