Archive for the ‘MySQL’ Category

MySQL proliferation

Long time no post but thats some stuff lingering in my head for a while looking at the mysql ecosystem lately.

For a long time I had to stay with MySQL-4.0 (sick I know) but there’s a lot of software to adapt to the new version(s) but lately I’m pushing it more and more into mysql 5.0.
MySQL 5.1 would be also interesting and even MySQL-5.4 … but thats where the trouble starts.
MySQL-5.4 came out of the dark, no one expected it and it gave numerous improvements … though its still in beta.
With that release (or at least around this time) MySQL started to change its release model to something new where the version numbers matter far less and where there should be regularly released. Well, Oracle has bought Sun which owns MySQL … so we will see what the next “release model” will be.

On the other hand there are some “forks” of mysql out there which offer more improvements or at least they are supposed to do this.
For one there is XtraDB which is supposed to be just a replacement for the InnoDB-plugin now … while it had more patches to the main mysql server before as far as I remember – so its not really a fork, just another storage engine.
Edit: just found a newer release – its called “MySQL with Percona patches”

Then there is MariaDB which should be a “… community developed branch of the MySQL database that uses the Maria engine by default.” (quote from the linked page) which is developed by Monty Program AB and the OpenDatabaseAlliance.

Being “only” a collection of patches and builds of MySQL with patches is OurDelta.
I’m pretty sure that there are more forks or patch collections out there, please forgive me if yours isn’t listed.

But here is the question. Which MySQL version / patchset / fork should one use?
Previously it was just the question of using the commercial enterprise or the community version. Now I’m just confused.

Next problem is the distribution on linux …
Nearly all my servers are using Gentoo as the linux distribution but have you ever looked at the status of mysql in there?
The latest “stable” version is dev-db/mysql-5.0.70. dev-db/mysql-community-5.1.21_beta is in there, but not marked as stable (5.1.39 being the last one on the mysql homepage), 5.4.x is nowhere to be seen, same for XtraDB, MariaDB … . I don’t blame it on the maintainers – who should keep up with that flow of releases, different branches and/or forks. I also heard rumours that all the latest versions are failing numerous tests for the maintainer(s) and therefore won’t go in.
If I want to use a recent stable version or one of the enhancements I will have to do my own builds instead of using the great gentoo infrastructure for that. I could, sure, but time is low and I would get far further if I could use the regular way to install (and update) software on Gentoo. I don’t have a full blown infrastructure team to keep the systems going.

If I wouldn’t have too many software using raw database calls in php or perl I would seriously consider switching to some other database, PostgreSQL or the like. I heard even Maurice considering switching to Postgresql in the future and supporting the Postgresql layer for Phorum.

If there is no one stopping this proliferation of the mysql ecosystem and providing reliable and regular stable builds of a tree, I think Oracle won’t have to worry about MySQL anymore. There will be not too much left of its userbase and community.
But maybe its just me, painting things black, who knows ;-).

The next steps for Phorum

Now that Phorum-5.2 has finally gone stable there will be hopefully some better modules as the possibilities have been vastly increased. One of the new modules for 5.2 which show quite some of the abilities is the rewritten user-avatar module for 5.2.
With modules you can use now (not everything is new in 5.2!!!):

  • use a supported API for files, users, custom profile fields and similar stuff
  • ability to hook the module-css into the css loaded by phorum for valid (x)html pages and not loading it separately (saving requests)
  • ability to hook the module-javascript into the javascript loaded by phorum for … see above 😉
    (both can use raw files, templates, functions for including it)
  • can do database calls without writing database dependent code (could still be because of the queries themselves)
  • use module-templates which are included in the module itself, no need to copy them to the template folder(s)
  • language files in the modules themselfes
  • adding controlcenter panels without copying files around

Also our module list for 5.2 is now auto-generated from the modules posted into the 5.2-modules forum in the right format.
Make sure to add categories too as listed in the docs!

So, now that we (could) have better modules, whats next?

Dan Langille has been working on a postgresql-layer for 5.2/5.3 which will probably be included in one of the later 5.2 release as a beta of this layer.
The next big release will be Phorum-5.3. Our plan for Phorum-5.3 is “just” to add even more APIs, changing large parts of the backend without touching much of the frontend code.
Therefore templates from 5.2 should work without a hitch with 5.3. Maybe there will be added features missing in the old template but otherwise it will continue to work as before.
I know we made it hard for some admins with the switch from 5.0 to 5.1 and 5.1 to 5.2 but all these changes were done for flexibility in the templates and making them far more consistent and therefore easier to implement.
Some of the APIs will be about forum handling and similar stuff so that you can build a new admin or an admin in another page far easier than before.
As usual you can see the tickets on the table for 5.3 in our ticket-list (from the 5.3-milestone).

And further in the future?
There is lots and lots of stuff in the ever growing Ideas-milestone.
We’ll see if any of this will see the light in 5.3 already or in a later release but we surely won’t get bored ;-).
I’m pretty sure that lots of stuff will be done at the MySQL Conference 2008 like last year where we’ve been coding and presenting there with lots of feature tickets closed for 5.2. You can help us to get there with donating to!

building a HA/LB solution

I’m currently in the process of trying to build a HA/LB solution for my forums.
Currently HA (HighAvailability) is created by running heartbeat on the two webserver-“nodes” with automatic ip-takeover and a mysql-slave which gets all the data from the main-db-server (but needs manual takeover).
LB (LoadBalancing) is done with FastCGI-Loadbalancing in LiteSpeed-Webserver but I’m not satisfied with the results as it seems that the first host is getting much more load than the second one.

Therefore I played with some Virtual Machines, one running haproxy ( ), two running lighttpd with fcgi-php.
So far it worked good but taking down one of the webservers still gave me some failed requests if it was running under “siege”. Thats something I wanted to avoid.
Lighttpd was simply choosen because of mod_extforward so that I could keep the original hosts ip in the REMOTE_ADDR and its support for fcgi-php.

But as I wrote in an earlier post there is one feature I’d badly miss in lighttpd and which really keeps me from switching:
.htaccess-support or generally spoken: dynamic configuration changes without changing the main-configuration and the need for a webserver reload.
I found one thread in the lighttpd-forums which sounds promising.
Reading dynamic configuration from mysql is something I’d love to see. It would kick ass :).
Yeah, sure. Lighttpd would have to work without mysql-connection too, some fallback mechanism needs to be in place but that would solve at least most of my problems.
For my own DoS functionality I need a way to block connections on the webserver-level before it even reaches PHP.

So there are some problems or lets better call it “tasks” left to solve for my HA/LB solution:
– find the right webserver to implement that
– build a solution to merge the logs and process them for statistics
– find out how to get haproxy (or another loadbalancing solution) to send failed requests to another backend in case of one going down

And the big task:
– find some automatic solution for mysql-takeover (without DRBD, which I don’t trust because of its network-based nature ;))
Any ideas anyone?

Charset hell (and mysql-upgrades)

Oh yeah, some people will remember me talking about charset hell when it wasn’t really that bad (yet).
But in the near future I will surely have to solve some charset problems for
It is still running MySQL-4.0.x which didn’t support charsets like MySQL-4.1 did and even more MySQL-5 is doing now.
But as MySQL-4.0 support has run out I really need to upgrade soon and then I’ll probably find myself in a lot of charset troubles ;).
First one will start with the upgrade itself. How will it look like after the upgrade? Default charset will probably be latin1 so it *should* be fine. But will it?
Beside that this upgrade is already some trouble as I will need to dump/restore the whole databases which are quite some GB’s and I hate to take the services down for some hours.

For now I’m finding enough excuses like I need to wait for Phorum-5.2 until I can upgrade but in the end I can’t do it all at once.
I still hope that Phorum-5.2 will still run on MySQL4 so that I can upgrade Phorum first and then convert to MySQL5 (for now I see no problems with that).
But Brian has announced that he doesn’t care about PHP4 and MySQL4 either as we are all developing on MySQL5 and PHP5.2 (which is correct, my development environment is like that too) – we’ll see what I can do about it ;).

Seems like I’m running a really explosive mixture with PHP4.4.x, MySQL4.0.x on my production boxes and doing development mostly on PHP-5.2.x and MySQL5.0.x.
Fortunately it seems like Phorum itself is not as vulnerable to changes in PHP itself as other apps because of its NON-OO-code and up to now we had always MySQL4 in mind as it was the requirement for Phorum-5.1 which is the current stable version.

Somewhen I’ll run into walls, thats for sure …