PlanetMysql.ru — информация о СУБД MySQL

I happened upon a customer who left me in awe and admiration. The reason: excellent comments for their SQL code.

I list four major places where SQL comments are helpful. I’ll use the sakila database. It is originally scarcely commented; I’ll present it now enhanced with comments, to illustrate.

Table definitions

The CREATE TABLE statement allows for a comment, intended to describe the nature of the table:

CREATE TABLE `film_text` (
 `film_id` smallint(6) NOT NULL,
 `title` varchar(255) NOT NULL,
 `description` text,
 PRIMARY KEY (`film_id`),
 FULLTEXT KEY `idx_title_description` (`title`,`description`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Reflection of `film`, used for FULLTEXT search.'

It’s too bad the comment’s max length is 60 characters, though. However, it’s a very powerful field.

Column definitions

One may comment particular columns:

CREATE TABLE `film` (
 `film_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
 `title` varchar(255) NOT NULL,
 `description` text,
 `release_year` year(4) DEFAULT NULL,
 `language_id` tinyint(3) unsigned NOT NULL COMMENT 'Soundtrack spoken language',
 `original_language_id` tinyint(3) unsigned DEFAULT NULL COMMENT 'Filmed spoken language',
 `rental_duration` tinyint(3) unsigned NOT NULL DEFAULT '3',
 `rental_rate` decimal(4,2) NOT NULL DEFAULT '4.99',
 `length` smallint(5) unsigned DEFAULT NULL,
 `replacement_cost` decimal(5,2) NOT NULL DEFAULT '19.99',
  ...
) ENGINE=InnoDB AUTO_INCREMENT=1001 DEFAULT CHARSET=utf8

Stored routines definitions

Here’s an original sakila procedure, untouched. It is already commented:

CREATE DEFINER=`root`@`localhost` PROCEDURE `rewards_report`(
 IN min_monthly_purchases TINYINT UNSIGNED
 , IN min_dollar_amount_purchased DECIMAL(10,2) UNSIGNED
 , OUT count_rewardees INT
)
 READS SQL DATA
 COMMENT 'Provides a customizable report on best customers'
BEGIN

 DECLARE last_month_start DATE;
 DECLARE last_month_end DATE;
 ...

SQL queries

Last but not least, while not part of the schema, SQL queries define the use of the schema. That is, the schema exists for the sole reason of being able to query it.

Where did that query come from? Which piece of code issued it? Why? What’s its purpose?

Looking at the PROCESSLIST, the slow log, etc., it is easier when the queries are commented:

SELECT
 /* List film details along with participating actors */
 /* Issued by analytics module */
 film.*,
 COUNT(*) AS count_actors,
 GROUP_CONCAT(CONCAT(actor.first_name, ' ', actor.last_name))
FROM
 film
 JOIN film_actor USING(film_id)
 JOIN actor USING(actor_id)
GROUP BY film.film_id;

Conclusion

Source code commenting is an important practice, and usually watched out for. SQL & table definitions commenting are often scarce or non-existent. I urge DBAs to adopt a comments coding convention for SQL, and apply it whenever they can.

Everybody working on Unix or in the database world stumbles over Oracle Berkeley DB every now and then. DB is an Open Source embedded database used by applications like OpenLDAP or Postfix. Traditionally it followed mostly a key-value access pattern. Now what caught my attention was the fact that the recently released DB 5.0 provides an SQLite-like C API with the promise of providing better concurrency and performance than regular SQLite. Time to give it a shot.

So I grabbed the source distribution, checked the documentation and saw that I shall use the --enable-sql_compat configure option so DB creates a libsqlite3.so which can be used by PHP's build system. On my system compiling this worked like a charm and after linking PHP against it the SQLite3 extension as well as th SQLite PDO driver worked as before. I didn't do any benchmarks, yet, but I would be interested to read some results. Oh and yes, according to the documentation there are a few minor changes in behavior, this should only affect very few PHP users though.

So the whole process after extracting DB and PHP sources looked like this:

$ cd db-5.0.21/build_unix
$ ../dist/configure --prefix=/opt/db-5.0.21 --enable-sql_compat --enable-cxx
$ make
# make install
$ cd ../..
$ cd php-src
$ ./configure --with-sqlite3=/opt/db-5.0.21 --with-pdo-sqlite=/opt/db-5.0.21 --with-other-php-options
$ make
# make install

Now you don't have to use the SQLite interface for using DB but can also use PHP's dba extension. In current releases of PHP PHP expects at most DB version number 4 for the DBA driver, but a patch was committed to PHP's svn repository so upcoming PHP releases can be build using the latest DB version by adding --with-db4=/opt/db-5.0.21 to PHP's configure line.

Now I can hear you complain: "three ways to access a database isn't enough!" - which is true as the SQLite3 extension as well as the PDO SQLite driver are limited to SQLite capabilities and DBA, as an abstraction layer for Berkeley DB-like databases, only offers some basic operations, too. But there's hope:

While browsing through the DB source distribution I stumbled over a directory called php_db4 which obviously caught my further attention, so the DB developers are bundling a DB-specific PHP extension wrapping DB's native C++ API in an PHP OO API. To try it simply follow the steps from the PHP manual for compiling custom extensions:

$ cd db-5.0.21/php_db4
$ phpize
$ ./configure --with-db4=/opt/db-5.0.21
$ make
# make install

And then a quick check using reflection to see what functionality is provided by this extension:

$ php -dextension=db4.so --re db4
Extension [  extension #45 db4 version 0.9 ] {

  - Constants [210] {
    Constant [ integer DB_VERSION_MAJOR ] { 5 }
    Constant [ integer DB_VERSION_MINOR ] { 0 }
    Constant [ integer DB_VERSION_PATCH ] { 21 }
    Constant [ string DB_VERSION_STRING ] { Berkeley DB 5.0.21: (March 30, 2010) }
    Constant [ integer DB_MAX_PAGES ] { 4294967295 }
    Constant [ integer DB_MAX_RECORDS ] { 4294967295 }
    Constant [ integer DB_DBT_APPMALLOC ] { 1 }
    Constant [ integer DB_DBT_ISSET ] { 8 }
    Constant [ integer DB_DBT_MALLOC ] { 16 }
    Constant [ integer DB_DBT_PARTIAL ] { 64 }
    Constant [ integer DB_DBT_REALLOC ] { 128 }
    Constant [ integer DB_DBT_USERMEM ] { 1024 }
    Constant [ integer DB_DBT_DUPOK ] { 4 }
    Constant [ integer DB_CREATE ] { 1 }
    Constant [ integer DB_CXX_NO_EXCEPTIONS ] { 2 }
    Constant [ integer DB_FORCE ] { 1 }
    Constant [ integer DB_NOMMAP ] { 8 }
    Constant [ integer DB_RDONLY ] { 1024 }
    Constant [ integer DB_RECOVER ] { 2 }
    Constant [ integer DB_MULTIVERSION ] { 4 }
    Constant [ integer DB_TXN_SNAPSHOT ] { 2 }
    Constant [ integer DB_THREAD ] { 16 }
    Constant [ integer DB_TRUNCATE ] { 32768 }
    Constant [ integer DB_TXN_NOSYNC ] { 1 }
    Constant [ integer DB_TXN_NOT_DURABLE ] { 2 }
    Constant [ integer DB_USE_ENVIRON ] { 4 }
    Constant [ integer DB_USE_ENVIRON_ROOT ] { 8 }
    Constant [ integer DB_AUTO_COMMIT ] { 256 }
    Constant [ integer DB_DIRTY_READ ] { 512 }
    Constant [ integer DB_DEGREE_2 ] { 1024 }
    Constant [ integer DB_READ_COMMITTED ] { 1024 }
    Constant [ integer DB_READ_UNCOMMITTED ] { 512 }
    Constant [ integer DB_NO_AUTO_COMMIT ] { 8192 }
    Constant [ integer DB_RPCCLIENT ] { 1 }
    Constant [ integer DB_INIT_CDB ] { 64 }
    Constant [ integer DB_INIT_LOCK ] { 128 }
    Constant [ integer DB_INIT_LOG ] { 256 }
    Constant [ integer DB_INIT_MPOOL ] { 512 }
    Constant [ integer DB_INIT_REP ] { 1024 }
    Constant [ integer DB_INIT_TXN ] { 2048 }
    Constant [ integer DB_JOINENV ] { 0 }
    Constant [ integer DB_LOCKDOWN ] { 4096 }
    Constant [ integer DB_PRIVATE ] { 8192 }
    Constant [ integer DB_RECOVER_FATAL ] { 16384 }
    Constant [ integer DB_SYSTEM_MEM ] { 65536 }
    Constant [ integer DB_EXCL ] { 64 }
    Constant [ integer DB_FCNTL_LOCKING ] { 2048 }
    Constant [ integer DB_RDWRMASTER ] { 16384 }
    Constant [ integer DB_WRITEOPEN ] { 65536 }
    Constant [ integer DB_TXN_NOWAIT ] { 16 }
    Constant [ integer DB_TXN_SYNC ] { 4 }
    Constant [ integer DB_ENCRYPT_AES ] { 1 }
    Constant [ integer DB_CDB_ALLDB ] { 64 }
    Constant [ integer DB_DIRECT_DB ] { 128 }
    Constant [ integer DB_NOLOCKING ] { 1024 }
    Constant [ integer DB_NOPANIC ] { 2048 }
    Constant [ integer DB_OVERWRITE ] { 4096 }
    Constant [ integer DB_PANIC_ENVIRONMENT ] { 8192 }
    Constant [ integer DB_REGION_INIT ] { 16384 }
    Constant [ integer DB_TIME_NOTGRANTED ] { 32768 }
    Constant [ integer DB_TXN_WRITE_NOSYNC ] { 32 }
    Constant [ integer DB_YIELDCPU ] { 65536 }
    Constant [ integer DB_UPGRADE ] { 1 }
    Constant [ integer DB_VERIFY ] { 2 }
    Constant [ integer DB_DIRECT ] { 16 }
    Constant [ integer DB_EXTENT ] { 64 }
    Constant [ integer DB_ODDFILESIZE ] { 128 }
    Constant [ integer DB_CHKSUM ] { 8 }
    Constant [ integer DB_DUP ] { 16 }
    Constant [ integer DB_DUPSORT ] { 4 }
    Constant [ integer DB_ENCRYPT ] { 1 }
    Constant [ integer DB_RECNUM ] { 64 }
    Constant [ integer DB_RENUMBER ] { 128 }
    Constant [ integer DB_REVSPLITOFF ] { 256 }
    Constant [ integer DB_SNAPSHOT ] { 512 }
    Constant [ integer DB_STAT_CLEAR ] { 1 }
    Constant [ integer DB_JOIN_NOSORT ] { 1 }
    Constant [ integer DB_AGGRESSIVE ] { 1 }
    Constant [ integer DB_NOORDERCHK ] { 2 }
    Constant [ integer DB_ORDERCHKONLY ] { 4 }
    Constant [ integer DB_PR_PAGE ] { 16 }
    Constant [ integer DB_PR_RECOVERYTEST ] { 32 }
    Constant [ integer DB_PRINTABLE ] { 8 }
    Constant [ integer DB_SALVAGE ] { 64 }
    Constant [ integer DB_REP_NOBUFFER ] { 2 }
    Constant [ integer DB_REP_PERMANENT ] { 4 }
    Constant [ integer DB_LOCKVERSION ] { 1 }
    Constant [ integer DB_FILE_ID_LEN ] { 20 }
    Constant [ integer DB_LOCK_NORUN ] { 0 }
    Constant [ integer DB_LOCK_DEFAULT ] { 1 }
    Constant [ integer DB_LOCK_EXPIRE ] { 2 }
    Constant [ integer DB_LOCK_MAXLOCKS ] { 3 }
    Constant [ integer DB_LOCK_MINLOCKS ] { 5 }
    Constant [ integer DB_LOCK_MINWRITE ] { 6 }
    Constant [ integer DB_LOCK_OLDEST ] { 7 }
    Constant [ integer DB_LOCK_RANDOM ] { 8 }
    Constant [ integer DB_LOCK_YOUNGEST ] { 9 }
    Constant [ integer DB_LOCK_NOWAIT ] { 1 }
    Constant [ integer DB_LOCK_RECORD ] { 2 }
    Constant [ integer DB_LOCK_SET_TIMEOUT ] { 4 }
    Constant [ integer DB_LOCK_SWITCH ] { 8 }
    Constant [ integer DB_LOCK_UPGRADE ] { 16 }
    Constant [ integer DB_HANDLE_LOCK ] { 1 }
    Constant [ integer DB_RECORD_LOCK ] { 2 }
    Constant [ integer DB_PAGE_LOCK ] { 3 }
    Constant [ integer DB_LOGVERSION ] { 17 }
    Constant [ integer DB_LOGOLDVER ] { 8 }
    Constant [ integer DB_LOGMAGIC ] { 264584 }
    Constant [ integer DB_ARCH_ABS ] { 1 }
    Constant [ integer DB_ARCH_DATA ] { 2 }
    Constant [ integer DB_ARCH_LOG ] { 4 }
    Constant [ integer DB_ARCH_REMOVE ] { 8 }
    Constant [ integer DB_FLUSH ] { 1 }
    Constant [ integer DB_LOG_CHKPNT ] { 2 }
    Constant [ integer DB_LOG_COMMIT ] { 4 }
    Constant [ integer DB_LOG_NOCOPY ] { 8 }
    Constant [ integer DB_LOG_NOT_DURABLE ] { 16 }
    Constant [ integer DB_LOG_WRNOSYNC ] { 32 }
    Constant [ integer DB_user_BEGIN ] { 10000 }
    Constant [ integer DB_debug_FLAG ] { 2147483648 }
    Constant [ integer DB_LOG_DISK ] { 1 }
    Constant [ integer DB_LOG_LOCKED ] { 2 }
    Constant [ integer DB_LOG_SILENT_ERR ] { 4 }
    Constant [ integer DB_MPOOL_CREATE ] { 1 }
    Constant [ integer DB_MPOOL_LAST ] { 16 }
    Constant [ integer DB_MPOOL_NEW ] { 32 }
    Constant [ integer DB_MPOOL_DIRTY ] { 2 }
    Constant [ integer DB_MPOOL_DISCARD ] { 1 }
    Constant [ integer DB_MPOOL_NOFILE ] { 1 }
    Constant [ integer DB_MPOOL_UNLINK ] { 2 }
    Constant [ integer DB_TXNVERSION ] { 1 }
    Constant [ integer DB_GID_SIZE ] { 128 }
    Constant [ integer DB_EID_BROADCAST ] { -1 }
    Constant [ integer DB_EID_INVALID ] { -2 }
    Constant [ integer DB_REP_CLIENT ] { 1 }
    Constant [ integer DB_REP_MASTER ] { 2 }
    Constant [ integer DB_RENAMEMAGIC ] { 198656 }
    Constant [ integer DB_BTREEVERSION ] { 9 }
    Constant [ integer DB_BTREEOLDVER ] { 8 }
    Constant [ integer DB_BTREEMAGIC ] { 340322 }
    Constant [ integer DB_HASHVERSION ] { 9 }
    Constant [ integer DB_HASHOLDVER ] { 7 }
    Constant [ integer DB_HASHMAGIC ] { 398689 }
    Constant [ integer DB_QAMVERSION ] { 4 }
    Constant [ integer DB_QAMOLDVER ] { 3 }
    Constant [ integer DB_QAMMAGIC ] { 270931 }
    Constant [ integer DB_AFTER ] { 1 }
    Constant [ integer DB_APPEND ] { 2 }
    Constant [ integer DB_BEFORE ] { 3 }
    Constant [ integer DB_CONSUME ] { 4 }
    Constant [ integer DB_CONSUME_WAIT ] { 5 }
    Constant [ integer DB_CURRENT ] { 6 }
    Constant [ integer DB_FAST_STAT ] { 1 }
    Constant [ integer DB_FIRST ] { 7 }
    Constant [ integer DB_GET_BOTH ] { 8 }
    Constant [ integer DB_GET_BOTHC ] { 9 }
    Constant [ integer DB_GET_BOTH_RANGE ] { 10 }
    Constant [ integer DB_GET_RECNO ] { 11 }
    Constant [ integer DB_JOIN_ITEM ] { 12 }
    Constant [ integer DB_KEYFIRST ] { 13 }
    Constant [ integer DB_KEYLAST ] { 14 }
    Constant [ integer DB_LAST ] { 15 }
    Constant [ integer DB_NEXT ] { 16 }
    Constant [ integer DB_NEXT_DUP ] { 17 }
    Constant [ integer DB_NEXT_NODUP ] { 18 }
    Constant [ integer DB_NODUPDATA ] { 19 }
    Constant [ integer DB_NOOVERWRITE ] { 20 }
    Constant [ integer DB_NOSYNC ] { 21 }
    Constant [ integer DB_POSITION ] { 23 }
    Constant [ integer DB_PREV ] { 24 }
    Constant [ integer DB_PREV_NODUP ] { 26 }
    Constant [ integer DB_SET ] { 27 }
    Constant [ integer DB_SET_LOCK_TIMEOUT ] { 1 }
    Constant [ integer DB_SET_RANGE ] { 28 }
    Constant [ integer DB_SET_RECNO ] { 29 }
    Constant [ integer DB_SET_TXN_NOW ] { 8 }
    Constant [ integer DB_SET_TXN_TIMEOUT ] { 2 }
    Constant [ integer DB_UPDATE_SECONDARY ] { 30 }
    Constant [ integer DB_WRITECURSOR ] { 8 }
    Constant [ integer DB_WRITELOCK ] { 16 }
    Constant [ integer DB_OPFLAGS_MASK ] { 255 }
    Constant [ integer DB_MULTIPLE ] { 2048 }
    Constant [ integer DB_MULTIPLE_KEY ] { 16384 }
    Constant [ integer DB_RMW ] { 8192 }
    Constant [ integer DB_DONOTINDEX ] { -30998 }
    Constant [ integer DB_KEYEMPTY ] { -30996 }
    Constant [ integer DB_KEYEXIST ] { -30995 }
    Constant [ integer DB_LOCK_DEADLOCK ] { -30994 }
    Constant [ integer DB_LOCK_NOTGRANTED ] { -30993 }
    Constant [ integer DB_NOSERVER ] { -30990 }
    Constant [ integer DB_NOSERVER_HOME ] { -30989 }
    Constant [ integer DB_NOSERVER_ID ] { -30988 }
    Constant [ integer DB_NOTFOUND ] { -30987 }
    Constant [ integer DB_OLD_VERSION ] { -30986 }
    Constant [ integer DB_PAGE_NOTFOUND ] { -30985 }
    Constant [ integer DB_REP_DUPMASTER ] { -30984 }
    Constant [ integer DB_REP_HANDLE_DEAD ] { -30983 }
    Constant [ integer DB_REP_HOLDELECTION ] { -30982 }
    Constant [ integer DB_REP_ISPERM ] { -30980 }
    Constant [ integer DB_REP_NEWMASTER ] { -30893 }
    Constant [ integer DB_REP_NEWSITE ] { -30976 }
    Constant [ integer DB_REP_NOTPERM ] { -30975 }
    Constant [ integer DB_REP_UNAVAIL ] { -30974 }
    Constant [ integer DB_RUNRECOVERY ] { -30973 }
    Constant [ integer DB_SECONDARY_BAD ] { -30972 }
    Constant [ integer DB_VERIFY_BAD ] { -30970 }
    Constant [ integer DB_VERB_DEADLOCK ] { 1 }
    Constant [ integer DB_VERB_RECOVERY ] { 8 }
    Constant [ integer DB_VERB_REPLICATION ] { 32 }
    Constant [ integer DB_VERB_WAITSFOR ] { 32768 }
  }

  - Classes [4] {
    Class [  class db4txn ] {

      - Constants [0] {
      }

      - Static properties [0] {
      }

      - Static methods [0] {
      }

      - Properties [0] {
      }

      - Methods [8] {
        Method [  public method abort ] {
        }

        Method [  public method commit ] {
        }

        Method [  public method discard ] {
        }

        Method [  public method id ] {
        }

        Method [  public method set_timeout ] {
        }

        Method [  public method set_name ] {
        }

        Method [  public method get_name ] {
        }

        Method [  public method db4txn ] {
        }
      }
    }

    Class [  class db4cursor ] {

      - Constants [0] {
      }

      - Static properties [0] {
      }

      - Static methods [0] {
      }

      - Properties [0] {
      }

      - Methods [7] {
        Method [  public method close ] {
        }

        Method [  public method count ] {
        }

        Method [  public method del ] {
        }

        Method [  public method dup ] {
        }

        Method [  public method get ] {

          - Parameters [2] {
            Parameter #0 [  &$param0 ]
            Parameter #1 [  &$param1 ]
          }
        }

        Method [  public method put ] {
        }

        Method [  public method pget ] {

          - Parameters [2] {
            Parameter #0 [  &$param0 ]
            Parameter #1 [  &$param1 ]
          }
        }
      }
    }

    Class [  class db4 ] {

      - Constants [0] {
      }

      - Static properties [0] {
      }

      - Static methods [0] {
      }

      - Properties [0] {
      }

      - Methods [15] {
        Method [  public method db4 ] {
        }

        Method [  public method open ] {
        }

        Method [  public method close ] {
        }

        Method [  public method cursor ] {
        }

        Method [  public method del ] {
        }

        Method [  public method get ] {
        }

        Method [  public method get_encrypt_flags ] {
        }

        Method [  public method pget ] {
        }

        Method [  public method get_type ] {
        }

        Method [  public method join ] {
        }

        Method [  public method put ] {
        }

        Method [  public method set_encrypt ] {
        }

        Method [  public method stat ] {
        }

        Method [  public method sync ] {
        }

        Method [  public method truncate ] {
        }
      }
    }

    Class [  class db4env ] {

      - Constants [0] {
      }

      - Static properties [0] {
      }

      - Static methods [0] {
      }

      - Properties [0] {
      }

      - Methods [11] {
        Method [  public method db4env ] {
        }

        Method [  public method close ] {
        }

        Method [  public method dbremove ] {
        }

        Method [  public method dbrename ] {
        }

        Method [  public method get_encrypt ] {
        }

        Method [  public method open ] {
        }

        Method [  public method remove ] {
        }

        Method [  public method set_data_dir ] {
        }

        Method [  public method set_encrypt ] {
        }

        Method [  public method txn_begin ] {
        }

        Method [  public method txn_checkpoint ] {
        }
      }
    }
  }
}

The extension bundles some examples to get you started with Berkeley DB.

Even though I'd prefer a relational client-server database for most use cases I can image situations where a simple embedded solutions has its benefits, especially if combined with other Oracle Berkeley DB based applications.

In a couple of recent consulting cases, I needed a tool to analyze how a log of queries accesses indexes and tables in the database, specifically, to find out which indexes are not used. I initially hacked together something similar to Daniel Nichter’s mysqlidxchk, but using the framework provided by Maatkit, which gave me a pretty good start right out of the box. This was useful in the very tight time constraints I was under, but was not a complete solution. Alas, I could not use anything like Percona’s enhancements for finding unused indexes.

So, in response to another consultant’s customer request (and sponsorship — thank you!) I spent more time actually writing a real tool in the Maatkit style, with full tests and all the rest of the usual goodies. The resulting tool finds all indexes in a server, EXPLAINs the log of queries against the server, and reports which indexes were never selected by EXPLAIN.

Such a tool invites many interesting questions beyond “which indexes are not used.” Here are a few samples:

• Which queries have several possible execution plans?
• Which indexes are chosen instead of other indexes?
• Which queries have many variations? Only one variation?
• Which indexes are considered as alternates for other indexes?

I plan to add functionality for these kinds of questions in the future. But for right now, there’s a start on this tool in Subversion trunk, under mk-index-usage. I am interested in feedback on it; what doesn’t it handle correctly? What else could it do for you? Please post your questions and suggestions to the Maatkit mailing list, or report an issue on the Maatkit project’s issue tracker.

It’s kind of nice to be writing a single-purpose tool again. Many of the Maatkit tools are extremely complex at this point, some with more than 50 command-line options. This one doesn’t have any options at all, besides the standard ones to connect to a MySQL server.

Related posts:

1. Analyze and optimize memcached usage with Maatkit Ryan poste
2. Try mk-query-advisor, a new Maatkit tool We have an
3. Seeking input for a new tool to verify MySQL upgrades I’ve

Related posts brought to you by Yet Another Related Posts Plugin.

Maatkit does more than just MySQL. I’ve just committed a new version of mk-query-digest, a powerful log analysis tool, with support for Posgtres logs, in both syslog and stderr format. I’m hoping that people will give this a spin in the real world. I have lots of test cases but that’s never enough.

A brief tutorial:

# Get it
$ wget http://www.maatkit.org/trunk/mk-query-digest

# Run it
$ perl mk-query-digest --type pglog /path/to/log/file

# Learn about it (search for the string "pglog")
$ perldoc mk-query-digest

I’m going to close comments on this blog post so I don’t get bug reports in the comments. If you have feedback, please post it to the Maatkit mailing list, or the Maatkit issue tracker. Or reply to the thread I just started on the Postgres mailing list.

Related posts:

1. mk-query-digest now understands HTTP You used t
2. I’m a Postgres user, as it turns out Someone re
3. How MySQL really executes a query WARNING: n

Related posts brought to you by Yet Another Related Posts Plugin.

Every once in a while, I find myself needing to round a number up to the nearest even power of 10, or round a time interval to the nearest quarter of an hour, or something like that. This is actually quite simple, but for some reason I always find myself confused about how to do it. I have to reason it out all over again, instead of just remembering how to do it. Perhaps writing this blog post will help me remember next time.

The basic idea for rounding to whole multiples is to divide the number, losing precision. Then round, floor, or ceiling the resulting number, and multiply to get back to the original magnitude. For rounding to fractions, reverse the process: multiply, round and divide again.

This actually works for any programming language, not just SQL. But I find myself doing it in SQL most often.

Here’s an example of how to turn a year into a decade:

mysql> SELECT FLOOR(YEAR(NOW()) / 10) * 10 AS decade;
+--------+
| decade |
+--------+
|   2000 | 
+--------+

There are other ways to do this, of course. In this case, since the original year is expressed in decimal notation, and we are rounding down to the nearest power of 10, we could simply take the leftmost three digits and add a zero. But that wouldn’t work if we were trying to “snap” to the nearest five-year interval. The technique I showed above does:

mysql> SELECT FLOOR(YEAR(NOW()) / 5) * 5 AS half_decade;
+-------------+
| half_decade |
+-------------+
|        2005 | 
+-------------+

Let’s suppose we want to take an arbitrary number, and round it to the nearest 1/8th. In this case, we need to divide by 1/8 and then multiply by 1/8 again to get to the nearest fraction, because dividing by eight and multiplying by eight would actually get us to the nearest even power of eight. I’ll just select random numbers between zero and 100 from one of the system tables to illustrate:

mysql> SELECT ROUND((RAND() * 100) / .125) * .125 AS nearest_eighth
     > FROM mysql.help_topic LIMIT 10;
+----------------+
| nearest_eighth |
+----------------+
|         42.875 | 
|         27.875 | 
|         10.875 | 
|         70.375 | 
|         19.625 | 
|         86.875 | 
|         75.750 | 
|         17.750 | 
|         61.500 | 
|         54.500 | 
+----------------+

Of course, 1/8 is an easy number to write out in decimal: .125. It would not be so easy to write out 1/14. So naturally, we can do this by using inverses.

mysql> SELECT ROUND((RAND() * 100) * 14) / 14 AS nearest_14th
     > FROM mysql.help_topic LIMIT 10;
+--------------+
| nearest_14th |
+--------------+
|      88.0714 | 
|      76.7857 | 
|      19.6429 | 
|      67.8571 | 
|      80.2857 | 
|      98.0714 | 
|      49.2857 | 
|      52.2143 | 
|      13.3571 | 
|      10.0000 | 
+--------------+

I hope this was useful to you. I’m betting I’ll be referring back to it myself the next time I need to round a number to the nearest fraction or whole multiple of some other number.

No related posts.

Related posts brought to you by Yet Another Related Posts Plugin.