4

I am wondering how to decide the value of innodb_buffer_pool_size for my Master-Slave style database.

I have a set of MySQL, 1 x master + 1 x slave.

As I know that the innodb_buffer_pool_size is generally for data storing in memory to speedup the data access (read). So can I set the different value between master & slave ?

For example, if I calculate the suitable innodb_buffer_pool_size value is 8G, I should set 8G in my slave but maybe 4G or less for my master ?

That's because the innodb_buffer_pool_size can benefit the read not the write process. Am I wrong ?

Thanks.

2 Answers 2

3

The buffer pool also benefits writing changes. When you change a row, it is first changed in the copy of the row's page, which resides in the buffer pool. InnoDB flushes that page back to disk later, and doesn't make you wait for the flush to finish before it calls the update complete (it only has to log the change in the InnoDB redo log to say it's safe).

The buffer pool also contains the change buffer for secondary indexes. This basically queues up updates to indexes, so when you update a table that has indexes, you don't have to wait for your updated values to be inserted into the indexes. This will also happen later in the background.

So both reads and writes benefit from having a buffer pool, and also both source and replica benefit from having a buffer pool. The larger the buffer pool, the more likely a given page may already be in RAM when you run a query either to read or write that page.

There is a point of diminishing returns on increasing the buffer pool size. If you only have a small database, say 10MB, it makes little sense to allocate many gigabytes of buffer pool.

But it's common to have a database that is larger than your RAM, so then we expect the buffer pool to be sized to hold the most frequently-accessed subset of database pages. Not all of your database is equally likely to be queried. Therefore it could be that a buffer pool 1/4 the size of your database is sufficient to make 95% of your queries run without needing to load more pages from disk. These ratios are just an example, because it depends completely on your application.

0

InnoDB buffer pool caches both data and index pages. You can set this value to 70-80% of available memory for Innodb-only installations.

If you have 16GB RAM on Master then you can set this upto 12GB similarly for your slave as well.

2
  • Hi, I am wondering if master is only for writing, why should I setup so much innodb_buffer_pool_size for it ?
    – James
    Nov 28, 2013 at 1:57
  • You can set lesser for master and more for Slave.. it's all your choice! I meant to say is if you have all InnoDB tables then you should give atleast 50% of memory to innodb_buffer_pool Nov 28, 2013 at 12:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.