Pythian Group

OpenSQLCamp was a huge success! I took videos of most of the sessions (we only had 3 video cameras, and 4 rooms, and 2 sessions were not recorded). Unfortunately, I was busy doing administrative stuff for opensqlcamp for the opening keynote and first 15 minutes of the session organizing, and when I got to the planning board, it was already full….so I was not able to give a session.

• Comparing Non-Relational Databases: MongoDB, Tokyo Tyrant, CouchDB by Igal Koshevoy of Pragmaticraft
• Drizzle Client Rewrite – Clark Boylan leads the requirements and design discussion for rewriting the Drizzle Client
• Drizzle Plugin Hacking
• Drizzle is Not MySQL with Changes – Brian Aker gives a talk on Drizzle, and how Drizzle is not MySQL.
• Fractal Trees – by Bradley Kuszmaul of Tokutek
• Geographic Operators in SQL (PostGIS) by Webb Sprague
• Goal-Driven Performance – Peter Zaitsev speaks about how to achieve goal-driven performance.
• Graph Engine for MySQL – by Antony Curtis
• Intro to Cassandra
• An Intro to CouchDB: What caught Ubuntu’s eye by Mike Miller (Cloudant)
• Maria – MySQL founder Monty Widenius talks about Maria, and the past, present and future of MySQL.

Read the rest of this entry . . .

MySQL Full Text Search Limitations

Suppose you have a MyISAM table containing a column with a full text index. This table starts to grow to a significant size (millions of rows) and gets updated fairly frequently. Chances are that you’ll start to see some bottlenecks when accessing this table, since without row level locking, the reading and writing operations will be blocking each other.

A solution that many people would suggest right away is to use the master for writes and a slave for reads, but this only masks the problem, and it won’t take long before enough read traffic on the slave starts causing slave lags.

Why Sphinx?

Read the rest of this entry . . .

In a nutshell: What’s New in MySQL 5.1.

Release notes: Changes in release 5.1.x (Production).

And yes, very early on (at about two minutes in), I talk about my take on Monty’s controversial post at Oops, we did it again.

To play the video directly, go to http://www.youtube.com/watch?v=Hs4S7vONGMQ. Or watch it embedded inline here:

Read the rest of this entry . . .

Liveblogging: Extending MySQL by Brian “Krow” Aker

Brian wins the award for “most frequent great quotes during a talk”.

Before MySQL 5.1 a UDF was the only way to extend MySQL.

All you need in a UDF is: init() execute() deinit()

my_bool id3_parse_init(UDF_INIT *initid UDF_ARGS *args, char *message)

UDF_ARGS tell you about incoming args
char *message is the output that might return
args->arg_count is the # of args

WARNING: use STRICT mode in MySQL, otherwise there are tons of silent failures.

“When you work on databases you start to put everything in databases. Tip, don’t put a DVD into a database, because really long BLOBs aren’t actually supported….”

In MySQL 5.1, you can now install plugins (example is memcache_servers plugin):

mysql> INSTALL PLUGIN memcache_servers SONAME 'libmemcache_engine.so'

In the plugin architecture, there’s a callback mechanism to call a structure and then the structure is self-hosted into the environment. mysql_declare_plugin() is usually found at the bottom of the file because that’s how Brian wrote the example….

A storage engine is really about connecting to anything.

MySQL has a parser, and optimizer, and a retrieve/store part, which talks to the storage engines. The engine interface is written in C++.

Quote about the BLACKHOLE storage engine: “It’s amazing how much money can be made out of something that does NOTHING.”

What about defining features?

tables_flags()
index_flags()
handlerton flags

Table object methods:

base_ext()
create()
delete_table()
rename_table()
optimize(), repair(), etc.

As you can see, the API is pretty specific to actions that are happening at the SQL layer.

Reads
rnd_init() — scan is about to begin
rnd_next() — get the next record
(rnd = random or read next data)
index_first() — index read is about to begin
index_next() — get the next record in the index

Writes:
write_row() — “here’s new data, please store this”.
delete_row()
update_row()

on delete or update, MySQL starts reading the rows and once the predicate matches, then the delete_row() or update_row() is called. update_row() will be passed the before and after image of the row.

What about information_schema?

The information_schema API is an easy way to get more information into the information_schema tables.

(there’s a really nice example of an information_schema plugin)

A quote about his code — “That in the middle there looks like a bug….but it’s never called, so that’s OK. That’s what I get for copying and pasting my own examples.”

The plugin is responsible for all the locking.

Daemon plugins created because otherwise people would “bastardize the storage engine plugins.”

A daemon plugin has full access to the server internals, and it is a simple interface to be able to extend.

“If you’re really going to shoot yourself in the foot, you really want to go full blast.”

UDP Daemon just allows UDP packets to be put into the db (instead of TCP/IP with its darn authentication schema). Simplest interface to MySQL that exists. No connector, just UDP. The point is you can put other listeners to MySQL, such as an HTTP listener.

Daemon plugins are also a way to

code for examples
skeleton engine