Re: help with site+database
On Wed, Jul 18, 2001 at 09:07:53AM +1000, Craig Sanders wrote:
> On Wed, Jul 18, 2001 at 12:37:05AM +1000, Jeremy Lunn wrote:
> > On Tue, Jul 17, 2001 at 11:52:14PM +1000, Craig Sanders wrote:
> > > > Expecially if you have a dinamic PHP site, for me, MySQL and PHP,
> > > > is the better and faster solution.
> > >
> > > actually, it's not. mysql is faster (these days, only marginally
> one database user-id is normal. but in a web environment you can get
> any number of actual users. with mysql, if one of them happens to be
> writing something to the table (e.g. to a sessions table, such as for
> Apache::Session) then the whole table is locked and no-one else can get
> access. this effectively serialises access.
> this is absurd for a web site database. it won't scale beyond about 5
> simultaneous users without a lot of very ugly client-side tricks (such
> as using multiple tables to do a job that should be done by one table).
> btw, with postgres, only the row that is being updated is locked which
> allows simultaneous use.
hmmm, where did I see --skip-locking...?
I'd be curious to hear more about the effects of locking under a "typical web"
load. The locking strategies seem crucial. Any database using crude and
"pessimistic locking" is just going to die under a web type load if it has
to lock at all. I know oracle has "optimistic" locking and "versioning".
I **think** postgres does too? Comments?
I'd bet many sites are not complex enough to read and write from the same
tables. Yeah, they write orders, they read parts. Once or twice a
day they batch load parts or modify one or two but generally there is
not a conflict. It amounts to a RO database; mysql has worked fine
for us in those circumstances.
Sessions, logins, clicktracks and so forth are the most likely
problems, where one will be writing and reading from the same
table, with indices and lots of fast moving data. That's not
the usual small business brochureware site, but a small business
with an active call center/internet would have that and need
good transaction support + data mining too.
I'm not sure the issue is mysql vs postgres, but what does it take
to run a particular site. If the site is heavily interactive with
complex queries and transactions, the choice seems limited. Oracle
will do the trick, but the installation on linux seems so fragile:
I'd not dare touch anything for fear I'd never recover. The only
other choice seems to be postgres. Oh well... have they got a
history in their cli yet?
Christopher F. Miller, Publisher email@example.com
MaineStreet Communications, Inc 208 Portland Road, Gray, ME 04039
Content management, electronic commerce, internet integration, Debian linux