pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
for help!
8+ messages / 7 participants
[nested] [flat]

* for help!
@ 2003-04-15 09:44 linweidong <wdlin@sis.sh.cn>
  2003-04-15 09:54 ` Re: for help! Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
  2003-04-15 16:34 ` Re: for help! scott.marlowe <scott.marlowe@ihs.com>
  0 siblings, 2 replies; 8+ messages in thread

From: linweidong @ 2003-04-15 09:44 UTC (permalink / raw)
  To: pgsql-performance

Hi,



 I am writing to you to discuss the performance problem of postgreSQL
database we encountered in our project. I  want to get suggestions from you.



The postgreSQL database we used need to process several millions records.
There are only six tables in the database. one of them contains several
million records, the  Others are less smaller. We need select more than 100
thousands records from the talbe which contains several million records in
10 seconds.  In the process of selecting, the speed of selecting is not
stable. Sometimes it cost 2 minutes , but sometimes 20 seconds. After
analyzing the time wasting in the process, we found the speed of  function
Count(*) is very slow. At the same time we have finished the setup of some
parameters like max_fsm_relation, max_fsm_pages, share memory size etc, but
the performance is not improved satisfied.



Under this condition, I want get some useful suggestion from you. How to
optimize the database?  How to improve the Count(*)? Because we  want to get
the number of records in the recordset  we got.



Thank you every much! I hope hear from you soon.


Wind


2003-4-15

^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: for help!
  2003-04-15 09:44 for help! linweidong <wdlin@sis.sh.cn>
@ 2003-04-15 09:54 ` Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
  2003-04-15 15:40   ` Re: for help! Will LaShell <will@lashell.net>
  1 sibling, 1 reply; 8+ messages in thread

From: Shridhar Daithankar @ 2003-04-15 09:54 UTC (permalink / raw)
  To: pgsql-performance

On Tuesday 15 April 2003 15:14, you wrote:
> The postgreSQL database we used need to process several millions records.
> There are only six tables in the database. one of them contains several
> million records, the  Others are less smaller. We need select more than 100
> thousands records from the talbe which contains several million records in
> 10 seconds.  In the process of selecting, the speed of selecting is not
> stable. Sometimes it cost 2 minutes , but sometimes 20 seconds. After
> analyzing the time wasting in the process, we found the speed of  function
> Count(*) is very slow. At the same time we have finished the setup of some
> parameters like max_fsm_relation, max_fsm_pages, share memory size etc, but
> the performance is not improved satisfied.

Why do you need to do select count(*) to select more than 100 thousand 
records?

Postgresql being MVCC database, select count(*) is not going to be anywhere 
near good, especially if you have transactions occuring on table.

As far as just selecting rows from table, that should be tad fast if there are 
proper indexes, table in analyzed every now and then and there are enough 
shared buffers.

If you post your queries and table schemas, that would be much helpful. Your 
tweaked settings in postgresql.conf and hardware spec. would be good as well.

> Under this condition, I want get some useful suggestion from you. How to
> optimize the database?  How to improve the Count(*)? Because we  want to
> get the number of records in the recordset  we got.

If you are using say libpq, you don't need to issue a select count(*) where 
foo and select where foo, to obtain record count and the records themselves. 
I believe every other interface stemming from libpq should provide any such 
hooks as well. Never used any other myself (barring ecpg)

 HTH

 Shridhar




^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: for help!
  2003-04-15 09:44 for help! linweidong <wdlin@sis.sh.cn>
  2003-04-15 09:54 ` Re: for help! Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
@ 2003-04-15 15:40   ` Will LaShell <will@lashell.net>
  0 siblings, 0 replies; 8+ messages in thread

From: Will LaShell @ 2003-04-15 15:40 UTC (permalink / raw)
  To: Shridhar Daithankar <shridhar_daithankar@persistent.co.in>; +Cc: pgsql-performance

<SNIP>
> > Under this condition, I want get some useful suggestion from you. How to
> > optimize the database?  How to improve the Count(*)? Because we  want to
> > get the number of records in the recordset  we got.
> 
> If you are using say libpq, you don't need to issue a select count(*) where 
> foo and select where foo, to obtain record count and the records themselves. 
> I believe every other interface stemming from libpq should provide any such 
> hooks as well. Never used any other myself (barring ecpg)

The python interfaces most definitely do. Doing the count is quite
unnecessary just as Shridhar points out.

>  HTH
> 
>  Shridhar
> 

Attachments:

  signature.asc (application/pgp-signature, 188B) download

^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: for help!
  2003-04-15 09:44 for help! linweidong <wdlin@sis.sh.cn>
@ 2003-04-15 16:34 ` scott.marlowe <scott.marlowe@ihs.com>
  2003-04-16 15:48   ` Re: for help! Josh Berkus <josh@agliodbs.com>
  1 sibling, 1 reply; 8+ messages in thread

From: scott.marlowe @ 2003-04-15 16:34 UTC (permalink / raw)
  To: linweidong <wdlin@sis.sh.cn>; +Cc: pgsql-performance

On Tue, 15 Apr 2003, linweidong wrote:

> Under this condition, I want get some useful suggestion from you. How to
> optimize the database?  How to improve the Count(*)? Because we  want to get
> the number of records in the recordset  we got.

Well, you can always use the trick of putting an on insert / delete 
trigger on the table that maintains a single row table with the current 
count.  That way, whenever a row is added or removed, the count is 
updated.  this will slow down inserts and deletes a little, but TANSTAAFL.




^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: for help!
  2003-04-15 09:44 for help! linweidong <wdlin@sis.sh.cn>
  2003-04-15 16:34 ` Re: for help! scott.marlowe <scott.marlowe@ihs.com>
@ 2003-04-16 15:48   ` Josh Berkus <josh@agliodbs.com>
  2003-04-16 15:53     ` Re: for help! Andrew Sullivan <andrew@libertyrms.info>
  0 siblings, 1 reply; 8+ messages in thread

From: Josh Berkus @ 2003-04-16 15:48 UTC (permalink / raw)
  To: scott.marlowe <scott.marlowe@ihs.com>; linweidong <wdlin@sis.sh.cn>; +Cc: pgsql-performance

Scott,

> Well, you can always use the trick of putting an on insert / delete
> trigger on the table that maintains a single row table with the current
> count.  That way, whenever a row is added or removed, the count is
> updated.  this will slow down inserts and deletes a little, but TANSTAAFL.

BTW, I tested this for a client.   I found the performance penalty on inserts 
and updates to be:

-- For a single stream of intermittent updates from a single connection
   on an adequately powered server with moderate disk support (IDE Linux RAID)
   (100 inserts/updates per minute, with VACUUM every 5 minutes)
   PL/pgSQL Trigger:  20% penalty      C Trigger: 9-11% penalty

-- For 5 streams of inserts and updates at high volume on an overloaded
   server with moderate disk support (dual fast SCSI disks)
   (1000 inserts/updates per minute, vacuum every 5 minutes)
   PL/pgSQL Trigger: 65% penalty    C Trigger: 40% penalty

Please note that the effective performance penalty on inserts and updates was 
dramatically higher for large batches of updates than for small ones.

-- 
Josh Berkus
Aglio Database Solutions
San Francisco




^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: for help!
  2003-04-15 09:44 for help! linweidong <wdlin@sis.sh.cn>
  2003-04-15 16:34 ` Re: for help! scott.marlowe <scott.marlowe@ihs.com>
  2003-04-16 15:48   ` Re: for help! Josh Berkus <josh@agliodbs.com>
@ 2003-04-16 15:53     ` Andrew Sullivan <andrew@libertyrms.info>
  2003-04-16 16:01       ` Re: for help! Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 8+ messages in thread

From: Andrew Sullivan @ 2003-04-16 15:53 UTC (permalink / raw)
  To: pgsql-performance

On Wed, Apr 16, 2003 at 08:48:36AM -0700, Josh Berkus wrote:
> Scott,
> 
> > Well, you can always use the trick of putting an on insert / delete
> > trigger on the table that maintains a single row table with the current
> > count.  That way, whenever a row is added or removed, the count is

> BTW, I tested this for a client.   I found the performance penalty
> on inserts and updates to be:

[. . .]

> Please note that the effective performance penalty on inserts and
> updates was dramatically higher for large batches of updates than
> for small ones.

Presumably the problem was to do with contention?  This is why I
don't really like the "update one row" approach for this sort of
thing.

But you _could_ write a trigger which inserts into a "staging" table,
and write a little daemon which only updates the count table with the
data from the staging table.  It's a mighty ugly hack, but it ought
to work.

A

-- 
----
Andrew Sullivan                         204-4141 Yonge Street
Liberty RMS                           Toronto, Ontario Canada
<andrew@libertyrms.info>                              M2P 2A8
                                         +1 416 646 3304 x110




^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: for help!
  2003-04-15 09:44 for help! linweidong <wdlin@sis.sh.cn>
  2003-04-15 16:34 ` Re: for help! scott.marlowe <scott.marlowe@ihs.com>
  2003-04-16 15:48   ` Re: for help! Josh Berkus <josh@agliodbs.com>
  2003-04-16 15:53     ` Re: for help! Andrew Sullivan <andrew@libertyrms.info>
@ 2003-04-16 16:01       ` Tom Lane <tgl@sss.pgh.pa.us>
  2003-04-16 16:11         ` Re: for help! Andrew Sullivan <andrew@libertyrms.info>
  0 siblings, 1 reply; 8+ messages in thread

From: Tom Lane @ 2003-04-16 16:01 UTC (permalink / raw)
  To: Andrew Sullivan <andrew@libertyrms.info>; +Cc: pgsql-performance

Andrew Sullivan <andrew@libertyrms.info> writes:
> But you _could_ write a trigger which inserts into a "staging" table,
> and write a little daemon which only updates the count table with the
> data from the staging table.  It's a mighty ugly hack, but it ought
> to work.

The $64 question with this sort of thing is "how accurate (up-to-date)
does the count have to be?".

Given that Josh is willing to vacuum every five minutes, he might find
that returning pg_class.reltuples is Close Enough (TM).

			regards, tom lane




^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: for help!
  2003-04-15 09:44 for help! linweidong <wdlin@sis.sh.cn>
  2003-04-15 16:34 ` Re: for help! scott.marlowe <scott.marlowe@ihs.com>
  2003-04-16 15:48   ` Re: for help! Josh Berkus <josh@agliodbs.com>
  2003-04-16 15:53     ` Re: for help! Andrew Sullivan <andrew@libertyrms.info>
  2003-04-16 16:01       ` Re: for help! Tom Lane <tgl@sss.pgh.pa.us>
@ 2003-04-16 16:11         ` Andrew Sullivan <andrew@libertyrms.info>
  0 siblings, 0 replies; 8+ messages in thread

From: Andrew Sullivan @ 2003-04-16 16:11 UTC (permalink / raw)
  To: pgsql-performance

On Wed, Apr 16, 2003 at 12:01:56PM -0400, Tom Lane wrote:
> Given that Josh is willing to vacuum every five minutes, he might find
> that returning pg_class.reltuples is Close Enough (TM).

Certainly, it's not going to be any farther off than the
staging-table+real-table approach, anyway.

A

-- 
----
Andrew Sullivan                         204-4141 Yonge Street
Liberty RMS                           Toronto, Ontario Canada
<andrew@libertyrms.info>                              M2P 2A8
                                         +1 416 646 3304 x110




^ permalink  raw  reply  [nested|flat] 8+ messages in thread


end of thread, other threads:[~2003-04-16 16:11 UTC | newest]

Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-04-15 09:44 for help! linweidong <wdlin@sis.sh.cn>
2003-04-15 09:54 ` Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
2003-04-15 15:40   ` Will LaShell <will@lashell.net>
2003-04-15 16:34 ` scott.marlowe <scott.marlowe@ihs.com>
2003-04-16 15:48   ` Josh Berkus <josh@agliodbs.com>
2003-04-16 15:53     ` Andrew Sullivan <andrew@libertyrms.info>
2003-04-16 16:01       ` Tom Lane <tgl@sss.pgh.pa.us>
2003-04-16 16:11         ` Andrew Sullivan <andrew@libertyrms.info>

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