I like to write tools that make hard things easy, when possible. By and large, MySQL is easy and simple. But some simple things are too hard with MySQL. I want to change that, at least for the things that matter the most to me, and which I think I know how to fix.

I will probably write a lot about this. I have already written a number of rants blog posts about the lack of instrumentation in MySQL, and that is where I’ll probably continue to put most of my energy.

To begin with, imagine this simple scenario. You are a remote DBA. Your client says “New Relic is showing periods of slow response time from the database.” You connect to MySQL at the command line and try to troubleshoot. How do you catch the problem in action, from within the database itself? The following are no good:

• It doesn’t count to see the problem two minutes later by observing the application tier, as New Relic does. That’s too late, and …

This blog posts explains how to add a new line in strings in MySQL Stored Procedures and how to output the result using the MySQL client tool.

Today I was fooling around with some stored procedure making it more fancy and stuff. What I wanted was the OUT variable to contain a newline. Easy of course, using CONCAT:

mysql> SELECT CONCAT('foo','\n','bar');
+--------------------------+
| CONCAT('foo','\n','bar') |
+--------------------------+
| foo
bar                  |
+--------------------------+

Now, if youconcat strings in a stored procedure, it …

So Wall Street Journal says that SAP is buying Sybase.

Not many independent database vendors left now.

My thinking is that SAP want to be sure that they had control of the underlying platform, but then again that's what I thought MaxDB was for.

Taking a quick look at http://www.sdn.sap.com/irj/sdn/dbos and you can see that SAP supports a pretty varied collection of databases:

• Various flavours of DB2
• Oracle
• SQL Server
  

  read more

In the past, I've joked with colleagues about the trap doors hidden in the TIMESTAMP column, and today I fell through one of them.

I think there are some advantages to using timestamps, especially in large tables as part of a key. If you are not too worried about its limited range, the meager 4 bytes it uses can be an advantage.

But there are some issues with this datatype, since it also comes with some default behaviors to watch out for.

Today I was adding in some data from a stage table to an aggregation table using INSERT...SELECT...ON DUPLICATE KEY UPDATE. In my case, both tables had identical structure, including column names.

I've worked up an example of todays puzzle. Here is the prerequisite table definitions and pre-population:

create table jd_stage (
customer_id int not null ,
order_date timestamp not null ,
order_amount decimal(10,4) not null …

I promised in last week's post on "How to Kill Good Ideas" to follow up with some ways that more constructively help create new ideas.  The first of these is taken from an idea by Mats Kindahl's post of two other ways to kill ideas. Without further delay, here they are...

1. Make it safe to contribute ideas
   The best way to do this is encourage risk taking and acknowledge that some ideas will fail and that's acceptable.  The people I know who are the most creative are also the most prolific when it comes to idea generation.  And some of those ideas are, objectively speaking, total crap.  But there are so many good ideas generated in the process, it really doesn't matter.
2. …

I like to write tools that make hard things easy, when possible. By and large, MySQL is easy and simple. But some simple things are too hard with MySQL. I want to change that, at least for the things that matter the most to me, and which I think I know how to fix. I will probably write a lot about this. I have already written a number of rants blog posts about the lack of instrumentation in MySQL, and that is where I’ll probably continue to put most of my energy.

The next MySQL User Group meeting in Moscow will happen at 7 PM on May 17th, in Oksana Hotel Moscow (VDNX metro station).
We expect a few server engineers to be there, including a couple of prominent "defectors" :-) from MariaDB - Monty and Sergey Petrunia.
You can find more information (in Russian) at the Moscow MySQL User Group mailing list.
Please RSVP either here, or in the list.
Thanks to PHPClub and personally to Alexandr Smirnov for making it happen!

How do you get more throughput from a workload that has a lot of concurrency and transactions that contend on updating the same rows? Move the updates for the contended rows to the end of the transaction. This is a good problem to prevent as it is very hard to diagnose on production servers. I will revisit that in another post. Stored procedures can also improve throughput as they remove sources of think time that extend transaction duration.

Overview I ran a benchmark to identify the benefit from doing this. The test was run for unmodified MySQL 5.0.84 and 5.0.84 with the Facebook patch. The patch makes a big difference because it has a fix for bug 49047 to make deadlock detection faster. That will also be in MySQL 5.1.46 or 5.1.47. The patch also allows for deadlock detection to be disabled which …

I have just released PBXT 1.0.11, which I have titled "Pre-GA". Going by our internal tests, and all instances of PBXT in production and testing by the community this is a GA version!

However, although PBXT has 1000's of instances in production, it is not used in very diverse applications. So I am waiting for wider testing and usage before removing the "Pre" prefix.

You can download the source code from primebase.org, or pull it straight from Launchpad. Here are instructions how to compile and build the engine with MySQL. PBXT builds with MySQL 5.1.46 GA, and earlier 5.1 versions.

If you don't want to compile it yourself, PBXT 1.0.11 will soon …