pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feed2 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