Choosing InnoDB or NDB Engine?

MySQL & MariaDB Other MySQL® Questions
1

Hello, all.

I am new to world of MySQL Clustering.
I set up a MySQL 4.1 cluster and playing around with it.
I found out that if I didn’t specify an engine for a table. MySQL would said the table already exists and would not let me create the table. I was able to create tables if I specified the engine as either InnoDB or NDB.

My questions:

  1. Where I can set the default engine to be used for all tables (my.cnf)? What is the parameter? Do I have to set this parameter for all data nodes?
  2. Which engine should be used or best be used (InnoDB or NDB)? Which engine offers best performance in a clustered setup?

Thanks.

BSDUX Guy.

2
[B]bsdux wrote on Sun, 17 December 2006 10:11[/B]
I am new to world of MySQL Clustering. I set up a MySQL 4.1 cluster and playing around with it. I found out that if I didn't specify an engine for a table. MySQL would said the table already exists and would not let me create the table. I was able to create tables if I specified the engine as either InnoDB or NDB.

Hello, bsdux

just to be sure, you know what you’re dealing with, keep in mind, that MySQL NDB Cluster has a number of limitations so far. Please take a look at the list: http://dev.mysql.com/doc/refman/4.1/en/mysql-cluster-limitat ions.html

[B]bsdux[/B]
1. Where I can set the default engine to be used for all tables (my.cnf)? What is the parameter? Do I have to set this parameter for all data nodes?

Yes, you can set the default storage engine at my.cnf in a section [mysqld]. The variable is “default-storage-engine” (a depricated name: default-table-type) and in order to use NDB as a default storage engine the value should be “NDBCLUSTER”. By the way, prior to changing this, please check the output of “SHOW VARIABLES LIKE ‘storage_engine’”. I really wonder what is the default storage engine since it does not allow you to create new tables.

Regarding the data nodes, I think this variable should only be set on the SQL node(s), but not on the data nodes or management server.

[B]bsdux[/B]
2. Which engine should be used or best be used (InnoDB or NDB)? Which engine offers best performance in a clustered setup?

Right now, you don’t have much choice. If you want to be clustered, you must use NDB. InnoDB uses local storage of the SQL node you are connected to.

One more thing. You’ve mentioned you’re O.K. if you use InnoDB or NDB as an ENGINE. What happens if you set ENGINE to MyISAM with your current configuration?

Regards,
Aurimas