Come join me at MySQL Camp 2009. I will be presenting how to run multiple instances of MySQL, on one installation.
The Hyatt Regency Santa Clara
5101 Great America Parkway
Santa Clara, CA 95054
USA
April 22, 2009 11:55 am – 12:35 pm
Presentation Schedule
I am continuously asked by wide-eyed green web 2.0 developers about Master-Master Replication (M-M). Development Managers and CTOs/CIOs aren’t immune to this either. They read a couple of posts about it, on the web, and seem to think that you can put together a couple of commodity servers, through in a Load Balancer and voila! Instant cluster, without the high price tag of Oracle’s RAC.
Unfortunately, its not that simple. MySQL actually has a cluster product which it acquired from Ericsson. It has some limitations. V5.0 needs to be able to have both data and index completely in memory. V5.1 needs to be able to have all index in memory. Either later choices performs a 2 phase commit, so at anytime, querying any node with the same query, will return the same result. In a M-M environment, depending on the load on either node, replication can be lagging, thus returning different results for the same query from each node.
If such a case is not handled by the application, at best, you will have inconsistent results, at worse dire results.
Some applications will simply not be able to use it at all. Here is an example. Say your DB Schema has a column that needs to be unique, however, not part of the Primary Key. If you insert a record on one node, then insert an identical record on the second node, you will end up with two legal records on their respective nodes. When it gets time to replicate that record in both directions, the unique constraint will make replication fail on both servers.
It does, if you are willing to live with a potentially inconsistent dataset after a node failure. Remember we specified earlier that replication can lag between nodes. If disaster strikes on one of your nodes, you have lost every transaction for the period that the other node was lagging behind the lost one.
The reuslt? Your website or app is still running, however data that was previously on one server will no longer be available. depending on your app, that could have huge consequences.
If you could mitigate the above mentioned risks, and design your app properly, you could take advantage of M-M. Reads would be shared between both nodes, theoretically allowing you to handle more load. You could also pull out one node at a time, deploy possible long schema changes, rebuild indexes, etc. while the other handles the whole load, then do the same to the other.
I tend to stay away from M-M. It adds an extra, often unnecessary, layer of complexity to your environment. It does have its place, and should be part of your little bag of tricks, but only pull it out when you need to, and when it absolutely fits. Don’t let that green PHP developper tell you he built it at home and it works, then you get stuck supporting a nightmare. His pager isn’t going to go off at 3am…yours is!
Hello Everyone!
I have finished my move to the Bay Area (Silicon Valley) and now have more time to return to the blog. I have learned a few new tricks as well I want to share.
So…..
I am relaunching the site. There will be a new format too. The podcast episodes will still be published for more high level discussions, then supported by a series of blog posts.
I think this will be a more productive format, since most users want to hear the high level discussion and then go to the site and refer to the material.
Thanks for all the fish!
Christos
I just started using MONyog a month ago, and purchased it today. I have used MySQL Enterprise Monitor for a year and a half, and although some believe its a superior product, its 5K per server per year for the Enterprise version.
I bought the Unlimited Pack for $999 and that is a perpetual license for an unlimited amount of servers. It gives me all the monitoring I need and without an agent installed on the MySQL server. That makes the sys-admins I work with very happy.
My prefered feature is the log analyzer. It makes analysing the slow-query-log and the general-log really easy and efficient.
Cool product, check it out if you can.
http://www.webyog.com/en/
International Business Machines said on Friday it has agreed to buy in-memory database software provider Solid Information Technology from private owners for an undisclosed sum. Solid’s largest owners were private equity firms Apax Partners and CapMan.
Solid is expected to have 2007 sales of around $14.4 million, Vesa Wallden, a member of Solid’s board told Reuters. IBM said the acquisition is expected to close in the first quarter of 2008. “IBM’s acquisition of Solid Information Technology supports the company’s growth strategy and capital allocation model, and it is expected to contribute to the achievement of the company’s objective for earnings-per-share growth through 2010,” IBM said in a statement.
Story Copyright © 2007 Reuters Limited. All rights reserved.
Recently we ran into a wall in one of my customers’ sites. They built an application that processed EDI documents. Each document, contained a list of transactions. Their application would launch a thread for each transaction in the document. On the surface this sounds good and the multi-threaded approach would speed up processing of a document.
InnoDB is the only built-in transactional storage engine and unfortunately has some limitations.
TX1 SET TRANSACTION ISOLATION LEVEL READ COMMITTED TX2 SET TRANSACTION ISOLATION LEVEL READ COMMITTED TX1 START TRANSACTION TX2 START TRANSACTION TX1 INSERT INTO child TX2 INSERT INTO child (with same parent) TX1 UPDATE parent TX2 UPDATE parent (same parent row) ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
We solved this issue using a third party storage engine; solidDB.
Federated Storage Engine (FSE) allows you to connect to a remote server and “mount” a table on your local server, which links to the data on the remote server, for read-only access.
$ mysql -u root -ppassword -h remote mysql> CREATE DATABASE test; mysql> use test; mysql> CREATE TABLE drivers (id INT,name VARCHAR(100)); mysql> INSERT INTO drivers (id, name) VALUES (1, ‘Chris’); mysql> INSERT INTO drivers (id, name) VALUES (2, ‘Sheeri’); mysql> INSERT INTO drivers (id, name) VALUES (3, ‘Elie’); mysql> select * from drivers; +------+--------+ | id | name | +------+--------+ | 1 | Chris | | 2 | Sheeri | | 3 | Elie | +------+--------+ 3 rows in set (0.08 sec) mysql> exit; $ mysql -u root -ppassword -h local mysql> CREATE DATABASE test; mysql> use test; mysql> CREATE TABLE drivers (id INT,name VARCHAR(100)) ENGINE=FEDERATED CONNECTION=’mysql://root:password@remote:3306/test/drivers’; mysql> select * from drivers; +------+--------+ | id | name | +------+--------+ | 1 | Chris | | 2 | Sheeri | | 3 | Elie | +------+--------+ 3 rows in set (0.14 sec) mysql> exit;
Enjoy FSE tables!
Bye! Thanks for all the fish!
