pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
2 million+ entries
12+ messages / 7 participants
[nested] [flat]

* 2 million+ entries
@ 1999-08-20 23:59  Matthew Hixson <hixson@frozenwave.com>
  0 siblings, 2 replies; 12+ messages in thread

From: Matthew Hixson @ 1999-08-20 23:59 UTC (permalink / raw)
  To: pgsql-sql

Hi,
  I'm going to be working on a project soon that involves an SQL database.  If
I have my way it will be using PostgreSQL 6.5 on a dual PII-350 with 256 MB of
RAM and two 18GB UW2 SCSI drives (Seagate Barracudas).  However there is a
possibility that it could be running on an NT server with even beefier hardware.
  What I'm wondering is; is there any problem with Postgres handling a table
with 2 million entries in it?  Is there an NT solution that would work better? 
Personally I can't stand NT, but its not really my decision to make.  This is
going to be mission critical so must remain rock solid 24x7.  (On second
thought that kindof rules out NT doesn't it...? :)
  Anyway here is the table structure that my project manager came up with.  I
mentioned that it might be better to put the "Subscriber Table", "Usage Table",
"Personal Information Table", "Credit Card Information Table", and "Interests
Table" all into one larger table.  So now we're wondering:  Does having
separate tables like this make sense for speeding up lookups or would it be
faster with my one large table idea?  In either case will PostgreSQL be able to
handle this much data gracefully?
  Thanks in advance for any insight into this.
   -M@



Subscriber Table
	UserName (unique)
	Password
	Active

Usage Table
	UserName (unique)
	DiskSpaceAuthorizedAmount
	DiskSpaceAuthorizedDate
	LastLogin
	FailedAttempts
	DefaultDirectoryLocal
	DefaultDirectoryzDisk
	AccountCreateDate
	OverDrawn
	WarningFlag
	NotifyFlag

Personal Information Table
	UserName (unique)
	FirstName
	LastName
	EmailAddress
	HomeAddress1
	HomeAddress2
	HomeCity
	HomeState
	HomeCountry
	HomePostalCode
	HomePhone1
	HomePhone2
	HomeFax
	WorkAddress1
	WorkAddress2
	WorkCity
	WorkState
	WorkCountry
	WorkPostalCode
	WorkPhone1
	WorkPhone2
	WorkFax
	Sex
	BirthYear
	Interests

Credit Card Info Table
	UserName (unique)
	CreditCard
	CardNumber
	IssuingBank
	NameOnCard
	ExpirationDate
	BillingName
	BillingAddress1
	BillingAddress2
	BillingCity
	BillingState
	BillingPostalCode

Disk Space Pricing Table
	InitialFreeSize
	NextBlockSize
	NextBlockPrice
	QuantityDiscount

Interests Table
	Sports - Active”
	Sports - Leisure”
	Theater”
	Collecting”
	Fashion”
--
Matthew Hixson - CIO 
FroZenWave Communications 
http://www.frozenwave.com



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

* Re: [SQL] 2 million+ entries
@ 1999-08-24 06:30  Michael Richards <miker@scifair.acadiau.ca>
  parent: Matthew Hixson <hixson@frozenwave.com>
  1 sibling, 2 replies; 12+ messages in thread

From: Michael Richards @ 1999-08-24 06:30 UTC (permalink / raw)
  To: Matthew Hixson <hixson@frozenwave.com>; +Cc: pgsql-sql

On Fri, 20 Aug 1999, Matthew Hixson wrote:

>   I'm going to be working on a project soon that involves an SQL database.  If
> I have my way it will be using PostgreSQL 6.5 on a dual PII-350 with 256 MB of
> RAM and two 18GB UW2 SCSI drives (Seagate Barracudas).  However there is a
> possibility that it could be running on an NT server with even beefier hardware.
If you're planning on doing some heavy database processing and have the
funds, I'd suggest using smaller drives with a RAID controller. Large
drives such as the 18Gb store to much data per platter and as a result end
up a little IO bound when you're doing heavy processing.

I'd suggest using a *BSD, ie FreeBSD, as it's filsystem performance is
much better than something like ext2. As for the large table size, I ran a
postgres database with 15,000,000 tuples once. It was 6.3, and the vacuum
process was particularily nasty, but I don't think you will have any
problems with 6.5.

We're currently testing a system with about 1.2 million records with 6.5
and it outperforms SQL server 6 by quite a lot. It's running on a dual P3
with a 4 9.1 gig Cheetah array on a DPT controller running RAID0. I'm
pretty happy with it... 

If you want to look at another aspect, even if Postgres didn't perform
quite as well as SQL server, consider about $1k for an NT server license
and $2k for a 10 client SQL server license.... put that moolah into
hardware and you're ahead again...

-Michael




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

* Re: [SQL] 2 million+ entries
@ 1999-08-24 07:28  Matthew Hixson <hixson@frozenwave.com>
  parent: Michael Richards <miker@scifair.acadiau.ca>
  1 sibling, 0 replies; 12+ messages in thread

From: Matthew Hixson @ 1999-08-24 07:28 UTC (permalink / raw)
  To: pgsql-sql

On Mon, 23 Aug 1999, Michael Richards wrote:

Thanks for the reply, Michael.  I'll forward it on to my project manager.

>I'd suggest using a *BSD, ie FreeBSD, as it's filsystem performance is
>much better than something like ext2. 

Speaking of filesystems does anyone know when SGI's XFS filesystem is supposed
to be available on Linux?  I hear they recently GPL'd it.
  Thanks,
   -M@

--
Matthew Hixson - CIO 
FroZenWave Communications 
http://www.frozenwave.com



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

* Re: [SQL] 2 million+ entries
@ 1999-08-24 08:06  Moray McConnachie <Moray.McConnachie@computing-services.oxford.ac.uk>
  parent: Michael Richards <miker@scifair.acadiau.ca>
  1 sibling, 1 reply; 12+ messages in thread

From: Moray McConnachie @ 1999-08-24 08:06 UTC (permalink / raw)
  To: pgsql-sql



> On Mon, 23 Aug 1999, Michael Richards wrote:
> 

> 
> >I'd suggest using a *BSD, ie FreeBSD, as it's filsystem performance is
> >much better than something like ext2. 
> 

People that ought to know tell me:

"No. I don't think even the *BSD advocates claim that *BSD 
beats Linux on large boxes (i.e. SMP and plenty of memory). 
The only places I can think of off-hand where *BSD advocates
can argue even the possibility for better performance 
against Linux are 
(1) when heavy swapping is going on (which means the box was
badly balanced when bought especially if it's a compute 
server) and
(2) certain unusual network uses (due to design differences and
historical curiosities). There are some functionality differences
between *BSD and Linux but that's a different issue."

However, we were talking about 2 GB plus of memory here...

----------------------
Moray.McConnachie@computing-services.oxford.ac.uk




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

* Re: [SQL] 2 million+ entries
@ 1999-08-24 08:24  Rudy Gireyev <rgireyev@cnmnetwork.com>
  parent: Matthew Hixson <hixson@frozenwave.com>
  1 sibling, 0 replies; 12+ messages in thread

From: Rudy Gireyev @ 1999-08-24 08:24 UTC (permalink / raw)
  To: Michael Richards <miker@scifair.acadiau.ca>; pgsql-sql

While we are on the hardware subject, why not replace the PIII
with Xeons? Either PII or PIII.

Rudy

On 24 Aug 99, at 3:30, Michael Richards wrote:

> On Fri, 20 Aug 1999, Matthew Hixson wrote:
> 
> >   I'm going to be working on a project soon that involves an SQL
> >   database.  If
> > I have my way it will be using PostgreSQL 6.5 on a dual PII-350 with 256
> > MB of RAM and two 18GB UW2 SCSI drives (Seagate Barracudas).  However
> > there is a possibility that it could be running on an NT server with
> > even beefier hardware.
> If you're planning on doing some heavy database processing and have the
> funds, I'd suggest using smaller drives with a RAID controller. Large
> drives such as the 18Gb store to much data per platter and as a result end
> up a little IO bound when you're doing heavy processing.
> 
> I'd suggest using a *BSD, ie FreeBSD, as it's filsystem performance is
> much better than something like ext2. As for the large table size, I ran a
> postgres database with 15,000,000 tuples once. It was 6.3, and the vacuum
> process was particularily nasty, but I don't think you will have any
> problems with 6.5.
> 
> We're currently testing a system with about 1.2 million records with 6.5
> and it outperforms SQL server 6 by quite a lot. It's running on a dual P3
> with a 4 9.1 gig Cheetah array on a DPT controller running RAID0. I'm
> pretty happy with it... 
> 
> If you want to look at another aspect, even if Postgres didn't perform
> quite as well as SQL server, consider about $1k for an NT server license
> and $2k for a 10 client SQL server license.... put that moolah into
> hardware and you're ahead again...
> 
> -Michael
> 
> 
> ************
> 
> 





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

* Re: [SQL] 2 million+ entries
@ 1999-08-25 01:25  Michael Richards <miker@scifair.acadiau.ca>
  parent: Moray McConnachie <Moray.McConnachie@computing-services.oxford.ac.uk>
  0 siblings, 0 replies; 12+ messages in thread

From: Michael Richards @ 1999-08-25 01:25 UTC (permalink / raw)
  To: Moray McConnachie <Moray.McConnachie@computing-services.oxford.ac.uk>; +Cc: pgsql-sql

On Tue, 24 Aug 1999, Moray McConnachie wrote:

> > >I'd suggest using a *BSD, ie FreeBSD, as it's filsystem performance is
> > >much better than something like ext2. 
> 
> People that ought to know tell me:
> 
> "No. I don't think even the *BSD advocates claim that *BSD 
> beats Linux on large boxes (i.e. SMP and plenty of memory). 
> The only places I can think of off-hand where *BSD advocates
> can argue even the possibility for better performance 
> against Linux are 
> (1) when heavy swapping is going on (which means the box was
> badly balanced when bought especially if it's a compute 
> server) and
If your system has resorted to swapping, it's obviously in big trouble.
Linux's comparison problems start long before a system starts swapping.
Given the swap fragmentation problems, there is no "possibility" for 
better performance... Even under paging conditions, linux falls far
short. You can't do a linear search through the entire map of the swap
every page you write and expect to maintain any sort of comparison against
some of the more mature unixes. Quite simply, it's flawed and needs to be
fixed; someone will do it sooner or later, but I'm building solutions now.

I'm not suggesting FreeBSD because I'm blindly advocating it's use... ie
"Linux RULEZ"
Which other unixes have you tried?
"Um... Windows???"
Rather, I'm suggesting it because we've tried it and found it performed
better for us than linux. This was not a blind decision; rather an
informed one following many tests and benchmarks.

> (2) certain unusual network uses (due to design differences and
> historical curiosities). There are some functionality differences
> between *BSD and Linux but that's a different issue."
This is not really an issue with filesystem performance. With a table of
2 million tuples, that is what eats the majority of your query processing
time.

I think it's well known that ffs is much faster than ext2. Given
softupdates, it's even better. Softupdates also avoids the filesystem
corruption problems with ext2 because it writes dependant entities in the 
proper order, not just any old order they appear in the filesystem cache.

If ext2 did perform just as well as ffs, then why is it being re-designed
for the 3rd time? Maybe ext3 will be better, but for now, I stick with
proven solutions. If it's not broken, don't fix it.

-Michael




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

* Re: [SQL] 2 million+ entries
@ 1999-08-25 16:59  G. Anthony Reina <reina@nsi.edu>
  0 siblings, 3 replies; 12+ messages in thread

From: G. Anthony Reina @ 1999-08-25 16:59 UTC (permalink / raw)
  To: Michael Richards <miker@scifair.acadiau.ca>; pgsql-sql

Michael Richards wrote:

> We're currently testing a system with about 1.2 million records with 6.5
> and it outperforms SQL server 6 by quite a lot. It's running on a dual P3
> with a 4 9.1 gig Cheetah array on a DPT controller running RAID0. I'm
> pretty happy with it...
>
>

Great balls of fire! A dual P3 with a four 9.1 Gig RAID0 Cheetah array?
That's fantastic! How much did that set you back? Are there any special
quirks about using Postgres on a multiple processor, multiple disk
system?

-Tony





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

* Re: [SQL] 2 million+ entries
@ 1999-08-25 17:19  Bruce Momjian <maillist@candle.pha.pa.us>
  parent: G. Anthony Reina <reina@nsi.edu>
  2 siblings, 0 replies; 12+ messages in thread

From: Bruce Momjian @ 1999-08-25 17:19 UTC (permalink / raw)
  To: G. Anthony Reina <reina@nsi.edu>; +Cc: Michael Richards <miker@scifair.acadiau.ca>; pgsql-sql

> Michael Richards wrote:
> 
> > We're currently testing a system with about 1.2 million records with 6.5
> > and it outperforms SQL server 6 by quite a lot. It's running on a dual P3
> > with a 4 9.1 gig Cheetah array on a DPT controller running RAID0. I'm
> > pretty happy with it...
> >
> >
> 
> Great balls of fire! A dual P3 with a four 9.1 Gig RAID0 Cheetah array?
> That's fantastic! How much did that set you back? Are there any special
> quirks about using Postgres on a multiple processor, multiple disk
> system?

No quirks.  Our multi-Unix-process model for each backend makes
multi-cpu's work great.  In fact, multi-threading, which mysql uses to
put multiple backends in a single unix process, can be a pain to get
working on a multi-cpu system unless the OS can handle threads across
multiple cpu processes.

-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  maillist@candle.pha.pa.us            |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026



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

* Re: [SQL] 2 million+ entries
@ 1999-08-26 05:48  Michael Richards <miker@scifair.acadiau.ca>
  parent: G. Anthony Reina <reina@nsi.edu>
  2 siblings, 0 replies; 12+ messages in thread

From: Michael Richards @ 1999-08-26 05:48 UTC (permalink / raw)
  To: G. Anthony Reina <reina@nsi.edu>; +Cc: pgsql-sql

On Wed, 25 Aug 1999, G. Anthony Reina wrote:

> > We're currently testing a system with about 1.2 million records with 6.5
> > and it outperforms SQL server 6 by quite a lot. It's running on a dual P3
> > with a 4 9.1 gig Cheetah array on a DPT controller running RAID0. I'm
> > pretty happy with it...
> 
> Great balls of fire! A dual P3 with a four 9.1 Gig RAID0 Cheetah array?
> That's fantastic! How much did that set you back? Are there any special
> quirks about using Postgres on a multiple processor, multiple disk
> system?
As I recall it was about $8k CAD. The system purrs. I'm especially happy
with the DPT controller. This would be a truly worthy machine for a large
news feed.

-Michael




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

* Re: [SQL] 2 million+ entries
@ 1999-08-26 06:28  Michael Richards <miker@scifair.acadiau.ca>
  parent: G. Anthony Reina <reina@nsi.edu>
  2 siblings, 0 replies; 12+ messages in thread

From: Michael Richards @ 1999-08-26 06:28 UTC (permalink / raw)
  To: G. Anthony Reina <reina@nsi.edu>; +Cc: pgsql-sql

On Wed, 25 Aug 1999, G. Anthony Reina wrote:

> That's fantastic! How much did that set you back? Are there any special
> quirks about using Postgres on a multiple processor, multiple disk
> system?

Oops. To answer your second question. No. None whatsoever. The multi-disk
array is handled by the RAID controller. To the OS it appears to be a
gigantic disk. The RAID controller also has hardware cache so it can do
nifty things like queue a large number of simultaneous requests and
elevator sort them for seek efficiency. The machine also has a large
amount of RAM.

If you really need high performance, I've done nifty things like putting
indexes on different disks. Postgres does not seem to have a problem with
tables which have been physically moved and replace with symbolic links. I
don't know how well it would handle DROP queries, but I'm not in the habit
of dropping 10Gb tables very often.

-Michael




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

* RE: [SQL] 2 million+ entries
@ 1999-08-26 18:05  Greg Youngblood <YoungblG@houstoncellular.com>
  0 siblings, 1 reply; 12+ messages in thread

From: Greg Youngblood @ 1999-08-26 18:05 UTC (permalink / raw)
  To: 'Michael Richards' <miker@scifair.acadiau.ca>; G. Anthony Reina <reina@nsi.edu>; +Cc: pgsql-sql

Which DPT controller do you have, how many SCSI buses, and how much cache?

I've got the I2O 3754U2 SmartRAID V with 64 meg on a single SCSI chain, also
connected to 4 9.1 baracuda (7200rpm) seagates. I'm about to add 2 more SCSI
chains, bump up the cache to 256 meg, and put in 4 seagate 50.1 gig drives. 

If you've got the I2O model of the controller card, what OS are you running?
If it's Linux, did you have any problems getting the I2O driver to recognize
the card on bootup? I ended up having to load it as a module.

Thanks,
Greg

Gregory S. Youngblood
ext 2164
 
 


> -----Original Message-----
> From: Michael Richards [mailto:miker@scifair.acadiau.ca]
> Sent: Thursday, August 26, 1999 12:49 AM
> To: G. Anthony Reina
> Cc: pgsql-sql@postgreSQL.org
> Subject: Re: [SQL] 2 million+ entries 
> 
> 
> On Wed, 25 Aug 1999, G. Anthony Reina wrote:
> 
> > > We're currently testing a system with about 1.2 million 
> records with 6.5
> > > and it outperforms SQL server 6 by quite a lot. It's 
> running on a dual P3
> > > with a 4 9.1 gig Cheetah array on a DPT controller 
> running RAID0. I'm
> > > pretty happy with it...
> > 
> > Great balls of fire! A dual P3 with a four 9.1 Gig RAID0 
> Cheetah array?
> > That's fantastic! How much did that set you back? Are there 
> any special
> > quirks about using Postgres on a multiple processor, multiple disk
> > system?
> As I recall it was about $8k CAD. The system purrs. I'm 
> especially happy
> with the DPT controller. This would be a truly worthy machine 
> for a large
> news feed.
> 
> -Michael
> 
> 
> ************
> 



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

* RE: [SQL] 2 million+ entries
@ 1999-08-27 21:39  Michael Richards <miker@scifair.acadiau.ca>
  parent: Greg Youngblood <YoungblG@houstoncellular.com>
  0 siblings, 0 replies; 12+ messages in thread

From: Michael Richards @ 1999-08-27 21:39 UTC (permalink / raw)
  To: Greg Youngblood <YoungblG@houstoncellular.com>; +Cc: G. Anthony Reina <reina@nsi.edu>; pgsql-sql

On Thu, 26 Aug 1999, Greg Youngblood wrote:

> Which DPT controller do you have, how many SCSI buses, and how much cache?
That machine has the 2144UW on it. I put the max for that card (64mb EDO)
cache on it. 

> I've got the I2O 3754U2 SmartRAID V with 64 meg on a single SCSI chain, also
> connected to 4 9.1 baracuda (7200rpm) seagates. I'm about to add 2 more SCSI
> chains, bump up the cache to 256 meg, and put in 4 seagate 50.1 gig drives. 
I'd do some solid performance testing on that card before upgrading to 256
megs of cache. (I didn't even know that model would take that amount) It
certainly sounds like overkill to me. I went with the 64 megs only because
I had a cheap source for the RAM. It is certainly more than we needed at
the time...

> If you've got the I2O model of the controller card, what OS are you running?
> If it's Linux, did you have any problems getting the I2O driver to recognize
> the card on bootup? I ended up having to load it as a module.

Because of the filesystem performance problems with linux's ext2
filesystem we opted to go FreeBSD instead.

-Michael




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


end of thread, other threads:[~1999-08-27 21:39 UTC | newest]

Thread overview: 12+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1999-08-20 23:59 2 million+ entries Matthew Hixson <hixson@frozenwave.com>
1999-08-24 06:30 ` Michael Richards <miker@scifair.acadiau.ca>
1999-08-24 07:28   ` Matthew Hixson <hixson@frozenwave.com>
1999-08-24 08:06   ` Moray McConnachie <Moray.McConnachie@computing-services.oxford.ac.uk>
1999-08-25 01:25     ` Michael Richards <miker@scifair.acadiau.ca>
1999-08-24 08:24 ` Rudy Gireyev <rgireyev@cnmnetwork.com>
1999-08-25 16:59 Re: [SQL] 2 million+ entries G. Anthony Reina <reina@nsi.edu>
1999-08-25 17:19 ` Bruce Momjian <maillist@candle.pha.pa.us>
1999-08-26 05:48 ` Michael Richards <miker@scifair.acadiau.ca>
1999-08-26 06:28 ` Michael Richards <miker@scifair.acadiau.ca>
1999-08-26 18:05 RE: [SQL] 2 million+ entries Greg Youngblood <YoungblG@houstoncellular.com>
1999-08-27 21:39 ` Michael Richards <miker@scifair.acadiau.ca>

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