Re: [codership-team] Is it safe to use Galera with Magento?

[Henrik Ingo]

On Thu, Nov 8, 2012 at 10:54 AM, myeagleflies <thr...@globi.org> wrote:
> First approach seems safer at first glance. However there may be potential
> problems in the future if there is a change to table schema.

I would go with the first approach too. If future Magento releases
change the schema in incompatible ways, you have to deal with that
case-by-case.

henrik


On Thu, Nov 8, 2012 at 10:54 AM, myeagleflies <thr...@globi.org> wrote:
> Hi,
>
> We are considering using Galera with Magento. Is someone else doing this?
> Are there any issues?
>
> My main concern right now is there is some tables in mysql which do not have
> primary indexes on them. According to galera limitations those tables should
> be modified by adding primary index. Could you please tell me what is best
> way to do this? I can think about adding additional id column to each table
> with auto_increment. Another approach could be to find columns which contain
> unique items and create primary index on that.
>
> First approach seems safer at first glance. However there may be potential
> problems in the future if there is a change to table schema.
>
> Thanks!
>
> --
>
>



--
henri...@avoinelama.fi
+358-40-8211286 skype: henrik.ingo irc: hingo
www.openlife.cc

My LinkedIn profile: http://www.linkedin.com/profile/view?id=9522559

[myeagleflies]

[myeagleflies]

We started testing Galera + Magento 10 days ago. Our setup is: 2 galera nodes and 2 garbd nodes. One of the galera nodes has virtual IP assigned to it via pacemaker. This is our cluster IP. Magento webservers communicate with cluster IP. This way one node is de facto primary and second galera node is secondary. We do not need to talk with both nodes at same time. Projected traffic to our website is small. Main purpose for Galera is to allow for easy failover.

I found some issues recently:
- I could not run 'USE magento' on one of the nodes. googled for it and someone suggested rebooting such node. Reboot seems to fix the issue. node is now properly connected to cluster.
- there are differences in data on both nodes. some tables contain more rows on one node than on another. and there is even one table which is missing on one of the nodes!

Difference in data look scary. Is this typical? What is recommended way of troubleshooting this issue? Is Magento not fully compatible with Galera?

We do have some tables without primary keys. If DELETE operations were performed on those tables this could explain differences in data in those tables. However this does not explain why one table is missing.

I am quite puzzled and looking for an advice.

Thanks in advance!

[Ilias Bertsimas]

Hi,

That sounds weird, could you please post the results of SHOW STATUS LIKE 'WSREP%'; from both of your nodes ?

Kind Regards,

Ilias.

[Mikkel Christensen]

Difference in data look scary. Is this typical?

Are you certain that all your tables are of type InnoDB?

What is recommended way of troubleshooting this issue? Is Magento not fully compatible with Galera?

  You need to add pk's to those tables without and generally to make sure to live up to the guidelines of Galera, i don't know of anybody who successfully ran Magento on Galera, but that doesn't mean it's imposible, just that noone (i have heard of) have done it yet, so please repport back on your findings :-)

Another good advice is to set up some kind of monitoring to alert you when Galera goes out of sync, when that happends you should examine logfiles to figure out what wend wrong to avoid it from happending again.

/ Mikkel

[myeagleflies]

Please take a look http://pastebin.com/e5tZ0YL3

[Ilias Bertsimas]

It seems you have 2 different clusters between those nodes:

node1:

1. wsrep_cluster_conf_id   1
2. wsrep_cluster_size      1

node2:

1. wsrep_cluster_conf_id   10
2. wsrep_cluster_size      2

Different nodes on each one and different conf_ids the first one split and formed a cluster of it's own using the same uuid as it seems.

[myeagleflies]

On Wednesday, November 14, 2012 11:02:57 AM UTC, mc wrote:

Difference in data look scary. Is this typical?

Are you certain that all your tables are of type InnoDB?

There are 20 tables which are not InnoDB:

- 3 MyISAM

- 17 MEMORY

What is best way of converting MyISAM to InnoDB? MEMORY probably has to stay as it is?

 

What is recommended way of troubleshooting this issue? Is Magento not fully compatible with Galera?

  You need to add pk's to those tables without and generally to make sure to live up to the guidelines of Galera, i don't know of anybody who successfully ran Magento on Galera, but that doesn't mean it's imposible, just that noone (i have heard of) have done it yet, so please repport back on your findings :-)

Well when googling for a way to find 'USE magento' issue I found some posts on it. So definitely some people use magento with galera. I do not feel very comfortable to be the first one to use it in production.

We are going to add those primary keys via 'magento way' - using magento module to do this. However we experienced some problems and when I started investigation I found issues which were reported at beginning of this thread.

 

Another good advice is to set up some kind of monitoring to alert you when Galera goes out of sync, when that happends you should examine logfiles to figure out what wend wrong to avoid it from happending again.

OK. This is definitely sound advice. Thanks!

myeagleflies

 

/ Mikkel

[myeagleflies]

OK. This is definitely serious issue. What is best way of fixing it? How to troubleshoot gardb to see which node those are connected to?

[Ilias Bertsimas]

Node1 seems to be on it's own, you can decide which node is more consistent and clear the data of the other one and make it join the conmsistent node by taking a full SST. 

[myeagleflies]

Please take a look at my config files http://pastebin.com/BVvwMB35


To shed some more light. We performed failover test few days ago:
- garb1 and galera node 1 were shut down. then tests were performed on magento
- garb1 and galera node 1 were powered on
- garb2 and galera node 2 were shut down. tests were performed on magento

I wonder if this could cause current split brain scenario?

[Ilias Bertsimas]

I can see in node1's config:

1. # Group communication system handle
2. wsrep_cluster_address="gcomm://"

You did not change that to point to another cluster node before shutdown ? If not then maybe that is the cause, as when you started node1 again it did not connect to the cluster and continued using the saved uuid it previously had but with the notion of being the first node of the cluster the others will bootstrap to.

[myeagleflies]

No I did not change it to another cluster before shutdown. I was not sure how failover happens but was presuming that possibly node which is brought up will be invited by other nodes to join their cluster. From what you are saying it seems we really should monitor for situations when one node goes down in order to prevent such splint brain scenarious.

BTW: Aren't garbd designed to prevent such events? Can I change node config not to start if it can't find other nodes to join to? I think it would be better if node which is going to be alone would simply refuse to start.

[Ilias Bertsimas]

As mentioned in the documentation the empty gcomm:// should only be used when you are starting the first node of a new cluster and should be changed to point to another node's address once you have the cluster up with more than 1 node.

I personally monitor the number of members every node sees and if it less than it should be then something is wrong.

[myeagleflies]

Cool. Thank you very much.

Could you provide an advice how to proceed with MyISAM and MEMORY tables? I presume first ones should be converted into InnoDB. What is the best way to do this? What about MEMORY tables?

[Ilias Bertsimas]

You're welcome.

Yes convert myisam to innodb,  I assume memory tables are not replicard in galera (according to the wiki section galera limitations) so you have to be able to handle the loss of data in a memory table in your application when you failover.

[myeagleflies]

It seems we hit a blocker. MyISAM table is using FULLTEXT index which can't be converted to InnoDB. Is there any workaround? If not I will have to migrate back to standard replication ;(

[myeagleflies]

I have found this blog entry (http://www.mysqlperformanceblog.com/2012/05/14/announcement-of-percona-xtradb-cluster-5-5-23/) which states MyISAM is supported in Percona since May 2012. Is MyISAM supported in current version of Galera too?

[Ilias Bertsimas]

Yes it is supported as this feature was imported from galera. The support is still experimental and there are a lot of things that do not work well. I would advice you to use only InnoDB with galera to avoid unexpected issues.

I do not know if you use the functionality that needs the FULLTEXT index and if you can just drop them. I guess you could try it out and see if it affects you at all. As it is not a custom project I assume changing magento to use a normal index with LIKE or regexp would not be an option.

[Ahmed Medhat]

It does not support MyISAM in either Percona build or native ones.

What I would do, Would be starting research on how to turn the need for these full-text indexes tables into something like Sphinx or Solr, once you're full-text/MyISAM free, you'll be fully Galera ready!

A Google search returned these results already http://code.google.com/p/magento-solr/ and http://code.google.com/p/magento-community-edition-solr/ that might help you regarding your issue.

Regards,

Ahmed Medhat

UNIX / Linux Infrastructure (Performance, Security & Cloud) Consultant

http://www.linkedin.com/in/ahmedmedhat

Disclaimer:
This transmission is confidential and intended solely for the person or organization to which it is addressed. It may contain privileged and confidential information. If you are not the intended recipient, you should not copy, distribute or take any action in reliance on it. If you have received this transmission in error, please notify the sender at the e-mail address above.

--
 
 

[myeagleflies]

No, the latter is not an option.

The former - if I drop those indexes there is likely to be performance hit. Not sure how to measure it.

I need to evaluate two options: going back to typical replication which is pretty safe but means manual failover. or sticking wit Galera but making custom changes to database scheme. making such changes on production site does not sound good.

[Henrik Ingo]

Galera has had experimental support for MyISAM since some time ago. It
seems that in Percona XtraDB Cluster it is actually made available:
http://www.mysqlperformanceblog.com/2012/05/14/announcement-of-percona-xtradb-cluster-5-5-23/

I have not personally tried it. I strongly recommend you setup some
kind of support relationship with Percona if you plan on using that
feature.

In this context it is also worth pointing out that in MySQL 5.6 also
InnoDB supports FULLTEXT indexes. So a good option here might simply
be to give it some time and try again next year.

henrik

[myeagleflies]

Sphinx and Solr look promising. Going Going to take a look.

[myeagleflies]

MySQL 5.6 sounds like ideal solution. When FULLTEXT indexes are supported in InnoDB then Magento will be fully compatible with Galera.

Regarding support relationship with Percona - I am not in position to make such decision. But can recommend it.

[Ronald Bradford]

The best case for when using FullText, is to separate these columns from your base tables.  

All tables can be InnoDB, and just the FULLTEXT table which is duplicated data is MyISAM.

You then maintain this copy using database triggers, or some other batch management process.

If there was a way to disable a table from Galera synchronous replication, you could the manage a MyISAM table this way.

The problem is you are relying on a product that is already written with given table schema design, so that has to be modified.

Regards

Ronald

--

[Alex Yurchenko]

What is the table(s) definition? Are they referenced by foreign keys
from other tables?

In certain primitive cases MyISAM replication may work quite well, e.g.
if you don't have auto_increment and non-deterministic fields there and
the table is infrequently updated. In some cases you may even tolerate
some inconsistency in the timestamps.

--
Alexey Yurchenko,
Codership Oy, www.codership.com
Skype: alexey.yurchenko, Phone: +358-400-516-011