At last night's New York MySQL Meetup we had a great presentation on the new stuff in RedHat Enterprise Linux 5 from Goutham Kandiar (from RH's NY office down the block). The highlight was when he moved a running MySQL server from one laptop to another.

Now, I'm a Sales Engineer, so I can appreciate good dog & pony shows. I can really appreciate the difficulty of making a dog or pony of a database. So this was quite a treat.

It turns out all you need is some reasonably modern hardware (they had Lenovo T60s with on-chip virtualization) and xen. You can use older hardware, but you'll have to modify your kernel (which limits you to OSes where that is a possibility). Goutham had a gigabit switch, making the 256M image move over in ~4 seconds, but he told me that an ad-hoc 802.11g network would still do the trick in about half a minute.

read more

During the last days and weeks new releases of PHP (4.4.5 and 5.2.1), a new version of MySQL (5.0.33), and a new Apache (2.2.4) saw the light of the day. We're currently working on the new XAMPP version and invite you to take a closer look at the beta versions of the next XAMPP release:

http://www.apachefriends.org/en/xampp-beta.html

XAMPP BETA versions are always for testing purposes only. There will be no upgrade packages from and to beta versions. To all testers: Many thanks in advance!!

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 …

USA Today has come out with a new survey - apparently, three out of every four people make up 75% of the population --David Letterman

The GBush Jeans Co introduced in the post on the trend reports, wants to analyze sales trends. The various stores including the company stores as well as the big department stores have agreed to provide the daily sales data from the respective transaction processing systems at the end of each day. "Data profiling" helps us understand this data before the pretty business performance trend reports can be created for the data warehouse user. For data profiling we create quick and dirty reports:

1. that are a diluted version of the final trend reports that will ultimately be produced; and
   
2. with simple SQL aggregation queries, using SUM and GROUP BY, on the data loaded into the …

I've suggested before that open source companies need not migrate to Silicon Valley. It's not really an open source question, though, so much as it is a persistent belief that to be big/important you must live in a big/important city.

The WSJ took on this topic in yesterday's Journal in an op-ed piece called "The Myth of 'Superstar' Cities." It begins:

These seem the best of times for America's elite cities. Wall Street's 2006 megabonuses created thousands of instant millionaires, and, with their venture-fund soulmates in places like San Francisco, Boston and Greenwich, the best people are prowling for Ferraris, planes, multimillion-dollar condos, the newest $200 lunch place and the latest in high fashion. In some markets, office …

Yet another industry alliance has formed (Open Solutions Alliance), in the tradition of OSDL. This one, however, promises not to fail where OSDL did (i.e., it will not focus on just one technology) because it will provide

"...business level advocacy. We need to talk to sets of customers about open source applications, make them aware of what's out there."Thus spake Barry Klawans to Dana.

It's not a bad goal. My problem with the premise is that I haven't met any enterprises that need help buying into open source. Quite the opposite. The problem we're having is managing growth, not sparking it. If the solution to be solved is customers buying into open source applications, then there is …

I hope this is my last post about this topic. It seems we've resolved all of the issues and I'll now describe the user-visible consequences.

To recall, we're talking about subquery predicates in form

(oe1, ..., oeN) [NOT] IN (SELECT ie1, ..., ieN FROM ... )

that are located in a context where it matters if the predicate's result is NULL or FALSE. The name "oe" stands for "outer expression", ie stands for "inner expression".

MySQL evaluates queries "from outside to inside", i.e. we first get the values of (oe1, .. oeN) and then we run the subquery and capture the rows it produces. An apparent and very useful optimization is to "inform" the subquery that we're looking only for rows that have "ie1=oe1", "ie2=oe2" and so on. This is done by injecting appropriate equalities into subquery's WHERE (or HAVING) clause. That is,

(oe1, ..., oeN) [NOT] IN (SELECT ie1, ..., ieN FROM ... )

becomes …

I was working with customer today which has MySQL on a system with some 64GB or RAM running MyISAM, so they set key_buffer_size to 16G... and every few days MySQL crashes.

Why ? Because key_buffer_size over 4GB in size is not really supported (checked with latest and greatest MySQL Enterprise 5.0.34). It works just fine until you have less than 4GB worth of key cache used and then it would crash.

If you do not know about this limitation it may be quite complicated to figure out what the problem is as MySQL will be crashing on random statements associated to random tables.

What I find interesting is this bug was known for years and not only fix was not made available but even maximum key_buffer_size was not restricted to safe limit. Former would be close to changing single line of code with variable values limits, might be a bit more than that but it should …

MySQL Replication is asynchronous which causes problems if you would like to use MySQL Slave as it can contain stale data. It is true delay is often insignificant but in times of heavy load or in case you was running some heavy queries on the master which not take time to replicate to the slave replication lag can be significant. Also even very small lag can cause the problems - for example you've posted comment on the blog and on next page reload you do not see it as it was read from the slave millisecond later.... this is something you would not like to happen.

I'll list some techniques here which I found to be helpful for offloading load to the slave without causing application to be have crazy. The same approach can be used in Master-Master replication in Active-Passive mode, just think about passive node as a slave.

Query type based distribution Elect some queries as "time critical" and other queries as "non …

I thought “svn”, I typed “cvs”. Hrrm… sounds about right.

In other revision control news, using quilt to manage work-in-progress patches in conjunction with BK is proving really, really great. I feel like an idiot having lived this long and not worked this way.

I have a feeling that if git was being used I’d just do everything there as it’s so quick anyway. I haven’t used bzr on these sorts of size of repos yet, but it should be good too.