The latest from the MySQL community

Giuseppe Maxia’s new article at the MySQL DevZone rightly points out that using partitions doesn’t work by magic; as with any other enhancement, it requires a bit of know-how to realise its value. MySQL 5.1 Partitions in Practice reviews some of the basics and points out a few gotchas (if you want to partition on a DATE or DATETIME column, pay close attention here). He then provides some realistic examples showing how you actually get partition pruning working for your queries and backs it up with some interesting performance figures.

On a related note, I recently reorganised the MySQL Manual’s discussion of functions in …

I've started some attempts at coding ha_trace storage engine I've mentioned earlier. One of the first things that became apparent was that I needed a way to put a hook into table name resolution code so I can wrap tables into ha_trace objects.

The need to intercept table name resolution and do something other than looking at the .frm files is not unique to call trace engine:

• Remote storage engines would benefit also:
  • NDB has a whole chunk of code that ships .frm files from one connected mysqld instance to another. It doesn't hook into name resolution; it ships table definitions proactively, which could be nuisance if you use a new mysqld node to just connect and run a few queries
  …

A

What are the skills of a MySQL DBA? Here are my 2 cents.

1. Solid understanding of database system including design and how data is processed.
2. Handy scripting language that you can use like PERL, Python, or even Shell.
3. MySQL Backup, High Availability and Performance Tuning.
4. SQL language.

Just finished reading this:
http://dev.mysql.com/tech-resources/articles/testing-partitions-large-db.html

His comments on partitioning affecting Archive are dead on. If you can keep it from scanning then it works better (though if all engines have to scan it normally is a big factor of improvement over the other engines).
Archive's compression cannot do much with a single column table like he is using, for that it has to do too much effort (and while there is optimizations for this, I would rather just write a column oriented store some day to solve these sorts of problems).

It does make me wondering how those tests would come out with 6.0. He has a multi-processor box, and in 6.0 Archive uses multiple threads for IO to …

This article explains how to test the performance of a large database with MySQL 5.1, showing the advantages of using partitions.

The test database uses data published by the US Bureau of Transportation Statistics. Currently, the data consists of ~ 113 million records (7.5 GB data + 5.2 GB index).

The MySQL Conference & Expo web site for next year is now live.  Although the program is not completely finalized, we've got some of the basic information up and you can now register.  The preliminary schedule of sessions and tutorials have been posted.  In the coming weeks expect more info on keynotes, as well as the …

Today I’ve been checking out a new client environment. My mission is to figure out (cold) some of the characteristics of the queries being run, and particularly if they’re “good” or “bad”. In my arsenal of “tools I really want to check out” has been Ma’atkit’s Query Profiler. They’re very different tools. Ma’atkit’s query [...]

Apart from more than 60 bug fixes the upcoming MySQL Workbench 5.0.11 release will contain a few major improvements in respect to the last release two weeks ago.

• The partitioning settings are now fully supported during reverse engineering of SQL scripts and live database and CREATE / ALTER generation for synchronizations. We had a preliminary implementation but this has been replaced by full parser support.
• Addition of Standard Insert grid input. Instead of having to type in the full INSERT statements the initial/test data can now be entered by using a data grid.
• Improved formatting of generated SQL output.
• Improved GRT Shell console. This is in preparation of the upcoming tutorials on the scripting- and plugin writing possibilities

The show-stopper bug that is holding back the release is now fixed. We will run detailed tests tomorrow and if nothing else comes up will upload to the …

I have 25 GB of data and I keep the parsed slow query logs in my database. How am I going to analyze these slow query logs? Sure these logs can tell you the top 10 slow queries, but is this the only use of these logs?

What if I migrate my database to a new hardware? How do I compare the slow query logs of the two hardware? The problem is that this is a live system and therefore the amount of query are not the same, so it is like comparing apples and oranges.

After a cup of coffee, I realized that I would just do the following:

1. Get the high level average count of slow queries and average query time for 1 week before and after the migration.
2. Drill down the average count of slow queries and average query time on a server by server basis.