Planet MySQL

Percona Live Europe Featured Talks: Monitoring Open Source Databases with Icinga with Bernd Erk

Welcome to another post in our series of interview blogs for the upcoming Percona Live Europe 2017 in Dublin. This series highlights a number of talks that will be at the conference and gives a short preview of what attendees can expect to learn from the presenter.

This blog post is with Bernd Erk, CEO of Icinga. His talk is titled Monitoring Open Source Databases with Icinga. Icinga is a popular open source successor of Nagios that checks hosts and services, and notifies you of their statuses. But you also need metrics for performance and growth to deal with your scaling needs. Adding conditional behaviors and configuration in Icinga is not just intuitive, but also intelligently adaptive at runtime. In our conversation, we how to intelligently monitor open source databases:

Percona: How did you get into database technology? What do you love about it?

Bernd: I started a position as a junior systems engineer in a large German mail order company. They were totally committed to Oracle databases and the tool stack around it. As Linux gained more and more attention, we became aware of MySQL very early and were fascinated by the simplicity of installation and administration. There were of course so many things Oracle had in those days that MySQL didn’t have, but most of our uses also didn’t require those extra (and of course expensive) features.

Percona: You’re presenting a session called “Monitoring Open Source Databases with Icinga”. Why is monitoring databases important, and what sort of things need to be monitored?

Bernd: Usually databases are a very important part of an IT infrastructure, and need to be online 24/7. I also had the personal experience of database downtime putting a lot of pressure on both the organization in general and the team in charge. Since most open source databases provide very good interfaces, it is not so hard to figure out if they are up and running. Like in many monitoring arenas, knowing what to monitor is the important information.

In addition to the basic local and remote availability checks, monitoring database replication is very important. We often see environments where the standby slave is outdated by, years or not able to keep up with the incoming load. From there you can go into databases and application metrics to learn more about performance and IO behavior.

Percona: Why are you using Icinga specifically? What value does it provide above other monitoring solutions?

Bernd: I’ve been involved with Icinga from the beginning, so it is my number one choice in open source monitoring. In my opinion, the great advance of Icinga 2 is the simplicity of legacy systems like Nagios (or Icinga 1), but also its support for complex environments (such as application-based clustering). There is also the live configuration of the Icinga 2 monitoring core through our REST API. With all the supported tools for metrics, logs and management around it, for me Icinga 2 is the best match for open source monitoring.

Percona: What do you want attendees to take away from your session? Why should they attend?

Bernd: Attendees will get a short overview on Icinga 2, and why it is different to Nagios (Icinga 1). I will also guide them through practical monitoring examples and show implemented checks in a live demo. After my talk, they should be able to adapt and extend on-premise or cloud monitoring with Icinga 2 using the default open source plugins.

Percona: What are you most looking forward to at Percona Live Europe 2017?

Bernd: Getting together with the great database community in all aspects, and going to Dublin (to be honest). I have never been there, and so it is my first time.

Want to find out more about Bernd and database monitoring? Register for Percona Live Europe 2017, and see his talk Monitoring Open Source Databases with Icinga. Register now to get the best price! Use discount code SeeMeSpeakPLE17 to get 10% off your registration.

Percona Live Open Source Database Conference Europe 2017 in Dublin is the premier European open source event for the data performance ecosystem. It is the place to be for the open source community as well as businesses that thrive in the MySQL, MariaDB, MongoDB, time series database, cloud, big data and Internet of Things (IoT) marketplaces. Attendees include DBAs, sysadmins, developers, architects, CTOs, CEOs, and vendors from around the world.

The Percona Live Open Source Database Conference Europe will be September 25-27, 2017 at the Radisson Blu Royal Hotel, Dublin.

gh-ost 1.0.42 released: JSON support, optimizations

gh-ost 1.0.42 is released and available for download.

JSON

MySQL 5.7's JSON data type is now supported.

There is a soft-limitation, that your JSON may not be part of your PRIMARY KEY. Currently this isn't even supported by MySQL anyhow.

Performance

Two noteworthy changes are:

  • Client side prepared statements reduce network traffic and round trips to the server.
  • Range query iteration avoids creating temporary tables and filesorting.

We're not running benchmarks at this time to observe performance gains.

5.7

More tests validating 5.7 compatibility (at this time GitHub runs MySQL 5.7 in production).

Ongoing

Many other changes included.

We are grateful for all community feedback in form of open Issues, Pull Requests and questions!

gh-ost is authored by GitHub. It is free and open source and is available under the MIT license.

Speaking

In two weeks time, Jonah Berquist will present gh-ost: Triggerless, Painless, Trusted Online Schema Migrations at Percona Live, Dublin.

Tom Krouper and myself will present MySQL Infrastructure Testing Automation at GitHub, where, among other things, we describe how we test gh-ost in production.

Percona Live Europe Featured Talks: Visualize Your Data with Grafana Featuring Daniel Lee

Welcome to another post in our series of interview blogs for the upcoming Percona Live Europe 2017 in Dublin. This series highlights a number of talks that will be at the conference and gives a short preview of what attendees can expect to learn from the presenter.

This blog post is with Daniel Lee, a software developer at Grafana. His tutorial is Visualize Your Data With Grafana. This presentation teaches you how to create dashboards and graphs in Grafana and how to use them to gain insight into the behavior of your systems. In our conversation, we discussed how data visualization could benefit your database environment:

Percona: How did you get into database technology? What do you love about it?

Daniel: I’m a developer and my first job was working on a transport logistics system, which was mostly composed of Stored Procedures in SQL Server 2000. Today, I would not build a system with all the logic in Stored Procedures – but that database knowledge is the foundation that I built everything else on. Databases and their data flows will always be the core of most interesting systems. More recently, I have switched from Windows to working with MariaDB on Linux. Grafana Labs uses Percona Server for MySQL for most of our internal applications (worldPing and Hosted Grafana). Working with Grafana also means working with time series databases like Graphite, which is also very interesting.

I enjoy working with data as it is one of the ways to learn how users use a system. Design decisions are theories until you have data to either back them up or disprove them.

Percona: Your presenting a session called “Visualize Your Data With Grafana”. How does monitoring make DBAs life easier, and how do graphs make this information easier to apply for DBAs?

Daniel: Good monitoring provides top-level metrics (throughput, number of errors, performance) for alerting, and other lower-level metrics to allow you to dig into the details and quickly diagnose and resolve an outage. Monitoring also helps you find any constraints (for example, finding bottlenecks for query performance: CPU, row locks, disk, buffer pool size, etc.). Performance monitoring allows you to see trends and lets you know when it is time to scale out or purchase more hardware.

Monitoring can also be used to communicate with business people. It is a way of translating lots of different system metrics into a measurable user experience. Visualizing your data with graphs is a very good way to communicate that information, both within your team and with your business stakeholders. Building dashboards with the metrics that are important to you rather than just the standard checklists (CPU, disk, network etc.) allows you to measure the user experience for your application and to see long-term trends.

Percona: Why Grafana? What does Grafana do better than other monitoring solutions?

Daniel: Grafana is the de facto standard in open source for visualizing time series data. It comes with tons of different ways to visualize your data (graphs, heat maps, gauges). Each data source comes with its own custom query editor that simplifies writing complex queries, and it is easy to create dynamic dashboards that look great on a TV.

Being open source, it can be connected to any data source/database, which makes it easy to unify different data sources in the same dashboard (for example, Prometheus or Graphite data combined with MySQL data). This also means your data is not subject to vendor lock-in like it is in other solutions. Grafana has a large and very active community that creates plugins and dashboards that extend Grafana into lots of niches, as well as providing ways to quickly get started with whatever you want to monitor.

Percona: What do you want attendees to take away from your session? Why should they attend?

Daniel: I want them to know that you can make the invisible visible, with that knowledge start to make better decisions based on data. I hope that my session helps someone take the first step to being more proactive in their monitoring by showing them what can be done with Grafana and other tools in the monitoring space.

In my session, I will give an overview of monitoring and metrics, followed by an intro to Grafana. I plan to show how to monitor MySQL and finish off with a quick look at the new MySQL data source for Grafana.

Percona: What are you most looking forward to at Percona Live Europe 2017?

Daniel: Firstly, it is always great to have an excuse to visit Ireland (I’m an Irishman living in Sweden). I’m also looking forward to getting feedback from the community on Grafana’s new MySQL data source plugin, as well as just talking to people and hearing about their experiences with database monitoring.

Want to find out more about Daniel and data visualization? Register for Percona Live Europe 2017, and see their talk Visualize Your Data With Grafana. Register now to get the best price! Use discount code SeeMeSpeakPLE17 to get 10% off your registration.

Percona Live Open Source Database Conference Europe 2017 in Dublin is the premier European open source event for the data performance ecosystem. It is the place to be for the open source community as well as businesses that thrive in the MySQL, MariaDB, MongoDB, time series database, cloud, big data and Internet of Things (IoT) marketplaces. Attendees include DBAs, sysadmins, developers, architects, CTOs, CEOs, and vendors from around the world.

The Percona Live Open Source Database Conference Europe will be September 25-27, 2017 at the Radisson Blu Royal Hotel, Dublin.

Massive Parallel Log Processing with ClickHouse

In this blog, I’ll look at how to use ClickHouse for parallel log processing.

Percona is seen primarily for our expertise in MySQL and MongoDB (at this time), but neither is quite suitable to perform heavy analytical workloads. There is a need to analyze data sets, and a very popular task is crunching log files. Below I’ll show how ClickHouse can be used to efficiently perform this task. ClickHouse is attractive because it has multi-core parallel query processing, and it can even execute a single query using multiple CPUs in the background.

I am going to check how ClickHouse utilizes multiple CPU cores and threads. I will use a server with two sockets, equipped with “Intel(R) Xeon(R) CPU E5-2683 v3 @ 2.00GHz” in each. That gives a total of 28 CPU cores / 56 CPU threads.

To analyze workload, I’ll use an Apache log file from one of Percona’s servers. The log has 1.56 billion rows, and uncompressed it takes 274G. When inserted into ClickHouse, the table on disk takes 9G.

How do we insert the data into ClickHouse? There is a lot of scripts to transform Apache log format to CSV, which ClickHouse can accept. As for the base, I used this one:

https://gist.github.com/sepehr/fff4d777509fa7834531

and my modification you can find here:

https://github.com/vadimtk/clickhouse-misc/blob/master/apachelog-to-csv.pl

The ClickHouse table definition:

CREATE TABLE default.apachelog ( remote_host String, user String, access_date Date, access_time DateTime, timezone String, request_method String, request_uri String, status UInt32, bytes UInt32, referer String, user_agent String) ENGINE = MergeTree(access_date, remote_host, 8192)

To test how ClickHouse scales on multiple CPU cores/threads, I will execute the same query by allocating from 1 to 56 CPU threads for ClickHouse processes. This can be done as:

ps -eLo cmd,tid | grep clickhouse-server | perl -pe 's/.* (d+)$/1/' | xargs -n 1 taskset -cp 0-$i

where $i is (N CPUs-1).

We must also take into account that not all queries are equal. Some are easier to execute in parallel than others. So I will test three different queries. In the end, we can’t get around Amdahl’s Law!

The first query should be easy to execute in parallel:

select extract(request_uri,'(w+)$') p,sum(bytes) sm,count(*) c from apachelog group by p order by c desc limit 100

Speedup:

CPUs Time, sec Speedup to 1 CPU 1 823.646 1 2 413.832 1.990291 3 274.548 3.000007 4 205.961 3.999039 5 164.997 4.991885 6 137.455 5.992114 7 118.079 6.975381 8 103.015 7.995399 9 92.01 8.951701 10 82.853 9.941052 11 75.334 10.93326 12 69.23 11.89724 13 63.848 12.90011 14 59.388 13.8689 15 55.433 14.85841 16 52.158 15.79136 17 49.054 16.7906 18 46.331 17.77743 19 43.985 18.72561 20 41.795 19.70681 21 39.763 20.71388 22 38.031 21.65723 23 36.347 22.66063 24 34.917 23.58868 25 33.626 24.49432 26 32.42 25.40549 27 31.21 26.39045 28 30.135 27.33187 29 29.947 27.50346 30 29.709 27.72379 31 29.283 28.1271 32 28.979 28.42217 33 28.807 28.59187 34 28.477 28.9232 35 28.146 29.26334 36 27.921 29.49916 37 27.613 29.8282 38 27.366 30.09742 39 27.06 30.43777 40 26.817 30.71358 41 26.644 30.913 42 26.394 31.2058 43 26.215 31.41888 44 25.994 31.686 45 25.762 31.97135 46 25.554 32.23159 47 25.243 32.62869 48 25.102 32.81197 49 24.946 33.01716 50 24.668 33.38925 51 24.537 33.56751 52 24.278 33.92561 53 24.035 34.26861 54 23.839 34.55036 55 23.734 34.70321 56 23.587 34.91949

 

It’s much more interesting to chart these results:

From the chart, we can see that the query scales linearly up to 28 cores. After that, it continues to scale up to 56 threads (but with a lesser slope). I think this is related to the CPU architecture (remember we have 28 physical cores and 56 CPU “threads”). Let’s look at the results again. With one available CPU, the query took 823.6 sec to execute. With all available CPUs, it took 23.6 sec. So the total speedup is 34.9 times.

But let’s consider a query that allows a lesser degree of parallelism. For example, this one:

select access_date c2, count(distinct request_uri) cnt from apachelog group by c2 order by c2 limit 300

This query uses aggregation that counts unique URIs, which I am sure limits the counting process to a single shared structure. So some part of the execution is limited to a single process. I won’t show the full results for all 1 to 56 CPUs, but for one CPU the execution time is 177.715 sec, and for 56 CPUs the execution time is 11.564 sec. The total speedup is 15.4 times.

The speedup chart looks like this:

As we suspected, this query allows less parallelism. What about even heavier queries? Let’s consider this one:

SELECT y, request_uri, cnt FROM (SELECT access_date y, request_uri, count(*) AS cnt FROM apachelog GROUP BY y, request_uri ORDER BY y ASC ) ORDER BY y,cnt DESC LIMIT 1 BY y

In that query, we build a derived table (to resolve the subquery) and I expect it will limit the parallelism even further. And it does: with one CPU the query takes 183.063 sec to execute. With 56 CPUs it takes 28.572 sec. So the speedup is only 6.4 times.

The chart is:

Conclusions

ClickHouse can capably utilize multiple CPU cores available on the server, and query execution is not limited by a single CPU (like in MySQL). The degree of parallelism is defined by the complexity of the query, and in the best case scenario, we see linear scalability with the number of CPU cores. For the scaling on multiple servers you can see my previous post:

https://www.percona.com/blog/2017/06/22/clickhouse-general-analytical-workload-based-star-schema-benchmark/

However, if query execution is serial, it limits the speedup (as described in Amdahl’s Law).

One example is a 1.5 billion record Apache log, and we can see that ClickHouse can execute complex analytical queries within tens of seconds.

cscope: Searching Code Efficiently

In this post, we will discuss how to search code with the help of cscope. Let’s begin by checking its description and capabilities (quoting directly from http://cscope.sourceforge.net/):

Cscope is a developer’s tool for browsing source code.

  • Allows searching code for:
    • all references to a symbol
    • global definitions
    • functions called by a function
    • functions calling a function
    • text string
    • regular expression pattern
    • a file
    • files including a file
  • Curses based (text screen)
  • An information database is generated for faster searches and later reference
  • The fuzzy parser supports C, but is flexible enough to be useful for C++ and Java, and for use as a generalized ‘grep database’ (use it to browse large text documents!)

Of course, developers aren’t the only ones browsing the code (as implied by the tool’s description). In the Support team, we find ourselves having to check code many times. This tool is a great aid in doing so. As you can imagine already, this tool can replace find and grep -R "<keyword(s)>" *, and will even add more functionality! Not only this, but our searches run faster (since they are indexed).

The main focus of this post is to explore cscope’s searching capabilities regarding code, but note that you can also use it for text searches that aren’t linked to function names or symbols (supporting regular expressions) and for file searches. This also means that even if the tool doesn’t recognize a function name, you can still use the text search as a fallback.

There is an online manual page, for quick reference:

http://cscope.sourceforge.net/cscope_man_page.html

To install it under RHEL/CentOS, simply issue:

shell> yum install cscope

You can use cscope with MySQL, Percona Server for MySQL or MariaDB code alike. In my case, I had a VM with Percona Server for MySQL 5.7.18 already available, so I’ve used that for demonstration purposes.

We should first get the source code for the exact version we are working with, and build the cscope database (used by the tool to perform searches):

shell> wget https://www.percona.com/downloads/Percona-Server-LATEST/Percona-Server-5.7.18-15/source/tarball/percona-server-5.7.18-15.tar.gz shell> tar xzf percona-server-5.7.18-15.tar.gz shell> cd percona-server-5.7.18-15 shell> cscope -bR

-b will build the database only, without accessing the CLI; -R will recursively build the symbol database from the directory it’s executed, down. We can also add -q for fast symbol lookup, at the expense of a larger database (we’ll check how much more below).

Now that we have built the cscope database, we will see a new file created: cscope.out. If we used -q, we will also see: cscope.in.out and cscope.po.out. Their sizes depend on the size of the codebase in question. Here are the sizes before and after building the cscope database (with -q):

shell> du -d 1 -h .. 615M ../percona-server-5.7.18-15 shell> cscope -bqR shell> du -h cscope.* 8.2M cscope.in.out 69M cscope.out 103M cscope.po.out shell> du -d 1 -h .. 794M ../percona-server-5.7.18-15

This gives around 30% increase in size while using -q, and around 10% increase without it. Your mileage may vary: be aware of this if you are using it on a test server with many different versions, or if the project size is considerably larger. It shouldn’t be much of a problem, but it’s something to take into account.

Ok, enough preamble already, let’s see it in action! To access the CLI, we can use cscope -d.

A picture is worth a thousand words. The following output corresponds to searching for the MAX_MAX_ALLOWED_PACKET symbol:

If there are multiple potential matches, the tool lists them for our review. If there is only one match, it will automatically open the file, with the cursor at the appropriate position. To check a match, either select it with the arrow keys and hit enter, or use the number/letter listed. When you are done and need to get back to cscope to continue checking other matches, simply exit the text editor (which can be defined by using CSCOPE_EDITOR). To get back to the main menu to modify the search, press CTRL-f. To exit the tool press CTRL-d. Lastly, CTRL-c toggles case insensitive mode on and off.

To show how the tool displays searches with many hits, let’s search for functions that call printf:

We can now see that letters are also used to list options, and that we can hit space to page down for more matches (from a total of 4508).

Lastly, as mentioned before if everything else fails and you are not able to find the function or symbol you need (due to limitations or bugs), you can use the “Find this text string” and “Find this egrep pattern” functionality.

I hope this brief tour of cscope has been useful, and helps you get you started using it. Note that you can use it for other projects, and it can be handy if you need to dive into the Linux kernel too.

Addendum

For even more power, you can read this vim tutorial (http://cscope.sourceforge.net/cscope_vim_tutorial.html), or set up ctags (http://ctags.sourceforge.net/) along with cscope.

Log Buffer #521: A Carnival of the Vanities for DBAs

While cloud technologies are roaring ahead in full force; the traditional RDBMS like Oracle, Microsoft SQL Server and MySQL are adapting pretty fast. This Log Buffer Edition covers blogs related to that and more.

Oracle:

Oracle JET Simple Table with Fake JSON Server

Every time any page is requested from your website by a human or another program or an automated bot, the event is tracked in a log file that is stored on the web server.

Gitora 2 enabled developers to manage database objects that support the CREATE OR REPLACE command, namely PACKAGES, PROCEDURES, FUNCTIONS, TRIGGERS, VIEWS, SYNONYMS and TYPES in a Git repository.

Oracle just released its first REST JDBC driver on OTN, in conjunction with the 17.3.0 Oracle REST Data Services Beta release.

When you are restrained in the JRE that you can use with SQLcl, you can embed your own in the sqlcl directory tree.

SQL Server:

Understanding Azure Data Factory – A Cloud Based Integration Service

The Shortcomings of Predictive Accuracy

Setting Variables in Calling T-SQL Code While Using sp_executesql

Azure DWH part 15: PolyBase and Azure Data Lake

Creating Basic Indexes on the Stack Overflow Public Database

MySQL:

Heads Up: The List of Replication Defaults That Have Changed in 8.0.2

IO-bound table scan performance for MyRocks, InnoDB and TokuDB

MyRocks Experimental Now Available with Percona Server for MySQL 5.7.19-17

Timing load & index for sysbench tables

Automatic Partition Maintenance in MariaDB

Upcoming Webinar September 14, 2017: Supercharge Your Analytics with ClickHouse

Join Percona’s CTO Vadim Tkachenko @VadimTk and Altinity’s Co-Founder, Alexander Zaitsev as they present Supercharge Your Analytics with ClickHouse on Thursday, September 14, 2017, at 10:00 am PDT / 1:00 pm EDT (UTC-7).

Reserve Your Spot

 

ClickHouse is a real-time analytical database system. Even though they’re only celebrating one year as open source software, it has already proved itself ready for serious workloads.

We will talk about ClickHouse in general, some of its internals and why it is so fast. ClickHouse works in conjunction with MySQL – traditionally weak for analytical workloads – and this presentation demonstrates how to make the two systems work together.

There will also be an in-person presentation on How to Build Analytics for 100bn Logs a Month with ClickHouse at the meetup Wednesday, September 13, 2017. RSVP here.

Alexander Zaitsev will also be speaking at Percona Live Europe 2017 on Building Multi-Petabyte Data Warehouses with ClickHouse on Wednesday, September 27 at 11:30 am. Use the promo code “SeeMeSpeakPLE17” for 15% off.

Alexander Zaitsev
Altinity’s Co-Founder
Alexander is a co-founder of Altinity. He has 20 years of engineering and engineering management experience in several international companies. Alexander is expert in high scale analytics systems design and implementation. He designed and deployed petabyte scale data warehouses, including one of earliest ClickHouse deployments outside of Yandex.

Vadim Tkachenko
CTO
Vadim Tkachenko co-founded Percona in 2006 and serves as its Chief Technology Officer. Vadim leads Percona Labs, which focuses on technology research and performance evaluations of Percona’s and third-party products. Percona Labs designs no-gimmick tests of hardware, filesystems, storage engines, and databases that surpass the standard performance and functionality scenario benchmarks. Vadim’s expertise in LAMP performance and multi-threaded programming help optimize MySQL and InnoDB internals to take full advantage of modern hardware. Oracle Corporation and its predecessors have incorporated Vadim’s source code patches into the mainstream MySQL and InnoDB products.

He also co-authored the book High Performance MySQL: Optimization, Backups, and Replication 3rd Edition. Previously, he founded a web development company in his native Ukraine and spent two years in the High Performance Group within the official MySQL support team. Vadim received a BS in Economics and an MS in computer science from the National Technical University of Ukraine. He now lives in California with his wife and two children.

Native ProxySQL Cluster - part 2

ProxySQL Cluster management

In the previous blog post we described what ProxySQL Cluster is, and how creating new users and new query rules in one of the node will result in all the other nodes being immediately aware of it and automatically sync.
All looks very straightforward if all the nodes are all up and running when we make a configuration change. But how to handle cases in which nodes are not available, or they will join later? How to handle conclicts?

Disclaimer
Before going forward, please remember that those features are EXPERIMENTAL and subject to changes.

Automatic node re-joining

In this exercise we will see what happen if a node get out of sync.
Specifically, we will shutdown proxysql132, apply some change in mysql_query_rules from one of the nodes, and re-start proxysql132.

Let's start shutting down proxysql132.

From the output below, we can see that proxysql130 isn't getting the checksum from proxysql132, specifically updated_at is not being updated, that means that proxysql132 is not running (we shut it down):

Admin130> SELECT hostname, port, name, version, FROM_UNIXTIME(epoch) epoch, checksum, FROM_UNIXTIME(changed_at) changed_at, FROM_UNIXTIME(updated_at) updated_at, diff_check, DATETIME('NOW') FROM stats_proxysql_servers_checksums WHERE name='mysql_query_rules' ORDER BY hostname; +--------------+------+-------------------+---------+---------------------+--------------------+---------------------+---------------------+------------+---------------------+ | hostname | port | name | version | epoch | checksum | changed_at | updated_at | diff_check | DATETIME('NOW') | +--------------+------+-------------------+---------+---------------------+--------------------+---------------------+---------------------+------------+---------------------+ | 172.16.3.130 | 6032 | mysql_query_rules | 2 | 2017-09-05 23:04:51 | 0xE2F5A21142C799C0 | 2017-09-05 23:04:51 | 2017-09-05 23:16:17 | 0 | 2017-09-05 23:16:17 | | 172.16.3.131 | 6032 | mysql_query_rules | 2 | 2017-09-05 23:04:52 | 0xE2F5A21142C799C0 | 2017-09-05 23:04:52 | 2017-09-05 23:16:17 | 0 | 2017-09-05 23:16:17 | | 172.16.3.132 | 6032 | mysql_query_rules | 2 | 2017-09-05 23:04:52 | 0xE2F5A21142C799C0 | 2017-09-05 23:04:52 | 2017-09-05 23:12:17 | 0 | 2017-09-05 23:16:17 | +--------------+------+-------------------+---------+---------------------+--------------------+---------------------+---------------------+------------+---------------------+ 3 rows in set (0.00 sec) Admin130> SELECT hostname, checksum, FROM_UNIXTIME(changed_at) changed_at, FROM_UNIXTIME(updated_at) updated_at FROM stats_proxysql_servers_checksums WHERE name='mysql_query_rules' ORDER BY hostname; +--------------+--------------------+---------------------+---------------------+ | hostname | checksum | changed_at | updated_at | +--------------+--------------------+---------------------+---------------------+ | 172.16.3.130 | 0xE2F5A21142C799C0 | 2017-09-05 23:04:51 | 2017-09-05 23:16:21 | | 172.16.3.131 | 0xE2F5A21142C799C0 | 2017-09-05 23:04:52 | 2017-09-05 23:16:21 | | 172.16.3.132 | 0xE2F5A21142C799C0 | 2017-09-05 23:04:52 | 2017-09-05 23:12:17 | +--------------+--------------------+---------------------+---------------------+ 3 rows in set (0.00 sec)

We will now make some useless change in mysql_query_rules, just to show the impact of changing rules:

Admin130> SELECT rule_id,schemaname,destination_hostgroup,apply FROM mysql_query_rules WHERE active=1; +---------+------------+-----------------------+-------+ | rule_id | schemaname | destination_hostgroup | apply | +---------+------------+-----------------------+-------+ | 1 | shard001 | 1 | 1 | | 2 | shard002 | 1 | 1 | | 3 | shard003 | 1 | 1 | | 4 | shard004 | 1 | 1 | | 5 | shard005 | 2 | 1 | | 6 | shard006 | 2 | 1 | | 7 | shard007 | 2 | 1 | | 8 | shard008 | 2 | 1 | +---------+------------+-----------------------+-------+ 8 rows in set (0.00 sec) Admin130> UPDATE mysql_query_rules SET rule_id=rule_id+8, destination_hostgroup=destination_hostgroup+4; Query OK, 8 rows affected (0.00 sec) Admin130> SELECT rule_id,schemaname,destination_hostgroup,apply FROM mysql_query_rules WHERE active=1; +---------+------------+-----------------------+-------+ | rule_id | schemaname | destination_hostgroup | apply | +---------+------------+-----------------------+-------+ | 9 | shard001 | 5 | 1 | | 10 | shard002 | 5 | 1 | | 11 | shard003 | 5 | 1 | | 12 | shard004 | 5 | 1 | | 13 | shard005 | 6 | 1 | | 14 | shard006 | 6 | 1 | | 15 | shard007 | 6 | 1 | | 16 | shard008 | 6 | 1 | +---------+------------+-----------------------+-------+ 8 rows in set (0.00 sec)

And finally, we load at runtime:

Admin130> SELECT hostname, checksum, FROM_UNIXTIME(changed_at) changed_at, FROM_UNIXTIME(updated_at) updated_at FROM stats_proxysql_servers_checksums WHERE name='mysql_query_rules' ORDER BY hostname; +--------------+--------------------+---------------------+---------------------+ | hostname | checksum | changed_at | updated_at | +--------------+--------------------+---------------------+---------------------+ | 172.16.3.130 | 0xE2F5A21142C799C0 | 2017-09-05 23:04:51 | 2017-09-05 23:21:00 | | 172.16.3.131 | 0xE2F5A21142C799C0 | 2017-09-05 23:04:52 | 2017-09-05 23:21:00 | | 172.16.3.132 | 0xE2F5A21142C799C0 | 2017-09-05 23:04:52 | 2017-09-05 23:12:17 | +--------------+--------------------+---------------------+---------------------+ 3 rows in set (0.00 sec) Admin130> LOAD MYSQL QUERY RULES TO RUNTIME; Query OK, 0 rows affected (0.00 sec) Admin130> SELECT hostname, checksum, FROM_UNIXTIME(changed_at) changed_at, FROM_UNIXTIME(updated_at) updated_at FROM stats_proxysql_servers_checksums WHERE name='mysql_query_rules' ORDER BY hostname; +--------------+--------------------+---------------------+---------------------+ | hostname | checksum | changed_at | updated_at | +--------------+--------------------+---------------------+---------------------+ | 172.16.3.130 | 0x81A6E7C4159B081D | 2017-09-05 23:21:05 | 2017-09-05 23:21:07 | | 172.16.3.131 | 0x81A6E7C4159B081D | 2017-09-05 23:21:06 | 2017-09-05 23:21:07 | | 172.16.3.132 | 0xE2F5A21142C799C0 | 2017-09-05 23:04:52 | 2017-09-05 23:12:17 | +--------------+--------------------+---------------------+---------------------+ 3 rows in set (0.00 sec)

proxysql131 immediately took the change from proxysql130, but proxysql132 is down, so it won't take the change.

Now, let's start proxysql132 and see what happens...

2017-09-06 00:22:51 main.cpp:703:ProxySQL_daemonize_phase2(): [INFO] Starting ProxySQL 2017-09-06 00:22:51 main.cpp:704:ProxySQL_daemonize_phase2(): [INFO] Sucessfully started Main daemonize phase1 completed in 0.00089 secs. 2017-09-06 00:22:51 main.cpp:714:ProxySQL_daemonize_phase3(): [INFO] Angel process started ProxySQL process 188934 Standard ProxySQL Cluster rev. 0.1.0702_DEBUG -- ProxySQL_Cluster.cpp -- Wed Sep 6 00:10:30 2017 Admin initialized in 0.019364 secs. Standard ProxySQL Admin rev. 0.2.0902_DEBUG -- ProxySQL_Admin.cpp -- Tue Sep 5 12:20:48 2017 Standard MySQL Threads Handler rev. 0.2.0902_DEBUG -- MySQL_Thread.cpp -- Tue Sep 5 12:20:48 2017 Main phase3 : GloMyLogger initialized in 4e-06 secs. Standard MySQL Authentication rev. 0.2.0902_DEBUG -- MySQL_Authentication.cpp -- Wed Sep 6 00:10:30 2017 Main init phase2 completed in 0.022463 secs. Main phase3 : GloMyLogger initialized in 3e-06 secs. 2017-09-06 00:22:51 MySQL_HostGroups_Manager.cpp:880:generate_mysql_servers_table(): [INFO] Dumping current MySQL Servers structures for hostgroup ALL 2017-09-06 00:22:51 MySQL_HostGroups_Manager.cpp:971:generate_mysql_replication_hostgroups_table(): [INFO] New mysql_replication_hostgroups table 2017-09-06 00:22:51 MySQL_HostGroups_Manager.cpp:1007:generate_mysql_group_replication_hostgroups_table(): [INFO] New mysql_group_replication_hostgroups table 2017-09-06 00:22:51 MySQL_HostGroups_Manager.cpp:880:generate_mysql_servers_table(): [INFO] Dumping current MySQL Servers structures for hostgroup ALL 2017-09-06 00:22:51 ProxySQL_Cluster.cpp:238:ProxySQL_Node_Entry(): [INFO] Created new Cluster Node Entry for host 172.16.3.130:6032 2017-09-06 00:22:51 ProxySQL_Cluster.cpp:238:ProxySQL_Node_Entry(): [INFO] Created new Cluster Node Entry for host 172.16.3.131:6032 2017-09-06 00:22:51 ProxySQL_Cluster.cpp:238:ProxySQL_Node_Entry(): [INFO] Created new Cluster Node Entry for host 172.16.3.132:6032 Main phase3 : GloAdmin initialized in 0.003573 secs. Standard Query Processor rev. 0.2.0902_DEBUG -- Query_Processor.cpp -- Tue Sep 5 12:20:48 2017 Main phase3 : Query Processor initialized in 0.000246 secs. 2017-09-06 00:22:51 ProxySQL_Cluster.cpp:53:ProxySQL_Cluster_Monitor_thread(): [INFO] Cluster: starting thread for peer 172.16.3.131:6032 2017-09-06 00:22:51 ProxySQL_Cluster.cpp:53:ProxySQL_Cluster_Monitor_thread(): [INFO] Cluster: starting thread for peer 172.16.3.130:6032 2017-09-06 00:22:51 ProxySQL_Cluster.cpp:53:ProxySQL_Cluster_Monitor_thread(): [INFO] Cluster: starting thread for peer 172.16.3.132:6032 Main phase3 : MySQL Threads Handler initialized in 0.002774 secs. In memory Standard Query Cache (SQC) rev. 1.2.0905_DEBUG -- Query_Cache.cpp -- Tue Sep 5 12:20:48 2017 2017-09-06 00:22:51 ProxySQL_Cluster.cpp:172:ProxySQL_Cluster_Monitor_thread(): [WARNING] Cluster: unable to connect to peer 172.16.3.132:6032 . Error: Can't connect to MySQL server on '172.16.3.132' (107) Main phase3 : Query Cache initialized in 0.000186 secs. 2017-09-06 00:22:51 ProxySQL_Cluster.cpp:325:set_checksums(): [INFO] Cluster: detected a new checksum for mysql_query_rules from peer 172.16.3.130:6032, version 3, epoch 1504653665, checksum 0x81A6E7C4159B081D . Not syncing yet ... 2017-09-06 00:22:51 ProxySQL_Cluster.cpp:345:set_checksums(): [INFO] Cluster: detected a new checksum for mysql_servers from peer 172.16.3.130:6032, version 1, epoch 1504652633, checksum 0x0000000000000000 . Not syncing yet ... 2017-09-06 00:22:51 ProxySQL_Cluster.cpp:347:set_checksums(): [INFO] Cluster: checksum for mysql_servers from peer 172.16.3.130:6032 matches with local checksum 0x0000000000000000 , we won't sync. 2017-09-06 00:22:51 ProxySQL_Cluster.cpp:365:set_checksums(): [INFO] Cluster: detected a new checksum for mysql_users from peer 172.16.3.130:6032, version 1, epoch 1504652633, checksum 0x7C60129E5360AC28 . Not syncing yet ... 2017-09-06 00:22:51 ProxySQL_Cluster.cpp:367:set_checksums(): [INFO] Cluster: checksum for mysql_users from peer 172.16.3.130:6032 matches with local checksum 0x7C60129E5360AC28 , we won't sync. 2017-09-06 00:22:51 ProxySQL_Cluster.cpp:401:set_checksums(): [INFO] Cluster: detected a new checksum for proxysql_servers from peer 172.16.3.130:6032, version 1, epoch 1504652633, checksum 0x474020F334F98128 . Not syncing yet ... 2017-09-06 00:22:51 ProxySQL_Cluster.cpp:403:set_checksums(): [INFO] Cluster: checksum for proxysql_servers from peer 172.16.3.130:6032 matches with local checksum 0x474020F334F98128 , we won't sync. Main phase3 : MySQL Threads Handler listeners started in 6.6e-05 secs. Standard MySQL Monitor (StdMyMon) rev. 1.2.0723_DEBUG -- MySQL_Monitor.cpp -- Tue Sep 5 12:20:48 2017 Main phase3 : MySQL Monitor initialized in 4.2e-05 secs. Main init phase3 completed in 0.00966 secs. 2017-09-06 00:22:51 ProxySQL_Cluster.cpp:325:set_checksums(): [INFO] Cluster: detected a new checksum for mysql_query_rules from peer 172.16.3.131:6032, version 3, epoch 1504653665, checksum 0x81A6E7C4159B081D . Not syncing yet ... 2017-09-06 00:22:51 ProxySQL_Cluster.cpp:345:set_checksums(): [INFO] Cluster: detected a new checksum for mysql_servers from peer 172.16.3.131:6032, version 1, epoch 1504652633, checksum 0x0000000000000000 . Not syncing yet ... 2017-09-06 00:22:51 ProxySQL_Cluster.cpp:347:set_checksums(): [INFO] Cluster: checksum for mysql_servers from peer 172.16.3.131:6032 matches with local checksum 0x0000000000000000 , we won't sync. 2017-09-06 00:22:51 ProxySQL_Cluster.cpp:365:set_checksums(): [INFO] Cluster: detected a new checksum for mysql_users from peer 172.16.3.131:6032, version 1, epoch 1504652633, checksum 0x7C60129E5360AC28 . Not syncing yet ... 2017-09-06 00:22:51 ProxySQL_Cluster.cpp:367:set_checksums(): [INFO] Cluster: checksum for mysql_users from peer 172.16.3.131:6032 matches with local checksum 0x7C60129E5360AC28 , we won't sync. 2017-09-06 00:22:51 ProxySQL_Cluster.cpp:401:set_checksums(): [INFO] Cluster: detected a new checksum for proxysql_servers from peer 172.16.3.131:6032, version 1, epoch 1504652633, checksum 0x474020F334F98128 . Not syncing yet ... 2017-09-06 00:22:51 ProxySQL_Cluster.cpp:403:set_checksums(): [INFO] Cluster: checksum for proxysql_servers from peer 172.16.3.131:6032 matches with local checksum 0x474020F334F98128 , we won't sync. 2017-09-06 00:22:51 ProxySQL_Cluster.cpp:172:ProxySQL_Cluster_Monitor_thread(): [WARNING] Cluster: unable to connect to peer 172.16.3.132:6032 . Error: Can't connect to MySQL server on '172.16.3.132' (107) 2017-09-06 00:22:51 ProxySQL_Cluster.cpp:172:ProxySQL_Cluster_Monitor_thread(): [WARNING] Cluster: unable to connect to peer 172.16.3.132:6032 . Error: Can't connect to MySQL server on '172.16.3.132' (107) 2017-09-06 00:22:51 ProxySQL_Cluster.cpp:473:set_checksums(): [INFO] Cluster: detected a peer 172.16.3.130:6032 with mysql_query_rules version 3, epoch 1504653665, diff_check 3. Own version: 1, epoch: 1504653771. Proceeding with remote sync 2017-09-06 00:22:51 ProxySQL_Cluster.cpp:473:set_checksums(): [INFO] Cluster: detected a peer 172.16.3.131:6032 with mysql_query_rules version 3, epoch 1504653665, diff_check 3. Own version: 1, epoch: 1504653771. Proceeding with remote sync 2017-09-06 00:22:52 ProxySQL_Cluster.cpp:473:set_checksums(): [INFO] Cluster: detected a peer 172.16.3.130:6032 with mysql_query_rules version 3, epoch 1504653665, diff_check 4. Own version: 1, epoch: 1504653771. Proceeding with remote sync 2017-09-06 00:22:52 ProxySQL_Cluster.cpp:1105:get_peer_to_sync_mysql_query_rules(): [INFO] Cluster: detected peer 172.16.3.130:6032 with mysql_query_rules version 3, epoch 1504653665 2017-09-06 00:22:52 ProxySQL_Cluster.cpp:572:pull_mysql_query_rules_from_peer(): [INFO] Cluster: Fetching MySQL Query Rules from peer 172.16.3.130:6032 started 2017-09-06 00:22:52 ProxySQL_Cluster.cpp:622:pull_mysql_query_rules_from_peer(): [INFO] Cluster: Fetching MySQL Query Rules from peer 172.16.3.130:6032 completed 2017-09-06 00:22:52 ProxySQL_Cluster.cpp:623:pull_mysql_query_rules_from_peer(): [INFO] Cluster: Loading to runtime MySQL Servers from peer 172.16.3.130:6032 2017-09-06 00:22:52 ProxySQL_Cluster.cpp:626:pull_mysql_query_rules_from_peer(): [INFO] Cluster: Saving to disk MySQL Query Rules from peer 172.16.3.130:6032 2017-09-06 00:22:52 ProxySQL_Cluster.cpp:325:set_checksums(): [INFO] Cluster: detected a new checksum for mysql_query_rules from peer 172.16.3.132:6032, version 2, epoch 1504653772, checksum 0x81A6E7C4159B081D . Not syncing yet ... 2017-09-06 00:22:52 ProxySQL_Cluster.cpp:327:set_checksums(): [INFO] Cluster: checksum for mysql_query_rules from peer 172.16.3.132:6032 matches with local checksum 0x81A6E7C4159B081D , we won't sync. 2017-09-06 00:22:52 ProxySQL_Cluster.cpp:345:set_checksums(): [INFO] Cluster: detected a new checksum for mysql_servers from peer 172.16.3.132:6032, version 1, epoch 1504653771, checksum 0x0000000000000000 . Not syncing yet ... 2017-09-06 00:22:52 ProxySQL_Cluster.cpp:347:set_checksums(): [INFO] Cluster: checksum for mysql_servers from peer 172.16.3.132:6032 matches with local checksum 0x0000000000000000 , we won't sync. 2017-09-06 00:22:52 ProxySQL_Cluster.cpp:365:set_checksums(): [INFO] Cluster: detected a new checksum for mysql_users from peer 172.16.3.132:6032, version 1, epoch 1504653771, checksum 0x7C60129E5360AC28 . Not syncing yet ... 2017-09-06 00:22:52 ProxySQL_Cluster.cpp:367:set_checksums(): [INFO] Cluster: checksum for mysql_users from peer 172.16.3.132:6032 matches with local checksum 0x7C60129E5360AC28 , we won't sync. 2017-09-06 00:22:52 ProxySQL_Cluster.cpp:401:set_checksums(): [INFO] Cluster: detected a new checksum for proxysql_servers from peer 172.16.3.132:6032, version 1, epoch 1504653771, checksum 0x474020F334F98128 . Not syncing yet ... 2017-09-06 00:22:52 ProxySQL_Cluster.cpp:403:set_checksums(): [INFO] Cluster: checksum for proxysql_servers from peer 172.16.3.132:6032 matches with local checksum 0x474020F334F98128 , we won't sync.

Without going into details (log is quite verbose), within 1 second proxysql132 has detected that its mysql_query_rules was out of sync, and it immediately syncs.

Going back in proxysql130's Admin, we can see that now proxysql132 is in sync with the rest of the cluster:

Admin130> SELECT hostname, port, name, version, FROM_UNIXTIME(epoch) epoch, checksum, FROM_UNIXTIME(changed_at) changed_at, FROM_UNIXTIME(updated_at) updated_at, diff_check, DATETIME('NOW') FROM stats_proxysql_servers_checksums WHERE name='mysql_query_rules' ORDER BY hostname; +--------------+------+-------------------+---------+---------------------+--------------------+---------------------+---------------------+------------+---------------------+ | hostname | port | name | version | epoch | checksum | changed_at | updated_at | diff_check | DATETIME('NOW') | +--------------+------+-------------------+---------+---------------------+--------------------+---------------------+---------------------+------------+---------------------+ | 172.16.3.130 | 6032 | mysql_query_rules | 3 | 2017-09-05 23:21:05 | 0x81A6E7C4159B081D | 2017-09-05 23:21:05 | 2017-09-05 23:30:49 | 0 | 2017-09-05 23:30:49 | | 172.16.3.131 | 6032 | mysql_query_rules | 3 | 2017-09-05 23:21:05 | 0x81A6E7C4159B081D | 2017-09-05 23:21:06 | 2017-09-05 23:30:49 | 0 | 2017-09-05 23:30:49 | | 172.16.3.132 | 6032 | mysql_query_rules | 2 | 2017-09-05 23:22:52 | 0x81A6E7C4159B081D | 2017-09-05 23:22:52 | 2017-09-05 23:30:49 | 0 | 2017-09-05 23:30:49 | +--------------+------+-------------------+---------+---------------------+--------------------+---------------------+---------------------+------------+---------------------+ 3 rows in set (0.00 sec) Bootstraping a new node

We now want to create a 4th proxysql instance, and make it join the cluster.
All we need in this 4th instance (proxysql133) are at least one peer node and credentials to connect to it. For this, we can use the same config file we used for the other nodes, that has no users, no query rules (and no mysql servers) , but only proxysql servers.
This is the log of proxysql133 upon start:

2017-09-06 00:36:34 ProxySQL_Admin.cpp:6207:flush_error_log(): [INFO] ProxySQL version 1.4.2-31-g924c1fa_DEBUG 2017-09-06 00:36:34 ProxySQL_Admin.cpp:6210:flush_error_log(): [INFO] Detected OS: Linux c-lon33 4.4.0-78-generic #99-Ubuntu SMP Thu Apr 27 15:29:09 UTC 2017 x86_64 2017-09-06 00:36:34 main.cpp:703:ProxySQL_daemonize_phase2(): [INFO] Starting ProxySQL 2017-09-06 00:36:34 main.cpp:704:ProxySQL_daemonize_phase2(): [INFO] Sucessfully started Main daemonize phase1 completed in 0.00089 secs. 2017-09-06 00:36:34 main.cpp:714:ProxySQL_daemonize_phase3(): [INFO] Angel process started ProxySQL process 41360 Standard ProxySQL Cluster rev. 0.1.0702_DEBUG -- ProxySQL_Cluster.cpp -- Wed Sep 6 00:10:30 2017 2017-09-06 00:36:34 ProxySQL_Admin.cpp:6164:Read_ProxySQL_Servers_from_configfile(): [INFO] Cluster: Adding ProxySQL Servers 172.16.3.130:6032 from config file 2017-09-06 00:36:34 ProxySQL_Admin.cpp:6164:Read_ProxySQL_Servers_from_configfile(): [INFO] Cluster: Adding ProxySQL Servers 172.16.3.131:6032 from config file 2017-09-06 00:36:34 ProxySQL_Admin.cpp:6164:Read_ProxySQL_Servers_from_configfile(): [INFO] Cluster: Adding ProxySQL Servers 172.16.3.132:6032 from config file Admin initialized in 0.039395 secs. Standard ProxySQL Admin rev. 0.2.0902_DEBUG -- ProxySQL_Admin.cpp -- Tue Sep 5 12:20:48 2017 Standard MySQL Threads Handler rev. 0.2.0902_DEBUG -- MySQL_Thread.cpp -- Tue Sep 5 12:20:48 2017 Main phase3 : GloMyLogger initialized in 4e-06 secs. Standard MySQL Authentication rev. 0.2.0902_DEBUG -- MySQL_Authentication.cpp -- Wed Sep 6 00:10:30 2017 Main init phase2 completed in 0.042762 secs. Main phase3 : GloMyLogger initialized in 3e-06 secs. 2017-09-06 00:36:34 MySQL_HostGroups_Manager.cpp:880:generate_mysql_servers_table(): [INFO] Dumping current MySQL Servers structures for hostgroup ALL 2017-09-06 00:36:34 MySQL_HostGroups_Manager.cpp:971:generate_mysql_replication_hostgroups_table(): [INFO] New mysql_replication_hostgroups table 2017-09-06 00:36:34 MySQL_HostGroups_Manager.cpp:1007:generate_mysql_group_replication_hostgroups_table(): [INFO] New mysql_group_replication_hostgroups table 2017-09-06 00:36:34 MySQL_HostGroups_Manager.cpp:880:generate_mysql_servers_table(): [INFO] Dumping current MySQL Servers structures for hostgroup ALL 2017-09-06 00:36:34 ProxySQL_Cluster.cpp:238:ProxySQL_Node_Entry(): [INFO] Created new Cluster Node Entry for host 172.16.3.130:6032 2017-09-06 00:36:34 ProxySQL_Cluster.cpp:238:ProxySQL_Node_Entry(): [INFO] Created new Cluster Node Entry for host 172.16.3.131:6032 2017-09-06 00:36:34 ProxySQL_Cluster.cpp:238:ProxySQL_Node_Entry(): [INFO] Created new Cluster Node Entry for host 172.16.3.132:6032 Main phase3 : GloAdmin initialized in 0.003828 secs. Standard Query Processor rev. 0.2.0902_DEBUG -- Query_Processor.cpp -- Tue Sep 5 12:20:48 2017 Main phase3 : Query Processor initialized in 0.000118 secs. 2017-09-06 00:36:34 ProxySQL_Cluster.cpp:53:ProxySQL_Cluster_Monitor_thread(): [INFO] Cluster: starting thread for peer 172.16.3.130:6032 2017-09-06 00:36:34 ProxySQL_Cluster.cpp:53:ProxySQL_Cluster_Monitor_thread(): [INFO] Cluster: starting thread for peer 172.16.3.131:6032 2017-09-06 00:36:34 ProxySQL_Cluster.cpp:53:ProxySQL_Cluster_Monitor_thread(): [INFO] Cluster: starting thread for peer 172.16.3.132:6032 Main phase3 : MySQL Threads Handler initialized in 0.0027 secs. In memory Standard Query Cache (SQC) rev. 1.2.0905_DEBUG -- Query_Cache.cpp -- Tue Sep 5 12:20:48 2017 Main phase3 : Query Cache initialized in 5.1e-05 secs. 2017-09-06 00:36:34 ProxySQL_Cluster.cpp:325:set_checksums(): [INFO] Cluster: detected a new checksum for mysql_query_rules from peer 172.16.3.131:6032, version 3, epoch 1504653665, checksum 0x81A6E7C4159B081D . Not syncing yet ... 2017-09-06 00:36:34 ProxySQL_Cluster.cpp:345:set_checksums(): [INFO] Cluster: detected a new checksum for mysql_servers from peer 172.16.3.131:6032, version 1, epoch 1504652633, checksum 0x0000000000000000 . Not syncing yet ... 2017-09-06 00:36:34 ProxySQL_Cluster.cpp:347:set_checksums(): [INFO] Cluster: checksum for mysql_servers from peer 172.16.3.131:6032 matches with local checksum 0x0000000000000000 , we won't sync. 2017-09-06 00:36:34 ProxySQL_Cluster.cpp:365:set_checksums(): [INFO] Cluster: detected a new checksum for mysql_users from peer 172.16.3.131:6032, version 1, epoch 1504652633, checksum 0x7C60129E5360AC28 . Not syncing yet ... 2017-09-06 00:36:34 ProxySQL_Cluster.cpp:401:set_checksums(): [INFO] Cluster: detected a new checksum for proxysql_servers from peer 172.16.3.131:6032, version 1, epoch 1504652633, checksum 0x474020F334F98128 . Not syncing yet ... 2017-09-06 00:36:34 ProxySQL_Cluster.cpp:403:set_checksums(): [INFO] Cluster: checksum for proxysql_servers from peer 172.16.3.131:6032 matches with local checksum 0x474020F334F98128 , we won't sync. 2017-09-06 00:36:34 ProxySQL_Cluster.cpp:325:set_checksums(): [INFO] Cluster: detected a new checksum for mysql_query_rules from peer 172.16.3.132:6032, version 2, epoch 1504653772, checksum 0x81A6E7C4159B081D . Not syncing yet ... 2017-09-06 00:36:34 ProxySQL_Cluster.cpp:345:set_checksums(): [INFO] Cluster: detected a new checksum for mysql_servers from peer 172.16.3.132:6032, version 1, epoch 1504653771, checksum 0x0000000000000000 . Not syncing yet ... 2017-09-06 00:36:34 ProxySQL_Cluster.cpp:347:set_checksums(): [INFO] Cluster: checksum for mysql_servers from peer 172.16.3.132:6032 matches with local checksum 0x0000000000000000 , we won't sync. 2017-09-06 00:36:34 ProxySQL_Cluster.cpp:365:set_checksums(): [INFO] Cluster: detected a new checksum for mysql_users from peer 172.16.3.132:6032, version 1, epoch 1504653771, checksum 0x7C60129E5360AC28 . Not syncing yet ... 2017-09-06 00:36:34 ProxySQL_Cluster.cpp:401:set_checksums(): [INFO] Cluster: detected a new checksum for proxysql_servers from peer 172.16.3.132:6032, version 1, epoch 1504653771, checksum 0x474020F334F98128 . Not syncing yet ... 2017-09-06 00:36:34 ProxySQL_Cluster.cpp:403:set_checksums(): [INFO] Cluster: checksum for proxysql_servers from peer 172.16.3.132:6032 matches with local checksum 0x474020F334F98128 , we won't sync. 2017-09-06 00:36:34 ProxySQL_Cluster.cpp:325:set_checksums(): [INFO] Cluster: detected a new checksum for mysql_query_rules from peer 172.16.3.130:6032, version 3, epoch 1504653665, checksum 0x81A6E7C4159B081D . Not syncing yet ... 2017-09-06 00:36:34 ProxySQL_Cluster.cpp:345:set_checksums(): [INFO] Cluster: detected a new checksum for mysql_servers from peer 172.16.3.130:6032, version 1, epoch 1504652633, checksum 0x0000000000000000 . Not syncing yet ... 2017-09-06 00:36:34 ProxySQL_Cluster.cpp:347:set_checksums(): [INFO] Cluster: checksum for mysql_servers from peer 172.16.3.130:6032 matches with local checksum 0x0000000000000000 , we won't sync. 2017-09-06 00:36:34 ProxySQL_Cluster.cpp:365:set_checksums(): [INFO] Cluster: detected a new checksum for mysql_users from peer 172.16.3.130:6032, version 1, epoch 1504652633, checksum 0x7C60129E5360AC28 . Not syncing yet ... 2017-09-06 00:36:34 ProxySQL_Cluster.cpp:401:set_checksums(): [INFO] Cluster: detected a new checksum for proxysql_servers from peer 172.16.3.130:6032, version 1, epoch 1504652633, checksum 0x474020F334F98128 . Not syncing yet ... 2017-09-06 00:36:34 ProxySQL_Cluster.cpp:403:set_checksums(): [INFO] Cluster: checksum for proxysql_servers from peer 172.16.3.130:6032 matches with local checksum 0x474020F334F98128 , we won't sync. Main phase3 : MySQL Threads Handler listeners started in 6.4e-05 secs. Standard MySQL Monitor (StdMyMon) rev. 1.2.0723_DEBUG -- MySQL_Monitor.cpp -- Tue Sep 5 12:20:48 2017 Main phase3 : MySQL Monitor initialized in 0.00103 secs. Main init phase3 completed in 0.011606 secs. 2017-09-06 00:36:34 ProxySQL_Cluster.cpp:473:set_checksums(): [INFO] Cluster: detected a peer 172.16.3.131:6032 with mysql_query_rules version 3, epoch 1504653665, diff_check 3. Own version: 1, epoch: 1504654594. Proceeding with remote sync 2017-09-06 00:36:34 ProxySQL_Cluster.cpp:473:set_checksums(): [INFO] Cluster: detected a peer 172.16.3.132:6032 with mysql_query_rules version 2, epoch 1504653772, diff_check 3. Own version: 1, epoch: 1504654594. Proceeding with remote sync 2017-09-06 00:36:34 ProxySQL_Cluster.cpp:473:set_checksums(): [INFO] Cluster: detected a peer 172.16.3.130:6032 with mysql_query_rules version 3, epoch 1504653665, diff_check 3. Own version: 1, epoch: 1504654594. Proceeding with remote sync 2017-09-06 00:36:35 ProxySQL_Cluster.cpp:473:set_checksums(): [INFO] Cluster: detected a peer 172.16.3.131:6032 with mysql_query_rules version 3, epoch 1504653665, diff_check 4. Own version: 1, epoch: 1504654594. Proceeding with remote sync 2017-09-06 00:36:35 ProxySQL_Cluster.cpp:1105:get_peer_to_sync_mysql_query_rules(): [INFO] Cluster: detected peer 172.16.3.131:6032 with mysql_query_rules version 3, epoch 1504653665 2017-09-06 00:36:35 ProxySQL_Cluster.cpp:572:pull_mysql_query_rules_from_peer(): [INFO] Cluster: Fetching MySQL Query Rules from peer 172.16.3.131:6032 started 2017-09-06 00:36:35 ProxySQL_Cluster.cpp:622:pull_mysql_query_rules_from_peer(): [INFO] Cluster: Fetching MySQL Query Rules from peer 172.16.3.131:6032 completed 2017-09-06 00:36:35 ProxySQL_Cluster.cpp:623:pull_mysql_query_rules_from_peer(): [INFO] Cluster: Loading to runtime MySQL Query Rules from peer 172.16.3.131:6032 2017-09-06 00:36:35 ProxySQL_Cluster.cpp:626:pull_mysql_query_rules_from_peer(): [INFO] Cluster: Saving to disk MySQL Query Rules from peer 172.16.3.131:6032 2017-09-06 00:36:40 ProxySQL_Cluster.cpp:521:set_checksums(): [WARNING] Cluster: detected a peer 172.16.3.130:6032 with mysql_users version 1, epoch 1504652633, diff_check 30. Own version: 1, epoch: 1504654594. diff_check is increasing, but version 1 doesn't allow sync. This message will be repeated every 30 checks until LOAD MYSQL USERS TO RUNTIME is executed on candidate master. 2017-09-06 00:36:40 ProxySQL_Cluster.cpp:521:set_checksums(): [WARNING] Cluster: detected a peer 172.16.3.132:6032 with mysql_users version 1, epoch 1504653771, diff_check 30. Own version: 1, epoch: 1504654594. diff_check is increasing, but version 1 doesn't allow sync. This message will be repeated every 30 checks until LOAD MYSQL USERS TO RUNTIME is executed on candidate master. 2017-09-06 00:36:40 ProxySQL_Cluster.cpp:521:set_checksums(): [WARNING] Cluster: detected a peer 172.16.3.131:6032 with mysql_users version 1, epoch 1504652633, diff_check 30. Own version: 1, epoch: 1504654594. diff_check is increasing, but version 1 doesn't allow sync. This message will be repeated every 30 checks until LOAD MYSQL USERS TO RUNTIME is executed on candidate master. 2017-09-06 00:36:46 ProxySQL_Cluster.cpp:521:set_checksums(): [WARNING] Cluster: detected a peer 172.16.3.130:6032 with mysql_users version 1, epoch 1504652633, diff_check 60. Own version: 1, epoch: 1504654594. diff_check is increasing, but version 1 doesn't allow sync. This message will be repeated every 30 checks until LOAD MYSQL USERS TO RUNTIME is executed on candidate master. 2017-09-06 00:36:46 ProxySQL_Cluster.cpp:521:set_checksums(): [WARNING] Cluster: detected a peer 172.16.3.132:6032 with mysql_users version 1, epoch 1504653771, diff_check 60. Own version: 1, epoch: 1504654594. diff_check is increasing, but version 1 doesn't allow sync. This message will be repeated every 30 checks until LOAD MYSQL USERS TO RUNTIME is executed on candidate master. 2017-09-06 00:36:46 ProxySQL_Cluster.cpp:521:set_checksums(): [WARNING] Cluster: detected a peer 172.16.3.131:6032 with mysql_users version 1, epoch 1504652633, diff_check 60. Own version: 1, epoch: 1504654594. diff_check is increasing, but version 1 doesn't allow sync. This message will be repeated every 30 checks until LOAD MYSQL USERS TO RUNTIME is executed on candidate master.

Once again, the error log is very verbose, but what is important to note is that within 1 second:

  • it detects that proxysql_servers table is identical with the rest of the cluster, no need to sync
  • it detects that mysql_servers table is identical with the rest of the cluster (well, empty), no need to sync
  • it detects that msyql_query_rules table is different, and immediately syncs
  • it detects that msyql_users table is different, but doesn't sync . This is intentional: if the remote node has version=1, this means that this is its own version and not a cluster version. Executing LOAD MYSQL USERS TO RUNTIME in one of the cluster node will validate which version is correct.
    Why this happened? Before modifying the query rules in the previous section of this blog post I intentionally shutdown the whole cluster and restarted it before adding proxysql132, in order to intentionally create a conflict in mysql_users table and show that this will prevent automatic sync.

Therefore it is important to note that nodes with version=1 cannot be trust as source of truth, and they won't sync from each other.

To solve this issue is easy, as the log suggest: let's run LOAD MYSQL USERS TO RUNTIME on proxysql130, so that version will increase and the new added node will consider proxysql130 a source of truth:

Admin130> SELECT hostname, port, name, version, FROM_UNIXTIME(epoch) epoch, checksum, FROM_UNIXTIME(changed_at) changed_at, FROM_UNIXTIME(updated_at) updated_at, diff_check, DATETIME('NOW') FROMxysql_servers_checksums WHERE name='mysql_users' ORDER BY hostname; +--------------+------+-------------+---------+---------------------+--------------------+---------------------+---------------------+------------+---------------------+ | hostname | port | name | version | epoch | checksum | changed_at | updated_at | diff_check | DATETIME('NOW') | +--------------+------+-------------+---------+---------------------+--------------------+---------------------+---------------------+------------+---------------------+ | 172.16.3.130 | 6032 | mysql_users | 1 | 2017-09-05 23:03:53 | 0x7C60129E5360AC28 | 2017-09-05 23:03:53 | 2017-09-05 23:46:34 | 0 | 2017-09-05 23:46:34 | | 172.16.3.131 | 6032 | mysql_users | 1 | 2017-09-05 23:03:53 | 0x7C60129E5360AC28 | 2017-09-05 23:03:54 | 2017-09-05 23:46:34 | 0 | 2017-09-05 23:46:34 | | 172.16.3.132 | 6032 | mysql_users | 1 | 2017-09-05 23:22:51 | 0x7C60129E5360AC28 | 2017-09-05 23:03:55 | 2017-09-05 23:46:34 | 0 | 2017-09-05 23:46:34 | +--------------+------+-------------+---------+---------------------+--------------------+---------------------+---------------------+------------+---------------------+ 3 rows in set (0.00 sec) Admin130> LOAD MYSQL USERS TO RUNTIME; Query OK, 0 rows affected (0.00 sec) Admin130> SELECT hostname, port, name, version, FROM_UNIXTIME(epoch) epoch, checksum, FROM_UNIXTIME(changed_at) changed_at, FROM_UNIXTIME(updated_at) updated_at, diff_check, DATETIME('NOW') FROM stats_proxysql_servers_checksums WHERE name='mysql_users' ORDER BY hostname; +--------------+------+-------------+---------+---------------------+--------------------+---------------------+---------------------+------------+---------------------+ | hostname | port | name | version | epoch | checksum | changed_at | updated_at | diff_check | DATETIME('NOW') | +--------------+------+-------------+---------+---------------------+--------------------+---------------------+---------------------+------------+---------------------+ | 172.16.3.130 | 6032 | mysql_users | 2 | 2017-09-05 23:46:56 | 0x7C60129E5360AC28 | 2017-09-05 23:03:53 | 2017-09-05 23:47:09 | 0 | 2017-09-05 23:47:09 | | 172.16.3.131 | 6032 | mysql_users | 1 | 2017-09-05 23:03:53 | 0x7C60129E5360AC28 | 2017-09-05 23:03:54 | 2017-09-05 23:47:09 | 0 | 2017-09-05 23:47:09 | | 172.16.3.132 | 6032 | mysql_users | 1 | 2017-09-05 23:22:51 | 0x7C60129E5360AC28 | 2017-09-05 23:03:55 | 2017-09-05 23:47:09 | 0 | 2017-09-05 23:47:09 | +--------------+------+-------------+---------+---------------------+--------------------+---------------------+---------------------+------------+---------------------+ 3 rows in set (0.00 sec)

There are few interesting points here:

  • the version changes only on local node, this because the other nodes have the same checksum, so they won't sync and load any configuration at runtime
  • proxysql133 is not in the list : this is intentional, and it is to prevent that a new node could corrupt the cluster

What happened on proxysql133? It finally synced mysql_users:

low sync. This message will be repeated every 30 checks until LOAD MYSQL SERVERS TO RUNTIME is executed on candidate master. 2017-09-06 00:46:47 ProxySQL_Cluster.cpp:521:set_checksums(): [WARNING] Cluster: detected a peer 172.16.3.130:6032 with mysql_users version 1, epoch 1504652633, diff_check 3060. Own version: 1, epoch: 1504654594. diff_check is increasing, but version 1 doesn't allow sync. This message will be repeated every 30 checks until LOAD MYSQL USERS TO RUNTIME is executed on candidate master. 2017-09-06 00:46:47 ProxySQL_Cluster.cpp:521:set_checksums(): [WARNING] Cluster: detected a peer 172.16.3.132:6032 with mysql_users version 1, epoch 1504653771, diff_check 3060. Own version: 1, epoch: 1504654594. diff_check is increasing, but version 1 doesn't allow sync. This message will be repeated every 30 checks until LOAD MYSQL USERS TO RUNTIME is executed on candidate master. 2017-09-06 00:46:47 ProxySQL_Cluster.cpp:521:set_checksums(): [WARNING] Cluster: detected a peer 172.16.3.131:6032 with mysql_users version 1, epoch 1504652633, diff_check 3060. Own version: 1, epoch: 1504654594. diff_check is increasing, but version 1 doesn't allow sync. This message will be repeated every 30 checks until LOAD MYSQL USERS TO RUNTIME is executed on candidate master. 2017-09-06 00:46:53 ProxySQL_Cluster.cpp:521:set_checksums(): [WARNING] Cluster: detected a peer 172.16.3.130:6032 with mysql_users version 1, epoch 1504652633, diff_check 3090. Own version: 1, epoch: 1504654594. diff_check is increasing, but version 1 doesn't allow sync. This message will be repeated every 30 checks until LOAD MYSQL USERS TO RUNTIME is executed on candidate master. 2017-09-06 00:46:53 ProxySQL_Cluster.cpp:521:set_checksums(): [WARNING] Cluster: detected a peer 172.16.3.132:6032 with mysql_users version 1, epoch 1504653771, diff_check 3090. Own version: 1, epoch: 1504654594. diff_check is increasing, but version 1 doesn't allow sync. This message will be repeated every 30 checks until LOAD MYSQL USERS TO RUNTIME is executed on candidate master. 2017-09-06 00:46:53 ProxySQL_Cluster.cpp:521:set_checksums(): [WARNING] Cluster: detected a peer 172.16.3.131:6032 with mysql_users version 1, epoch 1504652633, diff_check 3090. Own version: 1, epoch: 1504654594. diff_check is increasing, but version 1 doesn't allow sync. This message will be repeated every 30 checks until LOAD MYSQL USERS TO RUNTIME is executed on candidate master. 2017-09-06 00:46:56 ProxySQL_Cluster.cpp:515:set_checksums(): [INFO] Cluster: detected a peer 172.16.3.130:6032 with mysql_users version 2, epoch 1504655216, diff_check 3106. Own version: 1, epoch: 1504654594. Proceeding with remote sync 2017-09-06 00:46:56 ProxySQL_Cluster.cpp:1167:get_peer_to_sync_mysql_users(): [INFO] Cluster: detected peer 172.16.3.130:6032 with mysql_users version 2, epoch 1504655216 2017-09-06 00:46:56 ProxySQL_Cluster.cpp:671:pull_mysql_users_from_peer(): [INFO] Cluster: Fetching MySQL Users from peer 172.16.3.130:6032 started 2017-09-06 00:46:56 ProxySQL_Cluster.cpp:703:pull_mysql_users_from_peer(): [INFO] Cluster: Fetching MySQL Users from peer 172.16.3.130:6032 completed 2017-09-06 00:46:56 ProxySQL_Cluster.cpp:704:pull_mysql_users_from_peer(): [INFO] Cluster: Loading to runtime MySQL Users from peer 172.16.3.130:6032 2017-09-06 00:46:56 ProxySQL_Cluster.cpp:707:pull_mysql_users_from_peer(): [INFO] Cluster: Saving to disk MySQL Query Rules from peer 172.16.3.130:6032 Should the nodes in the original cluster knows about the new node?

Currently, the new node knows it is not part of the cluster:

Admin133> SELECT * FROM proxysql_servers; +--------------+------+--------+-------------+ | hostname | port | weight | comment | +--------------+------+--------+-------------+ | 172.16.3.130 | 6032 | 0 | proxysql130 | | 172.16.3.131 | 6032 | 0 | proxysql131 | | 172.16.3.132 | 6032 | 0 | proxysql132 | +--------------+------+--------+-------------+ 3 rows in set (0.00 sec) Admin133> SELECT hostname, port, name, version, FROM_UNIXTIME(epoch) epoch, checksum, FROM_UNIXTIME(changed_at) changed_at, FROM_UNIXTIME(updated_at) updated_at, diff_check, DATETIME('NOW') FROM stats_proxysql_servers_checksums WHERE version > 0 ORDER BY hostname, name; +--------------+------+-------------------+---------+---------------------+--------------------+---------------------+---------------------+------------+---------------------+ | hostname | port | name | version | epoch | checksum | changed_at | updated_at | diff_check | DATETIME('NOW') | +--------------+------+-------------------+---------+---------------------+--------------------+---------------------+---------------------+------------+---------------------+ | 172.16.3.130 | 6032 | mysql_query_rules | 3 | 2017-09-05 23:21:05 | 0x81A6E7C4159B081D | 2017-09-05 23:36:34 | 2017-09-06 00:08:25 | 0 | 2017-09-06 00:08:25 | | 172.16.3.130 | 6032 | mysql_servers | 1 | 2017-09-05 23:03:53 | 0x0000000000000000 | 2017-09-05 23:36:34 | 2017-09-06 00:08:25 | 0 | 2017-09-06 00:08:25 | | 172.16.3.130 | 6032 | mysql_users | 2 | 2017-09-05 23:46:56 | 0x7C60129E5360AC28 | 2017-09-05 23:36:34 | 2017-09-06 00:08:25 | 0 | 2017-09-06 00:08:25 | | 172.16.3.130 | 6032 | proxysql_servers | 1 | 2017-09-05 23:03:53 | 0x474020F334F98128 | 2017-09-05 23:36:34 | 2017-09-06 00:08:25 | 0 | 2017-09-06 00:08:25 | | 172.16.3.131 | 6032 | mysql_query_rules | 3 | 2017-09-05 23:21:05 | 0x81A6E7C4159B081D | 2017-09-05 23:36:34 | 2017-09-06 00:08:25 | 0 | 2017-09-06 00:08:25 | | 172.16.3.131 | 6032 | mysql_servers | 1 | 2017-09-05 23:03:53 | 0x0000000000000000 | 2017-09-05 23:36:34 | 2017-09-06 00:08:25 | 0 | 2017-09-06 00:08:25 | | 172.16.3.131 | 6032 | mysql_users | 1 | 2017-09-05 23:03:53 | 0x7C60129E5360AC28 | 2017-09-05 23:36:34 | 2017-09-06 00:08:25 | 0 | 2017-09-06 00:08:25 | | 172.16.3.131 | 6032 | proxysql_servers | 1 | 2017-09-05 23:03:53 | 0x474020F334F98128 | 2017-09-05 23:36:34 | 2017-09-06 00:08:25 | 0 | 2017-09-06 00:08:25 | | 172.16.3.132 | 6032 | mysql_query_rules | 2 | 2017-09-05 23:22:52 | 0x81A6E7C4159B081D | 2017-09-05 23:36:34 | 2017-09-06 00:08:25 | 0 | 2017-09-06 00:08:25 | | 172.16.3.132 | 6032 | mysql_servers | 1 | 2017-09-05 23:22:51 | 0x0000000000000000 | 2017-09-05 23:36:34 | 2017-09-06 00:08:25 | 0 | 2017-09-06 00:08:25 | | 172.16.3.132 | 6032 | mysql_users | 1 | 2017-09-05 23:22:51 | 0x7C60129E5360AC28 | 2017-09-05 23:36:34 | 2017-09-06 00:08:25 | 0 | 2017-09-06 00:08:25 | | 172.16.3.132 | 6032 | proxysql_servers | 1 | 2017-09-05 23:22:51 | 0x474020F334F98128 | 2017-09-05 23:36:34 | 2017-09-06 00:08:25 | 0 | 2017-09-06 00:08:25 | +--------------+------+-------------------+---------+---------------------+--------------------+---------------------+---------------------+------------+---------------------+ 12 rows in set (0.00 sec)

Similarly, the other nodes don't know this new node exists (well, they know "something" from 172.16.3.133 is pulling configuration, but that is all they know):

Admin130> SELECT * FROM proxysql_servers; +--------------+------+--------+-------------+ | hostname | port | weight | comment | +--------------+------+--------+-------------+ | 172.16.3.130 | 6032 | 0 | proxysql130 | | 172.16.3.131 | 6032 | 0 | proxysql131 | | 172.16.3.132 | 6032 | 0 | proxysql132 | +--------------+------+--------+-------------+ 3 rows in set (0.00 sec) Admin130> SELECT hostname, port, name, version, FROM_UNIXTIME(epoch) epoch, checksum, FROM_UNIXTIME(changed_at) changed_at, FROM_UNIXTIME(updated_at) updated_at, diff_check, DATETIME('NOW') FROM stats_proxysql_servers_checksums WHERE version > 0 ORDER BY hostname, name; +--------------+------+-------------------+---------+---------------------+--------------------+---------------------+---------------------+------------+---------------------+ | hostname | port | name | version | epoch | checksum | changed_at | updated_at | diff_check | DATETIME('NOW') | +--------------+------+-------------------+---------+---------------------+--------------------+---------------------+---------------------+------------+---------------------+ | 172.16.3.130 | 6032 | mysql_query_rules | 3 | 2017-09-05 23:21:05 | 0x81A6E7C4159B081D | 2017-09-05 23:21:05 | 2017-09-06 00:09:54 | 0 | 2017-09-06 00:09:54 | | 172.16.3.130 | 6032 | mysql_servers | 1 | 2017-09-05 23:03:53 | 0x0000000000000000 | 2017-09-05 23:03:53 | 2017-09-06 00:09:54 | 0 | 2017-09-06 00:09:54 | | 172.16.3.130 | 6032 | mysql_users | 2 | 2017-09-05 23:46:56 | 0x7C60129E5360AC28 | 2017-09-05 23:03:53 | 2017-09-06 00:09:54 | 0 | 2017-09-06 00:09:54 | | 172.16.3.130 | 6032 | proxysql_servers | 1 | 2017-09-05 23:03:53 | 0x474020F334F98128 | 2017-09-05 23:03:53 | 2017-09-06 00:09:54 | 0 | 2017-09-06 00:09:54 | | 172.16.3.131 | 6032 | mysql_query_rules | 3 | 2017-09-05 23:21:05 | 0x81A6E7C4159B081D | 2017-09-05 23:21:06 | 2017-09-06 00:09:54 | 0 | 2017-09-06 00:09:54 | | 172.16.3.131 | 6032 | mysql_servers | 1 | 2017-09-05 23:03:53 | 0x0000000000000000 | 2017-09-05 23:03:54 | 2017-09-06 00:09:54 | 0 | 2017-09-06 00:09:54 | | 172.16.3.131 | 6032 | mysql_users | 1 | 2017-09-05 23:03:53 | 0x7C60129E5360AC28 | 2017-09-05 23:03:54 | 2017-09-06 00:09:54 | 0 | 2017-09-06 00:09:54 | | 172.16.3.131 | 6032 | proxysql_servers | 1 | 2017-09-05 23:03:53 | 0x474020F334F98128 | 2017-09-05 23:03:54 | 2017-09-06 00:09:54 | 0 | 2017-09-06 00:09:54 | | 172.16.3.132 | 6032 | mysql_query_rules | 2 | 2017-09-05 23:22:52 | 0x81A6E7C4159B081D | 2017-09-05 23:22:52 | 2017-09-06 00:09:54 | 0 | 2017-09-06 00:09:54 | | 172.16.3.132 | 6032 | mysql_servers | 1 | 2017-09-05 23:22:51 | 0x0000000000000000 | 2017-09-05 23:03:55 | 2017-09-06 00:09:54 | 0 | 2017-09-06 00:09:54 | | 172.16.3.132 | 6032 | mysql_users | 1 | 2017-09-05 23:22:51 | 0x7C60129E5360AC28 | 2017-09-05 23:03:55 | 2017-09-06 00:09:54 | 0 | 2017-09-06 00:09:54 | | 172.16.3.132 | 6032 | proxysql_servers | 1 | 2017-09-05 23:22:51 | 0x474020F334F98128 | 2017-09-05 23:03:55 | 2017-09-06 00:09:54 | 0 | 2017-09-06 00:09:54 | +--------------+------+-------------------+---------+---------------------+--------------------+---------------------+---------------------+------------+---------------------+ 12 rows in set (0.00 sec)

Should the nodes in the original cluster knows about the new node? It depends!
The fact that the rest of the cluster doesn't know anything about the new node means that changes applied directly to the new node won't be replicated to the rest of the cluster.
This creates a core cluster where nodes know about each other and synchronize from each other, and nodes that can only sync from the core cluster without affecting it. This is a big plus: only the core cluster can be the source of truth.
If we want to expand the core cluster, it is enough to insert the new node in proxysql_servers table on any of the core cluster nodes.

Admin130> SELECT * FROM proxysql_servers; +--------------+------+--------+-------------+ | hostname | port | weight | comment | +--------------+------+--------+-------------+ | 172.16.3.130 | 6032 | 0 | proxysql130 | | 172.16.3.131 | 6032 | 0 | proxysql131 | | 172.16.3.132 | 6032 | 0 | proxysql132 | +--------------+------+--------+-------------+ 3 rows in set (0.00 sec) Admin130> SELECT hostname, port, name, version, FROM_UNIXTIME(epoch) epoch, checksum, FROM_UNIXTIME(changed_at) changed_at, FROM_UNIXTIME(updated_at) updated_at, diff_check, DATETIME('NOW') FROM stats_proxysql_servers_checksums WHERE version > 0 ORDER BY hostname, name; +--------------+------+-------------------+---------+---------------------+--------------------+---------------------+---------------------+------------+---------------------+ | hostname | port | name | version | epoch | checksum | changed_at | updated_at | diff_check | DATETIME('NOW') | +--------------+------+-------------------+---------+---------------------+--------------------+---------------------+---------------------+------------+---------------------+ | 172.16.3.130 | 6032 | mysql_query_rules | 3 | 2017-09-05 23:21:05 | 0x81A6E7C4159B081D | 2017-09-05 23:21:05 | 2017-09-06 00:09:54 | 0 | 2017-09-06 00:09:54 | | 172.16.3.130 | 6032 | mysql_servers | 1 | 2017-09-05 23:03:53 | 0x0000000000000000 | 2017-09-05 23:03:53 | 2017-09-06 00:09:54 | 0 | 2017-09-06 00:09:54 | | 172.16.3.130 | 6032 | mysql_users | 2 | 2017-09-05 23:46:56 | 0x7C60129E5360AC28 | 2017-09-05 23:03:53 | 2017-09-06 00:09:54 | 0 | 2017-09-06 00:09:54 | | 172.16.3.130 | 6032 | proxysql_servers | 1 | 2017-09-05 23:03:53 | 0x474020F334F98128 | 2017-09-05 23:03:53 | 2017-09-06 00:09:54 | 0 | 2017-09-06 00:09:54 | | 172.16.3.131 | 6032 | mysql_query_rules | 3 | 2017-09-05 23:21:05 | 0x81A6E7C4159B081D | 2017-09-05 23:21:06 | 2017-09-06 00:09:54 | 0 | 2017-09-06 00:09:54 | | 172.16.3.131 | 6032 | mysql_servers | 1 | 2017-09-05 23:03:53 | 0x0000000000000000 | 2017-09-05 23:03:54 | 2017-09-06 00:09:54 | 0 | 2017-09-06 00:09:54 | | 172.16.3.131 | 6032 | mysql_users | 1 | 2017-09-05 23:03:53 | 0x7C60129E5360AC28 | 2017-09-05 23:03:54 | 2017-09-06 00:09:54 | 0 | 2017-09-06 00:09:54 | | 172.16.3.131 | 6032 | proxysql_servers | 1 | 2017-09-05 23:03:53 | 0x474020F334F98128 | 2017-09-05 23:03:54 | 2017-09-06 00:09:54 | 0 | 2017-09-06 00:09:54 | | 172.16.3.132 | 6032 | mysql_query_rules | 2 | 2017-09-05 23:22:52 | 0x81A6E7C4159B081D | 2017-09-05 23:22:52 | 2017-09-06 00:09:54 | 0 | 2017-09-06 00:09:54 | | 172.16.3.132 | 6032 | mysql_servers | 1 | 2017-09-05 23:22:51 | 0x0000000000000000 | 2017-09-05 23:03:55 | 2017-09-06 00:09:54 | 0 | 2017-09-06 00:09:54 | | 172.16.3.132 | 6032 | mysql_users | 1 | 2017-09-05 23:22:51 | 0x7C60129E5360AC28 | 2017-09-05 23:03:55 | 2017-09-06 00:09:54 | 0 | 2017-09-06 00:09:54 | | 172.16.3.132 | 6032 | proxysql_servers | 1 | 2017-09-05 23:22:51 | 0x474020F334F98128 | 2017-09-05 23:03:55 | 2017-09-06 00:09:54 | 0 | 2017-09-06 00:09:54 | +--------------+------+-------------------+---------+---------------------+--------------------+---------------------+---------------------+------------+---------------------+ 12 rows in set (0.00 sec) Admin130> INSERT INTO proxysql_servers VALUES ('172.16.3.133',6032,0,'proxysql133'); Query OK, 1 row affected (0.00 sec) Admin130> LOAD PROXYSQL SERVERS TO RUNTIME; Query OK, 0 rows affected (0.00 sec) Admin130> SELECT hostname, port, name, version, FROM_UNIXTIME(epoch) epoch, checksum, FROM_UNIXTIME(changed_at) changed_at, FROM_UNIXTIME(updated_at) updated_at, diff_check, DATETIME('NOW') FROM stats_proxysql_servers_checksums WHERE version > 0 ORDER BY hostname, name; +--------------+------+-------------------+---------+---------------------+--------------------+---------------------+---------------------+------------+---------------------+ | hostname | port | name | version | epoch | checksum | changed_at | updated_at | diff_check | DATETIME('NOW') | +--------------+------+-------------------+---------+---------------------+--------------------+---------------------+---------------------+------------+---------------------+ | 172.16.3.130 | 6032 | mysql_query_rules | 3 | 2017-09-05 23:21:05 | 0x81A6E7C4159B081D | 2017-09-05 23:21:05 | 2017-09-06 00:11:53 | 0 | 2017-09-06 00:11:53 | | 172.16.3.130 | 6032 | mysql_servers | 1 | 2017-09-05 23:03:53 | 0x0000000000000000 | 2017-09-05 23:03:53 | 2017-09-06 00:11:53 | 0 | 2017-09-06 00:11:53 | | 172.16.3.130 | 6032 | mysql_users | 2 | 2017-09-05 23:46:56 | 0x7C60129E5360AC28 | 2017-09-05 23:03:53 | 2017-09-06 00:11:53 | 0 | 2017-09-06 00:11:53 | | 172.16.3.130 | 6032 | proxysql_servers | 2 | 2017-09-06 00:11:47 | 0x5E1BA8E79166D590 | 2017-09-06 00:11:48 | 2017-09-06 00:11:53 | 0 | 2017-09-06 00:11:53 | | 172.16.3.131 | 6032 | mysql_query_rules | 3 | 2017-09-05 23:21:05 | 0x81A6E7C4159B081D | 2017-09-05 23:21:06 | 2017-09-06 00:11:53 | 0 | 2017-09-06 00:11:53 | | 172.16.3.131 | 6032 | mysql_servers | 1 | 2017-09-05 23:03:53 | 0x0000000000000000 | 2017-09-05 23:03:54 | 2017-09-06 00:11:53 | 0 | 2017-09-06 00:11:53 | | 172.16.3.131 | 6032 | mysql_users | 1 | 2017-09-05 23:03:53 | 0x7C60129E5360AC28 | 2017-09-05 23:03:54 | 2017-09-06 00:11:53 | 0 | 2017-09-06 00:11:53 | | 172.16.3.131 | 6032 | proxysql_servers | 2 | 2017-09-06 00:11:48 | 0x5E1BA8E79166D590 | 2017-09-06 00:11:48 | 2017-09-06 00:11:53 | 0 | 2017-09-06 00:11:53 | | 172.16.3.132 | 6032 | mysql_query_rules | 2 | 2017-09-05 23:22:52 | 0x81A6E7C4159B081D | 2017-09-05 23:22:52 | 2017-09-06 00:11:53 | 0 | 2017-09-06 00:11:53 | | 172.16.3.132 | 6032 | mysql_servers | 1 | 2017-09-05 23:22:51 | 0x0000000000000000 | 2017-09-05 23:03:55 | 2017-09-06 00:11:53 | 0 | 2017-09-06 00:11:53 | | 172.16.3.132 | 6032 | mysql_users | 1 | 2017-09-05 23:22:51 | 0x7C60129E5360AC28 | 2017-09-05 23:03:55 | 2017-09-06 00:11:53 | 0 | 2017-09-06 00:11:53 | | 172.16.3.132 | 6032 | proxysql_servers | 2 | 2017-09-06 00:11:48 | 0x5E1BA8E79166D590 | 2017-09-06 00:11:48 | 2017-09-06 00:11:53 | 0 | 2017-09-06 00:11:53 | | 172.16.3.133 | 6032 | mysql_query_rules | 2 | 2017-09-05 23:36:35 | 0x81A6E7C4159B081D | 2017-09-06 00:11:47 | 2017-09-06 00:11:53 | 0 | 2017-09-06 00:11:53 | | 172.16.3.133 | 6032 | mysql_servers | 1 | 2017-09-05 23:36:34 | 0x0000000000000000 | 2017-09-06 00:11:47 | 2017-09-06 00:11:53 | 0 | 2017-09-06 00:11:53 | | 172.16.3.133 | 6032 | mysql_users | 2 | 2017-09-05 23:46:56 | 0x7C60129E5360AC28 | 2017-09-06 00:11:47 | 2017-09-06 00:11:53 | 0 | 2017-09-06 00:11:53 | | 172.16.3.133 | 6032 | proxysql_servers | 2 | 2017-09-06 00:11:48 | 0x5E1BA8E79166D590 | 2017-09-06 00:11:48 | 2017-09-06 00:11:53 | 0 | 2017-09-06 00:11:53 | +--------------+------+-------------------+---------+---------------------+--------------------+---------------------+---------------------+------------+---------------------+ 16 rows in set (0.00 sec)

Now all the nodes know that proxysql133 is part of the cluster.
Note that the creation of a Cluster with core nodes and satellite nodes makes this solution highly scalable, because it is possible to setup a relarively small core cluster (dozen of nodes), and a very large number of satellite nodes (thousands).
This also allows interesting future enhancements, for example:

  • nodes outside the core cluster won't allow any reconfiguration other than reconfiguration originated in the core (Admin in read only mode)
  • nodes in the core cluster won't allow any reconfiguration if they are not part of the quorum (switch to read only)
  • stop handling traffic is the quorum in the core cluster is lost
Conclusion

In this second blog post about ProxySQL Cluster we showed how this solution allows to add new nodes, either as a core node or as a satellite node. We also showed how nodes re-synchronize if they have been outside the cluster for some time, and also how to handle conflicting configurations.
Finally, we described future roadmap on how to scale this solution and create a quorum.

ClusterControl in the Cloud - All Our Resources

While many of our customers utilize ClusterControl on-premise to automate and manage their open source databases, several are deploying ClusterControl alongside their applications in the cloud. Utilizing the cloud allows your business and applications to benefit from the cost-savings and flexibility that come with cloud computing. In addition you don’t have to worry about purchasing, maintaining and upgrading equipment.

Along the same lines, ClusterControl offers a suite of database automation and management functions to give you full control of your database infrastructure. With it you can deploy, manage, monitor and scale your databases, securely and with ease through our point-and-click interface.

As the load on your application increases… Your cloud environment can be expanded to provide more computing power to handle that load. In much the same way ClusterControl utilizes state-of-the-art database, caching, and load balancing technologies that enable you to scale-out the load on your databases and spread that load evenly across nodes.

These performance benefits are just some of the many reasons to leverage ClusterControl to manage your open source database instances in the cloud. From advanced monitoring to backups and automatic failover, ClusterControl is your true end-to-end database management solution.

Below you will find some of our top resources to help you get your databases up-and-running in the cloud…

ClusterControl Single Console for Your Entire Database Infrastructure Find out what else is new in ClusterControl Install ClusterControl for FREE AWS Marketplace ClusterControl on the AWS Marketplace

Want to install ClusterControl directly onto your AWS EC2 instance? Check us out on the Amazon Marketplace.
(New version coming soon!)

Install Today

Top Blogs Migrating MySQL database from Amazon RDS to DigitalOcean

This blog post describes the migration process from EC2 instance to a DigitalOcean droplet

Read More

MySQL in the Cloud - Online Migration from Amazon RDS to EC2 Instance (PART ONE)

RDS for MySQL is easy to get started. It's a convenient way to deploy and use MySQL, without having to worry about any operational overhead. The tradeoff though is reduced control.

Read More

MySQL in the Cloud - Online Migration from Amazon RDS to Your Own Server (PART TWO)

It's challenging to move data out of RDS for MySQL. We will show you how to do the actual migration of data to your own server, and redirect your applications to the new database without downtime.

Read More

MySQL in the Cloud - Pros and Cons of Amazon RDS

Moving your data into a public cloud service is a big decision. All the major cloud vendors offer cloud database services, with Amazon RDS for MySQL being probably the most popular. In this blog, we’ll have a close look at what it is, how it works, and compare its pros and cons.

Read More

About Cloud Lock-in and Open Source Databases

Severalnines CEO Vinay Joosery discusses key considerations to take when choosing cloud providers to host and manage mission critical data; and thus avoid cloud lock-in.

Read More

Infrastructure Automation - Deploying ClusterControl and MySQL-based systems on AWS using Ansible

This blog post has the latest updates to our ClusterControl Ansible Role. It now supports automatic deployment of MySQL-based systems (MySQL Replication, Galera Cluster, NDB Cluster).

Read More

Leveraging AWS tools to speed up management of Galera Cluster on Amazon Cloud

We previously covered basic tuning and configuration best practices for MyQL Galera Cluster on AWS. In this blog post, we’ll go over some AWS features/tools that you may find useful when managing Galera on Amazon Cloud. This won’t be a detailed how-to guide as each tool described below would warrant its own blog post. But this should be a good overview of how you can use the AWS tools at your disposal.

Read More

5 Performance tips for running Galera Cluster for MySQL or MariaDB on AWS Cloud

Amazon Web Services is one of the most popular cloud environments. Galera Cluster is one of the most popular MySQL clustering solutions. This is exactly why you’ll see many Galera clusters running on EC2 instances. In this blog post, we’ll go over five performance tips that you need to take under consideration while deploying and running Galera Cluster on EC2.

Read More

How to change AWS instance sizes for your Galera Cluster and optimize performance

Running your database cluster on AWS is a great way to adapt to changing workloads by adding/removing instances, or by scaling up/down each instance. At Severalnines, we talk much more about scale-out than scale up, but there are cases where you might want to scale up an instance instead of scaling out.

Read More

Tags:  clustercontrol MySQL AWS rds ec2 Google Cloud azure galera

orchestrator 3.0.2 GA released: raft consensus, SQLite

orchestrator 3.0.2 GA is released and available for download (see also packagecloud repository).

3.0.2 is the first stable release in the 3.0* series, introducing (recap from 3.0 pre-release announcement):

orchestrator/raft

Raft is a consensus protocol, supporting leader election and consensus across a distributed system.  In an orchestrator/raft setup orchestrator nodes talk to each other via raft protocol, form consensus and elect a leader. Each orchestrator node has its own dedicated backend database. The backend databases do not speak to each other; only the orchestrator nodes speak to each other.

No MySQL replication setup needed; the backend DBs act as standalone servers. In fact, the backend server doesn't have to be MySQL, and SQLiteis supported. orchestrator now ships with SQLite embedded, no external dependency needed.

For details, please refer to the documentation:

SQLite

Suggested and requested by many, is to remove orchestrator's own dependency on a MySQL backend. orchestrator now supports a SQLite backend.

SQLite is a transactional, relational, embedded database, and as of 3.0 it is embedded within orchestrator, no external dependency required.

orchestrator-client

orchestrator-client is a client shell script which mimics the command line interface, while running curl | jq requests against the HTTP API. It stands to simplify your deployments: interacting with the orchestrator service via orchestrator-client is easier and only requires you to place a shell script (this is as opposed to installing the orchestrator binary + configuration file).

orchestrator-client is the way to interact with your orchestrator/raft cluster. orchestrator-client now has its own RPM/deb release package.

You may still use the web interface, web API ; and a special --ignore-raft-setup keeps power at your hand (use at your own risk).

State of orchestrator/raft

orchestrator/raft is a big change:

  • In the way it is deployed
  • In the way it is operated
  • In the high availability it provides
  • and more

This is why it has been tested in production for a few months.

orchestrator/raft now runs in production at GitHub ; we've decommissioned the "old" orchestrator setup having run both in parallel for a while. It drives our failovers and deploys over three data centers.

We are using MySQL as backend to our orchestrator cluster. We will introduce more staging tests for SQLite-based setups.

Roadmap

There's much to do, and we chose to release a version that has a way to go. We expect:

  • Dynamic raft cluster join/leave operations (right now the cluster is static and configuration based)
  • New nodes joining the cluster to auto-populate data from the cluster. This is actually a built-in feature to the hashicorp/raft library that we use, however we intentionally did not use this functionality, and expect to re-introduce it. At this time, adding a newly provisioned node to the cluster requires a backup/restore or dump/load of DB data from an existing node.
  • Partitioning of probe tasks across nodes
  • Various thoughts on proxy integrations
  • More...

We will focus on making operations simpler, and of course keep stability and reliability at highest priority.

orchestrator tutorial

In two weeks time I will be presenting the practical orchestrator tutorial, a 3 hour practical walkthrough on deployment, configuration, reasoning and more.

Updating InnoDB Table Statistics Manually

In this post, we will discuss how to fix cardinality for InnoDB tables manually.

As a support engineer, I often see situations when the cardinality of a table is not correct. When InnoDB calculates the cardinality of an index, it does not scan the full table by default. Instead it looks at random pages, as determined by options innodb_stats_sample_pages, innodb_stats_transient_sample_pages and innodb_stats_persistent_sample_pages, or by the 

CREATE TABLE option STATS_SAMPLE_PAGES. The default value for persistent statistics is 20. This approach works fine when the number of unique values in your secondary key grows in step with the size of the table. But what if you have a column that has a comparatively small number of unique values? This could be a common service, many-to-many relationship table, for example, or just a table containing a list of sell orders that belong to one of a dozen shops owned by the company. Such tables could grow up to billions of rows with a small (less than 100) number of unique shop IDs.

At some point, InnoDB will report the wrong values for such indexes. Really! If 20 pages have 100 unique shop IDs, how many unique shop IDs would 20000 pages have? 100 times 1000? This seems logical, and after a certain number of rows such indexes will have extraordinarily large cardinality values.

ANALYZE TABLE will not help, because it uses the same algorithm. Increasing the number of “stats” sample pages would help, but it has its own downside: the more pages you have to examine, the slower ANALYZE TABLE runs. While this command is not blocking, it still creates side effects as described in this blog post. And the longer it runs, the less control you have.

Another issue with InnoDB statistics: even if it is persistent and

STATS_AUTO_RECALC is set to 0, it still adds values for secondary indexes as shown in lp:1538765. Eventually, after you insert million of rows, your statistics get corrupted. ANALYZE TABLE  can fix it only if you specify a very large number of “stats” sample pages. Can we do anything about it?

InnoDB stores statistics in the “mysql” database, in the tables

innodb_table_stats and innodb_index_stats. Since they are regular MySQL tables, privileged users can access them. We can update them and modify statistics as we like. And these statistics are used by the Optimizer!

I created a small example showing how to do this trick. I used Percona Server for MySQL version 5.7.19, but the trick will work on any supported MySQL and Percona Server for MySQL version.

First, let’s create test tables. The first table has shops, with a few shop profiles with the shop ID and name:

create table shops( shop_id int not null auto_increment primary key, name varchar(32) ) engine=innodb;

The second table refers to the “shops” table:

create table goods( id int not null auto_increment primary key, shop_id int not null, name varchar(32), create_date datetime DEFAULT NULL, key (shop_id, create_date) ) engine=innodb;

Let’s check how many unique shops we have:

mysql> select count(distinct shop_id) from shops; +-------------------------+ | count(distinct shop_id) | +-------------------------+ | 100 | +-------------------------+ 1 row in set (0.02 sec)

With 100 distinct shops, and a key on

(shop_id, create_date), we expect cardinality in table goods to be not much different than this query result:mysql> select count(distinct id) as `Cardinality for PRIMARY`, -> count(distinct shop_id) as `Cardinality for shop_id column in index shop_id`, -> count(distinct shop_id, create_date) as `Cardinality for create_date column in index shop_id` -> from goods *************************** 1. row *************************** Cardinality for PRIMARY: 8000000 Cardinality for shop_id column in index shop_id: 100 Cardinality for create_date column in index shop_id: 169861 1 row in set (2 min 8.74 sec)

However, 

SHOW INDEX returns dramatically different values for the column shop_id:mysql> show index from goods; +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | goods | 0 | PRIMARY | 1 | id | A | 7289724 | NULL | NULL | | BTREE | | | | goods | 1 | shop_id | 1 | shop_id | A | 13587 | NULL | NULL | | BTREE | | | | goods | 1 | shop_id | 2 | create_date | A | 178787 | NULL | NULL | YES | BTREE | | | +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ 3 rows in set (0.09 sec)

ANALYZE TABLE does not help:mysql> analyze table goods; +------------+---------+----------+----------+ | Table | Op | Msg_type | Msg_text | +------------+---------+----------+----------+ | test.goods | analyze | status | OK | +------------+---------+----------+----------+ 1 row in set (0.88 sec) mysql> show index from goods; +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | goods | 0 | PRIMARY | 1 | id | A | 7765796 | NULL | NULL | | BTREE | | | | goods | 1 | shop_id | 1 | shop_id | A | 14523 | NULL | NULL | | BTREE | | | | goods | 1 | shop_id | 2 | create_date | A | 168168 | NULL | NULL | YES | BTREE | | | +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ 3 rows in set (0.00 sec)

As a result, if we join the two tables, Optimizer chooses the wrong

JOIN order and query execution plan:mysql> explain select goods.* from goods join shops using(shop_id) where create_date BETWEEN CONVERT_TZ('2015-11-01 00:00:00', 'MET','GMT') AND CONVERT_TZ('2015-11-07 23:59:59', 'MET','GMT') and goods.shop_id in(4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60,61,62,63,64,65,66,67,68,69,70,71,72,73,74,75,76,77,78,79,80,81,82,83,84,85,86,87,88,89,90,91,92,93,94,95,96,97,98,99,100,101,102,103,104,105,106,107,108,109,110,111,112,113,114,115,116,117,401,402,403,404,405,406,407,408,409,410,411,412,413,414,415,417,418,419,420,421,422,423,424,425,426,427,428,429,430,431,432,433,434,435,436,437,438,439,440,441,442,443,444,445,446,447,448,449,450,451,452,453,454,455,456,457,458,459,460,461,462,463,464,465,466,467,468,470,471,472,473,474,475,476,477,478,479,480,481,482,483,484,485,486); +----+-------------+-------+------------+-------+---------------+---------+---------+--------------------+------+----------+--------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+---------+---------+--------------------+------+----------+--------------------------+ | 1 | SIMPLE | shops | NULL | index | PRIMARY | PRIMARY | 4 | NULL | 100 | 100.00 | Using where; Using index | | 1 | SIMPLE | goods | NULL | ref | shop_id | shop_id | 4 | test.shops.shop_id | 534 | 11.11 | Using index condition | +----+-------------+-------+------------+-------+---------------+---------+---------+--------------------+------+----------+--------------------------+ 2 rows in set, 1 warning (0.13 sec) mysql> P md5sum PAGER set to 'md5sum' mysql> select goods.* from goods join shops using(shop_id) where create_date BETWEEN CONVERT_TZ('2015-11-01 00:00:00', 'MET','GMT') AND CONVERT_TZ('2015-11-07 23:59:59', 'MET','GMT') and goods.shop_id in(4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60,61,62,63,64,65,66,67,68,69,70,71,72,73,74,75,76,77,78,79,80,81,82,83,84,85,86,87,88,89,90,91,92,93,94,95,96,97,98,99,100,101,102,103,104,105,106,107,108,109,110,111,112,113,114,115,116,117,401,402,403,404,405,406,407,408,409,410,411,412,413,414,415,417,418,419,420,421,422,423,424,425,426,427,428,429,430,431,432,433,434,435,436,437,438,439,440,441,442,443,444,445,446,447,448,449,450,451,452,453,454,455,456,457,458,459,460,461,462,463,464,465,466,467,468,470,471,472,473,474,475,476,477,478,479,480,481,482,483,484,485,486); 4a94dabc4bfbfb7dd225bcb50278055b - 31896 rows in set (43.32 sec)

If compared to 

STRAIGHT_JOIN order:mysql> explain select goods.* from goods straight_join shops on(goods.shop_id = shops.shop_id) where create_date BETWEEN CONVERT_TZ('2015-11-01 00:00:00', 'MET','GMT') AND CONVERT_TZ('2015-11-07 23:59:59', 'MET','GMT') and goods.shop_id in(4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60,61,62,63,64,65,66,67,68,69,70,71,72,73,74,75,76,77,78,79,80,81,82,83,84,85,86,87,88,89,90,91,92,93,94,95,96,97,98,99,100,101,102,103,104,105,106,107,108,109,110,111,112,113,114,115,116,117,401,402,403,404,405,406,407,408,409,410,411,412,413,414,415,417,418,419,420,421,422,423,424,425,426,427,428,429,430,431,432,433,434,435,436,437,438,439,440,441,442,443,444,445,446,447,448,449,450,451,452,453,454,455,456,457,458,459,460,461,462,463,464,465,466,467,468,470,471,472,473,474,475,476,477,478,479,480,481,482,483,484,485,486); +----+-------------+-------+------------+--------+---------------+---------+---------+--------------------+-------+----------+-----------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+--------+---------------+---------+---------+--------------------+-------+----------+-----------------------+ | 1 | SIMPLE | goods | NULL | range | shop_id | shop_id | 10 | NULL | 31997 | 100.00 | Using index condition | | 1 | SIMPLE | shops | NULL | eq_ref | PRIMARY | PRIMARY | 4 | test.goods.shop_id | 1 | 100.00 | Using index | +----+-------------+-------+------------+--------+---------------+---------+---------+--------------------+-------+----------+-----------------------+ 2 rows in set, 1 warning (0.14 sec) mysql> P md5sum PAGER set to 'md5sum' mysql> select goods.* from goods straight_join shops on(goods.shop_id = shops.shop_id) where create_date BETWEEN CONVERT_TZ('2015-11-01 00:00:00', 'MET','GMT') AND CONVERT_TZ('2015-11-07 23:59:59', 'MET','GMT') and goods.shop_id in(4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60,61,62,63,64,65,66,67,68,69,70,71,72,73,74,75,76,77,78,79,80,81,82,83,84,85,86,87,88,89,90,91,92,93,94,95,96,97,98,99,100,101,102,103,104,105,106,107,108,109,110,111,112,113,114,115,116,117,401,402,403,404,405,406,407,408,409,410,411,412,413,414,415,417,418,419,420,421,422,423,424,425,426,427,428,429,430,431,432,433,434,435,436,437,438,439,440,441,442,443,444,445,446,447,448,449,450,451,452,453,454,455,456,457,458,459,460,461,462,463,464,465,466,467,468,470,471,472,473,474,475,476,477,478,479,480,481,482,483,484,485,486); 4a94dabc4bfbfb7dd225bcb50278055b - 31896 rows in set (7.94 sec)

The time difference for a small 8M row table is around six times! For a big table with many columns, it would be even larger.

Is

STRAIGHT_JOIN the only solution for this case?

No! It’s also not a great solution because if the query is complicated and involves more than two tables, it may be affected by bug fixes and improvements in the Optimizer code. Then the query order might not be optimal for new versions and updates. Therefore, you’ll need to test such queries at each upgrade, including minor ones.

So why does

ANALYZE TABLE not work? Because the default number of pages it uses to calculate statistics is too small for the difference. You can increase the table option STATS_SAMPLE_PAGES  until you find a proper one. The drawback is that the greater you set STATS_SAMPLE_PAGES, the longer it takes for ANALYZE TABLE to finish. Also, if you update a large portion of the table, you are often affected by lp:1538765. At some point, the statistics will again be inaccurate. Now let’s try our manual statistics update trick

InnoDB stores its persistent statistics in the tables

mysql.innodb_table_stats  and mysql.innodb_index_stats:mysql> alter table goods stats_persistent=1, stats_auto_recalc=0; Query OK, 0 rows affected (0.11 sec) Records: 0 Duplicates: 0 Warnings: 0 +---------------+------------+---------------------+---------+----------------------+--------------------------+ | database_name | table_name | last_update | n_rows | clustered_index_size | sum_of_other_index_sizes | +---------------+------------+---------------------+---------+----------------------+--------------------------+ | test | goods | 2017-09-05 00:21:12 | 7765796 | 34624 | 17600 | +---------------+------------+---------------------+---------+----------------------+--------------------------+ 1 row in set (0.00 sec) mysql> select * from mysql.innodb_index_stats where table_name='goods'; +---------------+------------+------------+---------------------+--------------+------------+-------------+-----------------------------------+ | database_name | table_name | index_name | last_update | stat_name | stat_value | sample_size | stat_description | +---------------+------------+------------+---------------------+--------------+------------+-------------+-----------------------------------+ | test | goods | PRIMARY | 2017-09-05 00:21:12 | n_diff_pfx01 | 7765796 | 20 | id | | test | goods | PRIMARY | 2017-09-05 00:21:12 | n_leaf_pages | 34484 | NULL | Number of leaf pages in the index | | test | goods | PRIMARY | 2017-09-05 00:21:12 | size | 34624 | NULL | Number of pages in the index | | test | goods | shop_id | 2017-09-05 00:21:12 | n_diff_pfx01 | 14523 | 20 | shop_id | | test | goods | shop_id | 2017-09-05 00:21:12 | n_diff_pfx02 | 168168 | 20 | shop_id,create_date | | test | goods | shop_id | 2017-09-05 00:21:12 | n_diff_pfx03 | 8045310 | 20 | shop_id,create_date,id | | test | goods | shop_id | 2017-09-05 00:21:12 | n_leaf_pages | 15288 | NULL | Number of leaf pages in the index | | test | goods | shop_id | 2017-09-05 00:21:12 | size | 17600 | NULL | Number of pages in the index | +---------------+------------+------------+---------------------+--------------+------------+-------------+-----------------------------------+ 8 rows in set (0.00 sec)

And we can update these tables directly:

mysql> update mysql.innodb_table_stats set n_rows=8000000 where table_name='goods'; Query OK, 1 row affected (0.18 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> update mysql.innodb_index_stats set stat_value=8000000 where stat_description in('id', 'shop_id,create_date,id') and table_name='goods'; Query OK, 2 rows affected (0.08 sec) Rows matched: 2 Changed: 2 Warnings: 0 mysql> update mysql.innodb_index_stats set stat_value=100 where stat_description in('shop_id') and table_name='goods'; Query OK, 1 row affected (0.09 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> update mysql.innodb_index_stats set stat_value=169861 where stat_description in('shop_id,create_date') and table_name='goods'; Query OK, 1 row affected (0.08 sec) Rows matched: 1 Changed: 1 Warnings: 0

I took index values from earlier, as calculated by this query:

select count(distinct id) as `Cardinality for PRIMARY`, count(distinct shop_id) as `Cardinality for shop_id column in index shop_id`, count(distinct shop_id, create_date) as `Cardinality for create_date column in index shop_id` from goods;

mysql> select * from mysql.innodb_table_stats where table_name='goods'; +---------------+------------+---------------------+---------+----------------------+--------------------------+ | database_name | table_name | last_update | n_rows | clustered_index_size | sum_of_other_index_sizes | +---------------+------------+---------------------+---------+----------------------+--------------------------+ | test | goods | 2017-09-05 00:47:45 | 8000000 | 34624 | 17600 | +---------------+------------+---------------------+---------+----------------------+--------------------------+ 1 row in set (0.00 sec) mysql> select * from mysql.innodb_index_stats where table_name='goods'; +---------------+------------+------------+---------------------+--------------+------------+-------------+-----------------------------------+ | database_name | table_name | index_name | last_update | stat_name | stat_value | sample_size | stat_description | +---------------+------------+------------+---------------------+--------------+------------+-------------+-----------------------------------+ | test | goods | PRIMARY | 2017-09-05 00:48:32 | n_diff_pfx01 | 8000000 | 20 | id | | test | goods | PRIMARY | 2017-09-05 00:21:12 | n_leaf_pages | 34484 | NULL | Number of leaf pages in the index | | test | goods | PRIMARY | 2017-09-05 00:21:12 | size | 34624 | NULL | Number of pages in the index | | test | goods | shop_id | 2017-09-05 00:49:13 | n_diff_pfx01 | 100 | 20 | shop_id | | test | goods | shop_id | 2017-09-05 00:49:26 | n_diff_pfx02 | 169861 | 20 | shop_id,create_date | | test | goods | shop_id | 2017-09-05 00:48:32 | n_diff_pfx03 | 8000000 | 20 | shop_id,create_date,id | | test | goods | shop_id | 2017-09-05 00:21:12 | n_leaf_pages | 15288 | NULL | Number of leaf pages in the index | | test | goods | shop_id | 2017-09-05 00:21:12 | size | 17600 | NULL | Number of pages in the index | +---------------+------------+------------+---------------------+--------------+------------+-------------+-----------------------------------+ 8 rows in set (0.00 sec)

Now the statistics are up to date, but not used:

mysql> explain select goods.* from goods join shops using(shop_id) where create_date BETWEEN CONVERT_TZ('2015-11-01 00:00:00', 'MET','GMT') AND CONVERT_TZ('2015-11-07 23:59:59', 'MET','GMT') and goods.shop_id in(4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60,61,62,63,64,65,66,67,68,69,70,71,72,73,74,75,76,77,78,79,80,81,82,83,84,85,86,87,88,89,90,91,92,93,94,95,96,97,98,99,100,101,102,103,104,105,106,107,108,109,110,111,112,113,114,115,116,117,401,402,403,404,405,406,407,408,409,410,411,412,413,414,415,417,418,419,420,421,422,423,424,425,426,427,428,429,430,431,432,433,434,435,436,437,438,439,440,441,442,443,444,445,446,447,448,449,450,451,452,453,454,455,456,457,458,459,460,461,462,463,464,465,466,467,468,470,471,472,473,474,475,476,477,478,479,480,481,482,483,484,485,486); +----+-------------+-------+------------+-------+---------------+---------+---------+--------------------+------+----------+--------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+---------+---------+--------------------+------+----------+--------------------------+ | 1 | SIMPLE | shops | NULL | index | PRIMARY | PRIMARY | 4 | NULL | 100 | 100.00 | Using where; Using index | | 1 | SIMPLE | goods | NULL | ref | shop_id | shop_id | 4 | test.shops.shop_id | 534 | 11.11 | Using index condition | +----+-------------+-------+------------+-------+---------------+---------+---------+--------------------+------+----------+--------------------------+ 2 rows in set, 1 warning (0.04 sec)

To finalize the changes, we need to run

FLUSH TABLE goods:mysql> FLUSH TABLE goods; Query OK, 0 rows affected (0.00 sec) mysql> explain select goods.* from goods join shops using(shop_id) where create_date BETWEEN CONVERT_TZ('2015-11-01 00:00:00', 'MET','GMT') AND CONVERT_TZ('2015-11-07 23:59:59', 'MET','GMT') and goods.shop_id in(4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60,61,62,63,64,65,66,67,68,69,70,71,72,73,74,75,76,77,78,79,80,81,82,83,84,85,86,87,88,89,90,91,92,93,94,95,96,97,98,99,100,101,102,103,104,105,106,107,108,109,110,111,112,113,114,115,116,117,401,402,403,404,405,406,407,408,409,410,411,412,413,414,415,417,418,419,420,421,422,423,424,425,426,427,428,429,430,431,432,433,434,435,436,437,438,439,440,441,442,443,444,445,446,447,448,449,450,451,452,453,454,455,456,457,458,459,460,461,462,463,464,465,466,467,468,470,471,472,473,474,475,476,477,478,479,480,481,482,483,484,485,486); +----+-------------+-------+------------+--------+---------------+---------+---------+--------------------+-------+----------+-----------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+--------+---------------+---------+---------+--------------------+-------+----------+-----------------------+ | 1 | SIMPLE | goods | NULL | range | shop_id | shop_id | 10 | NULL | 31997 | 100.00 | Using index condition | | 1 | SIMPLE | shops | NULL | eq_ref | PRIMARY | PRIMARY | 4 | test.goods.shop_id | 1 | 100.00 | Using index | +----+-------------+-------+------------+--------+---------------+---------+---------+--------------------+-------+----------+-----------------------+ 2 rows in set, 1 warning (0.28 sec) mysql> P md5sum PAGER set to 'md5sum' mysql> select goods.* from goods join shops using(shop_id) where create_date BETWEEN CONVERT_TZ('2015-11-01 00:00:00', 'MET','GMT') AND CONVERT_TZ('2015-11-07 23:59:59', 'MET','GMT') and goods.shop_id in(4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60,61,62,63,64,65,66,67,68,69,70,71,72,73,74,75,76,77,78,79,80,81,82,83,84,85,86,87,88,89,90,91,92,93,94,95,96,97,98,99,100,101,102,103,104,105,106,107,108,109,110,111,112,113,114,115,116,117,401,402,403,404,405,406,407,408,409,410,411,412,413,414,415,417,418,419,420,421,422,423,424,425,426,427,428,429,430,431,432,433,434,435,436,437,438,439,440,441,442,443,444,445,446,447,448,449,450,451,452,453,454,455,456,457,458,459,460,461,462,463,464,465,466,467,468,470,471,472,473,474,475,476,477,478,479,480,481,482,483,484,485,486); 4a94dabc4bfbfb7dd225bcb50278055b - 31896 rows in set (7.79 sec)

Now everything is good.

But

FLUSH TABLE is a blocking operation, right? Won’t it block queries and create a worse scenario than described for ANALYZE TABLE in this post?

At first glance this is true. But we can use the same trick Percona Toolkit uses: set

lock_wait_timeout to 1 and call FLUSH in a loop. To demonstrate how it works, I use a similar scenario as described in the ANALYZE TABLE blog post.

First, let’s reset the statistics to ensure our

FLUSH works as expected:mysql> analyze table goods; +------------+---------+----------+----------+ | Table | Op | Msg_type | Msg_text | +------------+---------+----------+----------+ | test.goods | analyze | status | OK | +------------+---------+----------+----------+ 1 row in set (0.38 sec) mysql> show indexes from goods; +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | goods | 0 | PRIMARY | 1 | id | A | 7765796 | NULL | NULL | | BTREE | | | | goods | 1 | shop_id | 1 | shop_id | A | 14523 | NULL | NULL | | BTREE | | | | goods | 1 | shop_id | 2 | create_date | A | 168168 | NULL | NULL | YES | BTREE | | | +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ 3 rows in set (0.00 sec)

And then update

mysql.innodb_*_stats tables manually. Then check that Optimizer still sees outdated statistics:mysql> update mysql.innodb_table_stats set n_rows=8000000 where table_name='goods'; Query OK, 1 row affected (0.09 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> update mysql.innodb_index_stats set stat_value=8000000 where stat_description in('id', 'shop_id,create_date,id') and table_name='goods'; Query OK, 2 rows affected (0.09 sec) Rows matched: 2 Changed: 2 Warnings: 0 mysql> update mysql.innodb_index_stats set stat_value=100 where stat_description in('shop_id') and table_name='goods'; Query OK, 1 row affected (0.11 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> update mysql.innodb_index_stats set stat_value=169861 where stat_description in('shop_id,create_date') and table_name='goods'; Query OK, 1 row affected (0.10 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> show indexes from goods; +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | goods | 0 | PRIMARY | 1 | id | A | 7765796 | NULL | NULL | | BTREE | | | | goods | 1 | shop_id | 1 | shop_id | A | 14523 | NULL | NULL | | BTREE | | | | goods | 1 | shop_id | 2 | create_date | A | 168168 | NULL | NULL | YES | BTREE | | | +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ 3 rows in set (0.00 sec)

Now let’s start a long running query in one session that blocks our

FLUSH TABLE command:mysql> select sleep(1) from goods limit 1000, 300;

And let’s run

FLUSH TABLE in a loop:sveta@Thinkie:~/build/ps-5.7/mysql-test$ until (`mysqlmtr -P13001 -e "set lock_wait_timeout=1; flush table goods;" test`); do sleep 1; done ERROR 1205 (HY000) at line 1: Lock wait timeout exceeded; try restarting transaction ERROR 1205 (HY000) at line 1: Lock wait timeout exceeded; try restarting transaction ERROR 1205 (HY000) at line 1: Lock wait timeout exceeded; try restarting transaction ...

Now let’s ensure we can access the table:

mysql> select * from goods order by id limit 10; ^C

We cannot! We cannot even connect to the database where the table is stored:

sveta@Thinkie:~/build/ps-5.7/mysql-test$ mysqlmtr -P13001 test Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A ^C

The reason for this is that while the 

FLUSH TABLE command was killed due to the metadata lock wait timeout, it also requested table lock for flushing and blocked other incoming queries.

But we can enclose

FLUSH TABLE into LOCK TABLE ... WRITE; ... UNLOCK TABLES; operations. In this case, the LOCK TABLE command gets blocked until all queries release metadata lock on the table. Then it exclusively locks the table, FLUSH TABLE runs and then the script immediately unlocks the table. Since closing the session causes an implicit unlock, I used a PHP one-liner to have everything in a single session:$ php -r ' > $link = new mysqli("127.0.0.1", "root", "", "test", 13001); > $link->query("set lock_wait_timeout=1"); > while(!$link->query("lock table goods write")) {sleep(1);} > $link->query("flush table goods"); > $link->query("unlock tables");'

We can confirm if a parallel session can access the table:

mysql> select * from goods order by id limit 10; +----+---------+----------------------------------+---------------------+ | id | shop_id | name | create_date | +----+---------+----------------------------------+---------------------+ | 1 | 58 | 5K0z2sHTgjWKKdryTaniQdZmjGjA9wls | 2015-09-19 00:00:00 | | 2 | 17 | xNll02kgUTWAFURj6j5lL1zXAubG0THG | 2013-10-19 00:00:00 | | 3 | 30 | clHX7uQopKmoTtEFH5LYBgQncsxRtTIB | 2017-08-01 00:00:00 | | 4 | 93 | bAzoQTN98AmFjPOZs7PGfbiGfaf9Ye4b | 2013-02-24 00:00:00 | | 5 | 20 | rQuTO5GHjP60kDbN6WoPpE2S8TtMbrVL | 2017-08-05 00:00:00 | | 6 | 37 | WxqxA5tBHxikaKbuvbIF84H9QuaCnqQ3 | 2013-10-18 00:00:00 | | 7 | 13 | DoYnFpQZSVV8UswBsWklgGBUc8zW9mVW | 2017-02-06 00:00:00 | | 8 | 81 | dkNxMQyZNZuTrONEX4gxRLa0DOedatIs | 2015-07-05 00:00:00 | | 9 | 12 | Z0t2uQ9itexpPf01KUpa7qBWlT5fBmXR | 2014-06-25 00:00:00 | | 10 | 90 | 6urABBQyaUVVyxljvd11D3kUxbdDRPRV | 2013-10-23 00:00:00 | +----+---------+----------------------------------+---------------------+ 10 rows in set (0.00 sec) mysql> update goods set name='test' where id=100; Query OK, 1 row affected (0.08 sec) Rows matched: 1 Changed: 1 Warnings: 0

After the PHP script finishes its job, statistics are corrected:

mysql> show index from goods; +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | goods | 0 | PRIMARY | 1 | id | A | 8000000 | NULL | NULL | | BTREE | | | | goods | 1 | shop_id | 1 | shop_id | A | 100 | NULL | NULL | | BTREE | | | | goods | 1 | shop_id | 2 | create_date | A | 169861 | NULL | NULL | YES | BTREE | | | +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ 3 rows in set (0.00 sec)

Conclusion

We can manually update persistent InnoDB statistics to fix Optimizer plans for our queries, with almost no impact on a live server.

MySQL Team speaking at Percona Live Dublin 2017


From September 25th to 27th 2017, the MySQL engineers and community team will be speaking at Percona Live Europe in Dublin.

MySQL is also part of the contributing sponsors.

For the conference, we tried to bring new faces to the Community. Usually, it’s always the same famous team leaders speaking at the conferences, this time, it’s not. A lot of key developers will present their own work. We have so much talented people in MySQL we want to present to our community.

Of course, once again we will focus our talks on MySQL 8.0. This is the list of sessions the MySQL Team will deliver:

Monday, Sep 25th

On Monday, I will deliver a tutorial with my friend Kenny from Percona. We will focus on the migration from a Master/Slave environment to a MySQL InnoDB Cluster running on MySQL 8.0. We will also highlight the last changes in Group Replication. Bring your laptop with Virtual Box 5.1 if you plan to attend it.

Tuesday, Sep 26th Wednesday, Sep 27th

Geir will be part of the keynote this time to tell us about the current state of Sakila. I’ll also have the pleasure to deliver a session and a demo with Jan.

I’m really looking forward this now usual European conference but in a new location as after London and Amsterdam, Dublin has been chosen to host it.

Don’t forget to also come to meet the MySQL Team during the Community Dinner !

Upcoming Webinar Tuesday September 12: Differences between MariaDB® and MySQL®

Join Percona’s Chief Evangelist, Colin Charles (@bytebot) as he presents Differences Between MariaDB and MySQL on Tuesday, September 12, 2017, at 7:00 am PDT / 10:00 am EDT (UTC-7).

Reserve Your Spot

 

Are they syntactically similar? Where do these two query languages differ? Why would I use one over the other?

MariaDB is on the path to gradually diverge from MySQL. One obvious example is the internal data dictionary currently under development for MySQL 8. This is a major change to the way metadata is stored and used within the server, and MariaDB doesn’t have an equivalent feature. Implementing this feature could mark the end of datafile-level compatibility between MySQL and MariaDB.

There are also non-technical differences between MySQL and MariaDB, including:

  • Licensing: MySQL offers their code as open-source under the GPL, and provides the option of non-GPL commercial distribution in the form of MySQL Enterprise. MariaDB can only use the GPL because their work is derived from the MySQL source code under the terms of that license.
  • Support services: Oracle provides technical support, training, certification and consulting for MySQL, while MariaDB has their own support services. Some people will prefer working with smaller companies, as traditionally it affords them more leverage as a customer.
  • Community contributions: MariaDB touts the fact that they accept more community contributions than Oracle. Part of the reason for this disparity is that developers like to contribute features, bug fixes and other code without a lot of paperwork overhead (and they complain about the Oracle Contributor Agreement). However, MariaDB has its own MariaDB Contributor Agreement — which more or less serves the same purpose.

Colin will take a look at some of the differences between MariaDB and MySQL and help answer some of the common questions our Database Performance Experts get about the two databases.

You can register for the webinar here.

Colin Charles, Percona Chief Evangelist

Colin Charles is the Chief Evangelist at Percona. He was previously on the founding team of MariaDB Server in 2009, worked at MySQL since 2005 and been a MySQL user since 2000. Before joining MySQL, he worked actively on the Fedora and OpenOffice.org projects. He’s well known within open source communities in APAC and has spoken at many conferences.

Building a Web App with AdonisJS

AdonisJS is a Node.js MVC framework. It offers a stable eco-system to write web servers so that you can focus on business needs over finalizing which package to choose or not. In this tutorial, I’ll be showing you how to build a web app with AdonisJS.

What We'll Be Building

In order to see how to build applications with AdonisJS, we’ll build a simple task list (todo) application. We’ll be using AdonisJS 4.0 in this tutorial. Below is a demo of what the final application will look like:

Requirements

This tutorial assumes you have the following installed on your computer:

  • Node.js 8.0 or greater
  • Npm 3.0 or greater
Installing Adonis CLI

We need to first install the Adonis CLI which will help us in creating new AdonisJS applications and also comes with some useful commands:

npm i -g @adonisjs/cli Create new project

We'll start by creating a new AdonisJS application. We'll make use of the adonis CLI.

adonis new adonis-tasks

The command above will create a new AdonisJS application with the name adonis-tasks using the fullstack app boilerplate. To make sure everything is working as expected, let’s run the newly created app. First, we cd into adonis-tasks and run the command below:

adonis serve --dev

Then visit http://127.0.0.1:3333 in your browser, and you should get something similar to the image below:

Good! Let’s now start fleshing out the application.

Database and Migration

We’ll start by structuring the application database. We’ll be using the AdonisJS migration schema to define our application’s database schema. Before we dive into the migration, let’s quickly take time to setup our database. For the purpose of this tutorial, we’ll be using MySQL. So, we need to install Node.js driver for MySQL.

npm install mysql --save

Next, we need to make AdonisJS know we are using MySQL. Taking a look at config/database.js, you see config settings for different databases including MySQL. Though we can easily enter our MySQL settings directly in the config file, that will mean we’ll have to change these settings every time we change our application environment (development, staging, production etc.) which is actually a bad practice. Instead, we’ll make use of environment variables and depending on the environment our application is running on, it will pull the settings for that environment. AdonisJS got us covered here. All we have to do is enter our config settings in the .env file.

So, open .env and add the snippet below to it:

// .env DB_CONNECTION=mysql DB_HOST=localhost DB_DATABASE=adonis-tasks DB_USER=root DB_PASSWORD=

Remember to update the database name, username and password accordingly with your own database settings.

For simplicity our application will have only one database table which we’ll call tasks. The tasks table will contain 3 fields id, title, created_at and updated_at. We’ll make use of the adonis make:migration command to create the migration file:

adonis make:migration tasks

On prompt choose Create table option and press Enter. This will create a new file within the database/migrations directory. The file name will be a timestamp with the name of the schema (in my case 1504289855390_tasks_schema.js). Open this file and update the up() as below:

// database/migrations/1504289855390_tasks_schema.js up () { this.create('tasks', (table) => { table.increments() table.string('title') table.timestamps() }) }

The increments() will create an id field with Auto Increment and set as Primary key. The timestamps() will create the created_at and updated_at fields respectively. With that done, we can run the migration:

adonis migration:run

With our database and table set up, let’s now create a model. We’ll call it Task. Though we won’t be making extensive use of the model in this tutorial, we'll use models over writing plain database queries because they bring ease of use and provide an expressive API to drive the data flow and also allows us use Lucid (AdonisJS ORM). To make a model, we use the adonis CLI make:model command:

adonis make:model Task

This will create a Task.js within the app/Models directory.

Creating Application Routes

Open start/routes.js and update with the snippet below:

// start/routes.js Route.get('/', 'TaskController.index') Route.post('tasks', 'TaskController.store') Route.delete('tasks/:id', 'TaskController.destroy')

We define three routes for our task list application. The first route will serve as our application landing page. It is bound to the index() of the TaskController (which we’ll create shortly). The second route is a POST request which will handle adding new task to the task list. It is bound to the store() of the TaskController. Lastly, we have a route with a DELETE request which will handle deleting a task. It takes the ID of a task to be deleted as a parameter. It is bound to the destroy() of the TaskController.

Creating The Task Controller

Having defined our application’s routes and bind to methods on the TaskController, it's time to create the TaskController itself. Again, we’ll use the adonis CLI command:

adonis make:controller Task

On prompt choose For HTTP requests option and press Enter. Now we have a TaskController.js file within the app/Controllers/Http directory.

Note: Before we ran the make:controller command, the app/Controllers/Http wasn’t present. It was created after running the command.

As we have seen from the routes above, the TaskController will have 3 methods (index(), store(), and destroy()). Open TaskController.js and add the following code into it:

// app/Controllers/Http/TaskController.js // remember to reference the Task model at the top const Task = use('App/Models/Task') async index ({ view }) { const tasks = await Task.all() return view.render('tasks.index', { tasks: tasks.toJSON() }) }

The index() simply fetches all the tasks that have been created from the database and renders a view. AdonisJS uses ES7 async/await and ES6 Object Destructuring. The tasks fetched is then passed to a view file tasks.index (which we’ll create shortly).

Creating Master Layout

AdonisJS makes use of Edge as its templating engine which has support for layouts. We are going to create a master layout for our application. All view files must be within the resources/views directory. So within the directory, let’s create a new view file and name it master.edge. Edge files have the .edge extension. Open the newly created file and paste the following code in it:

<!-- resources/views/master.edge --> <!DOCTYPE html> <html lang="en"> <head> <meta charset="UTF-8"> <meta name="viewport" content="width=device-width, initial-scale=1"> <title>Task List</title> {{ css('https://maxcdn.bootstrapcdn.com/font-awesome/4.7.0/css/font-awesome.min.css') }} {{ css('https://cdnjs.cloudflare.com/ajax/libs/bulma/0.5.1/css/bulma.min.css') }} </head> <body> <section class="section"> <div class="container"> <div class="columns"> <div class="column is-8 is-offset-2"> @!section('content') </div> </div> </div> </section> </body> </html>

We are using Bulma CSS framework. We use AdonisJS view’s css() global to reference our css files on CDN. The layout is simple, it contain only one section which is content.

Tips: The ! within @!section() indicate that it is a self closing section.

Creating The Task View

For simplicity our task list application will have just one view file. Every view specific stuff will be done within this view file. We are going to place this view within a tasks directory. Create a new directory named tasks within the resources/views directory, then within the task directory, create a new view file and name it index.edge. Now, open the index.edge file and paste the following code into it:

<!-- resources/views/tasks/index.edge --> @layout('master') @section('content') <div class="box"> <h1 class="title">Task List</h1> <table class="table is-bordered is-striped is-narrow is-fullwidth"> <thead> <tr> <th>SN</th> <th>Title</th> <th>Action</th> </tr> </thead> <tbody> @each(task in tasks) <tr> <td> {{ ($loop.index + 1) }} </td> <td> {{ task.title }} </td> <td> <button class="button is-danger is-outlined"> <span>DELETE</span> <span class="icon is-small"> <i class="fa fa-times" aria-hidden="true"></i> </span> </button> </td> </tr> @else <tr> <td colspan="3" class="has-text-centered">No task created yet!</td> </tr> @endeach </tbody> </table> </div> @endsection

First, we indicate we are using the master layout we created above. We simply display the tasks in a table. If there are no tasks, we display a appropriate message. For the SN of the tasks, we are using the index property of Edge’s $loop variable. The index property holds the iteration index, which starts from 0, hence the addition of 1. Lastly, we have a delete button which does nothing for now.

If we visit the application in the browser, since we haven’t added any tasks yet, we should get something similar to the image below:

Adding New Task

Let’s update the index.edge file to include a form for adding a new task. Add the following code immediately after @section('content'):

<!-- resources/views/tasks/index.edge --> <div class="box"> <h2 class="title">New Task</h2> <form action="/tasks" method="POST"> {{ csrfField() }} <div class="field has-addons"> <div class="control is-expanded"> <input class="input" type="text" name="title" value="{{ old('title', '') }}" placeholder="Task title"> </div> <div class="control"> <button type="submit" class="button is-primary"> Add Task </button> </div> </div> {{ elIf('<p class="help is-danger">$self</p>', getErrorFor('title'), hasErrorFor('title')) }} </form> </div>

It’s a simple form with one field for the title of the task. We also add a CSRF field since AdonisJS by default prevent us from CSRF attacks. Lastly, we display a validation error message if the form fails validation.

Next, we need to create the store() that will handle adding a new task to the database. Before we create this method, let’s quickly setup Adonis validator which will be used for validating our form. The validator is not installed by default, so we need to install it first:

adonis install @adonisjs/validator

Next, we need to register the provider inside start/app.js:

const providers = [ ... '@adonisjs/validator/providers/ValidatorProvider' ]

Now, let’s create the store() in TaskController.js. Paste the snippet below just after the index():

// app/Controllers/Http/TaskController.js // remember to reference the Validator at the top const { validate } = use('Validator') async store ({ request, response, session }) { // validate form input const validation = await validate(request.all(), { title: 'required|min:3|max:255' }) // show error messages upon validation fail if (validation.fails()) { session.withErrors(validation.messages()) .flashAll() return response.redirect('back') } // persist to database const task = new Task() task.title = request.input('title') await task.save() // Fash success message to session session.flash({ notification: 'Task added!' }) return response.redirect('back') }

First, we validate the requests coming from the form against some rules. If the validation fails, we simply save the validation messages to the session and return back to the form with the error messages. If everything went well, we persist the new task to the database and flash a notification message indicating that the task was added successfully then redirect to the form.

With that done, we can now add tasks to the task list. You should get something similar to the image below:

Deleting a Task

The last functionality our task list application will have is “deleting tasks”. To achieve this, we need to update the dummy delete button created earlier to include actual form for deleting a specific task. Replace the delete button entirely with the code below:

<!-- resources/views/tasks/index.edge --> <form action="{{ 'tasks/' + task.id + '?_method=DELETE' }}" method="POST"> {{ csrfField() }} <button type="submit" class="button is-danger is-outlined"> <span>DELETE</span> <span class="icon is-small"> <i class="fa fa-times" aria-hidden="true"></i> </span> </button> </form>

Remember the route handling deleting of task accepts the ID of the task as a parameter, so we are attaching the task ID to the form action. Also, we are passing the request method (DELETE in this case) as query string. This is the AdonisJS way of doing method spoofing, since HTML forms aren’t capable of making requests other than GET and POST.

Next, we add the destroy() to TaskController.js. Paste the code below into it just after the store():

// app/Controllers/Http/TaskController.js async destroy ({ params, session, response }) { const task = await Task.find(params.id) await task.delete() // Fash success message to session session.flash({ notification: 'Task deleted!' }) return response.redirect('back') }

We first get the ID of the task from the params object and then use it to retrieve the task from the database. We then delete the task thereafter. Lastly, we flash an appropriate message and redirect back to the page.

Below is what we get when we delete the task added above:

Conclusion

That’s it. We have been able to build a simple application with AdonisJS. Though this tutorial only covered the basics of AdonisJS, it should get you started in building your application with AdonisJS. I hope you find this tutorial helpful. If you have any questions, suggestions, comments, kindly leave them below.

Monitoring MySQL Problematic Queries

This blog describes how to identify queries that cause a sudden spike in system resources as well as the user and host who executed the culprit query using the Monyog MySQL Monitor and Advisor.

How many times have you seen a system go live and perform much worse than it did in testing? There could be several reasons behind bad performance. For instance, a slow running query in MySQL can be caused by a poor database design or may be due to higher-than-normal latency in network communication. Other issues such as using too few or too many indexes may also be a factor. This blog will identify the types of poorly performing queries and outline some concrete strategies for identifying them using monitoring. Finally, some tips for improving performance will be presented.

The Effects of Misbehaving Queries

Typically, misbehaving queries will result in two possible outcomes: high CPU usage and/or slow execution. The two issues tend to be related to some degree, because one will lead to or exacerbate the other. Depending on the root cause, the problem may be a database configuration or query issue. For instance, as MySQL databases grow in size, tables get fragmented over time. This contributes to MySQL load spikes. Protecting a server from MySQL high CPU issues requires close monitoring and periodic optimization of the database. Meanwhile, a query that requires a high degree of type conversion will also place a burden on the CPU.

Identifying the culprit requires a different approach based on the dominant outcome: high CPU usage and/or slow execution. In the next sections, we will examine how to track down both causes.

Some Well-known Causes of Slow-running Queries

Without knowing the root cause of a slow running query, it’s difficult for a DBA to troubleshoot the problem. Therefore, the first step should be to check efficiency of all the database components before going to use a query monitor or optimizer. This check will help to understand whether the root cause of the problem is related to a query or something else. Here are a few potential causes to consider:

  • Network latency: Is the slowness limited to a specific query, batch process, database, or are other network resources suffering as well?
  • Another reason behind SQL performance issue could be a bad index creation or accessing a bad index from the specified queries.
  • Choosing a slow execution plan may degrade performance.
  • Running a single query at a time may go smoothly, but check if running multiple queries at the same time hampers server performance.
  • If someone is experiencing a bad performance issue with database components, then a System Monitor can be quite helpful. By employing a System Monitor, performance of both database and non-database components can be monitored.
  • Ad hoc SQL queries that are run outside of a stored procedure: stored procedures almost always offer better performance because MySQL can cache their execution plans; ad hoc queries should, whenever feasible, be converted to stored procedures.
  • Long-running or CPU-heavy queries in execution plans. Table scan operations indicate the lack of a suitable index, and putting an index in place to eliminate the table scan can have an immediate and positive effect on performance.
  • Queries that include a large number of joins. Joins take time, and while MySQL Server is obviously designed to handle them, a large number of joins can really slow things down. A good general rule of thumb is to limit the number of joins to seven; if you have more than that, you may have to start looking at ways to cut back.
  • A slow-running query that always runs slowly. This is a query that could perhaps be rewritten to perform better. A query that runs slowly some of the time is one that’s likely being affected by outside factors, such as locks or resource contention.
Employing Monyog Tools

Using a Monitoring and Profiling tool such as Monyog will help in improving the performance issues that are related to queries. Monyog can display long running queries (Queries that holds a large number of resources) in MySQL, as well as a host of other potential issues, such as hanging threads and improper index usage – i.e. over or under utilization.

The Overview Page

As soon as you’ve logged into Monyog, the Overview page provides a high level picture of all the selected servers registered with Monyog. Below the count of the total servers registered with Monyog, total number of disconnected servers, and servers having critical alerts and warnings, you’ll find the top 10 queries across the selected servers in Monyog, based on total execution time:

Monyog Overview – Top 10 MySQL queries

Queries with the longest execution times are positioned at the top of the list, giving you an immediate place to start looking at bottlenecks. You can click on a query to get more details. These give the list of server names on which the particular query was executed. Clicking on the server names will open the sniffer for that server with the time-range selected as the first and last seen of the query.

Index Usage

Beyond the Overview page, the Index Usage monitor group is good starting point for identifying the presence of table scans. You should always try to keep these as low as possible by building indexes on searchable fields.

Although this screen does not relay information about specific queries, the monitor groups in Monyog read the MySQL Slow Query log do provide that information (covered in the next section). Once you have identified the problematic queries, you can create the appropriate indexes or rewrite the queries to use indexes.

Monitors tab – Index usage

Examining the MySQL Slow Query Log

The MySQL slow query log is a log that MySQL sends slow, potentially problematic queries to. Generally the queries that are logged are those that take longer than a specified amount of time to execute or queries that do not properly hit indexes.  Queries that do not use an index may not be slow if there are only a few hundred or few thousand records in the table(s) involved. But they are ‘potentially slow’ and should be identified if they access tables, which will continue to grow.

This logging functionality comes with MySQL but is turned off by default.  You can check whether or not it’s turned on from the MySQL Logs screen.

Monitors tab – MySQL logs

The No. of Slow Queries relays how many queries are taking longer than the Min. execution time for a query to be considered slow threshold (based on the MySQL long_query_time variable).  On the right, there is a chart icon that, when clicked, opens the TREND VALUES chart.

Selecting “History” from the TIMEFRAME dropdown at the top of the Monitors page allows us to group trend data by minutes, hours, days, weeks, months, or years.  More precise timeframes help to more accurately tie the offending query to the slowdown event:

Monitors tab – No. of Slow Queries

Thread Monitors

Accessible from the left-hand button bar (refer to the highlighted icon below), the Threads page shows you the number of threads currently being executed by MySQL fetched using the query SHOW FULL PROCESSLIST. Each query sent to MySQL is executed in a thread. The Threads feature should be used to check which queries are being executed presently. It gives you a general sense of what is keeping your server busy at that moment.

While not the best feature for monitoring queries executed over a period of time (that would be the Query Analyzer presented in the next section), it can nonetheless be employed to locate runaway processes.  Under the Actions heading on the far-right, there are buttons to kill each thread:

Threads

Real-time Query Monitoring

Monyog also gives us the ability to monitor MySQL servers via Real-time monitoring.  It may also provide invaluable information on poorly performing queries. To show performance metrics:

  1. Click the (Real-time) Clock icon on the left-hand side of the screen.
  2. On the next screen:
    1. Select a server to monitor.
    2. You may then choose to start a new session or load a saved one. 

Realtime MySQL Monitor

Like the Overview page, Monyog’s Query Analyzer screen also displays The Average Latency and the Total Time taken by each query to execute.  In addition, you’ll find the user and host who executed the query.

You can delve deeper into a query’s mechanics via the EXPLAIN command by clicking on the query and selecting the Explain tab on the Query Details screen:

Query Analyzer

The Explain Result’s Type column describes how tables are joined.  In the above Explain Result, we see a type of “ALL” in combination with an absence of keys.  That indicates that a full table scan is being done for each combination of rows from the previous tables. This is normally not good if the table is the first table not marked “const”, and usually very bad in all other cases. You can usually avoid ALL by adding indexes that enable row retrieval from the table based on constant values or column values from earlier tables.

MySQL queries details screen

“Performance Schema” Mode for Data Collection

The latest update of Monyog brings new easier ways to find problem SQL in Real-Time, including “sniffer” based Query Analyser using Performance Schema.  It allows you to view the success/failure status of every query executed and also the number of ‘full table scans’ for queries in a single glance.

Monyog’s query sniffer is a functionality that records a ‘pseudo server log’ and stores it in the Monyog embedded database.

The Sniffing Mode is set on the ADVANCED tab of the server properties dialog.

Server properties dialog

With ‘Performance Schema Sniffer’ enabled on the Query Analyzer screen, you can include many additional columns to the analysis, including a count of Full Table Scans.

Query Analyzer screen with manage columns panel

Here is the Query Analyzer screen in Sniffer mode with the Full Table Scan column added:

Query analyzer – sniffer

A Few More Tips…

Some other ideas that may help fix CPU problems:

  • Run SHOW FULL PROCESSLIST; while the CPU load is high.  This will show you any queries that are currently running or in the queue to run and what it’s doing.
  • Keep an eye on things like your buffer sizes, table cache, query cache and innodb_buffer_pool_size (if you’re using innodb tables) as all of these memory allocations can adversely affect query performance which can cause MySQL to consume CPU cycles.
  • If you are using WordPress, its plugins are notorious for doing monstrous queries.
  • Try modifying the innodb_buffer_pool_size parameter.  It should be set to at least the size of the file ibdata1, which is located in /var/lib/mysql.  InnoDB works much more efficiently when it is able to be resident in memory. This may be impractical in some situations because the ibdata1 can be quite large.  The innodb_log_buffer_size parameter should be 25% of the size of innodb_buffer_pool_size.
  • Give MySQL at least half of available server memory if possible.
Conclusion

By using the Monyog features described in this blog, you should be able to identify the queries and/or processes that are causing system bottlenecks.   You can then make the changes to improve the performance by modifying the query, Indexes and database design, based on Monyog’s Advice text.

SQL query-performance tuning is as much art as science, and is thought by some to belong to the realm of application development rather than Database Administration.  Under that assumption, the goal of DBAs would be to identify those slow-running or CPU-intensive queries, gather evidence and then work with developers to find ways of improving them.

Monyog is an agentless MySQL monitoring tool that can uncover key MySQL performance insights. You can download a 14-day free trial here.

The post Monitoring MySQL Problematic Queries appeared first on Webyog Blog.

Native ProxySQL Clustering now available

ProxySQL 1.4.2 now supports Native Clustering!!!

ProxySQL enters the club of the software associated with the buzz word "Cluster".
This blog post is the first in a series describing how to setup ProxySQL Cluster.
For details on the implementation, please refer to the documentation in the wiki

Disclaimer
Features described are EXPERIMENTAL and subject to change.

Preface

ProxySQL is a decentralized proxy, recommended to be deployed close to the application. This approach scales pretty well even up to hundreds of nodes, as it was designed to be easily reconfigurable at runtime.
This allows for simple configure a farm of ProxySQL instances using software like Ansible/Chef/Puppet/Salt (in alphabetical order), or service discovery tools like Etcd/Consul/ZooKeeper.
Furthermoore, ProxySQL is highly customizable, and can be adopted in any setup which makes use of these technologies, or even home-made tools.

This solution however have its drawbacks:

  • External software is required for its configuration (i.e. configuration management software)
  • Multiple instance can't sync up their configuration natively
  • Converge time it is not predictable
  • There is no protection against split brain (network partition)

In order to address the above, new features have been introduced in ProxySQL 1.4.2 to support clustering natively.
As we've already pointed, these features are EXPERIMENTAL and subject to change, especially because not all the features in the roadmap have been implemented yet.

Setting up a 3 node ProxySQL Cluster

We will start by setting up a cluster with just 3 nodes.
We will use the following bootstrap config file for /etc/proxysql.cnf.
Admin variables will mostly be set to their default values, we are specifying them in any case to enumerate the new cluster variables. What is important to note is that the cluster_username and cluster_password variables should specify credentials also listed in admin_credentials. In fact, admin_credentials can contain multiple sets of credentials.

datadir="/var/lib/proxysql" admin_variables = { admin_credentials="admin:admin;cluster1:secret1pass" mysql_ifaces="0.0.0.0:6032" cluster_username="cluster1" cluster_password="secret1pass" cluster_check_interval_ms=200 cluster_check_status_frequency=100 cluster_mysql_query_rules_save_to_disk=true cluster_mysql_servers_save_to_disk=true cluster_mysql_users_save_to_disk=true cluster_proxysql_servers_save_to_disk=true cluster_mysql_query_rules_diffs_before_sync=3 cluster_mysql_servers_diffs_before_sync=3 cluster_mysql_users_diffs_before_sync=3 cluster_proxysql_servers_diffs_before_sync=3 } proxysql_servers = ( { hostname="172.16.3.130" port=6032 comment="proxysql130" }, { hostname="172.16.3.131" port=6032 comment="proxysql131" }, { hostname="172.16.3.132" port=6032 comment="proxysql132" } )

At this stage, we can start proxysql on all the 3 nodes. As pointed out in the documention, when clustering is active proxysql generates checksums for the configuration of each active module.
Let's connect to any of the proxysql admin interface, and check the current configuration checksums:

Admin130> SELECT * FROM runtime_checksums_values ORDER BY name; +-------------------+---------+------------+--------------------+ | name | version | epoch | checksum | +-------------------+---------+------------+--------------------+ | admin_variables | 0 | 0 | | | mysql_query_rules | 1 | 1504615779 | 0x0000000000000000 | | mysql_servers | 1 | 1504615779 | 0x0000000000000000 | | mysql_users | 1 | 1504615779 | 0x0000000000000000 | | mysql_variables | 0 | 0 | | | proxysql_servers | 1 | 1504615779 | 0x474020F334F98128 | +-------------------+---------+------------+--------------------+ 6 rows in set (0.00 sec)

Note that only 4 modules are active right now:

  • mysql_query_rules
  • mysql_servers
  • mysql_users
  • proxysql_servers

Modules not active have version=0.
Modules with empty configuration have a zero valued checksum.
Epoch is a unix timestamp.

Admin130> SELECT name,FROM_UNIXTIME(epoch), checksum FROM runtime_checksums_values WHERE version ORDER BY name; +-------------------+----------------------+--------------------+ | name | FROM_UNIXTIME(epoch) | checksum | +-------------------+----------------------+--------------------+ | mysql_query_rules | 2017-09-05 12:49:39 | 0x0000000000000000 | | mysql_servers | 2017-09-05 12:49:39 | 0x0000000000000000 | | mysql_users | 2017-09-05 12:49:39 | 0x0000000000000000 | | proxysql_servers | 2017-09-05 12:49:39 | 0x474020F334F98128 | +-------------------+----------------------+--------------------+ 4 rows in set (0.00 sec)

To view the status of the whole cluster, we need to check table stats_proxysql_servers_checksums:

Admin130> SELECT * FROM stats_proxysql_servers_checksums; +--------------+------+-------------------+---------+------------+--------------------+------------+------------+------------+ | hostname | port | name | version | epoch | checksum | changed_at | updated_at | diff_check | +--------------+------+-------------------+---------+------------+--------------------+------------+------------+------------+ | 172.16.3.132 | 6032 | admin_variables | 0 | 0 | | 0 | 1504618520 | 0 | | 172.16.3.132 | 6032 | mysql_query_rules | 1 | 1504615782 | 0x0000000000000000 | 1504615782 | 1504618520 | 0 | | 172.16.3.132 | 6032 | mysql_servers | 1 | 1504615782 | 0x0000000000000000 | 1504615782 | 1504618520 | 0 | | 172.16.3.132 | 6032 | mysql_users | 1 | 1504615782 | 0x0000000000000000 | 1504615782 | 1504618520 | 0 | | 172.16.3.132 | 6032 | mysql_variables | 0 | 0 | | 0 | 1504618520 | 0 | | 172.16.3.132 | 6032 | proxysql_servers | 1 | 1504615782 | 0x474020F334F98128 | 1504615782 | 1504618520 | 0 | | 172.16.3.131 | 6032 | admin_variables | 0 | 0 | | 0 | 1504618520 | 0 | | 172.16.3.131 | 6032 | mysql_query_rules | 1 | 1504615780 | 0x0000000000000000 | 1504615781 | 1504618520 | 0 | | 172.16.3.131 | 6032 | mysql_servers | 1 | 1504615780 | 0x0000000000000000 | 1504615781 | 1504618520 | 0 | | 172.16.3.131 | 6032 | mysql_users | 1 | 1504615780 | 0x0000000000000000 | 1504615781 | 1504618520 | 0 | | 172.16.3.131 | 6032 | mysql_variables | 0 | 0 | | 0 | 1504618520 | 0 | | 172.16.3.131 | 6032 | proxysql_servers | 1 | 1504615780 | 0x474020F334F98128 | 1504615781 | 1504618520 | 0 | | 172.16.3.130 | 6032 | admin_variables | 0 | 0 | | 0 | 1504618520 | 0 | | 172.16.3.130 | 6032 | mysql_query_rules | 1 | 1504615779 | 0x0000000000000000 | 1504615780 | 1504618520 | 0 | | 172.16.3.130 | 6032 | mysql_servers | 1 | 1504615779 | 0x0000000000000000 | 1504615780 | 1504618520 | 0 | | 172.16.3.130 | 6032 | mysql_users | 1 | 1504615779 | 0x0000000000000000 | 1504615780 | 1504618520 | 0 | | 172.16.3.130 | 6032 | mysql_variables | 0 | 0 | | 0 | 1504618520 | 0 | | 172.16.3.130 | 6032 | proxysql_servers | 1 | 1504615779 | 0x474020F334F98128 | 1504615780 | 1504618520 | 0 | +--------------+------+-------------------+---------+------------+--------------------+------------+------------+------------+ 18 rows in set (0.00 sec)

Modules which are not active have version=0, empty configurations also have zero-values, and timestamps are "unix timestamps":

Admin130> SELECT hostname, port, name, version, FROM_UNIXTIME(epoch) epoch, checksum, FROM_UNIXTIME(changed_at) changed_at, FROM_UNIXTIME(updated_at) updated_at, diff_check, DATETIME('NOW') FROM stats_proxysql_servers_checksums WHERE version > 0 ORDER BY hostname, name; +--------------+------+-------------------+---------+---------------------+--------------------+---------------------+---------------------+------------+---------------------+ | hostname | port | name | version | epoch | checksum | changed_at | updated_at | diff_check | DATETIME('NOW') | +--------------+------+-------------------+---------+---------------------+--------------------+---------------------+---------------------+------------+---------------------+ | 172.16.3.130 | 6032 | mysql_query_rules | 1 | 2017-09-05 12:49:39 | 0x0000000000000000 | 2017-09-05 12:49:40 | 2017-09-05 13:44:48 | 0 | 2017-09-05 13:44:48 | | 172.16.3.130 | 6032 | mysql_servers | 1 | 2017-09-05 12:49:39 | 0x0000000000000000 | 2017-09-05 12:49:40 | 2017-09-05 13:44:48 | 0 | 2017-09-05 13:44:48 | | 172.16.3.130 | 6032 | mysql_users | 1 | 2017-09-05 12:49:39 | 0x0000000000000000 | 2017-09-05 12:49:40 | 2017-09-05 13:44:48 | 0 | 2017-09-05 13:44:48 | | 172.16.3.130 | 6032 | proxysql_servers | 1 | 2017-09-05 12:49:39 | 0x474020F334F98128 | 2017-09-05 12:49:40 | 2017-09-05 13:44:48 | 0 | 2017-09-05 13:44:48 | | 172.16.3.131 | 6032 | mysql_query_rules | 1 | 2017-09-05 12:49:40 | 0x0000000000000000 | 2017-09-05 12:49:41 | 2017-09-05 13:44:48 | 0 | 2017-09-05 13:44:48 | | 172.16.3.131 | 6032 | mysql_servers | 1 | 2017-09-05 12:49:40 | 0x0000000000000000 | 2017-09-05 12:49:41 | 2017-09-05 13:44:48 | 0 | 2017-09-05 13:44:48 | | 172.16.3.131 | 6032 | mysql_users | 1 | 2017-09-05 12:49:40 | 0x0000000000000000 | 2017-09-05 12:49:41 | 2017-09-05 13:44:48 | 0 | 2017-09-05 13:44:48 | | 172.16.3.131 | 6032 | proxysql_servers | 1 | 2017-09-05 12:49:40 | 0x474020F334F98128 | 2017-09-05 12:49:41 | 2017-09-05 13:44:48 | 0 | 2017-09-05 13:44:48 | | 172.16.3.132 | 6032 | mysql_query_rules | 1 | 2017-09-05 12:49:42 | 0x0000000000000000 | 2017-09-05 12:49:42 | 2017-09-05 13:44:48 | 0 | 2017-09-05 13:44:48 | | 172.16.3.132 | 6032 | mysql_servers | 1 | 2017-09-05 12:49:42 | 0x0000000000000000 | 2017-09-05 12:49:42 | 2017-09-05 13:44:48 | 0 | 2017-09-05 13:44:48 | | 172.16.3.132 | 6032 | mysql_users | 1 | 2017-09-05 12:49:42 | 0x0000000000000000 | 2017-09-05 12:49:42 | 2017-09-05 13:44:48 | 0 | 2017-09-05 13:44:48 | | 172.16.3.132 | 6032 | proxysql_servers | 1 | 2017-09-05 12:49:42 | 0x474020F334F98128 | 2017-09-05 12:49:42 | 2017-09-05 13:44:48 | 0 | 2017-09-05 13:44:48 | +--------------+------+-------------------+---------+---------------------+--------------------+---------------------+---------------------+------------+---------------------+ 12 rows in set (0.00 sec)

To clarify some fields:

  • epoch is the timestamp generated by the remote proxy when executing LOAD ... TO RUNTIME
  • changed_at is the timestamp recorded when the local proxy detected a change in the remote proxy
  • updated_at is the last timestamp for which the local proxy retrieved the checksum from the remote proxy
Add new users

Now that we have ProxySQL Cluster up and running, let's see how configuration is propagated from a single node to all other nodes.
Lets start by adding a new user on one of the nodes.

Admin130> SELECT * FROM mysql_users; Empty set (0.00 sec) Admin130> INSERT INTO mysql_users(username,password) VALUES ('sbtest','sbtest'); Query OK, 1 row affected (0.00 sec) Admin130> SELECT * FROM mysql_users; +----------+----------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+ | username | password | active | use_ssl | default_hostgroup | default_schema | schema_locked | transaction_persistent | fast_forward | backend | frontend | max_connections | +----------+----------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+ | sbtest | sbtest | 1 | 0 | 0 | NULL | 0 | 1 | 0 | 1 | 1 | 10000 | +----------+----------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+ 1 row in set (0.00 sec) Admin130> SELECT * FROM runtime_mysql_users; Empty set (0.00 sec)

As we can see from the above example, there are no users loaded at runtime, we have just created a new user in memory.
Time to load it into runtime and see what happens next:

Admin130> LOAD MYSQL USERS TO RUNTIME; Query OK, 0 rows affected (0.00 sec) Admin> SELECT hostname, port, name, version, FROM_UNIXTIME(epoch) epoch, checksum, FROM_UNIXTIME(changed_at) changed_at, FROM_UNIXTIME(updated_at) updated_at, diff_check, DATETIME('NOW') FROM stats_proxysql_servers_checksums WHERE version > 0 ORDER BY hostname, name; +--------------+------+-------------------+---------+---------------------+--------------------+---------------------+---------------------+------------+---------------------+ | hostname | port | name | version | epoch | checksum | changed_at | updated_at | diff_check | DATETIME('NOW') | +--------------+------+-------------------+---------+---------------------+--------------------+---------------------+---------------------+------------+---------------------+ | 172.16.3.130 | 6032 | mysql_query_rules | 1 | 2017-09-05 12:49:39 | 0x0000000000000000 | 2017-09-05 12:49:40 | 2017-09-05 13:47:52 | 0 | 2017-09-05 13:47:53 | | 172.16.3.130 | 6032 | mysql_servers | 1 | 2017-09-05 12:49:39 | 0x0000000000000000 | 2017-09-05 12:49:40 | 2017-09-05 13:47:52 | 0 | 2017-09-05 13:47:53 | | 172.16.3.130 | 6032 | mysql_users | 2 | 2017-09-05 13:47:48 | 0x7917CD487C11478F | 2017-09-05 13:47:48 | 2017-09-05 13:47:52 | 0 | 2017-09-05 13:47:53 | | 172.16.3.130 | 6032 | proxysql_servers | 1 | 2017-09-05 12:49:39 | 0x474020F334F98128 | 2017-09-05 12:49:40 | 2017-09-05 13:47:52 | 0 | 2017-09-05 13:47:53 | | 172.16.3.131 | 6032 | mysql_query_rules | 1 | 2017-09-05 12:49:40 | 0x0000000000000000 | 2017-09-05 12:49:41 | 2017-09-05 13:47:53 | 0 | 2017-09-05 13:47:53 | | 172.16.3.131 | 6032 | mysql_servers | 1 | 2017-09-05 12:49:40 | 0x0000000000000000 | 2017-09-05 12:49:41 | 2017-09-05 13:47:53 | 0 | 2017-09-05 13:47:53 | | 172.16.3.131 | 6032 | mysql_users | 2 | 2017-09-05 13:47:48 | 0x7917CD487C11478F | 2017-09-05 13:47:49 | 2017-09-05 13:47:53 | 0 | 2017-09-05 13:47:53 | | 172.16.3.131 | 6032 | proxysql_servers | 1 | 2017-09-05 12:49:40 | 0x474020F334F98128 | 2017-09-05 12:49:41 | 2017-09-05 13:47:53 | 0 | 2017-09-05 13:47:53 | | 172.16.3.132 | 6032 | mysql_query_rules | 1 | 2017-09-05 12:49:42 | 0x0000000000000000 | 2017-09-05 12:49:42 | 2017-09-05 13:47:53 | 0 | 2017-09-05 13:47:53 | | 172.16.3.132 | 6032 | mysql_servers | 1 | 2017-09-05 12:49:42 | 0x0000000000000000 | 2017-09-05 12:49:42 | 2017-09-05 13:47:53 | 0 | 2017-09-05 13:47:53 | | 172.16.3.132 | 6032 | mysql_users | 2 | 2017-09-05 13:47:48 | 0x7917CD487C11478F | 2017-09-05 13:47:49 | 2017-09-05 13:47:53 | 0 | 2017-09-05 13:47:53 | | 172.16.3.132 | 6032 | proxysql_servers | 1 | 2017-09-05 12:49:42 | 0x474020F334F98128 | 2017-09-05 12:49:42 | 2017-09-05 13:47:53 | 0 | 2017-09-05 13:47:53 | +--------------+------+-------------------+---------+---------------------+--------------------+---------------------+---------------------+------------+---------------------+ 12 rows in set (0.00 sec) Admin130> SELECT hostname, port, name, version, FROM_UNIXTIME(epoch) epoch, checksum, FROM_UNIXTIME(changed_at) changed_at, FROM_UNIXTIME(updated_at) updated_at, diff_check, DATETIME('NOW') FROM stats_proxysql_servers_checksums WHERE name='mysql_users' ORDER BY hostname, name; +--------------+------+-------------+---------+---------------------+--------------------+---------------------+---------------------+------------+---------------------+ | hostname | port | name | version | epoch | checksum | changed_at | updated_at | diff_check | DATETIME('NOW') | +--------------+------+-------------+---------+---------------------+--------------------+---------------------+---------------------+------------+---------------------+ | 172.16.3.130 | 6032 | mysql_users | 2 | 2017-09-05 13:47:48 | 0x7917CD487C11478F | 2017-09-05 13:47:48 | 2017-09-05 13:48:21 | 0 | 2017-09-05 13:48:21 | | 172.16.3.131 | 6032 | mysql_users | 2 | 2017-09-05 13:47:48 | 0x7917CD487C11478F | 2017-09-05 13:47:49 | 2017-09-05 13:48:21 | 0 | 2017-09-05 13:48:21 | | 172.16.3.132 | 6032 | mysql_users | 2 | 2017-09-05 13:47:48 | 0x7917CD487C11478F | 2017-09-05 13:47:49 | 2017-09-05 13:48:21 | 0 | 2017-09-05 13:48:21 | +--------------+------+-------------+---------+---------------------+--------------------+---------------------+---------------------+------------+---------------------+ 3 rows in set (0.00 sec)

We applied the change on 172.16.3.130 and the change was propagated everywhere else almost instantly.
Checking the log on 172.16.3.131 we can see the log entries generated during the sync:

2017-09-05 14:47:48 [INFO] Cluster: detected a new checksum for mysql_users from peer 172.16.3.130:6032, version 2, epoch 1504619268, checksum 0x7917CD487C11478F . Not syncing yet ... 2017-09-05 14:47:48 [INFO] Cluster: detected a peer 172.16.3.130:6032 with mysql_users version 2, epoch 1504619268, diff_check 3. Own version: 1, epoch: 1504615780. Proceeding with remote sync 2017-09-05 14:47:48 [INFO] Cluster: detected a peer 172.16.3.130:6032 with mysql_users version 2, epoch 1504619268, diff_check 4. Own version: 1, epoch: 1504615780. Proceeding with remote sync 2017-09-05 14:47:48 [INFO] Cluster: detected peer 172.16.3.130:6032 with mysql_users version 2, epoch 1504619268 2017-09-05 14:47:48 [INFO] Cluster: Fetching MySQL Users from peer 172.16.3.130:6032 started 2017-09-05 14:47:48 [INFO] Cluster: Fetching MySQL Users from peer 172.16.3.130:6032 completed 2017-09-05 14:47:48 [INFO] Cluster: Loading to runtime MySQL Users from peer 172.16.3.130:6032 2017-09-05 14:47:48 [INFO] Cluster: Saving to disk MySQL Query Rules from peer 172.16.3.130:6032 2017-09-05 14:47:48 [INFO] Cluster: detected a new checksum for mysql_users from peer 172.16.3.132:6032, version 2, epoch 1504619268, checksum 0x7917CD487C11478F . Not syncing yet ... 2017-09-05 14:47:48 [INFO] Cluster: checksum for mysql_users from peer 172.16.3.132:6032 matches with local checksum 0x7917CD487C11478F , we won't sync. 2017-09-05 14:47:49 [INFO] Cluster: detected a new checksum for mysql_users from peer 172.16.3.131:6032, version 2, epoch 1504619268, checksum 0x7917CD487C11478F . Not syncing yet ... 2017-09-05 14:47:49 [INFO] Cluster: checksum for mysql_users from peer 172.16.3.131:6032 matches with local checksum 0x7917CD487C11478F , we won't sync.

What happened?

  • ProxySQL detected a change in the checksum for table mysql_users
  • When diff_check reaches the threshold, the sync process is initialized
  • MySQL Users are fetched from 172.16.3.130:6032
  • MySQL Users are loaded to runtime
  • MySQL Users are also saved to disk because cluster_mysql_users_save_to_disk=true
  • A new checksum is also detected from 172.16.3.132:6032 , but it matches the local one so there is no need to sync
  • A new checksum is also detected from 172.16.3.131:6032 (that is the same proxysql instance), but it matches the local one so there is again no need to sync

Let's now add a new user directly on 172.16.3.131:6032, a different node.
We first verify what user(s) is/are present, then we add a new one:

Admin131> SELECT hostname, port, name, version, FROM_UNIXTIME(epoch) epoch, checksum, FROM_UNIXTIME(changed_at) changed_at, FROM_UNIXTIME(updated_at) updated_at, diff_check, DATETIME('NOW') FROM stats_proxysql_servers_checksums WHERE name='mysql_users' ORDER BY hostname, name; +--------------+------+-------------+---------+---------------------+--------------------+---------------------+---------------------+------------+---------------------+ | hostname | port | name | version | epoch | checksum | changed_at | updated_at | diff_check | DATETIME('NOW') | +--------------+------+-------------+---------+---------------------+--------------------+---------------------+---------------------+------------+---------------------+ | 172.16.3.130 | 6032 | mysql_users | 2 | 2017-09-05 13:47:48 | 0x7917CD487C11478F | 2017-09-05 13:47:48 | 2017-09-05 14:52:32 | 0 | 2017-09-05 14:52:32 | | 172.16.3.131 | 6032 | mysql_users | 2 | 2017-09-05 13:47:48 | 0x7917CD487C11478F | 2017-09-05 13:47:49 | 2017-09-05 14:52:32 | 0 | 2017-09-05 14:52:32 | | 172.16.3.132 | 6032 | mysql_users | 2 | 2017-09-05 13:47:48 | 0x7917CD487C11478F | 2017-09-05 13:47:48 | 2017-09-05 14:52:32 | 0 | 2017-09-05 14:52:32 | +--------------+------+-------------+---------+---------------------+--------------------+---------------------+---------------------+------------+---------------------+ 3 rows in set (0.00 sec) Admin131> SELECT * FROM mysql_users; +----------+-------------------------------------------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+ | username | password | active | use_ssl | default_hostgroup | default_schema | schema_locked | transaction_persistent | fast_forward | backend | frontend | max_connections | +----------+-------------------------------------------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+ | sbtest | *2AFD99E79E4AA23DE141540F4179F64FFB3AC521 | 1 | 0 | 0 | | 0 | 1 | 0 | 0 | 1 | 10000 | | sbtest | *2AFD99E79E4AA23DE141540F4179F64FFB3AC521 | 1 | 0 | 0 | | 0 | 1 | 0 | 1 | 0 | 10000 | +----------+-------------------------------------------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+ 2 rows in set (0.00 sec) Admin131> INSERT INTO mysql_users (username,password) VALUES ('user1','password1'); Query OK, 1 row affected (0.00 sec) Admin131> LOAD MYSQL USERS TO RUNTIME; Query OK, 0 rows affected (0.00 sec) Admin131> SELECT hostname, port, name, version, FROM_UNIXTIME(epoch) epoch, checksum, FROM_UNIXTIME(changed_at) changed_at, FROM_UNIXTIME(updated_at) updated_at, diff_check, DATETIME('NOW') FROM stats_proxysql_servers_checksums WHERE name='mysql_users' ORDER BY hostname, name; +--------------+------+-------------+---------+---------------------+--------------------+---------------------+---------------------+------------+---------------------+ | hostname | port | name | version | epoch | checksum | changed_at | updated_at | diff_check | DATETIME('NOW') | +--------------+------+-------------+---------+---------------------+--------------------+---------------------+---------------------+------------+---------------------+ | 172.16.3.130 | 6032 | mysql_users | 3 | 2017-09-05 14:53:45 | 0x7C60129E5360AC28 | 2017-09-05 14:53:45 | 2017-09-05 14:53:48 | 0 | 2017-09-05 14:53:48 | | 172.16.3.131 | 6032 | mysql_users | 3 | 2017-09-05 14:53:44 | 0x7C60129E5360AC28 | 2017-09-05 14:53:45 | 2017-09-05 14:53:48 | 0 | 2017-09-05 14:53:48 | | 172.16.3.132 | 6032 | mysql_users | 3 | 2017-09-05 14:53:45 | 0x7C60129E5360AC28 | 2017-09-05 14:53:45 | 2017-09-05 14:53:47 | 0 | 2017-09-05 14:53:48 | +--------------+------+-------------+---------+---------------------+--------------------+---------------------+---------------------+------------+---------------------+ 3 rows in set (0.00 sec)

Also in this case, configuration is immediately propagated to all the nodes.

It is interesting to note what happens when running LOAD TO RUNTIME without performing any changes.

Admin131> LOAD MYSQL USERS TO RUNTIME; Query OK, 0 rows affected (0.00 sec) Admin131> SELECT hostname, port, name, version, FROM_UNIXTIME(epoch) epoch, checksum, FROM_UNIXTIME(changed_at) changed_at, FROM_UNIXTIME(updated_at) updated_at, diff_check, DATETIME('NOW') FROM stats_proxysql_servers_checksums WHERE name='mysql_users' ORDER BY hostname, name; +--------------+------+-------------+---------+---------------------+--------------------+---------------------+---------------------+------------+---------------------+ | hostname | port | name | version | epoch | checksum | changed_at | updated_at | diff_check | DATETIME('NOW') | +--------------+------+-------------+---------+---------------------+--------------------+---------------------+---------------------+------------+---------------------+ | 172.16.3.130 | 6032 | mysql_users | 3 | 2017-09-05 14:53:45 | 0x7C60129E5360AC28 | 2017-09-05 14:53:45 | 2017-09-05 14:55:45 | 0 | 2017-09-05 14:55:45 | | 172.16.3.131 | 6032 | mysql_users | 4 | 2017-09-05 14:55:44 | 0x7C60129E5360AC28 | 2017-09-05 14:53:45 | 2017-09-05 14:55:45 | 0 | 2017-09-05 14:55:45 | | 172.16.3.132 | 6032 | mysql_users | 3 | 2017-09-05 14:53:45 | 0x7C60129E5360AC28 | 2017-09-05 14:53:45 | 2017-09-05 14:55:45 | 0 | 2017-09-05 14:55:45 | +--------------+------+-------------+---------+---------------------+--------------------+---------------------+---------------------+------------+---------------------+ 3 rows in set (0.00 sec)

On proxysql131 , version is increased by 1, but checksum doesn't change. This means that no sync is triggered as there is no need for it.
Similarly, the version on other nodes does not change, because they don't re-execute LOAD ... TO RUNTIME.

Create new query rules

We just saw how new users can be created in just one ProxySQL node, and instantly detected by other nodes.
It is now time to show to synchronize MySQL Query Rules.

Let's start by creating new query rules in a node without any specific behaviour.

Admin130> SELECT * FROM mysql_query_rules; Empty set (0.00 sec) Admin130> INSERT INTO mysql_query_rules (schemaname, destination_hostgroup) VALUES ('shard001',1); Query OK, 1 row affected (0.00 sec) Admin130> INSERT INTO mysql_query_rules (schemaname, destination_hostgroup) VALUES ('shard002',1); Query OK, 1 row affected (0.00 sec) Admin130> INSERT INTO mysql_query_rules (schemaname, destination_hostgroup) VALUES ('shard003',1); Query OK, 1 row affected (0.00 sec) Admin130> INSERT INTO mysql_query_rules (schemaname, destination_hostgroup) VALUES ('shard004',1); Query OK, 1 row affected (0.00 sec) Admin130> INSERT INTO mysql_query_rules (schemaname, destination_hostgroup) VALUES ('shard005',2), ('shard006',2), ('shard007',2), ('shard008',2); Query OK, 4 rows affected (0.00 sec) Admin130> UPDATE mysql_query_rules SET active=1, apply=1 WHERE destination_hostgroup IN (1,2); Query OK, 8 rows affected (0.00 sec)

Let's check the current query rules:

Admin130> SELECT rule_id,schemaname,destination_hostgroup,apply FROM mysql_query_rules WHERE active=1; +---------+------------+-----------------------+-------+ | rule_id | schemaname | destination_hostgroup | apply | +---------+------------+-----------------------+-------+ | 1 | shard001 | 1 | 1 | | 2 | shard002 | 1 | 1 | | 3 | shard003 | 1 | 1 | | 4 | shard004 | 1 | 1 | | 5 | shard005 | 2 | 1 | | 6 | shard006 | 2 | 1 | | 7 | shard007 | 2 | 1 | | 8 | shard008 | 2 | 1 | +---------+------------+-----------------------+-------+ 8 rows in set (0.00 sec)

On another node, say proxysql131, there are no rules:

Admin131> SELECT rule_id,schemaname,destination_hostgroup,apply FROM mysql_query_rules WHERE active=1; Empty set (0.00 sec) Admin131> SELECT rule_id,schemaname,destination_hostgroup,apply FROM runtime_mysql_query_rules WHERE active=1; Empty set (0.00 sec)

Now it is time to load the rules into runtime on proxysql130:

Admin130> SELECT name,FROM_UNIXTIME(epoch), checksum FROM runtime_checksums_values WHERE name='mysql_query_rules'; +-------------------+----------------------+--------------------+ | name | FROM_UNIXTIME(epoch) | checksum | +-------------------+----------------------+--------------------+ | mysql_query_rules | 2017-09-05 23:03:53 | 0x0000000000000000 | +-------------------+----------------------+--------------------+ 1 row in set (0.00 sec) Admin130> SELECT hostname, port, name, version, FROM_UNIXTIME(epoch) epoch, checksum, FROM_UNIXTIME(changed_at) changed_at, FROM_UNIXTIME(updated_at) updated_at, diff_check, DATETIME('NOW') FROM stats_proxysql_servers_checksums WHERE name='mysql_query_rules' ORDER BY hostname, name; +--------------+------+-------------------+---------+---------------------+--------------------+---------------------+---------------------+------------+---------------------+ | hostname | port | name | version | epoch | checksum | changed_at | updated_at | diff_check | DATETIME('NOW') | +--------------+------+-------------------+---------+---------------------+--------------------+---------------------+---------------------+------------+---------------------+ | 172.16.3.130 | 6032 | mysql_query_rules | 1 | 2017-09-05 23:03:53 | 0x0000000000000000 | 2017-09-05 23:03:53 | 2017-09-05 23:04:47 | 0 | 2017-09-05 23:04:47 | | 172.16.3.131 | 6032 | mysql_query_rules | 1 | 2017-09-05 23:03:53 | 0x0000000000000000 | 2017-09-05 23:03:54 | 2017-09-05 23:04:47 | 0 | 2017-09-05 23:04:47 | | 172.16.3.132 | 6032 | mysql_query_rules | 1 | 2017-09-05 23:03:54 | 0x0000000000000000 | 2017-09-05 23:03:55 | 2017-09-05 23:04:47 | 0 | 2017-09-05 23:04:47 | +--------------+------+-------------------+---------+---------------------+--------------------+---------------------+---------------------+------------+---------------------+ 3 rows in set (0.00 sec) Admin130> LOAD MYSQL QUERY RULES TO RUNTIME; Query OK, 0 rows affected (0.00 sec) Admin130> SELECT name,FROM_UNIXTIME(epoch), checksum FROM runtime_checksums_values WHERE name='mysql_query_rules'; +-------------------+----------------------+--------------------+ | name | FROM_UNIXTIME(epoch) | checksum | +-------------------+----------------------+--------------------+ | mysql_query_rules | 2017-09-05 23:04:51 | 0xE2F5A21142C799C0 | +-------------------+----------------------+--------------------+ 1 row in set (0.00 sec) Admin130> SELECT hostname, port, name, version, FROM_UNIXTIME(epoch) epoch, checksum, FROM_UNIXTIME(changed_at) changed_at, FROM_UNIXTIME(updated_at) updated_at, diff_check, DATETIME('NOW') FROM stats_proxysql_servers_checksums WHERE name='mysql_query_rules' ORDER BY hostname, name; +--------------+------+-------------------+---------+---------------------+--------------------+---------------------+---------------------+------------+---------------------+ | hostname | port | name | version | epoch | checksum | changed_at | updated_at | diff_check | DATETIME('NOW') | +--------------+------+-------------------+---------+---------------------+--------------------+---------------------+---------------------+------------+---------------------+ | 172.16.3.130 | 6032 | mysql_query_rules | 2 | 2017-09-05 23:04:51 | 0xE2F5A21142C799C0 | 2017-09-05 23:04:51 | 2017-09-05 23:06:22 | 0 | 2017-09-05 23:06:23 | | 172.16.3.131 | 6032 | mysql_query_rules | 2 | 2017-09-05 23:04:52 | 0xE2F5A21142C799C0 | 2017-09-05 23:04:52 | 2017-09-05 23:06:23 | 0 | 2017-09-05 23:06:23 | | 172.16.3.132 | 6032 | mysql_query_rules | 2 | 2017-09-05 23:04:52 | 0xE2F5A21142C799C0 | 2017-09-05 23:04:52 | 2017-09-05 23:06:23 | 0 | 2017-09-05 23:06:23 | +--------------+------+-------------------+---------+---------------------+--------------------+---------------------+---------------------+------------+---------------------+ 3 rows in set (0.00 sec)

As we can see from the above, the information is detected immediately by the other nodes and they immediately sync with the node that has the most recent changes.

Let's check the logs on proxysql131:

2017-09-06 00:04:51 ProxySQL_Cluster.cpp:325:set_checksums(): [INFO] Cluster: detected a new checksum for mysql_query_rules from peer 172.16.3.130:6032, version 2, epoch 1504652691, checksum 0xE2F5A21142C799C0 . Not syncing yet ... 2017-09-06 00:04:52 ProxySQL_Cluster.cpp:473:set_checksums(): [INFO] Cluster: detected a peer 172.16.3.130:6032 with mysql_query_rules version 2, epoch 1504652691, diff_check 3. Own version: 1, epoch: 1504652633. Proceeding with remote sync 2017-09-06 00:04:52 ProxySQL_Cluster.cpp:473:set_checksums(): [INFO] Cluster: detected a peer 172.16.3.130:6032 with mysql_query_rules version 2, epoch 1504652691, diff_check 4. Own version: 1, epoch: 1504652633. Proceeding with remote sync 2017-09-06 00:04:52 ProxySQL_Cluster.cpp:1106:get_peer_to_sync_mysql_query_rules(): [INFO] Cluster: detected peer 172.16.3.130:6032 with mysql_query_rules version 2, epoch 1504652691 2017-09-06 00:04:52 ProxySQL_Cluster.cpp:572:pull_mysql_query_rules_from_peer(): [INFO] Cluster: Fetching MySQL Query Rules from peer 172.16.3.130:6032 started 2017-09-06 00:04:52 ProxySQL_Cluster.cpp:622:pull_mysql_query_rules_from_peer(): [INFO] Cluster: Fetching MySQL Query Rules from peer 172.16.3.130:6032 completed 2017-09-06 00:04:52 ProxySQL_Cluster.cpp:623:pull_mysql_query_rules_from_peer(): [INFO] Cluster: Loading to runtime MySQL Servers from peer 172.16.3.130:6032 2017-09-06 00:04:52 ProxySQL_Cluster.cpp:626:pull_mysql_query_rules_from_peer(): [INFO] Cluster: Saving to disk MySQL Query Rules from peer 172.16.3.130:6032 2017-09-06 00:04:52 ProxySQL_Cluster.cpp:325:set_checksums(): [INFO] Cluster: detected a new checksum for mysql_query_rules from peer 172.16.3.132:6032, version 2, epoch 1504652692, checksum 0xE2F5A21142C799C0 . Not syncing yet ... 2017-09-06 00:04:52 ProxySQL_Cluster.cpp:327:set_checksums(): [INFO] Cluster: checksum for mysql_query_rules from peer 172.16.3.132:6032 matches with local checksum 0xE2F5A21142C799C0 , we won't sync. 2017-09-06 00:04:52 ProxySQL_Cluster.cpp:325:set_checksums(): [INFO] Cluster: detected a new checksum for mysql_query_rules from peer 172.16.3.131:6032, version 2, epoch 1504652692, checksum 0xE2F5A21142C799C0 . Not syncing yet ... 2017-09-06 00:04:52 ProxySQL_Cluster.cpp:327:set_checksums(): [INFO] Cluster: checksum for mysql_query_rules from peer 172.16.3.131:6032 matches with local checksum 0xE2F5A21142C799C0 , we won't sync.

Also in this case, we can see that a change is detected, and after a short period of time all nodes are synced.

Conclusion

In this first blog post about ProxySQL Cluster we showed how multiple ProxySQL instances are aware of each other, and how we can create new users and new query rules to any of the proxysql instances as well as how this information will immediately be propagated to all the other nodes.
Stay tuned for future blog post on ProxySQL Cluster.

This Week in Data with Colin Charles #5: db tech showcase and Percona Live Europe

Join Percona Chief Evangelist Colin Charles as he covers happenings, gives pointers and provides musings on the open source database community.

Percona Live Europe 2017 in Dublin

Have you registered for Percona Live Europe Dublin? We have announced some awesome keynotes, and our sponsor list is growing (and we’re always looking for more!).

There will also be a community dinner (Tuesday, September 26, 2017), so definitely watch the announcement that will be on the blog, and I’m sure on Twitter. Besides being fun, the Lightning Talks will happen during that time.

Releases Link List db tech showcase Tokyo, Japan

The annual db tech showcase Tokyo 2017 took place this week from 5-7 September. It was a fun event as always, with capacity for 800 people per day. The event grows larger each year, and reminds me of the heyday of the MySQL Conference & Expo.

The db tech showcase is a five-parallel-track show, with each talk approximately 50 minutes. The event started with a keynote by Richard Hipp, creator of SQLite (if you were a Percona Live Santa Clara 2017 attendee, you’d have also seen him there). The rest of the event is a mix between Japanese language content and English language content. The sponsor list is lengthy, and if you walk the floor you could collect a lot of datasheets.

One thing I really liked? At some talks, you’d get a clear folder with a contact form as well as the printed slide deck. This is a great way to let the speaker’s company contact you. It’s a common issue that I (and others) speak to large amounts of people and have no idea who’s in the talk. I can only imagine our marketing and sales teams being much happier if they could get access to an attendee list! I wonder if this will work in other markets?

It’s interesting to see that there is a Japan MariaDB User Group now. It’s clear the MySQL user group needs a revival! I saw a talk from Toshiba on performance tests using MariaDB Server, but not with MySQL (a little odd?). The MongoDB content was pretty latent, which is unsurprising because we don’t see a huge MongoDB uptake or community in Japan (or South Korea for that matter).

Will I go back? Absolutely. I’ve been going for a few years, and it’s a great place for people who are crazy about database technology. You really get a spectrum of database presentations, and I expect most people go back with many ideas of what they might want to evaluate for production.

I spoke about the Engineering that goes into Percona Server for MySQL 5.6 and 5.7, with a hint of MongoDB. The slides are in a mix of Japanese and English. The Japanese translation: Percona ServerをMySQL 5.6と5.7用に作るエンジニアリング(そしてMongoDBのヒント).

Upcoming Appearances

Percona’s website keeps track of community events, so check there to see where to listen to a Perconian speak. My upcoming appearances are:

Feedback

Did you try replication-manager last week? Guillaume Lefranc, the lead developer, writes in to talk about the new features such as support for MySQL 5.7, Binlog Flashback, multi-cluster mode and various stability fixes.

I look forward to feedback/tips via e-mail at colin.charles@percona.com or on Twitter @bytebot.

Releasing ProxySQL 1.4.2

Releasing ProxySQL 1.4.2

ProxySQL is a high performance, high availability, protocol aware proxy for MySQL, with a GPL license!

Today I am excited to announce the release of ProxySQL 1.4.2, the second stable release of series 1.4.x .

Binaries are available here .

This release introduce several bug fixes and enhancements compared to previous version:

  • introduced experimental native Cluster solution
  • fixes random failure in handling PREPARE #1169
  • don't use connections from connection pool for fast_forward users #1157
  • reset SQL_MODE and TIME_ZONE when a connection is reset #1160
  • added reload command in init script #1154
  • several improvements to Galera checker #1158 and #981
  • adds option autocommit_false_not_reusable #1144
  • does not return ERR for MYSQL_OPTION_MULTI_STATEMENTS_ON #1122
  • fix compiling issue in FreeBSD
  • reintroduced binaries for Ubuntu12
  • fixed memory leaks in auxiliary threads
  • several performance improvements

The most important news is the clustering solution built-in directly into ProxySQL .
A series of blog post will follow with further details.
Stay tuned!!

Once again, a special thanks to all the people that reports bugs and submit pull request: this makes each version of ProxySQL better than the previous one.
Please report any bugs or feature requests on github issue tracker

Thanks

Linkbench - in-memory, low-concurrency

After a few weeks of sysbench it is now time for more complex workloads and the first one is Linkbench with a cached database and low-concurrency. I prefer to start with cached & low-concurrency configurations before trying IO-bound & high-concurrency.

tl;dr:
  • InnoDB from MySQL 5.6 had the best throughput
  • CPU efficiency is similar for MyRocks and InnoDB
  • There is a CPU regression from MySQL 5.6 to 5.7 to 8.x
  • Write efficiency was similar for all engines on the load test but much better for MyRocks and TokuDB on the transaction test.

Configuration

I used my Linkbench repo and helper scripts to run sysbench with maxid1=10M, loaders=1 and requestors=1 so there will be 2 concurrent connections doing the load and 1 connection running transactions after the load finishes. My linkbench repo has a recent commit that changes the Linkbench workload and results with that change are labeled new config while results without the change are labeled old config. I share both. The test pattern is 1) load and 2) transactions. The transactions were run in 12 1-hour loops and I share results from the last hour. The test server has 48 HW threads, fast SSD and 256gb of RAM.

Tests were run for MyRocks, InnoDB from upstream MySQL and TokuDB. The binlog was enabled but sync on commit was disabled for the binlog and database log. All engines used jemalloc. Mostly accurate my.cnf files are here but the database cache was made large enough to cache the ~10gb database.
  • MyRocks was compiled on August 15 with git hash 0d76ae. Compression was not used.
  • InnoDB was from upstream 5.6.35, 5.7.17 and 8.0.2.  The performance schema was enabled.
  • TokuDB was from Percona Server 5.7.17. Compression was not used.

Load Results

All of the data is here and note that I did not adjust metrics computed from iostat to account for iostat overstating bytes written by up to 2X for RocksDB. The table below has a subset of the results for the new config. The results for the old config are similar.
  • InnoDB 5.6 has the best insert rate but there is a regression from InnoDB in 5.6 to 5.7 to 8.0.2
  • Write efficiency (wkb/i) is similar for all engines
  • CPU efficiency (Mcpu/i) is similar for MyRocks and InnoDB

ips     wkb/i   Mcpu/i  size    wMB/s   cpu     engine  54283  1.60     83     14       86.7    4.5    myrocks  64402  1.02     72     16       65.6    4.6    inno5635  56414  1.03     77     16       58.2    4.3    inno5717  42954  1.02     97     16       45.1    4.2    inno802  21611  1.42    179     14       30.7    3.9    toku5717
legend: * ips - inserts/second * wkb/i - iostat KB written per insert * Mcpu/i - normalized CPU time per insert * wMB/s - iostat write MB/s, average * size - database size in GB at test end * cpu - average value of vmstat us + sy columns

Transaction Results
These are results from the 12th 1-hour loop of the transaction phase. All of the data is here and note that I did not adjust metrics computed from iostat to account for iostat overstating bytes written by up to 2X for RocksDB. The table below has a subset of the results for the new config. I will explain them. The results for the old config are similar.
  • InnoDB 5.6 has the best transaction rate but there is a regression from 5.6 to 5.7 to 8.0.2
  • Write efficiency (wkb/t) is much better for TokuDB and MyRocks than for InnoDB
  • CPU efficiency (Mcpu/t) is similar for MyRocks and InnoDB
  • Response times are similar between MyRocks and InnoDB

tps     wkb/t   Mcpu/t  size  un    gn    ul    gl    wMB/s  engine 5489    0.78     642    15    0.3   0.1   0.5   0.5    4.3   myrocks 7239    5.15     524    26    0.3   0.1   0.4   0.2   37.3   inno5635 6463    5.17     580    26    0.3   0.1   0.5   0.3   33.4   inno5717 5855    5.25     623    25    0.3   0.1   0.6   0.3   30.7   inno802 3333    0.08    1043    18    0.6   0.2   1.0   0.8   10.0   toku5717
legend: * tps - transactions/second * wkb/t - iostat KB written per transaction * Mcpu/t - normalized CPU time per transaction * size - database size in GB at test end * un, gn, ul, gl - 99th percentile response time in millisecs for UpdateNode,                    GetNode, UpdateList and GetLinkedList transactions * wMB/s - iostat write MB/s, average

Charts
Charts for load and transaction throughput using the ips and tps columns from the tables above.

Pages