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;

…

I believe the future of SQL is to take a lot of primitives that are Computer Science fundamentals and add them as datatypes to expand on the allowed columns today. The idea is of the ilk of a merging of noSQL and SQL  for solving problems to make it easier for a new person to develop.


For instance, what would be awesome is a LIST type, where the list contains a distinct number of string items mapped to a bit, much like SET yet you don't need to predefine all the items in a set.


Here is a good example as how I would use a list type:


Imagine you need permissions on a per row basis. Some rows are public, some are private, some are viewable by a small set of people. (Less than 64).

Let's take the example of Finding all rows that are public or are viewable by only me.

When creating a row

INSERT INTO resource_permissions (resource_id, …

In addition to creating new indexes to improve performance, you can improve database performance with additional schema optimizations. These optimizations include using specific data types and/or column types. The benefit is a smaller disk footprint producing less disk I/O and results in more index data being packed in available system memory.

Data Types

Several data types can be replaced or modified with little or no impact to an existing schema.

BIGINT vs. INT

When a primary key is defined as a BIGINT AUTO_INCREMENT data type, there is generally no requirement why this datatype is required. An INT UNSIGNED AUTO_INCREMENT datatype is capable of supporting a maximum value of 4.3 billion. If the table holds more than 4.3 billion rows, other architecture considerations are generally necessary before this requirement.

The impact of modifying a BIGINT data type to an INT data type is a 50 percent reduction …

The management of indexes—how they are created and maintained—can impact the performance of SQL statements.

Combining Your DDL

An important management requirement when adding indexes to MySQL is the blocking nature of a DDL statement. Historically, the impact of an ALTER statement required that a new copy of the table be created. This could be a significant operation for time and disk volume when altering large tables. With the InnoDB plugin, first available in MySQL 5.1, and with other third party storage engines, various ALTER statements are now very fast, as they do not perform a full table copy. You should refer to the system documentation for the specific storage engine and MySQL version to confirm the full impact of your ALTER statement.

Combining multiple ALTER statements into one SQL statement is an easy optimization improvement. For example, if you needed to add a new index, modify an index, and add a new …

This tutorial describes a replicated MySQL setup (Mater/Master replication) with 2 nodes where data can be read and written to both nodes at the same time. MySQL takes care to replicate the data to the other node and ensures that primary auto increment keys don't collide.