Re: [SPAM] Re: WAL directory size calculation

Il 03/08/2016 18:01, Jeff Janes ha scritto:
> On Thu, Jul 28, 2016 at 6:25 AM, Moreno Andreo <moreno(dot)andreo(at)evolu-s(dot)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.
OK, got it, as I already stated. That was just a bad thought :-)
>
>> 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.

So if I want to keep checkpoint happening not frequently, the solution
is to have a bigger checkpoint_segment (or max_wal_size), so value gets
exceeded less frequently?

>> 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.

Another point cleared. I did not get this in the docs. I'll go an read
it again.

>
>> 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.

Exactly. I think it's its default value, since I didn't change it.

> 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.
Got it.
Thanks
Moreno
>
> Cheers,
>
> Jeff
>
>