Currently Drizzle offers three (3) separate query logging plugins. These plugins offer an extensible means of gathering all or selected queries and provide the foundation for a query analyser tool. Additional filtering includes selecting queries by execution time, result size, rows processed and by any given regular expression via PCRE.

During this tutorial I’ll be stepping though the various logging_query parameters which log SQL in a CSV format.

Confirm Logging Plugins

You can view the current ACTIVE plugins in Drizzle with the following SQL.

drizzle> select version();
+--------------+
| version()    |
+--------------+
| 2009.07.1097 |
+--------------+

drizzle> select * from information_schema.plugins where plugin_name like 'logging%';
+-----------------+----------------+---------------+--------------------------------------+---------------------------------+----------------+
| PLUGIN_NAME     | PLUGIN_VERSION | …

I just uploaded the pdf of the summer issue of Open Source Database Magazine. Included in this issue:

• A great article on the new features of Postgresql 8.4 by Robert Treat
• Part one of a two part article on Percona’s new XtraBackup backup program

Also the news, the book shelf and Peter Brawley’s Coding Corner. Downloads are available at http://www.osdbzine.net.

The PBMS engine now has built in support for transaction so that if you reference or dereference BLOBs during a transaction the changes you made will be committed or rolled back with the transaction. Up until now PBMS had depended on the host engine to handle transactions and inform it what needed to be done in the event of a rollback.

I have implemented transaction support by adding a circular transaction log and transaction cache. The circular transaction log is dynamic and can grow and shrink as required. The transaction records are all small (30 byte) fixed length records so the log doesn’t need to be that large to be able to handle a lot of transactions. A head and tail pointer are maintained to indicate where the head and tail of the circular log is. If the circular log becomes full then the records just overflow and are written to the end of the log. Once the transaction reader has caught up after an overflow has occurred it …

Similar to the MySQL and Drizzle user defined functions for Gearman, we now have PostrgeSQL functions too! These allow you to submit jobs to the job server from within your SQL query, trigger, or stored procedure. Here’s a snippet of how this looks in PostgreSQL:

shell$ psql test
test=# SELECT gman_do('reverse', 'Hello World!');
   gman_do
--------------
 !dlroW olleH
(1 row)

test=#

Special thanks to Selena Deckelmann for helping get these working!

Due to circumstances beyond my control..evidently involving the power supply of computer handling email for one of the authors .. the magazine has been delayed for a day or two while it gets sorted out. Sorry about that, but it is going to be worth the wait!

Last week I offered a chance for a reader to win a copy of the MySQL Administrator’s Bible. The reason why is that I was recording an interview with Brian Aker about the Drizzle project and wanted to hear what you thought I should ask Brian. There were some excellent responses. I worked as many of them as possible into the interview although there were some that for legal reasons we could not cover.  Even if your question was not included your name was included in the drawing. Thanks everyone for the input. The interview with Brian will be part of the  Open Source Database podcast scheduled to be released on July the 24th.

And now for the winner!

Congratulations …

Unlike COUNT(expression), COUNT(DISTINCT expression) is not a distributable function. That is, the expression can not be computed by looking at only the changed rows in the table change logs.



The following view can not be FAST refreshed with Flexviews today:

SELECT a a_alias, 
       b b_alias, 
       c c_alias, 
       COUNT(DISTINCT d) d_alias
  FROM T1
 GROUP BY a, b, c;

However, a dependent child materialization could be created to support the value for COUNT(DISTINCT d):

-- child materialization, dependent subview
--There will be one row for each DISTINCT value of d

SELECT a a_alias, 
       b b_alias, 
       c c_alias, 
       count(*) d_alias_cnt
  FROM T1
 GROUP BY a, b, c, d;

The original view could then be rewritten as:

SELECT a a_alias, 
       b b_alias, 
       c c_alias, …

I’ll be talking about the latest features in Gearman along with covering a few Gearman-powered applications tomorrow, July 14th, at 10AM Pacific time. Follow this link for details on how to sign up: Gearman: New Features and Applications for Distributed Job Queuing

Brian Aker and I will also be talking about Gearman at the PostgreSQL pgDay San Jose on Sunday, July 19th, and at OSCON the week after. See the Gearman presentations page for more information.

We had 12 projects, and by the time we’ve hit mid-terms, we’ve decided to cull 2 project so far, leaving us with 10 projects.

This year, the MySQL project can really divide itself into three groups – those hacking on MySQL, Drizzle, or phpMyAdmin. Next year, will we see others? I certainly hope so…

Drizzle – Padraig O’Sullivan is doing an excellent job at working on a new implementation of the INFORMATION_SCHEMA. Nathan Williams is doing great work at code cleanup for Drizzle, and making it conform to C++ standards. Jiangfeng Peng is hacking on batch nested loop join’s in Drizzle.

phpMyAdmin – Derek Schaefer is adding import improvements to phpMyAdmin, while Tomas Srnka is working on adding MySQL Replication support for phpMyAdmin (and impressing his mentor!). Zahra Naeem is working on change tracking of data/structures, and you’d expect some more work after the mid-term, once some problems are worked …

Finally merged into main. I added the ability for engines to be temporary only – that is you can only CREATE TEMPORARY table or be created and used during query execution. This allows us to refactor/remove some other code and go towards a “locking is inside the engine” mantra as anything but row level or true MVCC is certainly the exception these days.