MySQL Cluster Configuration, Tuning, and Maintenance

Today is the 1st day of MySQL UC and I’m sitting in on the clustering tutorial, which promises to be three hours of information on MySQL’s clustering feature. I’m sure you’re thrilled that I’m, ahem, live blogging this. In reality, this is just so I have a central place for my notes.

  1. Broken up into three parts. The MySQL servers sit separate from the NDB Storage Engine, which are storage nodes (NDB nodes). The third part is called a management server. The management server, oddly enough, isn’t required once the cluster is up and running unless you want to add another storage node.
  2. Memory based storage engine. If you’re not using 5.1+ then you must have enough RAM in each storage node to store the data set. This means that if you have four machines with 4GB of RAM each you can store 8GB of data (16GB of total storage divided by two for two copies of the data set).
  3. Storage nodes are static and pre-allocate resources on startup.
  4. Supports transactions and row level locking.
  5. Should be noted this is a storage engine so you can’t create MyISAM or InnoDB tables inside of a cluster.
  6. Uses fixed sized records. This means if you have a varchar(255) and put a single byte into it that field is still using 255 bytes.
  7. No foreign key constraint support.
  8. Replication across nodes is syncronous across nodes. I assume this means that an INSERT happens once all of the nodes have completed the INSERT. This is different than regular replication which is asyncronous and introduces race conditions.
  9. Tables are divided into fragments (one fragment for each storage node). Each storage node is responsible for each fragment. Each fragment also has a secondary fragment, which is a copy of another node’s primary fragment. This data distribution happens automatically.
  10. NDB takes your primary key, creates a hash and then converts that to a fragment. So you’ll have various rows on each different storage node.
  11. A node group is a set of nodes that share the same fragment information. If you lose an entire node group you’ve lost half of the table and the cluster will not continue to operate. However, if one node in a node group fails there will still be enough data to keep the cluster up and running.
  12. If a node fails and it’s secondary counterpart takes over it will, essentially, have to perform the job of two nodes. Until a node has fully recovered it will not rejoin the cluster.
  13. Backups are hot and non-locking. Each node writes its own set of backup files. No support for incremental backups.
  14. Because it’s memory based you could lose data on a system crash (as you might have transactions sitting in RAM when a crash occurs). The COMMIT command does not write changes to disk meaning that you could have data sitting in memory that’s not on disk when a node crashes. This means the odd truth is that MySQL Clusters support syncronous replication, but are not atomic.
  15. NDB nodes will checkpoint data to disk (data + logs), which are used for system recovery. They write two logs, the UNDO and REDO logs.
  16. They recommend using TRUNCATE to delete all rows from a table.
  17. Modification operations are distributed to both the primary and secondary fragments (obviously).
  18. NDB will run on 64-bit machines. They recommend Dual CPU 64-bit machines. NDB is threaded. Application nodes (MySQL servers) can be whatever.
  19. SCI offers 30-100% better performance over gigabit.
  20. They actually recommend avoiding joins and to denormalize your schemas. Are you kidding me? He actually said “Performance for joins sucks.”

Overall, I’m underwhelmed by MySQL Clustering. You’re limited in storage with the RAM and you can’t optimize your schemas due to fixed field sizes. And any RDBMS “solution” that recommends you denormalize puts me off.

That being said the actual technology is pretty interesting and I suspect that in a few years we’ll see the clustering features in MySQL come into their own. As of now I suspect few people would be able to justify the sacrifices for the gains clustering allows.

Mt. Baker

Over a few pints at a pub in Ballard on Friday night with Garren, Julia and Sanna Garren and I had the bright idea to head up to Bellingham on Saturday night and then go skiing/snowboarding on Mt. Baker all day Sunday for one last day on the slopes.

As it turns out the stars were perfectly aligned to not only make this trip happen, but to make it one of the best trips in recent memory. One of those trips where everything falls into place perfectly.

Garren and I headed out of Seattle around 7PM. About 15 miles outside of Bellingham is the Skagit Casino which, after some discussion and a quick turnaround, we went to in search of cheap Craps. Cheap craps we found with $3 tables and 10x odds. Something you won’t find in even the cheapest Vegas casinos. It was here that Garren quite possibly had one of his luckiest runs at the table. The luck appeared to spread as multiple shooters scored multiple points over the next four hours. Always nice when you walk out with more money than you walked in with at a Casino.

The next day the stars again aligned in our favor as we wound up in about 2 feet of fresh snow and very little competition for fresh tracks. I readily admit I was doing things I had absolutely no business doing on the mountain. Finding myself in the trees up to my waist in powder on multiple occasions giggling like a school girl.

My first WordPress plugin

So I finally broke down today and started working on my first WordPress plugin. I liked how recent links worked, but I wanted an aggregated list of both links I found interesting and my Flickr photos. I was going to hack the recent links plugin that I had used, but decided against it in the end. In the end I decided that it made sense to store my interesting links on del.icio.us and my photos on Flickr and then aggregate them into a single list, which is what you see now below this post.

This way my photos stay on Flickr and my links stay on del.icio.us, but they show up inline on my blog’s frontpage. I’ve got a few bugs and kinks to work out still, but it’s definitely ready for beta testing. If you are interested in playing with it then give me a shout and I’ll package it up for you to test out.

  • Integrates with Flickr and stores photo and tag information into a MySQL table.
  • Integrates with del.icio.us and stores link and tag information into a MySQL table.
  • Uses PEAR’s DB and HTML_Request packages and PHP5’s SimpleXML extension to seemlessly fetch and cache new content every hour or every time an admin visits the website.

General of 82nd Airborne Division wants Rumsfeld out

There is a growing chorus to oust Rumsfeld and it’s coming from a large group of retired generals. Possibly the funniest part of this ongoing story is the White House’s response.

The White House has defended Rumsfeld, saying he is “doing a very fine job.”

Who is in a better position to critique how well Rumsfeld is performing as the leader of the Department of Defense? A bunch of policy makers who’ve never fired a gun in their lives or the leader of one of the most elite fighting forces in the world who’s been fighting wars for 30 years?

Dear Tech Support

Dear Tech Support,

Last year I upgraded from Girlfriend 7.0 to Wife 1.0. I soon noticed that the new program began unexpected child processing that took up a lot of space and valuable resources. In addition, Wife 1.0 installed itself into all other programs and now monitors all other system activity.

Applications such as Poker Night 10.3, Football 5.0, Hunting and Fishing 7.5, and Racing 3.6 I can’t seem to keep Wife 1.0 in the background while attempting to run my favorite applications. I’m thinking about going back to Girlfriend 7.0, but the uninstall doesn’t work on Wife 1.0. Please help!

Thanks,

A Troubled User

Dear Troubled User,

This is a very common problem that men complain about.

Many people upgrade from Girlfriend 7.0 to Wife 1.0, thinking that it is just a Utilities and Entertainment program. Wife 1.0 is an OPERATING SYSTEM and is designed by its Creator to run EVERYTHING!!! It is also impossible to delete Wife 1.0 and to return to Girlfriend 7.0. It is impossible to uninstall, or purge the program files from the system once installed.

You cannot go back to Girlfriend 7.0 because Wife 1.0 is designed to not allow this. Look in your Wife 1.0 manual under Warnings-Alimony-Child Support. I recommend that you keep Wife 1.0 and work on improving the situation. I suggest installing the background application “Yes Dear” to alleviate software augmentation.

The best course of action is to enter the command C APOLOGIZE because ultimately you will have to give the APOLOGIZE command before the system will return to normal anyway. Wife 1.0 is a great program, but it tends to be very high maintenance. Wife 1.0 comes with several support programs, such as Clean and Sweep 3.0, Cook It 1.5 and Do Bills 4.2.

However, be very careful how you use these programs. Improper use will cause the system to launch the program Nag Nag 9.5. Once this happens, the only way to improve the performance of Wife 1.0 is to purchase additional software. I recommend Flowers 2.1 and Diamonds 5.0! WARNING!!! DO NOT, under any circumstances, install Secretary With Short Skirt 3.3. This application is not supported by Wife 1.0 and will cause irreversible damage to the operating system.

Best of luck,

Tech Support

Migration to WordPress

I’ve begun the progress of moving to WordPress. The process began by migrating data from my old blog into the WordPress tables. After that I had to create a plugin that would adequately translate my old posts into the new WordPress posts. Now that that is all done I’ve started working on a new design for my site. It’s been about two years since I last refreshed the design so it’s definitely time for a change around here.

WordPress, so far, isn’t difficult to work with per se, but the code is painful to look at. Not that there aren’t good things about WordPress. The following is a list of things I both like and dislike about my new home.

  • The code, as I already mentioned, is painful. There’s barely a trace of any kind of coding standards within the core code. Some of it is OOP, while other parts are procedural. To top this off; none of it appears to be documented.
  • The plugins and theme engines are fairly well laid out, documented and seamless. Within minutes I had created, activated and started using my own plugin. Very little programming knowledge is needed to get started, which would explain why there are thousands of WordPress plugins.
  • The database layout doesn’t use InnoDB, hence no foreign key constraints, nor does some of the field layout make much sense. That being said, it’s not horrible. The most annoying thing I found while migrating my old entries was that WordPress caches comment and post counts for posts and categories. So, despite a post have 3 comments, WordPress would show 0 comments.
  • Making WordPress sing with mod_rewrite was a snap.
  • Tags aren’t build in. What the hell?
  • The administrative part of the site is, quite simply, a work of art. I love it.
  • I’m currently using three plugins: the one that translates old joestump.net content into WordPress content, wp-recent-links and Spam Karma 2.2.
  • What you are looking at is not the final design.

So why did I convert to WordPress from my home grown code? Well, to put it simply, WordPress has all the bells and whistles that I simply don’t have the time to program. Another issue was that maintaining the old blog was a huge pain and I’m hoping by converting to WordPress it will directly lead to me blogging more frequently.