Scaling opportunity in the DB framework

[ChrisW]

Hi,

First post to the dev group - thanks for all the hard work, hope this
doesn't tread on too many toes!

I've been looking recently into scaling J!, mainly as our applications
written on the J! framework are often hit by huge spikes in traffic,
the relative cost effectiveness for us to use cloud computing allows
us to scale our infrastructure rapidly when demand is up.

One thing that I can't seem to be able to do without modifying the
framework is allowing the databases to handle multiple SQL servers.
I may have missed a trick here, but I can't see how to do it.

The ideal set up for this is to have one master server and n slave
servers, syncing off the master (you'll probably already know this),
the master handling the update/create/modify and the slaves handling
the brunt of the selects. There are of course other levels you can
take this to but this is a basic start, and if you need anything more
complicated, you would likley be doing it in-house anyway.

The nice thing to be able to do would be to have a list of database
connections, one being the master, and the Query functions ether being
passed if it was an update query (and then select the primary) or not,
and in this instance select a random server from the (weight loaded?)
list of other servers.

I would appreciate your feedback on this idea, part of my 2010 new
years resolutions is to get more stuff cloud based :)

Thanks again,

Chris W -.

[Hannes Papenberg]

Hi Chris,
we discussed something similar earlier and among other things, I think
this was one of the reasons why the database-branch was started. We
decided we wanted to add several database drivers for other systems,
like MSSQL, Oracle or MongoDB, and at one point we also talked about
adding a driver for MySQL that supported multiple MySQL-servers. I'm not
sure if this now actually is in that branch or if that was just
something Mitch or somebody else worked on for customers. In any case,
if you wanted to do some work on this, we most certainly could give you
write access to that branch, you can add such a driver and everyone
would benefit. ;-)

Hannes

> --
>
> You received this message because you are subscribed to the Google Groups "Joomla! CMS Development" group.
> To post to this group, send an email to joomla-...@googlegroups.com.
> To unsubscribe from this group, send email to joomla-dev-cm...@googlegroups.com.
> For more options, visit this group at http://groups.google.com/group/joomla-dev-cms?hl=en-GB.
>
>
>
>

[ChrisW]

Hi Hannes,

I would be very interested in trying that out, I've had a look in the
SVN and I'll be setting up a testing environment to see what I can
achieve!

Thanks for the pointers,

Chris -.

[Andrew Godziuk]

On Sun, Dec 27, 2009 at 6:34 PM, Hannes Papenberg
<hack...@googlemail.com> wrote:
> Hi Chris,
> we discussed something similar earlier and among other things, I think
> this was one of the reasons why the database-branch was started. We
> decided we wanted to add several database drivers for other systems,
> like MSSQL, Oracle or MongoDB, and at one point we also talked about
> adding a driver for MySQL that supported multiple MySQL-servers. I'm not
> sure if this now actually is in that branch or if that was just
> something Mitch or somebody else worked on for customers. In any case,
> if you wanted to do some work on this, we most certainly could give you
> write access to that branch, you can add such a driver and everyone
> would benefit. ;-)

I worked on multiple MySQL driver, but I didn't have time to submit it
to Joomlacode SVN after I gained write access. Sometime in December I
posted a message containing URL of the repo where I temporarily keep
the code.

This is for master-master, though, no slave support yet.

--
Andrzej Godziuk
http://CloudAccess.net/

[Mitch Pirtle]

On Sun, Dec 27, 2009 at 10:06 AM, ChrisW <scam...@gmail.com> wrote:
>
> The ideal set up for this is to have one master server and n slave
> servers, syncing off the master (you'll probably already know this),
> the master handling the update/create/modify and the slaves handling
> the brunt of the selects. There are of course other levels you can
> take this to but this is a basic start, and if you need anything more
> complicated, you would likley be doing it in-house anyway.

Have you checked out MySQL Proxy yet?

http://forge.mysql.com/wiki/MySQL_Proxy

I also have a database driver written to deal with master-slave
setups, can dig around for it if anyone is interested.

-- Mitch

[Cohen Ofer]

Did you check the warning on the reference manual of mysql-proxy?
http://dev.mysql.com/doc/refman/5.0/en/mysql-proxy.html
Quote: "Warning - MySQL Proxy is currently an Alpha release and should
not be used within production environments."
Also, on the project itself there is some known bugs:
http://forge.mysql.com/wiki/MySQL_Proxy_RW_Splitting#Bugs

Cohen Ofer

28/12/2009 16:45, Qoute Mitch Pirtle:

[Mitch Pirtle]

On Mon, Dec 28, 2009 at 2:09 PM, Cohen Ofer <ofer...@gmail.com> wrote:
> Did you check the warning on the reference manual of mysql-proxy?
> http://dev.mysql.com/doc/refman/5.0/en/mysql-proxy.html
> Quote: "Warning - MySQL Proxy is currently an Alpha release and should
> not be used within production environments."
> Also, on the project itself there is some known bugs:
> http://forge.mysql.com/wiki/MySQL_Proxy_RW_Splitting#Bugs

Just sayin' :-)

For the Quizilla project we created a MySQL driver that looked for
"^SELECT" and tossed that to the slave connections, otherwise handed
off to the master. Ironically it worked out really well for us on a
very, very high traffic website (peaked at 58M weekly page views).

-- Mitch