Friday, I helped a friend who is working a MySQL gig to optimize a query that should have been way fast, but was taking about 100 seconds. Brian even took a look and helped with it. I learned today what the real problem was. The whole system was running on a single ec2-small instance. And by "whole system", I mean a test mysql server, the production mysql server, a test apache server, and the production apache server. All on one ec2-small.

Talk about doing it wrong.

TIMESTAMP is interesting in that it can give you an easy way of keeping track of when was the last time a row was modified, with a few caveats, listed below. The tests were run on 5.1.30-community MySQL Community Server (GPL).

• By default, TIMESTAMP is NOT NULL. Inserting a NULL value causes it to store the current DATETIME;

  mysql> CREATE TABLE t1 (c1 TIMESTAMP);
  Query OK, 0 rows affected (0.11 sec)
  
  mysql> INSERT INTO t1 VALUES (NULL);
  Query OK, 1 row affected (0.03 sec)
  
  mysql> SELECT * FROM t1;
  +---------------------+
  | c1                  |
  +---------------------+
  | 2008-12-30 20:42:25 |
  +---------------------+
  1 row in set (0.00 sec)
  
  mysql> CREATE TABLE t2 (c1 TIMESTAMP, c2 TIMESTAMP);
  Query OK, 0 rows affected (0.06 sec)
  
  mysql> INSERT INTO t2 VALUES (NULL, NULL);
  Query OK, 1 row affected (0.02 sec)
  
  mysql> SELECT * FROM t2;
  +---------------------+---------------------+
  | c1                  | c2                  | …

I spent the last two weeks offline in the Indian Ocean (not all the time as literally as in the picture on the left, though). This triggered a bit of long term thinking and observations, some of which I’ve already published my ten New Year Resolutions in Swedish, in German and in English, respectively. Some of these are purely private, many are inspired by work at MySQL and Sun.

I won’t spam Planet MySQL with all of the resolutions, but here’s an excerpt to tease you into reading the actual blog entry: …

Listening to Sheeri's presentation on MySQL 5.1, I saw that there are a few questions left unanswered. I am listing here some of the questions that I found interesting, plus a few from an early webinar on the same topic.

Q: does Partitioning physically split data?

A: No. Some engines (MyISAM, Archive) do a physical split, but this is not necessary, as you see if you apply partitioning to a InnoDB table. Partitioning is a logical split of data, for easy retrieval. It is completely transparent to the user.

Q: Can you set partitions to different servers?

A: No. Partitions are logical parts of one table within one server. Partitioning through the Federated engine is not supported.

Q: How efficient are Row-Based …

Since Kore, Lars and Marcus forced me to write this:

• Sebastian's first foreign language was Latin, for me Latin was the reason to start programming (by trying to hack the vocabulary trainer my brother wrote, so I learned Basic instead of Latin)
• I started PHP since I was too dumb to get my Perl CGI scripts working after our home server crashed
• I still have more Perl books than PHP books
• Compiling PHP 4.0 on our Pentium 90 home server took a night or so - and often forgot to activate some …

MyISAM InnoDB Default since version 3.23 Relatively newer Files storage: FRM: table definition, MYD: Actual Data MYI: Index File FRM: table definition .ibd file (concept of table space) or single…

The post Difference MyISAM and InnoDB Storage Engines Mysql first appeared on Change Is Inevitable.

Quick tip - suppose you need the result set of a MySQL stored procedure in your Pentaho Data Integration (a.k.a. Kettle) Transformation, what do you do?

A Call DB Procedure sounds promising, but as it turns out, you can't use it to retrieve any result sets. Rather, this type of step is meant have an input stream from another source:

• 
  
• drive stored procedure execution presumably for some useful side-effect
  
• invoke a database stored function and obtain the scalar result
  

So, what can we do? The answer is simpler than might be expected.

Just use an ordinary …

This is a quick post to show how one can issue commands to telnet session from a shell script or command line with out going into interactive mode. I use this to get stats from our memcache servers or issue a flush_all via telnet from a script/cron.

So without further delay, following command will telnet to local memcached server on port 11211 and issue one of the memcached commands, stats

(sleep .5;echo stats) | telnet localhost 11211
You may have to play with the sleep timer to get it to work for your environment but in our .5 was the sweet spot. Good luck and let me know if you have another shell command. Obviously we can do this from perl, php, python, etc but the beauty of this is that you do not need any other dependencies plus its a very short command. …

In a nutshell: What’s New in MySQL 5.1.

Release notes: Changes in release 5.1.x (Production).

And yes, very early on (at about two minutes in), I talk about my take on Monty’s controversial post at Oops, we did it again.

To play the video directly, go to http://technocation.org/node/663/play. To download the 146 Mb video to your computer for offline playback, go to http://technocation.org/node/663/download. The slides …

Welcome, readers, to the 129th edition of Log Buffer, the weekly review of database blogs. Welcome also to 2009, so fresh it still has that wonderful new year smell. Let’s take ‘er out on the road and see what she can do.

Starting with Oracle (and reaching back here a little into the holidays), Doug Burns had performance on his mind—to be precise, his own at UKOUG; and some performance-related blogs he’s found worthwhile. Doug’s recommendations themselves always perform well.

Tanel Poder was on the same road. In his item, …