A customer issue has drawn my attention to this this pecularity: if partitioning is done by the value of some function, then partition pruning module will make use of comparisons of the partitioning column(s), but not of comparisons of the value of the partitioning function. Here is an example:

CREATE TABLE t1 ( recdate  DATETIME NOT NULL, ... )
PARTITION BY RANGE( TO_DAYS(recdate) ) (
  PARTITION p0 VALUES LESS THAN ( TO_DAYS('2007-01-01') ),
  PARTITION p1 VALUES LESS THAN ( TO_DAYS('2007-02-01') ),
  ...
);

EXPLAIN PARTITIONS SELECT * FROM t1 WHERE recdate='2007-01-15';
+----+-------------+-------+------------+------+-...
| id | select_type | table | partitions | type |
+----+-------------+-------+------------+------+-...
|  1 | SIMPLE      | t1    | p1         | ALL  |
+----+-------------+-------+------------+------+-...

EXPLAIN PARTITIONS SELECT * FROM t1 WHERE TO_DAYS(recdate)=TO_DAYS('2007-01-15'); …

1. How to build PBXT with MySQL
2. How to use SysBench
3. PBXT code download
4. SysBench code download
5. MySQL5.1 code download

This project is one of the Google Summer of Code 2007 projects for MySQL.

I am a graduate student of University of Toronto, and my mentor of this project is Paul McCullagh.

The goal of this project to provide a performance analysis tool for a MySQL storage engine PrimeBase XT. We aim to help developers to locate the bottleneck of the system. In contrast to traditional profiling tools, we focus on how to capture the impact of the resource contention through measuring the time spent on waiting critical resources, such as I/O, memory and locks. We also try to provide context information to help developers to identify the critical path.

Apparently, there’s been an outstanding bug for nearly two years for MySQL to add support for millisecond storage in DATETIME and TIME data types.

A microseconds part is allowable in temporal values in some contexts, such as in literal values, and in the arguments to or return values from some temporal functions. Microseconds are specified as a trailing .uuuuuu part in the value. Example:

…

However, microseconds cannot be stored into a column of any temporal data type. Any microseconds part is discarded.

What’s this about? I have to admit that I’ve known about this problem for about the same amount of time (probably three years).

At Rojo we used BIGINTs as timestamps which provided millisecond …

Following my article Everything fails, Monitor Everything, and some inquiries, I’ve made some small modifications to my initially hourly script. This script is still a quick and dirty trial of what I’m wanting to develop, but in true Guy Kawasaki terms “5. Don?t worry, be crappy”. It works for now, and enables me to determine what works and what doesn’t.

My goals are Data Collection, Data Analysis and Data Presentation. This is the start of Data Collection. So now I get the following files:

• os.vmstat.070524.122054.log
• os.ps.070524.122054.log
• mysql.innodbstatus.070524.122054.log
• mysql.processlist.070524.122054.log
• mysql.status.070524.122054.log
• mysql.tablestatus.070524.122054.log
• mysql.tablestatus.vertical.070524.122054.log
• mysql.variables.070524.122054.log
…

mysqlreport v3.2 has been released. Mark Leith’s post, Aggregating SHOW STATUS Output, reminded me that a user had asked me a few months ago to do this for mysqlreport. I forgot until Mark’s post.

I have finished implementing this feature with mysqlreport. The new version has three new command line options: –relative (-r), –report-count (-c), and –detach. All of these are explained in the doc. This feature required substantial alterations to the script, so please tell me if anything doesn’t work.

To proceed from one truth to another, and connect distant propositions by regular consequences, is the great prerogative of man --Samuel Johnson

The simple perl script proof of concept demonstrated here, performs data type validation on the provided data file. The script creates an output file with bad data attributes substituted by default values. The data type specification and default values are read from a specification file. A database table can be easily substituted for the specification file to make this process more robust. This functionality is provided out of the box by the commercial ETL tools.

#!/usr/local/bin/perl -w
use strict;
#
# http://www.dwoptimize.com/2007/05/data-type-validation-using-regular.html
# jag.singh@dwoptimize.com
# Version 0.3
#   feedback incorporated …

Here are some useful firefox keyboard shorcuts for urls:


Ctrl + enter : to complete a .com url . You can type "google" in the url bar and then ctrl + enter to complete the "www.google.com".

Ctrl + shift + enter : to complete a .org url . You can type "mysql" in the url bar and then ctrl + shift + enter to complete the "www.mysql.org".

F6: to select or move the cursor to the address in the url bar.

Ctrl + k : to move the cursor to the search box.

In a previous post, Thumper: Putting Blastwave on ZFS, I quickly saw some information and jumped to completely the wrong conclusion. In the comments, Boyd kindly pointed out that I should probably investigate it a little more thoroughly. So I have. Just to recap, effectively I am trying to install software, with pkgadd onto a ZFS filesystem. The full filesystem is 17 terabytes, and still has 17TB available. The steps I followed were:

zfs create zpool1/software
zfs create zpool1/software/blastwave
zfs set mountpoint=/opt/csw zpool1/software/blastwave
pkgadd -d http://www.blastwave.org/pkg_get.pkg

And the errors I was receiving were along the lines of:

pkgadd: ERROR: unable to create package object </opt/csw/bin>.
    pathname does not exist
    pathname does not exist
    unable to fix attributes …

Well I have to say, Skype is a piece of crap. Between the abysmal sound quality/compression artifacting and the compatibility issues with my Gentoo install we had a nightmare trying to communicate, eventually resorting to text chat.

I think we clarified a few ideas during this session, mostly spec-related but with one or two interesting ideas cropping up.

One notable thought was local auditing; how do we watch local administrators' queries? We can't capture packets from their session since they're working on the server itself so what can we do? This may involve a whole other branch of code that allows us to send local server events to the auditing server for storage and flagging. I'm not entirely sure how we're supposed to sit between the user and the mysql server, especially if the user is a domain/local administrator and has some determination not to get caught!

Another interesting thought was rules; …