Many of us find INFORMATION_SCHEMA painfully slow to work it when it comes to retrieving table meta data. Many people resort to using file system tools instead to
find for example how much space innodb tables are using and things like it. Besides being just slow accessing information_schema can often impact server performance
dramatically. The cause of majority of this slowness is not opening and closing tables, which can be solved with decent table cache size, and which is very fast for
Innodb but by the fact MySQL by default looks to refresh Innodb statistics each time table is queried from information schema.

The solution is simple, just set innodb_stats_on_metadata=0 which will prevent statistic update when you query information_schema. Most likely
you do not want it anyway. This will not make Innodb to operate without statistics at all as Innodb will still compute statistics for the …

Merry Christmas! Just in time for the holidays, we have released a new tool to help you configure and manage your MySQL servers. Our online MySQL Configuration Wizard can help you generate a good basic configuration file for a server. This MySQL tuning wizard is our answer to the commonly asked question, “what is a good default configuration file for my server with 16 GB of RAM?”

We have a raft of new features planned for future releases, including advanced configuration options, supersafe settings to prevent bad things from happening, and much more. In the future we plan to add more online tools to help you be more productive.

Please give it a spin and let us know what you think. Credit for this tool must go to Miguel Trias, our talented lead developer. Thanks also to the many experts inside Percona who helped test, and …

Many of you have seen the announcement for our Percona Live in Washington, DC in January! But did you know we also have our highly rated MySQL Training coming to Washington, DC the week of January 16h? Full details can be found on the Percona website. If you would like to attend the MySQL Training, click here to register and receive a 20% discount.

One of the more common questions I get asked is which Linux distribution I would use for a MySQL database server. Bearing the responsibility for someone else’s success means I should advise something that is stable, reliable, easy to manage and has plenty of resources available online. It should also allow running MySQL without too much hassle. Unless there are individual circumstances, it actually makes the decision quite easy.

There are probably only a few distributions, which can be considered: CentOS, Debian, RedHat Enterprise Linux, SuSE Linux and Ubuntu. Of course CentOS and Ubuntu derive from RedHat and Debian respectively, but their install bases are large enough to mention them separately. Running MySQL won’t be much different whether one or another distribution is used. All use common Linux kernel – the heart of Linux operating system – which in principle will behave the same way in all cases. The kernel versions may …

Problem with kernel_mutex in MySQL 5.1 and MySQL 5.5 is known: Bug report. In fact in MySQL 5.6 there are some fixes that suppose to provide a solution, but MySQL 5.6 yet has long way ahead before production, and it is also not clear if the problem is really fixed.

Meantime the problem with kernel_mutex is raising, I had three customer problems related to performance drops during the last month.

So what can be done there ? Let’s run some benchmarks.

But some theory before benchmarks. InnoDB uses kernel_mutex when it starts/stop transactions, and when InnoDB starts the transaction, usually there is loop through ALL active transactions, and this loop is inside kernel_mutex. That is to see kernel_mutex in action, we need many concurrent but short transactions.

For this we will …

Over the last few years, Domas’s technique of using GDB as a profiler has become a key tool in helping us analyze MySQL when customers are having trouble. We have our own implementation of it in Percona Toolkit (pt-pmp) and we gather GDB backtraces from pt-stalk and pt-collect.

Although it’s helped us figure out a lot of problems, sometimes it doesn’t go well. Getting GDB backtraces is pretty intrusive. Here are three things that I’ve witnessed:

1. The server freezes for the duration of the process. This is the most obvious impact on the running server: GDB forklifts the process and gets a stack trace from every thread, then lets it go on working. But this can take a while. It’s usually a couple of seconds, but on big servers with a lot of memory and many threads, it can take much longer (I’ve seen tens of seconds, …

As I stated in my last post, I decided to use the Innodb Table monitor to diagnose an Index count mismatch error a customers found in their mysqld.err log to verify if the problem still existed.

The Innodb Table Monitor has existed for basically forever in Innodb (the MySQL manual discusses it back in the 4.1 manual), and is from a time when what was going on inside of Innodb was a lot murkier than it is now.   To use it, you create a table (in any database you choose), like this:

CREATE TABLE innodb_table_monitor (a INT) ENGINE=INNODB;

This, primitively, tells Innodb that it should start the Table monitor.  The monitor …

Now that InnoDB is the default storage engine in MySQL, is it time to update the default configuration for the InnoDB log file size (innodb_log_file_size) setting?

In general, there are two settings that simply can’t be left at their historical defaults for a production installation. MySQL 5.5 increased the default buffer pool size to something more sane (128MB instead of 8MB), but the log file size remains at 5MB. That’s 10MB total, because there are two logs by default.

Is it time to update this? I think so. You simply can’t run a “real” server with 10MB of transaction logs. What’s a sane value? I’d say that something between 64MB and 256MB would be okay for a lot of entry-level workloads, but that would consume some disk space that might surprise people. Perhaps this is the reason that the default hasn’t been increased from 5MB. Regardless, I think that 64MB times two is okay as a default.

What do you …

We’re told that foreign key constraints are the best way to enforce logical referential integrity (RI) in SQL, preventing rows from becoming orphaned.  But then we learn that the enforcement of foreign keys incurs a significant performance overhead.1,2

MySQL allows us to set FOREIGN_KEY_CHECKS=0 to disable enforcement of RI when the overhead is too high.  But if you later restore enforcement, MySQL does not immediately scan all your data to verify that you haven’t broken any references while the enforcement was disabled.  That’s undoubtedly …

As MLC-based SSD cards are raising popularity, there is also a raising concern how long it can survive. As we know, a MLC NAND module can handle 5,000-10,000 erasing cycles, after which it gets unusable. And obviously the SSD card based on MLC NAND has a limited lifetime. There is a lot of misconceptions and misunderstanding on how long such card can last, so I want to show some calculation to shed a light on this question.

For base I will take Virident FlashMAX M1400 (1.4TB) card. Virident guarantees 15PB (PB as in petabytes) of writes on this card.
15PB sounds impressive, but how many years it corresponds to ? Of course it depends on your workload, and mainly how write intensive it is. But there are some facts that can help you to estimate.

On Linux you can look into the /proc/diskstats file, which shows something like:

 251       0 vgca0 30273954 0 968968610 …