By tim

Continuing my series of queries about how "Web 2.0" companies used databases, I asked Cal Henderson of Flickr to tell me "how the folksonomy model intersects with the traditional database. How do you manage a tag cloud?"

He replied: "lots of the 'web 2.0' feature set doesn't fit well with traditional normalised db schema design. denormalization (or heavy caching) is the only way to generate a tag cloud in milliseconds for hundereds of millions of tags. you can cache stuff that's slow to generate, but if it's so expensive to generate that you can't ever regenerate that view without pegging a whole database server then it's not going to work."

Here's the full text of my exchange with Cal:

The first question I asked was "what's your …

Jacom Nikom (MIT Lincoln Lab and Boston MySQL Meetup regular) is presenting about performance when processing sensor data stream.

Where does the data come from? Multiple locations, 10 pictures of different kinds of sattelite and telescope equipment. I get the sense that there is a ton of data that is being collected. Sensors send messages to a publish/subscribe middleware which apply algorithms, sends it through a historian and down to MySQL.

The historian creates a thread for each subscription. The historian processes the message and creates an INSERT query. This is happening from many sensors, each operating at as many as 100 messages/second.

When choosing a database they looked at benchmarks, tried to determine performance based on the predicted databases. Wanted to know how MySQL would respond to moving.

Jacob gets into some serious detail about performance on various versions of Linux, hardware, and MySQL. The …

This morning Jay mentioned in the MySQL UC keynote introduction that Beat Vontobel had helped save a woman's life earlier this week.

Without getting a lot of detail the story is that Beat was outside on the sidewalk and witnessed this major accident out on the road. He dropped everything and ran to the car where he checked pulse and breathing and talked to the person as she regained conciousness while the emergency response was on the way. Once they arrived he helped by moving to the back seat of the car and holding the driver's neck straight so the emergency response person could perform checks without risking movement of the spine.

Man, that's remarkable.

Everybody can contribute to the MySQL Open Source Community. Here are some ideas:

• You can start with completely a MySQL Conference Survey form (those forms that we are all avoiding).
• You can download, evaluate and test newer versions of MySQL Products.
• You can contribute to the MySQL Forums to ask questions and even give answers.
• Log Bugs & Enhancements on MySQL Products.
• Write about your experiences with MySQL in a Blog and add your Blog to PlanetMySQL. With now over 100 feeds, if everybody wrote something about MySQL in the next year, we would have over 1000 feeds next conference.
• Contribute your thoughts (via wiki), MySQL products, code snippets and …

Brian Aker — Director of Architecture

MySQL Server
SQL-Based, aiming to be SQL-03 compliant. Stable, scalable, easy to use, modular, high-performance RDBMS.

Client Library Support
Libmysql c-library (think OCI)
JDBC — type IV
ODBC
Perl DBD::DBI
PHP (built in)
ADO.Net, OleDB, Ruby, Erlang, Eiffel, Smalltalk and more provided by third parties.

So great because you can connect in many ways to different storage engines
Architecture

sql/ is for kernel
mysys/ is the portable runtime library. MySQL is ported to 52 platforms
(was 53 but Brian deleted OS/2 last week ). The portable runtime library wraps commands like pwrite, unlinking and renaming files so that operating system doesn’t matter.
mysql-test/ is for your test cases — run “mysql testrun –record”. Can take SQL from web …

I am sitting in Dorion's session about scaling the real time web. Technorati tags grew from 0 to 100 million in a year.

Technorati has about 10TB of core data in MySQL over about 20 machines and they use replication. With replication they add 100TB and 200 machines more. Currently growing at about 1TB per day in total.

A service oriented architecture to separate physical and logical access is used. Technorati uses commodity hardware and Open Source software.

Scaling Technorati Tags : Launched on Janurary 10 of 2005.

Tags are partitioned by entity (tags and posttags). Tags database is separate. Post information in one set of databases and tags information in another set of databases. A caching layer is also present. Overtime Technorati has blended the use of InnoDB and MyISAM based on use.

Currently around 120 million tags in a single table distributed using replication. …

Second keynote of Thursday morning (last day) at MySQL UC 2006 is Mitch Kapor talking about the Wikipedia. Mitch starts with a survey of the crowd. Most people in the crowd use the wikipedia, 15% of the crowd edits the wikipedia.

The typical response from a person that doesn't know about the wikipedia says "The Wikipedia can't possibly work..."

but it does

The mainstream media is very skeptical. Anyone can edit any article at any time. This radical openness is more open than open source projects. There is no review and anyone can post. The radical openness provides opportunities that have never been tried before.

Ideas that are challeneged by the Wikipedia:

• 
  
• someone has to be in charge - even the sysadmins are volunteer and do not keep a …

by Mitch Kapor

Wikipedia uses MySQL as their backend. Wikipedia is known among geeks, but hasn’t quite hit society at large, but probably will soon. What lessons can we learn from Wikipedia? People who hear about the concept of wikipedia say “It can’t possibly work — an encyclopedia written by volunteers, that is completely open?”

But we know it does. It’s increasingly becoming the web page of choice for a wide range of factual topics. [as a side note, I go there when I hear of something and know nothing about it, and I get a good overview]

The mainstream media has been skeptical about Wikipedia, and makes stories about it. People sometimes will put untrue facts in Wikipedia, so mainstream media siezes on it as proof that Wikipedia doesn’t work. But we know that that is the exception, not the rule.

So how and why does it work, if it’s so counter-intuitive? Most people have erroneous assumptions …

Hey, hey, this is well worth a read: MySQL to Offer Linux Bundled with Database as Free Download.

So, the MySQL Forge has been announce and has been live for a while… Even the Forge Wiki has content that isn’t created by me! Ha, the community at work…

And Ubuntu, now supported by MySQL. There’s a relationship there. Mark Shuttleworth gave a great keynote, talked about the relationship with upstreams, the new MySQL relationship they have, and how he was really excited to be here. Thawte I believe, ran on MySQL :-)

If there’s one thing to take away from his talk it’d have to be the fact that he’s also a VC and a lot of people ask him for money. Don’t go to a VC and tell them, “Hey, I’ve got Project Y, and …

“No, you can’t run a Migration BoF, and it won’t make sense without a few of our migration experts,” he exclaimed with a petulant expression.

Well, it turns out the migration BoF was actually quite good at the MySQL UC. It turns out, that everyone attending thought that it was a migration from 3.23 or 4 or 4.1 to MySQL 5. My understanding of this was that we’d actually prefer folk to come in from $database and figure they’d want to migrate to MySQL.

And fair enough, the only experience I’ve had over time was migrating from Postgresql right to MySQL. But the idea of a BoF is to let everyone talk, share experiences, and so on. So a few resources we discussed, and I think were generally useful (and hopefully now, there will be more 5.0 users):

• Upgrading from MySQL 4.1 to 5.0 - from our great …