The latest from the MySQL community

If you’re new to MySQL, you might not know that in the default configuration ‘a’='A’. Ie, string comparisons are by default case-insensitive. If this is a surprise to you, read up on Chapter 9 of the online manual, on character sets and collations.

The default character set is latin1 and the default collation is latin1_swedish_ci (’ci’ stands for ‘case-insensitive). If you don’t want ‘a’ to equal ‘A’, you can change this by setting the variables ‘character-set-server’ and ‘collation-server’ in your config file or your startup options. For example, we want to support unicode, so we use character-set-server=’utf8′ in our config file. We also added the following (in the [mysqld] section) so that clients will use the right character set and collation, too: init_connect=’set names utf8; set collation_connection=utf8_bin’.

On the other hand, by default in linux, …

MySQL is not so heavily-instrumented as Oracle. (Back to tuning using ratios rather than the wait interface… sigh…) But it does offer (among other tools) the slow query log. Turn it on (put ‘log_slow_queries‘ in your config file), and all queries that take longer than long_query_time seconds (also set in your config file) will be logged to a slow query log file, along with the time they took to execute. Then you can use mysqldumpslow to analyze the output. You can see, for example, which slow queries are taking the most cumulative time on your server, or are being executed most frequently. (The manual doesn’t say much about it, and mysqldumpslow ––help doesn’t give too much help, but I think mysqldumpslow -s t and mysqldumpslow -s c do that.)

If you use InnoDB, though (as we do), be aware that the “lock time” logged in the slow query log only counts time for …

I usually get a feel for "what's hot" in the marketplace by the number of books on the shelves at my favorite bookstore. A couple years ago there was six shelves at my Borders store filled with books on Oracle. Oracle for Dummies, PL/SQL, and scores of Certification books. I was quite surprised to find only three books on Oracle at the same store this past weekend.

MySQL still has some respect with about a 1/2 shelf. .Net clocked in with about 5 or 6 shelves with a smattering here and there of Java and the associated technologies. Good old Perl shared half a shelf with PHP. But I feel left behind because I don't know Excel Macros (about 12 shelves).

Maybe Oracle has really achieved a self-tuning, self-managing database and we don't need books anymore.

Please excuse me while I apply four more patches to my "up-to-date" 10.2.0.3 installation.

Dear data integration fans,

Once in a while, there are discussions on various blogs (usually with me smack in the middle of it) debating the differences between code generation and model based execution, how this impacts the way we approach databases, the open nature of it all, etc.

With this blog entry I want to push the notion that Pentaho Data Integration (Kettle) didn’t just evolve by chance into the state it is today as a streaming, metadata driven, model based engine. I made some careful design choices early on…

Open as possible

The goal of Kettle from the beginning was to be as open as possible. My definition of “as open as possible” included:

• open source with an LGPL license (see this JBoss link [PDF] for a nice explanation)
…

There's more noise about Solaris competing with Linux over at slashdot. I can understand the argument that Sun should direct their efforts to Linux. You know, Peace, Love, Linux. But which business would throw away their crown jewel to compete in the delivery of a product that already has a leading vendor (by some distance over #2)? Can you imagine asking MySQL to get into the Oracle support business (imagining that Oracle open sources their products)? Or let's pressure Alfresco to jump into the (future :-) open sourced MS SharePoint project. Yes, I know, it's ludicrous to ask MySQL or... READ MORE

What you see in the graph is a comparison of the read() versus
aio_read() in Archive. This is using Fedora Core 7. The test is
pretty simple. Read 10,000 rows, 1000 times per user. Increase the
number of users. No aggregate function was run, I did not see where
that was really needed for this test (aggregate functions/queries are
the main SELECT type used against Archive tables).

From the graph it is pretty obvious that aio_read() makes a
difference. I am going to run this later on the T1000 and see what
happens there (I've got other tests running on it right now so no
comparison for the moment...).

The open question for me now is how this compares to my hand written
AIO method. This code is pretty much ready to go (it won't be in MySQL 5.1,
it will got in the next version (which is either 5.2 or 6.0)). The
burnin test has been running …

And we thought it was all about peace, love, and...Solaris. But no, Sun is gearing up for one of the classic open source battles. We've had the various Linux distributions duking it out, and we've had MySQL versus PostgreSQL. Now we're getting Solaris versus Linux, and this is a fight that I believe may actually be worth having.

Why? Because it means more choice for customers, and not just any choice, but a choice between two exceptional operating systems, both completely open source.

Sun's strategy is becoming clearer with Solaris:

...

The Date dimension is a well known construct in general data warehousing. In many cases, the data for a date dimension is generated using a database stored procedure or shell-script.

Another approach to obtain the data for a date dimension is to generate it using an ETL tool like Pentaho Data Integration, a.k.a. Kettle. I think this approach makes sense for a number of reasons:

• 
  
• When you tend to use a particular ETL tool, you will be able to reuse the date dimension generator over an over, and on different database platforms.
  
• You won't need special database privileges beyond the ones you need …

New MySQL 6.0 Alpha Release

If you are reading this, chances are you already know the advantages of open source. Open source is free, benefits from a community of contributors, it's well tested, the list of benefits is long... right?

Well, I agree. It's hard to believe that someone would choose anything but apache to run a web server. But software runs in a lot of different places, different industries and cultures. In the world of billing, we are about 15 years behind the industry standard, as far as open source adoption goes. The other day I had in my hands an issue of a billing magazine (Billing & OSS World, I think is its name). They had an article on open source and it was half funny, half depressing. Open source was synonym of Linux, that's all. Like there is nothing else in the open source world but Linux.

In about two months, jbilling will be launching a new major release that will include a package of components that address es the needs of …