With 130 entries in the “MySQL” category and no MySQL-related subcategories, my blog had become impossible to search and navigate easily.

And thus I created a number of new categories for the MySQL entries within my blog. They’re listed in the left navigation bar, below the months, as well as below:

• MySQL Server, MySQL Cluster, Falcon
• Connectors: PHP, Ruby on Rails
• Tools: GUI, MySQL Workbench, MySQL Proxy
• Events: MySQL Users Conferences
• Licensing: GPL
• Architecture of Participation, Summer of Code, Virtual company
• Other: Release Policy, Documentation, Use cases

I hope this will make my blog more (re)usable.

(The picture is from this summer, when navigating the way up the Großvenediger, a 3662 m high mountain in the Hohe Tauern region of Austria.)

Giuseppe Maxia teased me last week to see whether I could decipher the notice on the Italian web site Punto Informatico about “MySQL University, lezione gratuita su Lua“.

It’s about Giuseppe teaching MySQL University attendees (for free as in both beer and speech) on the Lua scripting language. The MySQL reason to learn Lua is to do scripting in MySQL Proxy. Don’t worry, the MySQL University session is in English, not Italian, and Giuseppe has deciphered the Italian himself in his blog.

You can attend MySQL University virtually with slides in PDF and Wiki, audio on ogg stream, and interaction with the lecturer via IRC (at Freenode under #mysql-university).

Timing: This Thursday 13 Dec 2007 at 6:00am PST = 9:00am EST = 15:00 CET = 16:00 EET = 17:00 Moscow time

Interested?

1. Read Giuseppe’s article “Getting started with MySQL Proxy“
2. Register for the University session

References:

• Giuseppe’s own blog entry “MySQL University - Introducing Lua for Proxy scripting“: http://datacharmer.blogspot.com/2007/12/mysql-university-introducing-lua-for.html
• Lua on Wikipedia: http://en.wikipedia.org/wiki/Lua_%28programming_language%29
• MySQL Proxy: http://forge.mysql.com/wiki/MySQL_Proxy
• MySQL University: http://forge.mysql.com/wiki/MySQL_University
• Giuseppe’s article “Getting started with MySQL Proxy”: http://dev.mysql.com/tech-resources/articles/proxy-gettingstarted.html
• MySQL University: Writing LUA scripts for MySQL Proxy session:
  http://forge.mysql.com/wiki/Writing_LUA_Scripts_for_MySQL_Proxy

A while ago, I had a discussion with Stewart Smith, Vinay Joosery, Monty Taylor and a number of other MySQLers who know much more about MySQL Cluster than I do. The result is a model for using MySQL Proxy to offload MySQL Cluster from doing Table Scans, without touching the application.

The discussion started from me asking Stewart about the largest road block for expanding the number of use cases for MySQL Cluster. “Oh, that would probably be doing JOINs and other SELECTs requiring the scanning of large parts of the database”, he replied. “There, other storage engines are faster, such as MyISAM and InnoDB.”

In a very simple view, the application talks SQL with MySQL Cluster, and gets responses.

Stewart’s insight can be refined into the first simplistic diagram by adding the recognition that “SQL” can consist of

1. UPDATE, INSERT, DELETE statements (very light, usually invidual rows affected) — unidirectional blue arrow in the diagram below
2. Simple SELECT statements (also very light, defined as SELECTs that use indices and return invidual rows) — bidirectional black arrow
3. Complex SELECT statements (could be as easy as “SELECT *”, but defined as those not easily using indices and usually returning multiple rows) — dashed arrows having two arrowheads to show that plenty of data is being returned

This second figure doesn’t depict any change in application architecture from the first figure; it just shows a more granular view.

Now, enter the insight that plain MySQL Server (with MyISAM or InnoDB) can deliver the complex SELECTs faster.

In the new architecture represented by the above picture, we scale the application by

1. introducing Replication (replicating MySQL Cluster to plain MySQL Server)
2. changing the application to direct the complex SELECTs to MySQL Server instead of MySQL Cluster

This complicates life. Not only do we need to set up replication. We also need to touch the application all over the place, to direct queries to the appropriate server.

Now, enter MySQL Proxy.

Using LUA scripts, MySQL Proxy can relieve us of the second complication, i.e. having to change the application to point to different MySQL Servers depending on the type of the SQL. Let MySQL Proxy parse the traffic and direct it to the appropriate server! The application is left untouched, and the topmost part of the picture again has a simple bidirectional arrow saying “SQL”. The distinction of what type of SQL we’re talking about is left to MySQL Proxy.

MySQL Proxy can also be assigned to load balancing the queries to a number of MySQL Replication Slaves.

This picture clearly is the most complex architecture depicted here, but also represents the highest level of scaling.

All of the above can be done using current versions of MySQL Cluster and MySQL Server, and the MySQL Proxy.

Sources:

• MySQL Proxy: http://forge.mysql.com/wiki/MySQL_Proxy
• MySQL Cluster: http://www.mysql.com/products/database/cluster/
• MySQL Replication: http://dev.mysql.com/doc/refman/5.0/en/replication.html

Today at OSCON, MySQL co-founder Michael “Monty” Widenius and I presented the “State of the Dolphin” lightning talk.

My slides for this preso weren’t too graphic, which makes them all the easier to reuse in this blog:

Use our new software!

• Use MySQL 5.1, it‘s soon going RC
• Use Falcon, it‘s soon going Beta (new transactional storage engine, faster than InnoDB on large servers)
• Use MySQL Workbench (ER Tool), Now Beta
• Use MySQL Proxy, just released
• PHPers: Use mysqlnd (Native Driver)

Go test MySQL 5.1!

• We‘re happy with the quality
  • More stable than 5.0 was four months after GA
  • RC happening very soon, GA within a few versions after that
  • A better MySQL 5.0 (thousands of small fixes)
• We‘re happy with the new functionality
  • Table / Index Partitioning
  • Row-based replication – Transfers data instead of commands
  • Full-text indexing parser plugins – Flexible full text search
  • Disk-based Data Support for MySQL Cluster
  • Replication Support for MySQL Cluster
  • XPath Support - helps any customer wanting to better navigate and search XML documents stored in MySQL
  • Internal Task Scheduler (Events)

Other goodies coming soon (5.2, 6.0, …)

• Global Backup API
• Falcon and Maria (MyISAM++) storage engine
• Further new storage engines
• Hash & Merge joins (faster subselects)
• Federated tables over ODBC
• Foreign key support for all engines

Participate in our Development!

• Report bugs! Test them! Submit patches!
• Hang out on Freenode IRC #mysql-dev
• Subscribe to commits@lists.mysql.com to see our code reviews
• Attend MySQL University, the foremost education for MySQL developers (of C/C++ code, not apps)
• MySQL Forge: List your MySQL apps! Upload your code snippets! Fix the missing documents!
• Go visit MySQL Forge Worklog
  • Voting for best features starting soon

Hiring!

We are hiring outstanding C/C++ developers with systems or database engineering experience.
Visit www.mysql.com/jobs or email resume to jpugh@mysql.com