Percona XtraDB Cluster 5.7 and Storage Engine 'Memory'

Hi all,

i would like to know if Storage Engine MEMORY is working with the percona xtraDB Cluster 5.7 master-master replication.

I ask this because i have problems with a joomla installation when it try to execute some modifies on table, the error is “unknown storage engine ‘MEMORY’”

my default_storage_engine is innodb and i force the storage engine to be innodb

fron configuration:

default_storage_engine = InnoDB
enforce_storage_engine = InnoDB

thanks for any advice or hint
Claudio

No. The Engine memory is not supported in any xtradb cluster configuration. It doesn’t matter whether it is with multiple nodes accepting writes or one writer.

The problem with memory engine is that it provides no persistence. This means that the table contents disappear when the node is rebooted.

Ok thanks for the answer, i can’t understand why joomla use it, but this is not a percona problem

Some applications use memory engine for performance considerations. But very often you can use innodb engine. Try changing the table to InnoDB and verify that the performance impact is reduced.

But you must make sure that joomla support properly XtraDB Cluster. There are other things that must be taken into consideration. If possible, avoid master-master configurations (unless applications are using separate databases).

if i use

SHOW TABLE STATUS;

i get ONLY innodb in the Engine column, so all the tables are innodb, could be that in the UPDATE is used a memory engine rule ?

Each application use a different DB, we use percona xtradb cluster from several years without problems, also with joomla, but this new version 4.x seems to arise this problem.

Claudio

I’m sorry, but we can’t provide you with help for Joomla. If you have a testing environment, I suggest running the update on a standalone server (no pxc) and verifying if other engines than InnoDB are used in the new version.

I made a simple search and looks like some components require the MEMORY engine, but I don’t know if this is your case.

Ok i understand that percona is not supporting this type of engine but if i check

mysql> show engines;
±-------------------±--------±---------------------------------------------------------------------------±-------------±-----±-----------+
| Engine | Support | Comment | Transactions | XA | Savepoints |
±-------------------±--------±---------------------------------------------------------------------------±-------------±-----±-----------+
| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |
| BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO |
| InnoDB | DEFAULT | Percona-XtraDB, Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
| MyISAM | YES | MyISAM storage engine | NO | NO | NO |
| ARCHIVE | YES | Archive storage engine | NO | NO | NO |
| CSV | YES | CSV storage engine | NO | NO | NO |
| FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
±-------------------±--------±---------------------------------------------------------------------------±-------------±-----±-----------+
9 rows in set (0.00 sec)

it tells that it is supported instead

Why ?

Claudio

This is a bit complex. When you run the XtraDB Cluster version, you are running standard MySQL with some extensions that control the clustering. You can start the node with clustering disabled.

The Memory engine is supported by the standard component (linked and enabled by default) but is incompatible with the clustering component. There are two reasons for this engine not to be compatible with the clustering component:

  • It is not transactional.
  • It provides no durability.

Clustering requires the possibility of rolling back a transaction if there is a consistency issue. As memory is not transactional, it is impossible to roll back the changes. This would break the cluster and make it inconsistent.

Pep

Is possible for XtraDB to check the show engines command and show Support = NO in the engine list ?

Else if some application check for the available engine using mysql standard commands it will think that engine is supported and permitted, then when it will use that engine the clustering will answer with unknown storage engine ‘MEMORY’.

Or is there a way to know using a query which engines are actually supported by the cluster ?

Claudio

InnoDB is the only fully supported engine so far.

Replication works only with InnoDB storage engine.
Percona XtraDB Cluster Limitations