pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Slow response of PostgreSQL
18+ messages / 9 participants
[nested] [flat]

* Slow response of PostgreSQL
@ 2004-02-17 06:24  Saleem Burhani Baloch <peseek@khi.wol.net.pk>
  0 siblings, 4 replies; 18+ messages in thread

From: Saleem Burhani Baloch @ 2004-02-17 06:24 UTC (permalink / raw)
  To: pgsql-performance

Hello,

I m checking Postgresql and MS-SQl database server for our new development. On a very first query Postresql is out performed and I think it is very disappointing. My query consists on a single table only on both machines.

Table Structure
                    Table "inv_detail"
  Attribute   |         Type          |      Modifier
--------------+-----------------------+--------------------
 inv_no       | integer               | not null
 unit_id      | character(4)          | not null
 item         | character varying(90) | not null
 qty          | double precision      | not null default 0
 rate         | double precision      | not null default 0
 unit         | character varying(20) | not null
 vl_ex_stax   | double precision      | not null default 0
 stax_prc     | double precision      | not null default 0
 adl_stax_prc | double precision      | not null default 0
 package      | character varying(12) |

Having 440,000 Records.

My Query
--------
select count(*), sum(vl_ex_stax) , sum(qty) , unit from inv_detail group by unit;
on both databases.

PostgreSQL return result in 50 sec every time.
MS-SQL     return result in  2 sec every time.

MS-SQL Machine
**************
Athlon 600Mhz. (Unbranded)
256 MB Ram. ( 133 Mhz)
40 GB Baracude NTFS File System.
Windows 2000 Server Enterprise.
MS-SQL 2000 Enterprise. (Default Settings)

PostgreSQL Machine
******************
P-III 600Mhz (Dell Precision 220)
256 MB Ram (RD Ram)
40 GB Baracuda Ext2 File System.
RedHat 7.2
PostgreSQL 7.1.3-2

My PostgreSQL Conf is
*********************
log_connections = yes
syslog = 2
effective_cache_size = 327680
sort_mem = 10485760
max_connections = 64
shared_buffers = 512
wal_buffers = 1024

NOTICE:  QUERY PLAN:
********************
Aggregate  (cost=inf..inf rows=44000 width=28)
  ->  Group  (cost=inf..inf rows=440000 width=28)
        ->  Sort  (cost=inf..inf rows=440000 width=28)
              ->  Seq Scan on inv_detail  (cost=0.00..11747.00 rows=440000 width=28)
EXPLAIN

Even if I dont compare postgres with any other database server the time taken is alarmingly high. These settings are not good I know, but the Postgres result is very un-acceptable. I m looking forward for comments to change the conf setting for acceptable results.

And I have two more questions :

1- How can I lock a single record so that other users can only read it. ??
2- one user executes a query it will be process and when another user executes the same query having the same result should not again go for processing. The result should be come from the cache. Is this possible in postgres ??


Saleem

pgsql-performance@postgresql.org





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

* Re: Slow response of PostgreSQL
@ 2004-02-17 06:59  Christopher Kings-Lynne <chriskl@familyhealth.com.au>
  parent: Saleem Burhani Baloch <peseek@khi.wol.net.pk>
  3 siblings, 1 reply; 18+ messages in thread

From: Christopher Kings-Lynne @ 2004-02-17 06:59 UTC (permalink / raw)
  To: Saleem Burhani Baloch <peseek@khi.wol.net.pk>; +Cc: pgsql-performance

> select count(*), sum(vl_ex_stax) , sum(qty) , unit from inv_detail group by unit;
> on both databases.
> 
> PostgreSQL return result in 50 sec every time.
> MS-SQL     return result in  2 sec every time.

> My PostgreSQL Conf is
> *********************
> log_connections = yes
> syslog = 2
> effective_cache_size = 327680
> sort_mem = 10485760
> max_connections = 64
> shared_buffers = 512
> wal_buffers = 1024

This is a shockingly bad postgresql.conf.  I'm not surprised you have 
performance problems.  Change this:

effective_cache_size = 4000
sort_mem = 4096
shared_buffers = 1000
wal_buffers = 8

Also, you need a LOT more RAM in your PostgreSQL machine, at least half 
a gig for a basic database server.

> 1- How can I lock a single record so that other users can only read it. ??

You cannot do that in PostgreSQL.

> 2- one user executes a query it will be process and when another user executes the same query having the same result should not again go for processing. The result should be come from the cache. Is this possible in postgres ??

No, implement it in your application.  Prepared queries and stored 
procedures might help you here.

Chris




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

* Re: Slow response of PostgreSQL
@ 2004-02-17 07:42  Stephan Szabo <sszabo@megazone.bigpanda.com>
  parent: Saleem Burhani Baloch <peseek@khi.wol.net.pk>
  3 siblings, 0 replies; 18+ messages in thread

From: Stephan Szabo @ 2004-02-17 07:42 UTC (permalink / raw)
  To: Saleem Burhani Baloch <peseek@khi.wol.net.pk>; +Cc: pgsql-performance

On Tue, 17 Feb 2004, Saleem Burhani Baloch wrote:

> select count(*), sum(vl_ex_stax) , sum(qty) , unit from inv_detail group by unit;
> on both databases.

> PostgreSQL Machine
> ******************
> P-III 600Mhz (Dell Precision 220)
> 256 MB Ram (RD Ram)
> 40 GB Baracuda Ext2 File System.
> RedHat 7.2
> PostgreSQL 7.1.3-2

Besides the comments on the conf file already sent, 7.1.3 is many versions
behind the current version and definately has some deficiencies either
fully or partially corrected in later versions.  All in all, I'd suggest
going all the way to 7.4.1 since the hash aggregate stuff might help the
queries you're running.




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

* Re: Slow response of PostgreSQL
@ 2004-02-17 07:45  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Saleem Burhani Baloch <peseek@khi.wol.net.pk>
  3 siblings, 0 replies; 18+ messages in thread

From: Tom Lane @ 2004-02-17 07:45 UTC (permalink / raw)
  To: Saleem Burhani Baloch <peseek@khi.wol.net.pk>; +Cc: pgsql-performance

"Saleem Burhani Baloch" <peseek@khi.wol.net.pk> writes:
> PostgreSQL 7.1.3-2

Aside from the config issues Chris mentioned, I'd recommend trying
a somewhat less obsolete version of Postgres.  I believe the poor
performance with grouped aggregates should be fixed in 7.4 and later.

(Red Hat 7.2 is a bit long in the tooth as well.)

			regards, tom lane



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

* Re: Slow response of PostgreSQL
@ 2004-02-17 10:39  Saleem Burhani Baloch <peseek@khi.wol.net.pk>
  0 siblings, 2 replies; 18+ messages in thread

From: Saleem Burhani Baloch @ 2004-02-17 10:39 UTC (permalink / raw)
  To: Christopher Kings-Lynne <chriskl@familyhealth.com.au>; +Cc: pgsql-performance

I changed the conf as you wrote. But now the time is changed from 50 sec to 65 sec. :(
I have not more 256 MB ram now.
When I execute the query the 
Postmaster takes about 1.8 MB
Postgres session takes 18 MB ram only.
& psql takes 1.3 MB.

After the query finishes the 
Postgres session reducess memeory and just uses 10 MB ram.

I have a question why MS-SQL with 256 MB RAM gives result in 2 sec ?? If I have low memory Postgres should give result in 10 sec as compared to MS-SQL.

Looking forward for comments & suggestions

Saleem

> This is a shockingly bad postgresql.conf.  I'm not surprised you have 
> performance problems.  Change this:
> 
> effective_cache_size = 4000
> sort_mem = 4096
> shared_buffers = 1000
> wal_buffers = 8
> 
> Also, you need a LOT more RAM in your PostgreSQL machine, at least half 
> a gig for a basic database server.
> 
> 
> Chris






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

* Re: Slow response of PostgreSQL
@ 2004-02-17 11:00  Shridhar Daithankar <shridhar@frodo.hserus.net>
  parent: Saleem Burhani Baloch <peseek@khi.wol.net.pk>
  1 sibling, 0 replies; 18+ messages in thread

From: Shridhar Daithankar @ 2004-02-17 11:00 UTC (permalink / raw)
  To: pgsql-performance

Saleem Burhani Baloch wrote:
> I changed the conf as you wrote. But now the time is changed from 50 sec to 65 sec. :(
> I have not more 256 MB ram now.
> When I execute the query the 
> Postmaster takes about 1.8 MB
> Postgres session takes 18 MB ram only.
> & psql takes 1.3 MB.
> 
> After the query finishes the 
> Postgres session reducess memeory and just uses 10 MB ram.

Can you post explain analyze result for the query?

  Shridhar



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

* Re: Slow response of PostgreSQL
@ 2004-02-17 13:02  Bill Moran <wmoran@potentialtech.com>
  parent: Christopher Kings-Lynne <chriskl@familyhealth.com.au>
  0 siblings, 1 reply; 18+ messages in thread

From: Bill Moran @ 2004-02-17 13:02 UTC (permalink / raw)
  To: Christopher Kings-Lynne <chriskl@familyhealth.com.au>; +Cc: Saleem Burhani Baloch <peseek@khi.wol.net.pk>; pgsql-performance

Christopher Kings-Lynne wrote:
>> 1- How can I lock a single record so that other users can only read 
>> it. ??
> 
> You cannot do that in PostgreSQL.

How about SELECT ... FOR UPDATE?

-- 
Bill Moran
Potential Technologies
http://www.potentialtech.com




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

* Re: Slow response of PostgreSQL
@ 2004-02-17 15:02  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Saleem Burhani Baloch <peseek@khi.wol.net.pk>
  1 sibling, 0 replies; 18+ messages in thread

From: Tom Lane @ 2004-02-17 15:02 UTC (permalink / raw)
  To: Saleem Burhani Baloch <peseek@khi.wol.net.pk>; +Cc: Christopher Kings-Lynne <chriskl@familyhealth.com.au>; pgsql-performance

"Saleem Burhani Baloch" <peseek@khi.wol.net.pk> writes:
> I have a question why MS-SQL with 256 MB RAM gives result in 2 sec ?? If I have low memory Postgres should give result in 10 sec as compared to MS-SQL.

Are you still running 7.1?

			regards, tom lane



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

* Re: Slow response of PostgreSQL
@ 2004-02-17 17:15  scott.marlowe <scott.marlowe@ihs.com>
  parent: Saleem Burhani Baloch <peseek@khi.wol.net.pk>
  3 siblings, 0 replies; 18+ messages in thread

From: scott.marlowe @ 2004-02-17 17:15 UTC (permalink / raw)
  To: Saleem Burhani Baloch <peseek@khi.wol.net.pk>; +Cc: pgsql-performance


Easy two step procedure for speeding this up:

1:  Upgrade to 7.4.1
2:  Read this: 
http://www.varlena.com/varlena/GeneralBits/Tidbits/perf.html




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

* Re: Slow response of PostgreSQL
@ 2004-02-18 01:50  Christopher Kings-Lynne <chriskl@familyhealth.com.au>
  parent: Bill Moran <wmoran@potentialtech.com>
  0 siblings, 1 reply; 18+ messages in thread

From: Christopher Kings-Lynne @ 2004-02-18 01:50 UTC (permalink / raw)
  To: Bill Moran <wmoran@potentialtech.com>; +Cc: Saleem Burhani Baloch <peseek@khi.wol.net.pk>; pgsql-performance

>>> 1- How can I lock a single record so that other users can only read 
>>> it. ??
>>
>>
>> You cannot do that in PostgreSQL.
> 
> 
> How about SELECT ... FOR UPDATE?

No, because users cannot read the locked row in that case.

Chris




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

* Re: Slow response of PostgreSQL
@ 2004-02-18 01:50  scott.marlowe <scott.marlowe@ihs.com>
  parent: Christopher Kings-Lynne <chriskl@familyhealth.com.au>
  0 siblings, 2 replies; 18+ messages in thread

From: scott.marlowe @ 2004-02-18 01:50 UTC (permalink / raw)
  To: Christopher Kings-Lynne <chriskl@familyhealth.com.au>; +Cc: Bill Moran <wmoran@potentialtech.com>; Saleem Burhani Baloch <peseek@khi.wol.net.pk>; pgsql-performance

On Wed, 18 Feb 2004, Christopher Kings-Lynne wrote:

> >>> 1- How can I lock a single record so that other users can only read 
> >>> it. ??
> >>
> >>
> >> You cannot do that in PostgreSQL.
> > 
> > 
> > How about SELECT ... FOR UPDATE?
> 
> No, because users cannot read the locked row in that case.

I just tested it (within transactions) and it appeared that I could still 
view the rows selected for update.




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

* Re: Slow response of PostgreSQL
@ 2004-02-18 02:21  Bill Moran <wmoran@potentialtech.com>
  parent: scott.marlowe <scott.marlowe@ihs.com>
  1 sibling, 0 replies; 18+ messages in thread

From: Bill Moran @ 2004-02-18 02:21 UTC (permalink / raw)
  To: scott.marlowe <scott.marlowe@ihs.com>; +Cc: Christopher Kings-Lynne <chriskl@familyhealth.com.au>; Saleem Burhani Baloch <peseek@khi.wol.net.pk>; pgsql-performance

scott.marlowe wrote:
> On Wed, 18 Feb 2004, Christopher Kings-Lynne wrote:
> 
>>>>>1- How can I lock a single record so that other users can only read 
>>>>>it. ??
>>>>
>>>>
>>>>You cannot do that in PostgreSQL.
>>>
>>>
>>>How about SELECT ... FOR UPDATE?
>>
>>No, because users cannot read the locked row in that case.
> 
> I just tested it (within transactions) and it appeared that I could still 
> view the rows selected for update.

Thank you.  I was just about to test it myself.

The user's guide, section 9.3.2 states that this is the case: i.e. select
for update will prevent concurrent updating of the row, while allowing
queries utilizing that row to succeed.

-- 
Bill Moran
Potential Technologies
http://www.potentialtech.com




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

* Re: Slow response of PostgreSQL
@ 2004-02-18 04:52  Christopher Kings-Lynne <chriskl@familyhealth.com.au>
  parent: scott.marlowe <scott.marlowe@ihs.com>
  1 sibling, 0 replies; 18+ messages in thread

From: Christopher Kings-Lynne @ 2004-02-18 04:52 UTC (permalink / raw)
  To: scott.marlowe <scott.marlowe@ihs.com>; +Cc: Bill Moran <wmoran@potentialtech.com>; Saleem Burhani Baloch <peseek@khi.wol.net.pk>; pgsql-performance

>>>How about SELECT ... FOR UPDATE?
>>
>>No, because users cannot read the locked row in that case.
> 
> 
> I just tested it (within transactions) and it appeared that I could still 
> view the rows selected for update.

Ah, true.  My mistake.  OK, well you can do it in postgres then...

Chris




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

* Re: Slow response of PostgreSQL
@ 2004-02-19 09:01  Saleem Burhani Baloch <peseek@khi.wol.net.pk>
  0 siblings, 3 replies; 18+ messages in thread

From: Saleem Burhani Baloch @ 2004-02-19 09:01 UTC (permalink / raw)
  To: pgsql-performance

Hi,

Thanks every one for helping me. I have upgraded to 7.4.1 on redhat 8 ( rh 9 require a lot of lib's) and set the configuration sent by Chris. Now the query results in 6.3 sec waooo. I m thinking that why the 7.1 process aggregate slowly. Anyway.

I still have to go for 2 sec result and now I m thinking to go for Free BSD 5.2.

The record locking by "select ..... update for" is working fine, but a situation arrises when the second user goes for locking the record,  the task will wait untill the first user ends up his work. IS their is a way that we can know or get a message that the row/record is already locked by some one else.

Thanks for help.

Saleem




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

* Re: Slow response of PostgreSQL
@ 2004-02-19 09:26  Shridhar Daithankar <shridhar@frodo.hserus.net>
  parent: Saleem Burhani Baloch <peseek@khi.wol.net.pk>
  2 siblings, 1 reply; 18+ messages in thread

From: Shridhar Daithankar @ 2004-02-19 09:26 UTC (permalink / raw)
  To: Saleem Burhani Baloch <peseek@khi.wol.net.pk>; +Cc: pgsql-performance

On Thursday 19 February 2004 14:31, Saleem Burhani Baloch wrote:
> Hi,
>
> Thanks every one for helping me. I have upgraded to 7.4.1 on redhat 8 ( rh
> 9 require a lot of lib's) and set the configuration sent by Chris. Now the
> query results in 6.3 sec waooo. I m thinking that why the 7.1 process
> aggregate slowly. Anyway.
>
> I still have to go for 2 sec result and now I m thinking to go for Free BSD
> 5.2.

Before that you can try something with kernel 2.6.3. I think it could make the 
difference you are looking at.

> The record locking by "select ..... update for" is working fine, but a
> situation arrises when the second user goes for locking the record,  the
> task will wait untill the first user ends up his work. IS their is a way
> that we can know or get a message that the row/record is already locked by
> some one else.

Right now, it is hotly debated on HACKERS about adding a NOWAIT clause to 
SELECT FOR UPDATE. If you think your application deployment is away for 
months and can try CVS head, you can expect some action on it in coming few 
days.

As a side bonus you would be benefitted by performance and scalability 
additions that won't make there way in 7.4 stream.

HTH

 Shridhar



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

* Re: Slow response of PostgreSQL
@ 2004-02-19 13:52  Christopher Kings-Lynne <chriskl@familyhealth.com.au>
  parent: Saleem Burhani Baloch <peseek@khi.wol.net.pk>
  2 siblings, 0 replies; 18+ messages in thread

From: Christopher Kings-Lynne @ 2004-02-19 13:52 UTC (permalink / raw)
  To: Saleem Burhani Baloch <peseek@khi.wol.net.pk>; +Cc: pgsql-performance

> Thanks every one for helping me. I have upgraded to 7.4.1 on redhat 8 (
> rh 9 require a lot of lib's) and set the configuration sent by Chris.
> Now the query results in 6.3 sec waooo. I m thinking that why the 7.1
> process aggregate slowly. Anyway.

I'm glad we could help you Saleem :)

We knew PostgreSQL wasn't that slow :P

Chris





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

* Re: Slow response of PostgreSQL
@ 2004-02-19 20:46  Hannu Krosing <hannu@tm.ee>
  parent: Saleem Burhani Baloch <peseek@khi.wol.net.pk>
  2 siblings, 0 replies; 18+ messages in thread

From: Hannu Krosing @ 2004-02-19 20:46 UTC (permalink / raw)
  To: Saleem Burhani Baloch <peseek@khi.wol.net.pk>; +Cc: pgsql-performance

Saleem Burhani Baloch kirjutas N, 19.02.2004 kell 11:01:
> Hi,
> 
> Thanks every one for helping me. I have upgraded to 7.4.1 on 
> redhat 8 ( rh 9 require a lot of lib's) and set the configuration 
> sent by Chris. Now the query results in 6.3 sec waooo. I m thinking 
> that why the 7.1 process aggregate slowly. Anyway.
> 
> I still have to go for 2 sec result 

What is the plan now ?

----------------
Hannu




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

* Re: Slow response of PostgreSQL
@ 2004-02-20 14:34  Neil Conway <neilc@samurai.com>
  parent: Shridhar Daithankar <shridhar@frodo.hserus.net>
  0 siblings, 0 replies; 18+ messages in thread

From: Neil Conway @ 2004-02-20 14:34 UTC (permalink / raw)
  To: shridhar@frodo.hserus.net; +Cc: Saleem Burhani Baloch <peseek@khi.wol.net.pk>; pgsql-performance

Shridhar Daithankar <shridhar@frodo.hserus.net> writes:
> Right now, it is hotly debated on HACKERS about adding a NOWAIT
> clause to SELECT FOR UPDATE. If you think your application
> deployment is away for months and can try CVS head, you can expect
> some action on it in coming few days.

You can also try using the statement_timeout configuration variable
that is already included with 7.4. It's not exactly "don't wait for
locks", but should approximate that behavior well enough.

http://www.postgresql.org/docs/7.4/static/runtime-config.html#RUNTIME-CONFIG-CLIENT

-Neil




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


end of thread, other threads:[~2004-02-20 14:34 UTC | newest]

Thread overview: 18+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-02-17 06:24 Slow response of PostgreSQL Saleem Burhani Baloch <peseek@khi.wol.net.pk>
2004-02-17 06:59 ` Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2004-02-17 13:02   ` Bill Moran <wmoran@potentialtech.com>
2004-02-18 01:50     ` Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2004-02-18 01:50       ` scott.marlowe <scott.marlowe@ihs.com>
2004-02-18 02:21         ` Bill Moran <wmoran@potentialtech.com>
2004-02-18 04:52         ` Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2004-02-17 07:42 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
2004-02-17 07:45 ` Tom Lane <tgl@sss.pgh.pa.us>
2004-02-17 17:15 ` scott.marlowe <scott.marlowe@ihs.com>
2004-02-17 10:39 Re: Slow response of PostgreSQL Saleem Burhani Baloch <peseek@khi.wol.net.pk>
2004-02-17 11:00 ` Shridhar Daithankar <shridhar@frodo.hserus.net>
2004-02-17 15:02 ` Tom Lane <tgl@sss.pgh.pa.us>
2004-02-19 09:01 Re: Slow response of PostgreSQL Saleem Burhani Baloch <peseek@khi.wol.net.pk>
2004-02-19 09:26 ` Shridhar Daithankar <shridhar@frodo.hserus.net>
2004-02-20 14:34   ` Neil Conway <neilc@samurai.com>
2004-02-19 13:52 ` Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2004-02-19 20:46 ` Hannu Krosing <hannu@tm.ee>

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