Date: Fri, 20 Jan 2017 10:06:46 -0500
From: Stephen Frost <sfrost@snowman.net>
To: Vladimir Borodin <root@simply.name>
Cc: Pavel Stehule <pavel.stehule@gmail.com>,
	Dinesh Chandra 12108 <Dinesh.Chandra@cyient.com>,
	"Madusudanan.B.N" <b.n.madusudanan@gmail.com>,
	"pgsql-performance@postgresql.org" <pgsql-performance@postgresql.org>,
	David Steele <david@pgmasters.net>
Subject: Re: Backup taking long time !!!
Message-ID: <20170120150646.GP18360@tamriel.snowman.net>
References: <0fb0c8c3f69340a3a7d70b1c39fa2b2c@cyient.com>
 <CA+_K-Dw=_reBqdkM5EyghgR21eB7uDCKxTd7_Yh9jQ610-5gTg@mail.gmail.com>
 <23dc5b1ea9604ec3afdaabdd15491085@cyient.com>
 <CAFj8pRAHx-Wt4fyJmF=YH-F2_S=mO2E+=JDx+qnunkDP5_FPGA@mail.gmail.com>
 <b0748fe40949446283068a4d2d06c158@cyient.com>
 <CAFj8pRBFDh=hsghE8v5F71KztyczR7iWBXGJBOLLhUQDwUh8MQ@mail.gmail.com>
 <20170120122224.GF18360@tamriel.snowman.net>
 <DE553F58-646E-49E8-94D1-31794C3E9040@simply.name>
 <20170120134054.GH18360@tamriel.snowman.net>
 <7696A57C-C871-4A45-9141-FFD5F75CCB65@simply.name>
MIME-Version: 1.0
Content-Type: multipart/signed; micalg=pgp-sha512;
	protocol="application/pgp-signature"; boundary="1o9f3WYkqpI1o9iD"
Content-Disposition: inline
In-Reply-To: <7696A57C-C871-4A45-9141-FFD5F75CCB65@simply.name>
User-Agent: Mutt/1.5.21 (2010-09-15)
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org


--1o9f3WYkqpI1o9iD
Content-Type: text/plain; charset=utf-8
Content-Disposition: inline
Content-Transfer-Encoding: quoted-printable

Vladimir,

* Vladimir Borodin (root@simply.name) wrote:
> > 20 =D1=8F=D0=BD=D0=B2. 2017 =D0=B3., =D0=B2 16:40, Stephen Frost <sfros=
t@snowman.net> =D0=BD=D0=B0=D0=BF=D0=B8=D1=81=D0=B0=D0=BB(=D0=B0):
> >> Increments in pgbackrest are done on file level which is not really ef=
ficient. We have done parallelism, compression and page-level increments (9=
=2E3+) in barman fork [1], but unfortunately guys from 2ndquadrant-it don=
=E2=80=99t hurry to work on it.
> >=20
> > We're looking at page-level incremental backup in pgbackrest also.  For
> > larger systems, we've not heard too much complaining about it being
> > file-based though, which is why it hasn't been a priority.  Of course,
> > the OP is on 9.1 too, so.
>=20
> Well, we have forked barman and made everything from the above just becau=
se we needed ~ 2 PB of disk space for storing backups for our ~ 300 TB of d=
ata. (Our recovery window is 7 days) And on 5 TB database it took a lot of =
time to make/restore a backup.

Right, without incremental or compressed backups, you'd have to have
room for 7 full copies of your database.  Have you looked at what your
incrementals would be like with file-level incrementals and compression?

Single-process backup/restore is definitely going to be slow.  We've
seen pgbackrest doing as much as 3TB/hr with 32 cores handling
compression.  Of course, your i/o, network, et al, need to be able to
handle it.

> > As for your fork, well, I can't say I really blame the barman folks for
> > being cautious- that's usually a good thing in your backup software. :)
>=20
> The reason seems to be not the caution but the lack of time for working o=
n it. But yep, it took us half a year to deploy our fork everywhere. And it=
 would take much more time if we didn=E2=80=99t have system for checking ba=
ckups consistency.

How are you testing your backups..?  Do you have page-level checksums
enabled on your database?  pgbackrest recently added the ability to
check PG page-level checksums during a backup and report issues.  We've
also been looking at how to use pgbackrest to do backup/restore+replay
page-level difference analysis but there's still a number of things
which can cause differences, so it's a bit difficult to do.

Of course, doing a pgbackrest-restore-replay+pg_dump+pg_restore is
pretty easy to do and we do use that in some places to validate
backups.

> > I'm curious how you're handling compressed page-level incremental
> > backups though.  I looked through barman-incr and it wasn't obvious to
> > me what was going wrt how the incrementals are stored, are they ending
> > up as sparse files, or are you actually copying/overwriting the prior
> > file in the backup repository?
>=20
> No, we do store each file in the following way. At the beginning you writ=
e a map of changed pages. At second you write changed pages themselves. The=
 compression is streaming so you don=E2=80=99t need much memory for that bu=
t the downside of this approach is that you read each datafile twice (we be=
lieve in page cache here).

Ah, yes, I noticed that you passed over the file twice but wasn't quite
sure what functools.partial() was doing and a quick read of the docs
made me think you were doing seeking there.

All the pages are the same size, so I'm surprised you didn't consider
just having a format along the lines of: magic+offset+page,
magic+offset+page, magic+offset+page, etc...

I'd have to defer to David on this, but I think he was considering
having some kind of a bitmap to indicate which pages changed instead
of storing the full offset as, again, all the pages are the same size.

> >  Apologies, python isn't my first
> > language, but the lack of any comment anywhere in that file doesn't
> > really help.
>=20
> Not a problem. Actually, it would be much easier to understand if it was =
a series of commits rather than one commit that we do ammend and force-push=
 after each rebase on vanilla barman. We should add comments.

Both would make it easier to understand, though the comments would be
more helpful for me as I don't actually know the barman code all that
well.

Thanks!

Stephen

--1o9f3WYkqpI1o9iD
Content-Type: application/pgp-signature; name="signature.asc"
Content-Description: Digital signature

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1

iQIcBAEBCgAGBQJYgieGAAoJEO1sijiDR2RVVkcP/Akrf4uVINrmm8uENwsmuFtx
H1l6W2+ol+h8j0eKPSkwAf3S9aXtrOdrxh9yofVa4Ob70IxLfV+xMLXIxbiiXGTD
XjyC4uNHLgwc39mozqBfLVOQghIoIYJ2gYMvuVkal4yTpg9n86d7TMIfwl1+TMuT
ZxtFKS0FbGbqcqw3SAXV28gNst2BdUkai+aty98MB26OaOCejbfbtTSYmJ8cKcH5
ESgdfuuyvP3uTorzgmR2RXZ58S7lryr/vJ9N3+vNSO9uWznBg64/bDxPOYA9F4F+
WzZqy5NVR5o1QoFJ5s6xt0kY7uXNL5rJcI4lPasqpV69MWrBZP2wcMNrJdZaL5C+
uqPZfXSogH8WrrnnhH8Woow4LxEz76yrreI0dFbftSWFMUiPbVeR1ev8Pwruai3j
nkS1JDuLisosBnNTZ2+slW/Sh0Z0c/Gq9n4KvaDTE96fKlyn/j/BM3FQPSVH8+iY
h/tVXerJIyX8fH3GHSsTBwZ/7dui3M9YocrfTrBCh98xtmgSf7JSADICxpkCvR3R
81uaWdBOHvv+hX+OBbCJ/00jRwdA9VAeEgx24dJr6aQ7/HAxqzTKHttKys3tT9DR
IxWZi1NDAzAcJl7Nta0AUWyYENgwXcQJ4nroTX5D58IcxtNDJsAmRxkIirjsq92n
iYvnmukahbFSA8H6G/xd
=gei1
-----END PGP SIGNATURE-----

--1o9f3WYkqpI1o9iD--