How big can innodb_doublewrite_file grow? When I use the enable innodb_doublewrite_file ...


How big can this file grow ?
How can I calculate the optimal disk size, if it would be used only for this file?
Please look at the following view of the InnoDB/XtraDB Architecture



Please notice the Percona Server Documentation on This


  Performance Impact of the Doublewrite Buffer
  
  In usual workloads the performance impact is low-5% or so. As a
  consequence, you should always enable the doublewrite buffer because
  the strong guarantee against data corruption is worth the small
  performance drop.
  
  But if you experience a heavy workload, especially if your data does
  not fit in the buffer pool, the writes in the doublewrite buffer will
  compete against the random reads to access the disk. In this case, you
  can see a sharp performance drop compared to the same workload without
  the doublewrite buffer-a 30% performance degradation is not uncommon.
  
  Another case when you can see a big performance impact is when the
  doublewrite buffer is full. Then new writes must wait until entries in
  the doublewrite buffer are freed.
  
  How to Choose a Good Location for the Doublewrite Buffer
  
  Basically if you want to improve the I/O activity, you will put the
  doublewrite buffer on a different disk. But is it better on an SSD or
  a more traditional HDD? First you should note that pages are written
  in a circular fashion in the doublewrite buffer and only read on
  recovery. So the doublewrite buffer performs mostly sequential writes
  and a few sequential reads. Second HDDs are very good at sequential
  write if a write cache is enabled, which is not the case of SSDs.
  Therefore you should choose a fast HDD if you want to see performance
  benefits from this option. For instance, you could place the redo logs
  (also written in a sequential way) and the doublewrite buffer on the
  same disk.


What can we learn from this ?


The doublewrite buffer is a circular buffer. It is subject to pending writes when it is full.
The fact that the doublewrite buffer can get full proves that growth is never an issue.


CAVEAT

Looking at the InnoDB/XtraDB Architecture, it says that the Undo Logs can grow unllimitedly. That is the source of uncontrolled growth in the system tablespace.

Your Actual Question


  How big can grow innodb_doublewrite_file? How to calculate optimal disk size, if it would be used only for this file?


Nothing in the Documentation states a way to set a size or know its limitations. It only recommends placing the doublewrite file on a fast HDD.

CONJECTURE (OK I am guessing at this point)

If there is no limit on the doublewrite buffer size, then the number of blocks that make up all the data and index pages would be the maximum size. You would run this query:

SELECT
    ibbytes InnoDBSpace,
    ibbytes/POWER(1024,1) InnoDBSpaceKB,
    ibbytes/POWER(1024,2) InnoDBSpaceMB,
    ibbytes/POWER(1024,3) InnoDBSpaceGB,
    ibbytes/POWER(2,14)   InnoDBBlocks
FROM (SELECT SUM(data_length+index_length) ibbytes
FROM information_schema.tables WHERE engine = 'InnoDB') A;


These numbers assume that every block of data and index for InnoDB has a place in the doublewrite buffer to go to.

A more realistic number may be this:

SELECT
    ibbytes InnoDBSpace,
    ibbytes/POWER(1024,1) InnoDBSpaceKB,
    ibbytes/POWER(1024,2) InnoDBSpaceMB,
    ibbytes/POWER(1024,3) InnoDBSpaceGB,
    ibbytes/POWER(2,14)   InnoDBBlocks
FROM (SELECT (variable_value * POWER(2,14)) ibbytes
FROM information_schema.global_status
WHERE variable_name = 'Innodb_buffer_pool_pages_data');


These numbers are going by the actual working set in the InnoDB Buffer Pool

I hope this little explanation helped you !!!