PostgreSQL - backup and recovery with large databases

Life on a rollercoaster
Scaling the PostgreSQL backup and recovery
Federico Campoli
Brighton PostgreSQL Users Group
9 September 2016
Federico Campoli (Brighton PostgreSQL Users Group) Life on a rollercoaster 9 September 2016 1 / 51

Table of contents
1 2012 - Who am I? What I’m doing?
2 2013 - On thin ice
3 2014 - The battle of ﬁve armies
4 2015 - THIS IS SPARTA!
5 2016 - Breathe
6 Wrap up

Warning!
The story you are about to hear is true.
Only the names have been changed to protect the innocent.

Dramatis personae
A brilliant startup ACME

Dramatis personae
The clueless engineers CE

Dramatis personae
An elephant on steroids PG

Dramatis personae
The big cheese HW

Dramatis personae
The real hero DBA

In the beginning
Our story starts in the year 2012. The world was young and our DBA started a
new brilliant career in ACME.
After the usual time required by the onboarding, to our DBA were handed the
production’s servers.

2012 - Who am I? What I’m doing?
Table of contents
5 2016 - Breathe
6 Wrap up

Size does matter
PG, our powerful and friendly elephant was used for storing the data in a multi
shard conﬁguration.
Not really big actually but very troubled indeed!
A small logger database - 50 GB
A larger conﬁguration and auth datababase - 200 GB
Two archive db - 4 TB each
One db for the business intelligence - 2 TB
Each db had an hot standby counterpart hosted on less powerful HW.
Our story tells the life of the BI database.

The carnival of monsters
In the early 2013 our brave DBA addressed the several problems found on the
current backup and recovery conﬁguration.

Lagging standby
Suboptimal schema.
Churn on large tables and high wal generation rate
The slave lagged just because there was autovacuum running
rsync used in archive command.
The wal were archived over the network using rsync+ssh
The *.ready ﬁles in the pg xlog increased the risk of the cluster’s crash.

Base backup
Rudimentary init standby script.
Just a pg start backup call followed by a rsync between the master and the
slave
The several tablespaces were synced using a single rsync process with the
–delete option

Slow dump
Remote pg dump.
Each cluster was dumped remotely on a separate server using the custom
format.
The backup server had limited memory and cpu
Dump time between 3 hours and 2 days depending on the database size
The BI database was dumped on a daily basis, taking 14/18 hours.

2013 - On thin ice
Table of contents
5 2016 - Breathe
6 Wrap up

2013 - On thin ice
2013 - On thin ice

2013 - On thin ice
Parallel rsync
Our DBA took the baby step approach. He started fixing one issue at a time
without affecting ACME’s activity.
The first enhancement was the init backup script.
Two bash arrays listed the origin and destination’s tablespaces
An empty bash array stored the rsync pids
The script started the pg start backup
For each tablespace a rsync process were spawned and the pid was stored in
the third array
A loop checked that the pids were present in the process list
When all the rsync finished pg stop backup were executed
An email to DBA was sent to tell him to start the slave

2013 - On thin ice
Local archive
The high rate of wal generation required a diﬀerent archive strategy.
The archive command changed to a local copy
A simple rsync script copied every minute the archives to the slave
The script queried remotely the slave for the last restartpoint
The restartpoint was used by pg archivecleanup on the master
Implementing this solution solved the *.ready ﬁles problem but the autovacuum
still caused high lag.

2013 - On thin ice
Autovacuum tune down
DBA investigated the autovacuum issue and ﬁnally addressed the cause.
The high lag on the slave was caused when autovacuum (or vacuum) hit a table
concurrently updated. This behaviour is normal and is caused by the standby
code’s design.
With large denormalised tables which are updated constantly the only workaround
possible was to use the autovacuum cost based with a large naptime.
When the autovacuum process reached an arbitrary cost during the execution
there was a 1 minute sleep before the activity resumed.
.
The lag on the standbys disappeared at the cost of longer autovacuum runs.

2013 - On thin ice
In the meanwhile...
The CE decided to shard the business intelligence database using the hot
standby copy
The three new databases initially had the same amount of data which was
slowly cleaned up later
But even with one third of data on each shard, the daily dump was really
slow at the point of overlapping over the 24 hours

2013 - On thin ice
A slowish dump
pg dump connects to the running cluster like any other backend; it pulls out
data using in the copy format
With the custom format the compression happens on the server where
pg dump runs
The backup server were hammered on the network and the cpu

2013 - On thin ice
You got speed!
Our DBA wrote a bash script doing the following steps
Dump the database in custom format locally
Generate the file’s md5 checksum
Ship the file on the backup via rsync
Check the remote file’s md5
Send a message to nagios for success or failure
The backup time per each cluster dropped dramatically to just 5 hours including
the copy and the checksum verification.

2013 - On thin ice
Growing pains
Despite the business growth the CE ignored the problems with the poor schema
design.
Speed was achieved by brute force using expensive SSD storage
The amount of data store in the BI db increased
The only accepted solution was to create new shards over and over again
By the end of 2013 the BI databases total size was 15 TB

2013 - On thin ice
In the meanwhile...
Our DBA upgraded all the PG to the version 9.2 with pg upgrade
THANKS BRUCE!!!!!

2014 - The battle of ﬁve armies
Table of contents
5 2016 - Breathe
6 Wrap up

Bloating data
Q1 2014 opened with another backup performance issue
The dump size increased over the time
The database CPU usage increased constantly with no apparent reason
Most of the shards had the tablespace usage at 90%

Mostly harmless
Against all odds our DBA addressed the issue.
The table used by the BI database was causing the bloat
The table’s design was technically a materalised view
The table was partitioned in some way

Mostly harmless
The table had an harmless hstore ﬁeld
Where everybody added new keys just changing the app code
And nobody did housekeeping of their data
The row length jumped from 200 bytes to 1200 bytes in few months

Mostly harmless
The table had an harmless hstore ﬁeld
Where everybody added new keys just changing the app code
And nobody did housekeeping of their data
The row length jumped from 200 bytes to 1200 bytes in few months
Each BI shard contained up to 2 billion rows...

Just a little trick
Despite the impending doom and the CE resistance DBA succeeded in converting
the hstore ﬁeld to a conventional columnar store (SORRY OLEG!).
The storage usage dropped by 30%
The CPU usage dropped by 60%
The speed of ACME’s product boosted
ACME saved $BIG BUNCH OF MONEY in new HW otherwise required to
shard again the dying databases

In the meanwhile...
DBA knew the ﬁx was just a workaround
He asked the CE to help him in the schema redesign
He told them things would be problematic again in just one year
Nobody listened

2015 - THIS IS SPARTA!
Table of contents
5 2016 - Breathe
6 Wrap up

I hate to say that, but I told you so
As predicted by our DBA the time required for backing up the BI databases
increased again, approaching dangerously the 24 hours.

Parallel is da way!
Back in the 2013 PG 9.3 added the parallel export. But, to DBA great
disappointment, the version 9.3 was initially cursed by some bugs causing data
corruption. DBA could not use the parallel dump.
However...
The parallel backup takes advantage of the snapshot export introduced in the
PG 9.2
The debian packaging allows diﬀerent PG’s major versions on the same
machine
DBA installed the client 9.3 and used its pg dump to dump the 9.2 in parallel

It worked very well...
The wrapper script required some adjustments
Accept the -j parameter
Check if the 9.3+ client is installed
Override the format to directory if the parallel backup is possible
Adapt the checksum procedure to check the ﬁles in the dump directory

...with just a little infinitesimal catch
All fine right?
Not exactly
The restore test complained about the unknown parameter lock timeout
The backup hit the speed record since 2013
The schema was still the same of 2013
The databases performance were massively affected with 6 parallel jobs
DBA found that with just 4 parallel jobs the databases worked with minimal
disruption

In the meanwhile...
Our DBA upgraded PG to the latest version 9.4.
THANK YOU AGAIN BRUCE!!!!!
No more errors for the restore test.

2016 - Breathe
Table of contents
5 2016 - Breathe
6 Wrap up

2016 - Breathe
2016 - Breathe

2016 - Breathe
A new hope
The upgrade to PG 9.4 improved the performance issues and DBA had some time
to breath.
The script to ship the archived wal was improved to support multiple slaves
in cascading replica
Each slave had a dedicated rsync process conﬁgurable with compression and
protocol (rsync or rsync +ssh)
The script determined automatically the farthest slave querying the remote
controlﬁles and cleaned the local archive accordingly

2016 - Breathe
A new hope
The init standby script switched to the rsync protocol
The automated restore script used the ALTER SYSTEM added to the PG 9.4
to switch between the restore and production conﬁguration
Therefore the restore time improved to at most 9 hours for the largest BI
database (4.5 TB)
Working with BOFH JR, DBA wrapped the backup script in the
$BACKUP MANAGER pre and post execution hooks

2016 - Breathe
The rise of the machines
In the 2016 Q2, finally, DBA completed the configuration for $DEVOP TOOL and
deployed the several scripts to the 17 BI databases with minimal effort.

Wrap up
Table of contents
5 2016 - Breathe
6 Wrap up

Wrap up
BI database at a glance
Year N. Databases Average size Total size Version
2012 1 2 TB 2 TB 9.1
2013 5 3 TB 15 TB 9.2
2014 9 2.2 TB 19 TB 9.2
2015 13 2.7 TB 32 TB 9.4
2016 16 2.5 TB 40 TB 9.4

Wrap up
Few words of wisdom
Reading the products source code is always a good practice.
Understanding it is even better.
Bad design can lead to disasters, in particular if the business is successful.
It’s never too early to book the CE to a SQL training course.
If in doubt ask your DBA for advice. And please listen to him.
“One bad programmer can easily create two new jobs a year.” – David Parnas

Wrap up
That’s all folks!
QUESTIONS?

Wrap up
Boring legal stuﬀ
LAPD badge - source wikicommons
Montparnasse derailment - source wikipedia
Base jumper - copyright Chris McNaught
Disaster girl - source memegenerator
Blue elephant - source memecenter
Commodore 64 - source memecenter
Deadpool- source memegenerator
Thin ice - source Boating on Lake Winnebago
Boromir - source memegenerator
Sparta birds - source memestorage
Dart Vader - source memegenerator
Angry old man - source memegenerator

Wrap up
Contacts and license
Twitter: 4thdoctor scarf
Blog:http://www.pgdba.co.uk
Brighton PostgreSQL Meetup:
http://www.meetup.com/Brighton-PostgreSQL-Meetup/
This document is distributed under the terms of the Creative Commons

Wrap up
Life on a rollercoaster
Scaling the PostgreSQL backup and recovery
Federico Campoli
Brighton PostgreSQL Users Group
9 September 2016

PostgreSQL - backup and recovery with large databases

More Related Content

What's hot

Viewers also liked

Similar to PostgreSQL - backup and recovery with large databases

More from Federico Campoli

Recently uploaded

PostgreSQL - backup and recovery with large databases