Re: [PERFORM] performance hit for replication
-Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Matthew Nuzum Sent: 12 April 2005 17:25 To: pgsql-performance@postgresql.org Subject: [PERFORM] performance hit for replication So, my question is this: My server currently works great, performance wise. I need to add fail-over capability, but I'm afraid that introducing a stressful task such as replication will hurt my server's performance. Is there any foundation to my fears? I don't need to replicate the archived log data because I can easily restore that in a separate step from the nightly backup if disaster occurs. Also, my database load is largely selects. My application works great with PostgreSQL 7.3 and 7.4, but I'm currently using 7.3. If it's possible to upgrade to 8.0 then perhaps you could make use of PITR and continuously ship log files to your standby machine. http://www.postgresql.org/docs/8.0/interactive/backup-online.html I can't help further with this as I've yet to give it a go myself, but others here may have tried it. Regards, Dave. ---(end of broadcast)--- TIP 8: explain analyze is your friend
[PERFORM] performance hit for replication
I'd like to create a fail-over server in case of a problem. Ideally, it would be synchronized with our main database server, but I don't see any major problem with having a delay of up to 4 hours between syncs. My database is a little shy of 10 Gigs, with much of that data being in an archived log table. Every day a batch job is run which adds 100,000 records over the course of 3 hours (the batch job does a lot of pre/post processing). Doing a restore of the db backup in vmware takes about 3 hours. I suspect a powerful server with a better disk setup could do it faster, but I don't have servers like that at my disposal, so I need to assume worst-case of 3-4 hours is typical. So, my question is this: My server currently works great, performance wise. I need to add fail-over capability, but I'm afraid that introducing a stressful task such as replication will hurt my server's performance. Is there any foundation to my fears? I don't need to replicate the archived log data because I can easily restore that in a separate step from the nightly backup if disaster occurs. Also, my database load is largely selects. My application works great with PostgreSQL 7.3 and 7.4, but I'm currently using 7.3. I'm eager to hear your thoughts and experiences, -- Matthew Nuzum [EMAIL PROTECTED] www.followers.net - Makers of Elite Content Management System Earn a commission of $100 - $750 by recommending Elite CMS. Visit http://www.elitecms.com/Contact_Us.partner for details. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PERFORM] performance hit for replication
So, my question is this: My server currently works great, performance wise. I need to add fail-over capability, but I'm afraid that introducing a stressful task such as replication will hurt my server's performance. Is there any foundation to my fears? I don't need to replicate the archived log data because I can easily restore that in a separate step from the nightly backup if disaster occurs. Also, my database load is largely selects. My application works great with PostgreSQL 7.3 and 7.4, but I'm currently using 7.3. I'm eager to hear your thoughts and experiences, Well with replicator you are going to take a pretty big hit initially during the full sync but then you could use batch replication and only replicate every 2-3 hours. I am pretty sure Slony has similar capabilities. Sincerely, Joshua D. Drake ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [PERFORM] performance hit for replication
I'm eager to hear your thoughts and experiences, Well with replicator you are going to take a pretty big hit initially during the full sync but then you could use batch replication and only replicate every 2-3 hours. Sincerely, Joshua D. Drake Thanks, I'm looking at your product and will contact you off list for more details soon. Out of curiosity, does batch mode produce a lighter load? Live updating will provide maximum data security, and I'm most interested in how it affects the server. -- Matthew Nuzum [EMAIL PROTECTED] www.followers.net - Makers of Elite Content Management System Earn a commission of $100 - $750 by recommending Elite CMS. Visit http://www.elitecms.com/Contact_Us.partner for details. ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [PERFORM] performance hit for replication
On Tuesday 12 April 2005 09:25, Matthew Nuzum wrote: I'd like to create a fail-over server in case of a problem. Ideally, it would be synchronized with our main database server, but I don't see any major problem with having a delay of up to 4 hours between syncs. My database is a little shy of 10 Gigs, with much of that data being in an archived log table. Every day a batch job is run which adds 100,000 records over the course of 3 hours (the batch job does a lot of pre/post processing). Doing a restore of the db backup in vmware takes about 3 hours. I suspect a powerful server with a better disk setup could do it faster, but I don't have servers like that at my disposal, so I need to assume worst-case of 3-4 hours is typical. So, my question is this: My server currently works great, performance wise. I need to add fail-over capability, but I'm afraid that introducing a stressful task such as replication will hurt my server's performance. Is there any foundation to my fears? I don't need to replicate the archived log data because I can easily restore that in a separate step from the nightly backup if disaster occurs. Also, my database load is largely selects. My application works great with PostgreSQL 7.3 and 7.4, but I'm currently using 7.3. I'm eager to hear your thoughts and experiences, Your application sounds like a perfact candidate for Slony-I http://www.slony.info . Using Slony-I I see about a 5-7% performance hit in terms of the number of insert.update/delete per second i can process. Depending on your network connection , DML volume, and the power of your backup server, the replica could be as little as 10 seconds behind the origin. A failover/switchover could occur in under 60 seconds. -- Darcy Buskermolen Wavefire Technologies Corp. http://www.wavefire.com ph: 250.717.0200 fx: 250.763.1759 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [PERFORM] performance hit for replication
Matthew Nuzum wrote: I'm eager to hear your thoughts and experiences, Well with replicator you are going to take a pretty big hit initially during the full sync but then you could use batch replication and only replicate every 2-3 hours. Sincerely, Joshua D. Drake Thanks, I'm looking at your product and will contact you off list for more details soon. Out of curiosity, does batch mode produce a lighter load? Well more of a burstier load. You could also do live replication but replicator requires some IO which VMWare just ins't that good at :) Sincerely, Joshua D. Drake ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PERFORM] performance hit for replication
[EMAIL PROTECTED] (Joshua D. Drake) writes: So, my question is this: My server currently works great, performance wise. I need to add fail-over capability, but I'm afraid that introducing a stressful task such as replication will hurt my server's performance. Is there any foundation to my fears? I don't need to replicate the archived log data because I can easily restore that in a separate step from the nightly backup if disaster occurs. Also, my database load is largely selects. My application works great with PostgreSQL 7.3 and 7.4, but I'm currently using 7.3. I'm eager to hear your thoughts and experiences, Well with replicator you are going to take a pretty big hit initially during the full sync but then you could use batch replication and only replicate every 2-3 hours. I am pretty sure Slony has similar capabilities. Yes, similar capabilities, similar pretty big hit. There's a downside to batch replication that some of the data structures grow in size if you have appreciable periods between batches. -- (format nil [EMAIL PROTECTED] cbbrowne acm.org) http://www.ntlug.org/~cbbrowne/slony.html Rules of the Evil Overlord #78. I will not tell my Legions of Terror And he must be taken alive! The command will be: ``And try to take him alive if it is reasonably practical.'' http://www.eviloverlord.com/ ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
