Running Drupal in a MySQL Cluster (ndbcluster)

published by Drupal Caulfield on Tue, 02/23/2010 - 17:10

 This post is going to be part of a series of my experiences (and our sysadmin's) of implementing Drupal in a high availability environment. Setting things up turned out to be a bit of a challenge and I hope this walk-through on setting up Drupal in a high availability MySQL cluster will be helpful to others who wish to use the same process.

 

Our server structure looks like:

Big IP

2 Apache Servers

2 MySQL Servers

1 MySQL Management Server

They are virtualized in RHEL 5, running on PHP 5.2.x, Apache, and MySQL Cluster 7.x

We are replicating using Multi-Master Replication. 

There are two main takeaways which I would like to put down right now.

  • Drupal will not run out of the box on ndbcluster tables (You can see at http://drupal.org/node/270/ that Drupal doesn't support ndbcluser). You will need to change the schema of the menu_router table. To do this I found this way easiest:
  1. Install Drupal normally on your local machine or a development server that is running a regular MySQL setup with MyISAM or InnoDB tables. 
  2. Go into PHPMyAdmin if you have it and do an export of the newly created Drupal database. To do this just select the database from the list then click o the export tab near the top then make sure to check the box for Save as File and select OK. Otherwise log into your MySQL and do a MySQL dump of it.
  3. Open up the exported SQL file in a text editor (e.g., vim) and you will need to edit the entries for menu_router. After the last parenthesis you are going to change to "DEFAULT CHARSET=latin1"instead of utf8. Now, a tangent and note of caution. Basically, Drupal does all tables in UTF8 by default, and while this is great for internationalization it means that your rows will take 3 times as much as space as with latin1. menu_router takes too much row space as UTF8 for the limitations imposed by MySQL cluster (8kb). So, thus you have a couple options you can either change all the text types to varchar and make all those varchars small, say around 150, or you can make the much easier change of just setting character set to latin1. The drawback is you may potentially have a problem on sites that use international characters in the menu names or other not normally used symbols.  Back to the point, your new menu_router entry will look something like this: 

     CREATE TABLE `menu_router` (

      `path` varchar(255) NOT NULL default '',

      `load_functions` varchar(255) NOT NULL,

      `access_callback` varchar(255) NOT NULL default '',

      `page_callback` varchar(255) NOT NULL default '',

      `fit` int(11) NOT NULL default '0',

      `number_parts` smallint(6) NOT NULL default '0',

      `tab_parent` varchar(255) NOT NULL default '',

      `tab_root` varchar(255) NOT NULL default '',

      `title` varchar(255) NOT NULL default '',

      `title_callback` varchar(255) NOT NULL default '',

      `title_arguments` varchar(255) NOT NULL default '',

      `type` int(11) NOT NULL default '0',

      `block_callback` varchar(255) NOT NULL default '',

      `position` varchar(255) NOT NULL default '',

      `weight` int(11) NOT NULL default '0',

      PRIMARY KEY  (`path`),

      KEY `fit` (`fit`),

      KEY `tab_parent` (`tab_parent`)

    ) DEFAULT CHARSET=latin1;

  4. BLOB and TEXT types cannot be used as indexes or keys on a table. Now fortunately, based on my limited understanding, we can remove the key without ill effects, just a loss in performance. The only place I have run into this so far was the locale_sources table which uses a BLOB column for 'source' and also defines a key for source. You can just remove the key and things should work fine. For example: you could do this:
    $new_sql = str_replace("PRIMARY KEY  (`lid`),

    KEY `source` (`source`(30))", "PRIMARY KEY  (`lid`)", $old_sql);

  5. The User table needs to have a user 0, but ndbcluster will auto-increment all columns by default. When doing the insert of the user table you need to add the following line of SQL to your export before the insert of the user table:
    SET sql_mode = 'NO_AUTO_VALUE_ON_ZERO';
    then after the insert of the user table add this line to change it back:
    SET sql_mode = '';

  6. Take note that when doing your find and replaces or however you choose to edit your SQL file, that line endings are different in Windows (CR and LF end a line) and Linux (LF only ends a line), so if you are mixing environment you might have to play around with your find/replace or regex syntax. You may want to check out my blog post on using regex in Notepad++ which gives you one easy way to do things if you want to do it manually. 

  7. You are also going to want to do a find and replace and change all mention of ENGINE=MyISAM with ENGINE=NDBCLUSTER

  8. Set up your MySQL Cluster (more on this in another or updated post).

  9. You need to change two of the default settings in MySQL. These settings need to be changed in the 'servername.cnf or my.cnf' file on your management node. In the [MYSQL_CLUSTER] section add "autocommit=1" Now, I don't know why this needs to be set explicitly because Autocommit is supposed to be set to 1 by default, and ours was set to 1 under root, but not under the user we created for Drupal, it was showing up as 0. (Quick note you can see MySQL server variables by doing select @@variable from the mysql command lie, e.g., @@autocommit;)  Now under [MYSQLD] section you will want to add default-storage-engine = ndbcluster. This will ensure your new tables get created as ndbcluster type and not MyISAM. 

  10. Restart your MySQL cluster and the mysqld processes on all your MySQL servers.

  11. Create a database with the same name (e.g., drupal6) as the one you exported above with a user with privileges on your Master MySQL database server. If you have Master Master Replication going then the database should copy over automatically. 
  12. Import the database using this command "mysql -h yourserver -u userloginname -p drupal6 < drupal6.sql" where drupal6 is the name of your database. Of course, your database is likley on a different server so that is why you need to replace yoursever and userloginname with your credentials.
  13. Put a copy of Drupal on each Web server you will be using and in your settings.php file put in your database login credentials. E.g. in our case we might make $db_url = "superdrupal:superduperdrupal@mymysqlserver.com/drupal6" 
  14. If all has gone well then your databases should be replicating without a problem. If you have trouble logging in, like I did, it could be cause autocommit was not set to 1 and the database transactions were not getting commited, thus resulting in table lock timeouts and much sadness. Once I set autocommit=1 in the config file I no longer faced this problem. 
  15. This guide is a work in progress, but is very much needed because there are not many good resources out there for how to run Drupal on ndbcluster.

External Resources you may find useful:

High Performance MySQL: Optimization, Backups, Replication, and More 

I found the High Performance book to be really helpful for understanding MySQL.

MySQL Storage Engines

MySQL Cluster

The NDB Storage Engine

Drupal on NDBCluster issue

Kris Buytaert on ndbcluster on Drupal

Limits Associated with Database Objects in MySQL Cluster

Dries Buytaert founder of Drupal on the issue of database replication lag