I have recently been investigating a bew feature of MySQL 6.0 - the "Pool-of-Threads" scheduler. This feature is a fairly significant change to the way MySQL completes tasks given to it by database clients.

To begin with, be advised that the MySQL database is implemented as a single multi-threaded process. The conventional threading model is that there are a number of "internal" threads doing administrative work (including accepting connections from clients wanting to connect to the database), then one thread for each database connection. That thread is responsible for all communication with that database client connection, and performs the bulk of database operations on behalf of the client.

This architecture exists in other RDBMS implementations. Another common implementation is a collection of processes all cooperating via a region of shared memory, usually with semaphores or other synchronization objects located in that shared …

I have recently been investigating a bew feature of MySQL 6.0 - the "Pool-of-Threads" scheduler. This feature is a fairly significant change to the way MySQL completes tasks given to it by database clients.

To begin with, be advised that the MySQL database is implemented as a single multi-threaded process. The conventional threading model is that there are a number of "internal" threads doing administrative work (including accepting connections from clients wanting to connect to the database), then one thread for each database connection. That thread is responsible for all communication with that database client connection, and performs the bulk of database operations on behalf of the client.

This architecture exists in other RDBMS implementations. Another common implementation is a collection of processes all cooperating via a region of shared memory, usually with semaphores or other synchronization objects located in that shared …

Just a quick introduction to begin with.

I joined Sun Microsystems in Feb 2009 to look after the product management for MySQL Cluster and MySQL replication.

I started my career with Nortel (technically BNR which was the R&D arm of Northern Telecom but everything later got merged and rebranded as Nortel). I was responsible for writing the original proprietary, in-memory database for Nortel’s HLR product. Later on, we used a number of 3rd party databases for the HLR (provisioning rather than real-time) and then HSS - starting with Oracle for the HLR and then SOLID but then settling on MySQL Cluster as the scaleable real-time database for the HSS.

When I left Nortel (via an IBM rebadging) I moved to Sun, hoping to use my experience as a MySQL Cluster customer to help the team build upon their strong product.

So far, so good - I’ve been really impressed both with the MySQL team and with how well the product is …

There seems to be an optimizer problem in 5.1, if you put a NULL in the IN clause of a SELECT. For example, given the following table:

CREATE TABLE foo (
    a INT NOT NULL AUTO_INCREMENT,
    PRIMARY KEY (a)
);

Compare these two EXPLAINs:

mysql> EXPLAIN * FROM foo WHERE a IN (160000, 160001, 160002)\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: foo
         type: range
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: NULL
         rows: 3
        Extra: Using where
1 row in set (0.06 sec)

mysql> EXPLAIN SELECT * FROM foo WHERE a IN (NULL, 160000, 160001, 160002)\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: foo
         type: ALL
possible_keys: PRIMARY
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 327680
        Extra: Using where
1 …

A little while ago, I was the brave soul tasked with making sure Drizzle was working properly and passing all tests on Solaris and OpenSolaris. Brian recently blogged about some of the advantages of also running on Solaris and the SunStudio compilers - more warnings from the compiler is a good thing. Many kudos goes to Monty Taylor for being the brave soul who fixed most of the compiler warnings (and for us, warnings=errors - so we have to fix them) for the SunStudio compilers before I got to making te tests work.

So, I got to the end of it all and got pointed to an OpenSolaris x86 box where the drizzleslap test was timing out. The timeout for tests is some amazingly long amount of time - 15 minutes. All the drizzle-test-run tests are rather short tests.

To make running the tests quick, I usually LD_PRELOAD …

Three great beta deliveries in one week!

MySQL Connector/.Net 6.0.2 beta, a new version of the all-managed .NET driver for MySQL.

MySQL Connector/C++ 1.0.4 beta, a new release providing  C++ API for connecting client applications to the MySQL Server. If you know JDBC, this should be familiar to you.

MySQL Connector/C 6.0.0 beta, a new version of the C API for accessing MySQL database servers

Use them and let us know what you think.

Learn more about Connector/C++ here.

Learn more about Connector/C …

I am happy to announce next build of our backup tool. This version contains several bugfixes and introduces initial implementation of incremental backup.

Incremental backup works in next way. When you do regular backup, at the end of procedure you can see output:

PLAIN TEXT CODE:

1. The latest check point (for incremental): '1319:813219999'
2. >> log scanned up to (1319 813701532)
3. Transaction log of lsn (1318 3034677302) to (1319 813701532) was copied.
4. 090404 06:03:29  innobackupex: All tables unlocked
5. 090404 06:03:29  innobackupex: Connection to database server closed
6.  
7. innobackupex: Backup created in directory '/mnt/data/tmp'
8. innobackupex: MySQL binlog position: filename …

Over the past couple days I’ve been tearing through the network I/O code inside of Drizzle. I moved anything that touched NET into the Protocol class (reading commands, preferences, error checking) and wrapped them with new Protocol methods. I then moved the NET structure out of Session (or THD for the MySQL folks) and into Protocol. At this point all network I/O was going through Protocol, which allowed me to do a proper abstraction layer with the old libdrizzle as a derived implementation class. The next step was to add in a new plugin hook for Protocol, and move the old libdrizzle implementation into a plugin. Since the server is the only thing using the old libdrizzle, all of that code also went into the plugin (note: this means the final death of old libdrizzle!). So now each new connection grabs a Protocol object from the default registered Protocol plugin and Session will use this anytime it …

EclipseLink JPA replaces TopLink Essentials as the JPA implementation in GlassFish v3. One of the benefits of using EclipseLink is that it provides efficient pagination support for the MySQL database by generating native SQL statements such as "SELECT ... FROM <table> LIMIT <offset>, <rowcount>".

The MySQL LIMIT clause definition says:

The LIMIT clause can be used to constrain the number of rows returned by the SELECT statement. LIMIT takes one or two numeric arguments, which must both be non-negative …

EclipseLink JPA replaces TopLink Essentials as the JPA implementation in GlassFish v3. One of the benefits of using EclipseLink is that it provides efficient pagination support for the MySQL database by generating native SQL statements such as "SELECT ... FROM <table> LIMIT <offset>, <rowcount>".

The MySQL LIMIT clause definition says:

The LIMIT clause can be used to constrain the number of rows returned by the SELECT statement. LIMIT takes one or two numeric arguments, which must both be non-negative …