Grazr.com feed and OPML file hosting system

A week or so ago, I posted a journal entry about MySQL replication titled "Changing hats". The fruit of the effort in setting up this replication system can be seen at http://www.grazr.com. This is a really cool system we built at Grazr provides users free upload and hosting of their own Feeds (RDF, RSS, OPML, ATOM), displaying those feeds into the nifty Grazr widget. I was never a feed junky, but am becoming one! The idea (as I see it), in this information-overflow world, is to organise or distill your information sources. At least one part of my life could certainly use that. Still much more to learn, and Grazr.com also includes a great tutorial about feeds, OPML, and how to use Grazr.

This was a great experience, and I learned more in depth about so many things that I thought I knew about previously - OPML/Feeds, Replication, CSS, Ajax, DOM, et al.

There were a couple hurdles and bumps along the way in implementing this project. First of all, one of the most perplexing issues was that for some reason, one of my master databases suddenly wouldn't let me connect to the db, as if my DB password had been changed. This affected all accounts, including the replication client and web application users. You can guess what this caused. Not to worry, replication worked great in that we were able to point the web servers at the other master/slave pool. In retrospect, if I hadn't been so focused on why the mysql login wasn't working, I could have just pointed the slaves that connected to the first master in question to the second master, but in haste I assumed that the slaves might be too far behind the second master.

To remedy what the problem was on the first master, I first ran myisamchk on the tables in the mysql db (schema), but nothing seemed to be needing repair. I then decided to simply re-run 'mysql_install_db' again to just set the mysql user accounts back to defaults, then loaded the backup from the other master which pretty much has all the same accounts except for itself. I restarted the master and replication magically caught up to where it had last worked on all the slaves and the second master which couldn't connect to the first master.

One of my other problems is that one of my slaves kept crashing, which a message such as:

070307 12:37:19 InnoDB: Error: cannot allocate 268451840 bytes of
InnoDB: memory with malloc! Total allocated memory
InnoDB: by InnoDB 24806280 bytes. Operating system errno: 12
InnoDB: Check if you should increase the swap file or
InnoDB: ulimits of your operating system.
InnoDB: On FreeBSD check you have compiled the OS with
InnoDB: a big enough maximum process size.
InnoDB: Note that in most 32-bit computers the process
InnoDB: memory space is limited to 2 GB or 4 GB.
InnoDB: We keep retrying the allocation for 60 seconds...
InnoDB: Fatal error: cannot allocate the memory for the buffer pool
^G/usr/local/mysql/libexec/mysqld: Out of memory (Needed 237527040 bytes)
^G/usr/local/mysql/libexec/mysqld: Out of memory (Needed 178145280 bytes)
^G/usr/local/mysql/libexec/mysqld: Out of memory (Needed 133607424 bytes)

This slave is the same as all the other slaves both in hardware and db settings. I tried several times to just blow away the innodb tablespace files, reload the schema, restart the slave. It would work for a while then crash again. I just decided to disable innodb on that slave and create all the tables in that schema as MyISAM. I have relegated that box to being flaky and of need of replacement.

This brings to mind the issue of whether I really need InnoDB or MyISAM table types for this application. I have no need of transactions yet. My thought was that maybe some day we will need transactions and if the tables are already InnoDB, it will be easier. But running "ALTER TABLE sometable ENGINE=someEngine" is also quite simple!

As I mentioned in my "Changing Hats" post, this is a completely different role from an internal MySQL developer. There were certainly issues that had to be dealt with in a rapid fashion, but when you have launched a website and something is out of sorts with your db, that problem has to be dealt with immediately - as in past tense, particularly when you are trying to make a good impression of your new product!

One of the things I'm working on now is documenting my setup, things that I learned, and writing some automation tools. Also, I'd like to do something about fail-over. I have set all my slaves to run binary logging so that I could turn any one of them into a master.

I was in the Navy for six years, and one important thing you learn in the Navy is CYA, which is a habit I will never lose!