Indexes are separate data structures that provide alternate pathways to finding data. They can and do generally speed up the processing of queries and other DML commands, like the INSERT, UPDATE, REPLACE INTO, and DELETE statements. Indexes are also called fast access paths.

In the scope of the InnoDB Database Engine, the MySQL database maintains the integrity of indexes after you create them. The upside of indexes is that they can improve SQL statement performance. The downside is that they impose overhead on every INSERT, UPDATE, REPLACE INTO, and DELETE statement, because the database maintains them by inserting, updating, or deleting items for each related change in the tables that the indexes support.

Indexes have two key properties—usability and visibility. Indexes are both usable and visible by default. That means they …

On Percona Live! Amsterdam 2015 we had a talk with Peter Boros about GTID replication.

Here are the slides.

Running gtid replication in production from Balazs Pocze

Well, it was ended a week ago, but I had too many errands to run, so I couldn’t post anything about it.

It was really great, again.This was the third time I attended (2013 London, 2015 Santa Clara) so now I have met with a lot of familiar people – it is true that MySQL has a great community. The chosen city was great, Amsterdam is one of the coolest places of Europe, the hotel was neat, and the programs were also astounding.

The conference sessions were great too, I really enjoyed, them all, and because they are running on 8 thread parallel it is not that bad that there are some recurring sessions; if you missed one in spring you can watch it on autumn.

So, everything was comfy and neat. I hope I’ll attend on the next one too …

There were a few topics where I plan to dig deeper in the next weeks

• ProxySQL because HAProxy is a good choice, but it is only speaks TCP and HTTP but not …

Hi!

I'm speaking at Oracle Open World this October 29th (Thursday). My Session is a hands-on lab session: HOL3348 on MySQL Cluster.

If you are interested in familiarize a bit with MySQL Cluster this is definitely a session for you.

I will start by briefly introducing MySQL Cluster and its Architecture.

Then I will guide you through the needed steps to:

• Install a local MySQL Cluster
• Start MySQL Cluster
• Connect to MySQL Cluster (using the command line)
• Ways to monitor MySQL Cluster
• Safe shutdown of MySQL Cluster

We will have a chance to see which are the most common commands and operations used in MySQL Cluster Administration.

If there is …

For learning, testing and keep up with things, one might want to install/update the MySQL version to the latest DMR (Development Milestone Release). At the time of this writing the latest DMR for MySQL is  MySQL Community Server 5.7.8-rc. Having chosen Ubuntu server as my operating system and since this is a development version hence not yet part of an official Ubuntu release, you have to install

With Galera (Percona Cluster or MariaDB Cluster), it is sometimes advisable to not route traffic to a node during a backup due to the node already being under a heavier load.  In these situations, it may be wise to not route traffic there until the backup is complete.

Since the default /usr/bin/clustercheck script did not have the option of doing this, we created a modified version.  The below script looks for the presence of the xtrabackup tool running in the process list.  If it is found, the clustercheck script returns the appropriate exit code (502) which signals the load balancer to not route traffic its way.  The script could easily be modified to look for the presence of programs/tools in the process list.

We hope others will find it useful.

#!/bin/bash
# clustercheck.sh
#
# Script to make a proxy (ie HAProxy) capable of monitoring Percona XtraDB Cluster nodes properly
#
# Modified by Itchy …

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;

…

Eliminating overhead that adds unnecessary load to database servers when SQL statements are unnecessary can improve MySQL performance, including removing duplicate, repeating or unnecessary statements.

Removing Duplicate SQL Statements

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.

Removing …

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;

…