MySQL Performance: MySQL => InnoDB -> MEMORY Master-Slaves configuration

Members

Help

Home

Home » Performance » MySQL » InnoDB -> MEMORY Master-Slaves configuration

Show: Today's Messages :: Show Polls :: Message Navigator

InnoDB -> MEMORY Master-Slaves configuration [message #514]

Fri, 29 December 2006 08:52

delirii
Messages: 5
Registered: December 2006

Junior Member

Dear all,

I really need any advice about this kind of architecture :

A Master -> 4 slaves servers configuration with this particularity :
Most tables on the master are created with the InnoDB storage engine (others with MyISAM -> InnoDB is not needed for them) and the same tables are created on each salve server BUT with MEMORY Storage Engine.

Database will be about 3GB, slave servers (3.2GHZ Xeon processor) have 12MB of memory (at least 8MB of free memory). The 4 biggest (and most accessed) tables contains about 2 million rows. Others are smaller (10k or 100k rows).

The main idea behind this is to provide four very high performance MySQL slaves for our 4 load balanced webapps running on each server.

I've already built a sample of this architecture, running on a single server. I've solved the slave crash issue (how to recover the full data loss because of the MEMORY engine) but before going further, I want you to give me your opinion.

Thank you and... happy new year Smile

Report message to a moderator

Re: InnoDB -> MEMORY Master-Slaves configuration [message #515 is a reply to message #514 ]

Fri, 29 December 2006 09:18

Peter
Messages: 405
Registered: August 2006

Senior Member
Super Guru

Hm,

First if you use it mainly for reads and all data fits in memory Innodb tables normally perform already really well eliminating need for in memory tables.

And regarding memory tables and crash what would you expect ? You would need to reclone one way or around.

I would probably do physical clone ie via LVM and then run ALTER TABLE to convert tables to MEMORY before restoring replication.

Peter Zaitsev, MySQL Performance Expert
MySQL Performance Blog - http://www.mysqlperformanceblog.com
MySQL Consulting http://www.mysqlperformanceblog.com/mysql-consulting/

Report message to a moderator

Re: InnoDB -> MEMORY Master-Slaves configuration [message #516 is a reply to message #514 ]

Fri, 29 December 2006 09:44

delirii
Messages: 5
Registered: December 2006

Junior Member

Thank you for your answer. If I understand, InnoDB tables can be as fast as MEMORY tables for SELECT queries (if the memory is large enough to store all the data) ? At least if the innodb_buffer_pool_size is set correctly ?

So there is no good reason to use a different storage engine on the slave servers (even MyISAM) ? That can be a good news.

Regarding memory tables restoring process, i was thinking about that way :
- export master's data (dump) and save the binary log point right after this export (for instance each night).

When a slave crashes, the only thing to do is to modify the dump file to change the storage engine from Innodb to Memory, restore the dumped data and specify the new binary log point to the slave. Then start the slave (START SLAVE cmd).

Once again, because I'm quite new to all of this, I am still looking for the most robust and fastest solution.

Report message to a moderator

Previous Topic:	MySQL Performance References
Next Topic:	MySQL Replication

Goto Forum:

-=] Back to Top [=-

[ Generate printable PDF ] [ Syndicate this forum (XML) ] [

]

Current Time: Sun Jul 5 16:07:31 EDT 2009

Total time taken to generate the page: 0.01099 seconds

.:: Contact :: Home ::.

MySQL is a trademark of Sun Microsystems.
InnoDB is a trademark of Oracle Corp.

Percona Performance Forums are a service of Percona, Inc.
Not affiliated with Sun Microsystems or Oracle Corp.