List:General Discussion« Previous MessageNext Message »
From:Dietrich Speer Date:December 9 2002 4:26am
Subject:Re: E-Commerce, db-clustering and MySQL?
View as plain text  
Hello Bjorn:

Thanks for the encouragement. There are a lot of questions I still have,
though.

I am constantly working on moving the sql code into stored procedures in
order to improve speed.
I'd like to keep asp on 2000 servers. Doesn't the lack of stored procedures
force me to go back to parsed queries on the site? And am I right thinking
that that would be a big step backwards?

We're talking more than 100 stored procedures and tables with up to 750.000
rows.

But it sounds like it's doable .....

DIetrich


----- Original Message -----
From: "Björn Pålsson" <tiggrrrr@stripped>
To: <mysql@stripped>
Sent: Sunday, December 08, 2002 3:14 PM
Subject: Re: E-Commerce, db-clustering and MySQL?


> First, I wish to say that you are making the right descision leaving M$
and
> taking a step into MySQL.
>
> Currently, I'm working on a project involving +500.000 rows and estimated
> 30.000 visitors / day on a 2 x dual 2.8 GHz Xeon dedicated for SQL.
> Additional servers are for HTTP and other tasks. There will be a lot of
> load on the SQL when the users searches our database on either category,
> area or on a fulltext keyword.
>
> The key to managing thousands of visitors is to have a system setup to
> minimize the number of querys and managing the traffic between the
diffrent
> machines. We will use a second gigabit-switch and a secondary net on the
> 10.0.0.0 to unload our primary public switch. We also have pre-cached
pages
> with the most popular products/listings. In our case, the visitor may
input
> data and therefore he will trigger a re-cache. A separate machine is used
> for this purpose as this may happen very often.
>
> To make a user allowed to input data you must setup the servers in a ring
> with each server acting as both master and slave. If you have a clean
> system with very few inserts/updates you may choose a simpler solution
with
> a single master an several slaves f.x. if the only insert is in the
> checkout in a webshop. If you have click counters, this simple solution
> will fall.
>
> If you have the ring-solution, you must make shure your script never makes
> any contradicting inserts. Use auto_increment if possible when inserting
> and/or use your session-id as an identifier. Always use relative updates
> when you must update a value.
>
> Do not write
>
> SELECT count from article WHERE id = '$id'
>
> $count++
>
> UPDATE count set count = '$count' WHERE id = '$id'
>
> but use
>
> UPDATE count = count + 1
>
> All these things to think about will force you to dig deep into your
script
> and if you have a script-in-a-box solution from a third party coder, you
> are definately in a difficult situation. If you know your script well, you
> will have an easier task.
>
> Please tell me about your future findings!
>
> Greets
>
> >Hello:
> >
> >We are running a large e-commerce site currently with ASP/MSSQL. The ever
> >increasing number of hits, complexity of the site and request for
reporting
> >is slowly but surely bringing us to the point were the current database
> >application is getting tired.
> >
> >Now we're thinking Linux, MySQL and clustering.
> >
> >I have been looking around a little and don't like the fact that there
are
> >no stored procedures and no triggers. However - if we cluster several
quad
> >processor machines we should be fine without.
> >
> >Right now it's all theory, and there don't seem to be a lot of articles
on
> >the web about this. At least I had no luck finding any.
> >
> >Does anybody have real life experiences with this kind of a setup? Does
> >anybody know where to find reports about this?
> >
> >I'd be greatful for any link or hint!
> >
> >Dietrich Speer
> >
> >
>
>
> ---------------------------------------------------------------------
> Before posting, please check:
>    http://www.mysql.com/manual.php   (the manual)
>    http://lists.mysql.com/           (the list archive)
>
> To request this thread, e-mail <mysql-thread126942@stripped>
> To unsubscribe, e-mail
<mysql-unsubscribe-dspeer=heartlandamerica.com@stripped>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
>


Thread
E-Commerce, db-clustering and MySQL?Dietrich Speer8 Dec
  • Re: E-Commerce, db-clustering and MySQL?Benjamin Pflugmann8 Dec
  • RE: E-Commerce, db-clustering and MySQL?Brian P. Austin8 Dec
    • Re: E-Commerce, db-clustering and MySQL? Thanks to all!Dietrich Speer8 Dec
  • Re: E-Commerce, db-clustering and MySQL?Björn  Pålsson8 Dec
    • Re: E-Commerce, db-clustering and MySQL?Lou Scalpati8 Dec
    • Re: E-Commerce, db-clustering and MySQL?Dietrich Speer9 Dec