Обсуждение: large database on postgres

Hello,
I have one application that I'm trying to move the db to pgsql. The db
contains around 15 million rows of data. I'm running into 10 second delays
when doing a simple select on an index column.

My test box is Linux 1ghz Celeron  25MB Ram, 7200rpm IDE drive. I know I
need SCSI but is there any tuning I can do to speed this up?

-Tony

On Thu, 13 Jun 2002, Tony Carter wrote:

> Hello,
> I have one application that I'm trying to move the db to pgsql. The db
> contains around 15 million rows of data. I'm running into 10 second delays
> when doing a simple select on an index column.
>
> My test box is Linux 1ghz Celeron  25MB Ram, 7200rpm IDE drive. I know I
> need SCSI but is there any tuning I can do to speed this up?

Well, let's do the standard info requests :)
Have you used vacuum analyze?  What's the schema, query, and explain (or
explain analyze if you're using 7.2) output for the query.

Tony Carter wrote:
> Hello,
> I have one application that I'm trying to move the db to pgsql. The db
> contains around 15 million rows of data. I'm running into 10 second delays
> when doing a simple select on an index column.
>
> My test box is Linux 1ghz Celeron  25MB Ram, 7200rpm IDE drive. I know I
                                      ^^
Is this a typo?  If not, I would suggest that you're going to need more than
25M for a db server with 15mil rows if you want hot performance!

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

On Thu, 13 Jun 2002, Tony Carter wrote:

> My test box is Linux 1ghz Celeron  25MB Ram, 7200rpm IDE drive. I know I
> need SCSI but is there any tuning I can do to speed this up?

Get at least another drive and put the logs there.
This will only help you with inserts.

To speedup selects you need faster drives, RAID or more memory.

Don't forget to do a vacuum analyze daily and also a vacuum full once in a
while (depending on how many records you delete).

Stephan,
Here is more info. I don't have access to the server right now, I'll do the
explain analyze later.


table:stock_history

name    type
------------------------------
id    int         not null pk
name    varchar 250  not null  index

sql statement:

    select id from stock_history where name = "quest";
    takes about 2 minutes.


TIA,
Tony





-----Original Message-----
From: Stephan Szabo [mailto:sszabo@megazone23.bigpanda.com]
Sent: Friday, June 14, 2002 5:17 PM
To: Tony Carter
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] large database on postgres


On Thu, 13 Jun 2002, Tony Carter wrote:

> Hello,
> I have one application that I'm trying to move the db to pgsql. The db
> contains around 15 million rows of data. I'm running into 10 second delays
> when doing a simple select on an index column.
>
> My test box is Linux 1ghz Celeron  25MB Ram, 7200rpm IDE drive. I know I
> need SCSI but is there any tuning I can do to speed this up?

Well, let's do the standard info requests :)
Have you used vacuum analyze?  What's the schema, query, and explain (or
explain analyze if you're using 7.2) output for the query.

Yup, its a typo! It should read 256mb.
-Thanks


-----Original Message-----
From: Bill Moran [mailto:wmoran@potentialtech.com]
Sent: Friday, June 14, 2002 5:36 PM
To: Tony Carter
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] large database on postgres


Tony Carter wrote:
> Hello,
> I have one application that I'm trying to move the db to pgsql. The db
> contains around 15 million rows of data. I'm running into 10 second delays
> when doing a simple select on an index column.
>
> My test box is Linux 1ghz Celeron  25MB Ram, 7200rpm IDE drive. I know I
                                      ^^
Is this a typo?  If not, I would suggest that you're going to need more than
25M for a db server with 15mil rows if you want hot performance!

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

On Thu, 13 Jun 2002, Tony Carter wrote:

> My test box is Linux 1ghz Celeron  25MB Ram, 7200rpm IDE drive. I know I
> need SCSI but is there any tuning I can do to speed this up?

Actually, you may not need SCSI. So long as you make sure each IDE
drive has its own controller, IDE should be pretty near as good,
and a lot cheaper.  (Or, for the same price, better, since you can
get more drives for X dollars than you can with SCSI.)

I'm about to build a 10-disk system using a 3Ware Escalade 7850
IDE RAID controller. (System/logs on a mirrored pair of IDE disks
on the on-board controllers and data on an 8-disk RAID on the
Escalade.) I'll let everyone know how it goes.

cjs
--
Curt Sampson  <cjs@cynic.net>   +81 90 7737 2974   http://www.netbsd.org
    Don't you know, in this new Dark Age, we're all light.  --XTC