Skip site navigation (1) Skip section navigation (2)

Re: Performance question (PostgreSQL 7.1.3)

From: "Josh Berkus" <josh(at)agliodbs(dot)com>
To: Medve Gábor <GMedve(at)PGSM(dot)HU>, "'pgsql-novice(at)postgresql(dot)org'" <pgsql-novice(at)postgresql(dot)org>
Subject: Re: Performance question (PostgreSQL 7.1.3)
Date: 2001-10-23 22:51:20
Message-ID: web-490909@davinci.ethosmedia.com (view raw or flat)
Thread:
Lists: pgsql-novice
Gabor,

> I have no experiences with big databases. Now I have a database
> that's size
> is appr. 7,6GB (HW/OS: Intel Pentium III 600MHz, 512MB RAM, GNU/Linux
> 2.2.19
> kernel). There is a big table in it with 25318267 tuples today. If I
> make a
> simple query (SELECT x,y,z FROM table WHERE x = something) then it
> takes
> 2m42s to get the results. Is it a good value?
>  I've read the documentation and I've checked the kernel parameters
> (sem.h
> and shmparam.h) and the parameter values in postgresql.conf and I
> have no
> idea what could I do to get the results in shorter time period.
> Thanks for any kindness in advance.

Hmmm.. for 25 million records on a single-processor machine, 2 minutes
isn't unreasonable, especially if you have IDE rather than SCSI drives.
Drive access times are your big bottleneck with really large databases.

Things to check:
1. Indexes ... use EXPLAIN to find out how Postgres is finding the data.
2. VACUUM and VACUUM ANALYZE frequency
3. Postgresql.conf parameters: sort_mem, buffers, wal_files (make tem
high!)
4. Use multiple drives or RAID ... having the log files on a seperate
drive from the database makes a huge difference in really large queries,
although mostly for UPDATE and INSERT queries.  Also, putting the OS on
a seperate drive doesn't hurt.
5. Postgres verison ... an upgrade always improves the query optimizer.

Also, how large is each tuple?  It makes a difference in retrieval.

Finally, I suggest that you read through the PGSQL-SQL list archives.
We discuss performance issues all the time on that list.

-Josh Berkus





______AGLIO DATABASE SOLUTIONS___________________________
                                       Josh Berkus
  Complete information technology      josh(at)agliodbs(dot)com
   and data management solutions       (415) 565-7293
  for law firms, small businesses        fax 621-2533
    and non-profit organizations.      San Francisco

In response to

pgsql-novice by date

Next:From: Jochem van DietenDate: 2001-10-23 23:08:57
Subject: Re: Performance question (PostgreSQL 7.1.3)
Previous:From: Mark McArtheyDate: 2001-10-23 19:25:31
Subject: column length?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group