Showing entries 1 to 10 of 105
10 Older Entries »
Displaying posts with tag: tuning (reset)
Maximizing Database Performance – MySQL Tuning Best Practices

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 …

[Read more]
Real time query monitoring on MySQL - with 3rd party tool and without

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

Real time query monitoring on MySQL using the SYS schema

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

Is 80% of RAM how you should tune your innodb_buffer_pool_size?

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 …
[Read more]
MySQL Query Profiling with Performance Schema

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 …

[Read more]
More EXT4 vs XFS IO Testing

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 …

[Read more]
Items Affecting Performance of the MySQL Database

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 …
[Read more]
InnoDB, The Choice for High Concurrency Database Systems

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:

[Read more]
Benchmarking MySQL Performance

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 …
[Read more]
Bloating Your Buffers

There’s one thing that has always bugged me when I review configuration files. People always seem to want to set certain buffer settings to ridiculously high values without really understanding what they’re doing.

Case in point: sort_buffer_size.

The misconception seems to be that setting this value to a high number will always improve a servers performance because everything works better with more memory, right? For some variables (key_buffer, innodb_buffer_pool) maybe, but the entire length of a sort buffer is allocated when ORDER BY is used. In practice, I find it best to leave it at default and watch sort_merge_passes in your status counter to determine if you need to adjust this value. Also, adjust in small amounts.

[Read more]
Showing entries 1 to 10 of 105
10 Older Entries »