Those that know me, know that I‘m a little paranoid about backup automation around the house (because I‘m lazy, and that if it wasn‘t automated, it wouldn‘t get done).

I‘m also a little paranoid about offsite backups, so therefore I have a VPN between my house and my parents‘ house, and send my nightly backups (via rsync) to their place as well.

Normally, when I check my e-mail in the morning, I see a little report about what got backed up. I didn‘t notice that it was missing this morning, and instead it arrived at 3:00 in the afternoon.

That seemed a little odd, so I went and looked at my cricket instance, and low-and-behold, the outbound rsync had the 768Kbps side of my DSL connection pegged since 02:00 AM. A little “du”ing around on my backup server, and I found out that a backup had taken place in the middle of a torrent of FC6. That wouldn‘t have been a problem, except that BT stores …

On Thursday I saw something I’d not seen before. An Empty Innodb Status. Now given the amount of output normally shown it was certainly a first. And it looked like:

mysql> SHOW ENGINE INNODB STATUS;
+--------+------+--------+
| Type   | Name | Status |
+--------+------+--------+
| InnoDB |      |        |
+--------+------+--------+
1 row in set (0.03 sec)

To answer some of the most obvious questions.

• Yes it was a working existing MySQL instance, with InnoDB correctly configured. Indeed we had been benchmarking for several hours.
• MySQL Server was running, indeed a command selecting data from the mysql schema worked just fine after seeing this (All other tables were Innodb).
• Absolutely nothing in the host MySQL error log. (This was the second most disappointing aspect)
• The Process List showed two queries that had been running for some time, everything was taking  ; 1 second. …

For all those instant GUI people out there, there is an easy way to watch the present status of your MySQL Slaves using the watch command.

$ watch -n 1 -d "mysql -uroot -pxxxx mysql -e 'SHOW SLAVE STATUS\G'"

The watch provides a view of a file or command, and shows interval updates to this output (-n  seconds> option). You can also specific a granularity better then one second for example 0.5. -d also highlights the differences for you. So while you see the following output with your SHOW SLAVE STATUS, on a loaded system you will also see bin-log and relay-log changes, and perhaps Seconds_Behind_Master.

The question is, Why is Seconds_Behind_Master the last column in this display?


*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: localhost
Master_User: repl
Master_Port: …

For those that follow Daylight Savings Time in the US and Canada, watch out this weekend, because we “spring forward”!

The biggest caveat I have is: Do not arrive 1 hour late to work on Sunday or Monday.

As for MySQL, to test if you are fine, run:

SELECT @@global.time_zone;

If you get back “SYSTEM”, then MySQL is looking to the OS for timezone data, which is the default.

The real sanity check:

SELECT UNIX_TIMESTAMP('2007-03-11 02:00:00'), UNIX_TIMESTAMP('2007-03-11 03:00:00');

This should return the same value, even though you are feeding it different times, because this is when the 1 hr change occurs. If not, and you’ve played with timezone data, remember that timezone data is only loaded when MySQL starts, so if you haven’t restarted MySQL since you patched your OS, you need to do that.

This is mostly stolen from a MySQL list post I found …

With my very heavy travel load and skilling load I’ve not had time to scratch myself. It hasn’t stopped the brain working overtime on various issues including the classic find a pattern in a string starting with a wildcard character. On a recent gig I saw the true classic.


SELECT columns
FROM users
WHERE username LIKE '%str%'
OR firstname LIKE '%str%'
OR lastname LIKE '%str%'

I went through the various options and comments on leading ‘%’, OR’s, combined columns, FULLTEXT (which doesn’t work in this case), merge indexing etc, however it perplexed me that nobody has really solved this problem, or at least shared their solutions.

I have an idea, a theory and while I’d love to prove/disprove it, I simply just don’t have the time. So here are my notes, hopefully somebody can comment positively/negatively, do some research, or encourage me to pursue it more. …

Matt Asay proposes the following definition to the answer what consititudes an "open source company" that I blogged about yesterday: "An open source company is one that, as its core revenue-generating business, actively produces, distributes, and sells (or sells services around) software under an OSI-approved license."

I see a lot of merit in this definition. However it does shut out companies like EnterpriseDB that do proprietary extensions while feeding a lot of code back to the open source parent. Of course you can point to the fact that the product they sell is not open source.

This however is the only code based business model around BSD projects. Without picking favorites, I personally do appreciate the fact that BSD style projects produce an ecosystem that …

So the blogosphere seems to be in an uproar regarding what constitutes an "open source company" and what doesn't. This seems to have been spawned by a blog post by Nat Torkington. While working on the OSCON program he stumbled over what he deems as questionable entries, since he does not feel the relevant backing companies are sufficiently "open source".

Overall I agree that there is a fine balance. Now that open source has become so "posh" there are a lot people on the bandwagon that are just there for the ride and not to enable it. Obviously any company is in it for the money on some level. Personally, I am also not a follower of the open source as a political cause. With these two points as a baseline lets explore the topic some more.

To me the point of open source is to enable better software for everybody. The idea is that as I work …

I’ll be speaking at the upcoming 2007 MySQL Conference & Expo (Why they dropped the word User, who knows), this time with Guy Harrison (Author of many books including MySQL Stored Procedures). We will be talking on MySQL for Oracle DBAs and Developers.

Anyway, good friend Paul McCullagh, creator of PBXT will be also speaking on PrimeBase XT: Design and Implementation of a Transactional Storage Engine. He coined to me in an email “CU at the UC”. I’ve done a further level of refactoring, and added marketing. You …

So I am not much of a C hacker .. I never really got into pointers and stuff like that. PHP is so nice and easy. However I still want to give back to C based OSS projects I care about. A while ago I setup a wiki to track todo items for up coming PHP releases and other release related information. The page sees varying activity, but I think its helped Ilia a little bit for his 5.x releases. Its not that relevant for Derick's work on the 4.4.x branch, as that one is about bug fixing and not feature additions. Where I am really hoping it will make a big impact is with PHP 6. I am hoping that the list of todo items will assist in making sure that the next major version of PHP is all around …

At a customer site recently I came across the following table definition for an Innodb table. 140 columns, 3 part primary key totally 44 bytes, 2 million rows giving over 900MB in data size. And it had 15 indexes, totaling over 2.3GB in size. Add into the mix a Windows Server 2003 OS, 640MB innodb_buffer_pool_size setting and table scans out the warzoo. This all leads to very poor performance.

In is generally considered best practice for Innodb to use the shortest primary key as possible, and there is a clear reason for this. Innodb stores with every index the full primary key. So for example if an indexed column was 4 bytes in length, in this example the index row would be 48 bytes (before overhead). Fortunately an easy solution presented itself, because of this index storage requirement, Innodb will create an internal 6 byte primary key if none exists for a table. I of course had known about this but had never tried it in a production …