Setting up ProxySQL 1.4 with MySQL 5.7 Group Replication

January 9, 2018

Lire cet article en français

There are 3 pillars for a database architecture:

  • Monitoring
  • Backup / Restore process
  • High Availability

This blog post is about database High Availability; more precisely about one of the best combo of the moment :
MySQLProxySQL

 

  • MySQL 5.7 Group Replication : the only native HA solution for MySQL, it’s a Single/Multi-master update everywhere replication plugin for MySQL with built-in automatic distributed recovery, conflict detection and group membership.
  • ProxySQL 1.4 : probably the best proxy for MySQL.

 

Note : MySQL replication is indeed more than a serious alternative for HA. By the way ProxySQL natively handle it.

Note : ProxySQL have a plethora of features, however the purpose of this article is its use in a MySQL Group Replication context..

Note : MySQL NDB Cluster is also a HA solution adapted for the distributed computing environment. This is out of the scope of this article.

 

ProxySQL is compatible with MySQL Group Replication since version 1.3 see (in French) : Configurer ProxySQL pour MySQL Group Replication. However, version 1.4 have a native MySQL Group Replication support. It is therefore easier to use these two popular technologies together.

This is what we will see now.

 

In this article I will make the following assumptions :

 

MySQL Group Replication

Specifications

  • Server version : 5.7.20
  • Plugin version : 1.0
  • Node 1 : mysql_node1, 172.22.0.10 : 3306
  • Node 2 : mysql_node2, 172.22.0.20 : 3306
  • Node 3 : mysql_node3, 172.22.0.30 : 3306

These characteristics therefore represent a 3-nodes MySQL Group Replication cluster, up and running:

MySQL Enterprise Monitor gives us a graphical view of the cluster and its state: (click to enlarge) :

MySQL

 

The cluster is in single primary mode, ie only one node is available for reading & writing at a time (while the other 2 nodes are read-only).

MySQL Enterprise Monitor shows us (click to enlarge):

MySQL

 

I am going to extend the MySQL 5.7 sys schema with the following scriptaddition_to_sys.sql

 

So I load the functions and views into the cluster primary node (mysql_node1) :

 

This script will allow ProxySQL to monitor the state of the cluster nodes.

e.g.

 

On the cluster side, the last configuration step consists of creating the supervision users that will be used by ProxySQL (yes, there is a relationship with the previous step) :).

Here again I use the primary of the group :

 

Let’s configure ProxySQL now !

 

ProxySQL

Specifications

  • Proxy version : 1.4.4
  • Admin Interface : 172.22.0.2:6032
  • Cluster connection : 172.22.0.2:3306

 

 

ProxySQL configuration can be done online, which is obviously a very good thing.

Let’s start by logging into the admin interface on the default port 6032 with the default admin user and password… : admin (!) – Defaults that can and must be changed in real life.

 

Servers configuration

First step, add the cluster nodes to the proxy :

 

Hostgroups configuration

I have done a brief presentation of ProxySQL 1.3 objects last time : Configurer ProxySQL pour MySQL Group Replication

Version 1.4 has some differences, the most notable in our context is the new table : mysql_group_replication_hostgroups :

 

The best description I have found is available in my brilliant colleague’s blog post MySQL Group Replication: native support in ProxySQL

I quote

There are many new columns, let’s have a look at their meaning:

Column Name Description
writer_hostgroup the id of the hostgroup that will contain all the members that are writer
backup_writer_hostgroup if the group is running in multi-primary mode, there are multi writers (read_only=0) but if the amount of these writer is
larger than the max_writers, the extra nodes are located in that backup writer group
reader_hostgroup the id of the hostgroup that will contain all the members in read_only
offline_hostgroup the id of the hostgroup that will contain the host not being online or not being part of the Group
active when enabled, ProxySQL monitors the Group and move the server according in the appropriate hostgroups
max_writers limit the amount of nodes in the writer hostgroup in case of group in multi-primary mode
writer_is_also_reader boolean value, 0 or 1, when enabled, a node in the writer hostgroup will also belongs the the reader hostgroup
max_transactions_behind if the value is greater than 0, it defines how much a node can be lagging in applying the transactions from the Group, see this post for more info

by  @Lefred 🙂

 

Our configuration is as follows :

  • writer_hostgroup = 2
  • backup_writer_hostgroup = 4
  • reader_hostgroup = 3
  • offline_hostgroup = 1
  • active = 1
  • max_writers = 1
  • writer_is_also_reader = 1
  • max_transactions_behind = 0

Which gives us :

 

Supervision configuration

A little further up we created a supervision user in the cluster (remember?).

It is this user that will use ProxySQL to be aware of the state of the various nodes of the cluster :

 

Application user creation

The application connects to the MySQL server with a user and password (in the best case: D).

This user must obviously exist in the different MySQL instances that form the cluster, with the correct MySQL privileges.

This user must also be created in ProxySQL :

 

Change the listening port

By default the application will connect to the cluster through ProxySQL using port 6032 (in our case: 172.22.0.2:6032)

In real life, applications usually connect to MySQL using its default port, 3306.

It is therefore possible (not required) to modify the ProxySQL port to listen on 3306 :

 

Note : For a mysterious reason, this last configuration change is not online. In other words I must restart ProxySQL for this change to be taken into account.

 

The ProxySQL configuration for MySQL Group Replication is now complete \o/

I’m taking this opportunity to introduce a new table in version 1.4 : mysql_server_group_replication_log.

Useful for monitoring :

 

Playtime

As a reminder, workflow is as follows :

  • The app connects to ProxySQL (i.e. it only sees and knows the proxy)
  • ProxySQL retrieves transactions and redirects them to the primary node of the MySQL Group Replication cluster.
  • In case of primary node crash / shutdown,
    • MySQL Group Replication elects a new primary
    • ProxySQL identifies the new primary and sends transactions to this new primary

 

The application must therefore be configured to connect to ProxySQL. For example, if my app is Drupal  then my settings.php file will look like the following code snippet :

  • User : app_user
  • Password : app_pwd
  • Port : 3306 (ProxySQL)
  • Host : 172.22.0.2 (ProxySQL)

QED!

 

Let’s simulate all this in the command line!

My app is the mysql command-line tool. To simplify my commands, I first create a configuration file for the mysql client that contains the following information (btw it’s not recommended to have passwords in clear in a text file)  :

 

My servers are configured with the variable report_host filled (see http://dasini.net/blog/2016/11/08/deployer-un-cluster-mysql-group-replication/).

The cluster primary node is still mysql_node1 (no database failover yet since the beginning of this article, but we get there).

 

Now let’s test the failover with a slightly more complex example.

First, let’s create the test.poc table in InnoDB format :

 

The test application will run the following 2 queries :

  • INSERT INTO test.poc (host) VALUES (@@report_host);
  • SELECT * FROM test.poc;

 

With these 2 queries, the little scripts below and a timely kill -9, we will be able to follow the routing process (ProxySQL) and the database failover (MySQL Group Replication).

Transactions 1 & 2 (ids 2 et 9) are run on mysql_node 1.

From the third transaction (ids 11 et 18), they are run on the new primary mysql_node 3, because mysql_node 1 crashed.

 

Let’s finish this tutorial with pictures.

 

MySQL Enterprise Monitor

As stated in the introduction, a database architecture should rely on the 3 pillars : Monitoring / Backup process / High Availability.

Below a brief introduction of MySQL Enterprise Monitor (MEM) which is the MySQL monitoring tool, available with the commercial editions of MySQL (MySQL Enterprise Edition). MEM enables monitoring of MySQL instances and their hosts, notification of potential issues and problems, and advice on how to correct issues. You can also monitor and troubleshoot all types of MySQL Replication including MySQL Group Replication.

If you want to try our  Enterprise tools, please click here.

Below are some screenshots of the various states of the cluster supervised by MySQL Enterprise Monitor (click to enlarge):

MySQL

MySQL

MySQL

MySQL

MySQL

MySQL

MySQL

MySQL

 

 

The final word(s)

You already know, MySQL Replication and MySQL semi-synchronous Replication.

MySQL Group Replication is an additional tool that brings in especially the concept of built-in Automatic Database Failoverthat was missing in MySQL.

These architectures are used most of the time with a proxy. ProxySQL is without a doubt one of the best companion for MySQL today.

 

Note : MySQL provides MySQL InnoDB Cluster, which combines MySQL technologies ( MySQL Group Replication, MySQL Router and MySQL Shell) to enable you to create highly available clusters of MySQL server instances.
Tutorial : Tutoriel – Déployer MySQL innoDB Cluster

 

 

References

dasini.net

MySQL Group Replication

ProxySQL

 

Thanks for using MySQL!

 

1

How to Get MySQL?

July 24, 2017

Lire cet article en français

When starting a new project, it is generally recommended to go on the most recent version of MySQL, to take advantage of the latest features but also (mainly?) to be sure to be up to date with the security patches.

Powered by MySQL

This blog post centralizes the various URLs where to download the world’s most popular open source database.

 

MySQL Community Edition

If you’re looking for the latest binaries, client/server packages or source code, in 64/32 bits for Linux, Windows, Mac, Solaris, FreeBSD :

 

Linux Repository

On your Linux distros, the MySQL is often very old, (or worst you could download a totally different database).

The solution ? Install the MySQL APT/Yum/SUSE repository, which allows a simple and convenient way to install and update the latest GA, as well as the other products :

 

Others

Docker image, created, maintained and supported by MySQL :

 

GitHub repository, created, maintained and supported by MySQL :

 

MySQL NDB Cluster

MySQL NDB Cluster, is a high-availability, high-redundancy version of MySQL adapted for the distributed computing environment. Available in the above repositories, as well as in these 2 dedicated URLs :

 

MySQL Enterprise Edition

Oracle customers can sign in to their My Oracle Support account:

 

Oracle Software Delivery Cloud (e-delivery) allows you to evaluate the MySQL Enterprise products :

 

MySQL Enterprise Edition is also available in Oracle Cloud :

 

Old versions

Sometimes we do not have a choice, a particular version is mandatory. You will find it in the archives :

 

 

Thanks for using MySQL!

 

1

MySQL first Public Releases

July 19, 2017

Lire cet article en français

I regularly meet with MySQL customers and I’m still a little bit surprised to see critical applications running on “not really” recent versions (to put it mildly) 🙂

The good news is that obviously old versions of MySQL are sufficiently stable and powerful to run the modern business. However, even if I understand that it is sometimes appropriate to freeze all layers of an architecture, it is often a shame not to take advantage of the latest improvements from a performance, stability, security point of view and obviously for the new features that the latest GA provides :

 

Time being relative, let’s back to the future of MySQL first public releases (not GA) …

 

MySQL 3.22 | 1998

  • #1 movie in box office : Saving Private Ryan
  • #1 at Billboard’s Top Hot 100 : Too Close – Next
  • Event : September 4, Google, Inc. is founded in Menlo Park, California, by Stanford University PhD candidates Larry Page and Sergey Brin

 

Sources:
http://dev.cs.ovgu.de/db/mysql/News-3.22.x.html
http://www.boxofficemojo.com/yearly/
https://en.wikipedia.org/wiki/1998
https://en.wikipedia.org/wiki/Billboard_Year-End_Hot_100_singles_of_1998

 

MySQL 3.23 | 1999

 

Sources:
http://mysql.localhost.net.ar/doc/refman/4.1/en/news-3-23-x.html
http://www.boxofficemojo.com/yearly/
https://en.wikipedia.org/wiki/1999
https://en.wikipedia.org/wiki/Billboard_Year-End_Hot_100_singles_of_1999

 

MySQL 4.0 | 2001

 

Sources:
http://mysql.localhost.net.ar/doc/refman/4.1/en/news-4-0-x.html
http://www.boxofficemojo.com/yearly/
https://en.wikipedia.org/wiki/2001
https://en.wikipedia.org/wiki/Billboard_Year-End_Hot_100_singles_of_2001

 

MySQL 4.1 | 2003

 

Sources:
http://mysql.localhost.net.ar/doc/refman/4.1/en/news-4-1-x.html
http://www.boxofficemojo.com/yearly/
https://en.wikipedia.org/wiki/2003
https://en.wikipedia.org/wiki/Billboard_Year-End_Hot_100_singles_of_2003

 

MySQL 5.0 | 2003

 

Sources:
http://ftp.nchu.edu.tw/MySQL/doc/refman/5.0/en/news-5-0-x.html
http://www.boxofficemojo.com/yearly/
https://en.wikipedia.org/wiki/2003
https://en.wikipedia.org/wiki/Billboard_Year-End_Hot_100_singles_of_2003

 

MySQL 5.1 | 2005

 

Sources:
http://ftp.nchu.edu.tw/MySQL/doc/refman/5.1/en/news-5-1-x.html
http://www.boxofficemojo.com/yearly/
https://en.wikipedia.org/wiki/2005
https://en.wikipedia.org/wiki/Billboard_Year-End_Hot_100_singles_of_2005

 

MySQL 5.5 | 2009

  • #1 movie in box office : Avatar
  • #1 at Billboard’s Top Hot 100 : Boom Boom Pow – The Black Eyed Peas
  • Event : July 22, The longest total solar eclipse of the 21st century, lasting up to 6 minutes and 38.8 seconds, occurs over parts of Asia and the Pacific Ocean.

 

Sources:
https://dev.mysql.com/doc/relnotes/mysql/5.5/en/
http://www.boxofficemojo.com/yearly/
https://en.wikipedia.org/wiki/2009
https://en.wikipedia.org/wiki/Billboard_Year-End_Hot_100_singles_of_2009

 

MySQL 5.6 | 2011

 

Sources:
https://dev.mysql.com/doc/relnotes/mysql/5.6/en/
http://www.boxofficemojo.com/yearly/
https://en.wikipedia.org/wiki/2011
https://en.wikipedia.org/wiki/Billboard_Year-End_Hot_100_singles_of_2011

 

MySQL 5.7 | 2013

 

Sources:
https://dev.mysql.com/doc/relnotes/mysql/5.7/en/
http://www.boxofficemojo.com/yearly/
https://en.wikipedia.org/wiki/2013
https://en.wikipedia.org/wiki/Billboard_Year-End_Hot_100_singles_of_2013

 

MySQL 8.0 | 2016

 

Sources:
https://dev.mysql.com/doc/relnotes/mysql/8.0/en/
http://www.boxofficemojo.com/yearly/
https://en.wikipedia.org/wiki/2016
https://en.wikipedia.org/wiki/Billboard_Year-End_Hot_100_singles_of_2016

 

Sakila in Norway

Thanks for using MySQL!

1

30 mins with MySQL Query Rewriter

February 25, 2016

Lire cet article en français

TL;TR

Sometime DBAs have to deal with problematic queries and cannot tackle the problem at the source (problematic queries from ORMs, third party apps,… or source unknown…).

MySQL 5.7 provides :

  • A pre and post parse query rewrite APIs
    • Users can write their own plug-ins
    • You can eliminates many legacy use cases for proxies
  • With the post-parse query plugin, you can:
    • Rewrite problematic queries without the need to make application changes
    • Add hints
    • Modify join order

 

Query Rewrite Plugin APIs

Paraphrasing this MySQL Server Blog’s post :

MySQL now offer two APIs for writing query rewrite plugins.

Pre-parse rewrite plugin API,  is for when you know exactly – as in character-by-character exactly – what the offending queries look like. This one has a hook to intercept the query string right before it’s parsed.

Post-parse rewrite plugin API, comes in right after parsing and acts on the parse tree. It offers the basic functionality to walk over the parsed query, which is a lot more efficient than dealing with a string.

This blog post is about the Rewriter plugin, a post-parse query rewrite plugin, include in the MySQL 5.7 distribution (as of MySQL 5.7.6).

 

Rewriter plugin

Installation and checks

You are in MySQL world, so that’s obviously very easy! 🙂

To install the Rewriter query rewrite plugin, run install_rewriter.sql located in the share directory of your MySQL installation.

 

You can now check if everything is ok

New database objects were also created

 

The rewrite_rules table in the query_rewrite database provides persistent storage for the rules that the Rewriter plugin uses to decide whether to rewrite statements.

 

You can enable and disable the plugin at runtime :

 

Obviously it also possible to enable the plugin in the configuration file (my.cnf | my.ini)

 

 

Rewrite a query

Post parse rewrite plugin works only with SELECT statements.

You can try non-select statements but you’ll see, in rewrite_rules.message column, the following error message when you’ll flush the rules:

 

Rewriter plugin usage is very simple. Let’s start with a dummy example: transform a SELECT n into a SELECT n+1 (n is an integer).

Patterns

 -> SELECT 1      # Input

 <= SELECT 2    # Output

Add the rewrite rule

To add rules for the Rewriter plugin, add rows to the rewrite_rules table.

 

rewrite_rules table content is:

 

Rewriter’s stats shows:

 

Flush the rewrite rule

Then invoke the flush_rewrite_rules() stored procedure to load the rules from the table into the plugin.

When the plugin reads each rule from the rules table, it computes a normalized form (digest) from the pattern and a digest hash value, and updates the normalized_pattern and pattern_digest columns:

 

Within a pattern template, ? characters act as parameter markers that match data values. Parameter markers can be used only where data values should appear, not for SQL keywords, identifiers, and so forth. The ? characters should not be enclosed within quotation marks.

 

If the query parsing failed the stored procedure will raise an error:

You have more details in query_rewrite.rewrite_rules.message column

e.g.

Rewriter’s stats shows now:

 

Rewritten query

So we added and flushed the rules, we can now execute a query according to the pattern and see the behaviour

 

Magic !!!

The query was rewritten “on the fly”. However some interesting details are hiding in the warning.

All we need to know are in the Note : 1105

Rewriter’s stats are updated accordingly:

 

To disable an existing rule, modify its enabled column and reload the table into the plugin:

 

You can also delete table rows

 

OK folks! Let’s see more relevant examples

 

Rewrite plugin examples

Ex 1

Rewrite a Join into a Sub-query; ie for performance reason you must rewrite the query but you don’t have access to the app.

Patterns

 -> SELECT count(distinct emp_no) FROM employees.employees INNER JOIN employees.salaries USING(emp_no) WHERE DATEDIFF(to_date, from_date) < {integer};

 <= SELECT count(emp_no) FROM employees.employees WHERE emp_no IN ( SELECT emp_no FROM employees.salaries WHERE DATEDIFF(to_date, from_date) < {integer});

 

Without the rule

 

Add the rewrite rule

 

With the rule

The query time is dropped from 12.93 s to 3.77

 

 

Ex 2

Limit query execution time; ie add MySQL 5.7 hint /*+ MAX_EXECUTION_TIME(X)*/ that is query duration can’t be more than X milliseconds

Patterns

 -> SELECT count(distinct emp_no) FROM employees.employees INNER JOIN employees.salaries USING(emp_no) WHERE salary = {integer};

 <= SELECT /*+ MAX_EXECUTION_TIME(10000)*/ count(distinct emp_no) FROM employees.employees INNER JOIN employees.salaries USING(emp_no) WHERE salary = {integer};

 

Without the rule

 

Add the rewrite rule

 

With the rule

 

 

Ex 3

Schema evolution; ie table column added (or dropped) but you can’t change the query (or not immediately)

Patterns

 -> SELECT first_name, last_name FROM employees.employees WHERE year(hire_date) = 2000;

 <= SELECT first_name, last_name, birth_date FROM employees.employees WHERE year(hire_date) = 2000;

 

Without the rule

 

Add the rewrite rule

 

With the rule

 

Other ideas ?

– Index hints : the optimizer don’t use the best index, so you can rewrite the query with USE | FORCE | IGNORE index

– Prevent SELECT with “infinite result, so you can rewrite the query adding LIMIT 1000 or whatever.

Like mysql client text –safe-update option

Sky is the limit 🙂

 

The Rewriter plugin does not replace proper code and server optimisation neither fine tuned query.

Nevertheless it can be very useful.

 

Give it a try, it definitely worth more than 30 minutes.

 

 

Going further

Post-parse query rewrite plugin

 

Pre-parse query rewrite plugin

 

Thank you for using MySQL!

0

30 mins with JSON in MySQL

November 17, 2015

Lire cet article en français

Note: This article is inspired by Morgan Tocker‘s talk MySQL 5.7 + JSON.

As you may know MySQL 5.7 is GA and has over than 150 new features. One of them is a Native JSON Data Type and JSON Functions: “Allows for efficient and flexible storage, search and manipulation of schema-less data. Enhancements include a new internal binary format, support for easy integration within SQL, and index management on the JSON Documents using generated columns”.

Sounds interesting! Let’s take half an hour to have a foretaste…

 

Get JSON documents

First let’s get data in JSON format. Mirco Zeiss provides a really big JSON file representing san francisco’s subdivision parcels (from SF Open Data) at https://github.com/zemirco/sf-city-lots-json

To use these data some tweaks are necessary:

Looks good!

Note: The size of JSON documents stored in JSON columns is limited to the value of the max_allowed_packet system variable. (While the server manipulates a JSON value internally in memory, it can be larger; the limit applies when the server stores it.).

 

Our JSON document will be stored in an InnoDB table: features

 

Another way to store JSON documents is to put them in a string (VARCHAR or TEXT).

Let’s see if there are some differences between JSON documents stored in a string or in a JSON column.

 

Note: TEXT type is not large enough to handle our JSON data. (ERROR 1406 (22001): Data too long for column ‘feature’ at row 17360). LONGTEXT will do the job.

 

Populate tables

Note: In order to have a better idea of query execution time on my old (and not so stable) laptop with a small MySQL config (e.g. Buffer pool = 128MB), I ran the queries many time using mysqlslap: mysqlslap -c1 -i <N> { Concurrency = 1 / Iteration > 20 (depending on the query duration) }

So most of the time I’ll show mysqlslap output e.g. “Minimum number of seconds to run all queries: 59.392 seconds

provides by mysqlslap instead of regular query output e.g. “Query OK, 206560 rows affected (59.39 sec)“.

 

Copy JSON data in features

Minimum number of seconds to run all queries: 59.392 seconds

 

Copy JSON data in features_TEXT

Minimum number of seconds to run all queries: 39.784 seconds

 

Loading 206560 records into my respective tables shows performance difference about 40% slower in JSON column compare to TEXT.

However, be aware that MySQL JSON data type provides:

  • Automatic validation of JSON documents stored in JSON columns. Meaning that invalid documents produce an error.
  • Optimized storage format. JSON documents stored in JSON columns are converted to an internal format that permits quick read access to document elements. When the server later must read a JSON value stored in this binary format, the value need not be parsed from a text representation. The binary format is structured to enable the server to look up subobjects or nested values directly by key or array index without reading all values before or after them in the document.

 

Nothing comparable with the TEXT data type, in other words these features have a cost, logic and fair!

 

Let’s have a look on tables metadata:

 

 

Interesting point here, LONGTEXT data type consume more space than the JSON (optimized storage format) data type, about 20% more.

 

Return data from a JSON document

MySQL 5.7 provides a bunch of JSON functions.

JSON_EXTRACT returns data from a JSON document. Furthermore since MySQL 5.7.9 you can use inlined JSON path expressions that simplifies queries that deal with JSON data and make them more human-readable:

e.g.

JSON_EXTRACT(col, “$.json_field”) is similar to col->”$.json_field”

 

So how about retrieve our JSON documents?

Table with JSON data type

Minimum number of seconds to run all queries: 4.470 seconds

 

Table with TEXT data type

Minimum number of seconds to run all queries: 29.365 seconds

 

Get these documents implies a full table scan (no surprise).

However we can see the power of the MySQL JSON internal format that permits quick read access to document elements.

In this example the query execution time is about 7 times faster with JSON data type compare to TEXT.

 

Generated column

JSON columns cannot be indexed. BUT you can work around this restriction by creating an index on a generated column that extracts a scalar value from the JSON column. Generated columns can either be materialized (stored) or non-materialized (virtual).

Create a generated column is quite easy. And a VIRTUAL one is costless because column values are not stored, but are evaluated when rows are read, immediately after any BEFORE triggers.

A virtual column takes no storage. It’s usually what you’ll need in real life.

 

Last but not least it’s only about metadata change so adding a virtual column is fast (no table rebuild). It only requires a quick system table update that registers the new metadata.

 

 

Note: The disadvantage of such approach is that values are stored twice; once as the value of the generated column and once in the index.

 

New table descriptions are now:

 

 

Let’s have a look on table metadata:

 

Identical!

As expected data length is respectively the same.

 

Is there any cost difference between selecting a JSON documents from the virtual column and the JSON_EXTRACT function?

Minimum number of seconds to run all queries: 2.790 seconds

 

Minimum number of seconds to run all queries: 25.933 seconds

 

Obviously the QEP is the same: Full Table Scan (FTS).

Anyway 2 comments:

  • MySQL JSON internal format is still more efficient than TEXT data type, in this example query execution time is about 8 times faster with JSON.
  • In this example FTS on the virtual generated column (feature_type) is faster than the usage of json_extract function on the JSON document in the SELECT clause (from 4.470 to 2.790).

 

Create indexes on generated column

As of MySQL 5.7.8, InnoDB supports secondary indexes on virtual columns.

Adding or dropping a secondary index on a virtual column is an in-place operation.

 

 

New table descriptions are:

 

 

Let’s have another look on table metadata:

 

 

The index on feature_type column is materialized. Its size it’s approximately 6 MB.

 

Now because of this index, the query should be more efficient:

Minimum number of seconds to run all queries: 0.178 seconds

 

Minimum number of seconds to run all queries: 0.178 seconds

 

As expected the optimizer uses the index (feature_type) and the query execution time is much better in both cases (from 2.790 to 0.178 for JSON column).

 

Wrapup

MySQL 5.7 implements native JSON data type support and provides a set of function that allows to Create, Search, Modify JSON values and Return JSON attributes values as well. That’s good and I guess many developers will be happy to use this new feature.

Generated columns is also an interesting feature. It could be used among others to simulate functional indexes, as a materialized cache for often used expressions… or like we did to provide index management on the JSON documents.

 

Give it a try, it definitely worth more than 30 minutes.

 

Want to know more about MySQL 5.7?

 

Going further

MySQL Documentation

The JSON data type

https://dev.mysql.com/doc/refman/5.7/en/json.html

 

JSON Functions

https://dev.mysql.com/doc/refman/5.7/en/json-functions.html

 

CREATE TABLE and Generated Columns

http://dev.mysql.com/doc/refman/5.7/en/create-table.html#create-table-generated-columns

 

MySQL Server Blog

Native JSON Data Type and Binary Format

http://mysqlserverteam.com/json-labs-release-native-json-data-type-and-binary-format/

 

JSON functions

http://mysqlserverteam.com/json-labs-release-json-functions-part-1-manipulation-json-data/

http://mysqlserverteam.com/mysql-5-7-lab-release-json-functions-part-2-querying-json-data/

 

Inline JSON Path Expressions in MySQL 5.7

http://mysqlserverteam.com/inline-json-path-expressions-in-mysql-5-7/

 

Getting Started With MySQL & JSON on Windows

http://mysqlserverteam.com/getting-started-with-mysql-json-on-windows/

 

Effective Functional Indexes in InnoDB

http://mysqlserverteam.com/json-labs-release-effective-functional-indexes-in-innodb/

 

MySQL 5.7

What Is New in MySQL 5.7

https://dev.mysql.com/doc/refman/5.7/en/mysql-nutshell.html

 

Complete list of new features in MySQL 5.7

http://www.thecompletelistoffeatures.com/

 

1

Free ebooks on Packt Publishing website

February 28, 2015
Tags:

 

Packt Publishing started a Free Learning Campaign by providing a free ebook everyday till 6th March 2015.

How ? Follow this link: http://bit.ly/1DoRno5

0

Generate html with the mysql client

September 12, 2013

I’m a big fan of the MySQL command line tool ie the default text client modestly named: mysql. I use it everyday because you can do almost everything with it (DML, DDL, DCL, administrative tasks,…).

It has many options including generate xml or html output.

 

So you can easily script it and generate an HTML document:

 

 

It’s very simple and it’s valid HTML but it is not valid XHTML!

Why, in 2013, a tool generates HTML rather than XHTML ?

1

MySQL Tech Day @Paris on Oct 10, 2013

September 10, 2013

MySQL Tech Day @Paris


We’re happy to announce you that MySQL Tech Day will take place in Paris on Oct 10, 2013 in Oracle main office. It’ll be a full day event giving you an occasion to listen directly from Oracle developers about most of the improvements made recently within MySQL 5.6 and 5.7 development.

 

The agenda is the following :

  • Overview: MySQL Innovation @Oracle

  • MySQL Performance: Latest improvements in MySQL 5.6 & 5.7

  • MySQL Optimizer: What is new, what is coming

  • 50 tips to boost MySQL Performance

  • MySQL Performance Schema: Overview / HOWTO

  • MySQL Performance Schema: Hands-on (live lab)

 

Don’t miss such an opportunity to get most of the hot news about MySQL improvement directly from the source! It’ll be just two weeks after MySQL Connect, so there will be really a lot of news! So, book this date in your agenda right now and then don’t forget to register yourself for the event – attending is totally free, but places are limited, so registration is mandatory to secure enough seats.

 

To register : please, contact Alexis Bensa:

  • by email: alexis.bensa(@)oracle.com

  • or directly by phone: 01.57.60.27.96

 

Oracle main office address: Portes de La Defense 15, boulevard Charles de Gaulle.

 

Further details will be communicated over a time.

update (Oct 2): schedules

0

ALTER Optimization

August 14, 2012

One of my colleague asked me : « Is an ALTER TABLE with many specifications is faster than 1 ALTER TABLE by specification ? »

The answers seems quite intuitive, anyway figures are better than a long speech…

 

Context :

  • MySQL Community Server 5.5.22
  • InnoDB table with 8 539 238 rows.
  • 17 columns with INT, CHAR, VARCHAR, TEXT, DATETIME, … fields

 

 

Adding 3 indexes (with a single command)

 

 

Total: 146.43 sec

 

 

Deleting the index (with a single command)

 

 

Note: With MySQL 5.5, delete secondary indexes (not PK) is costless.

 

 

Adding 3 indexes (with 3 commands)

 

 

 

mysql5.5> ALTER TABLE lien ADD KEY IdxPaysLangueCreation (Pays,Langue,Creation);

Query OK, 0 rows affected (1 min 7.24 sec)

Records: 0 Duplicates: 0 Warnings: 0

 

Total: 151.16 sec (4 % slower)

 

Of course, more data to moved implies a wider gap between the 2 methods

 

 

 

Deleting the index (with 3 commands)

 

 

 

 

Total: 1.77 sec

 

QED

 

 

Divers

 

 

 

 

0