There are those that are very adamant about letting people know that using INFORMATION_SCHEMA can crash your database. For example, in making changes to many tables at once Baron writes: “querying the INFORMATION_SCHEMA database on MySQL can completely lock a busy server for a long time. It can even crash it. It is very dangerous.” Though Baron is telling the truth here, he left out one extremely important piece of information: you can actually figure out how dangerous your INFORMATION_SCHEMA query will be, ahead of time, using EXPLAIN.

This paper is an introduction to the new 'performance schema' feature, which will be part of the upcoming MySQL 5.5 release. Covering in details every part of the performance schema would require much, much more than a simple article. The pace of this teaser is voluntarily fast, to have a quick overview of the new landscape, and help users already familiar with MySQL to understand by examples what the performance schema provides.

InnoDB at 10,000 IOPS

Mark Callaghan discusses the implications of using FLUSH TABLES WITH READ LOCK and what this command actually does.

In my old post there is a bug when run in MySQL 5.1.30 and old, because the status variable Queries was added in MySQL 5.1.31. So i change to choose between Queries and Questions status variables, and I think the Queries represent more accurate result.

Sheeri explains why turning on the query cache without thinking about the consequences is bad but also why simple benchmarks proving 'the query cache is bad' may be skewed

Maximize your strengths, minimize your weaknesses. You can apply this approach to many things in life, I apply it to describing and using MySQL the product, and it’s components. The Query Cache like many features in MySQL, and indeed features in many different RDBMS products (don’t get me started on Oracle *features*) have relative benefits. In one context it can be seen as ineffective, or even detrimental to your performance, however it’s course grain nature makes it both trivial to disable dynamically (SET GLOBAL query_cache_size=0;), and also easy to get basic statistics on current performance (SHOW GLOBAL STATUS LIKE ‘QCache%’;) to determine effectiveness and action appropriately.

Mark Callaghan tests the query cache. "The query cache has had an interesting history with MySQL. I don't have much experience with it. I might want to use it in the future so I enabled it during a run of sysbench readonly on an 8-core server. The results are fascinating."

presented at MySQL Camp 2009.

Beginner's Guide to Website Performance with MySQL and memcached Adam Donnison (Sun Microsystems) Download the slides in .ODP format at http://assets.en.oreilly.com/1/event/21/Beginner%27s%20Guide%20to%20Website%20Performance%20with%20MySQL%20and%20memcached%20Presentation.odp The description, taken from the official conference page is at http://www.mysqlconf.com/mysql2009/public/schedule/detail/7009 Memcached is a distributed memory object caching system that is getting a lot of press at the moment for its ability to provide a high-performance front-end for database applications. Sure, memcached can do wonders, but where is it most effective and what are the pain points? When a site grows the site administrator starts to worry about how to maintain performance as the hit count rises. Should extra hardware be thrown at the problem synchronisation issues start to rear their heads. Writing caching code is  []

High Performance Ruby on Rails and MySQL David Berube (Berube Consulting) From the official conference description is at http://www.mysqlconf.com/mysql2009/public/schedule/detail/6942 MySQL is among the fastest relational databases commonly available today; unfortunately, the database alone is only part of the picture. For todays web applications, the one weak link in the entire performance chain from the network to the web application and ending in the database can cause an entire application to seem slow. Unfortunately, Ruby on Rails on exacerbates this problem: Rails makes it easy to develop complicated web applications fast, but it also makes it easy to access your databases in an extremely inefficient manner. Thousands of queries may be generated when just a few are necessary. Even if you can get past that problem, ActiveRecord itself can be a problem: it may create thousands or even millions of  []

The ScaleDB Storage Engine: Enabling High Performance and Scalability Using Materialized Views and a Shared-Disk Clustering Architecture Moshe Shadmon (ScaleDB) Slides can be downloaded at http://assets.en.oreilly.com/1/event/21/The%20ScaleDB%20Storage%20Engine_%20%20Enabling%20High%20Performance%20and%20Scalability%20Using%20Materialized%20Views%20and%20a%20Shared-Disk%20Clustering%20Architecture%20Presentation.ppt The official conference page is at http://www.mysqlconf.com/mysql2009/public/schedule/detail/7112

Chasing Bottlenecks by Morgan Tocker Description: The best way to performance tune a system is to find out what your bottlenecks are, and attacking those first. In the first part of this session, I'll be looking at some of the issues faced with common database workloads. From there, I'll then be showing how you can get more information out of MySQL and your Operating System to find out about your workload. This session is designed for beginner to intermediate MySQL users.

At the May 2009 Boston MySQL User Group, Giuseppe Maxia of Sun Microsystems gave a presentation about MySQL 5.4 with use cases and benchmarks to show how it outperforms all other current MySQL releases (including the Percona, OurDelta, and Google releases/patches). The slides are at http://www.slideshare.net/datacharmer/mysql-54-theory-and-practice

Use Dtrace to monitor your queries.

At the 2008 MySQL Conference and Expo, The Pythian Group gave away "EXPLAIN" cheatsheets. They were very nice, printed in full color and laminated to ensure you can spill your coffee* on it and it will survive.For those not at the conference, or those that want to make more, the file is downloadable as a 136Kb PDF at explain-diagram.pdf* or tea, for

We've been running into a problem with one client:SELECT COUNT(*) FROM tbl;takes 0.25 seconds on one db, and 0.06 seconds on another. Consistently. That's a fourfold difference. There aren't any significant configuration differences (like query cache, etc.), the software versions are the same, and the table fits into memory. This has been looked at by at least 3 in-house MySQL experts, and the only thing we can determine is that it's a hardware difference.Th

This is an issue that keeps rearing its ugly head over and over again, and since it greatly affects performance, it is most important that DBAs of any DMBS running on Linux come to grips with it. So I decided to do some research and try different settings on my notebook. Here are my findings.What can you find on the web?A Wikipedia search for the word swappiness will come up empty (any volunteers out there want to write an article?). A Google search will show some pretty old material---the best article I found is from 2004: Linux: Tuning Swappiness. This article includes a detailed discussion with some interesting remarks by Andrew Morton, a Linux kernel maintainer.So, what is swappiness?

There's no video for Jacob Nikom's December 2007 Boston MySQL User Group meeting, but the slides for "Measuring MySQL Server Performance" can be downloaded (2.33 MB) at http://technocation.org/files/doc/Measuring_MySQL_server_performance_03.pptAnd with that, this is (I believe) post #10,000 at Planet MySQL!

Includes: the link to the playlist for all 11 videos plus 11 individual links to each video: Spider: Sharding for the Masses - Giuseppe Maxia A Better mysqltuner - Sheeri Cabral (my session!) Getting acquainted with Apache Derby - Kristian Wagaan New kid on the block: The BlackRay Data Engine — Felix Schupp MySQL High Availability Solutions — Lenz Grimmer Bringing Master/Slave into the 21st Century using Tungsten Database Clustering — Linas Virbalas PBXT: Technology trends that affect your database — Vladimir Kolesnikov MySQL Proxy: a MySQL toolbox - Architecture and concepts of misuse — Jan Kneschke Galera Replication, multi-master synchronous replication for MySQL — Seppo Jaakola Panel Discussion: The OSS Toolshed Shootout Speakers and technologies: Sheeri K. Cabral - MySQL; Holger Klemt - Firebird; Felix Schupp - BlackRay;  []