Hardware recommendations to scale to silly load

matt wrote:

I'm wondering if the good people out there could perhaps give me some
pointers on suitable hardware to solve an upcoming performance issue.
I've never really dealt with these kinds of loads before, so any
experience you guys have would be invaluable. Apologies in advance for
the amount of info below...

My app is likely to come under some serious load in the next 6 months,
but the increase will be broadly predictable, so there is time to throw
hardware at the problem.

Currently I have a ~1GB DB, with the largest (and most commonly accessed
and updated) two tables having 150,000 and 50,000 rows.

A typical user interaction with the system involves about 15
single-table selects, 5 selects with joins or subqueries, 3 inserts, and
3 updates. The current hardware probably (based on benchmarking and
profiling) tops out at about 300 inserts/updates *or* 2500 selects per
second.

There are multiple indexes on each table that updates & inserts happen
on. These indexes are necessary to provide adequate select performance.

Are you sure? Have you tested the overall application to see if possibly
you gain more on insert performance than you lose on select performanc?

(Hey, you asked for musings ...)

Current hardware/software:
Quad 700MHz PIII Xeon/1MB cache
3GB RAM
RAID 10 over 4 18GB/10,000rpm drives
128MB battery backed controller cache with write-back enabled
Redhat 7.3, kernel 2.4.20
Postgres 7.2.3 (stock redhat issue)

It's possible that compiling Postgres manually with proper optimizations
could yield some improvements, as well as building a custom kernel in
Redhat.

Also, you don't mention which filesystem you're using:
http://www.potentialtech.com/wmoran/postgresql.php

I need to increase the overall performance by a factor of 10, while at
the same time the DB size increases by a factor of 50. e.g. 3000
inserts/updates or 25,000 selects per second, over a 25GB database with
most used tables of 5,000,000 and 1,000,000 rows.

Notably, the data is very time-sensitive, so the active dataset at any
hour is almost certainly going to be more on the order of 5GB than 25GB
(plus I'll want all the indexes in RAM of course).

Also, and importantly, the load comes but one hour per week, so buying a
Starfire isn't a real option, as it'd just sit idle the rest of the
time. I'm particularly interested in keeping the cost down, as I'm a
shareholder in the company!

I can't say for sure without looking at your application overall, but
many applications I've seen could be optimized. It's usually a few
seconds here and there that take hours to find and tweak.

But if you're in the situation where you have more time than money,
you may find that an overall audit of your app is worthwhile. Consider
taking parts that are in perl (for example) and recoding them into C
(that is, unless you've already identified that all the bottlenecks are
at the PostgreSQL server)

I doubt if the suggestions I've made are going to get you 10x, but they
may get you 2x, and then you only need the hardware to do 5x.

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

On Tue, 2003-08-26 at 20:35, matt wrote:

I'm wondering if the good people out there could perhaps give me some
pointers on suitable hardware to solve an upcoming performance issue.
I've never really dealt with these kinds of loads before, so any
experience you guys have would be invaluable. Apologies in advance for
the amount of info below...

My app is likely to come under some serious load in the next 6 months,
but the increase will be broadly predictable, so there is time to throw
hardware at the problem.

Currently I have a ~1GB DB, with the largest (and most commonly accessed
and updated) two tables having 150,000 and 50,000 rows.

A typical user interaction with the system involves about 15
single-table selects, 5 selects with joins or subqueries, 3 inserts, and
3 updates. The current hardware probably (based on benchmarking and
profiling) tops out at about 300 inserts/updates *or* 2500 selects per
second.

There are multiple indexes on each table that updates & inserts happen
on. These indexes are necessary to provide adequate select performance.

Current hardware/software:
Quad 700MHz PIII Xeon/1MB cache
3GB RAM
RAID 10 over 4 18GB/10,000rpm drives
128MB battery backed controller cache with write-back enabled

Much more cache needed. Say 512MB per controller?

Redhat 7.3, kernel 2.4.20
Postgres 7.2.3 (stock redhat issue)

Upgrade to Pg 7.3.4!

I need to increase the overall performance by a factor of 10, while at
the same time the DB size increases by a factor of 50. e.g. 3000

Are you *sure* about that???? 3K updates/inserts per second xlates
to 10,800,000 per hour. That, my friend, is a WHOLE HECK OF A LOT!

inserts/updates or 25,000 selects per second, over a 25GB database with

Likewise: 90,000,000 selects per hour.

most used tables of 5,000,000 and 1,000,000 rows.

Notably, the data is very time-sensitive, so the active dataset at any

During the 1 hour surge, will SELECTs at 10 minutes after the
hour depend on INSERTs at 5 minutes after the hour?

If not, maybe you could pump the INSERT/UPDATE records into
flat files, to be processed after the 1-hour surge is complete.
That may reduce the h/w requirements.

hour is almost certainly going to be more on the order of 5GB than 25GB
(plus I'll want all the indexes in RAM of course).

Also, and importantly, the load comes but one hour per week, so buying a

Only one hour out of 168????? May I ask what kind of app it is?

Starfire isn't a real option, as it'd just sit idle the rest of the
time. I'm particularly interested in keeping the cost down, as I'm a
shareholder in the company!

What a fun exercises. Ok, lets see:
Postgres 7.3.4
RH AS 2.1
12GB RAM
motherboard with 64 bit 66MHz PCI slots
4 - Xenon 3.0GHz (1MB cache) CPUs
8 - 36GB 15K RPM as RAID10 on a 64 bit 66MHz U320 controller
having 512MB cache (for database)
2 - 36GB 15K RPM as RAID1 on a 64 bit 66MHz U320 controller
having 512MB cache (for OS, swap, WAL files)
1 - library tape drive plugged into the OS' SCSI controller. I
prefer DLT, but that's my DEC bias.
1 - 1000 volt UPS.

If you know when the flood will be coming, you could perform
SELECT * FROM ... WHERE statements on an indexed field, to
pull the relevant data into Linux's buffers.

Yes, the 8 disks is capacity-overkill, but the 8 high-speed
spindles is what you're looking for.

So what do I need? Can anyone who has (or has ever had) that kind of
load in production offer any pointers, anecdotes, etc? Any theoretical
musings also more than welcome. Comments upon my sanity will be
referred to my doctor.

If the best price/performance option is a second hand 32-cpu Alpha
running VMS I'd be happy to go that way ;-)

I'd love to work on a GS320! You may even pick one up for a million
or 2. The license costs for VMS & Rdb would eat you, though.

Rdb *does* have ways, though, using large buffers and hashed indexes,
with the table tuples stored on the same page as the hashed index
keys, to make such accesses *blazingly* fast.

Many thanks for reading this far.

--
-----------------------------------------------------------------
Ron Johnson, Jr. ron.l.johnson@cox.net
Jefferson, LA USA

"A C program is like a fast dance on a newly waxed dance floor
by people carrying razors."
Waldi Ravens

On Wed, Aug 27, 2003 at 02:35:13AM +0100, matt wrote:

I need to increase the overall performance by a factor of 10, while at
the same time the DB size increases by a factor of 50. e.g. 3000
inserts/updates or 25,000 selects per second, over a 25GB database with
most used tables of 5,000,000 and 1,000,000 rows.

Your problem is mostly going to be disk related. You can only get in
there as many tuples in a second as your disk rotates per second. I
suspect what you need is really expensive disk hardware (sorry to
tell you that) set up as RAID 1+0 on fibre channel or something.
3000 write transactions per second is probably too much to ask for
any standard hardware.

But given that you are batching this once a week, and trying to avoid
big expenses, are you use this is the right approach? Perhaps you
should consider a redesign using COPY and such?

A

-- 
----
Andrew Sullivan                         204-4141 Yonge Street
Liberty RMS                           Toronto, Ontario Canada
<andrew@libertyrms.info>                              M2P 2A8
                                         +1 416 646 3304 x110

Are you sure? Have you tested the overall application to see if possibly
you gain more on insert performance than you lose on select performanc?

Unfortunately dropping any of the indexes results in much worse select
performance that is not remotely clawed back by the improvement in
insert performance.

Actually there doesn't really seem to *be* that much improvement in
insert performance when going from 3 indexes to 2. I guess indexes must
be fairly cheap for PG to maintain?

It's possible that compiling Postgres manually with proper optimizations
could yield some improvements, as well as building a custom kernel in
Redhat.

Also, you don't mention which filesystem you're using:
http://www.potentialtech.com/wmoran/postgresql.php

Yeah, I can imagine getting 5% extra from a slim kernel and
super-optimised PG.

The FS is ext3, metadata journaling (the default), mounted noatime.

But if you're in the situation where you have more time than money,
you may find that an overall audit of your app is worthwhile. Consider
taking parts that are in perl (for example) and recoding them into C
(that is, unless you've already identified that all the bottlenecks are
at the PostgreSQL server)

I can pretty cheaply add more CPU horsepower for the app servers, as
they scale horizontally, so I can chuck in a couple (or 3, or 4, or ...)
more dual-cpu boxen with a gig of ram and tell the load balancer about
them. The problem with the DB is that that approach simply won't work -
the box just has to get bigger!

I doubt if the suggestions I've made are going to get you 10x, but they
may get you 2x, and then you only need the hardware to do 5x.

It all helps :-) A few percent here, a few percent there, pretty soon
you're talking serious improvements...

Thanks

Matt

matt wrote:

Are you sure? Have you tested the overall application to see if possibly
you gain more on insert performance than you lose on select performanc?

Unfortunately dropping any of the indexes results in much worse select
performance that is not remotely clawed back by the improvement in
insert performance.

Bummer. It was just a thought: never assume dropping indexes will hurt
performance. But, since you've obviously tested ...

Actually there doesn't really seem to *be* that much improvement in
insert performance when going from 3 indexes to 2. I guess indexes must
be fairly cheap for PG to maintain?

Don't know how "cheap" they are.

I have an app that does large batch updates. I found that if I dropped
the indexes, did the updates and recreated the indexes, it was faster
than doing the updates while the indexes were intact.

It doesn't sound like your app can use that approach, but I thought I'd
throw it out there.

It's possible that compiling Postgres manually with proper optimizations
could yield some improvements, as well as building a custom kernel in
Redhat.

Also, you don't mention which filesystem you're using:
http://www.potentialtech.com/wmoran/postgresql.php

Yeah, I can imagine getting 5% extra from a slim kernel and
super-optimised PG.

The FS is ext3, metadata journaling (the default), mounted noatime.

ext3 is more reliable than ext2, but it's 1.1x slower. You can squeeze
a little performance by using Reiser or JFS, if you're not willing to
take the risk of ext2, either way, it's a pretty minor improvement.

Does noatime make much difference on a PostgreSQL database? I haven't
tested that yet.

But if you're in the situation where you have more time than money,
you may find that an overall audit of your app is worthwhile. Consider
taking parts that are in perl (for example) and recoding them into C
(that is, unless you've already identified that all the bottlenecks are
at the PostgreSQL server)

I can pretty cheaply add more CPU horsepower for the app servers, as
they scale horizontally, so I can chuck in a couple (or 3, or 4, or ...)
more dual-cpu boxen with a gig of ram and tell the load balancer about
them. The problem with the DB is that that approach simply won't work -
the box just has to get bigger!

Can you split it onto multiple boxes? Some database layouts lend themselves
to this, others don't. Obviously you can't do joins from one server to
another, so you may lose more in multiple queries than you gain by having
multiple servers. It's worth looking into though.

I know my answers aren't quite the ones you were looking for, but my
experience is that many people try to solve poor application design
by simply throwing bigger hardware at the problem. It appears as though
you've already done your homework, though.

Hope this has been _some_ help.

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

Hi,

can anyone point me to information regarding this please?

Objective is to find entries that match one (or more) supplied strings in
two tables. The first has about 20.000 entries with 1 varchar field to
check, the other about 40.000 with 5 varchar fields to check. The currently
used sequential scan is getting too expensive.

Thanks,
Fabian

Don't know how "cheap" they are.

I have an app that does large batch updates. I found that if I dropped
the indexes, did the updates and recreated the indexes, it was faster
than doing the updates while the indexes were intact.

Yeah, unfortunately it's not batch work, but real time financial work.
If I drop all the indexes my select performance goes through the floor,
as you'd expect.

Does noatime make much difference on a PostgreSQL database? I haven't
tested that yet.

Yup, it does. In fact it should probably be in the standard install
documentation (unless someone has a reason why it shouldn't). Who
*cares* when PG last looked at the tables? If 'nomtime' was available
that would probably be a good thing too.

Can you split it onto multiple boxes? Some database layouts lend themselves
to this, others don't. Obviously you can't do joins from one server to
another, so you may lose more in multiple queries than you gain by having
multiple servers. It's worth looking into though.

I'm considering that. There are some tables which I might be able to
split out. There amy even be some things I can pull from the DB
altogether (session info in particular, so long as I can reliably send a
given user's requests to the same app server each time, bearing in mind
I can't see the cookies too easily because 50% of the requests are over
SSL)

I know my answers aren't quite the ones you were looking for, but my
experience is that many people try to solve poor application design
by simply throwing bigger hardware at the problem. It appears as though
you've already done your homework, though.

Well, I *hope* that's the case! The core issue is simply that we have
to deal with an insane load for 1 hour a week, and there's just no
avoiding it.

Maybe I can get Sun/HP/IBM to lend some gear (it's a pretty high-profile
site).

On 27 Aug 2003, matt wrote:

I'm wondering if the good people out there could perhaps give me some
pointers on suitable hardware to solve an upcoming performance issue.
I've never really dealt with these kinds of loads before, so any
experience you guys have would be invaluable. Apologies in advance for
the amount of info below...

My app is likely to come under some serious load in the next 6 months,
but the increase will be broadly predictable, so there is time to throw
hardware at the problem.

Currently I have a ~1GB DB, with the largest (and most commonly accessed
and updated) two tables having 150,000 and 50,000 rows.

A typical user interaction with the system involves about 15
single-table selects, 5 selects with joins or subqueries, 3 inserts, and
3 updates. The current hardware probably (based on benchmarking and
profiling) tops out at about 300 inserts/updates *or* 2500 selects per
second.

There are multiple indexes on each table that updates & inserts happen
on. These indexes are necessary to provide adequate select performance.

Current hardware/software:
Quad 700MHz PIII Xeon/1MB cache
3GB RAM
RAID 10 over 4 18GB/10,000rpm drives
128MB battery backed controller cache with write-back enabled
Redhat 7.3, kernel 2.4.20
Postgres 7.2.3 (stock redhat issue)

I need to increase the overall performance by a factor of 10, while at
the same time the DB size increases by a factor of 50. e.g. 3000
inserts/updates or 25,000 selects per second, over a 25GB database with
most used tables of 5,000,000 and 1,000,000 rows.

It will likely take a combination of optimizing your database structure /
methods and increasing your hardware / OS performance.

You probably, more than anything, should look at some kind of
superfast, external storage array that has dozens of drives, and a large
battery backed cache. You may be able to approximate this yourself with
just a few dual channel Ultra 320 SCSI cards and a couple dozen hard
drives. The more spindles you throw at a database, generally speaking,
the more parallel load it can handle.

You may find that once you get to 10 or 20 drives, RAID 5 or 5+0 or 0+5
will be outrunning 1+0/0+1 due to fewer writes.

You likely want to look at the fastest CPUs with the fastest memory you
can afford. those 700MHz xeons are likely using PC133 memory, which is
painfully slow compared to the stuff pumping data out at 4 to 8 times the
rate of the older stuff.

Maybe an SGI Altix could do this? Have you looked at them? They're not
cheap, but they do look to be quite fast, and can scale to 64 CPUs if need
be. They're interbox communication fabric is faster than most CPU's front
side busses.

Notably, the data is very time-sensitive, so the active dataset at any
hour is almost certainly going to be more on the order of 5GB than 25GB
(plus I'll want all the indexes in RAM of course).

Also, and importantly, the load comes but one hour per week, so buying a
Starfire isn't a real option, as it'd just sit idle the rest of the
time. I'm particularly interested in keeping the cost down, as I'm a
shareholder in the company!

Interesting. If you can't spread the load out, can you batch some parts
of it? Or is the whole thing interactive therefore needing to all be
done in real time at once?

So what do I need?

whether you like it or not, you're gonna need heavy iron if you need to do
this all in one hour once a week.

Can anyone who has (or has ever had) that kind of
load in production offer any pointers, anecdotes, etc? Any theoretical
musings also more than welcome. Comments upon my sanity will be
referred to my doctor.

If the best price/performance option is a second hand 32-cpu Alpha
running VMS I'd be happy to go that way ;-)

Actually, I've seen stuff like that going on Ebay pretty cheap lately. I
saw a 64 CPU E10k (366 MHz CPUs) with 64 gigs ram and 20 hard drives going
for $24,000 a month ago. Put Linux or BSD on it and Postgresql should
fly.

You probably, more than anything, should look at some kind of
superfast, external storage array

Yeah, I think that's going to be a given. Low end EMC FibreChannel
boxes can do around 20,000 IOs/sec, which is probably close to good
enough.

You mentioned using multiple RAID controllers as a boost - presumably
the trick here is to split the various elements (WAL, tables, indexes)
across different controllers using symlinks or suchlike? Can I feasibly
split the DB tables across 5 or more controllers?

Also, and importantly, the load comes but one hour per week, so buying a
Starfire isn't a real option, as it'd just sit idle the rest of the
time. I'm particularly interested in keeping the cost down, as I'm a
shareholder in the company!

Interesting. If you can't spread the load out, can you batch some parts
of it? Or is the whole thing interactive therefore needing to all be
done in real time at once?

All interactive I'm afraid. It's a micropayment system that's going to
be used here in the UK to do online voting for a popular TV programme.
The phone voting system has a hard limit of [redacted] million votes per
hour, and the producers would like to be able to tell people to vote
online if the phone lines are busy. They can vote online anyway, but we
expect the average viewer to have to make 10 calls just to get through
during peak times, so the attraction is obvious.

whether you like it or not, you're gonna need heavy iron if you need to do
this all in one hour once a week.

Yeah, I need to rent a Starfire for a month later this year, anybody got
one lying around? Near London?

Actually, I've seen stuff like that going on Ebay pretty cheap lately. I
saw a 64 CPU E10k (366 MHz CPUs) with 64 gigs ram and 20 hard drives going
for $24,000 a month ago. Put Linux or BSD on it and Postgresql should
fly.

Jeez, and I thought I was joking about the Starfire. Even Slowaris
would be OK on one of them.

The financial issue is that there's just not that much money in the
micropayments game for bursty sales. If I was doing these loads
*continuously* then I wouldn't be working, I'd be in the Maldives :-)

I'm also looking at renting equipment, or even trying out IBM/HP's
'on-demand' offerings.

Martha Stewart called it a Good Thing whenmatt@ymogen.net (matt)wrote:

I'm also looking at renting equipment, or even trying out IBM/HP's
'on-demand' offerings.

You're assuming that this is likely to lead to REAL savings, and that
seems unlikely.

During the recent power outage in the NorthEast, people looking for
generators and fuel were paying _premium_ prices, not discounted
prices.

If your hardware requirement leads to someone having to buy hardware
to support your peak load, then _someone_ has to pay the capital cost,
and that someone is unlikely to be IBM or HP. "Peak demand" equipment
is likely to attract pretty "peaked" prices.

If you can find someone who needs the hardware during the day, but who
_never_ needs it during your needful hours, then there might be an
arrangement to be had, assuming the "someone else" trusts you to use
what's, at other times, their hardware, and assuming you trust them
with the financial information you're managing.
--
select 'cbbrowne' || '@' || 'ntlug.org';
http://www3.sympatico.ca/cbbrowne/linux.html
Rules of the Evil Overlord #170. "I will be an equal-opportunity
despot and make sure that terror and oppression is distributed fairly,
not just against one particular group that will form the core of a
rebellion." <http://www.eviloverlord.com/&gt;

After takin a swig o' Arrakan spice grog, scott.marlowe@ihs.com
("scott.marlowe") belched out... :-):

whether you like it or not, you're gonna need heavy iron if you need
to do this all in one hour once a week.

The other thing worth considering is trying to see if there is a way
of partitioning the workload across multiple hosts.

At the point that you start going past hardware that is
"over-the-counter commodity" stuff, the premiums start getting pretty
high. Dual-CPU Intel boxes are pretty cheap compared to buncha-CPU
Sparc boxes.

If some sort of segmentation of the workload can be done, whether by
area code, postal code, or perhaps the last couple digits of the
caller's phone number, or even a "round robin," it's likely to be a
lot cheaper to get an array of 4 Dual-Xeon boxes with 8 disk drives
apiece than a Sun/HP/IBM box with 16 CPUs.
--
let name="cbbrowne" and tld="ntlug.org" in name ^ "@" ^ tld;;
http://cbbrowne.com/info/linuxxian.html
"Show me... show me... show me... COMPUTERS!"

Christopher Browne wrote:

Martha Stewart called it a Good Thing whenmatt@ymogen.net (matt)wrote:

I'm also looking at renting equipment, or even trying out IBM/HP's
'on-demand' offerings.

You're assuming that this is likely to lead to REAL savings, and that
seems unlikely.

During the recent power outage in the NorthEast, people looking for
generators and fuel were paying _premium_ prices, not discounted
prices.

If your hardware requirement leads to someone having to buy hardware
to support your peak load, then _someone_ has to pay the capital cost,
and that someone is unlikely to be IBM or HP. "Peak demand" equipment
is likely to attract pretty "peaked" prices.

If you can find someone who needs the hardware during the day, but who
_never_ needs it during your needful hours, then there might be an
arrangement to be had, assuming the "someone else" trusts you to use
what's, at other times, their hardware, and assuming you trust them
with the financial information you're managing.

I hadn't considered this, but that's not a bad idea.

With FreeBSD, you have jails, which allow multiple users to share
hardware without having to worry about user A looking at user B's
stuff. Does such a paradigm exist on any heavy iron? I have no
idea where you'd go to find this kind of "co-op" server leasing,
but it sure sounds like it could work.

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

On Wed, 2003-08-27 at 21:26, Bill Moran wrote:

Christopher Browne wrote:

Martha Stewart called it a Good Thing whenmatt@ymogen.net (matt)wrote:

[snip]

With FreeBSD, you have jails, which allow multiple users to share
hardware without having to worry about user A looking at user B's
stuff. Does such a paradigm exist on any heavy iron? I have no

IBM invented the idea (or maybe stole it) back in the '70s. The
VM hypervisor was designed as a conversion tool, to let customers
run both OS/MVS and DOS/VSE, to aid in converting from VSE to MVS.

Customers, the cheap, uncooperative beasts, liked VSE, but also liked
VM, since it let them have, for example, a dev, test, and production
"systems" all on the same piece of h/w, thus saving them oodles of
money in h/w costs and maintenance fees.

Yes, yes, the modern term for this is "server consolidation", and
VMware does the same thing, 30 years after dinosaur customers had
it on boxen that academics, analysts and "young whippersnappers"
said were supposed to be extinct 20 years ago.

--
-----------------------------------------------------------------
Ron Johnson, Jr. ron.l.johnson@cox.net
Jefferson, LA USA

"Knowledge should be free for all."
Harcourt Fenton Mudd, Star Trek:TOS, "I, Mudd"

2003-08-27 ragyogó napján matt ezt üzente:

Yeah, I can imagine getting 5% extra from a slim kernel and
super-optimised PG.

Hm, about 20%, but only for the correctness - 20% not help you also :(

The FS is ext3, metadata journaling (the default), mounted noatime.

Worst fs under linux :) Try xfs.

--
Tomka Gergely
"S most - vajon barbárok nélkül mi lesz velünk?
Ők mégiscsak megoldás voltak valahogy..."

2003-08-27 ragyogó napján Bill Moran ezt üzente:

With FreeBSD, you have jails, which allow multiple users to share
hardware without having to worry about user A looking at user B's
stuff. Does such a paradigm exist on any heavy iron? I have no

Of course. All IBM hw can do this, because on all ibm hw runs linux, and
linux have more ways to do this :)

--
Tomka Gergely
"S most - vajon barbárok nélkül mi lesz velünk?
Ők mégiscsak megoldás voltak valahogy..."

Are you *sure* about that???? 3K updates/inserts per second xlates
to 10,800,000 per hour. That, my friend, is a WHOLE HECK OF A LOT!

Yup, I know!

During the 1 hour surge, will SELECTs at 10 minutes after the
hour depend on INSERTs at 5 minutes after the hour?

Yes, they do. It's a payments system, so things like account balances
and purchase histories have to be updated in real time.

Only one hour out of 168????? May I ask what kind of app it is?

Online voting for an unnamed TV show...

If the best price/performance option is a second hand 32-cpu Alpha
running VMS I'd be happy to go that way ;-)

I'd love to work on a GS320! You may even pick one up for a million
or 2. The license costs for VMS & Rdb would eat you, though.

You'd be amazed how little they do go for actually :-)

On Thu, 2003-08-28 at 03:17, matt wrote:

Are you *sure* about that???? 3K updates/inserts per second xlates
to 10,800,000 per hour. That, my friend, is a WHOLE HECK OF A LOT!

Yup, I know!

During the 1 hour surge, will SELECTs at 10 minutes after the
hour depend on INSERTs at 5 minutes after the hour?

Yes, they do. It's a payments system, so things like account balances
and purchase histories have to be updated in real time.

Only one hour out of 168????? May I ask what kind of app it is?

Online voting for an unnamed TV show...

If the best price/performance option is a second hand 32-cpu Alpha
running VMS I'd be happy to go that way ;-)

I'd love to work on a GS320! You may even pick one up for a million
or 2. The license costs for VMS & Rdb would eat you, though.

You'd be amazed how little they do go for actually :-)

Then that's what I'd do. VMS, Rdb, (your favorite 3GL language).
Presumably the SELECT statements will be direct lookup instead
of range retrieval? If so, then I'd create a *large* amount of
GLOBAL BUFFERS, many MIXED AREAs, tables PLACED VIA HASHED INDEXES
so that the index nodes are on the same page as the corresponding
tuples. Thus, 1 disk I/O gets both the relevant index key, plus
the tuple. (Each I/O reads 3 pages into GBs [Global Buffers], so
that if a later statement needs a records nearby, it's already in
RAM.)

With fast storage controllers (dual-redundant, with 512MB each)
you could even use RAID5, and your app may not even know the diffie.
Of course, since the requirements are *so* extreme, better still
stick to RAID10.

I know that a certain pharmaceutical company had a similar situation,
where test results would flood in every morning. A certain North-
eastern US wireless phone company needed to record every time every
phone call was started and stopped.

The technique I described is how both of these high-volume apps
solved The Need For Speed.

With VMS 7.3 and Rdb 7.1.04 and, oh, 16GB RAM, a carefully crafted
stored procedure run an hour or 2 before the show could pull the
necessary 5GB slice of the DB into GBs, and you'd reduce the I/O
load during the show itself.

Sorry it's not PostgreSQL, but I *know* that Rdb+VMS could handle
the task...

--
-----------------------------------------------------------------
Ron Johnson, Jr. ron.l.johnson@cox.net
Jefferson, LA USA

"You ask us the same question every day, and we give you the
same answer every day. Someday, we hope that you will believe us..."
U.S. Secretary of Defense Donald Rumsfeld, to a reporter

On Tue, 2003-08-26 at 23:59, Ron Johnson wrote:

What a fun exercises. Ok, lets see:
Postgres 7.3.4
RH AS 2.1
12GB RAM
motherboard with 64 bit 66MHz PCI slots
4 - Xenon 3.0GHz (1MB cache) CPUs
8 - 36GB 15K RPM as RAID10 on a 64 bit 66MHz U320 controller
having 512MB cache (for database)
2 - 36GB 15K RPM as RAID1 on a 64 bit 66MHz U320 controller
having 512MB cache (for OS, swap, WAL files)
1 - library tape drive plugged into the OS' SCSI controller. I
prefer DLT, but that's my DEC bias.
1 - 1000 volt UPS.

Be careful here, we've seen that with the P4 Xeon's that are
hyper-threaded and a system that has very high disk I/O causes the
system to be sluggish and slow. But after disabling the hyper-threading
itself, our system flew..

--
Chris Bowlby <excalibur@hub.org>
Hub.Org Networking Services

On 28 Aug 2003 at 11:05, Chris Bowlby wrote:

On Tue, 2003-08-26 at 23:59, Ron Johnson wrote:

What a fun exercises. Ok, lets see:
Postgres 7.3.4
RH AS 2.1
12GB RAM
motherboard with 64 bit 66MHz PCI slots
4 - Xenon 3.0GHz (1MB cache) CPUs
8 - 36GB 15K RPM as RAID10 on a 64 bit 66MHz U320 controller
having 512MB cache (for database)
2 - 36GB 15K RPM as RAID1 on a 64 bit 66MHz U320 controller
having 512MB cache (for OS, swap, WAL files)
1 - library tape drive plugged into the OS' SCSI controller. I
prefer DLT, but that's my DEC bias.
1 - 1000 volt UPS.

Be careful here, we've seen that with the P4 Xeon's that are
hyper-threaded and a system that has very high disk I/O causes the
system to be sluggish and slow. But after disabling the hyper-threading
itself, our system flew..

Anybody has opteron working? Hows' the performance?

Bye
Shridhar

--
A father doesn't destroy his children. -- Lt. Carolyn Palamas, "Who Mourns for
Adonais?", stardate 3468.1.