On 2 November 2014 05:33, Mike Wilson mfwil...@gmail.com wrote:
Any recommendations would be very helpful.
Try using ionice and renice to increase the priority of the WAL sender
process on the master. If it helps, you are lagging because not enough
resources are being used by the sender process
2014-11-02 19:16 GMT-02:00 Mike Wilson mfwil...@gmail.com:
Thanks for the information Greg.
Unfortunately modifying the application stack this close to the holiday
season won’t be an option so I’m left with:
1) Trying to optimize the settings I have for the query mix I have.
2)
Load on the slave is relatively light. It averages about 1.0 due to some data
ware house select queries running against it frequently. Previously only the
load on the master seems to have affected our replication lag no matter what
the slave was doing.
In thinking about this a bit more,
Thanks for the information Greg.
Unfortunately modifying the application stack this close to the holiday season
won’t be an option so I’m left with:
1) Trying to optimize the settings I have for the query mix I have.
2) Optimize any long running DML queries (if any) to prevent lag due to
I have two 9.3.4 PG instances that back a large internet website that has very
seasonal traffic and can generate large query loads. My instances are in a
master-slave streaming replication setup and are stable and in general perform
very well. The only issues we have with the boxes is that
Hello Mike,
what kind of load does the slave get?
what does the recovery process do on the slave during the times when lag is
being observed? Does it use 100% of the CPU?
WAL can be replayed by only one process, so no need to increase the
max_wal_senders.
Cheers,
-- Valentine Gogichashvili
On Wed, Apr 30, 2008 at 11:09 AM, Gauri Kanekar
[EMAIL PROTECTED] wrote:
relid |relname | n_tup_ins | n_tup_upd | n_tup_hot_upd | n_dead_tup
---++---+---+---+
16461 | table1 | 0 | 8352496 | 5389 |
table1 structure :
idintegernot null
codeintegernot null
cridintegernot null
statuscharacter varying(1)default 'A'::character varying
delta1bigintdefault 0
delta2bigintdefault 0
delta3bigintdefault 0
delta4
On Wed, Apr 30, 2008 at 12:16 PM, Gauri Kanekar
[EMAIL PROTECTED] wrote:
fillfactor is set to 80 as you suggested.
delta* fields r updated and these fields are no where related to any of the
index fields.
That's weird. With that fillfactor, you should have a very high
percentage of HOT update
Gauri Kanekar wrote:
HOT doesn't seems to be working in our case.
This is table1 structure :
idintegernot null
codeintegernot null
cridintegernot null
statuscharacter varying(1)default 'A'::character varying
delta1bigintdefault 0
Heikki Linnakangas wrote:
Did you dump and reload the table after setting the fill factor? It only
affects newly inserted data.
VACUUM FULL or CLUSTER should do the job too, right? After all, they
recreate the table so they must take the fillfactor into account.
--
Craig Ringer
--
Sent
Craig Ringer wrote:
Heikki Linnakangas wrote:
Did you dump and reload the table after setting the fill factor? It
only affects newly inserted data.
VACUUM FULL or CLUSTER should do the job too, right? After all, they
recreate the table so they must take the fillfactor into account.
Please keep list in the loop.
On Wed, Apr 30, 2008 at 6:45 PM, Gauri Kanekar
[EMAIL PROTECTED] wrote:
Hi,
We have recreated the indices with fillfactor set to 80, which has improved
HOT
a little,
Wait. Did you say, you recreated the indexes with fill factor ? That's
no help for HOT. You
Pavan Deolasee [EMAIL PROTECTED] writes:
That's weird. With that fillfactor, you should have a very high
percentage of HOT update ratio. It could be a very special case that
we might be looking at.
He's testing
update table1 set delta1 = 100 where code/100 =999;
so all the rows being
[EMAIL PROTECTED] (Frank Ch. Eigler) writes:
Tom Lane [EMAIL PROTECTED] writes:
Also, you need to make sure you have the FSM parameters set high enough
so that all the free space found by a VACUUM run can be remembered.
Would it be difficult to arrange FSM parameters to be automatically
set
On Wed, Apr 30, 2008 at 8:16 PM, Tom Lane [EMAIL PROTECTED] wrote:
Pavan Deolasee [EMAIL PROTECTED] writes:
That's weird. With that fillfactor, you should have a very high
percentage of HOT update ratio. It could be a very special case that
we might be looking at.
He's testing
It's
We have tried fillfactor for indices and it seems to work.
Need to try fillfactor for table. May for that reason the bulk update
queries don't get the advantage of HOT
:)
On Wed, Apr 30, 2008 at 9:45 PM, Pavan Deolasee [EMAIL PROTECTED]
wrote:
On Wed, Apr 30, 2008 at 8:16 PM, Tom Lane [EMAIL
On Tue, 29 Apr 2008, Gauri Kanekar wrote:
We do vacuum full, as vacuum verbose analyse dont regain space for us.
Ah, now we're getting to the root of your problem here. You expect that
VACUUM should reclaim space.
Whenever you UPDATE a row, it writes a new one out, then switches to use
From most of the reply found that upgrade to higher version of postgres may
be to 8.3.1 may be one of the solution to tackle this problem
Checked about HOT feature in 8.3.1.
Do we need to do any special config changes or any other setting for HOT to
work??
Any special guideline to follow to
Gauri Kanekar wrote:
Andrew,
Can you explain me in detail why u said vacuum full is making the things
worst.
We do vacuum full, as vacuum verbose analyse dont regain space for us.
vacuum full stops all access so that the data files can be re-writen
without the unused space.
normal vacuum
On Tue, Apr 29, 2008 at 4:35 PM, Gauri Kanekar
[EMAIL PROTECTED] wrote:
Do we need to do any special config changes or any other setting for HOT to
work??
No. HOT is enabled by default, on all tables. There is no way and need
to disable it.
Any special guideline to follow to make HOT
Thanx for the help.
Need some more help.
table1 has two indices
unique indx1 = pkfld
unique indx2 = fkfld1,fkfld2
did following steps in the listed order -
1. vacuumed the whole DB
2. table1
RecCnt == 11970789
Size == 2702.41 MB
3.update table1 set fld7 = 1000 where fld1/100 =
Gauri Kanekar escribió:
Do we need to do any special config changes or any other setting for HOT to
work??
No. HOT is always working, if it can. You don't need to configure it.
--
Alvaro Herrerahttp://www.CommandPrompt.com/
The PostgreSQL Company - Command
Gauri Kanekar escribió:
Found that the size increased gradually. Is HOT working over here ??
Guide me if im doing something wrong.
Probably not. Try vacuuming between the updates.
--
Alvaro Herrerahttp://www.CommandPrompt.com/
PostgreSQL Replication,
On Tue, Apr 29, 2008 at 6:29 PM, Gauri Kanekar
[EMAIL PROTECTED] wrote:
Found that the size increased gradually. Is HOT working over here ??
Guide me if im doing something wrong.
You have chosen a bad case for HOT. Since you are repeatedly updating
the same set of rows, the dead space
Thats how our updates works.
We usually tend to touch the same row many times a day.
~ Gauri
On Tue, Apr 29, 2008 at 6:39 PM, Pavan Deolasee [EMAIL PROTECTED]
wrote:
On Tue, Apr 29, 2008 at 6:29 PM, Gauri Kanekar
[EMAIL PROTECTED] wrote:
Found that the size increased gradually. Is HOT
On Tue, Apr 29, 2008 at 6:42 PM, Gauri Kanekar
[EMAIL PROTECTED] wrote:
Thats how our updates works.
We usually tend to touch the same row many times a day.
Then start with a non-100 fillfactor. I would suggest something like
80 and then adjust based on the testing. Since you are anyways have
Pavan Deolasee [EMAIL PROTECTED] writes:
Any special guideline to follow to make HOT working??
You can do couple of things to benefit from HOT.
1. HOT addresses a special, but common case where UPDATE operation
does not change any of the index keys. So check if your UPDATE changes
any of
Greg Smith [EMAIL PROTECTED] writes:
The model here assumes that you'll need that space again for the next time
you UPDATE or INSERT a row. So instead VACUUM just keeps those available
for database reuse rather than returning it to the operating system.
Now, if you don't VACUUM frequently
On Apr 29, 2008, at 10:16 AM, Tom Lane wrote:
Greg Smith [EMAIL PROTECTED] writes:
The model here assumes that you'll need that space again for the
next time
you UPDATE or INSERT a row. So instead VACUUM just keeps those
available
for database reuse rather than returning it to the
[EMAIL PROTECTED] (Gauri Kanekar) writes:
Basically we have some background process which updates table1 and
we don't want the application to make any changes to table1 while
vacuum. Vacuum requires exclusive lock on table1 and if any of
the background or application is ON vacuum don't kick
Alvaro Herrera wrote:
Gauri Kanekar escribió:
Do we need to do any special config changes or any other setting for HOT to
work??
No. HOT is always working, if it can. You don't need to configure it.
Unless you have upgraded since you started this thread you are still
running 8.1.3.
HOT doesn't seems to be working in our case.
This is table1 structure :
idintegernot null
codeintegernot null
cridintegernot null
statuscharacter varying(1)default 'A'::character varying
delta1bigintdefault 0
delta2bigint
On Wed, Apr 30, 2008 at 10:59 AM, Gauri Kanekar
[EMAIL PROTECTED] wrote:
HOT doesn't seems to be working in our case.
Can you please post output of the following query ?
SELECT relid, relname, n_tup_ins, n_tup_upd, n_tup_hot_upd, n_dead_tup
from pg_stat_user_tables WHERE relname = 'table1';
relid |relname | n_tup_ins | n_tup_upd | n_tup_hot_upd | n_dead_tup
---++---+---+---+
16461 | table1 | 0 | 8352496 | 5389 |8351242
On Wed, Apr 30, 2008 at 11:07 AM, Pavan Deolasee [EMAIL PROTECTED]
All,
We have a table table1 which get insert and updates daily in high numbers,
bcoz of which its size is increasing and we have to vacuum it every
alternate day. Vacuuming table1 take almost 30min and during that time the
site is down.
We need to cut down on this downtime.So thought of having a
2008/4/28 Gauri Kanekar [EMAIL PROTECTED]:
All,
We have a table table1 which get insert and updates daily in high
numbers, bcoz of which its size is increasing and we have to vacuum it every
alternate day. Vacuuming table1 take almost 30min and during that time the
site is down.
We need
Peter,
We are doing vacuum full every alternate day. We also do vacuum analyze very
often.
We are currently using 8.1.3 version.
Auto vacuum is already on. But the table1 is so busy that auto vacuum don't
get sufficient chance to vacuum it :(.
Have already tried all the option listed by you,
On Mon, 2008-04-28 at 19:35 +0530, Gauri Kanekar wrote:
Peter,
We are doing vacuum full every alternate day. We also do vacuum
analyze very often.
We are currently using 8.1.3 version.
Auto vacuum is already on. But the table1 is so busy that auto vacuum
don't get sufficient chance to
Gauri Kanekar wrote:
Peter,
We are doing vacuum full every alternate day. We also do vacuum analyze very
often.
We are currently using 8.1.3 version.
Auto vacuum is already on. But the table1 is so busy that auto vacuum don't
get sufficient chance to vacuum it :(.
Have already tried all the
Thats one of the thingsto be done in near future.
But it need some changes from application point of view. :( ... so just got
escalated for that reason.
But for now, which one will be a well suited replication system ?
~ Gauri
On Mon, Apr 28, 2008 at 7:43 PM, Brad Nicholson [EMAIL PROTECTED]
Salman,
Slony don't do automatic failover. And we would appreciate a system with
automatic failover :(
~ Gauri
On Mon, Apr 28, 2008 at 7:46 PM, salman [EMAIL PROTECTED]
wrote:
Gauri Kanekar wrote:
Peter,
We are doing vacuum full every alternate day. We also do vacuum analyze
very
On Mon, Apr 28, 2008 at 07:35:37PM +0530, Gauri Kanekar wrote:
Peter,
We are doing vacuum full every alternate day. We also do vacuum analyze very
often.
VACUUM FULL is making your problem worse, not better. Don't do that.
We are currently using 8.1.3 version.
You need immediately to
On Mon, Apr 28, 2008 at 07:48:48PM +0530, Gauri Kanekar wrote:
Slony don't do automatic failover. And we would appreciate a system with
automatic failover :(
No responsible asynchronous system will give you automatic failover.
You can lose data that way.
A
--
Andrew Sullivan
[EMAIL
On Mon, 28 Apr 2008, Gauri Kanekar wrote:
We are doing vacuum full every alternate day. We also do vacuum analyze
very often. We are currently using 8.1.3 version...Have already tried
all the option listed by you, thats y we reached to the decision of
having a replication sytsem.
Andrew
On Mon, Apr 28, 2008 at 9:38 AM, Gauri Kanekar
[EMAIL PROTECTED] wrote:
All,
We have a table table1 which get insert and updates daily in high numbers,
bcoz of which its size is increasing and we have to vacuum it every
alternate day. Vacuuming table1 take almost 30min and during that time
[EMAIL PROTECTED] (Gauri Kanekar) writes:
We have a table table1 which get insert and updates daily in high
numbers, bcoz of which its size is increasing and we have to vacuum
it every alternate day. Vacuuming table1 take almost 30min and
during that time the site is down. We need to cut down
Basically we have some background process which updates table1 and we
don't want the application to make any changes to table1 while vacuum.
Vacuum requires exclusive lock on table1 and if any of the background or
application is ON vacuum don't kick off. Thats the reason we need to get the
site
But unless we do full vacuum the space is not recovered. Thats y we prefer
full vacuum.
~ Gauri
On Tue, Apr 29, 2008 at 10:38 AM, Greg Smith [EMAIL PROTECTED] wrote:
On Tue, 29 Apr 2008, Gauri Kanekar wrote:
Basically we have some background process which updates table1 and we
don't want
On Tue, 29 Apr 2008, Gauri Kanekar wrote:
Basically we have some background process which updates table1 and we
don't want the application to make any changes to table1 while vacuum.
Vacuum requires exclusive lock on table1 and if any of the background or
application is ON vacuum don't kick
Andrew,
Can you explain me in detail why u said vacuum full is making the things
worst.
We do vacuum full, as vacuum verbose analyse dont regain space for us.
~ Gauri
On Mon, Apr 28, 2008 at 9:52 PM, Andrew Sullivan [EMAIL PROTECTED]
wrote:
On Mon, Apr 28, 2008 at 07:35:37PM +0530, Gauri
On Tue, Apr 29, 2008 at 10:41 AM, Gauri Kanekar
[EMAIL PROTECTED] wrote:
But unless we do full vacuum the space is not recovered. Thats y we prefer
full vacuum.
There is no point in recovering the space by moving tuples and
truncating the relation (that's what VACUUM FULL does) because you are
On Tue, Apr 29, 2008 at 11:16 AM, Gauri Kanekar
[EMAIL PROTECTED] wrote:
Andrew,
Can you explain me in detail why u said vacuum full is making the things
worst.
1. VACUUM FULL takes exclusive lock on the table. That makes table
unavailable for read/writes.
2. VACUUM FULL moves live tuples
Hi,
Andrew Sullivan wrote:
This isn't quite true. Slony-II was originally conceived by Jan as
an attempt to implement some of the Postgres-R ideas.
Oh, right, thanks for that correction.
Part of the problem, as near as I could tell, was that we had no
group communication protocol that
On Mon, Jun 18, 2007 at 08:54:46PM +0200, Markus Schiltknecht wrote:
Postgres-R has been the name of the research project by Bettina Kemme et
al. Slony-II was the name Neil and Gavin gave their attempt to continue
that project.
This isn't quite true. Slony-II was originally conceived by Jan
On Thu, 2007-06-14 at 16:14 -0700, Craig James wrote:
Looking for replication solutions, I find:
Slony-I
Seems good, single master only, master is a single point of failure,
no good failover system for electing a new master or having a failed
master rejoin the cluster. Slave databases
Hi,
Joshua D. Drake wrote:
Slony-II
Seems brilliant, a solid theoretical foundation, at the forefront of
computer science. But can't find project status -- when will it be
available? Is it a pipe dream, or a nearly-ready reality?
Dead
Not quite... there's still Postgres-R, see
Markus Schiltknecht wrote:
Not quite... there's still Postgres-R, see www.postgres-r.org And I'm
continuously working on it, despite not having updated the website for
almost a year now...
I planned on releasing the next development snapshot together with 8.3,
as that seems to be delayed,
Hi,
Craig James wrote:
Is Postgres-R the same thing as Slony-II? There's a lot of info and
news around about Slony-II, but your web page doesn't seem to mention it.
Hm... true. Good point. Maybe I should add a FAQ:
Postgres-R has been the name of the research project by Bettina Kemme et
On 6/15/07, Craig James [EMAIL PROTECTED] wrote:
I don't think I can use PGPool as the replicator, because then it becomes a new
single point of failure that could bring the whole system down. If you're
using it for INSERT/UPDATE, then there can only be one PGPool server.
Are you sure? I
On 6/14/07, Craig A. James [EMAIL PROTECTED] wrote:
Looking for replication solutions, I find:
Slony-I
Seems good, single master only, master is a single point of failure,
no good failover system for electing a new master or having a failed
master rejoin the cluster. Slave databases are
Hello,
On Thu, 2007-06-14 at 16:14 -0700, Craig James wrote:
Cluster
Seems pretty good, but web site is not current,
http://www.pgcluster.org is a bit up2date, also
http://pgfoundry.org/projects/pgcluster is up2date (at least downloads
page :) )
Regards,
--
Devrim GÜNDÜZ
PostgreSQL
On Thu, 14 Jun 2007 17:38:01 -0700
Craig James [EMAIL PROTECTED] wrote:
I would consider PGCluster, but it seems to be a patch to Postgres
itself. I'm reluctant to introduce such a major piece of technology
Yes it is. For most of the time it is not very much behind actual
versions of
Looking for replication solutions, I find:
Slony-I
Seems good, single master only, master is a single point of failure,
no good failover system for electing a new master or having a failed
master rejoin the cluster. Slave databases are mostly for safety or
for parallelizing queries for
Craig James wrote:
Looking for replication solutions, I find:
Slony-I
Seems good, single master only, master is a single point of failure,
no good failover system for electing a new master or having a failed
master rejoin the cluster. Slave databases are mostly for safety or
for parallelizing
Which replication problem are you trying to solve?
On Thu, 14 Jun 2007, Craig James wrote:
Looking for replication solutions, I find:
Slony-I
Seems good, single master only, master is a single point of failure,
no good failover system for electing a new master or having a failed
master rejoin
On 6/15/07, Craig James [EMAIL PROTECTED] wrote:
[snip]
Is this a good summary of the status of replication? Have I missed any
important solutions or mischaracterized anything?
* Mammoth Replicator, commercial.
* Continuent uni/cluster, commercial
On Thu, Jun 14, 2007 at 6:14 PM, in message [EMAIL PROTECTED],
Craig James [EMAIL PROTECTED] wrote:
Looking for replication solutions, I find:
Slony-I
Slony-II
PGReplication
PGCluster
You wouldn't guess it from the name, but pgpool actually supports replication:
Thanks to all who replied and filled in the blanks. The problem with the web
is you never know if you've missed something.
Joshua D. Drake wrote:
Looking for replication solutions, I find...
Slony-II
Dead
Wow, I'm surprised. Is it dead for lack of need, lack of resources, too
complex, or
Looking for replication solutions, I find:
Slony-I
Seems good, single master only, master is a single point of failure,
no good failover system for electing a new master or having a failed
master rejoin the cluster. Slave databases are mostly for safety or
for parallelizing queries for
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
Most of our data is replicated offline using custom tools tailored to
our loading pattern, but we have a small amount of global information,
such as user signups, system configuration, advertisements, and such,
that go into a single small (~5-10
Andreas Kostyrka wrote:
Slony provides near instantaneous failovers (in the single digit seconds
range). You can script an automatic failover if the master server
becomes unreachable.
But Slony slaves are read-only, correct? So the system isn't fully functional
once the master goes down.
Craig James wrote:
Andreas Kostyrka wrote:
Slony provides near instantaneous failovers (in the single digit seconds
range). You can script an automatic failover if the master server
becomes unreachable.
But Slony slaves are read-only, correct? So the system isn't fully
functional once the
What about Daffodil Replicator - GPL -
http://sourceforge.net/projects/daffodilreplica/
--
Thanks,
Eugene Ogurtsov
Internal Development Chief Architect
SWsoft, Inc.
Craig A. James wrote:
Looking for replication solutions, I find:
Slony-I
Seems good, single master only, master is a
different. one is for all kinds of
scheduled maintenance, while the other is what you do when you've got a
hardware failure.
Andreas
-- Ursprüngl. Mitteil. --
Betreff:Re: [PERFORM] Replication
Von:Craig James [EMAIL PROTECTED]
Datum: 15.06.2007 01:48
Andreas Kostyrka wrote:
Slony
One more point for your list:
Choose Slony if Replicator doesn't support your platform. :-)
--
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
Vivek Khera, Ph.D.Khera Communications, Inc.
Internet: [EMAIL PROTECTED] Rockville, MD +1-301-869-4449
Once again, Joshua, would you please explain what you mean with
batch and live replication system? Slony does group multiple
master transactions into one replication transaction to improve
performance (fewer commits on the slaves). The interval of these
groups is configurable and for high
Centuries ago, Nostradamus foresaw when [EMAIL PROTECTED] (Joshua D. Drake) would
write:
I hope you understand that I, in no way have ever suggested
(purposely) anything negative about Slony. Only that I believe they
serve different technical solutions.
Stipulating that I may have some bias
Christopher Browne wrote:
Centuries ago, Nostradamus foresaw when [EMAIL PROTECTED] ("Joshua D. Drake") would write:
I hope you understand that I, in no way have ever suggested
(purposely) anything negative about Slony. Only that I believe they
serve different technical solutions.
Chris Cheston wrote:
HI all, I'm trying to implement a highly-scalable, high-performance,
real-time database replication system to back-up my Postgres database
as data gets written.
So far, Mammoth Replicator is looking pretty good but it costs $1000+ .
Yes but it includes 30 days of support and
80 matches
Mail list logo
