With the added complexity of growing data volumes and ever changing workloads, database performance tuning is now necessary to maximize resource utilizations and system performance. However, performance tuning is often easier said than done.

Let’s face it, tuning is difficult for a number of reasons. For one thing, it requires a significant amount of expertise in order to understand execution plans, and often update or re-write good SQL. On top of that, tuning is usually very time consuming. There will always be a large volume of SQL statements to sort through, which may lead to uncertainty around which specific statement needs tuning; and given every statement is different, so too is the tuning approach.

As data volumes grow and technology becomes increasingly complex, it is becoming more important to tune databases properly to deliver end-user experience and to lower infrastructure costs. Performance tuning can help database …

I've tried out Idera's MySQL Query Explorer, a free tool and I found it easy to use and simple to setup. The only improvement that can be suggested is to add, on the technical requirements page, that your MySQL instance (MySQL Server version 5.5 and newer) must be running with the performance_schema turned on. Otherwise the tool will just display an empty grid. After you've successfully set

On an earlier post I wrote about real time query monitoring on MySQL with a third party tool and without one. The script is useful as it works with MySQL 5.5 and later. However, if you're using a later version of MySQL, you should look at the SYS schema. It is a collection of views, functions and procedures to help MySQL administrators get insight in to MySQL Database usage. It's available to

It seems these days if anyone knows anything about tuning InnoDB, it’s that you MUST tune your innodb_buffer_pool_size to 80% of your physical memory. This is such prolific tuning advice, it seems engrained in many a DBA’s mind.  The MySQL manual to this day refers to this rule, so who can blame the DBA?  The question is: does it makes sense?

What uses the memory on your server?

Before we question such advice, let’s consider what can take up RAM in a typical MySQL server in their broad categories.  This list isn’t necessarily complete, but I think it outlines the large areas a MySQL server could consume memory.

• OS Usage: Kernel, running processes, filesystem cache, etc.
• MySQL fixed usage: query cache, InnoDB …

One of my favorite tools for query optimization is profiling. But recently I noticed this warning:

mysql> set profiling=1;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> show warnings;
+---------+------+----------------------------------------------------------------------+
| Level   | Code | Message                                                              |
+---------+------+----------------------------------------------------------------------+
| Warning | 1287 | '@@profiling' is deprecated and will be removed in a future release. |
+---------+------+----------------------------------------------------------------------+

After looking through certain documentation , I should indeed start using the Performance Schema to get this information.

Okay, so let’s give that a try.

I confirmed that I started MySQL 5.6.23 …

I had a few more suggestions thrown out at me before I could wrap this one up.

• Try disabling the RAID controller read-ahead
• Try a few custom options to XFS
• Try RAID-10

First, my final “best” state benchmarks for comparison:

Following my previous post, I got some excellent feedback in the forms of comments, tweets and other chat. In no particular order:

• Commenter Tibi noted that ensuring I’m mounting with noatime, nodiratime and nobarrier should all improve performance.
• Commenter benbradley pointed out a missing flag on some of my sysbench tests which will necessitate re-testing.
• Former co-worker @preston4tw suggests looking at different IO schedulers. For all tests past, I used deadline which seems to be best, but re-testing with noop could be useful.
• Fellow DBA @kormoc encouraged me to try many smaller partitions to limit the number of concurrent fsyncs.

There seem to be …

To learn about the many factors that can affect the performance of the MySQL Database, take the MySQL Performance Tuning course.

You will learn:

• How your hardware and operating system can affect performance
• How to set up and logging to improve performance
• Best practices for backup and recovery
• And much more

You can take this 4-day instructor-led course through the following formats:

• Training-on-Demand: Start training within 24 hours of registering for training, following lectures at your own pace through streaming video and booking time on a lab environment to suit your schedule.
• Live-Virtual Event: Attend a live event from your own desk, no travel required. Choose …

InnoDB has proven to be a reliable data storage engine for modern, high concurrency database systems. It is fully ACID compliant, and supports a wide range of isolation modes, from READ-UNCOMMITEED to SERIALIZABLE.

InnoDB multiversion concurrency control (MVCC) enables records and tables to be updated without the overhead associated with row-level locking mechanisms. The MVCC implementation in InnoDB largely eliminates the need to lock tables or rows during updates, and enables good performance for high concurrency workloads.

To learn more about this subject and related performance tuning topics tuning, take the MySQL Performance Tuning training course. This 4-day, instructor-led course is available as:

…

Benchmarking lets you test how a system responds when it is given work to do, and removes the guesswork from your performance tuning efforts. The workloads you use when benchmarking are very different from real life deployments, which can be extremely variable. If you remember this, benchmarking is extremely useful, enabling you to measure current performance and determine the performance impact of any changes. Such changes could be the addition of new hardware or opening up your application to more users.

Benchmarking is one of the topics that you can learn more about by taking the MySQL Performance Tuning course. You can take this course in the following formats:

• Training-on-Demand: Start training within 24 hours of registration, following lecture …