Thread: 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