The latest from the MySQL community

MySQL AB, the developer of the world's most popular open source database, will host three European Customer Conferences next month with the theme, "Scaling the Modern Enterprise" -- in London on October 16, in Munich on October 18, and in Paris on October 23.

• MySQL Northern Europe Customer Conference: Tuesday, October 16 2007, Cavendish Conference Center, London
• MySQL Central Europe Customer Conference: Thursday, October 18 2007, Hotel Hilton Munich City, Munich
• MySQL Southern Europe Customer Conference: Tuesday, October 23 2007, Hotel Sofitel Le Parc, Paris

For conference details and registration information, please click here.

Kris brought up a simple question:

Why do I have to set the slow-query-time by hand ? Why can't the server figure out the normal query time and tell me when something is unusual slow ?

In earlier articles I already talked about that the proxy can log the query-time in microseconds and we already implemented a Query Histogram with average and max query-time. Unusual slow ...

Almost all (actually, 99.7%) of the values lie within 3 standard deviations of the mean (or between the mean minus 3 times the standard deviation and the mean plus 3 times the standard deviation). Statisticians use the following notation to represent this: μ ± 3σ.

see http://en.wikipedia.org/wiki/68-95-99.7_rule

Hmm, so everything which is slower than μ ± …

Last week I spent some time talking with Tom Kemp, CEO of Centrify. Centrify is not open source but, depending on how you look at it, either solidifies or dilutes Microsoft's hold on its customers. In Centrify's case, it extends Active Directory to platforms other than Microsoft, meaning that it:

...centrally secures...heterogeneous systems, web applications, databases and storage systems using Microsoft Active Directory. Centrify DirectControl secures...non-Microsoft platforms using the same authentication, authorization and Group Policy services deployed for your Windows environment.

In other words, it lets customers choose to leverage their Microsoft technology without being forced completely into the Microsoft ecosystem. This is a good thing. Choice is good.

…

So today I asked a question in #mysql and got no reply, so I asked the same question in #postgresql and got some interesting replies. The question was if its "good practice" to rely on the RDBMS to detect constraint violations or if one should try to avoid this by running relevant SELECT's before any writes.

Now the answer I got immediately was that everybody felt it was good practice to avoid ROLLBACKs, so they all favored SELECTs. I was not so convinced initially. The fetch requires that you maintain additional code that essentially duplicates the schema definition. I was also quite obsessed about the idea that you might have to do lock the table in order to prevent concurrent requests form interfering and causing the constraint violation to still come and bite you.

Say for example you have a table that maintains who is friends with what users. You create a relationship table and put a unique constraint to only allow every …

Yesterday, I used mysqlpdump to dump 300G of data. Today, as step two of the process, I need to restore that data into the new server. Every good dump tool needs a restore tool, so I wrote
mysqlprestore, which is really just a modification of mysqlpdump that will spawn off threads and run your restore in parallel, processing an output directory from mysqlpdump.

I really need to stick it in version control or something, and I’m sure there are bugs, but it’s working for me so far. Maybe we can merge the two into a single great tool?

…

Just read this article about running PHP applications on Phalanger and found it greatly interesting that PHP apps are often quite a bit faster running on Phalanger than on the Zend engine.  In fact the article states that phpBB serves up pages twice as fast from Phalanger than from Zend.

Also interesting is the existence of a managed MySQL extension for Phalanger.  This appears to be based on libmysql however.

Phalanger works great on .NET but also works on Mono.  This makes it very easy to use C# (or any other CLR language) to write some layers of your app, like data access code, while allowing you to write the UI in PHP.  With Phalanger you use the full range of managed components like Connector/Net, LinQ, generics, …

I mentioned the Gartner conference among a few of the upcoming open source events this fall. I've been to the past versions of this conference in Phoenix and in Orlando and it's a very good event for managers and newcomers to Open Source. It's not a hardcore technical conference like OSCON or the MySQL conference, but it's very good for its audience. Gartner has come a long way in recent years in helping IT organizations ramp up their use of open source with a carefully considered "best practices" approach backed by good research and thorough analysis. The Gartner guys that... READ MORE

General query logging can be very handy in MySQL on profuction server for various debugging needs. Unfortunately you can't switch it on and off without restarting server until MySQL 5.0.

What can you do in MySQL 5.0 and below ?

Use Our Patch - With this patch you can get all queries logged as slow queries (with times) and as you can change long-query-time online you can effectively enable and disable debug logging live. Note however this is not 100% equivalent for general query log - for example connects or queries with syntax errors will not be logged.

Enable logging to /dev/null You can enable queries to say "all_queries" log and symlink that to /dev/null. So when you will need to enable queries you can symlink it to something else and run "flush logs" so logs are …

If you like to compile MySQL from sources by yourself, for different needs, like debugging, testing etc, you probably can face this issue.

What I usually do to fast compile and test is

PLAIN TEXT SQL:

1. ./configure --prefix=/dir/to/mysql
2. make
3. make install

and then, for example, load the dump of InnoDB from previous version:

mysql testdatabase < dump.sql


I bet you will not notice all your tables now is MyISAM. Why?

PLAIN TEXT SQL:

1. Welcome TO the MySQL monitor.  Commands end WITH ; OR \g.
2. Your MySQL connection id IS 1
3. Server version: 5.1.21-beta-log Source distribution
4.  
5. Type 'help;' OR '\h' FOR help. Type '\c' TO clear the buffer.
6.  
7. mysql> SHOW engines;
8. …

We had our first ever MySQL User Conference in Japan this week, put on by our very own Larry Stefonic (Larry-san as we call him) and our all-star Japanese sales & marketing team.  We had several hundred people at this multi-day event and it was standing room only.  There were keynotes by Marten Mickos as well as Yukihiro Matsumoto (Matz) the author of Ruby, the director of HP Japan and the General Manager of Sumisho, one of our top …