Running Drupal in a MySQL Cluster (ndbcluster)

 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

 

Comments

testing ndbcluster with drupal

I guess you have been running Drupal with ndbcluster for almost 10 month now. Any other issues? is the replication running smoothly?

I'm considering Drupal+ndbcluster for my next project. However I did not have multiple server to conduct real simulation.

Thanks for the guide by the way.

So far we have not had any

So far we have not had any problems running ndbcluster other then that we will see a warning on occasion from the profile module regarding trying to create a duplicate profile id and I think this may be due to the ndbcluster replication. The error looks like:

Duplicate entry '1722-1' for key 'PRIMARY' query: INSERT INTO profile_values (fid, uid, value) VALUES (1, 1722, 'redacted') in /modules/profile/profile.module on line 244.

But it doesn't cause any issues. So, I would say we have been quite happy with ndbcluster.

Well we finally had our first

Well we finally had our first outage and it was do to the locale_sources table.

So if you use locale, pay attention here.

Locale_sources can grow so unwieldy that MySQL will choke on it, at least when installed as I mentioned above. Basic steps to fix is, disable and uninstall locale (you will probably need to kill Apache to do so, and maybe need to restart MySQL in order to be able to get in to turn it off).

Then, simply re-enable locale, and re-create any languages that were available. The po files should automatically get re-imported and you should be good to go after that.

New release 7.2.0 (developement milestone)

Hi,

We're releasing a development milestone version 7.2.0 which includes the "join pushdown"-feature...
Testing if this benefits drupal has been on our wish list a long time.

If you want to test, and report good/bad it would be great!

7.2.0 should be released this month.

/Jonas

Awesome, thanks for coming to

Awesome, thanks for coming to this little piece of the internet and letting us know about this. I found your presentation really helpful for explaining the benefits (mentioned in your blog post at http://jonasoreland.blogspot.com/2010/04/distributed-push-down-joins-fir... ) Now, if I can get our sysadmin to put the new version up on test, we should be able to run some tests if this solves the slow join problem with 'locale' in Drupal. (Well and theoretically speed up all the places Drupal does a lot of join queries, which is fairly common in the code).

Performance Benchmarking

Have you done any benchmarking to compare performance of drupal with ndbcluster vs. standard mysql?
I think it would make sense if we know how much benefits would be before we switching from standard version to ndbcluster.

Thanks!

You know, we did some

You know, we did some benchmarking, but I don't have the numbers sitting around anymore (this was a couple years back). Long story short, it was slower, but not by much. Check out this pdf from Oracle on performance tuning ndbcluster though http://www.severalnines.com/downloads/presentations/MySQL_UC2010_Cluster...

Add new comment

  • No HTML tags allowed.
  • Web page addresses and e-mail addresses turn into links automatically.
  • Lines and paragraphs break automatically.

Comment using an existing account (Google, Twitter, etc.)