If you haven’t taken the opportunity to book a meeting with me in Singapore yet, I suggest coming to the MySQL Meetup that’s happening there on Thursday. More information at the meetup site, but the key bits are:

When?

Thursday, Jul 5, 2007, 7:00 PM

Where?

Delifrance HARBOURFRONT CENTRE 1 MARITIME SQUARE #01-15 WORLD TRADE CENTRE Singapore

I understand the meetup group has been dormant for a couple of years. It’ll be good to re-jig it. I most likely will be very unreachable via email (though I’m assured that there’s some cool Wireless@SG thing happening in Singapore that will give me Internet access even if the hotel doesn’t), so consider calling +6-012-204-3201.

Technorati Tags: mysql, …

Over the weekend we migrated one of our tables from MyISAM to InnoDB. Generally speaking we’re pretty happy with most of our InnoDB migrations.

InnoDB generally uses 2-3x more data on disk but the write ahead log (WAL) buys us so much more added performance that it’s generally worth the switch.

This migration however was a bit more problematic. Importing the data from mysqldump as MyISAM into the new box only took about 2 hours. Converting the data to InnoDB has so far taken 24 hours and using 21G vs the original 6.1G for MyISAM.

I should also note that it’s not complete yet and it’s 3.5x larger than our original MyISAM install.

Clearly the performance will be worse in our situation for InnoDB because I don’t think the buffer pool efficiency will make the write ahead logging fast enough to beat MyISAM on the same hardware.

We’re not the only people who have …

MySQL AB today announced that its database helps power Qype, the popular German multi-city magazine and review portal. The MySQL database gives Qype users quick and easy access to the addresses and GPS coordinates of places of interest in more than 2,200 cities and towns -- along with user reviews on them.

So I have mentioned the BLOB Streaming Project quite a lot lately, and for good reason! Paul has recently released an alpha version of the engine itself, called MyBS. I took some time last weekend to benchmark it's performance and thought I would share my result. Keep in mind, these are rather crude, but I think they do get the point across.

For the tests I used a slightly modified version StuffDawg since it already stores images inside the database. Oddly enough, I found a few bugs with StuffDawg that I was able to patch up and will be releasing an update soon.

So before I explain things, here is the juicy stuff - the results:

tim@filedawg ~/tmp $ time wget --quiet http://stuffdawg/image.php?itemid=20   

real    0m0.767s
user    0m0.000s
sys     0m0.012s
tim@filedawg ~/tmp $ time wget --quiet …

Sometime in the next six to 12 months a bad guy will use one with its
web interface to trigger a bomb.

I would also place odds that within the next 12 to 24 months, some
kid will hack an alien ship to stop an invasion of earth with an iPhone.

Movies count.

And here you only thought you only read my blog for advice on
databases and hacking right?

Saw the latest Die Hard film today... it was good... but very painful...

I just noticed PeterZ's post on page counters:
http://www.mysqlperformanceblog.com/2007/07/01/implementing-efficient-counters-with-mysql/

Let me offer a different solution.

Do not do it.

Really, if you grow a site to any size at all you will discover that
the contention you create on updates is just too much. Sure, you can
optimize it by have the update done on a de-normalized table, but
unless you are using 5.1 with its row level replication, eventually
the replication locks will nail you.

Instead build an accesslog type table. You probably already have one,
aka you are recording your accesslog in your database. Just run a job
every so often that updates the counter on your object in its own
table (which will …

Last week The Open Road caught up with Justin Steinman @ Novell and Mike Olson @ Oracle to discover how open source factors into these companies' businesses. This time, we're switching gears a bit to talk with a company that sells services around software - both open source and proprietary - rather than a software company.

Being familiar with the interesting open source work happening at SAIC, I decided to talk with two members of its Open Source Community of Practice: Ryan Brunton, a developer within SAIC's Open Source Community of Practice, and Wayne Waddoups, vice president of Strategy, SAIC Office of Technology. SAIC has long worked with projects like Linux and MySQL, but it's the cutting edge work it's doing with open source applications and infrastructure that caught my eye. …

Glyn Moody has an excellent article in Redmond Magazine on open source and interoperability. As it turns out, sometimes it takes Microsoft to notify the open-source community that for all the great things we've done, we sometimes fall short. One area that open source had traditionally failed in was in stitching together an end-to-end solution, as Nick McGrath (a friend and a wonderful person) suggests:

Of all the accusations Microsoft has leveled over the years against open source, perhaps the least contentious is that it lacks the tight integration offered by Microsoft's own products. As Nick McGrath, director of platform strategy for Microsoft in the United Kingdom, puts it: "One of the problems I've seen with open-source software is it doesn't take on board some of the issues that customers have around interoperability and integration. …

When seeing that the MySQL Proxy was released, I decided to try to experiment with it since I see strong potential with this tool, both for replication and for other uses (recall that I'm a replication guy, so this is my primary focus). I'm actually on vacation, but this will of course not stop me from tinkering with things (I know, I'm just a hopeless case in this aspect ;) ).

After reporting a minor bug, I managed to build and run it with some sample scripts. I'm using Kubuntu Feisty, and had some initial problems, but it was actually pretty straightforward. I'll repeat the steps anyway, in case anybody else have problems.

1. Get the source from the repository svn co http://svn.mysql.com/svnpublic/mysql-proxy/ mysql-proxy
2. Make sure you have all packages necessary. Several of the packages …

On many web sites you would see a counter how many time given object - blog post, forum thread, image, movie etc was viewed. This is sometimes handy feature but it can be rather expensive from performance point of view.

The nasty thing with counters as they are implemented the most trivial way - they convert read load to write load. When you would simply fetch given object information now you do not only fetch the data but also update the view counter.

For smaller single system web site with no caching the overhead may well be insignificant - as you run update for the same row which just was fetched it does not cause any synchronous IO and background IO can be batched rather effectively.

As the system growths however and you implement some form of caching, ie memcache you end up reducing number of reads from database dramatically but writes still have to happen at full speed. It also really hurts if you choose replication …