I was trying to demonstrate to a client how to monitor queries that generate internal temporary tables. With an EXPLAIN plan you see ‘Creating temporary’. Within MySQL you can use the SHOW STATUS to look at queries that create temporary tables.

There is the issue that the act of monitoring impacts the results, SHOW STATUS actually creates a temporary table. You can see in this example.

mysql> select version();
+-----------------+
| version()       |
+-----------------+
| 5.1.31-1ubuntu2 |
+-----------------+
1 row in set (0.00 sec)

mysql> show global status like 'created_tmp%';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 48    |
| Created_tmp_files       | 5     |
| Created_tmp_tables      | 155   |
+-------------------------+-------+
3

  [Read more...]

Welcome to the 152^nd edition of Log Buffer, the weekly review of database blogs.

PostgreSQL

Courtesy the United States PostgreSQL Association, the big news: PostgreSQL 8.4 Released!.

Josh Berkus writes, “Now that PostgreSQL 8.4 is out, I thought I’d write a little about my favorite 8.4 feature. As Mr. Performance Whack-a-Mole, what makes me happy about 8.4 is the ability to whack moles faster … which is why I’m very fond of pg_stat_statements.”

On ad’s corner,

  [Read more...]

It’s not the most common task in the world, but you might want to view processes from a particular user and once in a while you might even need to kill processes from a single user, be it during an attack or because you simply got a bug in an application bombarding your db server with connections!

Here is a small stored procedure which does exactly that!

call process_list(’show’,‘username’,‘hostname’);

– shows all processes owned by username@hostname

call process_list(‘kill’,‘username’,‘hostname’);

– kills all processes owned by

  [Read more...]

I'm pleased to announce the release of Condition Pushdown for ha_partition version 0.1.
http://launchpad.net/partitionconditionpushdownformysql

This release is a patch file for MySQL's table partitioning feature. (ndb is excluded)
This patch file works to add methods("cond_push" and "cond_pop") to ha_partition. As a result, every storage engine's table that use table partitioning can get condition-pushdown through ha_partition.

There was no problem because only ndb supported engine-condition-pushdown up to now.
This patch was needed because the Spider storage engine supported engine-condition-pushdown recently.

Enjoy!

Hi!

Yesterday I had the honour of presenting my mini-bi/datawarehousing tutorial "Starring Sakila" for MySQL University. I did a modified version of the presentation I did together with Matt Casters at the MySQL user's conference 2009. The structure of the presentation is still largely the same, although I condensed various bits, and I added practical examples of setting up the ETL process and creating a Pentaho Analysis View (OLAP pivot table) on top of a Mondrian Cube.

The slides, session recording, and materials such as SQL script, pentaho data integration jobs and transformations, and Sakila Rentals Cube for Mondrian are all available here on MySQL



  [Read more...]

Image via Wikipedia

The NoSQL event has triggered a bit of a hard time for the RDBMS the last week.  I won’t add any commentary as this follows what I have been talking about for a while, but here are some of the links.  Most notable is Michael Stonebraker’s post on the ACM site.

  [Read more...]

I’ve had a few VCs ask how we compare to Hadoop and companies using MapReduce. With Google blessing MapReduce, it seems to be the cool new thing. I figure I’m going to have to explain this to VCs, so I might as well blog about it.

MapReduce is a process of dividing a problem into small pieces and distributing (mapping) those pieces to a large number of computers. Then it collects the processed data and merges (reduces) it into a result set. Hadoop provides the plumbing, so users focus on writing the query and Hadoop handles the dirty work of mapping and reducing. Such a query, using a procedural language like Java, is more complex than a comparable SQL query, but more on that below.

So what is MapReduce good for? It really shines when you want to summarize, analyze or transform a very large data set. This is why it is well suited to web data. Map reduce

  [Read more...]

Earlier this year I had published a small blog about being efficient when using mysql prompt. This is a small continuation of it highlighting a couple of other cool features which I really find very useful when working command line (i.e. always!).

The first I’m gonna list here is setting the prompt itself by typing –

thus enabling date and time display at each comand which is great for auditing and record keeping. There is a whole bunch of prompt values which you can find here:

Option  Description
\c      //A counter that increments for each statement you issue
\D      //The full current date
\d      //The

  [Read more...]

Version 0.4 of libdrizzle has been released. This was mostly a maintenance release with build system changes and small bug fixes. This is the client and protocol library for Drizzle and MySQL that provides both client and server interfaces.

Version 0.4.1 of the Drizzle PHP Extension has also been released. James Luedke has moved development and releases of the extension into PECL, and has also fixed a number of bugs, extended the interface, and worked with the PHP/PECL developers to get the extension up to the proper PHP coding standards. Thanks James!

Yesterday I still worked for Sun Microsystems as a Principal Engineer & MySQL Sr. Architect. Today was my first day of work at Monty Program, Inc, a subsidiary of a tiny company established by Monty Widenius in February. Yet I didn't even make the top ten. If people want not to miss the train they have to hurry up.

Am I happy? Oh, yeah...
No more waking up with the question constantly drilling my mind: “What am I doing here?” What are all of us, MySQL Server developers, doing without Monty? Waiting for the time when all our options are vested? I can't . That's too long for me. I'm already too old to wait any more.
Besides, we've already lost at least 3 years. We have to do what we planned to do in 2005. We have to raise the Server to the level where any RDBMS that claims to be called mature should be.

So who is newly born? Me? In a way, yes. This is my





  [Read more...]

Version 0.8 of the Gearman C Server and Library has been released. This includes basic HTTP protocol support, build system improvements, and bug fixes.

Version 0.4.0 of the Gearman PHP Extension has also been released.

If you want to learn more about Gearman, be sure to check out the upcoming Boston MySQL Meetup, MySQL Webinar, or the one of the events at OSCON (tutorial, session, and BoF).

I'm pleased to announce the release of Vertical Partitioning storage engine version 0.2.
http://launchpad.net/vpformysql


The main changes in this version are following.


- Support MySQL's table partitioning.
  MySQL's table partitioning that is only introduced last release is now supported.
  note: After this version, you need to apply a patch for installing Vertical Partitioning storage engine.

- After this version, the primary key part of InnoDB's secondary index is effectively used.
  You can use the primary key columns with InnoDB's secondary index by using following statements.
    create table tbl_b(
      col_a int not













  [Read more...]