PLEASE NOTE: I am currently reviewing and extending this document. While caring for a remarkable amount of MySQL server instances, troubleshooting becomes a common task. It might of interest for you which Recovering a crashed MySQL server After a server … Weiterlesen →

Memcached is the de-facto distributed caching server used to scale many web2.0 sites today. With the requirement to support a very large number of users as sites grow, memcached aids scalability by effectively cutting down on MySQL traffic and improving response times.

Memcached is a very light-weight server but is known not to scale beyond 4-6 threads. Some scalability improvements have gone into the 1.3 release (still in beta). With the new Intel Nehalem based systems improved hyper-threading providing twice as much performance as current systems, we were curious to see how memcached would perform on these systems. So we ran some tests, the results of which are shown below :

memcached 1.3.2 does scale slightly better than 1.2.5 after 4 threads. However, both versions reach their …

Memcached is the de-facto distributed caching server used to scale many web2.0 sites today. With the requirement to support a very large number of users as sites grow, memcached aids scalability by effectively cutting down on MySQL traffic and improving response times.

Memcached is a very light-weight server but is known not to scale beyond 4-6 threads. Some scalability improvements have gone into the 1.3 release (still in beta). With the new Intel Nehalem based systems improved hyper-threading providing twice as much performance as current systems, we were curious to see how memcached would perform on these systems. So we ran some tests, the results of which are shown below :

memcached 1.3.2 does scale slightly better than 1.2.5 after 4 threads. However, both versions reach their …

The Kickfire appliance is designed for business intelligence and analytical workloads, as opposed to OLTP (online transaction processing) environments.  Most of the focus in the MySQL area right now revolves around increasing performance for OLTP type workloads, which makes sense as this is the traditional workload that MySQL has been used for.  In contrast,  Kickfire focuses squarely on analytic environments, delivering high performance execution of analytical and reporting queries .

A MySQL server with fast processors, fast disks (or ssd) and lot of memory will answer many OLTP queries easily.  Kickfire will outperform such a server for typical analytical queries such as aggregation over a large number of rows.

A typical OLTP query might ask “What is the shipping address for this invoice?”.  Contrast this with a typical analytical query, which asks “How much of this item did we sell in all of …

The BoF schedule for MySQL Conference and Expo (2009) is now published. Lenz Grimmer, Sergei Golubchik, Tomas Ulin and myself will be available during a BoF which focuses on MySQL Community Code Contributions. Lenz will be moderating. For background material, you may start here.

I’m presenting about Maatkit, the toolkit I created to make life better with MySQL, at the MySQL conference next week.

I’m going to give you a whirlwind tour throught some of Maatkit’s features and functionality. The toolkit is much too large and complex to cover more than a small part of it in depth. So here is your advance warning: I’m going to go through a lot of material, and I won’t be stopping for lengthy discussions :-) The Maatkit documentation is very thorough, and I hope to introduce you to things that could be of use to you, so you can go learn about those topics from the documentation.

Let me give you an idea: when I’m optimizing queries, I open up the output of mk-query-digest in …

Having written about what I think is cool about the upcoming MySQL Conference and the MySQL Camp, now I want to finish up with what I’d like to see at the Percona Performance Conference. Just to recap, this is a conference we created to serve those who want to learn about performance — not “learn about MySQL,” not “learn about database performance,” just learn about performance, period.

I want to see everything. I think this is going to be the single best conference I’ve ever been to. Even the way the conference is organized is exciting. For example, it’s running from early morning till late at night, nonstop. The sessions are also (mostly) only 25 minutes. This means if you decide a session isn’t all that interesting, you didn’t spend much time on it, and you don’t have long to wait for the next one.

So here is a small sample of the …

The MySQL Users Conference is going to be an excellent conference with a lot of change going on in the MySQL world. Some of the key areas to be looking at include:MySQL strategic directions.Increasing vertical scalability of MySQL with upcoming MySQL software releases, new and enhanced storage engines and patches.Enhanced diagnostics with DTrace.New BI software products and tools.Performance

I found this yesterday while tracking down a locking issue for a client. They had a connection time out on a lock, but before it times out, SHOW PROCESSLIST had the status 'statistics' so it wasn't actually executing the query yet. So, what was it doing and why did it time out there?

The answer actually was remarkably simple, but I did have to take a peek in the MySQL server source code (horay for Open Source!) The server sets the thd_proc_info to 'statistics' when calling the join optimiser, that's the part of the optimiser that works out the best join order.

A lesser known feature of the join optimiser is that if it works out that only one row can match (lookup on primary or unique key field), it'll try to retrieve the row. If there's no match, there's an "impossible WHERE clause" and essentially the entire query is optimised away and 0 rows returned.

If there is a match, all references to columns in …

I found this yesterday while tracking down a locking issue for a client. They had a connection time out on a lock, but before it times out, SHOW PROCESSLIST had the status ’statistics’ so it wasn’t actually executing the query yet. So, what was it doing and why did it time out there?

The answer actually was remarkably simple, but I did have to take a peek in the MySQL server source code (horay for Open Source!) The server sets the thd_proc_info to ’statistics’ when calling the join optimiser, that’s the part of the optimiser that works out the best join order.

A lesser known feature of the join optimiser is that if it works out that only one row can match (lookup on primary or unique key field), it’ll try to retrieve the row. If there’s no match, there’s an “impossible WHERE clause” and essentially the entire query is optimised away and 0 rows returned.

If there is a match, all references to columns in …