Large databases (100GB+)

#21

Bodo <bodo (AT) nomail (DOT) org.invalid> wrote:

Quote:

In article <9eoht7-2er.ln1 (AT) xl (DOT) homelinux.org>, Axel Schwenke <axel.schwenke (AT) gmx (DOT) de> wrote:

InnoDB Hotbackup

Now free?
I'd love to test it.

Free as in Freibier

You get one to taste it and then have the option to buy more

Quote:

Only available in select Commercial Editions

http://www.mysql.com/products/enterprise/backup.html

And also Oracle now made
InnoDB Hotbackup freely available in the Enterprise Backup Tool.

Anybody can download Enterprise Backup for testing purposes.
To use it longer than 30 days, you need a license/contract.

http://edelivery.oracle.com/

XL

#22

On 2010-12-11, Axel Schwenke <axel.schwenke (AT) gmx (DOT) de> wrote:

Quote:

Ignoramus13683 <ignoramus13683 (AT) NOSPAM (DOT) 13683.invalid> wrote:
On 2010-12-10, Axel Schwenke <axel.schwenke (AT) gmx (DOT) de> wrote:

Having to run CHECK/REPAIR table is purely a MyISAM problem. That's
one reason why most "real" MySQL installations use InnoDB nowadays.

Is there any disadvantage of InnoDB that I might overlook?

The obvious points are the lacking support for FULLTEXT and SPATIAL
indexes in InnoDB. Also InnoDB tends to allocate more disk space for
the same data (factor 1.5 to 2 compared to MyISAM) - but OTOH InnoDB
suffers much less from fragmentation than MyISAM does.

But the biggest difference is, that InnoDB is a transactional engine
(and that's also the reason why it can recover much faster, i.e.
after a crash). If your application does not use explicite transaction
control, then InnoDB will run in AUTOCOMMIT mode and this means you
will see huge amounts of log I/O.

The preferred solution is to add transaction control to your
application code (frame groups of DML statements in BEGIN/COMMIT).
If you cannot do that, you might need to configure
innodb_flush_log_at_trx_commit=2 to avoid the AUTOCOMMIT
performance trap.

See:

http://dev.mysql.com/doc/refman/5.1/en/commit.html
http://dev.mysql.com/doc/refman/5.1/...db-tuning.html
http://dev.mysql.com/doc/refman/5.1/...arameters.html

... make sure you use a recent version
of MySQL to get the fast recovery implementation. I suggest to wait
for 5.5 becoming GA (should be really soon).

How about 5.1, would this work satisfactorily?

Yes, provided you use a recent version. In 5.1 there are two InnoDB
implementations: the builtin (old) and the plugin (new). Make sure
you use the plugin as it has some nice new features (inluding fast
recovery). In 5.5 the plugin code is used as builtin InnoDB.

See:

http://dev.mysql.com/doc/refman/5.1/...in-innodb.html
http://blogs.innodb.com/wp/2010/04/i...ance-recovery/

This is very nice. It looks like MySQL plus InnoDB is the
winner. Right?

InnoDB is the superior engine for almost all use cases (there are
edge cases though). In 5.5 InnoDB becomes the default engine.
From a design point of view, InnoDB ist very similar to Oracle DB.
Only better (says Heikki

Thanks Axel. This was very interesting and the setting above is
perfect for my needs. Now I need to figurte out how to use that plugin
in Ubuntu Lucid.

i

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Large databases (100GB+)

comp.databases.mysql comp.databases.mysql

Re: Large databases (100GB+) - 12-14-2010 , 04:36 AM

Re: Large databases (100GB+) - 12-14-2010 , 09:46 AM