I'm glad someone asked this, because I'd like to try and optimize my
database or the way I'm using it, and I've reached my personal limit on
optimization at this point.
1) I'm using two machines so far for my e-comerce solution. The website is
usedcars.com, in case you want to go and check the performance, which
probably sucks right now. :-) One machine is running the web server and java
servlets, the other postgres lastest from CVS. Server for psql is RH Linux
6, on PII 333, 192MB RAM, 104MB Swap. The HD psql is running on is 4GB
UW SCSI. No RAID, just daily FTP and Tape backups. I'm getting ready to
upgrade the MB to the latest PIII whatever this weekend. I'm thinking a
dual-CPU box would be a good idea, however, I'm not sure how much good a
dual-CPU will really do. I feel that in another 2 months, I'll be in the
same boat again. Whereas if I come up with a distributed solution, I can
just keep adding those $1000 Linux boxes. :-)
2) One table gets 99% of the queries, it has about 300K rows, by watching
the log, it seems to get about 120 queries per minute on average, peaking
at 480 queries per minute during peak hours. Not sure what a join is, do
you mean union? If so, then yes, almost every query to this table is of
the form
select a.*, b.1, b.2 where a.oid = b.id and a.int4 < x and a.str =
'String' union select a.*, c.1, c.2 where a.oid = c.id and a.int4 < x and
a.str = 'String' limit 100;
I defined indexes on all the fields which appear in a WHERE clause, using
either hash or btree, as recommended by the docs. The only operator I
couldn't find was for a money type.
I don't use order by, although I'd like to, because the docs said that
indexes are not used on order by queries. I use limit also.
I think writing a Distribution/Replication engine using Java servlets
should be a medium size task. Making it fault-tolerant and dealing with
crash and error recovery issues could make it larger. If I end up writing
it from scratch, I'll post the source for others to use.
Thanks,
Rich
On Fri, 29 Oct 1999, Chris Albertson wrote:
>
> > >
> > > Date: Thu, 28 Oct 1999 01:11:32 -0700
> > > From: Rich Ryan <[EMAIL PROTECTED]>
> > > Subject: HSA (Highly Scalable Architecture) Distribution and
replication
> > >
> > > Has anyone written a HSA e-commerce solution using postgres? In
english,
> > > this means I want more than one server running postgres with the same
> > > database(s). I get lots and lots of queries, and it's just too much
for one
> > > poor machine to handle.
> >
> >
> > Is this really true? "too much for one poor machine to handle"?
> >
> > Could you be more specific?
> > 1) What kind of hardware are you using. What CPU(s), how fast?
> > How much RAM?, What kind of disk(s) (RAID?) and so on.
> >
> > 2) How big is your task? How many rows and tables. How many
> > queries per minute? Do you do many big joins?
> >
> > I think Postgres scales well if you throw hardware at at. I wonder
> > if you've tried that yet.
> >
> > All that said, I'd be very much interrested in Distribution and
> > replication for another reason. I am working with a large database.
> > (on order of a hundred million rows) Our application is distributed
> > over the (much to slow) Internet. The ability to periodically
> > synchronize servers would work for us.
> >
> >
> > PS Please cc any reply to me directly as a get the digest.
> > --
> > Chris Albertson
> >
> > [EMAIL PROTECTED] Voice: 626-351-0089 X127
> > Logicon, Pasadena California Fax: 626-351-0699
> >
>
************
