Converting Magento to Work Well on Galera Cluster

I have a Magento data set which run on MySQL-wsrep with Galera. Galera has its known limitations, and one of it is:

DELETE operation is unsupported on tables without primary key. Also rows in tables without primary key may appear in different order on different nodes. Don’t use tables without primary key.

Basically, if you want to have your DB serve by Galera cluster, please use InnoDB storage engine and define a primary key for each table. That’s all. Since Magento dataset is unaware of this limitation, you could see that there are many tables do not meet the criteria.

You can use following query to identify unsupported stuffs in Galera (Thanks to Giuseppe Maxia for this):

SELECT DISTINCT Concat(t.table_schema, '.', t.table_name)     AS tbl,
                t.engine,
                IF(Isnull(c.constraint_name), 'NOPK', '')     AS nopk,
                IF(s.index_type = 'FULLTEXT', 'FULLTEXT', '') AS ftidx,
                IF(s.index_type = 'SPATIAL', 'SPATIAL', '')   AS gisidx
FROM   information_schema.tables AS t
       LEFT JOIN information_schema.key_column_usage AS c
              ON ( t.table_schema = c.constraint_schema
                   AND t.table_name = c.table_name
                   AND c.constraint_name = 'PRIMARY' )
       LEFT JOIN information_schema.statistics AS s
              ON ( t.table_schema = s.table_schema
                   AND t.table_name = s.table_name
                   AND s.index_type IN ( 'FULLTEXT', 'SPATIAL' ) )
WHERE  t.table_schema NOT IN ( 'information_schema', 'performance_schema','mysql' )
       AND t.table_type = 'BASE TABLE'
       AND ( t.engine <> 'InnoDB'
              OR c.constraint_name IS NULL
              OR s.index_type IN ( 'FULLTEXT', 'SPATIAL' ) )
ORDER  BY t.table_schema,
          t.table_name;

Example:

mysql> SELECT DISTINCT
    ->        CONCAT(t.table_schema,'.',t.table_name) as tbl,
    ->        t.engine,
    ->        IF(ISNULL(c.constraint_name),'NOPK','') AS nopk,
    ->        IF(s.index_type = 'FULLTEXT','FULLTEXT','') as ftidx,
    ->        IF(s.index_type = 'SPATIAL','SPATIAL','') as gisidx
    ->   FROM information_schema.tables AS t
    ->   LEFT JOIN information_schema.key_column_usage AS c
    ->     ON (t.table_schema = c.constraint_schema AND t.table_name = c.table_name
    ->         AND c.constraint_name = 'PRIMARY')
    ->   LEFT JOIN information_schema.statistics AS s
    ->     ON (t.table_schema = s.table_schema AND t.table_name = s.table_name
    ->         AND s.index_type IN ('FULLTEXT','SPATIAL'))
    ->   WHERE t.table_schema NOT IN ('information_schema','performance_schema','mysql')
    ->     AND t.table_type = 'BASE TABLE'
    ->     AND (t.engine <> 'InnoDB' OR c.constraint_name IS NULL OR s.index_type IN ('FULLTEXT','SPATIAL'))
    ->   ORDER BY t.table_schema,t.table_name;
+-------------------------------------------------+--------+------+----------+--------+
| tbl                                             | engine | nopk | ftidx    | gisidx |
+-------------------------------------------------+--------+------+----------+--------+
| magento.api2_acl_user                           | InnoDB | NOPK |          |        |
| magento.api_session                             | InnoDB | NOPK |          |        |
| magento.catalogsearch_fulltext                  | MyISAM |      | FULLTEXT |        |
| magento.catalog_category_anc_categs_index_idx   | InnoDB | NOPK |          |        |
| magento.catalog_category_anc_categs_index_tmp   | InnoDB | NOPK |          |        |
| magento.catalog_category_anc_products_index_idx | InnoDB | NOPK |          |        |
| magento.catalog_category_anc_products_index_tmp | InnoDB | NOPK |          |        |
| magento.catalog_category_product_index_enbl_idx | InnoDB | NOPK |          |        |
| magento.catalog_category_product_index_enbl_tmp | InnoDB | NOPK |          |        |
| magento.catalog_category_product_index_idx      | InnoDB | NOPK |          |        |
| magento.catalog_category_product_index_tmp      | InnoDB | NOPK |          |        |
| magento.catalog_product_index_price_downlod_tmp | MEMORY |      |          |        |
| magento.oauth_nonce                             | MyISAM | NOPK |          |        |
| magento.weee_discount                           | InnoDB | NOPK |          |        |
| magento.widget_instance_page_layout             | InnoDB | NOPK |          |        |
| magento.xmlconnect_config_data                  | InnoDB | NOPK |          |        |
+-------------------------------------------------+--------+------+----------+--------+

 

I do not know much about Magento data set and structure. So I am assuming that the output above can simply bring future problems according to Galera limitation, so it might be good to comply with that and alter whatever necessary on those tables.

So I start by adding a simple auto increment primary key into tables labeled as NOPK:

mysql> ALTER TABLE magento.api2_acl_user ADD id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY FIRST;
mysql> ALTER TABLE magento.api_session ADD id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY FIRST;
mysql> ALTER TABLE magento.weee_discount ADD id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY FIRST;
mysql> ALTER TABLE magento.widget_instance_page_layout ADD id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY FIRST;
mysql> ALTER TABLE magento.xmlconnect_config_data ADD id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY FIRST;

Next, add primary key and convert the storage as engine to InnoDB:

mysql> ALTER TABLE magento.oauth_nonce ADD id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY FIRST, ENGINE='InnoDB';

Then, remove the full-text indexing and convert the storage engine to InnoDB:

mysql> ALTER TABLE magento.catalogsearch_fulltext DROP INDEX FTI_CATALOGSEARCH_FULLTEXT_DATA_INDEX;
mysql> ALTER TABLE magento.catalogsearch_fulltext ENGINE='InnoDB';

I am quite sure the above drop indexes statement would likely to have some performance hit. Maybe Magento does not really fit in Galera multi-master environment, but it is worth to give it a try. I will keep updating this post to share about this.

Am going to sleep now. Cheers!

3 thoughts on “Converting Magento to Work Well on Galera Cluster

  1. very useful, especially since I’m not particularly familiar with MySQL.

    how are you getting on with it? found any other problems?

    i’m trying to configure a load of new servers to increase resiliency compared to our current architecture. I’ve got 16 apache servers, and 4 larger servers for galera, with haproxy load balancing for both apache and MySQL.
    I’ve copied over a magento website & db for testing purposes, i haven’t made any changes apart from moving MyISAM tables to InnoDB, I’ve not created primary keys yet, i’m using galera 3.0, based on MySQL 5.6 which should support full-text indexing in InnoDB.

    so far everything appears to be ok, apart from when i try applying catalogPriceRules, which crashes the galera cluster. have you experienced anything like that?

    Reply

    1. Hi Lee,

      I was not experiencing that problem, have you checked the MySQL Galera error log? It must state the failed query before it crashes (or look into GRA_* generated file). I have abandoned the testing work for Magento so I could not say more..

      Reply

Leave a Reply

Your email address will not be published. Required fields are marked *