The latest from the MySQL community

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 ? ± 3?. is …

What is the mySQL Optimizer? Well it's the part of the mysql query engine that determines what index to use, based on given sql. It sucks in every version that I tested, which is

3.23
4.0
4.1
5.0
5.1


I find that in nearly every app that I've end up writing I need to create my own optimizer to determine what index to use.

Why?


Well, imagine this. Your table has 30 indexes and many of the prefixes of compound indexes are the same. The reason is because given a question you want to sort the data quickly in different views. The optimizer sucks at figuring out which index to use under these cases (and many others)

For example:

SELECT * FROM SomeTable WHERE owner_id = ? AND perms IN (0,1) AND karma IN (0,1) ORDER by date_create DESC, photo_id DESC limit 10


Now the table contains an index on …

Spent the day giving talks at the MySQL Korean User Conference.

A number of surprises:

1) A lot of the audience spoke english.
2) I got enough advanced questions to realize that this was not an
audience who had "just heard of" MySQL. They were users.
3) It was nice to talk to several people about projects that they
told me about last year when I was here for Linux World.
4) Even when the person doesn't speak english, I can figure out what
they are saying if they talk about MySQL. The nouns are all the same :)

Tomorrow I get a bit of spare time before my flight to walk around
the city (which is awesome, since I really like Seoul). I always find
the people to be very friendly here.






…

One of my clients asked me today to make their MySQL installation go back to default database install. Basically they wanted me to get rid of all their databases (in this case test databases) so they can start fresh and go live with only the databases they needed. So here are the steps [...] …

"MySQL Proxy is a simple program that sits between your client and MySQL server(s) that can monitor, analyze or transform their communication. Its flexibility allows for unlimited uses; common ones include: load balancing; failover; query analysis; query filtering and modification; and many more."

sounds like a cool idea. I cannot wait to tried it out. Cannot find a FC5 binary, so I downloaded a FC4 version a few days ago. Easy enough:

[vandba@cooler sbin]# ./mysql-proxy --help
Usage:
mysql-proxy [OPTION...] - MySQL Proxy

Help Options:
-?, --help Show help options
--help-all Show all help options
--help-admin Show options for the admin-module
--help-proxy Show options for the proxy-module

Application Options:
--version Show version

However, when I trying to connect to it from another server. I can connect, but I cannot do …

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?

…