pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedperformance problem
37+ messages / 17 participants
[nested] [flat]
* performance problem
@ 2003-11-18 20:43 Rick Gigger <rick@alpinenetworking.com>
0 siblings, 3 replies; 37+ messages in thread
From: Rick Gigger @ 2003-11-18 20:43 UTC (permalink / raw)
To: PgSQL General ML <pgsql-general@postgresql.org>
I am currently trying to import a text data file without about 45,000
records. At the end of the import it does an update on each of the 45,000
records. Doing all of the inserts completes in a fairly short amount of
time (about 2 1/2 minutes). Once it gets to the the updates though it slows
to a craw. After about 10 minutes it's only done about 3000 records.
Is that normal? Is it because it's inside such a large transaction? Is
there anything I can do to speed that up. It seems awfully slow to me.
I didn't think that giving it more shared buffers would help but I tried
anyway. It didn't help.
I tried doing a analyze full on it (vacuumdb -z -f) and it cleaned up a lot
of stuff but it didn't speed up the updates at all.
I am using a dual 800mhz xeon box with 2 gb of ram. I've tried anywhere
from about 16,000 to 65000 shared buffers.
What other factors are involved here?
^ permalink raw reply [nested|flat] 37+ messages in thread
* Re: performance problem
@ 2003-11-18 21:03 Mike Mascari <mascarm@mascari.com>
parent: Rick Gigger <rick@alpinenetworking.com>
2 siblings, 1 reply; 37+ messages in thread
From: Mike Mascari @ 2003-11-18 21:03 UTC (permalink / raw)
To: Rick Gigger <rick@alpinenetworking.com>; +Cc: PgSQL General ML <pgsql-general@postgresql.org>
Rick Gigger wrote:
> I am currently trying to import a text data file without about 45,000
> records. At the end of the import it does an update on each of the 45,000
> records. Doing all of the inserts completes in a fairly short amount of
> time (about 2 1/2 minutes). Once it gets to the the updates though it slows
> to a craw. After about 10 minutes it's only done about 3000 records.
>
> Is that normal? Is it because it's inside such a large transaction? Is
> there anything I can do to speed that up. It seems awfully slow to me.
>
> I didn't think that giving it more shared buffers would help but I tried
> anyway. It didn't help.
>
> I tried doing a analyze full on it (vacuumdb -z -f) and it cleaned up a lot
> of stuff but it didn't speed up the updates at all.
>
> I am using a dual 800mhz xeon box with 2 gb of ram. I've tried anywhere
> from about 16,000 to 65000 shared buffers.
>
> What other factors are involved here?
It is difficult to say without knowing either the definition of the
relation(s) or the update queries involved. Are there indexes being
created after the import that would allow PostgreSQL to locate the
rows being updated quickly, or is the update an unqualified update (no
WHERE clause) that affects all tuples?
EXPLAIN ANALYZE is your friend...
Mike Mascari
mascarm@mascari.com
^ permalink raw reply [nested|flat] 37+ messages in thread
* Re: performance problem
@ 2003-11-18 21:24 scott.marlowe <scott.marlowe@ihs.com>
parent: Rick Gigger <rick@alpinenetworking.com>
2 siblings, 1 reply; 37+ messages in thread
From: scott.marlowe @ 2003-11-18 21:24 UTC (permalink / raw)
To: Rick Gigger <rick@alpinenetworking.com>; +Cc: PgSQL General ML <pgsql-general@postgresql.org>
On Tue, 18 Nov 2003, Rick Gigger wrote:
> I am currently trying to import a text data file without about 45,000
> records. At the end of the import it does an update on each of the 45,000
> records. Doing all of the inserts completes in a fairly short amount of
> time (about 2 1/2 minutes). Once it gets to the the updates though it slows
> to a craw. After about 10 minutes it's only done about 3000 records.
Are you doing your updates like this:
update table1 set field1='1' where id=1;
update table1 set field2=4 where id=1;
...
update table1 set field10='something else' where id=1;
update table1 set field1='3' where id=2;
...
Maybe an analyze after the import and before the updates would help. As
might a vacuum [full]. If the table isn't updated by other processes
probably not.
Maybe you've got a foreign key mistmatch going on and a lot of sequential
scanning?
> Is that normal? Is it because it's inside such a large transaction? Is
> there anything I can do to speed that up. It seems awfully slow to me.
Possibly. If you are creating a lot of dead tuples, then the operations
can get slower and slower. Have you checked your fsm settings et. al.?
> I didn't think that giving it more shared buffers would help but I tried
> anyway. It didn't help.
Usually doesn't. More sort_mem might though. Make it something like
16384 or 32768 (it's measured in kbytes)
> I tried doing a analyze full on it (vacuumdb -z -f) and it cleaned up a lot
> of stuff but it didn't speed up the updates at all.
You need to probably do the analyze between the import and the update.
> I am using a dual 800mhz xeon box with 2 gb of ram. I've tried anywhere
> from about 16,000 to 65000 shared buffers.
That's VERY high. When postgresql has to manage a lot of buffers it
actually is slower than letting the kernel in Linux or BSD do it for you.
> What other factors are involved here?
Not sure. More concrete examples would help. Have you run your queries
with explain analyze at the front and looked for differences in number of
rows / loops? Those are the dead giveaways.
Take a look here:
http://www.varlena.com/varlena/GeneralBits/Tidbits/perf.html
^ permalink raw reply [nested|flat] 37+ messages in thread
* Re: performance problem
@ 2003-11-18 21:41 Rick Gigger <rick@alpinenetworking.com>
parent: Mike Mascari <mascarm@mascari.com>
0 siblings, 1 reply; 37+ messages in thread
From: Rick Gigger @ 2003-11-18 21:41 UTC (permalink / raw)
To: Mike Mascari <mascarm@mascari.com>; +Cc: PgSQL General ML <pgsql-general@postgresql.org>
Uh, I feel a little silly now. I had and index on the field in question
(needed to locate the row to update) but later recreated the table and
forgot to readd it. I had assumed that it was there but double checked just
now and it was gone. I then readded the index and and it finished in a few
minutes.
Sorry about that one. Thanks for the help.
rg
----- Original Message -----
From: "Mike Mascari" <mascarm@mascari.com>
To: "Rick Gigger" <rick@alpinenetworking.com>
Cc: "PgSQL General ML" <pgsql-general@postgresql.org>
Sent: Tuesday, November 18, 2003 2:03 PM
Subject: Re: [GENERAL] performance problem
> Rick Gigger wrote:
>
> > I am currently trying to import a text data file without about 45,000
> > records. At the end of the import it does an update on each of the
45,000
> > records. Doing all of the inserts completes in a fairly short amount of
> > time (about 2 1/2 minutes). Once it gets to the the updates though it
slows
> > to a craw. After about 10 minutes it's only done about 3000 records.
> >
> > Is that normal? Is it because it's inside such a large transaction? Is
> > there anything I can do to speed that up. It seems awfully slow to me.
> >
> > I didn't think that giving it more shared buffers would help but I tried
> > anyway. It didn't help.
> >
> > I tried doing a analyze full on it (vacuumdb -z -f) and it cleaned up a
lot
> > of stuff but it didn't speed up the updates at all.
> >
> > I am using a dual 800mhz xeon box with 2 gb of ram. I've tried anywhere
> > from about 16,000 to 65000 shared buffers.
> >
> > What other factors are involved here?
>
> It is difficult to say without knowing either the definition of the
> relation(s) or the update queries involved. Are there indexes being
> created after the import that would allow PostgreSQL to locate the
> rows being updated quickly, or is the update an unqualified update (no
> WHERE clause) that affects all tuples?
>
> EXPLAIN ANALYZE is your friend...
>
> Mike Mascari
> mascarm@mascari.com
>
>
>
^ permalink raw reply [nested|flat] 37+ messages in thread
* Re: performance problem
@ 2003-11-18 21:50 Martin Marques <martin@bugs.unl.edu.ar>
parent: Rick Gigger <rick@alpinenetworking.com>
2 siblings, 0 replies; 37+ messages in thread
From: Martin Marques @ 2003-11-18 21:50 UTC (permalink / raw)
To: Rick Gigger <rick@alpinenetworking.com>; PgSQL General ML <pgsql-general@postgresql.org>
El Mar 18 Nov 2003 17:43, Rick Gigger escribió:
> I am currently trying to import a text data file without about 45,000
> records. At the end of the import it does an update on each of the 45,000
> records. Doing all of the inserts completes in a fairly short amount of
> time (about 2 1/2 minutes). Once it gets to the the updates though it slows
> to a craw. After about 10 minutes it's only done about 3000 records.
Thats not a big amount of rows. It shouldn't be making that amount of trouble,
unless you have something wrong with the update query.
Try inserting all the 45K rows and then run an explain analyze to the update
query to see what's wrong (reply the output of explain analyze to the list).
The update query itself would help alot, too.
> Is that normal? Is it because it's inside such a large transaction? Is
> there anything I can do to speed that up. It seems awfully slow to me.
Not sure, but I have had lot's of big transactions with heady load, and never
had a problem like the one you describe.
> I didn't think that giving it more shared buffers would help but I tried
> anyway. It didn't help.
>
> I tried doing a analyze full on it (vacuumdb -z -f) and it cleaned up a lot
> of stuff but it didn't speed up the updates at all.
>
> I am using a dual 800mhz xeon box with 2 gb of ram. I've tried anywhere
> from about 16,000 to 65000 shared buffers.
How's memory performace while you're running the updates? What does free say
(if you are in some unix environment).
--
select 'mmarques' || '@' || 'unl.edu.ar' AS email;
-----------------------------------------------------------------
Martín Marqués | mmarques@unl.edu.ar
Programador, Administrador, DBA | Centro de Telemática
Universidad Nacional
del Litoral
-----------------------------------------------------------------
^ permalink raw reply [nested|flat] 37+ messages in thread
* Re: performance problem
@ 2003-11-18 23:54 Rick Gigger <rick@alpinenetworking.com>
parent: scott.marlowe <scott.marlowe@ihs.com>
0 siblings, 2 replies; 37+ messages in thread
From: Rick Gigger @ 2003-11-18 23:54 UTC (permalink / raw)
To: scott.marlowe <scott.marlowe@ihs.com>; +Cc: PgSQL General ML <pgsql-general@postgresql.org>
> Usually doesn't. More sort_mem might though. Make it something like
> 16384 or 32768 (it's measured in kbytes)
I thought that it was measured in 8k blocks.
http://candle.pha.pa.us/main/writings/pgsql/hw_performance/node3.html
> That's VERY high. When postgresql has to manage a lot of buffers it
> actually is slower than letting the kernel in Linux or BSD do it for you.
Even if you've got the memory to spare? Does postgres actually slow down
just because it's slower to manager a lot of them just or because you're
taking the memory away from the kernel so the kernel has to swap more?
rg
^ permalink raw reply [nested|flat] 37+ messages in thread
* Re: performance problem
@ 2003-11-19 02:25 Doug McNaught <doug@mcnaught.org>
parent: Rick Gigger <rick@alpinenetworking.com>
1 sibling, 1 reply; 37+ messages in thread
From: Doug McNaught @ 2003-11-19 02:25 UTC (permalink / raw)
To: Rick Gigger <rick@alpinenetworking.com>; +Cc: scott.marlowe <scott.marlowe@ihs.com>; PgSQL General ML <pgsql-general@postgresql.org>
"Rick Gigger" <rick@alpinenetworking.com> writes:
> > That's VERY high. When postgresql has to manage a lot of buffers it
> > actually is slower than letting the kernel in Linux or BSD do it for you.
>
> Even if you've got the memory to spare? Does postgres actually slow down
> just because it's slower to manager a lot of them just or because you're
> taking the memory away from the kernel so the kernel has to swap more?
The latter, mainly, I think. Also you *really* don't want your kernel
to swap out any of your shared buffers, which can happen if they take
up a significant portion of RAM...
-Doug
^ permalink raw reply [nested|flat] 37+ messages in thread
* Re: performance problem
@ 2003-11-19 02:30 Martijn van Oosterhout <kleptog@svana.org>
parent: Rick Gigger <rick@alpinenetworking.com>
1 sibling, 0 replies; 37+ messages in thread
From: Martijn van Oosterhout @ 2003-11-19 02:30 UTC (permalink / raw)
To: Rick Gigger <rick@alpinenetworking.com>; +Cc: scott.marlowe <scott.marlowe@ihs.com>; PgSQL General ML <pgsql-general@postgresql.org>
On Tue, Nov 18, 2003 at 04:54:59PM -0700, Rick Gigger wrote:
> > That's VERY high. When postgresql has to manage a lot of buffers it
> > actually is slower than letting the kernel in Linux or BSD do it for you.
>
> Even if you've got the memory to spare? Does postgres actually slow down
> just because it's slower to manager a lot of them just or because you're
> taking the memory away from the kernel so the kernel has to swap more?
It's a combination. Any buffer that's stored in the postgresql cache is also
in the kernel cache so that's just slightly inefficient. The kernel pool
manager tends to be more optimised for handling large numbers of buffers.
Because it's the kernel is can tailor for hardware quirks like high memory
and bounce buffers and such. And when you're not using your shared memory
it's wasted but the kernel can reallocate any memory it likes for any
purpose.
Hope this helps,
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/
> "All that is needed for the forces of evil to triumph is for enough good
> men to do nothing." - Edmond Burke
> "The penalty good people pay for not being interested in politics is to be
> governed by people worse than themselves." - Plato
^ permalink raw reply [nested|flat] 37+ messages in thread
* Re: performance problem
@ 2003-11-20 20:41 Rick Gigger <rick@alpinenetworking.com>
parent: Doug McNaught <doug@mcnaught.org>
0 siblings, 3 replies; 37+ messages in thread
From: Rick Gigger @ 2003-11-20 20:41 UTC (permalink / raw)
To: Doug McNaught <doug@mcnaught.org>; +Cc: scott.marlowe <scott.marlowe@ihs.com>; PgSQL General ML <pgsql-general@postgresql.org>
> > > That's VERY high. When postgresql has to manage a lot of buffers it
> > > actually is slower than letting the kernel in Linux or BSD do it for
you.
I am confused. In this tutorial (by Bruce Momjian)
http://candle.pha.pa.us/main/writings/pgsql/hw_performance/node8.html
it says: "As a start for tuning, use 25% of RAM for cache size, and 2-4% for
sort size."
If I've got 2g of RAM then that is 2097152k. 25% of that = 524288k. So the
tutorial is saying that 524288k is a good starting point for shared buffers
with this amount of RAM.
If each buffer is 8k
(http://candle.pha.pa.us/main/writings/pgsql/hw_performance/node3.html)
then that would be 65536 buffers.
I'm pretty sure that that is a lot more than I need to cache every tuple in
my database. Now everytime I see someone comment on this list about
appropriate numbers for shared buffers they say something like "(65536
buffers is) VERY high. Now since I obviously don't need that much shared
cache so I am not concerned but it seems to me that one of the following
must be true.
My calculations here are wrong.
or The tutorial is not accurate in saying that 25% is a good starting point.
or The people making comments that 65536 is "VERY high" are wrong.
Am I just confused or does this make sense?
> > Even if you've got the memory to spare? Does postgres actually slow
down
> > just because it's slower to manager a lot of them just or because you're
> > taking the memory away from the kernel so the kernel has to swap more?
>
> The latter, mainly, I think. Also you *really* don't want your kernel
> to swap out any of your shared buffers, which can happen if they take
> up a significant portion of RAM...
So if I'm not swapping at all and I've got over 1g of unused memory then I'm
probably ok even with the very high buffer setting? (Although I will
probably reduce it anyway since it is unnecessary).
rg
^ permalink raw reply [nested|flat] 37+ messages in thread
* Re: performance problem
@ 2003-11-20 20:52 Rick Gigger <rick@alpinenetworking.com>
parent: Rick Gigger <rick@alpinenetworking.com>
0 siblings, 2 replies; 37+ messages in thread
From: Rick Gigger @ 2003-11-20 20:52 UTC (permalink / raw)
To: Rick Gigger <rick@alpinenetworking.com>; Mike Mascari <mascarm@mascari.com>; +Cc: PgSQL General ML <pgsql-general@postgresql.org>
Ok, adding the index back in worked the first time but then I tried
generating the database from scratch again, this time adding the index right
from the start. It added the 45000 rows in about a minute but then was
going dog slow on the updates again. So I did an explain and sure enough it
was not using the index. After some investigation I determined that it was
not using the index because when the transaction started there were only 4
rows in the table so at that point it didn't want to use it. It apparently
doesn't gather analysis data fast enough to handle this kind of transaction.
I worked around this by starting the transaction and inserting the 45,000
rows and then killing it. The I removed the index and readded it which
apparently gathered some stats and since there were all of the dead tuples
in there from the failed transaction it now decided that it should use the
index. I reran the script and this time it took 5 minutes again instead of
1 1/2 hours.
I am using 7.2.4. Has this improved in later versions? I'm not concerened
since this is a very rare thing to need to do and it's obviously possible to
work around but it would be nice if postgres could figure things like that
out on it's own. (It certainly would have saved me a lot of time and
confusion last night at about 3 am). Is there a way to for the use of a
specific index on a query?
rg
----- Original Message -----
From: "Rick Gigger" <rick@alpinenetworking.com>
To: "Mike Mascari" <mascarm@mascari.com>
Cc: "PgSQL General ML" <pgsql-general@postgresql.org>
Sent: Tuesday, November 18, 2003 2:41 PM
Subject: Re: [GENERAL] performance problem
> Uh, I feel a little silly now. I had and index on the field in question
> (needed to locate the row to update) but later recreated the table and
> forgot to readd it. I had assumed that it was there but double checked
just
> now and it was gone. I then readded the index and and it finished in a
few
> minutes.
> Sorry about that one. Thanks for the help.
>
> rg
>
> ----- Original Message -----
> From: "Mike Mascari" <mascarm@mascari.com>
> To: "Rick Gigger" <rick@alpinenetworking.com>
> Cc: "PgSQL General ML" <pgsql-general@postgresql.org>
> Sent: Tuesday, November 18, 2003 2:03 PM
> Subject: Re: [GENERAL] performance problem
>
>
> > Rick Gigger wrote:
> >
> > > I am currently trying to import a text data file without about 45,000
> > > records. At the end of the import it does an update on each of the
> 45,000
> > > records. Doing all of the inserts completes in a fairly short amount
of
> > > time (about 2 1/2 minutes). Once it gets to the the updates though it
> slows
> > > to a craw. After about 10 minutes it's only done about 3000 records.
> > >
> > > Is that normal? Is it because it's inside such a large transaction?
Is
> > > there anything I can do to speed that up. It seems awfully slow to
me.
> > >
> > > I didn't think that giving it more shared buffers would help but I
tried
> > > anyway. It didn't help.
> > >
> > > I tried doing a analyze full on it (vacuumdb -z -f) and it cleaned up
a
> lot
> > > of stuff but it didn't speed up the updates at all.
> > >
> > > I am using a dual 800mhz xeon box with 2 gb of ram. I've tried
anywhere
> > > from about 16,000 to 65000 shared buffers.
> > >
> > > What other factors are involved here?
> >
> > It is difficult to say without knowing either the definition of the
> > relation(s) or the update queries involved. Are there indexes being
> > created after the import that would allow PostgreSQL to locate the
> > rows being updated quickly, or is the update an unqualified update (no
> > WHERE clause) that affects all tuples?
> >
> > EXPLAIN ANALYZE is your friend...
> >
> > Mike Mascari
> > mascarm@mascari.com
> >
> >
> >
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: don't forget to increase your free space map settings
>
^ permalink raw reply [nested|flat] 37+ messages in thread
* Re: performance problem
@ 2003-11-20 20:59 fred@redhotpenguin.com
parent: Rick Gigger <rick@alpinenetworking.com>
2 siblings, 1 reply; 37+ messages in thread
From: fred@redhotpenguin.com @ 2003-11-20 20:59 UTC (permalink / raw)
To: Rick Gigger <rick@alpinenetworking.com>; +Cc: Doug McNaught <doug@mcnaught.org>; scott.marlowe <scott.marlowe@ihs.com>; PgSQL General ML <pgsql-general@postgresql.org>
As discussed on previous posts and mentioned below, the kernel is often
better at handling large amounts of buffers than Postgres.
^ permalink raw reply [nested|flat] 37+ messages in thread
* Re: performance problem
@ 2003-11-20 21:06 Alvaro Herrera Munoz <alvherre@dcc.uchile.cl>
parent: Rick Gigger <rick@alpinenetworking.com>
1 sibling, 1 reply; 37+ messages in thread
From: Alvaro Herrera Munoz @ 2003-11-20 21:06 UTC (permalink / raw)
To: Rick Gigger <rick@alpinenetworking.com>; +Cc: Mike Mascari <mascarm@mascari.com>; PgSQL General ML <pgsql-general@postgresql.org>
On Thu, Nov 20, 2003 at 01:52:10PM -0700, Rick Gigger wrote:
> I worked around this by starting the transaction and inserting the 45,000
> rows and then killing it. The I removed the index and readded it which
> apparently gathered some stats and since there were all of the dead tuples
> in there from the failed transaction it now decided that it should use the
> index. I reran the script and this time it took 5 minutes again instead of
> 1 1/2 hours.
Stats are not collected automatically. You should run ANALYZE after
importing your data. And it's probably faster to create the index after
the data is loaded, too.
--
Alvaro Herrera (<alvherre[@]dcc.uchile.cl>)
Y una voz del caos me habló y me dijo
"Sonríe y sé feliz, podría ser peor".
Y sonreí. Y fui feliz.
Y fue peor.
^ permalink raw reply [nested|flat] 37+ messages in thread
* Re: performance problem
@ 2003-11-20 21:07 Rick Gigger <rick@alpinenetworking.com>
parent: fred@redhotpenguin.com
0 siblings, 0 replies; 37+ messages in thread
From: Rick Gigger @ 2003-11-20 21:07 UTC (permalink / raw)
To: fred@redhotpenguin.com; +Cc: Doug McNaught <doug@mcnaught.org>; scott.marlowe <scott.marlowe@ihs.com>; PgSQL General ML <pgsql-general@postgresql.org>
Ah, so then the 25% recomendation for a starting point is probably not a bad
one, but if you get over a 1 gig of ram you might as well stop at about
256mb for the shared buffers because it just won't do you much good and will
start to slow down small queries.
That makes sense to me.
----- Original Message -----
From: <fred@redhotpenguin.com>
To: "Rick Gigger" <rick@alpinenetworking.com>
Cc: "Doug McNaught" <doug@mcnaught.org>; "scott.marlowe"
<scott.marlowe@ihs.com>; "PgSQL General ML" <pgsql-general@postgresql.org>
Sent: Thursday, November 20, 2003 1:59 PM
Subject: Re: [GENERAL] performance problem
> As discussed on previous posts and mentioned below, the kernel is often
> better at handling large amounts of buffers than Postgres.
>
> From researching previous posts and testing on my own setups, 256mb is
> about the max you want to go with shared buffers (if you have at least 1
> gig ram). After that the smaller queries (which there are usually a lot
> of) slow down, and you only gain marginal improvement on the larger
> queries. You'll see an improvement slightly on the large queries going
> over 256 but not enough to improve the overall performance. There's a few
> more technically detailed explanations of this in the list archive I
> believe, by those who can explain it better than I.
>
> >> > > That's VERY high. When postgresql has to manage a lot of buffers
it
> >> > > actually is slower than letting the kernel in Linux or BSD do it
for
> > you.
> >
> > I am confused. In this tutorial (by Bruce Momjian)
> > http://candle.pha.pa.us/main/writings/pgsql/hw_performance/node8.html
> > it says: "As a start for tuning, use 25% of RAM for cache size, and 2-4%
> > for
> > sort size."
> >
> > If I've got 2g of RAM then that is 2097152k. 25% of that = 524288k. So
> > the
> > tutorial is saying that 524288k is a good starting point for shared
> > buffers
> > with this amount of RAM.
> >
> > If each buffer is 8k
> > (http://candle.pha.pa.us/main/writings/pgsql/hw_performance/node3.html)
> > then that would be 65536 buffers.
> >
> > I'm pretty sure that that is a lot more than I need to cache every tuple
> > in
> > my database. Now everytime I see someone comment on this list about
> > appropriate numbers for shared buffers they say something like "(65536
> > buffers is) VERY high. Now since I obviously don't need that much
shared
> > cache so I am not concerned but it seems to me that one of the following
> > must be true.
> >
> > My calculations here are wrong.
> > or The tutorial is not accurate in saying that 25% is a good starting
> > point.
> > or The people making comments that 65536 is "VERY high" are wrong.
> >
> > Am I just confused or does this make sense?
> >
> >> > Even if you've got the memory to spare? Does postgres actually slow
> > down
> >> > just because it's slower to manager a lot of them just or because
> >> you're
> >> > taking the memory away from the kernel so the kernel has to swap
more?
> >>
> >> The latter, mainly, I think. Also you *really* don't want your kernel
> >> to swap out any of your shared buffers, which can happen if they take
> >> up a significant portion of RAM...
> >
> > So if I'm not swapping at all and I've got over 1g of unused memory then
> > I'm
> > probably ok even with the very high buffer setting? (Although I will
> > probably reduce it anyway since it is unnecessary).
> >
> > rg
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 7: don't forget to increase your free space map settings
> >
>
>
^ permalink raw reply [nested|flat] 37+ messages in thread
* Re: performance problem
@ 2003-11-20 21:11 Andrew Sullivan <andrew@libertyrms.info>
parent: Rick Gigger <rick@alpinenetworking.com>
2 siblings, 0 replies; 37+ messages in thread
From: Andrew Sullivan @ 2003-11-20 21:11 UTC (permalink / raw)
To: PgSQL General ML <pgsql-general@postgresql.org>
On Thu, Nov 20, 2003 at 01:41:13PM -0700, Rick Gigger wrote:
> it says: "As a start for tuning, use 25% of RAM for cache size, and 2-4% for
> sort size."
It's an old rule of thumb which has been superseded by new tests.
A
--
----
Andrew Sullivan 204-4141 Yonge Street
Afilias Canada Toronto, Ontario Canada
<andrew@libertyrms.info> M2P 2A8
+1 416 646 3304 x110
^ permalink raw reply [nested|flat] 37+ messages in thread
* Re: performance problem
@ 2003-11-20 21:12 Rick Gigger <rick@alpinenetworking.com>
parent: Alvaro Herrera Munoz <alvherre@dcc.uchile.cl>
0 siblings, 0 replies; 37+ messages in thread
From: Rick Gigger @ 2003-11-20 21:12 UTC (permalink / raw)
To: Alvaro Herrera Munoz <alvherre@dcc.uchile.cl>; +Cc: Mike Mascari <mascarm@mascari.com>; PgSQL General ML <pgsql-general@postgresql.org>
Ah, I didn't realize that you could just do an ANALYZE. I thought there was
only VACUUM ANALYZE but that can't run inside of a transaction.
Thanks,
rg
----- Original Message -----
From: "Alvaro Herrera Munoz" <alvherre@dcc.uchile.cl>
To: "Rick Gigger" <rick@alpinenetworking.com>
Cc: "Mike Mascari" <mascarm@mascari.com>; "PgSQL General ML"
<pgsql-general@postgresql.org>
Sent: Thursday, November 20, 2003 2:06 PM
Subject: Re: [GENERAL] performance problem
On Thu, Nov 20, 2003 at 01:52:10PM -0700, Rick Gigger wrote:
> I worked around this by starting the transaction and inserting the 45,000
> rows and then killing it. The I removed the index and readded it which
> apparently gathered some stats and since there were all of the dead tuples
> in there from the failed transaction it now decided that it should use the
> index. I reran the script and this time it took 5 minutes again instead
of
> 1 1/2 hours.
Stats are not collected automatically. You should run ANALYZE after
importing your data. And it's probably faster to create the index after
the data is loaded, too.
--
Alvaro Herrera (<alvherre[@]dcc.uchile.cl>)
Y una voz del caos me habló y me dijo
"Sonríe y sé feliz, podría ser peor".
Y sonreí. Y fui feliz.
Y fue peor.
^ permalink raw reply [nested|flat] 37+ messages in thread
* Re: performance problem
@ 2003-11-20 21:13 Mike Mascari <mascarm@mascari.com>
parent: Rick Gigger <rick@alpinenetworking.com>
1 sibling, 1 reply; 37+ messages in thread
From: Mike Mascari @ 2003-11-20 21:13 UTC (permalink / raw)
To: Rick Gigger <rick@alpinenetworking.com>; +Cc: PgSQL General ML <pgsql-general@postgresql.org>
Rick Gigger wrote:
> Ok, adding the index back in worked the first time but then I tried
> generating the database from scratch again, this time adding the index right
> from the start. It added the 45000 rows in about a minute but then was
> going dog slow on the updates again. So I did an explain and sure enough it
> was not using the index. After some investigation I determined that it was
> not using the index because when the transaction started there were only 4
> rows in the table so at that point it didn't want to use it. It apparently
> doesn't gather analysis data fast enough to handle this kind of transaction.
The statistics are collected as a result of an ANALYZE command:
http://www.postgresql.org/docs/current/static/sql-analyze.html
This does not happen automatically. EXPLAIN output will show a default
assumption of 1000 rows, IIRC.
> I worked around this by starting the transaction and inserting the 45,000
> rows and then killing it. The I removed the index and readded it which
> apparently gathered some stats and since there were all of the dead tuples
> in there from the failed transaction it now decided that it should use the
> index. I reran the script and this time it took 5 minutes again instead of
> 1 1/2 hours.
If you examine the behavior of pg_dump output, you'll notice that it
doesn't built indexes until after the COPY command has completed the
data import. It's a waste of cpu cycles and disk bandwidth to update
indexes on upon every insert. Your script should:
1) Drop all indexes on the relation
2) Use COPY if possible instead of INSERT to import the data
3) Recreate the indexes
4) Use UPDATE to update as necessary
> I am using 7.2.4. Has this improved in later versions? I'm not concerened
> since this is a very rare thing to need to do and it's obviously possible to
> work around but it would be nice if postgres could figure things like that
> out on it's own. (It certainly would have saved me a lot of time and
> confusion last night at about 3 am). Is there a way to for the use of a
> specific index on a query?
You can force the use of an index scan by turning sequential scans to off:
SET ENABLE_SEQSCAN TO OFF;
But the problem is that the statistics didn't match the data. You
could have:
1) Used INSERTs to insert the data into a relation with an index
2) Executed ANALYZE <foo> to update the statistics
3) Perform the UPDATE
After the UPDATE, you'll still have dead tuples (the original rows)
which require that they be marked as dead, and so you should
occassionally run either VACUUM to mark them as such or VACUUM FULL to
reclaim the dead space or VACUUM FULL ANALYZE to also update the
relation's statistics.
I would just execute the steps I outlined above with COPY and not
worry about ANALYZEs and VACUUMs in a script. People often run VACUUM
ANALYZE in a 1/day cron job and VACUUM FULL ANALYZE in a 1/week cron
job. Then, of course, there's REINDEX...
Hope that helps,
Mike Mascari
mascarm@mascari.com
^ permalink raw reply [nested|flat] 37+ messages in thread
* Re: performance problem
@ 2003-11-20 21:19 Tom Lane <tgl@sss.pgh.pa.us>
parent: Rick Gigger <rick@alpinenetworking.com>
2 siblings, 2 replies; 37+ messages in thread
From: Tom Lane @ 2003-11-20 21:19 UTC (permalink / raw)
To: Rick Gigger <rick@alpinenetworking.com>; +Cc: Doug McNaught <doug@mcnaught.org>; scott.marlowe <scott.marlowe@ihs.com>; PgSQL General ML <pgsql-general@postgresql.org>
"Rick Gigger" <rick@alpinenetworking.com> writes:
> I am confused. In this tutorial (by Bruce Momjian)
> http://candle.pha.pa.us/main/writings/pgsql/hw_performance/node8.html
> it says: "As a start for tuning, use 25% of RAM for cache size, and 2-4% for
> sort size."
That advice is widely considered obsolete --- it was developed in the
days when typical RAM sizes were a lot less than today. I don't believe
anyone has shown a good case for setting shared_buffers much higher than
10000.
regards, tom lane
^ permalink raw reply [nested|flat] 37+ messages in thread
* Re: performance problem
@ 2003-11-20 22:20 Rick Gigger <rick@alpinenetworking.com>
parent: Mike Mascari <mascarm@mascari.com>
0 siblings, 0 replies; 37+ messages in thread
From: Rick Gigger @ 2003-11-20 22:20 UTC (permalink / raw)
To: Mike Mascari <mascarm@mascari.com>; +Cc: PgSQL General ML <pgsql-general@postgresql.org>
> If you examine the behavior of pg_dump output, you'll notice that it
> doesn't built indexes until after the COPY command has completed the
> data import. It's a waste of cpu cycles and disk bandwidth to update
> indexes on upon every insert. Your script should:
>
> 1) Drop all indexes on the relation
> 2) Use COPY if possible instead of INSERT to import the data
> 3) Recreate the indexes
> 4) Use UPDATE to update as necessary
I never thought of dropping the indexes in the middle of the script and
readding them. I guess that would be fine as long as nothing else was
running that needed the index while it was gone. I don't think 7.2.x
supports using COPY unless you are inserting all of the fields which I don't
want to do although I suppose I could just insert all of the defaults. Is
that correct?
> > I am using 7.2.4. Has this improved in later versions? I'm not
concerened
> > since this is a very rare thing to need to do and it's obviously
possible to
> > work around but it would be nice if postgres could figure things like
that
> > out on it's own. (It certainly would have saved me a lot of time and
> > confusion last night at about 3 am). Is there a way to for the use of a
> > specific index on a query?
>
> You can force the use of an index scan by turning sequential scans to off:
>
SET ENABLE_SEQSCAN TO OFF;
Thanks.
> But the problem is that the statistics didn't match the data. You
> could have:
>
> 1) Used INSERTs to insert the data into a relation with an index
> 2) Executed ANALYZE <foo> to update the statistics
> 3) Perform the UPDATE
That would have been the easiest thing. I wanted to do that but I thought
that you had to do a vacuum (which I couldn't do in the transaction)
together with analyze. I didn't realize that analyze was a command all by
itself.
> After the UPDATE, you'll still have dead tuples (the original rows)
> which require that they be marked as dead, and so you should
> occassionally run either VACUUM to mark them as such or VACUUM FULL to
> reclaim the dead space or VACUUM FULL ANALYZE to also update the
> relation's statistics.
I do them all nightly with cron on all my important databases. I just had a
problem with need the analyze to happen in the middle of the transaction in
this one special case.
> I would just execute the steps I outlined above with COPY and not
> worry about ANALYZEs and VACUUMs in a script. People often run VACUUM
> ANALYZE in a 1/day cron job and VACUUM FULL ANALYZE in a 1/week cron
> job. Then, of course, there's REINDEX...
Doing the vacuum full analyze doesn't take long on to do if I do it once a
day so I just do that.
^ permalink raw reply [nested|flat] 37+ messages in thread
* Re: performance problem
@ 2003-11-20 22:21 Rick Gigger <rick@alpinenetworking.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 37+ messages in thread
From: Rick Gigger @ 2003-11-20 22:21 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Doug McNaught <doug@mcnaught.org>; scott.marlowe <scott.marlowe@ihs.com>; PgSQL General ML <pgsql-general@postgresql.org>
> "Rick Gigger" <rick@alpinenetworking.com> writes:
> > I am confused. In this tutorial (by Bruce Momjian)
> > http://candle.pha.pa.us/main/writings/pgsql/hw_performance/node8.html
> > it says: "As a start for tuning, use 25% of RAM for cache size, and 2-4%
for
> > sort size."
>
> That advice is widely considered obsolete --- it was developed in the
> days when typical RAM sizes were a lot less than today. I don't believe
> anyone has shown a good case for setting shared_buffers much higher than
> 10000.
>
> regards, tom lane
Thank you!
^ permalink raw reply [nested|flat] 37+ messages in thread
* Re: performance problem
@ 2003-11-20 23:50 Bruce Momjian <pgman@candle.pha.pa.us>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 37+ messages in thread
From: Bruce Momjian @ 2003-11-20 23:50 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Rick Gigger <rick@alpinenetworking.com>; Doug McNaught <doug@mcnaught.org>; scott.marlowe <scott.marlowe@ihs.com>; PgSQL General ML <pgsql-general@postgresql.org>
Tom Lane wrote:
> "Rick Gigger" <rick@alpinenetworking.com> writes:
> > I am confused. In this tutorial (by Bruce Momjian)
> > http://candle.pha.pa.us/main/writings/pgsql/hw_performance/node8.html
> > it says: "As a start for tuning, use 25% of RAM for cache size, and 2-4% for
> > sort size."
>
> That advice is widely considered obsolete --- it was developed in the
> days when typical RAM sizes were a lot less than today. I don't believe
> anyone has shown a good case for setting shared_buffers much higher than
> 10000.
Yes, I will update that page shortly.
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073
^ permalink raw reply [nested|flat] 37+ messages in thread
* Performance Problem
@ 2004-05-27 15:00 Frank Smith <frank.smith@aeat.co.uk>
0 siblings, 2 replies; 37+ messages in thread
From: Frank Smith @ 2004-05-27 15:00 UTC (permalink / raw)
To: pgsql-admin
Hi
ID:77777
I am running PostgreSQL 7.2.2 on Red Hat 9 and I am suffering a growing
performance problem. The problem shows through a slowing of queries and
an increase in the system CPU usage. Queries that took less than 6
seconds clime to take more than 5 minutes and as the system is driven by
Apache through Perl scripts, the web server times out. Clearly I could
reset the Apache timers, however this would just hide the problem for a
little longer and of course once the problem starts to happen the system
tends to cascade because the users try again and the orphaned processes
continue to use processor time until they complete.
I use Cron to 'VACUUM ANALIZE' the system every night and this greatly
improved the performance but has not stopped the delay from growing. The
strange thing is that apart from the delay everything seems fine.
Some facts:
Rebooting does not remove the problem.
Restarting Apache and/or Postgres makes no difference.
Other that the ANALIZE mentioned above the system has not been tuned in
anyway.
The following sequence does however cure the problem;
pg_dump database > temp.db
dropdb database
createdb database
psql -e database < temp.db
I have not tried leaving out the dropdb and createdb but I do not need
to stop postgres.
Has anybody any idea how to fix my problem is it something I have or
have not done? Please do not tell me to upgrade to the latest version of
Postgres unless it is a clearly identifiable Postgres problem that is at
the rot of my dilemma.
Thanks
Frank
I get balder by the handful, daily.
***********************************************************************
This transmission contains information which may be confidential and
which may also be privileged. It is intended for the named addressee
only. Unless you are the named addressee, or authorised to receive it
on behalf of the addressee you may not copy or use it, or disclose it
to anyone else. If you have received this transmission in error please
contact the sender. Thank you for your cooperation.
***********************************************************************
For more information about AEA Technology please visit our website at http://www.aeat.co.uk
AEA Technology plc registered office 329 Harwell, Didcot, Oxfordshire OX11 0QJ.
Registered in England and Wales, number 3095862.
^ permalink raw reply [nested|flat] 37+ messages in thread
* Re: Performance Problem
@ 2004-05-31 03:00 Christopher Browne <cbbrowne@acm.org>
parent: Frank Smith <frank.smith@aeat.co.uk>
1 sibling, 0 replies; 37+ messages in thread
From: Christopher Browne @ 2004-05-31 03:00 UTC (permalink / raw)
To: pgsql-admin
Centuries ago, Nostradamus foresaw when frank.smith@aeat.co.uk ("Frank Smith") would write:
> Hi
>
> ID:77777
>
> I am running PostgreSQL 7.2.2 on Red Hat 9 and I am suffering a
> growing performance problem. The problem shows through a slowing of
> queries and an increase in the system CPU usage. Queries that took
> less than 6 seconds clime to take more than 5 minutes and as the
> system is driven by Apache through Perl scripts, the web server
> times out. Clearly I could reset the Apache timers, however this
> would just hide the problem for a little longer and of course once
> the problem starts to happen the system tends to cascade because the
> users try again and the orphaned processes continue to use processor
> time until they complete.
>
> I use Cron to 'VACUUM ANALIZE' the system every night and this
> greatly improved the performance but has not stopped the delay from
> growing. The strange thing is that apart from the delay everything
> seems fine.
>
> Some facts:
>
> Rebooting does not remove the problem.
> Restarting Apache and/or Postgres makes no difference.
> Other that the ANALIZE mentioned above the system has not been tuned in
> anyway.
> The following sequence does however cure the problem;
>
> pg_dump database > temp.db
> dropdb database
> createdb database
> psql -e database < temp.db
>
> I have not tried leaving out the dropdb and createdb but I do not need
> to stop postgres.
>
> Has anybody any idea how to fix my problem is it something I have or
> have not done? Please do not tell me to upgrade to the latest
> version of Postgres unless it is a clearly identifiable Postgres
> problem that is at the rot of my dilemma.
Well, there certainly have been _major_ improvements in performance
between 7.2 and 7.4, so it is more than plausible that that could have
a significant impact on performance.
It sounds as though the traffic on the system is causing query plans
to get out of whack.
But it is not clear what your problem actually is; you have not
explained the nature of the queries that are performing badly.
I would imagine that it would be beneficial to run VACUUM and/or
ANALYZE somewhat more often.
- Let's suppose you run VACUUM on tables getting lots of UPDATEs and
DELETEs (note, I did _not_ say INSERT; tables that only see INSERTs
essentially NEVER need to be vacuumed) once every 4 hours.
- Tables seeing frequent updates of any sort (INSERT/DELETE/UPDATE)
perhaps should get an ANALYZE every hour.
There are significant merits to jumping to a newer version in this
regard:
1. As of 7.3, you can run pg_autovacuum, which will automatically
do VACUUMs and ANALYZEs, which hopefully would prevent things
from going downhill.
2. As of 7.4, there is a new logging parameter that allows logging
just those queries that are running slowly, which should help you
to find just the offending queries.
The sort of information we'd actually need to help fix the problem is
the queries and the output provided by running EXPLAIN ANALYZE on some
of the slow queries.
There are some tables that we have in our applications that we happen
to ANALYZE _and_ VACUUM every couple of minutes, as they are UPDATEd
almost continually. Waiting an hour between VACUUMS would lead to
_really_ bad performance. You might have an instance of a table like
that, but you haven't said anything that would indicate that.
--
(reverse (concatenate 'string "gro.mca" "@" "enworbbc"))
http://cbbrowne.com/info/sgml.html
Rules of the Evil Overlord #149. "Ropes supporting various fixtures
will not be tied next to open windows or staircases, and chandeliers
will be hung way at the top of the ceiling."
<http://www.eviloverlord.com/;
^ permalink raw reply [nested|flat] 37+ messages in thread
* Re: Performance Problem
@ 2004-05-31 10:41 Harald Fuchs <hf517@protecting.net>
parent: Frank Smith <frank.smith@aeat.co.uk>
1 sibling, 0 replies; 37+ messages in thread
From: Harald Fuchs @ 2004-05-31 10:41 UTC (permalink / raw)
To: pgsql-admin
In article <s0b610a3.089@uk.aeat.com>,
"Frank Smith" <frank.smith@aeat.co.uk> writes:
> Hi
> ID:77777
> I am running PostgreSQL 7.2.2 on Red Hat 9 and I am suffering a growing
> performance problem. The problem shows through a slowing of queries and
> an increase in the system CPU usage. Queries that took less than 6
> seconds clime to take more than 5 minutes and as the system is driven by
> Apache through Perl scripts, the web server times out. Clearly I could
> reset the Apache timers, however this would just hide the problem for a
> little longer and of course once the problem starts to happen the system
> tends to cascade because the users try again and the orphaned processes
> continue to use processor time until they complete.
> I use Cron to 'VACUUM ANALIZE' the system every night and this greatly
> improved the performance but has not stopped the delay from growing. The
> strange thing is that apart from the delay everything seems fine.
If VACUUM does not stop the delay from growing, you might be suffering
index bloat. Either REINDEX by crontab, or upgrade to 7.4, where
VACUUM seems to take care of that.
^ permalink raw reply [nested|flat] 37+ messages in thread
* Re: Performance Problem
@ 2004-06-02 15:09 Frank Smith <frank.smith@aeat.co.uk>
0 siblings, 0 replies; 37+ messages in thread
From: Frank Smith @ 2004-06-02 15:09 UTC (permalink / raw)
To: frank.smith@aeat.co.uk; postgresql@finner.de; +Cc: pgsql-admin
Thanks to all
I will try the various options out and look to see if we can upgrade
and let you know the result.
It will take a few days as I am not officially here but on
holiday??????
Frank
>>> Frank Finner <postgresql@finner.de> 31/05/04 15:03:02 >>>
Hi,
I had a similiar problem with 7.3.5 some time ago (march 23rd). The
query durations increased extremely, as did the time vacuum needed. The
database
continuously (every 10 minutes) drops and creates tables as replication
of a
production database. "Vacuum analyze" did obviously not take care of
that and
did not release space correctly. Because my database is not so big
(about 1 GB),
I finally decided to dump, drop and recreate it out of the dump every
night.
This works fine for me. I was told on the list (Tom Lane did), that in
7.4 vacuum does handle these things in a much better way, so
dump/recreate is
no longer necessary. If possible (alas, not for me with my database),
I
recommend to upgrade to 7.4.
Regards, Frank Finner.
On 31 May 2004 12:41:07 +0200 Harald Fuchs <hf517@protecting.net> sat
down,
thought long and then wrote:
> In article <s0b610a3.089@uk.aeat.com>,
> "Frank Smith" <frank.smith@aeat.co.uk> writes:
>
> > Hi
> > ID:77777
>
> > I am running PostgreSQL 7.2.2 on Red Hat 9 and I am suffering a
growing
> > performance problem. The problem shows through a slowing of queries
and
> > an increase in the system CPU usage. Queries that took less than 6
> > seconds clime to take more than 5 minutes and as the system is
driven by
> > Apache through Perl scripts, the web server times out. Clearly I
could
> > reset the Apache timers, however this would just hide the problem
for a
> > little longer and of course once the problem starts to happen the
system
> > tends to cascade because the users try again and the orphaned
processes
> > continue to use processor time until they complete.
>
> > I use Cron to 'VACUUM ANALIZE' the system every night and this
greatly
> > improved the performance but has not stopped the delay from
growing. The
> > strange thing is that apart from the delay everything seems fine.
>
> If VACUUM does not stop the delay from growing, you might be
suffering
> index bloat. Either REINDEX by crontab, or upgrade to 7.4, where
> VACUUM seems to take care of that.
***********************************************************************
This transmission contains information which may be confidential and
which may also be privileged. It is intended for the named addressee
only. Unless you are the named addressee, or authorised to receive it
on behalf of the addressee you may not copy or use it, or disclose it
to anyone else. If you have received this transmission in error please
contact the sender. Thank you for your cooperation.
***********************************************************************
For more information about AEA Technology please visit our website at http://www.aeat.co.uk
AEA Technology plc registered office 329 Harwell, Didcot, Oxfordshire OX11 0QJ.
Registered in England and Wales, number 3095862.
^ permalink raw reply [nested|flat] 37+ messages in thread
* Performance problem...
@ 2005-03-14 18:03 Marcin Giedz <marcin.giedz@eulerhermes.pl>
0 siblings, 1 reply; 37+ messages in thread
From: Marcin Giedz @ 2005-03-14 18:03 UTC (permalink / raw)
To: pgsql-admin
Hello...
Our company is going to change SQL engine from MySQL to PSQL. Of course some
performance problems occured. Our server is Dual Xeon 3.0GHz + 8GB RAM +
RAID1(software - two 146GB SCSI 15k) for sql data + RAID1(software - two
146GB SCSI 15k) for pg_xlog. Postgres.conf parameters are as follows:
max_connections = 150
shared_buffers = 50000 # min 16, at least max_connections*2, 8KB each
work_mem = 2048 # min 64, size in KB
maintenance_work_mem = 524288 # min 1024, size in KB
checkpoint_segments = 32 # in logfile segments, min 1, 16MB each
archive_command = 'cp "%p" /mnt/logs/"%f"' # command to use to
archive a logfile segment
effective_cache_size = 655360 # typically 8KB each
random_page_cost = 1.2 # units are one sequential page fetch cost
stats_start_collector = true
stats_row_level = true
Of course our system is Debian Sarge with Shared memory size = 1GB
Here is an example:
.... I know you don't have our schemas/tables etc. but I also attached QUERY
PLAN for such query. Maybe there is something wrong with this query maybe it
should be changed? or so?
SELECT DISTINCT t1.Id, t1.IdTypNazwa, t1.IdFirma, t1.Nazwa, t1.NazwaAscii,
t1.MskNazwa, t3.Id, t3.numer, t3.MskNumer, t4.Id, t4.numer, t4.MskNumer,
t5.Id, t5.numer, t5.MskNumer, t6.Id, t6.numer, t6.MskNumer, t7.Id, t7.numer,
t7.MskNumer, t8.Id, t8.numer, t8.MskNumer, t9.Id, t9.numer, t9.MskNumer,
t10.Id, t10.IdTypFormaPrawna, t10.MskFormaPrawna, t11.slowo FROM firmy.adres
AS n INNER JOIN firmy.nazwa AS t1 ON t1.IdFirma = n.IdPodmiot AND
t1.IdTypNazwa = 153 AND t1.Historia = 0 LEFT JOIN firmy.numer AS t3 ON
t3.IdPodmiot = t1.IdFirma AND t3.IdTypNumer = 156 AND t3.Historia = 0 LEFT
JOIN firmy.numer AS t4 ON t4.IdPodmiot = t1.IdFirma AND t4.IdTypNumer = 160
AND t4.Historia = 0 LEFT JOIN firmy.numer AS t5 ON t5.IdPodmiot = t1.IdFirma
AND t5.IdTypNumer = 155 AND t5.Historia = 0 LEFT JOIN firmy.numer AS t6 ON
t6.IdPodmiot = t1.IdFirma AND t6.IdTypNumer = 627 AND t6.Historia = 0 LEFT
JOIN firmy.numer AS t7 ON t7.IdPodmiot = t1.IdFirma AND t7.IdTypNumer = 848
AND t7.Historia = 0 LEFT JOIN firmy.numer AS t8 ON t8.IdPodmiot = t1.IdFirma
AND t8.IdTypNumer = 763 AND t8.Historia = 0 LEFT JOIN firmy.numer AS t9 ON
t9.IdPodmiot = t1.IdFirma AND t9.IdTypNumer = 762 AND t9.Historia = 0 LEFT
JOIN firmy.formaprawna AS t10 ON t10.IdFirma = t1.IdFirma LEFT JOIN
slowniki.tslownik AS t11 ON t11.idslownik = t10.IdTypFormaPrawna AND
t11.idjezyktyp = 2 WHERE n.Ulica ILIKE 'pu%' AND n.IdKraj = 190 LIMIT 25
pl=# explain analyze SELECT DISTINCT t1.Id, t1.IdTypNazwa, t1.IdFirma,
t1.Nazwa, t1.NazwaAscii,
pl-# t1.MskNazwa, t3.Id, t3.numer, t3.MskNumer, t4.Id, t4.numer, t4.MskNumer,
pl-# t5.Id, t5.numer, t5.MskNumer, t6.Id, t6.numer, t6.MskNumer, t7.Id,
t7.numer,
pl-# t7.MskNumer, t8.Id, t8.numer, t8.MskNumer, t9.Id, t9.numer, t9.MskNumer,
pl-# t10.Id, t10.IdTypFormaPrawna, t10.MskFormaPrawna, t11.slowo FROM
firmy.adres
pl-# AS n INNER JOIN firmy.nazwa AS t1 ON t1.IdFirma = n.IdPodmiot AND
pl-# t1.IdTypNazwa = 153 AND t1.Historia = 0 LEFT JOIN firmy.numer AS t3 ON
pl-# t3.IdPodmiot = t1.IdFirma AND t3.IdTypNumer = 156 AND t3.Historia = 0
LEFT
pl-# JOIN firmy.numer AS t4 ON t4.IdPodmiot = t1.IdFirma AND t4.IdTypNumer =
160
pl-# AND t4.Historia = 0 LEFT JOIN firmy.numer AS t5 ON t5.IdPodmiot =
t1.IdFirma
pl-# AND t5.IdTypNumer = 155 AND t5.Historia = 0 LEFT JOIN firmy.numer AS t6
ON
pl-# t6.IdPodmiot = t1.IdFirma AND t6.IdTypNumer = 627 AND t6.Historia = 0
LEFT
pl-# JOIN firmy.numer AS t7 ON t7.IdPodmiot = t1.IdFirma AND t7.IdTypNumer =
848
pl-# AND t7.Historia = 0 LEFT JOIN firmy.numer AS t8 ON t8.IdPodmiot =
t1.IdFirma
pl-# AND t8.IdTypNumer = 763 AND t8.Historia = 0 LEFT JOIN firmy.numer AS t9
ON
pl-# t9.IdPodmiot = t1.IdFirma AND t9.IdTypNumer = 762 AND t9.Historia = 0
LEFT
pl-# JOIN firmy.formaprawna AS t10 ON t10.IdFirma = t1.IdFirma LEFT JOIN
pl-# slowniki.tslownik AS t11 ON t11.idslownik = t10.IdTypFormaPrawna AND
pl-# t11.idjezyktyp = 2 WHERE n.Ulica ILIKE 'pu%' AND n.IdKraj = 190 LIMIT 25;
QUERY PLAN
Limit (cost=84757.05..84759.05 rows=25 width=264) (actual
time=3153.752..3154.418 rows=25 loops=1)
-> Unique (cost=84757.05..88861.61 rows=51307 width=264) (actual
time=3153.748..3154.391 rows=25 loops=1)
-> Sort (cost=84757.05..84885.32 rows=51307 width=264) (actual
time=3153.745..3153.768 rows=44 loops=1)
Sort Key: t1.id, t1.idtypnazwa, t1.idfirma, t1.nazwa,
t1.nazwaascii, t1.msknazwa, t3.id, t3.numer, t3.msknumer, t4.id, t4.numer,
t4.msknumer,
t5.id, t5.numer, t5.msknumer, t6.id, t6.numer, t6.msknumer, t7.id, t7.numer,
t7.msknumer, t8.id, t8.numer, t8.msknumer, t9.id, t9.numer, t9.msknumer,
t10.i
d, t10.idtypformaprawna, t10.mskformaprawna, t11.slowo
-> Hash Left Join (cost=18104.92..77085.08 rows=51307
width=264) (actual time=643.240..3131.874 rows=1128 loops=1)
Hash Cond: ("outer".idtypformaprawna = "inner".idslownik)
-> Merge Left Join (cost=17680.10..73498.20 rows=50457
width=227) (actual time=626.711..3100.239 rows=1128 loops=1)
Merge Cond: ("outer".idfirma = "inner".idfirma)
-> Merge Left Join (cost=17680.10..71408.80
rows=50457 width=215) (actual time=626.209..2930.366 rows=1128 loops=1)
Merge Cond: ("outer".idfirma =
"inner".idpodmiot)
-> Merge Left Join (cost=17101.31..70698.83
rows=50457 width=195) (actual time=623.431..2915.149 rows=1128 loops=1)
Merge Cond: ("outer".idfirma =
"inner".idpodmiot)
-> Merge Left Join
(cost=15446.22..68901.47 rows=50457 width=175) (actual time=614.432..2892.178
rows=1128 loops=1)
Merge Cond: ("outer".idfirma =
"inner".idpodmiot)
-> Merge Left Join
(cost=7301.66..60235.12 rows=50457 width=155) (actual time=260.861..2454.992
rows=1128 loo
ps=1)
Merge Cond:
("outer".idfirma = "inner".idpodmiot)
-> Merge Left Join
(cost=7301.66..49786.89 rows=50457 width=135) (actual time=258.841..2054.790
rows=11
28 loops=1)
Merge Cond:
("outer".idfirma = "inner".idpodmiot)
-> Merge Left Join
(cost=0.00..42050.02 rows=50457 width=115) (actual time=5.759..1735.173
rows=1
128 loops=1)
Merge Cond:
("outer".idfirma = "inner".idpodmiot)
-> Merge Left
Join (cost=0.00..31611.72 rows=50457 width=95) (actual time=4.530..1337.763
r
ows=1128 loops=1)
Merge
Cond: ("outer".idfirma = "inner".idpodmiot)
-> Merge
Join (cost=0.00..21021.26 rows=50457 width=75) (actual time=2.709..813.394 r
ows=1128 loops=1)
Merge
Cond: ("outer".idpodmiot = "inner".idfirma)
->
Index Scan using firmy_adres_idpodmiot on adres n (cost=0.00..12596.46 rows=
42837 width=4) (actual time=1.261..337.163 rows=1128 loops=1)
Filter:
(((ulica)::text ~~* 'pu%'::text) AND (idkraj = 190))
->
Index Scan using firmy_nazwa_idfirma on nazwa t1 (cost=0.00..7539.00 rows=11
0134 width=75) (actual time=0.023..392.591 rows=109085 loops=1)
Filter:
((idtypnazwa = 153) AND (historia = 0))
-> Index
Scan using firmy_numer_idpodmiot on numer t3 (cost=0.00..9869.42 rows=75337
width=24) (actual time=0.018..463.952 rows=77155 loops=1)
Filter:
((idtypnumer = 156) AND (historia = 0))
-> Index Scan
using firmy_numer_idpodmiot on numer t4 (cost=0.00..9869.42 rows=56067
width=
24) (actual time=0.012..335.900 rows=57050 loops=1)
Filter:
((idtypnumer = 160) AND (historia = 0))
-> Sort
(cost=7301.66..7375.98 rows=29728 width=24) (actual time=252.882..288.512
rows=28192 loop
s=1)
Sort Key:
t5.idpodmiot
-> Index Scan
using firmy_numer_idtypnumer on numer t5 (cost=0.00..5092.94 rows=29728
width
=24) (actual time=0.029..66.937 rows=27904 loops=1)
Index
Cond: ((idtypnumer = 155) AND (historia = 0))
-> Index Scan using
firmy_numer_idpodmiot on numer t6 (cost=0.00..9869.42 rows=57326 width=24)
(actual
time=0.049..340.456 rows=59336 loops=1)
Filter: ((idtypnumer
= 627) AND (historia = 0))
-> Sort (cost=8144.56..8239.70
rows=38056 width=24) (actual time=353.474..395.087 rows=37693 loops=1)
Sort Key: t7.idpodmiot
-> Index Scan using
firmy_numer_idtypnumer on numer t7 (cost=0.00..5249.29 rows=38056 width=24)
(actual
time=0.032..90.333 rows=37549 loops=1)
Index Cond:
((idtypnumer = 848) AND (historia = 0))
-> Sort (cost=1655.10..1658.97
rows=1550 width=24) (actual time=8.884..9.971 rows=1699 loops=1)
Sort Key: t8.idpodmiot
-> Index Scan using
firmy_numer_idtypnumer on numer t8 (cost=0.00..1572.96 rows=1550 width=24)
(actual time=0
.053..5.287 rows=1690 loops=1)
Index Cond: ((idtypnumer =
763) AND (historia = 0))
-> Sort (cost=578.79..580.00 rows=485
width=24) (actual time=2.698..3.077 rows=509 loops=1)
Sort Key: t9.idpodmiot
-> Index Scan using
firmy_numer_idtypnumer on numer t9 (cost=0.00..557.15 rows=485 width=24)
(actual time=0.032..1.
757 rows=508 loops=1)
Index Cond: ((idtypnumer = 762)
AND (historia = 0))
-> Index Scan using formaprawna_idfirma_key on
formaprawna t10 (cost=0.00..1500.26 rows=58650 width=16) (actual
time=0.013..103
.667 rows=59116 loops=1)
-> Hash (cost=415.86..415.86 rows=3583 width=41)
(actual time=16.463..16.463 rows=0 loops=1)
-> Seq Scan on tslownik t11 (cost=0.00..415.86
rows=3583 width=41) (actual time=0.020..12.802 rows=3595 loops=1)
Filter: (idjezyktyp = 2)
Total runtime: 3159.199 ms
(55 rows)
pl=#
I now .... query plan is not very readable but .... as you can see only one
seq scan occured for field "ulica" in table "adres" .... on the rest Index
scan were used. I'm rather new and really don't know how to interpret and
what are they mean: Merge Left Join? How to read actual time in each row ow
query plan.... and why this query takes about 3 second to receive 25 records
when on MySQL it takes only 0.14sec.? All indexes are made - the same indexes
on mysql and postgresql. What is the cause of such big difference?
Many thanks for response....
Marcin Giedz
^ permalink raw reply [nested|flat] 37+ messages in thread
* Re: Performance problem...
@ 2005-03-14 18:32 Scott Marlowe <smarlowe@g2switchworks.com>
parent: Marcin Giedz <marcin.giedz@eulerhermes.pl>
0 siblings, 1 reply; 37+ messages in thread
From: Scott Marlowe @ 2005-03-14 18:32 UTC (permalink / raw)
To: Marcin Giedz <marcin.giedz@eulerhermes.pl>; +Cc: pgsql-admin
On Mon, 2005-03-14 at 12:03, Marcin Giedz wrote:
> Hello...
>
>
> Our company is going to change SQL engine from MySQL to PSQL. Of course some
> performance problems occured. Our server is Dual Xeon 3.0GHz + 8GB RAM +
> RAID1(software - two 146GB SCSI 15k) for sql data + RAID1(software - two
> 146GB SCSI 15k) for pg_xlog. Postgres.conf parameters are as follows:
>
> max_connections = 150
> shared_buffers = 50000 # min 16, at least max_connections*2, 8KB each
> work_mem = 2048 # min 64, size in KB
50,000 shared buffers may or may not be too much. Try it at different
sizes from 5,000 or so up to 50,000 and find the "knee". It's usually
closer to 10,000 than 50,000, but ymmv...
On the other hand, for a machine with 8 gigs of ram, 2 meg of work_mem
is pretty small. Try bumping it up to 8 or 16 megs. You can change
this one "on the fly" for testing, so just do:
set work_mem=16384;
and then run the query again and see if that helps. The hash aggregate
method uses sort/work mem to do it's work, and if it doesn't think it
can hold the result set in that space the planner will pick another
method, like the merge left join.
In your explain analyze output, look for gross mismatches between
estimated and actual rows. Most of yours here look pretty good in the
areas where the data is being collected, but during the merges, the
numbers are WAY off, but i'm not sure what to do to change that.
^ permalink raw reply [nested|flat] 37+ messages in thread
* Re: Performance problem...
@ 2005-03-15 08:59 Marcin Giedz <marcin.giedz@eulerhermes.pl>
parent: Scott Marlowe <smarlowe@g2switchworks.com>
0 siblings, 1 reply; 37+ messages in thread
From: Marcin Giedz @ 2005-03-15 08:59 UTC (permalink / raw)
To: pgsql-admin
Dnia poniedziałek, 14 marca 2005 19:32, Scott Marlowe napisał:
> On Mon, 2005-03-14 at 12:03, Marcin Giedz wrote:
> > Hello...
> >
> >
> > Our company is going to change SQL engine from MySQL to PSQL. Of course
> > some performance problems occured. Our server is Dual Xeon 3.0GHz + 8GB
> > RAM + RAID1(software - two 146GB SCSI 15k) for sql data + RAID1(software
> > - two 146GB SCSI 15k) for pg_xlog. Postgres.conf parameters are as
> > follows:
> >
> > max_connections = 150
> > shared_buffers = 50000 # min 16, at least max_connections*2, 8KB
> > each work_mem = 2048 # min 64, size in KB
>
> 50,000 shared buffers may or may not be too much. Try it at different
> sizes from 5,000 or so up to 50,000 and find the "knee". It's usually
> closer to 10,000 than 50,000, but ymmv...
Playing with shared_buffers from 10000 to 50000 doesn't change anything in
total time for this query :( But when I change work_mem a little higher to
10000 total runtime decreases a little about 10% but when I change
random_page_cost to 0.2 (I know that almost all papers say it should be
higher then 1.0) total runtime decreases almost 3 times and lasts about 900ms
- earlier with random_page_cost=1.2 it took 2.7s. Is it possible to have
random_page_cost on this value?
>
> On the other hand, for a machine with 8 gigs of ram, 2 meg of work_mem
> is pretty small. Try bumping it up to 8 or 16 megs. You can change
> this one "on the fly" for testing, so just do:
>
> set work_mem=16384;
> and then run the query again and see if that helps. The hash aggregate
> method uses sort/work mem to do it's work, and if it doesn't think it
> can hold the result set in that space the planner will pick another
> method, like the merge left join.
>
> In your explain analyze output, look for gross mismatches between
> estimated and actual rows. Most of yours here look pretty good in the
> areas where the data is being collected, but during the merges, the
> numbers are WAY off, but i'm not sure what to do to change that.
^ permalink raw reply [nested|flat] 37+ messages in thread
* Re: Performance problem...
@ 2005-03-15 16:08 Scott Marlowe <smarlowe@g2switchworks.com>
parent: Marcin Giedz <marcin.giedz@eulerhermes.pl>
0 siblings, 1 reply; 37+ messages in thread
From: Scott Marlowe @ 2005-03-15 16:08 UTC (permalink / raw)
To: Marcin Giedz <marcin.giedz@eulerhermes.pl>; +Cc: pgsql-admin
On Tue, 2005-03-15 at 02:59, Marcin Giedz wrote:
> Dnia poniedziałek, 14 marca 2005 19:32, Scott Marlowe napisał:
> > On Mon, 2005-03-14 at 12:03, Marcin Giedz wrote:
> > > Hello...
> > >
> > >
> > > Our company is going to change SQL engine from MySQL to PSQL. Of course
> > > some performance problems occured. Our server is Dual Xeon 3.0GHz + 8GB
> > > RAM + RAID1(software - two 146GB SCSI 15k) for sql data + RAID1(software
> > > - two 146GB SCSI 15k) for pg_xlog. Postgres.conf parameters are as
> > > follows:
> > >
> > > max_connections = 150
> > > shared_buffers = 50000 # min 16, at least max_connections*2, 8KB
> > > each work_mem = 2048 # min 64, size in KB
> >
> > 50,000 shared buffers may or may not be too much. Try it at different
> > sizes from 5,000 or so up to 50,000 and find the "knee". It's usually
> > closer to 10,000 than 50,000, but ymmv...
>
> Playing with shared_buffers from 10000 to 50000 doesn't change anything in
> total time for this query :( But when I change work_mem a little higher to
> 10000 total runtime decreases a little about 10% but when I change
> random_page_cost to 0.2 (I know that almost all papers say it should be
> higher then 1.0) total runtime decreases almost 3 times and lasts about 900ms
> - earlier with random_page_cost=1.2 it took 2.7s. Is it possible to have
> random_page_cost on this value?
IF random_page_cost needs to be that low, then it's likely that the
query planner is either getting bad statistics and making a poor
decision, or that you've got a corner case that it just can't figure
out. What does explain analyze <yourqueryhere> say with
random_page_cost set to 1.2 and 0.2? HAve you run analyze and vacuumed
full lately?
^ permalink raw reply [nested|flat] 37+ messages in thread
* Re: Performance problem...
@ 2005-03-15 16:17 Marcin Giedz <marcin.giedz@eulerhermes.pl>
parent: Scott Marlowe <smarlowe@g2switchworks.com>
0 siblings, 1 reply; 37+ messages in thread
From: Marcin Giedz @ 2005-03-15 16:17 UTC (permalink / raw)
To: pgsql-admin
Dnia wtorek, 15 marca 2005 17:08, Scott Marlowe napisał:
> On Tue, 2005-03-15 at 02:59, Marcin Giedz wrote:
> > Dnia poniedziałek, 14 marca 2005 19:32, Scott Marlowe napisał:
> > > On Mon, 2005-03-14 at 12:03, Marcin Giedz wrote:
> > > > Hello...
> > > >
> > > >
> > > > Our company is going to change SQL engine from MySQL to PSQL. Of
> > > > course some performance problems occured. Our server is Dual Xeon
> > > > 3.0GHz + 8GB RAM + RAID1(software - two 146GB SCSI 15k) for sql data
> > > > + RAID1(software - two 146GB SCSI 15k) for pg_xlog. Postgres.conf
> > > > parameters are as follows:
> > > >
> > > > max_connections = 150
> > > > shared_buffers = 50000 # min 16, at least max_connections*2,
> > > > 8KB each work_mem = 2048 # min 64, size in KB
> > >
> > > 50,000 shared buffers may or may not be too much. Try it at different
> > > sizes from 5,000 or so up to 50,000 and find the "knee". It's usually
> > > closer to 10,000 than 50,000, but ymmv...
> >
> > Playing with shared_buffers from 10000 to 50000 doesn't change anything
> > in total time for this query :( But when I change work_mem a little
> > higher to 10000 total runtime decreases a little about 10% but when I
> > change random_page_cost to 0.2 (I know that almost all papers say it
> > should be higher then 1.0) total runtime decreases almost 3 times and
> > lasts about 900ms - earlier with random_page_cost=1.2 it took 2.7s. Is it
> > possible to have random_page_cost on this value?
>
> IF random_page_cost needs to be that low, then it's likely that the
> query planner is either getting bad statistics and making a poor
> decision, or that you've got a corner case that it just can't figure
> out. What does explain analyze <yourqueryhere> say with
> random_page_cost set to 1.2 and 0.2? HAve you run analyze and vacuumed
> full lately?
It cann't be possible - I've run vacuum full analyze - it didn't change
anything ;)
Marcin
^ permalink raw reply [nested|flat] 37+ messages in thread
* Re: Performance problem...
@ 2005-03-15 17:00 Scott Marlowe <smarlowe@g2switchworks.com>
parent: Marcin Giedz <marcin.giedz@eulerhermes.pl>
0 siblings, 1 reply; 37+ messages in thread
From: Scott Marlowe @ 2005-03-15 17:00 UTC (permalink / raw)
To: Marcin Giedz <marcin.giedz@eulerhermes.pl>; +Cc: pgsql-admin
On Tue, 2005-03-15 at 10:17, Marcin Giedz wrote:
> Dnia wtorek, 15 marca 2005 17:08, Scott Marlowe napisał:
> > On Tue, 2005-03-15 at 02:59, Marcin Giedz wrote:
> > > Dnia poniedziałek, 14 marca 2005 19:32, Scott Marlowe napisał:
> > > > On Mon, 2005-03-14 at 12:03, Marcin Giedz wrote:
> > > > > Hello...
> > > > >
> > > > >
> > > > > Our company is going to change SQL engine from MySQL to PSQL. Of
> > > > > course some performance problems occured. Our server is Dual Xeon
> > > > > 3.0GHz + 8GB RAM + RAID1(software - two 146GB SCSI 15k) for sql data
> > > > > + RAID1(software - two 146GB SCSI 15k) for pg_xlog. Postgres.conf
> > > > > parameters are as follows:
> > > > >
> > > > > max_connections = 150
> > > > > shared_buffers = 50000 # min 16, at least max_connections*2,
> > > > > 8KB each work_mem = 2048 # min 64, size in KB
> > > >
> > > > 50,000 shared buffers may or may not be too much. Try it at different
> > > > sizes from 5,000 or so up to 50,000 and find the "knee". It's usually
> > > > closer to 10,000 than 50,000, but ymmv...
> > >
> > > Playing with shared_buffers from 10000 to 50000 doesn't change anything
> > > in total time for this query :( But when I change work_mem a little
> > > higher to 10000 total runtime decreases a little about 10% but when I
> > > change random_page_cost to 0.2 (I know that almost all papers say it
> > > should be higher then 1.0) total runtime decreases almost 3 times and
> > > lasts about 900ms - earlier with random_page_cost=1.2 it took 2.7s. Is it
> > > possible to have random_page_cost on this value?
> >
> > IF random_page_cost needs to be that low, then it's likely that the
> > query planner is either getting bad statistics and making a poor
> > decision, or that you've got a corner case that it just can't figure
> > out. What does explain analyze <yourqueryhere> say with
> > random_page_cost set to 1.2 and 0.2? HAve you run analyze and vacuumed
> > full lately?
> It cann't be possible - I've run vacuum full analyze - it didn't change
> anything ;)
>
You might want to try adjusting these values to see if you can get the
query planner to choose the faster plan without dropping
random_page_cost to 0.2. I.e. give the query planner candy and flowers,
don't just bonk it on the head with a big stick and drag it back home...
#cpu_tuple_cost = 0.01 # (same)
#cpu_index_tuple_cost = 0.001 # (same)
#cpu_operator_cost = 0.0025 # (same)
Does explain analyze show a big difference in expected an actual rows
returned for any of the parts of the query plan?
^ permalink raw reply [nested|flat] 37+ messages in thread
* Re: Performance problem...
@ 2005-03-16 13:46 Marcin Giedz <marcin.giedz@eulerhermes.pl>
parent: Scott Marlowe <smarlowe@g2switchworks.com>
0 siblings, 1 reply; 37+ messages in thread
From: Marcin Giedz @ 2005-03-16 13:46 UTC (permalink / raw)
To: pgsql-admin
Dnia wtorek, 15 marca 2005 18:00, Scott Marlowe napisał:
OK now I know I mys query lasts so long:
SELECT DISTINCT t1.Id, t1.IdTypNazwa, t1.IdFirma, t1.Nazwa, t1.NazwaAscii,
t1.MskNazwa, t3.Id, t3.numer, t3.MskNumer, t4.Id, t4.numer, t4.MskNumer,
t5.Id, t5.numer, t5.MskNumer, t6.Id, t6.numer, t6.MskNumer, t7.Id, t7.numer,
t7.MskNumer, t8.Id, t8.numer, t8.MskNumer, t9.Id, t9.numer, t9.MskNumer,
t10.Id, t10.IdTypFormaPrawna, t10.MskFormaPrawna, t11.slowo FROM firmy.adres
AS n INNER JOIN firmy.nazwa AS t1 ON t1.IdFirma = n.IdPodmiot AND
t1.IdTypNazwa = 153 AND t1.Historia = 0 LEFT JOIN firmy.numer AS t3 ON
t3.IdPodmiot = t1.IdFirma AND t3.IdTypNumer = 156 AND t3.Historia = 0 LEFT
JOIN firmy.numer AS t4 ON t4.IdPodmiot = t1.IdFirma AND t4.IdTypNumer = 160
AND t4.Historia = 0 LEFT JOIN firmy.numer AS t5 ON t5.IdPodmiot = t1.IdFirma
AND t5.IdTypNumer = 155 AND t5.Historia = 0 LEFT JOIN firmy.numer AS t6 ON
t6.IdPodmiot = t1.IdFirma AND t6.IdTypNumer = 627 AND t6.Historia = 0 LEFT
JOIN firmy.numer AS t7 ON t7.IdPodmiot = t1.IdFirma AND t7.IdTypNumer = 848
AND t7.Historia = 0 LEFT JOIN firmy.numer AS t8 ON t8.IdPodmiot = t1.IdFirma
AND t8.IdTypNumer = 763 AND t8.Historia = 0 LEFT JOIN firmy.numer AS t9 ON
t9.IdPodmiot = t1.IdFirma AND t9.IdTypNumer = 762 AND t9.Historia = 0 LEFT
JOIN firmy.formaprawna AS t10 ON t10.IdFirma = t1.IdFirma LEFT JOIN
slowniki.tslownik AS t11 ON t11.idslownik = t10.IdTypFormaPrawna AND
t11.idjezyktyp = 2 WHERE n.Miasto ILIKE 'warszawa%' LIMIT 25
...because of DISTINCT :D With DISTINCT it tooks about 0,5 minute to search
several records ..... without DISTINCT (of course a few doubled records
occured) but query tooks 10ms ;). On the other hand doubled records are
annoying ..... is it possible to avoid these without changing data in tables?
Maybe some other query?
BR,
Marcin
> On Tue, 2005-03-15 at 10:17, Marcin Giedz wrote:
> > Dnia wtorek, 15 marca 2005 17:08, Scott Marlowe napisał:
> > > On Tue, 2005-03-15 at 02:59, Marcin Giedz wrote:
> > > > Dnia poniedziałek, 14 marca 2005 19:32, Scott Marlowe napisał:
> > > > > On Mon, 2005-03-14 at 12:03, Marcin Giedz wrote:
> > > > > > Hello...
> > > > > >
> > > > > >
> > > > > > Our company is going to change SQL engine from MySQL to PSQL. Of
> > > > > > course some performance problems occured. Our server is Dual Xeon
> > > > > > 3.0GHz + 8GB RAM + RAID1(software - two 146GB SCSI 15k) for sql
> > > > > > data + RAID1(software - two 146GB SCSI 15k) for pg_xlog.
> > > > > > Postgres.conf parameters are as follows:
> > > > > >
> > > > > > max_connections = 150
> > > > > > shared_buffers = 50000 # min 16, at least
> > > > > > max_connections*2, 8KB each work_mem = 2048 # min 64,
> > > > > > size in KB
> > > > >
> > > > > 50,000 shared buffers may or may not be too much. Try it at
> > > > > different sizes from 5,000 or so up to 50,000 and find the "knee".
> > > > > It's usually closer to 10,000 than 50,000, but ymmv...
> > > >
> > > > Playing with shared_buffers from 10000 to 50000 doesn't change
> > > > anything in total time for this query :( But when I change work_mem a
> > > > little higher to 10000 total runtime decreases a little about 10% but
> > > > when I change random_page_cost to 0.2 (I know that almost all papers
> > > > say it should be higher then 1.0) total runtime decreases almost 3
> > > > times and lasts about 900ms - earlier with random_page_cost=1.2 it
> > > > took 2.7s. Is it possible to have random_page_cost on this value?
> > >
> > > IF random_page_cost needs to be that low, then it's likely that the
> > > query planner is either getting bad statistics and making a poor
> > > decision, or that you've got a corner case that it just can't figure
> > > out. What does explain analyze <yourqueryhere> say with
> > > random_page_cost set to 1.2 and 0.2? HAve you run analyze and vacuumed
> > > full lately?
> >
> > It cann't be possible - I've run vacuum full analyze - it didn't change
> > anything ;)
>
> You might want to try adjusting these values to see if you can get the
> query planner to choose the faster plan without dropping
> random_page_cost to 0.2. I.e. give the query planner candy and flowers,
> don't just bonk it on the head with a big stick and drag it back home...
>
> #cpu_tuple_cost = 0.01 # (same)
> #cpu_index_tuple_cost = 0.001 # (same)
> #cpu_operator_cost = 0.0025 # (same)
>
> Does explain analyze show a big difference in expected an actual rows
> returned for any of the parts of the query plan?
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: the planner will ignore your desire to choose an index scan if your
> joining column's datatypes do not match
^ permalink raw reply [nested|flat] 37+ messages in thread
* Re: Performance problem...
@ 2005-03-16 14:05 Scott Marlowe <smarlowe@g2switchworks.com>
parent: Marcin Giedz <marcin.giedz@eulerhermes.pl>
0 siblings, 1 reply; 37+ messages in thread
From: Scott Marlowe @ 2005-03-16 14:05 UTC (permalink / raw)
To: Marcin Giedz <marcin.giedz@eulerhermes.pl>; +Cc: pgsql-admin
On Wed, 2005-03-16 at 07:46, Marcin Giedz wrote:
> Dnia wtorek, 15 marca 2005 18:00, Scott Marlowe napisał:
>
> OK now I know I mys query lasts so long:
>
> SELECT DISTINCT t1.Id, t1.IdTypNazwa, t1.IdFirma, t1.Nazwa, t1.NazwaAscii,
> t1.MskNazwa, t3.Id, t3.numer, t3.MskNumer, t4.Id, t4.numer, t4.MskNumer,
> t5.Id, t5.numer, t5.MskNumer, t6.Id, t6.numer, t6.MskNumer, t7.Id, t7.numer,
> t7.MskNumer, t8.Id, t8.numer, t8.MskNumer, t9.Id, t9.numer, t9.MskNumer,
> t10.Id, t10.IdTypFormaPrawna, t10.MskFormaPrawna, t11.slowo FROM firmy.adres
> AS n INNER JOIN firmy.nazwa AS t1 ON t1.IdFirma = n.IdPodmiot AND
> t1.IdTypNazwa = 153 AND t1.Historia = 0 LEFT JOIN firmy.numer AS t3 ON
> t3.IdPodmiot = t1.IdFirma AND t3.IdTypNumer = 156 AND t3.Historia = 0 LEFT
> JOIN firmy.numer AS t4 ON t4.IdPodmiot = t1.IdFirma AND t4.IdTypNumer = 160
> AND t4.Historia = 0 LEFT JOIN firmy.numer AS t5 ON t5.IdPodmiot = t1.IdFirma
> AND t5.IdTypNumer = 155 AND t5.Historia = 0 LEFT JOIN firmy.numer AS t6 ON
> t6.IdPodmiot = t1.IdFirma AND t6.IdTypNumer = 627 AND t6.Historia = 0 LEFT
> JOIN firmy.numer AS t7 ON t7.IdPodmiot = t1.IdFirma AND t7.IdTypNumer = 848
> AND t7.Historia = 0 LEFT JOIN firmy.numer AS t8 ON t8.IdPodmiot = t1.IdFirma
> AND t8.IdTypNumer = 763 AND t8.Historia = 0 LEFT JOIN firmy.numer AS t9 ON
> t9.IdPodmiot = t1.IdFirma AND t9.IdTypNumer = 762 AND t9.Historia = 0 LEFT
> JOIN firmy.formaprawna AS t10 ON t10.IdFirma = t1.IdFirma LEFT JOIN
> slowniki.tslownik AS t11 ON t11.idslownik = t10.IdTypFormaPrawna AND
> t11.idjezyktyp = 2 WHERE n.Miasto ILIKE 'warszawa%' LIMIT 25
>
> ...because of DISTINCT :D With DISTINCT it tooks about 0,5 minute to search
> several records ..... without DISTINCT (of course a few doubled records
> occured) but query tooks 10ms ;). On the other hand doubled records are
> annoying ..... is it possible to avoid these without changing data in tables?
> Maybe some other query?
Can you enclose the whole query in something like:
select distinct * from (your query here) as a
and see if that helps?
^ permalink raw reply [nested|flat] 37+ messages in thread
* Re: Performance problem...
@ 2005-03-16 16:00 Marcin Giedz <marcin.giedz@eulerhermes.pl>
parent: Scott Marlowe <smarlowe@g2switchworks.com>
0 siblings, 0 replies; 37+ messages in thread
From: Marcin Giedz @ 2005-03-16 16:00 UTC (permalink / raw)
To: pgsql-admin
Dnia środa, 16 marca 2005 15:05, Scott Marlowe napisał:
> On Wed, 2005-03-16 at 07:46, Marcin Giedz wrote:
> > Dnia wtorek, 15 marca 2005 18:00, Scott Marlowe napisał:
> >
> > OK now I know I mys query lasts so long:
> >
> > SELECT DISTINCT t1.Id, t1.IdTypNazwa, t1.IdFirma, t1.Nazwa,
> > t1.NazwaAscii, t1.MskNazwa, t3.Id, t3.numer, t3.MskNumer, t4.Id,
> > t4.numer, t4.MskNumer, t5.Id, t5.numer, t5.MskNumer, t6.Id, t6.numer,
> > t6.MskNumer, t7.Id, t7.numer, t7.MskNumer, t8.Id, t8.numer, t8.MskNumer,
> > t9.Id, t9.numer, t9.MskNumer, t10.Id, t10.IdTypFormaPrawna,
> > t10.MskFormaPrawna, t11.slowo FROM firmy.adres AS n INNER JOIN
> > firmy.nazwa AS t1 ON t1.IdFirma = n.IdPodmiot AND t1.IdTypNazwa = 153 AND
> > t1.Historia = 0 LEFT JOIN firmy.numer AS t3 ON t3.IdPodmiot = t1.IdFirma
> > AND t3.IdTypNumer = 156 AND t3.Historia = 0 LEFT JOIN firmy.numer AS t4
> > ON t4.IdPodmiot = t1.IdFirma AND t4.IdTypNumer = 160 AND t4.Historia = 0
> > LEFT JOIN firmy.numer AS t5 ON t5.IdPodmiot = t1.IdFirma AND
> > t5.IdTypNumer = 155 AND t5.Historia = 0 LEFT JOIN firmy.numer AS t6 ON
> > t6.IdPodmiot = t1.IdFirma AND t6.IdTypNumer = 627 AND t6.Historia = 0
> > LEFT JOIN firmy.numer AS t7 ON t7.IdPodmiot = t1.IdFirma AND
> > t7.IdTypNumer = 848 AND t7.Historia = 0 LEFT JOIN firmy.numer AS t8 ON
> > t8.IdPodmiot = t1.IdFirma AND t8.IdTypNumer = 763 AND t8.Historia = 0
> > LEFT JOIN firmy.numer AS t9 ON t9.IdPodmiot = t1.IdFirma AND
> > t9.IdTypNumer = 762 AND t9.Historia = 0 LEFT JOIN firmy.formaprawna AS
> > t10 ON t10.IdFirma = t1.IdFirma LEFT JOIN slowniki.tslownik AS t11 ON
> > t11.idslownik = t10.IdTypFormaPrawna AND t11.idjezyktyp = 2 WHERE
> > n.Miasto ILIKE 'warszawa%' LIMIT 25
> >
> > ...because of DISTINCT :D With DISTINCT it tooks about 0,5 minute to
> > search several records ..... without DISTINCT (of course a few doubled
> > records occured) but query tooks 10ms ;). On the other hand doubled
> > records are annoying ..... is it possible to avoid these without changing
> > data in tables? Maybe some other query?
>
> Can you enclose the whole query in something like:
>
> select distinct * from (your query here) as a
>
> and see if that helps?
>
Yes it works excellent ;) Thanks so much .....
BR,
Marcin
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
^ permalink raw reply [nested|flat] 37+ messages in thread
* performance problem
@ 2005-04-12 17:04 sanjay tumula <stumula@comcast.net>
0 siblings, 0 replies; 37+ messages in thread
From: sanjay tumula @ 2005-04-12 17:04 UTC (permalink / raw)
To: pgsql-admin
i am having poor performance with postgres 8.0.0
the config of my machine is:
single xeon 3.4 Ghz, 1GB of memory, two IDE drives mounted using LVM and
JFS file system on Suse 9.2 professional.
What I am trying to do is simple...atleast in ORACLE or DB2 that I am
trying to stay away from.
I have bunch of records that i need to load...about 30mil every month
for last 18months. each month is in four parts of about 7 mil.
so i have a stage table..that i load and check to see if there are any
dups and load into prod tables...
i tried several ways...all using pl/Pgsql...details are below....
what is it that i am doing wrong? with method one...it finishes in
around 8hrs for 7.5 mil records....
with method 2...its processing 5000 records in 5 mins...but it increases
with each pass... first pass < 1min, second pass 2.5mins..and so on...
any input is greatly appreciated...other than i need to upgrade the
machine...which i am in the process of.
1. tried to reduce the 7mil, into 'manageable chunks' of about 500,000,
100,000 or even 5000.....
for commiting between...to keep transactions small.
a sample SQL that i use...
/************************************************
copy existing records with updates to history
************************************************/
INSERT INTO sms.sms_src_history
(
smsshist_id_n
,smsshist_phone_s
,status_cd_s
,smsshist_effective_date_s
,smsshist_effective_time_s
,resporg_cd_s
,dataset_cd_s
,smsshist_phone_n
,smsshist_npa_n
,smsshist_nxx_n
,smsshist_line_n
,smsshist_create_dt
,smsshist_create_user_s
,smsshist_effective_dt
)
SELECT
nextval('sms.sms_src_history_seq')
,smsscurr_phone_s
,status_cd_s
,smsscurr_effective_date_s
,smsscurr_effective_time_s
,resporg_cd_s
,dataset_cd_s
,smsscurr_phone_n
,smsscurr_npa_n
,smsscurr_nxx_n
,smsscurr_line_n
,LOCALTIMESTAMP
,'PRCURR_LOAD'
,smsscurr_effective_dt
FROM
sms.sms_src_current
WHERE
smsscurr_phone_s
IN
(
SELECT
phone_s
FROM
(
SELECT
trim(phone_s) as phone_s
,trim(status_cd_s) as status_cd_s
,trim(effective_date_s) as effective_date_s
,trim(effective_time_s) as effective_time_s
,trim(resporg_cd_s) as resporg_cd_s
FROM
sms_stage.stg_sms_src_current
WHERE
sssc_id_n between begin_rec and end_rec
EXCEPT
SELECT
smsscurr_phone_s,status_cd_s,smsscurr_effective_date_s
,smsscurr_effective_time_s,resporg_cd_s
FROM
sms.sms_src_current
) AS in_values
EXCEPT
(
SELECT trim(phone_s)
FROM sms_stage.stg_sms_src_current
WHERE sssc_id_n between begin_rec and end_rec
EXCEPT
SELECT smsscurr_phone_s
FROM sms.sms_src_current
)
)
2. declare a cursor and go thru records one by one...this one i tried
only 5000 at a time....still VERY slow
stg_curs CURSOR FOR
SELECT
sssc_id_n
,trim(phone_s) as phone_s
,trim(status_cd_s) as status_cd_s
,trim(effective_date_s) as effective_date_s
,trim(effective_time_s) as effective_time_s
,trim(resporg_cd_s) as resporg_cd_s
,trim(dataset_cd_s) as dataset_cd_s
FROM
sms_stage.stg_sms_src_current
WHERE
sssc_id_n between begin_rec and end_rec
prod_curs CURSOR FOR
SELECT *
FROM sms.sms_src_current
WHERE smsscurr_phone_n = to_number(substr(stg_rec.phone_s,1,3)
||substr(stg_rec.phone_s,5,3)
||substr(stg_rec.phone_s,9,4),'9999999999')
ORDER BY smsscurr_id_n DESC
etc etc...
3. the portion of the shell script that call the above function is
echo "Getting Record Count At: "`date`
recnum=`psql -t -c "SELECT COALESCE(MAX(sssc_id_n),0) from
sms_stage.stg_sms_src_current " sms`
echo "Done Getting Record Count At: "`date`
if [ $recnum -eq 0 ]
then
echo "No Source Records To Process In Stage" else
echo "Processing Source Data Started At: "`date`
for (( begin_rec = 1,end_rec = increment
;end_rec < recnum
;begin_rec = end_rec + 1,end_rec = end_rec + increment
))
do
echo "----------------------------------------------------"
echo "Calling process_src_data At: "`date`
echo "With"
echo "begin_rec: "$begin_rec
echo "end_rec: "$end_rec
psql -e -v precs=$begin_rec,$end_rec -f pg_process_src_data.sh sms
echo "Completed process_src_data At: "`date`
etc....
4. the following is my conf file
start of conf file:
#-------------------------------------------------------------------------------
external_pid_file = 'postmaster' # write an extra pid file
#---------------------------------------------------------------------------
# CONNECTIONS AND AUTHENTICATION
#---------------------------------------------------------------------------
# - Connection Settings -
#listen_addresses = 'localhost' # what IP interface(s) to listen on;
# defaults to localhost, '*' = any
listen_addresses = '*' # what IP interface(s) to listen on;
#port = 5432
max_connections = 200
# note: increasing max_connections costs about 500 bytes of shared
# memory per connection slot, in addition to costs from
shared_buffers
# and max_locks_per_transaction.
#superuser_reserved_connections = 2
superuser_reserved_connections = 15
#unix_socket_directory = ''
#unix_socket_group = ''
#unix_socket_permissions = 0777 # octal
unix_socket_permissions = 0700 # octal
#rendezvous_name = '' # defaults to the computer name
# - Security & Authentication -
#authentication_timeout = 60 # 1-600, in seconds
#ssl = false
ssl = true
#password_encryption = true
#krb_server_keyfile = ''
#db_user_namespace = false
#---------------------------------------------------------------------------
# RESOURCE USAGE (except WAL)
#---------------------------------------------------------------------------
# - Memory -
shared_buffers = 38400 # min 16, at least max_connections*2,
8KB each
work_mem = 153600 # min 64, size in KB
maintenance_work_mem = 102400 # min 1024, size in KB
max_stack_depth = 10240 # min 100, size in KB
# - Free Space Map -
max_fsm_pages = 200000 # min max_fsm_relations*16, 6 bytes each
max_fsm_relations = 10000 # min 100, ~50 bytes each
# - Kernel Resource Usage -
#max_files_per_process = 1000 # min 25
preload_libraries = '$libdir/plpgsql:plpgsql_init'
# - Cost-Based Vacuum Delay -
#vacuum_cost_delay = 0 # 0-1000 milliseconds
#vacuum_cost_page_hit = 1 # 0-10000 credits
#vacuum_cost_page_miss = 10 # 0-10000 credits
#vacuum_cost_page_dirty = 20 # 0-10000 credits
#vacuum_cost_limit = 200 # 0-10000 credits
# - Background writer -
bgwriter_delay = 1000 # 10-10000 milliseconds between rounds
bgwriter_percent = 5 # 0-100% of dirty buffers in each round
#bgwriter_maxpages = 100 # 0-1000 buffers max per round
#---------------------------------------------------------------------------
# WRITE AHEAD LOG
#---------------------------------------------------------------------------
# - Settings -
fsync = false # turns forced synchronization on or off
#wal_sync_method = fsync # the default varies across platforms:
# fsync, fdatasync, open_sync, or
open_datasync
wal_buffers = 40 # min 4, 8KB each
#commit_delay = 0 # range 0-100000, in microseconds
#commit_siblings = 5 # range 1-1000
# - Checkpoints -
checkpoint_segments = 10 # in logfile segments, min 1, 16MB each
checkpoint_timeout = 600 # range 30-3600, in seconds
#checkpoint_warning = 30 # 0 is off, in seconds
# - Archiving -
#archive_command = '' # command to use to archive a logfile
segment
archive_command = 'test ! -f /data/archivelog/"%f" && cp "%p"
/data/archivelog/"%f"'
#---------------------------------------------------------------------------
# QUERY TUNING
#---------------------------------------------------------------------------
# - Planner Method Configuration -
#enable_hashagg = true
#enable_hashjoin = true
#enable_indexscan = true
#enable_mergejoin = true
#enable_nestloop = true
#enable_seqscan = true
#enable_sort = true
#enable_tidscan = true
# - Planner Cost Constants -
effective_cache_size = 3000 # typically 8KB each
#random_page_cost = 4 # units are one sequential page fetch cost
#cpu_tuple_cost = 0.01 # (same)
#cpu_index_tuple_cost = 0.001 # (same)
#cpu_operator_cost = 0.0025 # (same)
# - Genetic Query Optimizer -
#geqo = true
#geqo_threshold = 12
#geqo_effort = 5 # range 1-10
#geqo_pool_size = 0 # selects default based on effort
#geqo_generations = 0 # selects default based on effort
#geqo_selection_bias = 2.0 # range 1.5-2.0
# - Other Planner Options -
#default_statistics_target = 10 # range 1-1000
#from_collapse_limit = 8
#join_collapse_limit = 8 # 1 disables collapsing of explicit JOINs
#---------------------------------------------------------------------------
# ERROR REPORTING AND LOGGING
#---------------------------------------------------------------------------
# - Where to Log -
#log_destination = 'stderr' # Valid values are combinations of stderr,
# syslog and eventlog, depending on
# platform.
# This is relevant when logging to stderr:
redirect_stderr = true # Enable capturing of stderr into log files.
# These are only relevant if redirect_stderr is true:
log_directory = 'pg_log' # Directory where log files are written.
# May be specified absolute or relative to
PGDATA
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log' # Log file name pattern.
# May include strftime() escapes
log_truncate_on_rotation = false # If true, any existing log file of the
# same name as the new log file will be
truncated
# rather than appended to. But such truncation
# only occurs on time-driven rotation,
# not on restarts or size-driven rotation.
# Default is false, meaning append to existing
# files in all cases.
log_rotation_age = 14400 # Automatic rotation of logfiles will happen
after
# so many minutes. 0 to disable.
#log_rotation_size = 10240 # Automatic rotation of logfiles will happen
after
# so many kilobytes of log output. 0 to
disable.
# These are relevant when logging to syslog:
#syslog_facility = 'LOCAL0'
#syslog_ident = 'postgres'
# - When to Log -
#client_min_messages = notice # Values, in order of decreasing detail:
# debug5, debug4, debug3, debug2, debug1,
# log, notice, warning, error
#log_min_messages = notice # Values, in order of decreasing detail:
# debug5, debug4, debug3, debug2, debug1,
# info, notice, warning, error, log,
fatal,
# panic
#log_error_verbosity = default # terse, default, or verbose messages
#log_min_error_statement = panic # Values in order of increasing severity:
# debug5, debug4, debug3, debug2, debug1,
# info, notice, warning, error,
panic(off)
log_min_duration_statement = 2147483 # -1 is disabled, in milliseconds.
silent_mode = true # DO NOT USE without syslog or
redirect_stderr
# - What to Log -
#debug_print_parse = false
#debug_print_rewritten = false
#debug_print_plan = false
#debug_pretty_print = false
#log_connections = false
#log_disconnections = false
#log_duration = false
#log_line_prefix = '%t' # e.g. '<%u%%%d> '
# %u=user name %d=database name
# %r=remote host and port
# %p=PID %t=timestamp %i=command tag
# %c=session id %l=session line number
# %s=session start timestamp
%x=transaction id
# %q=stop here in non-session processes
# %%='%'
#log_statement = 'none' # none, mod, ddl, all
#log_hostname = false
#---------------------------------------------------------------------------
# RUNTIME STATISTICS
#---------------------------------------------------------------------------
# - Statistics Monitoring -
#log_parser_stats = false
#log_planner_stats = false
#log_executor_stats = false
log_statement_stats = true
# - Query/Index Statistics Collector -
#stats_start_collector = true
stats_command_string = true
#stats_block_level = false
#stats_row_level = false
#stats_reset_on_server_start = true
#---------------------------------------------------------------------------
# CLIENT CONNECTION DEFAULTS
#---------------------------------------------------------------------------
# - Statement Behavior -
#search_path = '$user,public' # schema names
#default_tablespace = '' # a tablespace name, or '' for default
#check_function_bodies = true
#default_transaction_isolation = 'read committed'
#default_transaction_read_only = false
#statement_timeout = 0 # 0 is disabled, in milliseconds
# - Locale and Formatting -
#datestyle = 'iso, mdy'
#timezone = unknown # actually, defaults to TZ environment
setting
#australian_timezones = false
#extra_float_digits = 0 # min -15, max 2
#client_encoding = sql_ascii # actually, defaults to database encoding
# These settings are initialized by initdb -- they might be changed
lc_messages = 'en_US.UTF-8' # locale for system error
message strings
lc_monetary = 'en_US.UTF-8' # locale for monetary formatting
lc_numeric = 'en_US.UTF-8' # locale for number formatting
lc_time = 'en_US.UTF-8' # locale for time formatting
# - Other Defaults -
#explain_pretty_print = true
#dynamic_library_path = '$libdir'
#---------------------------------------------------------------------------
# LOCK MANAGEMENT
#---------------------------------------------------------------------------
deadlock_timeout = 30000 # in milliseconds
#max_locks_per_transaction = 64 # min 10, ~200*max_connections bytes each
#---------------------------------------------------------------------------
# VERSION/PLATFORM COMPATIBILITY
#---------------------------------------------------------------------------
# - Previous Postgres Versions -
add_missing_from = false
#regex_flavor = advanced # advanced, extended, or basic
#sql_inheritance = true
default_with_oids = false
# - Other Platforms & Clients -
#transform_null_equals = false
# END OF CONF FILE
^ permalink raw reply [nested|flat] 37+ messages in thread
* performance problem
@ 2005-04-14 16:58 sanjay tumula <stumula@comcast.net>
0 siblings, 0 replies; 37+ messages in thread
From: sanjay tumula @ 2005-04-14 16:58 UTC (permalink / raw)
To: pgsql-admin
looks like reducing shared_buffers and turning off archiving might be
helping...i will post a followup in a few days...
kind of disappointed that no one bothered to reply yet though.
^ permalink raw reply [nested|flat] 37+ messages in thread
* performance problem
@ 2005-04-15 14:24 sanjay tumula <stumula@comcast.net>
0 siblings, 0 replies; 37+ messages in thread
From: sanjay tumula @ 2005-04-15 14:24 UTC (permalink / raw)
To: pgsql-admin
Ruud,
thanx for your reply.
I am narrowing down on the problem...found that Suse 9.2 has a lot of
memory related problems.
reverted back to 9.1 and rebuilding the app..will post an update.
you suggestion will not work for me though...i need to check existing
records and create new records..which cannot be done on one table.
unless you are going thru hoops and doing a lot of text processing....at
which point why do you need a database :-)
also here is something interesting that i found regarding IN vs = on a
btree index.
select col1, col2
from table1
where col1 in ( 1,2,3)
;
this comes right back
select col1, col2
from table2
where col1 = 1
;
comes right back.
select col1,col2
from table2
where col1
IN
(
select col1 from table2
where col1 between 1 and 2
)
;
this takes a while....
which tells me IN is still not optimized..or am i missing something?
lastly..please post to the forum and not directly to my email....so the
community can see the 'whole' picture.
thanx
Sanjay
^ permalink raw reply [nested|flat] 37+ messages in thread
* performance problem
@ 2005-04-17 05:54 sanjay tumula <stumula@comcast.net>
0 siblings, 0 replies; 37+ messages in thread
From: sanjay tumula @ 2005-04-17 05:54 UTC (permalink / raw)
To: pgsql-admin
the problem is solved...
once i reverted back to 9.1...things seems to be much better.
i also revamped a lot of my code to optimize it for speed.
will post the results after i complete a sizable chunk of my data.
the in also seems to working much better now...
thanx
^ permalink raw reply [nested|flat] 37+ messages in thread
end of thread, other threads:[~2005-04-17 05:54 UTC | newest]
Thread overview: 37+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-11-18 20:43 performance problem Rick Gigger <rick@alpinenetworking.com>
2003-11-18 21:03 ` Mike Mascari <mascarm@mascari.com>
2003-11-18 21:41 ` Rick Gigger <rick@alpinenetworking.com>
2003-11-20 20:52 ` Rick Gigger <rick@alpinenetworking.com>
2003-11-20 21:06 ` Alvaro Herrera Munoz <alvherre@dcc.uchile.cl>
2003-11-20 21:12 ` Rick Gigger <rick@alpinenetworking.com>
2003-11-20 21:13 ` Mike Mascari <mascarm@mascari.com>
2003-11-20 22:20 ` Rick Gigger <rick@alpinenetworking.com>
2003-11-18 21:24 ` scott.marlowe <scott.marlowe@ihs.com>
2003-11-18 23:54 ` Rick Gigger <rick@alpinenetworking.com>
2003-11-19 02:25 ` Doug McNaught <doug@mcnaught.org>
2003-11-20 20:41 ` Rick Gigger <rick@alpinenetworking.com>
2003-11-20 20:59 ` fred@redhotpenguin.com
2003-11-20 21:07 ` Rick Gigger <rick@alpinenetworking.com>
2003-11-20 21:11 ` Andrew Sullivan <andrew@libertyrms.info>
2003-11-20 21:19 ` Tom Lane <tgl@sss.pgh.pa.us>
2003-11-20 22:21 ` Rick Gigger <rick@alpinenetworking.com>
2003-11-20 23:50 ` Bruce Momjian <pgman@candle.pha.pa.us>
2003-11-19 02:30 ` Martijn van Oosterhout <kleptog@svana.org>
2003-11-18 21:50 ` Martin Marques <martin@bugs.unl.edu.ar>
2004-05-27 15:00 Performance Problem Frank Smith <frank.smith@aeat.co.uk>
2004-05-31 03:00 ` Re: Performance Problem Christopher Browne <cbbrowne@acm.org>
2004-05-31 10:41 ` Re: Performance Problem Harald Fuchs <hf517@protecting.net>
2004-06-02 15:09 Re: Performance Problem Frank Smith <frank.smith@aeat.co.uk>
2005-03-14 18:03 Performance problem... Marcin Giedz <marcin.giedz@eulerhermes.pl>
2005-03-14 18:32 ` Re: Performance problem... Scott Marlowe <smarlowe@g2switchworks.com>
2005-03-15 08:59 ` Re: Performance problem... Marcin Giedz <marcin.giedz@eulerhermes.pl>
2005-03-15 16:08 ` Re: Performance problem... Scott Marlowe <smarlowe@g2switchworks.com>
2005-03-15 16:17 ` Re: Performance problem... Marcin Giedz <marcin.giedz@eulerhermes.pl>
2005-03-15 17:00 ` Re: Performance problem... Scott Marlowe <smarlowe@g2switchworks.com>
2005-03-16 13:46 ` Re: Performance problem... Marcin Giedz <marcin.giedz@eulerhermes.pl>
2005-03-16 14:05 ` Re: Performance problem... Scott Marlowe <smarlowe@g2switchworks.com>
2005-03-16 16:00 ` Re: Performance problem... Marcin Giedz <marcin.giedz@eulerhermes.pl>
2005-04-12 17:04 performance problem sanjay tumula <stumula@comcast.net>
2005-04-14 16:58 performance problem sanjay tumula <stumula@comcast.net>
2005-04-15 14:24 performance problem sanjay tumula <stumula@comcast.net>
2005-04-17 05:54 performance problem sanjay tumula <stumula@comcast.net>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox