To check for query performance improvements followed indexing/query changes or MySQL configuration changes our customers often decide to run the query and see if there is any significant improvement.

Leaving aside question of checking single query alone might not be the best way to see real improvement for your application, the problem they usually run into is - query speed when it is run first time may be very different from second and further runs, especially when it comes to disk IO workloads.

The attempt to fix it is often using SELECT SQL_NO_CACHE or disabling QueryCache for the test. This works by blocking query cache but still repeated runs execute much faster, why ?

The reason is - there are all kind of other caches and QueryCache is only first in line. There is also all kinds of page caches inside MySQL (key_buffer and …

First of all: Happy New Year everyone! For reasons I don't understand, or maybe no reason at all, there are a couple thousand of you who end up here every day (actually, closer to 4000 a day but many end up on old archive pages and, I'm guessing, quickly leave).

By now you've probably seen the oh so promising Apple iPhone. Woah, does that thing look neat!

I'm attached to my Treo, but I think I found my upgrade. I might even switch to Cingular for it, at least if they get an unlimited data plan that's not vastly super-duper overpriced. Heck, with the beautiful "visual voicemail" feature I might switch to Cingular anyway.

One thing that I'll occasionally will be missing is, of course, an ssh client which brings me to the big question: Will Apple provide an SDK and let us install …

Sean McCown writes in the latest Database Underground about how DBAs should try and make things better for users of an organization. I think Sean hits upon an important point not just for DBAs but for all IT workers. Too often IT is seen as a cost center, an impediment, as something as necessary for an organization but not necessarily glorified or appreciated. As technicians we may find ourselves amazed at how the business people don't see value in what we do, we may groan and complain about they don't understand how hard we work or we may think they don't care. From the business perspective, though, it's hard to understand why it takes so much to do some of the things we do. But then again, we sometimes marvel at some of the things the business folks do and we shake our …

EnterpriseDB Closes Extraordinary Year, EnterpriseDB (Press Release)

Compiere Launches Expanded Partner Program to Extend Global Reach, Compiere (Press Release)

Key Players In The Linux and Open Source Industry Sign On As Sponsors For LinuxWorld OpenSolutions Summit, LinuxWorld (Press Release)

Decision Intelligence Joins Pentaho Partner Program, Pentaho (Press Release)

Open …

Normally I ignore the spam that makes it thru to my inbox. It's usually pretty easy to pick out by reading the subject and sender's address (or name). I'll just mark it as spam and go on with life. But this particular gem came thru a few days ago with a subject of "MySQL Performance and Tuning...", a topic that's been near and dear to my heart.

click to see full-sized copy

So you can imagine how pissed I was to open the message, only to be confronted by one of those annoying large image-looking spam messages.

Worst of all is their insulting language at the bottom of the spam:

If you no longer wish to receive these emails...

It's …

So I go to my favorite coffee place, Starbucks, pretty much every day. I always get the same thing which recent has been: tall vanilla latte, %, no foam. And they always ask me for my name which I always respond by saying: Sunny. Most of the time, not all, they get my name wrong. I don’t understand why is it so hard for people to write down Sunny. I am ok even if they just write Sonny, which is the most common misspelling of my name, but Starbucks staff always find creative ways of spelling “Sunny”.

I have seen following names.. and I am not kidding or making up any of them. All the people who go with me to Starbucks know this! Here is the list I have so far: Sunny, Sonny, Suni, Sunee, Suuniee, Suney, Suny and the one I have in front of me which made me blog this, Sunni.

So if any Starbucks staff reading this, could you explain to me how come you guys are so bad with spelling names? Is quality of hiring staff is so low that …

Unless you have your head under a rock, you know that Apple has finally released their iPhone today.

http://www.apple.com/iphone/

According to the press information this phone is running OSX.

Does this mean that I will be able to write an application via Xcode using Objective C for the device?

Cell phones are a pain to program for. Even my Treo, which may soon sit in the bottom of a trashcan if Apple's phone is any good, is a pain to program for. Palm was never that friendly for developers. It certainly is better then what a lot of other providers have made available, but it is not as easy as writing a standalone GUI on a Mac, or coming up with a web interface.

But Objective C and Xcode? Widgets?

Now that would be a pretty incredible development platform to use with cell phones. Porting MySQL to …

What did we learn from 2006? For me, it's the realization that our job as developers is simply to create "great software"--software that can address the needs of real users, whether it's open source or commercial.

The open source model has several strong advantages in creating better software. It allows us to stay close to our users, listen to them, and incorporate features as they are really needed. The flexible development style and collaborative (rather than adversarial) relationship between users and developers could create better results at dramatically lower costs. In addition, our users often can help us to innovate in wholly unexpected directions.

But commercial software also has its advantages. The biggest advantage of the commercial software model is being able to spread the development costs across a large number of users. It can also support a more focused development approach and, if properly directed, …

I had a fun case today.

There is set of cache tables which cache certain content in MyISAM tables and queries for these tables such as:

PLAIN TEXT SQL:

1. SELECT DATA FROM cache0003 WHERE `key`=2342526263 AND real_key='cp_140797_6460aad5d2e50d3e859e8649007686ac';

The "key" is CRC32 of the real key which is used to keep index size as small as possible so if we have a cache miss we can in most case learn it without going to the disk.

So far so good.

The problem I discovered however is some of these queries would take enormous amount of time while CRC32 conflicts are really rare.

Looking deep into the problem I found out PHP and MySQL are both to blame. PHP is to blame because in 32bit PHP version result of crc32() function was returned as signed integer, in 64bit build of same PHP version it became signed.

The system worked on 32bit platform initially …

One of the features not yet mentioned that is in 5.1 is Federated's new "CREATE SERVER" command.

The synopsis?

Tired of typing URL's for Federated tables? AKA:

CREATE TABLE A (
`a_id` int(20) NOT NULL,
`some_string` varchar(128) NOT NULL default ''
)
ENGINE="FEDERATED" CONNECTION='mysql://joe@foo.example.com:3306/ schema_a/A';

With 5.1 you have a new option, the "CREATE SERVER" command.

CREATE SERVER 'master_database' foreign data wrapper 'mysql' options
(HOST '10.0.2.128',
DATABASE 'schema_a',
USER 'joe',
PASSWORD '',
PORT 3306,
SOCKET '',
OWNER 'root');

For the create table all you then need to do is:
CREATE TABLE A (
`a_id` int(20) NOT NULL,
`some_string` varchar(128) NOT NULL default ''
)
ENGINE="FEDERATED" CONNECTION='master_database'; …