On 2 November 2014 05:33, Mike Wilson 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 (rather than the
2014-11-02 19:16 GMT-02:00 Mike Wilson :
> 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 r
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
l
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, the
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
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 wh
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 <[EMAI
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 t
[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 automaticall
"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
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.
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.
CLUSTER
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 via
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
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 u
"table1" structure :
idintegernot null
codeintegernot null
cridintegernot null
statuscharacter varying(1)default 'A'::character varying
delta1bigintdefault 0
delta2bigintdefault 0
delta3bigintdefault 0
delta
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 |83
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]>
wrot
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';
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
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.
HO
[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 d
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 opera
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 freque
"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 chan
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 h
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 graduall
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
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, Consult
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
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
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
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 w
>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 ma
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
th
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 tupl
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 a
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 Ka
"Gauri Kanekar" <[EMAIL PROTECTED]> writes:
> 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 down.
As has been pointed out to you repeatedly, "vacuum" hasn't required
exclusive lock si
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 kic
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
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
s
[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
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 th
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 Sull
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 PROTECT
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
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
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]>
w
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 opt
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
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, that
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.
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 havin
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 would
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 J
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 databa
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
al.
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, th
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 www.postgre
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 postg
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 Replic
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
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 h
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 Kos
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 sing
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 m
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.
T
-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
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 perfo
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
>>> 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:
http://pg
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
(http://www.continuent.com/index.php?optio
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
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 q
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 performan
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 x806
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.
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 bi
On 8/14/2004 12:22 PM, Joshua D. Drake wrote:
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.
You know I never took anything you said negative. I think People here
need to know th
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 hig
On 8/13/2004 9:39 PM, Joshua D. Drake wrote:
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+ .
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
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+ .
Has anyone tried Slony-I and other replication systems? Slony
A long time ago, in a galaxy far, far away, "Bupp Phillips" <[EMAIL PROTECTED]> wrote:
>I have a table that has 103,000 records in it (record size is about
>953 bytes) and when I do a select all (select * from ) it takes
>a whopping 30 secs for the data to return!!
>SQLServer on the other hand ta
85 matches
Mail list logo
