* [linux-lvm] Re: [PERFORM] backup/restore - another area. [not found] <878ynu9ed5.fsf@stark.dyndns.tv> @ 2003-10-14 17:20 ` markw 2003-10-16 7:56 ` Jeff 0 siblings, 1 reply; 6+ messages in thread From: markw @ 2003-10-14 17:20 UTC (permalink / raw) To: threshar; +Cc: pgsql-performance, linux-lvm Jeff, I'm curious to what kind of testing you've done with LVM. I'm not currently trying any backup/restore stuff, but I'm running our DBT-2 workload using LVM. I've started collecting vmstat, iostat, and readprofile data, initially running disktest to gauge the performance. For anyone curious, I have some data on a 14-disk volume here: http://developer.osdl.org/markw/lvm/results.4/log/ and a 52-disk volume here: http://developer.osdl.org/markw/lvm/results.5/data/ Mark >Jeff <threshar@torgo.978.org> writes: > > Idea #1: > Use an LVM and take a snapshop - archive that. > From the way I see it. the downside is the LVM will use a lot of space > until the snapshot is removed. Also PG may be in a slightly inconsistant > state - but this should "appear" to PG the same as if the power went out. > > For restore, simply unarchive this snapshot and point postgres at it. Let > it recover and you are good to go. > > Little overhead from what I see... > I'm leaning towards this method the more I think of it. ^ permalink raw reply [flat|nested] 6+ messages in thread
* [linux-lvm] Re: [PERFORM] backup/restore - another area. 2003-10-14 17:20 ` [linux-lvm] Re: [PERFORM] backup/restore - another area markw @ 2003-10-16 7:56 ` Jeff 2003-10-16 14:06 ` Josh Berkus 0 siblings, 1 reply; 6+ messages in thread From: Jeff @ 2003-10-16 7:56 UTC (permalink / raw) To: markw@osdl.org Cc: pgsql-performance@postgresql.org, linux-lvm@sistina.com, pgsql-admin On Tue, 14 Oct 2003, markw@osdl.org wrote: > I'm curious to what kind of testing you've done with LVM. I'm not > currently trying any backup/restore stuff, but I'm running our DBT-2 > workload using LVM. I've started collecting vmstat, iostat, and > readprofile data, initially running disktest to gauge the performance. > [added -admin to this, since this is very relevant there] I was going to post this data yesterday, but I had severe inet issues. So, I tried this out with lvm2 on 2.4.21 on a 2xp2-450 with 2 disks. (I just looked at your 14 and 52 disk data. drool.) So I have a db which is about 3.2GB on disk. All backups were done to an nfs mount, but I ran a network monitor to check bandwidth usage. I note where things were io bound. backing up: pg_dump: 18m [cpu bound] pg_dump | gzip -1: 18m [cpu bound] snapshot, then tar: 4m [io bound] snapshot, then tar | gzip: 21m [cpu bound] The times for a compressed backup are a bit slower for snapshots, but this is where the snapshot method wins tacos - restore. restore: psql: 158m snapshot: 8m Yes folks, 8m. When I started PG back up it checked the WAL and got itself back online. The benefits of the pg_dump backup afaict are that the data is in a format readable to anything and is [mostly] cross-pg compatible. The downside is it seems to be quite slow and restoring it can be long and tiresome. The benefits of the snapshot are that backups are very, very quick and restore is very, very quick (It won't need to re-enable foriegn keys, no need to rebuild indexes, no need to re-vacuum analyze). The downside is this method will only work on that specific version of PG and it isn't the "cleanest" thing in the world since you are essentially simulating a power failure to PG. Luckly the WAL works like a champ. Also, these backups can be much larger since it has to include the indexes as well. but this is a price you have to pay. I did have some initial problems with snapshots & corruption but it turned out to be user-error on my part. COOL HUH? -- Jeff Trout <jeff@jefftrout.com> http://www.jefftrout.com/ http://www.stuarthamm.net/ ^ permalink raw reply [flat|nested] 6+ messages in thread
* [linux-lvm] Re: [PERFORM] backup/restore - another area. 2003-10-16 7:56 ` Jeff @ 2003-10-16 14:06 ` Josh Berkus 2003-10-16 14:06 ` Jeff 0 siblings, 1 reply; 6+ messages in thread From: Josh Berkus @ 2003-10-16 14:06 UTC (permalink / raw) To: Jeff, markw@osdl.org Cc: pgsql-performance@postgresql.org, linux-lvm@sistina.com, pgsql-admin Jeff, > The downside is > this method will only work on that specific version of PG and it isn't the > "cleanest" thing in the world since you are essentially simulating a power > failure to PG. Luckly the WAL works like a champ. Also, these backups can > be much larger since it has to include the indexes as well. but this is a > price you have to pay. The other downside is, of course, that the database needs to be shut down. > COOL HUH? Certainly very useful in the DBA's arsenal of backup tools. -- Josh Berkus Aglio Database Solutions San Francisco ^ permalink raw reply [flat|nested] 6+ messages in thread
* [linux-lvm] Re: [PERFORM] backup/restore - another area. 2003-10-16 14:06 ` Josh Berkus @ 2003-10-16 14:06 ` Jeff 2003-10-16 14:06 ` Josh Berkus 0 siblings, 1 reply; 6+ messages in thread From: Jeff @ 2003-10-16 14:06 UTC (permalink / raw) To: Josh Berkus; +Cc: markw, pgsql-performance, linux-lvm, pgsql-admin On Thu, 16 Oct 2003 09:49:59 -0700 Josh Berkus <josh@agliodbs.com> wrote: > Jeff, > > > The downside is > > this method will only work on that specific version of PG and it > > isn't the"cleanest" thing in the world since you are essentially > > simulating a power failure to PG. Luckly the WAL works like a champ. > > Also, these backups can be much larger since it has to include the > > indexes as well. but this is a price you have to pay. > > The other downside is, of course, that the database needs to be shut > down. > I left the DB up while doing this. Even had a program sitting around committing data to try and corrupt things. (Which is how I discovered I was doing the snapshot wrong) You could do pg_ctl stop; snapshot; pg_ctls tart for a "clean" image. -- Jeff Trout <jeff@jefftrout.com> http://www.jefftrout.com/ http://www.stuarthamm.net/ ^ permalink raw reply [flat|nested] 6+ messages in thread
* [linux-lvm] Re: [PERFORM] backup/restore - another area. 2003-10-16 14:06 ` Jeff @ 2003-10-16 14:06 ` Josh Berkus 2003-10-16 14:06 ` Jeff 0 siblings, 1 reply; 6+ messages in thread From: Josh Berkus @ 2003-10-16 14:06 UTC (permalink / raw) To: Jeff; +Cc: markw, pgsql-performance, linux-lvm, pgsql-admin Jeff, > I left the DB up while doing this. > > Even had a program sitting around committing data to try and corrupt > things. (Which is how I discovered I was doing the snapshot wrong) Really? I'm unclear on the method you're using to take the snapshot, then; I seem to have missed a couple posts on this thread. Want to refresh me? -- Josh Berkus Aglio Database Solutions San Francisco ^ permalink raw reply [flat|nested] 6+ messages in thread
* [linux-lvm] Re: [PERFORM] backup/restore - another area. 2003-10-16 14:06 ` Josh Berkus @ 2003-10-16 14:06 ` Jeff 0 siblings, 0 replies; 6+ messages in thread From: Jeff @ 2003-10-16 14:06 UTC (permalink / raw) To: Josh Berkus; +Cc: markw, pgsql-performance, linux-lvm, pgsql-admin On Thu, 16 Oct 2003 10:09:27 -0700 Josh Berkus <josh@agliodbs.com> wrote: > Jeff, > > > I left the DB up while doing this. > > > > Even had a program sitting around committing data to try and corrupt > > things. (Which is how I discovered I was doing the snapshot wrong) > > Really? I'm unclear on the method you're using to take the snapshot, > then; I seem to have missed a couple posts on this thread. Want to > refresh me? > I have a 2 disk stripe LVM on /dev/postgres/pgdata/ lvcreate -L4000M -s -n pg_backup /dev/postgres/pgdata mount /dev/postgres/pg_backup /pg_backup tar cf - /pg_backup | gzip -1 > /squeegit/mb.backup umount /pg_backup; lvremove -f /dev/postgres/pg_backup; In a nutshell an LVM snapshot is an atomic operation that takes, well, a snapshot of hte FS as it was at that instant. It does not make a 2nd copy of the data. This way you can simply tar up the pgdata directory and be happy as the snapshot will not be changing due to db activity. -- Jeff Trout <jeff@jefftrout.com> http://www.jefftrout.com/ http://www.stuarthamm.net/ ^ permalink raw reply [flat|nested] 6+ messages in thread
end of thread, other threads:[~2003-10-16 14:06 UTC | newest]
Thread overview: 6+ messages (download: mbox.gz follow: Atom feed
-- links below jump to the message on this page --
[not found] <878ynu9ed5.fsf@stark.dyndns.tv>
2003-10-14 17:20 ` [linux-lvm] Re: [PERFORM] backup/restore - another area markw
2003-10-16 7:56 ` Jeff
2003-10-16 14:06 ` Josh Berkus
2003-10-16 14:06 ` Jeff
2003-10-16 14:06 ` Josh Berkus
2003-10-16 14:06 ` Jeff
This is a public inbox, see mirroring instructions for how to clone and mirror all data and code used for this inbox