Category Archives: Routines

Private: OurSQL Episode 6: Falcon, part 2

In this episode, the second part in a two-part series about Falcon, the new storage engine provided by MySQL, we talk about what happens when commit, going over and explaining the serial log and indexes.

Direct play episode 6 at:
http://technocation.org/content/oursql-episode-6%3A-falcon%2C-part-2-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/

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.

Acknowledgements

http://www.technocation.org

http://music.podshow.com

http://www.russellwolff.com

http://www.smallfishadventures.com/Home.html “The Thank you song” — Smallfish

Feedback

If you have any feedback about this podcast, or want to suggest topics to cover in future podcasts, please email

podcast@technocation.org

You can also:

Call the comment line at +1 617-674-2369

Or use Odeo to leave a voice mail through your computer:
http://odeo.com/sendmeamessage/Sheeri

Or use the Technocation forums:
http://technocation.org/forum

OurSQL Episode 5: Falcon, Part 1

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.

Archive Storage Engine and Reporting

To contact me via e-mail:

awfief@gmail.com
To contact me via e-mail:

awfief@gmail.com
To contact me via e-mail:

awfief@gmail.com
Having used Oracle, DB2, Postgres, Sybase, Informix, and MSSQL, I always enjoyed that MySQL just named everything “MySQL”. Sure, it can get confusing — there’s MySQL the server, MySQL the client, MySQL the database instance. . . .MySQL the flamethrower (the kids love this one). . . .But seriously, the ‘big guys’ have all this complicated jargon for really simple ideas.

MySQL has joined them. Granted, I’d been out of the MySQL world for about a year, and some wonderful things have happened in that year. Even a year ago, the company I worked for wasn’t using the most recent software nor taking advantage of all the features their versions of MySQL did have to offer. But I digress.

I’ve been working on MySQL knowledge, particularly with the free webinars. Today I attended the “MySQL Network and MySQL 5.0” webinar, where I learned that MySQL is packaging (better) software, support, tools, access to developers, and a knowledgebase into what they call “MySQL Network.” I was completely unclear on the concept of MySQL Network from the description, and from the name I figured it would have something to do with technical networking, not business to business networking.

Meanwhile, yesterday I realized that the “Pluggable Storage Engines” in MySQL just mean “you can use different table types, and turn off the ones you don’t want to use.” I was familiar with the concept, but not the buzz-phrase used to describe it.
To contact me via e-mail:

awfief@gmail.com
To contact me via e-mail:

awfief@gmail.com
Having used Oracle, DB2, Postgres, Sybase, Informix, and MSSQL, I always enjoyed that MySQL just named everything “MySQL”. Sure, it can get confusing — there’s MySQL the server, MySQL the client, MySQL the database instance. . . .MySQL the flamethrower (the kids love this one). . . .But seriously, the ‘big guys’ have all this complicated jargon for really simple ideas.

MySQL has joined them. Granted, I’d been out of the MySQL world for about a year, and some wonderful things have happened in that year. Even a year ago, the company I worked for wasn’t using the most recent software nor taking advantage of all the features their versions of MySQL did have to offer. But I digress.

I’ve been working on MySQL knowledge, particularly with the free webinars. Today I attended the “MySQL Network and MySQL 5.0” webinar, where I learned that MySQL is packaging (better) software, support, tools, access to developers, and a knowledgebase into what they call “MySQL Network.” I was completely unclear on the concept of MySQL Network from the description, and from the name I figured it would have something to do with technical networking, not business to business networking.

Meanwhile, yesterday I realized that the “Pluggable Storage Engines” in MySQL just mean “you can use different table types, and turn off the ones you don’t want to use.” I was familiar with the concept, but not the buzz-phrase used to describe it.
To contact me via e-mail:

awfief@gmail.com
Having used Oracle, DB2, Postgres, Sybase, Informix, and MSSQL, I always enjoyed that MySQL just named everything “MySQL”. Sure, it can get confusing — there’s MySQL the server, MySQL the client, MySQL the database instance. . . .MySQL the flamethrower (the kids love this one). . . .But seriously, the ‘big guys’ have all this complicated jargon for really simple ideas.

MySQL has joined them. Granted, I’d been out of the MySQL world for about a year, and some wonderful things have happened in that year. Even a year ago, the company I worked for wasn’t using the most recent software nor taking advantage of all the features their versions of MySQL did have to offer. But I digress.

I’ve been working on MySQL knowledge, particularly with the free webinars. Today I attended the “MySQL Network and MySQL 5.0” webinar, where I learned that MySQL is packaging (better) software, support, tools, access to developers, and a knowledgebase into what they call “MySQL Network.” I was completely unclear on the concept of MySQL Network from the description, and from the name I figured it would have something to do with technical networking, not business to business networking.

Meanwhile, yesterday I realized that the “Pluggable Storage Engines” in MySQL just mean “you can use different table types, and turn off the ones you don’t want to use.” I was familiar with the concept, but not the buzz-phrase used to describe it.
The first Boston MySQL User Group meeting went swimmingly. About 1/2 the people who RSVP’s yes or maybe showed up, ) got pizza as a thank-you gift.

My boss offered me a ride home, definitely — I’ll just go into work later, and not be tempted by a ride home.

The demographics of the group was really amazing:

about 15% female
those with no experience with any database
those with experience with databases but not MySQL
those who’ve been using MySQL for weeks
those who’ve been using MySQL for months
those who’ve been using MySQL for years
those who are trying to convince their company to switch
about 10% Indian
about 20% other-Asian
(I didn’t notice anyone that was recognizably Hispanic or black)
job titles ranging from developer, dba, all the way up to the vice president and president level
The publishing sector was represented by O’Reilly, Addison-Wesley (which is owned by Pearson, which handles the MySQL Press imprint), and Apress. O’Reilly and Apress solicited authors.

Corrections always welcome, and special thanks to Mike Kruckenberg, and Mark Rubin of MySQL AB.

I cannot wait for next month. . .
To contact me via e-mail:

awfief@gmail.com
To contact me via e-mail:

awfief@gmail.com
Having used Oracle, DB2, Postgres, Sybase, Informix, and MSSQL, I always enjoyed that MySQL just named everything “MySQL”. Sure, it can get confusing — there’s MySQL the server, MySQL the client, MySQL the database instance. . . .MySQL the flamethrower (the kids love this one). . . .But seriously, the ‘big guys’ have all this complicated jargon for really simple ideas.

MySQL has joined them. Granted, I’d been out of the MySQL world for about a year, and some wonderful things have happened in that year. Even a year ago, the company I worked for wasn’t using the most recent software nor taking advantage of all the features their versions of MySQL did have to offer. But I digress.

I’ve been working on MySQL knowledge, particularly with the free webinars. Today I attended the “MySQL Network and MySQL 5.0” webinar, where I learned that MySQL is packaging (better) software, support, tools, access to developers, and a knowledgebase into what they call “MySQL Network.” I was completely unclear on the concept of MySQL Network from the description, and from the name I figured it would have something to do with technical networking, not business to business networking.

Meanwhile, yesterday I realized that the “Pluggable Storage Engines” in MySQL just mean “you can use different table types, and turn off the ones you don’t want to use.” I was familiar with the concept, but not the buzz-phrase used to describe it.
To contact me via e-mail:

awfief@gmail.com
Having used Oracle, DB2, Postgres, Sybase, Informix, and MSSQL, I always enjoyed that MySQL just named everything “MySQL”. Sure, it can get confusing — there’s MySQL the server, MySQL the client, MySQL the database instance. . . .MySQL the flamethrower (the kids love this one). . . .But seriously, the ‘big guys’ have all this complicated jargon for really simple ideas.

MySQL has joined them. Granted, I’d been out of the MySQL world for about a year, and some wonderful things have happened in that year. Even a year ago, the company I worked for wasn’t using the most recent software nor taking advantage of all the features their versions of MySQL did have to offer. But I digress.

I’ve been working on MySQL knowledge, particularly with the free webinars. Today I attended the “MySQL Network and MySQL 5.0” webinar, where I learned that MySQL is packaging (better) software, support, tools, access to developers, and a knowledgebase into what they call “MySQL Network.” I was completely unclear on the concept of MySQL Network from the description, and from the name I figured it would have something to do with technical networking, not business to business networking.

Meanwhile, yesterday I realized that the “Pluggable Storage Engines” in MySQL just mean “you can use different table types, and turn off the ones you don’t want to use.” I was familiar with the concept, but not the buzz-phrase used to describe it.
The first Boston MySQL User Group meeting went swimmingly. About 1/2 the people who RSVP’s yes or maybe showed up, ) got pizza as a thank-you gift.

My boss offered me a ride home, definitely — I’ll just go into work later, and not be tempted by a ride home.

The demographics of the group was really amazing:

about 15% female
those with no experience with any database
those with experience with databases but not MySQL
those who’ve been using MySQL for weeks
those who’ve been using MySQL for months
those who’ve been using MySQL for years
those who are trying to convince their company to switch
about 10% Indian
about 20% other-Asian
(I didn’t notice anyone that was recognizably Hispanic or black)
job titles ranging from developer, dba, all the way up to the vice president and president level
The publishing sector was represented by O’Reilly, Addison-Wesley (which is owned by Pearson, which handles the MySQL Press imprint), and Apress. O’Reilly and Apress solicited authors.

Corrections always welcome, and special thanks to Mike Kruckenberg, and Mark Rubin of MySQL AB.

I cannot wait for next month. . .
To contact me via e-mail:

awfief@gmail.com
Having used Oracle, DB2, Postgres, Sybase, Informix, and MSSQL, I always enjoyed that MySQL just named everything “MySQL”. Sure, it can get confusing — there’s MySQL the server, MySQL the client, MySQL the database instance. . . .MySQL the flamethrower (the kids love this one). . . .But seriously, the ‘big guys’ have all this complicated jargon for really simple ideas.

MySQL has joined them. Granted, I’d been out of the MySQL world for about a year, and some wonderful things have happened in that year. Even a year ago, the company I worked for wasn’t using the most recent software nor taking advantage of all the features their versions of MySQL did have to offer. But I digress.

I’ve been working on MySQL knowledge, particularly with the free webinars. Today I attended the “MySQL Network and MySQL 5.0” webinar, where I learned that MySQL is packaging (better) software, support, tools, access to developers, and a knowledgebase into what they call “MySQL Network.” I was completely unclear on the concept of MySQL Network from the description, and from the name I figured it would have something to do with technical networking, not business to business networking.

Meanwhile, yesterday I realized that the “Pluggable Storage Engines” in MySQL just mean “you can use different table types, and turn off the ones you don’t want to use.” I was familiar with the concept, but not the buzz-phrase used to describe it.
The first Boston MySQL User Group meeting went swimmingly. About 1/2 the people who RSVP’s yes or maybe showed up, ) got pizza as a thank-you gift.

My boss offered me a ride home, definitely — I’ll just go into work later, and not be tempted by a ride home.

The demographics of the group was really amazing:

about 15% female
those with no experience with any database
those with experience with databases but not MySQL
those who’ve been using MySQL for weeks
those who’ve been using MySQL for months
those who’ve been using MySQL for years
those who are trying to convince their company to switch
about 10% Indian
about 20% other-Asian
(I didn’t notice anyone that was recognizably Hispanic or black)
job titles ranging from developer, dba, all the way up to the vice president and president level
The publishing sector was represented by O’Reilly, Addison-Wesley (which is owned by Pearson, which handles the MySQL Press imprint), and Apress. O’Reilly and Apress solicited authors.

Corrections always welcome, and special thanks to Mike Kruckenberg, and Mark Rubin of MySQL AB.

I cannot wait for next month. . .
So I’ve been looking into the Archive Storage Engine. What I would really like to do with it is get data in realtime, 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.