Sebastian Bergmann

Some of the bloggers on Planet MySQL are sharing their proposals for the upcoming 2008 MySQL Conference & Expo. Hereby, I follow their example and give you mine.

The first proposal I submitted is on PHPUnit and will showcase Michael Lively Jr.'s port of DbUnit:

Testing PHP/MySQL Applications with PHPUnit/DbUnit

In the last decade, PHP has developed from a niche language for adding dynamic functionality to small websites to a powerful tool making strong inroads into large-scale Web systems. Critical business logic like this needs to work correctly. But how do you ensure that it does? You test it, of course.

To make code testing viable, good tool support is needed. This is where PHPUnit comes into play. It is a member of the xUnit family of testing frameworks and provides both a framework that makes the writing of tests easy as well as the functionality to easily run the tests and analyse their results.

This session introduces the audience to PHPUnit's DbUnit extension that makes the writing of tests that interact with databases more convenient and easier.

The scond proposal takes a piece of code for data partitioning that I wrote a long time ago for phpOpenTracker and relates it to MySQL 5.1's native support for this concept:

(Custom) Data Partitioning - Then & Now

Partitioning allows you to distribute portions of individual tables across a filesystem according to rules which you can set largely as needed. In effect, different portions of a table are stored as separate tables in different locations.

Some queries can be greatly optimized in virtue of the fact that data satisfying a given WHERE clause can be stored only on one or more partitions, thereby excluding any remaining partitions from the search.

Starting with version 5.1, MySQL natively supports partitioning. But partioning can even be implemented with older versions using MyISAM Merge Tables and custom application logic. This session will first present a small PHP library that rewrites SQL queries to a merge table to use as few individual tables as possible before it will give an overview of MySQL 5.1's native partioning implementation.

If you are interested in these topics, share your opinion in the comments. Thanks!

After two years in which I did not have the time to work on it, the development of phpOpenTracker is slowly commencing again. The first step to restart the development has been the migration from BerliOS to a Trac setup that is generously provided by Tobias Schlitt.

While setting up Trac it occured to me that I do not have a logo for phpOpenTracker. To get to such a logo I try something new: I ask you to participate in the phpOpenTracker Logo Contest.

Here are the rules for the contest:

• Submissions should be sent by mail to sb at sebastian-bergmann dot de. Sending just an URL is recommended. At least one PNG that can be used in the standard Trac template is required. Sending a design concept is appreciated.
• The design should convey the message of phpOpenTracker's flexibility. It must not exploit or offend anyone's sex, race or religion, be obscene or propagate violence.
• The logo shall be available in a free format (bitmap or vector) and eventually made available in source form, modifiable using Free Software (xcf, fig, etc.).
• The winning submitter must agree to put the logo under the Creative Commons Attribution-NonCommercial-NoDerivs 2.5 license and attach no other restrictions to the use of the logo. All submitters must declare that they do not infringe the rights of another party and that publishing their logos for the purpose of this contest is acceptable.
• The contest runs until April 22, 2007.

Expect more details on the development of phpOpenTracker 2.0 in this blog in the near future.

Contest Submissions

• Submission by Gregory Wilson

  I abbreviated "phpOpenTracker" to "pOT" and designed around the concept of a decorated Greek urn/pot depicting a hunt. The bottom section has a stylized web log scatterplot and footprints.

• Submission by Aaron Wormus

Although I am still busy with writing my thesis paper, I do need a distraction from time to time. Since I started getting phpOpenTracker questions again recently, I decided to (slowly) start working on phpOpenTracker 2.0 again. phpOpenTracker 2.0 will be a complete rewrite, utilizing the features of PHP 5.2 and MySQL 5.1.

The biggest problem with phpOpenTracker 1.x is that its database schema is normalized. Even simple phpOpenTracker API calls result in (possibly multiple) SQL queries that involve multiple tables. These (more often than not rather complicated) JOINs degrade in performance the more data is stored in the database. The database schema for phpOpenTracker 2.0 is denormalized and only consists of two tables (current draft).

While the denormalization should eliminate the JOIN-related performance problems, temporal partitioning of the data can further increase the performance. The backend for MySQL in phpOpenTracker 1.x optionally uses MyISAM Merge Tables for this: Rows for table X that are INSERTed on day D of month M of year Y are not INSERTed into table X directly, but into the MyISAM table X_yyyymmdd. Table X_yyyymm is a MyISAM Merge Table that aggregates all X_yyyymmdd tables of that month, X_yyyy is a MyISAM Merge Table that aggregates all X_yyyymmdd tables of that year. X finally aggregates all X_yyyymmdd tables. SELECT queries that operate on this table X and contain BETWEEN expressions that operate on the timestamp column of the table are transparently rewritten to use the smallest table possible (X_yyyymmdd, X_yyyymm, X_yyyy, X), thus reducing the amount of data that needs to be looked at to process the query.

The Partitioning feature of MySQL 5.1 should make this application-level partitioning with dates obsolete.

[Partitioning] enables distributing portions of individual tables across a filesystem, according to rules which can be set when the table is created. In effect, different portions of a table are stored as separate tables in different locations, but from the user point of view, the partitioned table is still a single table. [...] Queries against partitioned tables can take advantage of partition pruning. In some cases, this can result in query execution that is an order of magnitude faster than the same query against a non-partitioned version of the same table.

Unfortunately, the implementation of partitioning in MySQL 5.1 has one limitation that makes me doubt its usefullness for phpOpenTracker: the maximum number of partitions possible for a given table is 1024. This means that a table that is partitioned into one partition per day can only hold data for less than three years (1024 / 365 = 2.8).

Another issue I have with the partitioning feature in MySQL 5.1 is the rather verbose syntax for defining such a partition:

mysql> CREATE TABLE part_date3
    ->      (  c1 int default NULL,
    ->  c2 varchar(30) default NULL,
    ->  c3 date default NULL) engine=myisam
    ->      partition by range (to_days(c3))
    -> (PARTITION p0 VALUES LESS THAN (to_days('1995-01-01')),
    -> PARTITION p1 VALUES LESS THAN (to_days('1996-01-01')) ,
    -> PARTITION p2 VALUES LESS THAN (to_days('1997-01-01')) ,
    -> PARTITION p3 VALUES LESS THAN (to_days('1998-01-01')) ,
    -> PARTITION p4 VALUES LESS THAN (to_days('1999-01-01')) ,
    -> PARTITION p5 VALUES LESS THAN (to_days('2000-01-01')) ,
    -> PARTITION p6 VALUES LESS THAN (to_days('2001-01-01')) ,
    -> PARTITION p7 VALUES LESS THAN (to_days('2002-01-01')) ,
    -> PARTITION p8 VALUES LESS THAN (to_days('2003-01-01')) ,
    -> PARTITION p9 VALUES LESS THAN (to_days('2004-01-01')) ,
    -> PARTITION p10 VALUES LESS THAN (to_days('2010-01-01')),
    -> PARTITION p11 VALUES LESS THAN MAXVALUE );
Query OK, 0 rows affected (0.00 sec)

Why am I not able to just say: "Partition this table over column t into one partition per day"?

For over a year now I have neither worked on or worked with phpOpenTracker because I lacked the time. Although I could not test this phpOpenTracker 1.5.0 release myself I am releasing it as "stable" now because I got enough positive feedback during its long testing phase.

phpOpenTracker 1.5 will be the last release series (with bugfixes in phpOpenTracker 1.5.X releases) of phpOpenTracker 1. The little time I can invest into the phpOpenTracker project I will spend on the development of phpOpenTracker 2.

I released a second release candidate (ChangeLog) for the upcoming feature release of phpOpenTracker.

The phpOpenTracker 1.5 release series will be the last one to support version 4 of PHP and versions 3.23 and 4.0 of MySQL.