Why we're using MyISAM store engine?

[Davide Tampellini]

hi, i have a little question:
why Joomla is using MyISAM store engine instead of InnoDB?

just because i'm curios...

[Niels Braczek]

Davide Tampellini schrieb:

> why Joomla is using MyISAM store engine instead of InnoDB?

It is for historical reasons. Five years ago, InnoDB was not widely
available.

Regards,
Niels

--
| http://www.kolleg.de · Das Portal der Kollegs in Deutschland |
| http://www.bsds.de · BSDS Braczek Software- und DatenSysteme |
| Webdesign · Webhosting · e-Commerce · Joomla! Content Management |
------------------------------------------------------------------

[Russell Winter]

Remember with later MySQL 5.5 releases, InnoDB (as well as many other changes) is planned to be the default, so I would guess that many hosts over the coming 6 months will also start setting their servers up this way..

Russ

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

[Paul Sweeney]

Hey all, I just wanted to chime in here that we've been using Joomla
to host 12 websites, and due to scaling issues, ended up converting
the whole sodding lot over to InnoDB. I can happily report that we've
had no issues in the last six months due to the InnoDB conversion, and
hey, it's faster and more scalable at this point. A huge plus when
you're dealing with master <--> master replication between two MySQL
boxes.

Cheers,
Paul

On Nov 21, 2:31 pm, Russell Winter <winte...@gmail.com> wrote:
> Remember with later MySQL 5.5 releases, InnoDB (as well as many other
> changes) is planned to be the default, so I would guess that many hosts over
> the coming 6 months will also start setting their servers up this way..
>
> Russ
>

> On 22 November 2010 02:10, Niels Braczek <nbrac...@bsds.de> wrote:
>
>
>
>
>
>
>
> > Davide Tampellini schrieb:
>
> > > why Joomla is using MyISAM store engine instead of InnoDB?
>
> > It is for historical reasons. Five years ago, InnoDB was not widely
> > available.
>
> > Regards,
> > Niels
>
> > --
> > |http://www.kolleg.de  ·   Das Portal der Kollegs in Deutschland |
> > |http://www.bsds.de  ·   BSDS Braczek Software- und DatenSysteme |
> > | Webdesign · Webhosting · e-Commerce · Joomla! Content Management |
> >  ------------------------------------------------------------------
>
> > --
> > You received this message because you are subscribed to the Google Groups
> > "Joomla! General Development" group.
> > To post to this group, send an email to
> > joomla-de...@googlegroups.com.
> > To unsubscribe from this group, send email to

> > joomla-dev-gene...@googlegroups.com<joomla-dev-general%2Bunsubs cr...@googlegroups.com>
> > .

[Niels Braczek]

Paul Sweeney schrieb:

> Hey all, I just wanted to chime in here that we've been using Joomla
> to host 12 websites, and due to scaling issues, ended up converting
> the whole sodding lot over to InnoDB. I can happily report that we've
> had no issues in the last six months due to the InnoDB conversion, and
> hey, it's faster and more scalable at this point. A huge plus when
> you're dealing with master <--> master replication between two MySQL
> boxes.

Did you modify more than just changing type from MyISAM to InnoDB? Id
could be interesting to use the constraints...

[Paul Sweeney]

As far as using any InnoDB features? Nope, no constraints, no
transactions, nothing really fancy on that end.

What we *have* done is gut the database driver and replace it with one
that does read/write separation, but checking the query (if
(strpos('select', $query)...), and routing to either a read/write
MySQL server, or a read-only SQL server.

And that's enabled us to scale this whole sheebang fairly well.

Cheers,
Paul

[Andy Nagai]

What feature makes innodb necessary for your scaling solution?

--
You received this message because you are subscribed to the Google Groups "Joomla! General Development" group.
To post to this group, send an email to joomla-de...@googlegroups.com.

To unsubscribe from this group, send email to joomla-dev-gene...@googlegroups.com.

[greg keys]

Paul, I'd be very interested in learning more about your database driver, is that something fairly easy to do,
or was it a custom deal? I'd like to know for the personal growth of my site if it would be advantageous to
apply.

Greg Keys
www.HawtWebsites.com LLC
www.Socialables.com
(971) Tec-Webs or 971-832-9327

[Janich]

Paul, that sounds like a good solution even though its an alternative
hack.
You wouldnt happen to have the library shared someplace? I think more
people could use this... I would certainly be interested in looking at
the changes.
And who knows if such a thing could make it into the core?

Andy, Im no db expert but I assume tablelocking could become a problem
in time. There seems to other nice things as well, but it all boils
down to the size of the scenario and preferences of the admin.

Do you know of any advantages/disadvantages of using innoDB?

This might be usefull reading:
- http://tag1consulting.com/MySQL_Engines_MyISAM_vs_InnoDB
- http://www.mysqlperformanceblog.com

Regards,
Janich





The only advantage InnoDB has over MyISAM is that it supports row
locking, while MyISAM only supports table locking

On Nov 23, 2:00 am, Paul Sweeney <paul.swee...@atomiconline.com>
wrote:

[Marco Pivetta]

Joomla is a CMS, and as a CMS it has a huge lot of reads and few writes.
This is why MyISAM is better suited for a CMS, as it's fast, simple and compresses data very well.
InnoDB could be a good advantage if you plan to have lots of writes (I.E. you use DB session storage. That should let you think about using InnoDB tables for sessions, as 1 req almost equals one DB write operation...).
Also consider that InnoDB has fewer problems with crashes and corruption, and you also don't need to optimize InnoDB tables. This shouldn't worry you, but in my case I had a website with something like 400 writes/sec being executed every day. That brought a heavy lot of table and indexes being crashed when I was working on MyISAM. Imagine a repair on a 4Gb DB table (2 hours with the website offline). Switching to InnoDB was a great advantage, but also made the website generally slower.
I would consider InnoDB as a good solution to be able to use foreign keys inside Joomla development, but there's no other effective advantage if you're not running an Ecommerce site requiring safe transactions...

Marco Pivetta
@Ocramius
http://marco-pivetta.com

2010/11/23 Janich <jan...@gmail.com>

--
You received this message because you are subscribed to the Google Groups "Joomla! General Development" group.
To post to this group, send an email to joomla-de...@googlegroups.com.

To unsubscribe from this group, send email to joomla-dev-gene...@googlegroups.com.

[francesco perillo]

> What we *have* done is gut the database driver and replace it with one
> that does read/write separation, but checking the query (if
> (strpos('select', $query)...), and routing to either a read/write
> MySQL server, or a read-only SQL server.

Nice !

So you have one "master" write only database and several
"slave/replicated" read only databases ?

Francesco

[Oli Griffiths]

Just a note, not sure if you're doing this, but you should be checking if
the query starts with SELECT, INSERT, UPDATE etc not just doing a strpos
(unless ur checking of its 0) as one can do an INSERT with a SELECT nested
in it.

Just my 2 cents., thought it may help

Oli

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

Contact Organic Development:
Telephone: 0845 869 7654
Web: http://www.organic-development.com
Email: in...@organic-development.com
Twitter: http://twitter.com/growwithorganic

This email and any attachments to it may be confidential and are intended solely for the use of the individual to whom it is addressed. Any views or opinions expressed are solely those of the author and do not necessarily represent those of Organic Development Ltd.
If you are not the intended recipient of this email, you must neither take any action based upon its contents, nor copy or show it to anyone.
Please contact the sender if you believe you have received this email in error.

[Paul Sweeney]

@Oli - I'm sure it's not bulletproof, but we're checking for "SET"
commands and passing them to both servers, detect select statements
(if(strtolower(substr($sql, 0, 6)) == 'select')) and pass those to the
read-only slave, and then everything else falls through to the read/
write master.

We could get more complex here with our rules, and nested selects are
run against the master but so far this has served us pretty well, and
the master DBs don't break much of a sweat at the moment.

@francesco - It's two master DBs replicating to each other, and each
master has a slave.

@Janich - The driver is a hacked up version of the default MySQL
driver, we just juggle two connection resources instead of one, and
then the query() function just branches based on the query type. Let
me check with the higher-ups here as far as releasing this as a
standalone plugin or replacement.

@Andy
@Marco Pivetta

Why are we using InnoDB? Sessions. Sessions were murdering our
servers. As sessions are held in the DB (and as the memcache sesion
driver isn't complete and/or supported in J!1.5) and as we're
replicating sessions between two masters, we set ourselves up for some
messy situations:

Concurrent deletions (a session being deleted on both master DBs) will
cause a replication error, which makes our DB very angry and takes a
while to resolve. Typical race condition stuff. Now, when replication
lags, the window for this race condition grows. MyISAM implements
table-level locking, meaning that it takes one or two slow queries
against the session table to back up replication... and you see where
we're going with this.

InnoDB's row-level locking is much kinder and friendlier, and now our
replication lags hover around a second or two at the worst. We've also
re-jiggered session deletion to stagger deletes (master1 only deletes
its sessions, and master2 only deletes its own).

@Marco - The common knowledge is that InnoDB is good for transaction /
foreign-key constraints, and that MyISAM is great for reads, but
honestly, in our benchmarking, InnoDB's pretty solid for reads. We are
seeing a small speed bump on MyISAM and at this point we're starting
to think about converting our slaves to MyISAM to maximise speed on
the slaves, and writes on the InnoDB masters, but prior to this, the
cost of table-level locks outweighed the speed advantage.

For crashes and corruption? I can't really speak to that; it's been 6
months and we've not run into any issues on that front.

Cheers,
Paul



On Nov 23, 12:54 am, Oli Griffiths <o...@organic-development.com>
wrote:

> Just a note, not sure if you're doing this, but you should be checking if
> the query starts with SELECT, INSERT, UPDATE etc not just doing a strpos
> (unless ur checking of its 0) as one can do an INSERT with a SELECT nested
> in it.
>
> Just my 2 cents., thought it may help
>
> Oli
>

> On 23/11/2010 08:49, "francesco perillo" <fperi...@gmail.com> wrote:
>
>
>
>
>
>
>
>
>
> >> What we *have* done is gut the database driver and replace it with one
> >> that does read/write separation, but checking the query (if
> >> (strpos('select', $query)...), and routing to either a read/write
> >> MySQL server, or a read-only SQL server.
>
> >Nice !
>
> >So you have  one "master" write only database and several
> >"slave/replicated" read only databases ?
>
> >Francesco
>
> >--
> >You received this message because you are subscribed to the Google Groups
> >"Joomla! General Development" group.
> >To post to this group, send an email to
> >joomla-de...@googlegroups.com.
> >To unsubscribe from this group, send email to
> >joomla-dev-gene...@googlegroups.com.
> >For more options, visit this group at
> >http://groups.google.com/group/joomla-dev-general?hl=en-GB.
>
> Contact Organic Development:
> Telephone:      0845 869 7654
> Web:            http://www.organic-development.com

> Email:          i...@organic-development.com

[Davide Tampellini]

i bump up this discussion.
Paul, can you share your edits to MySQL driver? i'd like to implement that too..

thanks man!