What’s the difference between INT(2) and INT(20) ? Not a lot. It’s about output formatting, which you’ll never encounter when talking with the server through an API (like you do from most app languages).

The confusion stems from the fact that with CHAR(n) and VARCHAR(n), the (n) signifies the length or maximum length of that field. But for INT, the range and storage size is specified using different data types: TINYINT, SMALLINT, MEDIUMINT, INT (aka INTEGER), BIGINT.

At Open Query we tend to pick on things like INT(2) when reviewing a client’s schema, because chances are that the developers/DBAs are working under a mistaken assumption and this could cause trouble somewhere – even if not in the exact spot where we pick on it. So it’s a case of pattern recognition.

A very practical example of this comes from a client I worked with last week. I first spotted some harmless ones, we talked about it, and then we hit …

Here is some file IO performance numbers from DELL MD1120 SAS storage array. Last year I did the same test with HP P800 storage array and numbers were impressive. But when it comes to this high end storage array, few surprises.  Before getting into actual details; lets see the test stats and configuration details. System Configuration:

DELL [...]

To program is human, to instrument is divine. Complex systems that will support a heavy workload will eventually have to be tuned for it. There are two prerequisites for tuning: tunability, and measurability.

Tunability generally means configuration settings. Adding configuration settings is a sign of a humble and wise programmer. It means that the programmer acknowledges “I don’t understand how this system will be used, what environment it will run in, or even what my code really does.” Sometimes things are hard-coded. InnoDB is notorious for this, although don’t take that to mean that I think Heikki Tuuri isn’t humble and wise — nobody’s perfect. Sometimes programmers set out to create systems that are self-tuning. I’m not aware of any success stories I can point to in this regard, but I can point to plenty of failures. Perhaps I can’t think of any successes because I don’t need to.

Measurability …

Our presentation "MySQL, where are you going?" of March 25 at the OpenExpo in Bern is now available in German and English.

When you have missed it, you can download it now from here...

The video recording should be available as well soon.

Our presentation "MySQL, where are you going?" of March 25 at the OpenExpo in Bern is now available in German and English.

When you have missed it, you can download it now from here...

The video recording should be available as well soon.

The Audit API has more uses that you may think! When a statement is executed in the server, the notification function in this API will be called, and we can use that do some interesting things!

Like: Ever wanted to know what the most executed query in your running system is? I mean the information is in there somewhere, right, it's just q question of how to get at it? And frankly, I don't think the queries:
SELECT * FROM user_data WHERE user_id = 57;
and
SELECT * FROM user_data WHERE user_id = 113;
Should count as different queries? I mean, hey, the issue is that the application may be issuing the same query too many times, with different arguments mostly, but still the query:
SELECT * FROM user_data WHERE user_id = <somevalue>

Is executed too often? And if so, how can I figure it out? Well, using MySQL 5.5 with a combination of the Audit Plugin API and the INFORMATION_SCHEMA API, …

One year after the last stable release, v5.0 is now ready to use.

Changes between v5.0 beta and v5.0 stable:
* New feature: SSL options in session manager
* New feature: Startup script file in session manager
* New feature: Quick filter with "More values" auto fetches distinct cell values from table
* New feature: SQL export to clipboard
* New feature: Rewritten search and replace dialog, supporting regular expressions now
* Enhancement: Custom row height in result grids
* Enhancement: Implementing stuff for portable into main exe, so there is no need for a launcher any longer
* Many bugs (approximately 50) fixed

Changes between v4.0 and v5.0 beta:
* Completely rewritten, homebrown database communication layer, supporting Unicode from the very beginning
* Rewritten …

I ran several CPU-bound tests using sysbench and MySQL 5.1.45 with the Facebook patch for MySQL 5.1, PBXT 1.1, InnoDB plugin 1.0.6 and MyISAM. The server reports 16 x86 CPU cores. The Facebook patch has many useful changes and we are still trying to figure out whether they improve performance for sysbench.

All tests were run with a warm buffer cache for 1, 2, 4, 8, 16, 32, 64, 128, 256, 512 and 1024 threads. The data was cached by each storage engine with one exception. MyISAM does not cache data blocks.

The results are interesting. PBXT does great and is similar to InnoDB except on the oltp read-only test that uses HANDLER. In that case it is much faster at high concurrency. I am not ready to switch yet, but this is another positive step in my evaluation. I need to run a few …

At work we have been looking for tools to monitor MySQL and at the same time provide as much diagnosis information as possible upfront when an alarm is triggered. After looking around at different options, I decided to test MONyog from Webyog, the makers of the better known SQLyog. Before we go on, the customary disclaimer: This review reflects my own opinion and in no way represents any decision that my current employer may or may not make in regards of this product.
First ImpressionYou know what they say about the first impression, and in this where MONyog started with the right foot. Since it is an agent-less system, it only requires to install the RPM or untar the tarball in the server where you're going to run the monitor and launch the daemon to get started. How much faster or simpler can it be? But in order to start monitoring a server you need to do some …

Go read over on Baldy’s (my father and SmugMug’s co-founder) blog for the details.  Leave a comment there and who knows?  You might have an iPad headed your way.  :)