Finally, episode 5 is here. In this episode, the first part in a two-part series about Falcon, the new storage engine provided by MySQL, we talk about what happens when you query a Falcon table, going over and explaining MVCC and the record cache. Next episode will go over the serial logs and indexes.

Direct play episode 5 at:
http://technocation.org/content/oursql-episode-5%3A-falcon%2C-part-1-0

Subscribe to the podcast by clicking:
http://phobos.apple.com/WebObjects/MZStore.woa/wa/viewPodcast?id=206806301

You can Direct download all the oursql podcasts at:
http://technocation.org/podcasts/oursql/

Jim Starkey is a great speaker, and very funny to listen to – at one point he refers to a performance hit as a “performance surprise”. He's also a piece of history – listen for his take on MVCC, which he invented.

Links:
Falcon features:
http://dev.mysql.com/doc/falcon/en/se-falcon-features.html

Falcon documentation
http://www.mysql.org/doc/refman/5.1/en/se-falcon.html

Special thanks to Arjen Lentz (http://arjen-lentz.livejournal.com/) and Mark Matthews (http://www.jroller.com/page/mmatthews ) of MySQL AB for their answers and explanations, and Jim Starkey and Technocation for their use of the audio from the July 2006 Boston User Group meeting with Jim Starkey.

So I've been looking into the Archive Storage Engine. What I would really like to do with it is get data in realtime, because (of course) the higher-ups want reports on realtime data -- that is, they are not satisfied with a report that is run regularly, they want all the data up until "now".

It is inadvisable to replicate from one storage engine type to another. I have not yet played with it, but since an Archive table does not allow updates and deletes, replicating from a MyISAM or InnoDB table to an Archive one is a bad idea.

Most folks probably run a batch job; but I wonder if it can be done in real-time. Or rather, 'what is the best way to run it real-time?' One way, off the top of my head, is to do this are to replicate to a blackhole table with a trigger, to insert into an archive table whenever an INSERT statement is called. The blackhole table should not give an error upon UPDATE or DELETE statements.

This also allows for easy aggregation, because the trigger can say "update the count and the country of a new profile" instead of having an entire replicated set of data, with reports running "SELECT count(*)". Instead of copying all the data and running the same reports on a different server/table, we can now collect the data we actually want, which is "1 new paid membership at time t located in Sao Paulo, Brazil." For reporting, we do not care what the name of the member is.

I have searched around but have not yet found how users are getting data into their archived databases. I need a sandbox server at work so I can play with the options.