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 …

We remember when we first started auditing MySQL servers, there
were very few tools available. In one of our early big
gigs, we were battling serious performance issues for a client.
At the time, tuning-primer.sh was about the only tool
available that could be used to diagnose performance bottlenecks.
Fortunately, with a lot of manual interpolation of the raw
data it presented, we were able to find the issue with the server
and suggest how to resolve them. For that we are very
thankful. It was a first step in analyzing MySQL status
variables, minimizing the number of formulas to learn and
calculate by hand. Obviously doing it by hand takes
forever!

Now fast-forward to today. Unfortunately, not much has
changed. Many DBAs and developers are still using open
source tools such as tuning-primer, mysqltuner.pl, mysqlreport,
and so on. Don’t get the wrong; those tools have …

When it is not possible to eliminate an SQL statement to improve
performance, it might be possible to simplify the statement.
Consider the following questions:

Are all columns required?

Can a table join be removed?

Is a join or WHERE restriction necessary for additional SQL
statements in a given function?

Column Improvement

An important requirement of simplification is to capture all SQL
statements in order for a given executed function. Using a
sampling process will not identify all possible improvements.
Here is an example of a query simplification:

mysql> SELECT fid, val, val
-> FROM table1
-> WHERE fid = X;

This query returned 350,000 rows of data that was cached by the
application server during system startup. For this query, …

When it’s not possible to remove SQL statements that are unnecessary and
the rate of change of common data is relatively low, caching SQL
results can provide a significant performance boost to your
application and enable additional scalability of your database
server.

MySQL Caching

The MySQL query cache can provide a boost in performance for a
high read environment and can be implemented without any
additional application overhead. The following is an example
using the profiling functionality to show the execution time and
the individual complexity of a regular SQL statement and a
subsequent cached query:

SET GLOBAL query_cache_size=1024*1024*16;
SET GLOBAL query_cache_type=1;
SET PROFILING=1;
SELECT name FROM firms WHERE id=727;
SELECT name FROM firms WHERE id=727;
SHOW PROFILES;

Capture of all SQL statements for a given function or process
will highlight any duplicate SQL statements that are executed to
complete a specific request. The best practice is to enable the
general query log in development environments. Analysis of all
SQL statements should be the responsibility of the developer to
ensure that only necessary SQL statements are executed. Adding
instrumentation to your application to report the number of SQL
statements and provide debugging for dynamic viewing of all SQL
statements easily enables more information to identify duplicate
statements. The use of application frameworks can be a primary
cause of unnecessary duplicate SQL statements.

Adding indexes can provide significant performance benefits.
However, the most effective SQL optimization for a relational
database is to eliminate the need to execute the SQL statement
completely. For a highly tuned application, the greatest amount
of time for the total execution of the statement is the network
overhead.

These are all overheads that add unnecessary load to the database
server when statements are unnecessary. You can use the profiling
functionality to get detailed timing of steps within the
execution of a query.

In order to simplify the configuration of MySQL for standalone
nodes, clusters, and replication configurations, we decided it
would be easiest to maintain a common my.cnf file. We have
to admit, the idea wasn’t ours; we picked the idea up from
www.fromdual.com and thought it was such a great idea, we decided
to implement it as well.

Below is our version of a standardized my.cnf implementing
several of our best practices. We hope it will be of
benefit to you.

########################################################################################################
# my.cnf (Configuration file for MySQL)
#
# Provided by Itchy Ninja Software to implement general best practices for MySQL regardless of server
# type. We chose a single file instead of maintaining multiple versions of the configuration file.
# Based upon http://www.fromdual.com/mysql-configuration-file-sample
#
# Sections are included for Percona XTRADB Cluster …

At Yahoo, we manage a massive number of MySQL databases spread
across multiple data centers.

In order to identify and respond to performance issues, we rely
on an extremely lightweight and robust web based tool to
proactively investigate the issues in them.

The tool has real time tracking features and continually gathers
the most important performance metrics, provides visualization
and statistical analysis for quickly identifying performance
patterns, bottlenecks and possible tuning opportunities.

Every day hundreds of millions of dollars are wasted by allowing
improperly tuned or misconfigured systems, misunderstood
infrastructure, and inefficient IT operations to live and thrive
in data centers around the globe. There are both direct and
indirect costs associated with allowing these unhealthy systems
to continue to exist. Let’s look at some.

The setup:

Let us start by using a small example. We will start by looking
at a small database setup. This setup will have a single
master-slave, with a database size of lets say 500GB. Traffic is
steady and let’s say this translates into 500 IOPS on the master.
You have chosen to host this on Amazon’s AWS. A common way of
ensuring backups occur in AWS is to setup ebs snapshots of the
slave. In terms of usage, let us assume your CPU is about 50%
used and you have about 20GB of hot data that needs to stay in
the memory for the database.

Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion
of Oracle or any other party.