Il 03/08/2016 18:01, Jeff Janes ha scritto:
On Thu, Jul 28, 2016 at 6:25 AM, Moreno Andreo <moreno.andreo@xxxxxxxxxx> 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
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
