pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedPerformance decrease
5+ messages / 4 participants
[nested] [flat]
* Performance decrease
@ 2006-04-20 15:20 Radovan Antloga <radovan.antloga@siol.net>
2006-04-20 15:41 ` Re: Performance decrease Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 5+ messages in thread
From: Radovan Antloga @ 2006-04-20 15:20 UTC (permalink / raw)
To: pgsql-performance
I'm new to PG and I'm testing default PG settings
for now.
I have PG 8.1.3. installed with autovacuum=on.
My test table has 15830 records with 190 fields.
I have different fields types (date, numeric, varchar,
integer, smallint,...).
I decided to evaluate PG because I need to use schemas.
First test I did is not very promising.
I tried to update one fields in test table several times
to see how PG react on this.
I do like this:
update table
set field = null
After first execute I get time 3 seconds. Then I repeat
this update. After each update time increase. I get
4 sec, 7 sec, 10 sec, 12 sec, 15 sec, 18 sec, 21 sec.
Is this normal (default) behaviour or I must do something
to prevent this.
Regards,
Radovan Antloga
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Performance decrease
2006-04-20 15:20 Performance decrease Radovan Antloga <radovan.antloga@siol.net>
@ 2006-04-20 15:41 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-04-20 16:10 ` Re: Performance decrease Radovan Antloga <radovan.antloga@siol.net>
0 siblings, 1 reply; 5+ messages in thread
From: Tom Lane @ 2006-04-20 15:41 UTC (permalink / raw)
To: Radovan Antloga <radovan.antloga@siol.net>; +Cc: pgsql-performance
"Radovan Antloga" <radovan.antloga@siol.net> writes:
> My test table has 15830 records with 190 fields.
190 fields in a table seems like rather a lot ... is that actually
representative of your intended applications?
> I do like this:
> update table
> set field = null
Again, is that representative of something you'll be doing a lot in
practice? Most apps don't often update every row of a table, in my
experience.
> After first execute I get time 3 seconds. Then I repeat
> this update. After each update time increase. I get
> 4 sec, 7 sec, 10 sec, 12 sec, 15 sec, 18 sec, 21 sec.
There should be some increase because of the addition of dead rows,
but both the original 3 seconds and the rate of increase seem awfully
high for such a small table. What are you running this on?
For comparison purposes, here's what I see on a full-table UPDATE
of a 10000-row table on a rather slow HP box:
regression=# \timing
Timing is on.
regression=# create table t1 as select * from tenk1;
SELECT
Time: 1274.213 ms
regression=# update t1 set unique2 = null;
UPDATE 10000
Time: 565.664 ms
regression=# update t1 set unique2 = null;
UPDATE 10000
Time: 589.839 ms
regression=# update t1 set unique2 = null;
UPDATE 10000
Time: 593.735 ms
regression=# update t1 set unique2 = null;
UPDATE 10000
Time: 615.575 ms
regression=# update t1 set unique2 = null;
UPDATE 10000
Time: 755.456 ms
regression=#
Vacuuming brings the time back down:
regression=# vacuum t1;
VACUUM
Time: 242.406 ms
regression=# update t1 set unique2 = null;
UPDATE 10000
Time: 458.028 ms
regression=#
regards, tom lane
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Performance decrease
2006-04-20 15:20 Performance decrease Radovan Antloga <radovan.antloga@siol.net>
2006-04-20 15:41 ` Re: Performance decrease Tom Lane <tgl@sss.pgh.pa.us>
@ 2006-04-20 16:10 ` Radovan Antloga <radovan.antloga@siol.net>
2006-04-20 17:00 ` Re: Performance decrease Jim C. Nasby <jnasby@pervasive.com>
2006-04-20 21:19 ` Re: Performance decrease Guido Neitzer <guido.neitzer@pharmaline.de>
0 siblings, 2 replies; 5+ messages in thread
From: Radovan Antloga @ 2006-04-20 16:10 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-performance
>190 fields in a table seems like rather a lot ... is that actually
>representative of your intended applications?
Test table is like table I use in production
with Firebird and Oracle db. Table has a lot of smallint
and integer fields. As you can see I have Firebird for
low cost projects (small companies) and Oracle medium
or large project.
>Again, is that representative of something you'll be doing a lot in
>practice? Most apps don't often update every row of a table, in my
>experience.
I agree with you !
I have once or twice a month update on many records (~6000) but
not so many. I did not expect PG would have problems with
updating 15800 records.
My test was on Windows XP SP2.
I have AMD 64 2.1 GHz cpu with
1GB ram.
Regards,
Radovan Antloga
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Performance decrease
2006-04-20 15:20 Performance decrease Radovan Antloga <radovan.antloga@siol.net>
2006-04-20 15:41 ` Re: Performance decrease Tom Lane <tgl@sss.pgh.pa.us>
2006-04-20 16:10 ` Re: Performance decrease Radovan Antloga <radovan.antloga@siol.net>
@ 2006-04-20 17:00 ` Jim C. Nasby <jnasby@pervasive.com>
1 sibling, 0 replies; 5+ messages in thread
From: Jim C. Nasby @ 2006-04-20 17:00 UTC (permalink / raw)
To: Radovan Antloga <radovan.antloga@siol.net>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-performance
On Thu, Apr 20, 2006 at 06:10:21PM +0200, Radovan Antloga wrote:
> I have once or twice a month update on many records (~6000) but
> not so many. I did not expect PG would have problems with
> updating 15800 records.
And generally speaking, it doesn't. But you do need to ensure that
you're vacuuming the database frequently enough. Autovacuum is a good
way to do that.
> My test was on Windows XP SP2.
> I have AMD 64 2.1 GHz cpu with
> 1GB ram.
One think to keep in mind is that the windows code is rather new, so it
is possible to find some performance issues there.
--
Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Performance decrease
2006-04-20 15:20 Performance decrease Radovan Antloga <radovan.antloga@siol.net>
2006-04-20 15:41 ` Re: Performance decrease Tom Lane <tgl@sss.pgh.pa.us>
2006-04-20 16:10 ` Re: Performance decrease Radovan Antloga <radovan.antloga@siol.net>
@ 2006-04-20 21:19 ` Guido Neitzer <guido.neitzer@pharmaline.de>
1 sibling, 0 replies; 5+ messages in thread
From: Guido Neitzer @ 2006-04-20 21:19 UTC (permalink / raw)
To: Radovan Antloga <radovan.antloga@siol.net>; +Cc: pgsql-performance
On 20.04.2006, at 18:10 Uhr, Radovan Antloga wrote:
> I have once or twice a month update on many records (~6000) but
> not so many. I did not expect PG would have problems with
> updating 15800 records.
It has no problems with that. We have a database where we often
update/insert rows with about one hundred columns. No problem so far.
Performance is in the sub 10ms range. The whole table has about
100000 records.
Do you wrap every update in a separate transaction? I do commits
every 200 updates for bulk updates.
cug
--
PharmaLine, Essen, GERMANY
Software and Database Development
Attachments:
smime.p7s (application/pkcs7-signature, 2.4K) download
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2006-04-20 21:19 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-04-20 15:20 Performance decrease Radovan Antloga <radovan.antloga@siol.net>
2006-04-20 15:41 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-04-20 16:10 ` Radovan Antloga <radovan.antloga@siol.net>
2006-04-20 17:00 ` Jim C. Nasby <jnasby@pervasive.com>
2006-04-20 21:19 ` Guido Neitzer <guido.neitzer@pharmaline.de>
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