hackmysql.com is moving to (mt) Media Temple this weekend. Because of Memorial Day, I suspect that site traffic will be really low, so perhaps no one will even notice.

I'm trying to automate some trivial maintenance tasks for my own MySQL server, and trying also to minimize the effort, so ... Here is the recipe:

Take an excellent generalized stored procedure like the one by Konstantin Osipov, see "Dynamic SQL is Stored Procedures" on MySQLForge (example 4).
Tune it a bit so that it takes into account only non system tables (I'm trying to turn it into something similar to Microsoft's sp_MSforeachtable), here is the code:

1. DELIMITER $$
2. DROP PROCEDURE IF EXISTS `test`.`sp_4_each_table` $$
3. CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_4_each_table`(db_name VARCHAR(64), template VARCHAR(21845))
4. BEGIN
5. #
6. DECLARE done INT DEFAULT 0;
7. #
8. DECLARE tname VARCHAR(64);
9. #
10. DECLARE c …

My pal Jeff Barr of Amazon posted on his personal blog about how he was interviewed by Google and continues to get pinged by their recruiters after he already gave them a polite "no thanks". He asks whether their recruiters shouldn't maybe have way of looking up candidates in some sort of database -- you know, maybe like a search engine?

I don't think I've blogged about this yet, but I interviewed at Google from late 2005 through early 2006. The experience was pretty strange. Google kept me on the hook for five full months, having me in repeatedly for meetings with people who had no idea why they were talking to me or what to ask me, putting me in front of middle managers who would theoretically be my superiors but who had far less experience than me, handing me off from one recruiter to another, and never getting back to me when they promised to.

In one of the …

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 …