Обсуждение: Performance Issues

On Mon, Sep 08, 2003 at 11:43:42AM +0530, Ravi T Ramachandra wrote:
> I recently setup postgres on a Linux box with 4GB Ram and 2.5 GHz
> processor.

Big box.

> We have created a database with 1.5 million rows in a
> table.

Small database.

> When we try to select rows from the table, it is taking enormous
> time with the default configuration.

Problem is very probably default configuration (you didn't
say which postgresql version you had, it would have been
interesting, but no matter, or specify that your performance
results were consistent during several runs without reboot, but
I suppose so).

Default configuration is for a small box, or a small database,
or "extremely conservative", whatever. It's "the size that
works for everybody", for some modest value of "work". That's
very different from "one size fits all", and you'll agree that
postgresql can't expect to have 4 GB of RAM to play with on most
machines.

You want to tune performance :-)

To begin with, you want to bump shared_buffers from 64 or
512 to 4096 at the very least, probably all the way to say
32768 or maybe 65536 or even more, I don't know if there's
an upper limit, soft or hard (I've never had more than 1GB
RAM on a machine so I've never wondered <jealous> :-) ) Note
that "cat /proc/sys/kernel/shmmax" should be more or equal to
shared_buffers*8192, otherwise maybe postmaster won't start,
a quick check says that my untuned Linux is at 4096*8192. To
change that, umm, google, yes:

http://www.lyris.com/lm_help/7.5/tuning_postgresql.html

which has a lot of other tips.

Continuing in that vein yields

http://www.varlena.com/GeneralBits/Tidbits/perf.html
http://developer.postgresql.org/docs/postgres/kernel-resources.html

And more in-depth:

http://candle.pha.pa.us/main/writings/pgsql/performance.pdf
http://www.argudo.org/postgresql/soft-tuning.html

If you still have performance problems after a dose of that
medicine I'll be very surprised :-)

Nathan

On Mon, Sep 08, 2003 at 11:43:42 +0530,
  Ravi T Ramachandra <ravi.ramachandra@wipro.com> wrote:
>
> SELECT * FROM TABLE A WHERE COL1 = 1 AND COL2 = 'ABC'.
>
> We have created index definition as follows
>
> CREATE INDEX IDX ON A(COL1, COL2);
>
> Explain on the above statement shows it is sequential scan.   The
> process size for the postmaster shows as 4MB (is this normal ?)

While Nathan's advice may be useful for you, it probably isn't causing the
problem you are seeing. Your problem is most likely that COL isn't
an INTEGER (aka INT or INT4). Try using single quotes around '1'.

Thanks Bruno and Nathan for your responses.  The integer column was a
SMALLINT and for some reason, postgres doesn't do index lookup for
smallint.   When we changed the column from smallint to int, it is
making lots of differences.

Regards,
Ravi


-----Original Message-----
From: Bruno Wolff III [mailto:bruno@wolff.to]
Sent: Monday, September 08, 2003 6:43 PM
To: Ravi T Ramachandra
Cc: pgsql-admin@postgresql.org; Sandeep Bhasin
Subject: Re: [ADMIN] Performance Issues


On Mon, Sep 08, 2003 at 11:43:42 +0530,
  Ravi T Ramachandra <ravi.ramachandra@wipro.com> wrote:
>
> SELECT * FROM TABLE A WHERE COL1 = 1 AND COL2 = 'ABC'.
>
> We have created index definition as follows
>
> CREATE INDEX IDX ON A(COL1, COL2);
>
> Explain on the above statement shows it is sequential scan.   The
> process size for the postmaster shows as 4MB (is this normal ?)

While Nathan's advice may be useful for you, it probably isn't causing
the problem you are seeing. Your problem is most likely that COL isn't
an INTEGER (aka INT or INT4). Try using single quotes around '1'.

**************************Disclaimer************************************

Information contained in this E-MAIL being proprietary to Wipro Limited is
'privileged' and 'confidential' and intended for use only by the individual
 or entity to which it is addressed. You are notified that any use, copying
or dissemination of the information contained in the E-MAIL in any manner
whatsoever is strictly prohibited.

***************************************************************************

A long time ago, in a galaxy far, far away, ravi.ramachandra@wipro.com ("Ravi T Ramachandra") wrote:
> I recently setup postgres on a Linux box with 4GB Ram and 2.5 GHz
> processor.   We have created a database with 1.5 million rows in a
> table.  When we try to select rows from the table, it is taking
> enormous time with the default configuration.   It takes 2 to 3
> seconds to select 1 row that has been selected with indexed columns.
>
> SELECT * FROM TABLE A WHERE COL1 = 1 AND COL2 = 'ABC'.  
>
> We have created index definition as follows
>
> CREATE INDEX IDX ON A(COL1, COL2);
>
> Explain on the above statement shows it is sequential scan.   The
> process size for the postmaster shows as 4MB (is this normal ?)

The size seems normal for a database with default parameters.  You
might want to do some tuning of parameters in postgresql.conf to
indicate the realistic size of your hardware, instead of its *very*
conservative assumptions.

And as for the SEQ SCAN, there are two most likely reasons:

1.  If the query planner thinks that "most" of the rows will be
returned by the query, then it would indeed be preferable to do a seq
scan.

Somehow, I doubt that's the case here, but this sort of thing *does*
happen, and surprises people...

2.  Did you ever run ANALYZE on the table to give the query planner
some statistics on what actually is in the table?

If there are no useful stats (in pg_statistic), then the query planner
will do a seq scan because it has no reason to prefer anything else.

Run VACUUM ANALYZE VERBOSE; on the database, and see if that changes
things.  I would surely expect it to...
--
select 'cbbrowne' || '@' || 'ntlug.org';
http://cbbrowne.com/info/postgresql.html
"But   life  wasn't yes-no,   on-off.   Life was shades   of gray, and
rainbows not in the order of the spectrum."
-- L. E. Modesitt, Jr., _Adiamante_