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.

Removing SQL statements can reduce the application processing time. Additional steps necessary for each SQL statement include parsing, permission security checks, and generation of the query execution plan.

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.

Here is an example:

mysql> show profile source for query 7;

…

Introduction

This document outlines best practices for loading data into MySQL very quickly.  While this is not a comprehensive list of loading methods and configuration, it is a good starting point.

MySQL Configuration

Assuming you are loading into InnoDB tables (and you should probably be doing so), you will want to ensure that MySQL is properly performance tuned for loading large amounts of data.  Out of the box MySQL configuration is rarely sufficient for performance with MySQL.  It is essential that the InnoDB settings, in particular, be set properly.

First of all, consider the InnoDB Buffer Pool.  If you are doing a one-time load, it may be a good idea to configure this as large as possible.  In fact, we sometimes set this to approximately 90% of the available RAM on the system for the load.  This can then be dropped to between 70 and 80% for …

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.

Features

Lightweight Agentless Java Web Application
Rich User Interface
Gather and Store performance metrics 
Detect anomalies and send alerts
Access to Real time Performance data

Open Source

Today, we’re releasing MySQL Performance Analyzer. You can check out the code on …

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.

If we look at what this …

Itchy Ninja Software is pleased to announce the release of Analyst for MySQL v1.1. Revolutionize the way you work and administrate MySQL, MariaDB, Galera, and Percona XtraDB installations.

Make More Efficient Use of Your Time

Gathering all of the metrics to diagnose a database installation is a very time consuming process, and many simply do not have the experience to know where to begin. With Analyst for MySQL, you will be able to get your hands on hundreds of metrics within moments. It really takes all of the guesswork, as well as tedious long sessions of writing queries out of managing a MySQL database server.

Cross-Platform

Not only can you run the program on Windows, Mac, or Linux, you can also generate server reports from each of those platforms as well! No need to install anything on the server at any time. All diagnostics are run from your laptop or desktop machine. The …

On servers that are into the swap file and have multiple processes running, I often wonder if it’s MySQL that is in the swap or some other process.  With Linux this is a fairly easy thing to figure out.  Below is the format of a command to do just that:

echo 0 $(cat /proc/`pidof process`/smaps | grep TYPE | awk '{print $2}' | sed 's#^#+#') | bc

In the above command, TYPE refers to what type of Memory you want to examine.  Below are some of the options for this value:

Check Out Our Latest Technical Resources for MySQL, MariaDB & MongoDB Clusters

Like every month this year, we have created new content and tools for you; here is a summary of what we’ve published this December. Please do check it out and let us know if you have any comments or feedback.

And thank you for following us in the past 12 months and for your fidelity; we look forward to “seeing” you next year as well and wish you a great start to 2015!

New Live Technical Webinars

Infrastructure automation isn’t easy, but it’s not rocket science either, says Riaan Nolan. Riaan has been in operations for the past decade, and has built over a dozen eCommerce …

August 28, 2014 By Severalnines Check Out Our Latest Resources for MySQL, MariaDB & MongoDB Clusters

Here is a summary of resources & tools that we’ve made available to you in the past weeks. If you have any questions on these, feel free to contact us!

New Technical Webinars

Performance Tuning of HAProxy for Database Load Balancing

09 September 2014 - with Baptiste Assmann of HAProxy Technologies

Do you know what HAProxy can tell you about your application and database instances? Do you know the difference between …

August 13, 2014 By Severalnines

New Webinar: Performance Tuning of HAProxy for Database Load Balancing

Date, Time & Registration: 

Tuesday, September 9th @10am CEST (Europe & MEA / Asia Pacific)

Register here

Tuesday, September 9th …