Re: [PERFORM] BUG #2784: Performance serious degrades over a period
Bruno Wolff III <[EMAIL PROTECTED]> wrote: > > This really should have been asked on pgsql-performance and would probably > get a better response there.. > > On Sun, Nov 26, 2006 at 16:35:52 +, > Michael Simms <[EMAIL PROTECTED]> wrote: > > PostgreSQL version: 8.1.4 > > Operating system: Linux kernel 2.6.12 > > Description:Performance serious degrades over a period of a month > > Details: > > > > OK, we have a database that runs perfectly well after a dump and restore, > > but over a period of a month or two, it just degrades to the point of > > uselessness. > > vacuumdb -a is run every 24 hours. We have also run for months at a time > > using -a -z but the effect doesnt change. > > > > This sounds like you either need to increase your FSM setting or vacuum > more often. I think vacuumdb -v will give you enough information to tell > if FSM is too low at the frequency you are vacuuming. > > > The database is for a counter, not the most critical part of the system, but > > a part of the system nonetheless. Other tables we have also degrade over > > time, but the counter is the most pronounced. There seems to be no common > > feature of the tables that degrade. All I know is that a series of queries > > that are run on the database every 24 hours, after a dump/restore takes 2 > > hours. Now, 2 months after, it is taking over 12. We are seriously > > considering switching to mysql to avoid this issue. > > You probably will want to vacuum the counter table more often than the other > tables in the database. Depending on how often the counter(s) are being > updated and how many separate counters are in the table you might want to > vacuum that table as often as once a minute. > > Depending on your requirements you might also want to consider using a > sequence > instead of a table row for the counter. Just to throw it in to the mix: you might also be in a usage pattern that would benefit from a scheduled reindex every so often. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PERFORM] Postgres scalability and performance on windows
Tom,
This is the query and the schema
Query is :
SELECT subq.percentCover, ds.datasetname, ds.maxresolution
FROM
(
select
sum(area(intersection(snaptogrid(chunkgeometry,0.0001),
GeometryFromText('POLYGON((-0.140030845589332
50.8208343077265,-0.138958398039148 50.8478005422809,-0.0963639712296823
50.8471133071392,-0.0974609286275892 50.8201477285483,-0.140030845589332
50.8208343077265))',4326))) * 100/ (0.00114901195862628)) as
percentCover,
datasetid as did from
tbl_metadata_chunks
where chunkgeometry &&
GeometryFromText('POLYGON((-0.140030845589332
50.8208343077265,-0.138958398039148 50.8478005422809,-0.0963639712296823
50.8471133071392,-0.0974609286275892 50.8201477285483,-0.140030845589332
50.8208343077265))',4326)
and datasetid in (select datasetid from
tbl_metadata_dataset where typeofdataid=1)
group by did
order by did desc
)
AS subq INNER JOIN tbl_metadata_dataset AS
ds ON subq.did = ds.datasetid
ORDER by ceil(subq.percentCover),1/ds.maxresolution
DESC;
Schema is
Table 1
CREATE TABLE public.tbl_metadata_dataset
(
datasetname varchar(70) NOT NULL,
maxresolution real,
typeofdataid integer NOT NULL,
datasetid serial NOT NULL,
CONSTRAINT "PK_Dataset" PRIMARY KEY (datasetid)
);
-- Indexes
CREATE INDEX dsnameindex ON tbl_metadata_dataset USING btree
(datasetname);-- Owner
ALTER TABLE public.tbl_metadata_dataset OWNER TO postgres;
-- Triggers
CREATE CONSTRAINT TRIGGER "RI_ConstraintTrigger_2196039" AFTER DELETE ON
tbl_metadata_dataset FROM tbl_metadata_chunks NOT DEFERRABLE INITIALLY
IMMEDIATE FOR EACH ROW EXECUTE PROCEDURE "RI_FKey_noaction_del"('dsid',
'tbl_metadata_chunks', 'tbl_metadata_dataset', 'UNSPECIFIED',
'datasetid', 'datasetid');
CREATE CONSTRAINT TRIGGER "RI_ConstraintTrigger_2196040" AFTER UPDATE ON
tbl_metadata_dataset FROM tbl_metadata_chunks NOT DEFERRABLE INITIALLY
IMMEDIATE FOR EACH ROW EXECUTE PROCEDURE "RI_FKey_noaction_upd"('dsid',
'tbl_metadata_chunks', 'tbl_metadata_dataset', 'UNSPECIFIED',
'datasetid', 'datasetid');
Table 2
CREATE TABLE public.tbl_metadata_chunks
(
chunkid serial NOT NULL,
chunkgeometry geometry NOT NULL,
datasetid integer NOT NULL,
CONSTRAINT tbl_metadata_chunks_pkey PRIMARY KEY (chunkid),
CONSTRAINT dsid FOREIGN KEY (datasetid) REFERENCES
tbl_metadata_dataset(datasetid)
);
-- Indexes
CREATE INDEX idx_dsid ON tbl_metadata_chunks USING btree (datasetid);
CREATE UNIQUE INDEX tbl_metadata_chunks_idx2 ON tbl_metadata_chunks
USING btree (nativetlx, nativetly, datasetid);
CREATE INDEX tbl_metadata_chunks_idx3 ON tbl_metadata_chunks USING gist
(chunkgeometry);-- Owner
ALTER TABLE public.tbl_metadata_chunks OWNER TO postgres;
-- Triggers
CREATE CONSTRAINT TRIGGER "RI_ConstraintTrigger_2194515" AFTER DELETE ON
tbl_metadata_chunks FROM tbl_metadata_chunkinfo NOT DEFERRABLE INITIALLY
IMMEDIATE FOR EACH ROW EXECUTE PROCEDURE "RI_FKey_restrict_del"('fk',
'tbl_metadata_chunkinfo', 'tbl_metadata_chunks', 'UNSPECIFIED',
'chunkid', 'chunkid');
CREATE CONSTRAINT TRIGGER "RI_ConstraintTrigger_2194516" AFTER UPDATE ON
tbl_metadata_chunks FROM tbl_metadata_chunkinfo NOT DEFERRABLE INITIALLY
IMMEDIATE FOR EACH ROW EXECUTE PROCEDURE "RI_FKey_restrict_upd"('fk',
'tbl_metadata_chunkinfo', 'tbl_metadata_chunks', 'UNSPECIFIED',
'chunkid', 'chunkid');
CREATE CONSTRAINT TRIGGER "RI_ConstraintTrigger_2196037" AFTER INSERT ON
tbl_metadata_chunks FROM tbl_metadata_dataset NOT DEFERRABLE INITIALLY
IMMEDIATE FOR EACH ROW EXECUTE PROCEDURE "RI_FKey_check_ins"('dsid',
'tbl_metadata_chunks', 'tbl_metadata_dataset', 'UNSPECIFIED',
'datasetid', 'datasetid');
CREATE CONSTRAINT TRIGGER "RI_ConstraintTrigger_2196038" AFTER UPDATE ON
tbl_metadata_chunks FROM tbl_metadata_dataset NOT DEFERRABLE INITIALLY
IMMEDIATE FOR EACH ROW EXECUTE PROCEDURE "RI_FKey_check_upd"('dsid',
'tbl_metadata_chunks', 'tbl_metadata_dataset', 'UNSPECIFIED',
'datasetid', 'datasetid');
-Original Message-
From: Frank Wiles [mailto:[EMAIL PROTECTED]
Sent: 24 November 2006 17:05
To: Guido Neitzer
Cc: Gopal; pgsql-performance@postgresql.org
Subject: Re: [PERFORM] Postgres scalability and performance on windows
On Fri, 24 Nov 2006 09:22:45 +0100
Guido Neitzer <[EMAIL PROTECTED]> wrote:
> > effective_cache_size = 82728 # typically 8KB each
> Hmm. I don't know what the real effect of this might be as the doc
> states:
>
> "This parameter has no effect on the size of shared memory allocated
> by PostgreSQL, nor does it reserve kernel disk cache; it is used
> only for estimation purposes."
This is a hint to the optimizer about how much of the database may
be in the OS level cache.
-
Frank Wiles <[EMAIL PROTECTED]>
http://
Re: [PERFORM] Postgres scalability and performance on windows
"Gopal" <[EMAIL PROTECTED]> writes:
> This is the query and the schema
> ...
> select
> sum(area(intersection(snaptogrid(chunkgeometry,0.0001),
> GeometryFromText('POLYGON((-0.140030845589332
> 50.8208343077265,-0.138958398039148 50.8478005422809,-0.0963639712296823
> 50.8471133071392,-0.0974609286275892 50.8201477285483,-0.140030845589332
> 50.8208343077265))',4326))) * 100/ (0.00114901195862628)) as
> percentCover,
So evidently area(intersection(snaptogrid(...))) takes about 300
microsec per row. The PostGIS hackers would have to comment on whether
that seems out-of-line or not, and whether you can make it faster.
regards, tom lane
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
Re: [PERFORM] Postgres scalability and performance on windows
On Nov 28, 2006, at 8:24 AM, Tom Lane wrote:
"Gopal" <[EMAIL PROTECTED]> writes:
This is the query and the schema
...
select
sum(area(intersection(snaptogrid(chunkgeometry,0.0001),
GeometryFromText('POLYGON((-0.140030845589332
50.8208343077265,-0.138958398039148
50.8478005422809,-0.0963639712296823
50.8471133071392,-0.0974609286275892
50.8201477285483,-0.140030845589332
50.8208343077265))',4326))) * 100/ (0.00114901195862628)) as
percentCover,
So evidently area(intersection(snaptogrid(...))) takes about 300
microsec per row. The PostGIS hackers would have to comment on
whether
that seems out-of-line or not, and whether you can make it faster.
This is consistent with the typical cost for GIS geometry ops -- they
are relatively expensive. When running queries against PostGIS
fields for our apps, about half the CPU time will be spent inside the
geometry ops. Fortunately, there is significant opportunity for
improvement in the performance of the underlying code if anyone found
the time to optimize (and uglify) it for raw speed.
Cheers,
J. Andrew Rogers
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
RES: [PERFORM] Priority to a mission critical transaction
Hi, There is an article about "Lowering the priority of a PostgreSQL query" (http://weblog.bignerdranch.com/?p=11) that explains how to use the setpriority() to lower PostgreSQL processes. I?m wondering how much effective it would be for i/o bound systems. Will the setpriority() system call affect i/o queue too? Reimer > -Mensagem original- > De: Jim C. Nasby [mailto:[EMAIL PROTECTED] > Enviada em: domingo, 26 de novembro de 2006 22:52 > Para: Brad Nicholson > Cc: [EMAIL PROTECTED]; pgsql-performance@postgresql.org > Assunto: Re: [PERFORM] Priority to a mission critical transaction > > > On Thu, Nov 23, 2006 at 03:40:15PM -0500, Brad Nicholson wrote: > > On Tue, 2006-11-21 at 21:43 -0200, Carlos H. Reimer wrote: > > > Hi, > > > > > > We have an application that is mission critical, normally very fast, > > > but when an I/O or CPU bound transaction appears, the mission critical > > > application suffers. Is there a way go give some kind of priority to > > > this kind of application? > > > Reimer > > > > > > Not that I'm aware of. Depending on what the problems transactions are, > > setting up a replica on a separate machine and running those > > transactions against the replica might be the solution. > > The BizGres project has been working on resource quotas, which might > eventually evolve to what you're looking for. > -- > Jim Nasby[EMAIL PROTECTED] > EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) > > ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: RES: [PERFORM] Priority to a mission critical transaction
"Carlos H. Reimer" <[EMAIL PROTECTED]> writes: > There is an article about "Lowering the priority of a PostgreSQL query" > (http://weblog.bignerdranch.com/?p=11) that explains how to use the > setpriority() to lower PostgreSQL processes. > I?m wondering how much effective it would be for i/o bound systems. That article isn't worth the electrons it's written on. Aside from the I/O point, there's a little problem called "priority inversion". See the archives for (many) past discussions of nice'ing backends. regards, tom lane ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: RES: [PERFORM] Priority to a mission critical transaction
* Carlos H. Reimer <[EMAIL PROTECTED]> [061128 20:02]: > Hi, > > There is an article about "Lowering the priority of a PostgreSQL query" > (http://weblog.bignerdranch.com/?p=11) that explains how to use the > setpriority() to lower PostgreSQL processes. > > I?m wondering how much effective it would be for i/o bound systems. > > Will the setpriority() system call affect i/o queue too? Nope, and in fact the article shows the way not to do it. See http://en.wikipedia.org/wiki/Priority_inversion Basically, lowering the priority of one backend in PostgreSQL can lead to reduced performance of all, especially also the backends with higher priorities. (Think of priority inversion as a timed soft deadlock. It will eventually resolve, because it's not a real deadlock, but it might mean halting important stuff for quite some time.) Taking the example above, consider the following processes and nice values: 19x backends As nice = 0 1x backend B nice = 10 (doing maintenance work) 1x updatedb nice = 5 (running as a cronjob at night) Now, it possible (the probability depends upon your specific situation), where backend B grabs some internal lock that is needed, and then it gets preempted by higher priority stuff. Well, the A backends need that lock too, so they cannot run; instead we wait till updatedb (which updates the locate search db, and goes through the whole filesystem of the server) is finished. Luckily most if not all of these processes are disc io bound, so they get interrupted any way, and low priority processes don't starve. Well, replace updatedb with something hogging the CPU, and rethink the situation. Andreas ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: RES: [PERFORM] Priority to a mission critical transaction
All, The Bizgres project is working on resource management for PostgreSQL. So far, however, they have been able to come up with schemes that work for BI/DW at the expense of OLTP. Becuase of O^N lock checking issues, resource management for OLTP which doesn't greatly reduce overall performance seems a near-impossible task. -- Josh Berkus PostgreSQL @ Sun San Francisco ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PERFORM] RES: Priority to a mission critical transaction
Short summary: * Papers studying priority inversion issues with databases including PosgreSQL and realistic workloads conclude setpriority() helps even in the presence of priority inversion issues for TCP-C and TCP-W like workloads. * Avoiding priority inversion with priority inheritance will further help some workloads (TCP-C) more than others (TCP-W) but even without such schedulers priority inversion does not cause as much harm as the benefit you get from indirectly scheduling I/O through setpriority() in any paper I've seen. Andreas Kostyrka wrote: > * Carlos H. Reimer <[EMAIL PROTECTED]> [061128 20:02]: >> Will the setpriority() system call affect i/o queue too? > > Nope, and in fact the article shows the way not to do it. Actually *YES* setpriority() does have an indirect effect on the I/O queue. This paper: http://www.cs.cmu.edu/~bianca/icde04.pdf studies setpriority() with non-trivial (TCP-W and TCP-C) workloads on a variety of databases and shows that that setpriority() is *extremely* effective for PostgreSQL. "For TPC-C on MVCC DBMS, and in particular PostgreSQL, CPU scheduling is most effective, due to its ability to indirectly schedule the I/O bottleneck. For TPC-C running on PostgreSQL, the simplest CPU scheduling policy (CPU-Prio) provides a factor of 2 improvement for high-priority transactions, while adding priority inheritance (CPU-Prio-Inherit) provides a factor of 6 improvement while hardly penalizing low-priority transactions. Preemption (P-CPU) provides no appreciable benefit over CPU-Prio-Inherit." > See http://en.wikipedia.org/wiki/Priority_inversion Priority Inversion is a well studied problem; and depends on both the workload and the database. In particular, TPC-W workloads have been studied on a variety of databases including PostgreSQL. Again, from: http://www.cs.cmu.edu/~bianca/icde04.pdf They observe that avoiding priority inversion issues by enabling priority inheritance with PostgreSQL has a negligible effect on TCP-W like workloads, but a significant improvement on TCP-C like workloads. "Recall from Section 5.3 that CPU scheduling (CPUPrio) is more effective than NP-LQ for TPC-W. Thus Figure 8 compares the policies CPU-Prio-Inherit to CPU-Prio for the TPC-W workload on PostgreSQL. We find that there is no improvement for CPU-Prio- Inherit over CPU-Prio. This is to be expected given the low data contention found in the TPC-W workload; priority inversions can only occur during data contention. Results for low-priority transactions are not shown, but as in Figure 4, low-priority transactions are only negligibly penalized on average." Yes, theoretically priority inversion can have pathologically bad effects (not unlike qsort), it affects some workloads more than others. But in particular, their paper concludes that PostgreSQL with TCP-C and TCP-W like workloads gain significant benefits and no drawbacks from indirectly tuning I/O scheduling with setpriority(). If anyone has references to papers or studies that suggest that priority inversion actually is a problem with RDBMS's - and PostgreSQL on Linux in particular, I'd be very interested. Otherwise it seems to me existing research points to significant benefits with only theoretical drawbacks in pathological cases. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: RES: [PERFORM] Priority to a mission critical transaction
Someone should ask them to remove the article. --- Tom Lane wrote: > "Carlos H. Reimer" <[EMAIL PROTECTED]> writes: > > There is an article about "Lowering the priority of a PostgreSQL query" > > (http://weblog.bignerdranch.com/?p=11) that explains how to use the > > setpriority() to lower PostgreSQL processes. > > > I?m wondering how much effective it would be for i/o bound systems. > > That article isn't worth the electrons it's written on. Aside from the > I/O point, there's a little problem called "priority inversion". See > the archives for (many) past discussions of nice'ing backends. > > regards, tom lane > > ---(end of broadcast)--- > TIP 7: You can help support the PostgreSQL project by donating at > > http://www.postgresql.org/about/donate -- Bruce Momjian [EMAIL PROTECTED] EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PERFORM] RES: Priority to a mission critical transaction
Before asking them to remove it, are we sure priority inversion is really a problem? I thought this paper: http://www.cs.cmu.edu/~bianca/icde04.pdf did a pretty good job at studying priority inversion on RDBMs's including PostgreSQL on various workloads (TCP-W and TCP-C) and found that the benefits of setting priorities vastly outweighed the penalties of priority inversion across all the databases and all the workloads they tested. Bruce Momjian wrote: > Someone should ask them to remove the article. > > --- > > Tom Lane wrote: >> "Carlos H. Reimer" <[EMAIL PROTECTED]> writes: >>> There is an article about "Lowering the priority of a PostgreSQL query" >>> (http://weblog.bignerdranch.com/?p=11) that explains how to use the >>> setpriority() to lower PostgreSQL processes. >>> I?m wondering how much effective it would be for i/o bound systems. >> That article isn't worth the electrons it's written on. Aside from the >> I/O point, there's a little problem called "priority inversion". See >> the archives for (many) past discussions of nice'ing backends. >> >> regards, tom lane >> >> ---(end of broadcast)--- >> TIP 7: You can help support the PostgreSQL project by donating at >> >> http://www.postgresql.org/about/donate > ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: RES: [PERFORM] Priority to a mission critical transaction
Josh Berkus wrote: All, The Bizgres project is working on resource management for PostgreSQL. So far, however, they have been able to come up with schemes that work for BI/DW at the expense of OLTP. Becuase of O^N lock checking issues, resource management for OLTP which doesn't greatly reduce overall performance seems a near-impossible task. Right - I guess it is probably more correct to say that the implementation used in Bizgres is specifically targeted at BI/DW workloads rather than OLTP. At this point we have not measured its impact on concurrency in anything other than a handwaving manner - e.g pgbench on an older SMP system showed what looked like about a 10% hit. However the noise level for pgbench is typically >10% so - a better benchmark on better hardware is needed. Cheers Mark ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] RES: Priority to a mission critical transaction
Ron Mayer wrote: Short summary: * Papers studying priority inversion issues with databases including PosgreSQL and realistic workloads conclude setpriority() helps even in the presence of priority inversion issues for TCP-C and TCP-W like workloads. * Avoiding priority inversion with priority inheritance will further help some workloads (TCP-C) more than others (TCP-W) but even without such schedulers priority inversion does not cause as much harm as the benefit you get from indirectly scheduling I/O through setpriority() in any paper I've seen. Andreas Kostyrka wrote: * Carlos H. Reimer <[EMAIL PROTECTED]> [061128 20:02]: Will the setpriority() system call affect i/o queue too? Nope, and in fact the article shows the way not to do it. Actually *YES* setpriority() does have an indirect effect on the I/O queue. While I was at Greenplum a related point was made to me: For a TPC-H/BI type workload on a well configured box the IO subsystem can be fast enough so that CPU is the bottleneck for much of the time - so being able to use setpriority() as a resource controller makes sense. Also, with such a workload being mainly SELECT type queries, the dangers connected with priority inversion are considerably reduced. Cheers Mark ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
