On Thu, Jul 28, 2016 at 6:25 AM, Moreno Andreo <moreno.andreo@evolu-s.it> wrote:
> Hi folks! :-)
> I'm about to bring up my brand new production server and I was wondering if
> it's possible to calculate (approx.) the WAL directory size.
> I have to choose what's better in terms of cost vs. performance (we are on
> Google Cloud Platform) between a ramdisk or a separate persistent disk.
As others have said, there is almost no point in putting WAL on a
ramdisk. It will not be there exactly at the time you need it.
> Obviously ramdisk will be times faster disk, but having a, say, 512 GB
> ramdisk will be a little too expensive :-)
> I've read somewhere that the formula should be 16 MB * 3 *
> checkpoint_segment in size. But won't it be different depending on the type
> of /wal_level/ we set? And won't it also be based on the volume of
> transactions in the cluster?
Not in usual cases. If you have more volume, then checkpoint_segment
will get exceeded more frequently and you will have more frequent
checkpoints. As long as your system can actually keep up with the
checkpoints, then the more frequent checkpoints will cancel the higher
volume, leaving you with the same steady-state number of segments.
> And, in place of not-anymore-used-in-9.5 /checkpoint_segment/ what should I
> use? /max_wal_size/?
max_wal_size doesn't just replace "checkpoint_segment" in the formula.
It replaces the entire
formula itself. That was the reason for introducing it.
> Aside of this, I'm having 350 DBs that sum up a bit more than 1 TB, and plan
> to use wal_level=archive because I plan to have a backup server with barman.
>
> Using the above formula I have:
> 16 MB * 3 * 1 GB
If you are getting the "1 GB" from max_wal_size, then see above.
Note that max_wal_size is not a hard limit. It will be exceeded if
your system can't keep up with the checkpoint schedule. Or if
archive_command can't keep up.
Cheers,
Jeff