TIMESTAMP and DATETIME is how dates and times, or temporal, data is represented ion MySQL. These types are combined with a bunch of operators on these types, such as ADDATE and TIME_FORMAT and such. So is this enough to effectively manage temporal data then? Nope, in my mind, far from it.

To begin with, time isn't such a simple type of data as we might look at it. Is a date just a count of the number of seconds since 00:00:00 on Jan 1 1970 or something like that? And we then convert it to a calendar and all is fine? Is time data really linear in that sense? No, it's not. Dec 24 2010 is Christmas Eve and when you get your Christmas gifts here in Sweden. Is this significant? Also, it's a Friday. That this data is a Friday can be calculated from the date or the seconds-since-jan-1-1970 counter, but that is a different thing. Looking at that counter, you cannot see that there is no way to look at that number and figure out that this day is …

I keep a file of notes, one per line, for reference. The real reason is for source material if I ever write a 3rd edition of High Performance MySQL, but hardly a day goes by that I don’t look something up. At the very top, I have a section that I want to share with you. It’s a list of URLs that are invaluable sources of information and insight.

• http://blogs.sun.com/realneel/
• http://developer.cybozu.co.jp/kazuho/
• http://www.chriscalender.com/
• http://www.facebook.com/notes.php?id=102841356695
• http://mysqlha.blogspot.com/
…

New Community version of GreenSQL Database Firewall, version 1.3 is now available.
GreenSQL 1.3 includes new features, many bug fixes and enhancements.

In this version, GreenSQL improvers the native support for PostgreSQL (http://www.postgresql.org) databases, improvers the native support for MySQL (http://www.mysql.com) databases and provides many Protocol and Network Optimizations. The Web Based GUI usability has been improved and many bugs been fixed.

GreenSQL community version 1.3.0 improvements and enhancements include:

1. Proxies dashboard: correctly displaying the proxy current status
2. Proxies automatic reloading fixes
3. Alerts include User IP Address
4. MySQL and PostgreSQL protocol fixes
5. Network optimizations
6. Alerts – redesign and graphics
7. …

I wish I could go to PGWest this year. A lot of great work has been done on PostgreSQL in the last year and a half. There’s a new release with built-in replication, and there are in-place upgrades. That solves two of what I think are its three biggest shortcomings in many large-scale database deployments. (Lack of index-only queries is the third shortcoming.)

PGWest is the first major conference about Postgres since the 9.0 release, so all the cool stuff is happening there. I encourage MySQL users to go to it as well — there is a lot to learn from PostgreSQL. Just remember to be polite and don’t start, encourage, or tolerate any sniping between MySQL and Postgres fans.

Related posts:

1. Postgres folks, consider …

I’ve written before about how to get accepted to the conference. We want great technical submissions in a broad variety of topics, for databases well beyond MySQL. I wanted to post a quick list of things that come to my mind as good ways to get voted down or rejected out of hand. In general, I can put it this way: you are being peer-reviewed by presenters and industry experts. You need to write your proposal for the committee as well as for attendees. Lightly edited copy-and-paste from real examples:

In this tutorial, ______ will teach a condensed version of his standard commercial training workshop, a $1,500 value.

THIS IS A MUST ATTEND FOR THOSE WHO WISH TO BECOME ______ EXPERTS.

Sometimes being a committee member is fun. My all-time personal favorite, from the 2010 conference:

NETWORKING FOR TECHIES — BUILDING BUSINESS CONTACTS AT IT …

MySQL 5.1 Plugin Development

MySQL 5.1 Plugin Development, by Sergei Golubchik and Andrew Hutchings, Packt 2010. About 250 pages. (Here’s a link to the publisher’s site.)

This book is well worth reading for anyone interested in MySQL internals. I learned a lot from it. It is well-written and understandable. I cannot say that I’m planning to write storage engines or more advanced plugins, but I have a great many ideas how to improve MySQL, and I now understand more clearly which of those are suitable to write as plugins, and of what type of plugin is appropriate. I also think I have a better idea how much work these various ideas might involve.

The book begins …

I’ve written a tool to profile a process’s IO usage. It works by gathering lsof and strace from a process, and then figuring out how the file descriptors, function calls, and filenames are all related to each other. The manual page has examples. I’m curious to see how it works for you. Note that it might be a good idea to run this on your development or staging system before you go running it against your production MySQL server — there are rumors of strace misbehaving on some kernels.

Related posts:

1. How to find per-process I/O statistics on Linux
2. iopp: a tool …

Some people seem unclear about whether there will be a 2011 MySQL conference from O’Reilly. There most definitely will be, and the CfP is open. We are looking for speakers. I’m on the speaker selection committee again this year for the Nth time, and my advice from past years still stands. This year we’re also looking for a greater diversity of database products, including other relational databases, NoSQL databases, and ancillary technologies.

Related posts:

1. Postgres folks, consider the 2011 MySQL …

Let’s suppose that your backup process looks like this: you stop a replication slave, shut down MySQL, and copy away the data directory. Assume that the slave is perfect and has the same data as the master. Nothing is broken, nothing is wrong, everything is working fine. In most cases, this should work, right?

Under what kinds of circumstances will you not get all your data back if you restore the file copy and start MySQL?

Related posts:

1. Pop quiz: how can one slave break another slave
2. Progress on High Performance MySQL Backup and Recovery chapter
3. …

If you’ve ever needed to know how the data and index percentages per table engine were laid out on your MySQL server, but didn’t have the time to write out a query… here it is!

select
(select (sum(DATA_LENGTH)+sum(INDEX_LENGTH))/(POW(1024,3)) as total_size from tables) as total_size_gb,
(select sum(INDEX_LENGTH)/(POW(1024,3)) as index_size from tables) as total_index_gb,
(select sum(DATA_LENGTH)/(POW(1024,3)) as data_size from tables) as total_data_gb, 

(select ((sum(INDEX_LENGTH) / ( sum(DATA_LENGTH) + sum(INDEX_LENGTH)))*100) as perc_index from tables) as perc_index,
(select ((sum(DATA_LENGTH) / ( sum(DATA_LENGTH) + sum(INDEX_LENGTH)))*100) as perc_data from tables) as perc_data,

(select ((sum(INDEX_LENGTH) / ( sum(DATA_LENGTH) + sum(INDEX_LENGTH)))*100) as perc_index from tables where ENGINE='innodb') as innodb_perc_index,
(select ((sum(DATA_LENGTH) / ( sum(DATA_LENGTH) + sum(INDEX_LENGTH)))*100) as perc_data from tables where ENGINE='innodb') as …