Planet MySQL

Webinar Wed 7/18: MariaDB 10.3 vs. MySQL 8.0

Please join Percona’s Chief Evangelist, Colin Charles as he presents as he presents MariaDB 10.3 vs. MySQL 8.0 on Wednesday, July 18th, 2018, at 9:00 AM PDT (UTC-7) / 12:00 PM EDT (UTC-4).

Register Now

 

Technical considerations

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

MariaDB 10.3 is on the path of gradually diverging from MySQL 8.0. One obvious example is the internal data dictionary currently under development for MySQL 8.0. 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.

Non-technical considerations

There are also non-technical differences between MySQL 8.0 and MariaDB 10.3, 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 10.3 and MySQL 8.0 and help answer some of the common questions our Database Performance Experts get about the two databases.

Register Now

The post Webinar Wed 7/18: MariaDB 10.3 vs. MySQL 8.0 appeared first on Percona Database Performance Blog.

Why Consumer SSD Reviews are Useless for Database Performance Use Case

If you’re reading consumer SSD reviews and using them to estimate SSD performance under database workloads, you’d better stop. Databases are not your typical consumer applications and they do not use IO in the same way.

Let’s look, for example, at this excellent AnandTech review of Samsung 960 Pro –  a consumer NVMe device that I happen to have in my test lab.

The summary table is actually great, showing the performance both at Queue Depth 1 (single threaded) as well as Queue Depth 32 – a pretty heavy concurrent load.

Even at QD1 we see 50K (4K) writes per second, which should be enough for pretty serious database workloads.

In reality, though, you might be in for some disappointing surprises. While “normal” buffered IO is indeed quite fast, this drive really hates fsync() calls, with a single thread fsync() latency of 3.5ms or roughly 300 fsync/sec. That’s just slightly more than your old school spinning drive.

Why is fsync() performance critical for databases?

To achieve Durability—the letter “D” of ACID—databases tend to rely on a write ahead log (WAL) which is sequentially written. The WAL must be synced to disk on every transaction commit using fsync() or similar measures, such as opening file with O_SYNC flag. These tend to have similar performance implications.

Other database operations use fsync() too, but writing WAL is where it usually hurts the most.    

In a fully durable configuration MySQL tends to be impacted even more by poor fsync() performance. It may need to perform as many as three fsync operations per transaction commit. Group commit reduces the impact on throughput but transaction latency will still be severely impacted

Want more bad news? If the fsync() performance is phenomenal on your consumer SSD it indeed might be too good to be true. Over the years, some consumer SSDs “faked” fsync and accepted possible data loss in the case of power failure. This might not be a big deal if you only use them for testing but it is a showstopper for any real use.

Want to know more about your drive’s fsync() performance?  You can use these sysbench commands:

sysbench fileio --time=60 --file-num=1 --file-extra-flags= --file-total-size=4096 --file-block-size=4096 --file-fsync-all=on --file-test-mode=rndwr --file-fsync-freq=0 --file-fsync-end=0  --threads=1 --percentile=99 prepare sysbench fileio --time=60 --file-num=1 --file-extra-flags= --file-total-size=4096 --file-block-size=4096 --file-fsync-all=on --file-test-mode=rndwr --file-fsync-freq=0 --file-fsync-end=0  --threads=1 --percentile=99 run | grep "avg:"

You can also use ioping as described in this blog post

I wish that manufacturers’ tech specifications described fsync latency, along with a clear statement as to whether the drive guarantees no loss of data on power failure. Likewise, I wish folk doing storage reviews could include these in their research.

Interested in fsync() performance for variety of devices?  Yves Trudeau wrote an excellent blog post about fsync() performance on various storage devices  a few months ago.

Other technical resources

Principal Support Escalation Specialist Sveta Smirnova presents Troubleshooting MySQL Concurrency Issues with Load Testing Tools. 

You can download a three part series of eBooks by Principal Consultant Alexander Rubin and me on MySQL Performance.

The post Why Consumer SSD Reviews are Useless for Database Performance Use Case appeared first on Percona Database Performance Blog.

Mastering Continuent Clustering Series: Connection Handling in the Tungsten Connector

In this blog post, we talk about how query connections are handled by the Tungsten Connector, especially read-only connections.

There are multiple ways to configure session handling in the Connector. The three main modes are Bridge, Proxy/Direct and Proxy/SmartScale.

In Bridge mode, the data source to connect to is chosen ONCE for the lifetime of the connection, which means that the selection of a different node will only happen if a NEW connection is opened through the Connector.

So if your application reuses its connections, all traffic sent through that session will continue to land on the selected read slave, i.e., when using connection pooling.

http://docs.continuent.com/tungsten-clustering-6.0/connector-bridgemode.html

The key difference is in how the slave latency checking is handled:

  • In Bridge mode, the latency is checked at connection time, and you will stick to the slave for the connection lifetime (which will be ended if the slave goes offline).
  • In Proxy modes, the latency is re-evaluated before each query, which can bring the connection to another slave if the latency becomes too high during the life of the connection.

If you have long-lasting, read-only connections that should not read from stale slaves, then use a Proxy mode.

If your connection lifetime is short (i.e make/break – one transaction then disconnect), or your application is not sensitive to reasonably outdated data for reads, then use Bridge mode and its optional read-only port.

In future articles, we will continue to cover more advanced subjects of interest!

Questions? Contact us.

We use Amazon RDS for MySQL, Do we still need a MySQL DBA ?

Recently one of our prospective customers asked this question, “We use Amazon RDS for MySQL, Do we still need a MySQL Database Architect / DBA / Engineer? ”  The quick answer is – Yes,  you still need a MySQL DBA to solve problems which Amazon RDS or Amazon Aurora for MySQL cannot solve, We spend almost 6 hours in meeting, helping the customer to understand how cloud Database Infrastructure and Database as a Service (DBaaS) simplifies Database Infrastructure provisioning and rolling-out scalability and high availability solutions faster across multiple locations, This post is about how we think the corporations can benefit from Amazon RDS / Aurora and build highly responsive web-scale database infrastructure, we have also included the solutions which simplifies maximum ROI for the customers from their cloud database infrastructure, We as an full-service database infrastructure solutions provider strongly believe, Amazon RDS/Aurora roll-out has to be planned very well and uncontrolled database infrastructure operations on cloud does occasionally more harm than good, Thank you for taking time reading this post and positive / constructive comments are welcome:

What are the problems / challenges solved by Amazon RDS / Aurora ? 

  • Infrastructure independent – When you usually procure a server, You get CPU, memory and storage devices bundled but when you are deploying your Database infrastructure in Amazon RDS / Aurora, All the required system resources are available distributed (split apart), This means you are building MySQL infrastructure from the day one optimal, scalable, highly available, reliable and secured.
  • Capacity planning and sizing – You are not spending days / weeks on logistics of  Capacity planning and sizing .. When you need infrastructure to grow / scale-out, You have it available on-demand . This makes faster deployment possible !
  • Built for planet-scale – Your database infrastructure is highly scalable (both vertically and horizontally) on Amazon RDS / Aurora, You don’t have to spend time / resources in planning scalability solutions of your MySQL infrastructure.
  • Fully Automated Backup – Amazon RDS takes care of your MySQL backup, You don’t have to spend time in researching and deploying robust backup solutions, So more reliable DR (Maximum Reliability of MySQL infrastructure).
  • Highly Available MySQL infrastructure – You can build synchronous / asynchronous replication solutions faster, across multiple geographies.. This means, You have multi-location MySQL replication for availability and scaling-out reads available on demand. Amazon RDS auto-failover solution provides maximum availability of your MySQL infrastructure.
  •  Self healing DBaaS – Amazon Aurora is designed to automatically detect database crashes and restart without the need for crash recovery or to rebuild the database cache. If the entire instance fails, Amazon Aurora will automatically fail over to one of up to 15 read replicas.

What are the problems your DBA(s) can solve ?

Most of the Sr. MySQL DBA(s) can help you in addressing the following concerns / requirements, We have intentionally strikethroughed the activities which Amazon RDS / Aurora can do for you:

  • Logical and physical schema development (designing tables, indexes, datatypes etc.) and optimal SQL engineering.
  • Performance diagnostics / forensics, audit, recommendations and tuning.
  • Benchmarking MySQL for performance, scalability, high availability and capacity planning / sizing (uncontrolled Amazon RDS instances are suicidal !).
  • MySQL index performance optimization.
  • MySQL database archiving and partitioning, Bigger Amazon RDS / Aurora instances are expensive and operationally exhaustive, so plan how much data you want to retain in the primary database instance (Amazon RDS) and where / how do you want to archive the old data.
  • Continuous(24*7) MySQL performance monitoring to troubleshoot proactively, Issues like MySQL deadlocks needs to be fixed before causing any major impact to the business.
  • Installation and configuration of MySQL / MariaDB. 
  • MySQL backup .
  • MySQL scale-out / replication , sharding, clustering (High Availability solutions).
  • MySQL / MariaDB upgrades. 
  • MySQL / MariaDB bug fixing.
  • MySQL / MariaDB security. 

Can you remove DBA function from your business ? 

No, We don’t recommend any of our customers (past, present and prospective) to remove DBA function from their business, because Amazon RDS / Aurora is actually solving your operational challenges in the database infrastructure, but you still need MySQL / MariaDB Expertise (outsourced or in-house) to build an optimal and scalable (systems not designed for performance and scalability cannot be fixed by heavy infrastructure investments) database infrastructure operations.

How do we like to conclude this post ?   

Amazon RDS/Aurora is built to enable your MySQL DBA(s) and improve their productivity, They address operational challenges in database infrastructure management, So combining Sr. DBA expertise with Amazon RDS will benefit your business in building planet-scale MySQL operations cost efficiently. Definitely one cannot replace other, Amazon RDS and seasoned DBA(s) co-exist for good.

The post We use Amazon RDS for MySQL, Do we still need a MySQL DBA ? appeared first on MySQL Consulting, Support and Remote DBA Services.

ClusterControl Release 1.6.2: New Backup Management and Security Features for MySQL & PostgreSQL

We are excited to announce the 1.6.2 release of ClusterControl - the all-inclusive database management system that lets you easily automate and manage highly available open source databases in any environment: on-premise or in the cloud.

ClusterControl 1.6.2 introduces new exciting Backup Management as well as Security & Compliance features for MySQL & PostgreSQL, support for MongoDB v 3.6 … and more!

Release Highlights Related resources  ClusterControl ChangeLog  Download ClusterControl  Learn More About ClusterControl Backup Management
  • Continuous Archiving and Point-in-Time Recovery (PITR) for PostgreSQL
  • Rebuild a node from a backup with MySQL Galera clusters to avoid SST
Security & Compliance
  • New, consolidated Security section
Additional Highlights
  • Support for MongoDB v 3.6

View the ClusterControl ChangeLog for all the details!

ClusterControl Single Console for Your Entire Database Infrastructure Find out what else is new in ClusterControl Install ClusterControl for FREE View Release Details and Resources Release Details Backup Management

One of the issues with MySQL and PostgreSQL is that there aren’t really any out-of-the-box tools for users to simply (in the GUI) pick up restore-time: certain operations need to be performed to do that, such as finding the full backup, restore it and apply any changes manually that happened after the backup was taken.

ClusterControl provides a single process to restore data to point in time with no extra actions needed.

With the same system, users can verify their backups (in the case of MySQL for instance, ClusterControl will do the installation, set up the cluster, do a restore and, if the backup is sound, make it valid - which, as one can imagine, represents a lot of steps).

With ClusterControl, users can not only go back to a point in time, but also pick up the exact transaction that happened; and, with surgical precision, restore their data before disaster really strikes.

New for PostgreSQL

Continuous Archiving and Point-in-Time Recovery (PITR) for PostgreSQL: ClusterControl automates that process now and enables continuous WAL archiving as well as a PITR with backups.

New for MySQL Galera Cluster

Rebuild a node from a backup with MySQL Galera clusters to avoid SST: ClusterControl reduces the time it takes to recover a node by avoiding streaming a full dataset over the network from another node.

Security & Compliance

The new Security section in ClusterControl lets users easily check which security features they have enabled (or disabled) for their clusters, thus simplifying the process of taking the relevant security measures for their setups.

Additional New Functionalities

View the ClusterControl ChangeLog for all the details!

 

Download ClusterControl today!

Happy Clustering!

Tags:  clustercontrol PostgreSQL MySQL MongoDB pitr backup security point in time recovery

When Should I Use Amazon Aurora and When Should I use RDS MySQL?

Now that Database-as-a-service (DBaaS) is in high demand, there is one question regarding AWS services that cannot always be answered easily : When should I use Aurora and when RDS MySQL?

DBaaS cloud services allow users to use databases without configuring physical hardware and infrastructure, and without installing software. I’m not sure if there is a straightforward answer, but when trying to find out which solution best fits an organization there are multiple factors that should be taken into consideration. These may be performance, high availability, operational cost, management, capacity planning, scalability, security, monitoring, etc.

There are also cases where although the workload and operational needs seem to best fit to one solution, there are other limiting factors which may be blockers (or at least need special handling).

In this blog post, I will try to provide some general rules of thumb but let’s first try to give a short description of these products.

What we should really compare is the MySQL and Aurora database engines provided by Amazon RDS.

An introduction to Amazon RDS

Amazon Relational Database Service (Amazon RDS) is a hosted database service which provides multiple database products to choose from, including Aurora, PostgreSQL, MySQL, MariaDB, Oracle, and Microsoft SQL Server. We will focus on MySQL and Aurora.

With regards to systems administration, both solutions are time-saving. You get an environment ready to deploy your application and if there are no dedicated DBAs, RDS gives you great flexibility for operations like upgrades or backups. For both products, Amazon applies required updates and the latest patches without any downtime. You can define maintenance windows and automated patching (if enabled) will occur within them. Data is continuously backed up to S3 in real time, with no performance impact. This eliminates the need for backup windows and other, complex or not, scripted procedures. Although this sounds great, the risk of vendor lock-in and the challenges of enforced updates and client-side optimizations are still there.

So, Aurora or RDS MySQL?

Amazon Aurora is a relational, proprietary, closed-source database engine, with all that that implies.

RDS MySQL is 5.5, 5.6 and 5.7 compatible and offers the option to select among minor releases. While RDS MySQL supports multiple storage engines with varying capabilities, not all of them are optimized for crash recovery and data durability. Until recently, it was a limitation that Aurora was only compatible with MySQL 5.6 but it’s now compatible with both 5.6 and 5.7 too.

So, in most cases, no significant application changes are required for either product. Keep in mind that certain MySQL features like the MyISAM storage engine are not available with Amazon Aurora. Migration to RDS can be performed using Percona XtraBackup.

For RDS products shell access to the underlying operating system is disabled and access to MySQL user accounts with the “SUPER” privilege isn’t allowed. To configure MySQL variables or manage users, Amazon RDS provides specific parameter groups, APIs and other special system procedures which be used. If you need to enable remote access this article will help you do so https://www.percona.com/blog/2018/05/08/how-to-enable-amazon-rds-remote-access/

Performance considerations

Although Amazon RDS uses SSDs to achieve better IO throughput for all its database services, Amazon claims that the Aurora is able to achieve a 5x performance boost than standard MySQL and provides reliability out of the box. In general, Aurora seems to be faster, but not always.

For example, due to the need to disable the InnoDB change buffer for Aurora (this is one of the keys for the distributed storage engine), and that updates to secondary indexes must be write through, there is a big performance penalty in workloads where heavy writes that update secondary indexes are performed. This is because of the way MySQL relies on the change buffer to defer and merge secondary index updates. If your application performs a high rate of updates against tables with secondary indexes, Aurora performance may be poor. In any case, you should always keep in mind that performance depends on schema design. Before taking the decision to migrate, performance should be evaluated against an application specific workload. Doing extensive benchmarks will be the subject of a future blog post.

Capacity Planning

Talking about underlying storage, another important thing to take into consideration is that with Aurora there is no need for capacity planning. Aurora storage will automatically grow, from the minimum of 10 GB up to 64 TiB, in 10 GB increments, with no impact on database performance. The table size limit is only constrained by the size of the Aurora cluster volume, which has a maximum of 64 tebibytes (TiB). As a result, the maximum table size for a table in an Aurora database is 64 TiB. For RDS MySQL, the maximum provisioned storage limit constrains the size of a table to a maximum size of 16 TB when using InnoDB file-per-table tablespaces.

Replication

Replication is a really powerful feature of MySQL (like) products. With Aurora, you can provision up to fifteen replicas compared to just five in RDS MySQL. All Aurora replicas share the same underlying volume with the primary instance and this means that replication can be performed in milliseconds as updates made by the primary instance are instantly available to all Aurora replicas. Failover is automatic with no data loss on Amazon Aurora whereas the replicas failover priority can be set.

An explanatory description of Amazon Aurora’s architecture can be found in Vadim’s post written a couple of years ago https://www.percona.com/blog/2015/11/16/amazon-aurora-looking-deeper/

The architecture used and the way that replication works on both products shows a really significant difference between them. Aurora is a High Availablity (HA) solution where you only need to attach a reader and this automatically becomes Multi-AZ available. Aurora replicates data to six storage nodes in Multi-AZs to withstand the loss of an entire AZ (Availability Zone) or two storage nodes without any availability impact to the client’s applications.

On the other hand, RDS MySQL allows only up to five replicas and the replication process is slower than Aurora. Failover is a manual process and may result in last-minute data loss. RDS for MySQL is not an HA solution, so you have to mark the master as Multi-AZ and attach the endpoints.

Monitoring

Both products can be monitored with a variety of monitoring tools. You can enable automated monitoring and you can define the log types to publish to Amazon CloudWatch. Percona Monitoring and Management (PMM) can also be used to gather metrics.

Be aware that for Aurora there is a limitation for the T2 instances such that Performance Schema can cause the host to run out of memory if enabled.

Costs

Aurora instances will cost you ~20% more than RDS MySQL. If you create Aurora read replicas then the cost of your Aurora cluster will double. Aurora is only available on certain RDS instance sizes. Instances pricing details can be found here and here.

Storage pricing may be a bit tricky. Keep in mind that pricing for Aurora differs to that for RDS MySQL. For RDS MySQL you have to select the type and size for the EBS volume, and you have to be sure that provisioned EBS IOPs can be supported by your instance type as EBS IOPs are restricted by the instance type capabilities. Unless you watch for this, you may end up having EBS IOPs that cannot be really used by your instance.

For Aurora, IOPs are only limited by the instance type. This means that if you want to increase IOPs performance on Aurora you should proceed with an instance type upgrade. In any case, Amazon will charge you based on the dataset size and the requests per second.

That said, although for Aurora you pay only for the data you really use in 10GB increments if you want high performance you have to select the correct instance. For Aurora, regardless of the instance type, you get billed $0.10 per GB-month and $0.20 per 1 million requests so if you need high performance the cost maybe even more than RDS MySQL. For RDS MySQL storage costs are based on the EBS type and size.

Percona provides support for RDS services and you might be interested in these cases studies:

When a more fully customized solution is required, most of our customers usually prefer the use of AWS EC2 instances supported by our managed services offering.

TL;DR
  • If you are looking for a native HA solution then you should use Aurora
  • For a read-intensive workload within an HA environment, Aurora is a perfect match. Combined with ProxySQL for RDS you can get a high flexibility
  • Aurora performance is great but is not as much as expected for write-intensive workloads when secondary indexes exist. In any case, you should benchmark both RDS MySQL and Aurora before taking the decision to migrate.  Performance depends much on workload and schema design
  • By choosing Amazon Aurora you are fully dependent on Amazon for bug fixes or upgrades
  • If you need to use MySQL plugins you should use RDS MySQL
  • Aurora only supports InnoDB. If you need other engines i.e. MyISAM, RDS MySQL is the only option
  • With RDS MySQL you can use specific MySQL releases
  • Aurora is not included in the AWS free-tier and costs a bit more than RDS MySQL. If you only need a managed solution to deploy services in a less expensive way and out of the box availability is not your main concern, RDS MySQL is what you need
  • If for any reason Performance Schema must be ON, you should not enable this on Amazon Aurora MySQL T2 instances. With the Performance Schema enabled, the T2 instance may run out of memory
  • For both products, you should carefully examine the known issues and limitations listed here https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/MySQL.KnownIssuesAndLimitations.html and here https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Aurora.AuroraMySQL.html

The post When Should I Use Amazon Aurora and When Should I use RDS MySQL? appeared first on Percona Database Performance Blog.

The Perfect Server - Ubuntu 18.04 (Nginx, MySQL, PHP, Postfix, BIND, Dovecot, Pure-FTPD and ISPConfig 3.1)

This tutorial shows the steps to install an Ubuntu 18.04 (Bionic Beaver) server with Nginx, PHP, MariaDB, Postfix, pure-ftpd, BIND, Dovecot and ISPConfig 3.1. ISPConfig is a web hosting control panel that allows you to configure the installed services through a web browser. This setup provides a full hosting server with web, email (inc. spam and antivirus filter), Database, FTP and DNS services.

Mastering Continuent Clustering Series: Global Clustering with Active/Active Meshed Replication

Did you know that Continuent Clustering supports having clusters at multiple sites world-wide with active-active replication meshing them together?

Not only that, but we support a flexible hybrid model that allows for a blended architecture using any combination of node types. So mix-and-match your highly available database layer on bare metal, Amazon Web Services (AWS), Azure, Google Cloud, VMware, etc.

The possibilities are endless, as is the business value. This strong topology allows you to have all the benefits of high availability with local reads and writes, while spreading that data globally to be accessible in all regions. Latency is limited only by the WAN link and the speed of the target node.

This aligns perfectly with the distributed Software-as-a-Service (SaaS) model where customers and data span the globe. Applications have access to ALL the data in ALL regions while having the ability to scale reads across all available slave nodes, giving you the confidence that operations will continue in the face of disruption.

Continuent Clustering incorporates the asynchronous Tungsten Replicator to distribute events from the write master to all read slaves. The loosely-coupled nature of this method allows for resilience in the face of uncertain global network communications and speeds. The Replicator intelligently picks up where it left off in the event of a network outage. Not only that, performance is enhanced by the asynchronous nature of the replication because the master does not need to wait for any slave to acknowledge the write.

Overall, Continuent Clustering is the most flexible, performant global database layer available today – use it underlying your SaaS offering as a strong base upon which to grow your worldwide business!

Click here for more online information on Continuent Clustering solutions

Want to learn more or run a POC? Contact us.

InnoDB Cluster in a Nutshell: Part 2 MySQL Router

MySQL InnoDB Cluster is an Oracle High Availability solution that can be easily installed over MySQL to provide high availability with multi-master capabilities and automatic failover. In the previous post we presented the first component of InnoDB Cluster, group replication. Now we will go through the second component, MySQL Router.  We will address MySQL Shell in a final instalment of this three-part series. By then, you should have a good overview of the features offeed by MySQL InnoDB Cluster.

MySQL Router

This component is responsible for distributing the traffic between members of the cluster. It is a proxy-like solution to hide cluster topology from applications, so applications don’t need to know which member of a cluster is the primary node and which are secondaries.

The tool is capable of performing read/write splitting by exposing different interfaces. A common setup is to have one read-write interface and one read-only interface. This is default behavior that also exposes 2 similar interfaces to use x-protocol (used for json and document store).

The read and write split is done using a concept of roles: Primary for writes and Secondary for read-only. This is analogous to how members of cluster are named. Additionally, each interface is exposed via a TCP port so applications only need to know the IP:port combination used for writes and the one used for reads. Then, MySQL Router will take care of connections to cluster members depending on the type of traffic to server.

MySQL Router is a very simple tool, maybe too simple as it is a layer four load balance and lacks some of the advanced features that some of it’s competitors have (e.g.. ProxySQL).

Here is a short list of the most important features of MySQL Router:

  • As mentioned, read and write split based on roles.
  • Load balancing both for reads and writes use different algorithms.
  • Configuration is stored in a configuration test file.
  • Automatically detects cluster topology by connecting and retrieving information, based on this information the router configures itself with default rules.
  • Automatically detects failing nodes and redirects traffic accordingly.
Algorithms used for routing

An important thing to mention is the routing_strategy algorithms that are available, as they are assigned by default depending on the routing mode:

  • For PRIMARY mode (i.e. writer node – or nodes): uses the first-available algorithm that picks the first writer node from a list of writes and in case of failure moves to the next in the list. If the failing node comes back to life, it’s automatically added to the end of the list. When no writers are available then write routing is stopped
  • For read-only mode (i.e. read nodes): uses the round-robin algorithm between servers listed in the destinations variable. This mode splits read traffic between all servers in an even manner.

Additional routing_strategy algorithms :

  • next-available: similar to first-available but in this case a failing node is marked as crashed and can’t get back into the rotation.
  • round-robin-with-fallback: same as round-robin but it includes the ability in this case of using servers from the primary list (writers) to distribute the read traffic.
A sample configuration

For performance purposes it’s recommended to setup MySQL Router in the same place as the application, considering an instance per application server.

Here you can see a sample configuration file auto-generated by --bootstrap functionality:

$ cat /etc/mysqlrouter/mysqlrouter.conf # File automatically generated during MySQL Router bootstrap [DEFAULT] name=system user=mysqlrouter keyring_path=/var/lib/mysqlrouter/keyring master_key_path=/etc/mysqlrouter/mysqlrouter.key connect_timeout=30 read_timeout=30 [logger] level = INFO [metadata_cache:percona] router_id=4 bootstrap_server_addresses=mysql://192.168.70.4:3306,mysql://192.168.70.3:3306,mysql://192.168.70.2:3306 user=mysql_router4_56igr8pxhz0m metadata_cluster=percona ttl=5 [routing:percona_default_rw] bind_address=0.0.0.0 bind_port=6446 destinations=metadata-cache://percona/default?role=PRIMARY routing_strategy=round-robin protocol=classic [routing:percona_default_ro] bind_address=0.0.0.0 bind_port=6447 destinations=metadata-cache://percona/default?role=SECONDARY routing_strategy=round-robin protocol=classic [routing:percona_default_x_rw] bind_address=0.0.0.0 bind_port=64460 destinations=metadata-cache://percona/default?role=PRIMARY routing_strategy=round-robin protocol=x [routing:percona_default_x_ro] bind_address=0.0.0.0 bind_port=64470 destinations=metadata-cache://percona/default?role=SECONDARY routing_strategy=round-robin protocol=x

We are almost done now, only one post left. The final post is about our third component MySQL Shell, so please keep reading.

The post InnoDB Cluster in a Nutshell: Part 2 MySQL Router appeared first on Percona Database Performance Blog.

How to Control Replication Failover for MySQL and MariaDB

Automated failover is pretty much a must have for many applications - uptime is taken for granted. It’s quite hard to accept that an application is down for 20 or 30 minutes because someone has to be paged to log in and investigate the situation before taking action.

In the real world, replication setups tend to grow over time to become complex, sometimes messy. And there are constraints. For instance, not every node in a setup makes a good master candidate. Maybe the hardware differs and some of the replicas have less powerful hardware as they are dedicated to handle some specific types of the workload? Maybe you are in the middle of migration to a new MySQL version and some of the slaves have already been upgraded? You’d rather not have a master in more recent version replicating to old replicas, as this can break replication. If you have two datacenters, one active and one for disaster recovery, you may prefer to pick master candidates only in the active datacenter, to keep the master close to the application hosts. Those are just example situations, where you may find yourself in need of manual intervention during the failover process. Luckily, many failover tools have an option to take control of the process by using whitelists and blacklists. In this blog post, we’d like to show you some examples how you can influence ClusterControl’s algorithm for picking master candidates.

Whitelist and Blacklist Configuration

ClusterControl gives you an option to define both whitelist and blacklist of replicas. A whitelist is a list of replicas which are intended to become master candidates. If none of them are available (either because they are down, or there are errant transactions, or there are other obstacles that prevent any of them from being promoted), failover will not be performed. In this way, you can define which hosts are available to become a master candidate. Blacklists, on the other hand, define which replicas are not suitable to become a master candidate.

Both of those lists can be defined in the cmon configuration file for a given cluster. For example, if your cluster has id of ‘1’, you want to edit ‘/etc/cmon.d/cmon_1.cnf’. For whitelist you will use ‘replication_failover_whitelist’ variable, for blacklist it will be a ‘replication_failover_blacklist’. Both accept a comma separated list of ‘host:port’.

Let’s consider the following replication setup. We have an active master (10.0.0.141) which has two replicas (10.0.0.142 and 10.0.0.143), both act as intermediate masters and have one replica each (10.0.0.144 and 10.0.0.147). We also have a standby master in a separate datacenter (10.0.0.145) which has a replica (10.0.0.146). Those hosts are intended to be used in case of a disaster. Replicas 10.0.0.146 and 10.0.0.147 act as backup hosts. See below screenshot.

Given that the second datacenter is only intended for disaster recovery, we don’t want any of those hosts to be promoted as master. In the worst case scenario, we will take manual action. The second datacenter’s infrastructure is not scaled to the size of the production datacenter (there are three replicas less in the DR datacenter), so manual actions are needed anyway before we can promote a host in the DR datacenter. We also would not like for a backup replica (10.0.0.147) to be promoted. Neither we want a third replica in the chain to be picked up as a master (even though it could be done with GTID).

Whitelist Configuration

We can configure either whitelist or a blacklist to make sure that failover will be handled to our liking. In this particular setup, using whitelist may be more suitable - we will define which hosts can be used for failover and if someone adds a new host to the setup, it will not be taken under consideration as master candidate until someone will manually decide it is ok to use it and add it to the whitelist. If we used blacklist, adding a new replica somewhere in the chain could mean that such replica could theoretically be automatically used for failover unless someone explicitly says it cannot be used. Let’s stay on the safe side and define a whitelist in our cluster configuration file (in this case it is /etc/cmon.d/cmon_1.cnf as we have just one cluster):

replication_failover_whitelist=10.0.0.141:3306,10.0.0.142:3306,10.0.0.143:3306

We have to make sure that the cmon process has been restarted to apply changes:

service cmon restart

Let’s assume our master has crashed and cannot be reached by ClusterControl. A failover job will be initiated:

The topology will look like below:

As you can see, the old master is disabled and ClusterControl will not attempt to automatically recover it. It is up to the user to check what has happened, copy any data which may not have been replicated to the master candidate and rebuild the old master:

Then it’s a matter of a few topology changes and we can bring the topology to the original state, just replacing 10.0.0.141 with 10.0.0.142:

Blacklist Configuration

Now we are going to see how the blacklist works. We mentioned that, in our example, it may not be the best option but we will try it for the sake of illustration. We will blacklist every host except 10.0.0.141, 10.0.0.142 and 10.0.0.143 as those are the hosts we want to see as master candidates.

replication_failover_blacklist=10.0.0.145:3306,10.0.0.146:3306,10.0.0.144:3306,10.0.0.147:3306

We will also restart the cmon process to apply configuration changes:

service cmon restart

The failover process is similar. Again, once the master crash is detected, ClusterControl will start a failover job.

ClusterControl Single Console for Your Entire Database Infrastructure Find out what else is new in ClusterControl Install ClusterControl for FREE When a Replica May Not be a Good Master Candidate

In this short section, we would like to discuss in more details some of the cases in which you may not want to promote a given replica to become a new master. Hopefully, this will give you some ideas of the cases where you may need to consider inducing more manual control of the failover process.

Different MySQL Version

First, if your replica uses a different MySQL version than the master, it is not a good idea to promote it. Generally speaking, a more recent version is always a no-go as replication from the new to the old MySQL version is not supported and may not work correctly. This is relevant mostly to major versions (for example, 8.0 replicating to 5.7) but the good practice is to avoid this setup altogether, even if we are talking about small version differences (5.7.x+1 -> 5.7.x). Replicating from lower to higher/newer version is supported as it is a must for the upgrade process, but still, you would rather want to avoid this (for example, if your master is on 5.7.x+1 you would rather not replace it with a replica on 5.7.x).

Different Roles

You may assign different roles to your replicas. You can pick one of them to be available for developers to test their queries on a production dataset. You may use one of them for OLAP workload. You may use one of them for backups. No matter what it is, typically you would not want to promote such replica to master. All of those additional, non-standard workloads may cause performance problems due to the additional overhead. A good choice for a master candidate is a replica which is handling “normal” load, more or less the same type of load as the current master. You can then be certain it will handle the master load after failover if it handled it before that.

Different Hardware Specifications

We mentioned different roles for replicas. It is not uncommon to see different hardware specifications too, especially in conjunction with different roles. For example, a backup slave most likely doesn’t have to be as powerful as a regular replica. Developers may also test their queries on a slower database than the production (mostly because you would not expect the same level of concurrency on development and production database) and, for example, CPU core count can be reduced. Disaster recovery setups may also be reduced in size if their main role would be to keep up with the replication and it is expected that DR setup will have to be scaled (both vertically, by sizing up the instance and horizontally, by adding more replicas) before traffic can be redirected to it.

Delayed Replicas Related resources  ClusterControl for MySQL Replication  My MySQL Database is Corrupted... What Do I Do Now?  How to do Point-in-Time Recovery of MySQL & MariaDB Data using ClusterControl

Some of the replicas may be delayed - it is a very good way of reducing recovery time if data has been lost, but it makes them very bad master candidates. If a replica is delayed by 30 minutes, you will either lose that 30 minutes of transactions or you will have to wait (probably not 30 minutes as, most likely, the replica can catch up faster) for the replica to apply all delayed transactions. ClusterControl allows you to pick if you want to wait or if you want to failover immediately, but this would work ok for a very small lag - tens of seconds at most. If failover is supposed to take minutes, it’s just no point on using such a replica and therefore it’s a good idea to blacklist it.

Different Datacenter

We mentioned scaled-down DR setups but even if your second datacenter is scaled to the size of production, it still may be a good idea to keep the failovers within a single DC only. For starters, your active application hosts may be located in the main datacenter thus moving the master to a standby DC would significantly increase latency for write queries. Also, in case of a network split, you may want to manually handle this situation. MySQL does not have a quorum mechanism built in therefore it is kind of tricky to correctly handle (in an automatic way) network loss between two datacenters.

Tags:  MySQL replication MariaDB failover blacklist whitelist

Descending index in MySQL 8.0

MySQL 8.0 has come with a list of new features for DBA’s ,we will discuss the new feature in MySQL 8.0 which supports Descending index.Prior to MySQL 8.0 (i.e MySQL 5.6 and 5.7) creating desc index syntax was supported but desc keyword was ignored, Now in MySQL 8.0 release descending index is extended are supported.

What is index?

  • Indexes play an important role in performance optimization  and they are used frequently to speed up access to particular data and reduce disk I/O operations .
  • To understand index easily you can imagine a book,every book has an index with content referring to a page number.If you want to search something in a book you first refer to the index and get the page number and then get the information in the page,like this the indexes in MySQL will tell you the row with matching data.

InnoDB uses a B+Tree structure internally for  indexes. A B+Tree is particularly efficient when data doesn’t fit in memory and must be read from the disk, as it ensures that a fixed maximum number of reads would be required to access any data requested, based only on the depth of the tree, So before working with indexes, it is important to understand how indexes work behind the scene and what is the data structure that is used to store these indexes.

B+tree Index:

Indexes are stored on disk in the form of a data structure known as B+tree. B+tree is in many ways similar to a binary search tree. B+tree follows on the same structure as of a binary search tree, in that each key in a node has all key values less than the key as its left children, and all key values more than the key as its right children.

But there are some very important differences,

  • B+tree can have more than 1 keys in a node, in fact thousands of keys is seen typically stored in a node and hence, the branching factor of a B+tree is very large and that allows the B+trees to be a lot shallower as compared to their binary search tree counterparts.
  • B+trees have all the key values in their leaf nodes. All the leaf nodes of a B+tree are at the same height, which implies that every index lookup will take same number of B+tree lookups to find a value (equisearch)
  • Within a B+tree all leaf nodes are linked together in a linked-listed, left to right, and since the values at the leaf nodes are sorted, so range lookups are very efficient.

 

B+ tree example

Image courtesy :wikipedia

What is Descending indexes?

  • A descending index is an index in which the InnoDB stores the entries in descending order and and the optimizer will choose this index  when descending order is requested in the query ,which is more efficient for queries with ORDER BY clauses and it need not use a filesort operation.
  • Descending indexes are supported only for the InnoDB storage engine.
  • By default the indexes are stored in the Ascending order in a B+Tree.

How to add a descending index on a table ?

The keyword DESC is used along with the common index creation syntax ( Alter/Create )

Alter table table_name add desc_idx(col1_name desc,col2_name asc); Create index desc_idx on table_name(col1_name desc,col2_name asc);

The index (col1_name desc,col2_name asc) satisfies two conditions:

  • Order by col1_name desc,col2_name asc : Forward Scan
  • Order by col1_name asc,col2_name desc : Backward Scan

Now let us see the query performance based on handlers count for the query with and without indexes

  • Here i am using employees table for testing purpose
mysql> select @@version; +-----------+ | @@version | +-----------+ | 8.0.11    | +-----------+ mysql> show create table employees\G *************************** 1. row ***************************       Table: employees Create Table: CREATE TABLE `employees` (  `emp_no` int(11) NOT NULL,  `birth_date` date NOT NULL,  `first_name` varchar(14) NOT NULL,  `last_name` varchar(16) NOT NULL,  `gender` enum('M','F') NOT NULL,  `hire_date` date NOT NULL,  PRIMARY KEY (`emp_no`), ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 1 row in set (0.00 sec) mysql> select count(*) from employees; +----------+ | count(*) | +----------+ |  300025 | +----------+
  • Now let us consider an example of the below query with ordering on descending and ascending conditions.

No index were created initially.

mysql> explain select * from employees order by hire_date desc,first_name asc limit 10\G; *************************** 1. row ***************************           id: 1  select_type: SIMPLE        table: employees   partitions: NULL         type: ALL possible_keys: NULL         key: NULL      key_len: NULL          ref: NULL         rows: 299157     filtered: 100.00        Extra: Using filesort 1 row in set, 1 warning (0.01 sec)

 

mysql> select * from employees order by hire_date desc,first_name asc limit 10; +--------+------------+------------+------------+--------+------------+ | emp_no | birth_date | first_name | last_name  | gender | hire_date | +--------+------------+------------+------------+--------+------------+ | 463807 | 1964-06-12 | Bikash     | Covnot | M | 2000-01-28 | | 428377 | 1957-05-09 | Yucai      | Gerlach | M | 2000-01-23 | | 499553 | 1954-05-06 | Hideyuki   | Delgrande | F | 2000-01-22 | | 222965 | 1959-08-07 | Volkmar    | Perko | F | 2000-01-13 | |  47291 | 1960-09-09 | Ulf        | Flexer | M | 2000-01-12 | | 422990 | 1953-04-09 | Jaana      | Verspoor | F | 2000-01-11 | | 227544 | 1954-11-17 | Shahab     | Demeyer | M | 2000-01-08 | | 205048 | 1960-09-12 | Ennio      | Alblas | F | 2000-01-06 | | 226633 | 1958-06-10 | Xuejun     | Benzmuller | F | 2000-01-04 | | 424445 | 1953-04-27 | Jeong      | Boreale | M | 2000-01-03 | +--------+------------+------------+------------+--------+------------+ 10 rows in set (1.30 sec)

The query took 1.30 secs for limit of 10 records,so from above handler Handler_read_rnd_next count we can see ,the query is doing full table scan which is equal to total number of records in table.

mysql> show status like 'hand_%'; +----------------------------+--------+ | Variable_name              | Value | +----------------------------+--------+ | Handler_commit             |1 | | Handler_read_first         |1 | | Handler_read_key           |1 | | Handler_read_rnd_next      |300025 |
  • Let us create index according to the order in the query for the columns hire_date and first_name.
mysql> create index desc_idx on employees(hire_date desc,first_name); Query OK, 0 rows affected (2.15 sec) Records: 0  Duplicates: 0  Warnings: 0 mysql> explain select * from employees order by hire_date desc,first_name asc limit 10\G *************************** 1. row ***************************           id: 1  select_type: SIMPLE        table: employees   partitions: NULL         type: index possible_keys: NULL          key: desc_idx      key_len: 61          ref: NULL         rows: 10     filtered: 100.00        Extra: Using index 1 row in set, 1 warning (0.00 sec)
  • Now the query executes much faster.
mysql> select * from employees order by hire_date desc,first_name asc limit 10; +--------+------------+------------+------------+--------+------------+ | emp_no | birth_date | first_name | last_name  | gender | hire_date | +--------+------------+------------+------------+--------+------------+ | 463807 | 1964-06-12 | Bikash     | Covnot | M | 2000-01-28 | | 428377 | 1957-05-09 | Yucai      | Gerlach | M | 2000-01-23 | | 499553 | 1954-05-06 | Hideyuki   | Delgrande | F | 2000-01-22 | | 222965 | 1959-08-07 | Volkmar    | Perko | F | 2000-01-13 | |  47291 | 1960-09-09 | Ulf        | Flexer | M | 2000-01-12 | | 422990 | 1953-04-09 | Jaana      | Verspoor | F | 2000-01-11 | | 227544 | 1954-11-17 | Shahab     | Demeyer | M | 2000-01-08 | | 205048 | 1960-09-12 | Ennio      | Alblas | F | 2000-01-06 | | 226633 | 1958-06-10 | Xuejun     | Benzmuller | F | 2000-01-04 | | 424445 | 1953-04-27 | Jeong      | Boreale | M | 2000-01-03 | +--------+------------+------------+------------+--------+------------+ 10 rows in set (0.00 sec)

We can see from Handler_read_next count,the query is using the index to read the next row in key order and here Handler_read_first suggests that the number of times the first entry in an index was read.

mysql> show status like 'hand_%'; +----------------------------+-------+ | Variable_name              | Value | +----------------------------+-------+ | Handler_commit             |1 | | Handler_read_first      |1 | | Handler_read_next       |9 |
  • Lets the run the query by changing order to hire_date asc and first_name desc.
mysql> explain select * from employees order by hire_date asc,first_name desc limit 10\G *************************** 1. row ***************************           id: 1  select_type: SIMPLE        table: employees   partitions: NULL         type: index possible_keys: NULL          key: desc_idx      key_len: 61          ref: NULL         rows: 10     filtered: 100.00        Extra: Backward index scan 1 row in set, 1 warning (0.00 sec) mysql>  select * from employees order by hire_date asc,first_name desc limit 10; +--------+------------+-------------+--------------+--------+------------+ | emp_no | birth_date | first_name  | last_name | gender | hire_date | +--------+------------+-------------+--------------+--------+------------+ | 111692 | 1954-10-05 | Tonny       | Butterworth | F | 1985-01-01 | | 110183 | 1953-06-24 | Shirish     | Ossenbruggen | F | 1985-01-01 | | 111035 | 1962-02-24 | Przemyslawa | Kaelbling    | M | 1985-01-01 | | 110725 | 1961-03-14 | Peternela   | Onuegbe | F | 1985-01-01 | | 110022 | 1956-09-12 | Margareta   | Markovitch | M | 1985-01-01 | | 110303 | 1956-06-08 | Krassimir   | Wegerle | F | 1985-01-01 | | 110085 | 1959-10-28 | Ebru        | Alpin | M | 1985-01-01 | | 110511 | 1957-07-08 | DeForest    | Hagimont | M | 1985-01-01 | | 111400 | 1959-11-09 | Arie        | Staelin | M | 1985-01-01 | | 110114 | 1957-03-28 | Isamu       | Legleitner | F | 1985-01-14 | +--------+------------+-------------+--------------+--------+------------+ 10 rows in set (0.00 sec) mysql> show status like 'hand_%'; +----------------------------+-------+ | Variable_name              | Value | +----------------------------+-------+ | Handler_commit             | 1 | | Handler_read_last       | 1 | | Handler_read_prev       | 9 |

As we can see from handler Handler_read_last and Handler_read_prev the index scan is backward way.

Conclusion:

In MySQL 8.0 it is a great new feature to avoid filesorting for the queries with Order by desc and asc clause.

More can be read on this official work log WL#1074

Image Courtesy : Photo by Verne Ho on Unsplash

This Week in Data with Colin Charles 45: OSCON and Percona Live Europe 2018 Call for Papers

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

Hello again after the hiatus last week. I’m en route to Portland for OSCON, and am very excited as it is the conference’s 20th anniversary! I hope to see some of you at my talk on July 19.

On July 18, join me for a webinar: MariaDB 10.3 vs. MySQL 8.0 at 9:00 AM PDT (UTC-7) / 12:00 PM EDT (UTC-4). I’m also feverishly working on an update to MySQL vs. MariaDB: Reality Check, now that both MySQL 8.0 and MariaDB Server 10.3 are generally available.

Rather important: Percona Live Europe 2018 Call for Papers is now open. You can submit talk ideas until August 10, and the theme is Connect. Accelerate. Innovate.

Releases Link List Industry Updates Upcoming appearances
  • OSCON – Portland, Oregon, USA – July 16-19 2018
Feedback

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

The post This Week in Data with Colin Charles 45: OSCON and Percona Live Europe 2018 Call for Papers appeared first on Percona Database Performance Blog.

MySQL 8.0 Group Replication – Three-server installation

MySQL InnoDB Cluster was introduced in MySQL version 5.7. MySQL InnoDB Cluster consists of three parts – Group Replication, MySQL Shell and MySQL Router. MySQL InnoDB Cluster provides a complete high availability solution for MySQL. I am not going to go into the details of InnoDB Cluster and how it works, as there are enough manual pages and blogs to cover these topics.

MySQL InnoDB Cluster manual
Blog sites: mysqlhighavailability.com and mysqlserverteam.com.

Instead, I will be showing you how to install Group Replication on three new installations of mysql 8.0 manually, without using the MySQL Shell.

These instructions should enable you to setup Group Replication in less than an hour. I am doing this on a Mac running 10.13, but most of these commands can easily be translated over to Linux or Windows. I will try to supply the correct commands for all three operating systems.

I will be installing Group Replication on three new installations of MySQL (without any data) with the IP addresses (host names) of 192.168.1.151 (ic-1), 192.168.1.152 (ic-2) and 192.168.1.153 (ic-3). It is important that you don’t run any other commands on the server, and that you start with a fresh install of MySQL. If you already have a server with data, you will need to export the data and import it into the other servers before you go any further here. Starting Group Replication with a server with data requires a different set of commands, and this blog might not work in that situation.

Group Replication may be setup as either a single-primary (one server to handle the writes and two servers for reads), or multi-primary (read/write to any of the servers). This post covers setting up a single-primary configuration. The server with the IP address of 192.168.1.151 will be our single-primary (read/write server) and the other two servers will be read-only.

Let’s begin.

Edit your /etc/hosts file, and add the IP addresses and host names for the three servers.

192.168.1.151 ic-1 192.168.1.152 ic-2 192.168.1.153 ic-3

Flush the directory service cache by running this as root:

Mac - dscacheutil -flushcache Linux - /etc/rc.d/init.d/nscd restart Windows - ipconfig /flushdns

You will need to add some variables to your MySQL options file, which is located in these directories: (see Using Option Files)

Mac and Linux - /etc/my.cnf Windows - C:\ProgramData\MySQL\MySQL Server X (where X is the version number of MySQL)

NOTE: On Windows, the my.ini file may be hidden.

The following variables need to be in the options file under the [mysqld] section: (you may remove the # comment lines if you want, but read each line before deleting it)

You will need to change the server_id value for each server so each has a unique ID. I simply used 1, 2 and 3 for the three servers.

# All members in the group requires a unique server_id greater than zero server_id=1 # this is the default port for MySQL - you can change it if you want, but it should be the same on all servers port=3306 # you may specify a name for the binary log, or leave it blank to use the default name # however, binary logging is required log_bin=mysql-bin # these settings are required log_slave_updates=ON binlog_checksum=NONE enforce_gtid_consistency=ON gtid_mode=ON master_info_repository=TABLE relay_log_info_repository=TABLE transaction_write_set_extraction=XXHASH64 # Group Replication specific options # this is the name of the plugin # load the plugin - in Windows, the plugin will be named group_replication.dll plugin_load_add ="group_replication.so" # this is required group_replication = FORCE_PLUS_PERMANENT # you will turn this on and back off during Group Replication setup group_replication_bootstrap_group = OFF # this is the UUID for the entire group # Each server has their own UUID in the file auto.cnf located in the MySQL data directory # generate your own group_replication_group_name on Linux with `uuidgen -t`, # on a Mac use "uuidgen" # all members use this value as group_replication_group_name # for Windows - The Windows SDK comes with a tool called uuidgen group_replication_group_name = 8E2F4761-C55C-422F-8684-D086F6A1DB0E

In the config file make sure group_replication_start_on_boot is set to OFF or add a # (comment) to the line with the value of ON as shown below. You will want to uncomment this line after you setup and start Group Replication, so if the server is rebooted, Group Replication will begin automatically.

# uncomment this line AFTER you have started Group Replication # so that Group Replication will start after a reboot #group_replication_start_on_boot = ON

You will need to change this line to match each of the servers, and while you can change the group_replication_local_address (or port number – the recommended default value is 33061), it doesn’t matter what port you use as long as that port isn’t being used by another application.

# change this to be the local address for each server # the port number can be anything except ports already in use # and do not use 3306 group_replication_local_address = '192.168.1.151:33061'

This line must contain all of the servers that will be in your group. The group_replication_group_seeds is a list of group members used to establish the connection from the new member to the group.

# add all of the members of the group here, along with the same port numbers group_replication_group_seeds = '192.168.1.151:33061,192.168.1.152:33061,192.168.1.153:33061'

That is all you need for the configuration file. After you have made the changes, reboot the MySQL instance.

NOTE: If you installed MySQL as the root user, be sure that the OS user “mysql” owns all of the mysql directories. You will need to change the directory name (in this example it is /usr/local) to be the directory where you installed the MySQL Server.

# be sure that the mysql user own the mysql directory # if you install MySQL via root on a Mac or Linux, there is a good chance that root owns the directory $ cd /usr/local $ chown -R mysql mysql*

Now we are ready to install Group Replication. Be sure to restart the mysqld processes to make the /etc/my.cnf changes permanent.

Let’s start with Server #1, which will be our read-write primary server.

On Server #1 (IP 192.168.1.151)

Since we included the variable group_replication_group_seeds in the options file, each instance has already been added to the group.

The table performance_schema.replication_group_members shows network and status information for replication group members. The network addresses shown are the addresses used to connect clients to the group, and should not be confused with the member’s internal group communication address specified by group_replication_local_address. (source: https://dev.mysql.com/doc/refman/8.0/en/replication-group-members-table.html)

You can run this command from a mysql prompt on each of the servers to see if they have joined the group successfully:

# command to run from MySQL prompt
SELECT * FROM performance_schema.replication_group_members;

mysql> SELECT * FROM performance_schema.replication_group_members; +---------------------------+--------------------------------------+------------------+-------------+--------------+-------------+----------------+ | CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION | +---------------------------+--------------------------------------+------------------+-------------+--------------+-------------+----------------+ | group_replication_applier | 60889f20-48ed-11e8-b6e2-0998e2a48fe0 | MacVM151-2.local | 3306 | OFFLINE | | | +---------------------------+--------------------------------------+------------------+-------------+--------------+-------------+----------------+ 1 row in set (0.00 sec)

Or, you can run the command with the \G at the end instead of the semi-colon (;)

# command to run from MySQL prompt
SELECT * FROM performance_schema.replication_group_members\G

mysql> SELECT * FROM performance_schema.replication_group_members\G *************************** 1. row *************************** CHANNEL_NAME: group_replication_applier MEMBER_ID: 60889f20-48ed-11e8-b6e2-0998e2a48fe0 MEMBER_HOST: MacVM151-2.local MEMBER_PORT: 3306 MEMBER_STATE: OFFLINE MEMBER_ROLE: MEMBER_VERSION: 1 row in set (0.00 sec)

The MEMBER_ID 60889f20-48ed-11e8-b6e2-0998e2a48fe0 is the UUID for this particular MySQL instance. The UUID is located in the auto.cnf file in the MySQL data directory.

# cat /usr/local/mysql/data/auto.cnf [auto] server-uuid=60889f20-48ed-11e8-b6e2-0998e2a48fe0

You can take a look at the MASTER STATUS of the primary server, and it should be relatively blank.

# command to run from MySQL prompt
SHOW MASTER STATUS\G

mysql> SHOW MASTER STATUS\G *************************** 1. row *************************** File: mysql-bin.000001 Position: 151 Binlog_Do_DB: Binlog_Ignore_DB: Executed_Gtid_Set: 1 row in set (0.00 sec)

The active binary log for the server is “mysql-bin.000001“. You can take a look at the events in the log and see nothing has happened on the server.

# command to run from MySQL prompt
SHOW BINLOG EVENTS in ‘mysql-bin.000001’\G

mysql> SHOW BINLOG EVENTS in 'mysql-bin.000001'\G *************************** 1. row *************************** Log_name: mysql-bin.000001 Pos: 4 Event_type: Format_desc Server_id: 151 End_log_pos: 124 Info: Server ver: 8.0.11, Binlog ver: 4 *************************** 2. row *************************** Log_name: mysql-bin.000001 Pos: 124 Event_type: Previous_gtids Server_id: 151 End_log_pos: 151 Info: 2 rows in set (0.00 sec)

Now go to Server #2 (IP 192.168.1.152)

Confirm that Server #2 is part of the group.

# command to run from MySQL prompt
SELECT * FROM performance_schema.replication_group_members;

mysql> SELECT * FROM performance_schema.replication_group_members; +---------------------------+--------------------------------------+----------------+-------------+--------------+-------------+----------------+ | CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION | +---------------------------+--------------------------------------+----------------+-------------+--------------+-------------+----------------+ | group_replication_applier | f4112942-48ed-11e8-86c4-f57773daeb0a | MacVM152.local | 3306 | OFFLINE | | | +---------------------------+--------------------------------------+----------------+-------------+--------------+-------------+----------------+ 1 row in set (0.00 sec)

OPTIONAL: If you want, you can run the same informational commands for Server #2 as you did on Server #1. (SHOW MASTER STATUS, SHOW BINLOG EVENTS, cat auto.cnf)

Now go to Server #3 (IP 192.168.1.153)

Confirm that Server #3 is part of the group.

# command to run from MySQL prompt
SELECT * FROM performance_schema.replication_group_members;

mysql> SELECT * FROM performance_schema.replication_group_members; +---------------------------+--------------------------------------+----------------+-------------+--------------+-------------+----------------+ | CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION | +---------------------------+--------------------------------------+----------------+-------------+--------------+-------------+----------------+ | group_replication_applier | bedc9968-48ee-11e8-9735-0a5899f91373 | MacVM153.local | 3306 | OFFLINE | | | +---------------------------+--------------------------------------+----------------+-------------+--------------+-------------+----------------+ 1 row in set (0.01 sec)

OPTIONAL: If you want, you can run the same informational commands on Server #3 as you did on Server #1. (SHOW MASTER STATUS, SHOW BINLOG EVENTS, cat auto.cnf)

Make sure that the “group_replication” plugin is active on all three servers. You value of PLUGIN_STATUS should be ACTIVE. Run this command on all three servers.

# command to run from MySQL prompt
select * from information_schema.plugins where PLUGIN_NAME = ‘group_replication’\G

mysql> select * from information_schema.plugins where PLUGIN_NAME = 'group_replication'\G *************************** 1. row *************************** PLUGIN_NAME: group_replication PLUGIN_VERSION: 1.1 PLUGIN_STATUS: ACTIVE PLUGIN_TYPE: GROUP REPLICATION PLUGIN_TYPE_VERSION: 1.2 PLUGIN_LIBRARY: group_replication.so PLUGIN_LIBRARY_VERSION: 1.9 PLUGIN_AUTHOR: ORACLE PLUGIN_DESCRIPTION: Group Replication (1.1.0) PLUGIN_LICENSE: GPL LOAD_OPTION: FORCE_PLUS_PERMANENT 1 row in set (0.00 sec)

You should see the same output on the other two servers (IP 192.168.1.152 and 192.168.1.152) as on Server #1.

Next you will want to create the replication users. Since we will be turning on replication, we don’t want to write this to the binary logs. Execute this command on all of the servers.

# run this on all three servers
# commands to run from MySQL prompt
SET SQL_LOG_BIN=0;
CREATE USER rpl_user@’%’ IDENTIFIED BY ‘R3plic4tion!’;
GRANT REPLICATION SLAVE ON *.* TO rpl_user@’%’;
FLUSH PRIVILEGES;
SET SQL_LOG_BIN=1;

Once the users have been created, we can start execute our CHANGE MASTER statement and start Group Replication. Execute these two commands on all of the servers.

# run this on all three servers
CHANGE MASTER TO MASTER_USER=’rpl_user’, MASTER_PASSWORD=’R3plic4tion!’ FOR CHANNEL ‘group_replication_recovery’;
START GROUP_REPLICATION;

We have to create the replication users, but we don’t want to write this to the binary log, as it would get replicated to the other servers and cause an error.

On Server #1 (IP 192.168.1.151)

# commands to run from MySQL prompt
SET SQL_LOG_BIN=0;
CREATE USER rpl_user@’%’ IDENTIFIED BY ‘R3plic4tion!’;
GRANT REPLICATION SLAVE ON *.* TO rpl_user@’%’;
FLUSH PRIVILEGES;
SET SQL_LOG_BIN=1;

mysql> SET SQL_LOG_BIN=0; Query OK, 0 rows affected (0.00 sec) mysql> CREATE USER rpl_user@'%' IDENTIFIED BY 'R3plic4tion!'; Query OK, 0 rows affected (0.04 sec) mysql> GRANT REPLICATION SLAVE ON *.* TO rpl_user@'%'; Query OK, 0 rows affected (0.00 sec) mysql> FLUSH PRIVILEGES; Query OK, 0 rows affected (0.00 sec) mysql> SET SQL_LOG_BIN=1; Query OK, 0 rows affected (0.00 sec)

Since we are only running single-primary mode, we don’t need to do a CHANGE MASTER on Server #1. In replication, the slave is responsible for connecting to the master to get the write statements. Since the secondary nodes won’t be accepting any writes, then the primary won’t need to connect to them. But, since the primary could fail, and be brought back into the group as a read-only slave, we need to go ahead and run the following CHANGE MASTER statement.

The CHANGE MASTER statement will produce two warnings – we can look at the warnings and ignore them.

# commands to run from MySQL prompt
CHANGE MASTER TO MASTER_USER=’rpl_user’, MASTER_PASSWORD=’R3plic4tion!’ FOR CHANNEL ‘group_replication_recovery’;

mysql> CHANGE MASTER TO MASTER_USER='rpl_user', MASTER_PASSWORD='R3plic4tion!' FOR CHANNEL 'group_replication_recovery'; Query OK, 0 rows affected, 2 warnings (0.06 sec) mysql> show warnings; +-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Level | Code | Message | +-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Note | 1759 | Sending passwords in plain text without SSL/TLS is extremely insecure. | | Note | 1760 | Storing MySQL user name or password information in the master info repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START SLAVE; see the 'START SLAVE Syntax' in the MySQL Manual for more information. | +-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 2 rows in set (0.00 sec)

Now we will need to turn group_replication_bootstrap_group to ON, and then we can start Group Replication (but do this only for on Server #1). We will want to turn group_replication_bootstrap_group to OFF after we have started Group Replication for the first time.

# commands to run from MySQL prompt
SET GLOBAL group_replication_bootstrap_group=ON;
START GROUP_REPLICATION;
SET GLOBAL group_replication_bootstrap_group=OFF;

mysql> SET GLOBAL group_replication_bootstrap_group=ON; Query OK, 0 rows affected (0.00 sec) mysql> START GROUP_REPLICATION; Query OK, 0 rows affected (3.10 sec) mysql> SET GLOBAL group_replication_bootstrap_group=OFF; Query OK, 0 rows affected (0.00 sec)

Check the status of the MASTER server:

# commands to run from MySQL prompt
SHOW MASTER STATUS\G

mysql> SHOW MASTER STATUS\G *************************** 1. row *************************** File: mysql-bin.000001 Position: 458 Binlog_Do_DB: Binlog_Ignore_DB: Executed_Gtid_Set: 8e2f4761-c55c-422f-8684-d086f6a1db0e:1 1 row in set (0.00 sec)

There has only been one GTID executed on the MASTER – <font face="courier" color=blue8e2f4761-c55c-422f-8684-d086f6a1db0e:1. Check the binlog to see what has been written to it.

# commands to run from MySQL prompt
SHOW BINLOG EVENTS in ‘mysql-bin.000001’;

mysql> SHOW BINLOG EVENTS in 'mysql-bin.000001'; +------------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------+ | Log_name | Pos | Event_type | Server_id | End_log_pos | Info | +------------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------+ | mysql-bin.000001 | 4 | Format_desc | 151 | 124 | Server ver: 8.0.11, Binlog ver: 4 | | mysql-bin.000001 | 124 | Previous_gtids | 151 | 151 | | | mysql-bin.000001 | 151 | Gtid | 151 | 229 | SET @@SESSION.GTID_NEXT= '8e2f4761-c55c-422f-8684-d086f6a1db0e:1' | | mysql-bin.000001 | 229 | Query | 151 | 291 | BEGIN | | mysql-bin.000001 | 291 | View_change | 151 | 390 | view_id=15247058086370221:1 | | mysql-bin.000001 | 390 | Query | 151 | 458 | COMMIT | +------------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------+ 6 rows in set (0.00 sec)

We still only have one member of the Group Replication, and we can see which server is a member:

# commands to run from MySQL prompt
SELECT * FROM performance_schema.replication_group_members;

mysql> SELECT * FROM performance_schema.replication_group_members; +---------------------------+--------------------------------------+------------------+-------------+--------------+-------------+----------------+ | CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION | +---------------------------+--------------------------------------+------------------+-------------+--------------+-------------+----------------+ | group_replication_applier | 60889f20-48ed-11e8-b6e2-0998e2a48fe0 | MacVM151-2.local | 3306 | ONLINE | PRIMARY | 8.0.11 | +---------------------------+--------------------------------------+------------------+-------------+--------------+-------------+----------------+ 1 row in set (0.00 sec)

# commands to run from MySQL prompt
SELECT * FROM performance_schema.replication_group_members\G

mysql> SELECT * FROM performance_schema.replication_group_members\G *************************** 1. row *************************** CHANNEL_NAME: group_replication_applier MEMBER_ID: 60889f20-48ed-11e8-b6e2-0998e2a48fe0 MEMBER_HOST: MacVM151-2.local MEMBER_PORT: 3306 MEMBER_STATE: ONLINE MEMBER_ROLE: PRIMARY MEMBER_VERSION: 8.0.11 1 row in set (0.00 sec)

You can see one member of the group (Server #1) is online. Group Replication has been started on Server #1.

Now we can add Server #2. We will do the same steps as we did for Server #1, except we don’t need to bootstrap Group Replication, as it has already been started. Oon Server #2 (IP 192.168.1.152):

# commands to run from MySQL prompt
SET SQL_LOG_BIN=0;
CREATE USER rpl_user@’%’ IDENTIFIED BY ‘R3plic4tion!’;
GRANT REPLICATION SLAVE ON *.* TO rpl_user@’%’;
FLUSH PRIVILEGES;
SET SQL_LOG_BIN=1;

mysql> SET SQL_LOG_BIN=0; Query OK, 0 rows affected (0.00 sec) mysql> CREATE USER rpl_user@'%' IDENTIFIED BY 'R3plic4tion!'; Query OK, 0 rows affected (0.03 sec) mysql> GRANT REPLICATION SLAVE ON *.* TO rpl_user@'%'; Query OK, 0 rows affected (0.01 sec) mysql> FLUSH PRIVILEGES; Query OK, 0 rows affected (0.00 sec) mysql> SET SQL_LOG_BIN=1; Query OK, 0 rows affected (0.00 sec)

# commands to run from MySQL prompt
SHOW MASTER STATUS\G

mysql> SHOW MASTER STATUS\G *************************** 1. row *************************** File: mysql-bin.000001 Position: 151 Binlog_Do_DB: Binlog_Ignore_DB: Executed_Gtid_Set: 1 row in set (0.00 sec)

Run the CHANGE MASTER statement:

# commands to run from MySQL prompt
CHANGE MASTER TO MASTER_USER=’rpl_user’, MASTER_PASSWORD=’R3plic4tion!’ FOR CHANNEL ‘group_replication_recovery’;

mysql> CHANGE MASTER TO MASTER_USER='rpl_user', MASTER_PASSWORD='R3plic4tion!' FOR CHANNEL 'group_replication_recovery'; Query OK, 0 rows affected, 2 warnings (0.06 sec) mysql> show warnings; +-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Level | Code | Message | +-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Note | 1759 | Sending passwords in plain text without SSL/TLS is extremely insecure. | | Note | 1760 | Storing MySQL user name or password information in the master info repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START SLAVE; see the 'START SLAVE Syntax' in the MySQL Manual for more information. | +-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 2 rows in set (0.00 sec)

The <font face="courier" color=blueCHANGE MASTER statement produced two warnings – we can look at the warnings and ignore them.
# commands to run from MySQL prompt
SHOW WARNINGS\G

mysql> SHOW WARNINGS\G *************************** 1. row *************************** Level: Note Code: 1759 Message: Sending passwords in plain text without SSL/TLS is extremely insecure. *************************** 2. row *************************** Level: Note Code: 1760 Message: Storing MySQL user name or password information in the master info repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START SLAVE; see the 'START SLAVE Syntax' in the MySQL Manual for more information. 2 rows in set (0.00 sec)

Start Group Replication on Server #2:

# commands to run from MySQL prompt
START GROUP_REPLICATION;

mysql> START GROUP_REPLICATION; Query OK, 0 rows affected (3.10 sec) NOTE: If you put in the wrong password in your <font face="courier" color=blueCHANGE MASTER statement, the server will be in the group, but will be in a <font face="courier" color=blueMEMBER_STATE of <font face="courier" color=blueRECOVERING and it will never join the group. mysql> SELECT * FROM performance_schema.replication_group_members; +---------------------------+--------------------------------------+------------------+-------------+--------------+-------------+----------------+ | CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION | +---------------------------+--------------------------------------+------------------+-------------+--------------+-------------+----------------+ | group_replication_applier | 60889f20-48ed-11e8-b6e2-0998e2a48fe0 | MacVM151-2.local | 3306 | ONLINE | PRIMARY | 8.0.11 | | group_replication_applier | f4112942-48ed-11e8-86c4-f57773daeb0a | MacVM152.local | 3306 | RECOVERING | SECONDARY | 8.0.11 | +---------------------------+--------------------------------------+------------------+-------------+--------------+-------------+----------------+ 2 rows in set (0.00 sec)

Check to see that Server #2 has joined the group. You can execute this command on Server #1 or #2. (This was executed from Server #1)

# commands to run from MySQL prompt
SELECT * FROM performance_schema.replication_group_members;

mysql> SELECT * FROM performance_schema.replication_group_members; +---------------------------+--------------------------------------+------------------+-------------+--------------+-------------+----------------+ | CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION | +---------------------------+--------------------------------------+------------------+-------------+--------------+-------------+----------------+ | group_replication_applier | 60889f20-48ed-11e8-b6e2-0998e2a48fe0 | MacVM151-2.local | 3306 | ONLINE | PRIMARY | 8.0.11 | | group_replication_applier | f4112942-48ed-11e8-86c4-f57773daeb0a | MacVM152.local | 3306 | ONLINE | SECONDARY | 8.0.11 | +---------------------------+--------------------------------------+------------------+-------------+--------------+-------------+----------------+ 2 rows in set (0.00 sec)

Or you can run it with the <font face="courier" color=blue\G at the end.

# commands to run from MySQL prompt
SELECT * FROM performance_schema.replication_group_members\G

mysql> SELECT * FROM performance_schema.replication_group_members\G *************************** 1. row *************************** CHANNEL_NAME: group_replication_applier MEMBER_ID: 60889f20-48ed-11e8-b6e2-0998e2a48fe0 MEMBER_HOST: MacVM151-2.local MEMBER_PORT: 3306 MEMBER_STATE: ONLINE MEMBER_ROLE: PRIMARY MEMBER_VERSION: 8.0.11 *************************** 2. row *************************** CHANNEL_NAME: group_replication_applier MEMBER_ID: f4112942-48ed-11e8-86c4-f57773daeb0a MEMBER_HOST: MacVM152.local MEMBER_PORT: 3306 MEMBER_STATE: ONLINE MEMBER_ROLE: SECONDARY MEMBER_VERSION: 8.0.11 2 rows in set (0.00 sec)

Running the same commands from Server #2 gets the same results, as you can query the status of the Group from any member of the group.

This was executed from Server #2

# commands to run from MySQL prompt
SELECT * FROM performance_schema.replication_group_members;

mysql> SELECT * FROM performance_schema.replication_group_members; +---------------------------+--------------------------------------+------------------+-------------+--------------+-------------+----------------+ | CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION | +---------------------------+--------------------------------------+------------------+-------------+--------------+-------------+----------------+ | group_replication_applier | 60889f20-48ed-11e8-b6e2-0998e2a48fe0 | MacVM151-2.local | 3306 | ONLINE | PRIMARY | 8.0.11 | | group_replication_applier | f4112942-48ed-11e8-86c4-f57773daeb0a | MacVM152.local | 3306 | ONLINE | SECONDARY | 8.0.11 | +---------------------------+--------------------------------------+------------------+-------------+--------------+-------------+----------------+ 2 rows in set (0.00 sec)

And the option with <font face="courier" color=blue\G at the end:
# commands to run from MySQL prompt
SELECT * FROM performance_schema.replication_group_members\G

mysql> SELECT * FROM performance_schema.replication_group_members\G *************************** 1. row *************************** CHANNEL_NAME: group_replication_applier MEMBER_ID: 60889f20-48ed-11e8-b6e2-0998e2a48fe0 MEMBER_HOST: MacVM151-2.local MEMBER_PORT: 3306 MEMBER_STATE: ONLINE MEMBER_ROLE: PRIMARY MEMBER_VERSION: 8.0.11 *************************** 2. row *************************** CHANNEL_NAME: group_replication_applier MEMBER_ID: f4112942-48ed-11e8-86c4-f57773daeb0a MEMBER_HOST: MacVM152.local MEMBER_PORT: 3306 MEMBER_STATE: ONLINE MEMBER_ROLE: SECONDARY MEMBER_VERSION: 8.0.11 2 rows in set (0.00 sec)

Group replication now has Server #1 and Server #2. Now we can add Server #3 (IP 192.168.1.153)

We run the same commands as above. Note:I only show the commands to run – I do not show the screen output here.

# commands to run from MySQL prompt
SET SQL_LOG_BIN=0;
CREATE USER rpl_user@’%’ IDENTIFIED BY ‘R3plic4tion!’;
GRANT REPLICATION SLAVE ON *.* TO rpl_user@’%’;
FLUSH PRIVILEGES;
SET SQL_LOG_BIN=1;

CHANGE MASTER TO MASTER_USER=’rpl_user’, MASTER_PASSWORD=’R3plic4tion!’ FOR CHANNEL ‘group_replication_recovery’;

START GROUP_REPLICATION;

If you didn’t see any errors, Group Replication is ready to go. As before, you can check the status of the group from any server.

# commands to run from MySQL prompt
SELECT * FROM performance_schema.replication_group_members\G

mysql> SELECT * FROM performance_schema.replication_group_members\G *************************** 1. row *************************** CHANNEL_NAME: group_replication_applier MEMBER_ID: 60889f20-48ed-11e8-b6e2-0998e2a48fe0 MEMBER_HOST: MacVM151-2.local MEMBER_PORT: 3306 MEMBER_STATE: ONLINE MEMBER_ROLE: PRIMARY MEMBER_VERSION: 8.0.11 *************************** 2. row *************************** CHANNEL_NAME: group_replication_applier MEMBER_ID: bedc9968-48ee-11e8-9735-0a5899f91373 MEMBER_HOST: MacVM153.local MEMBER_PORT: 3306 MEMBER_STATE: ONLINE MEMBER_ROLE: SECONDARY MEMBER_VERSION: 8.0.11 *************************** 3. row *************************** CHANNEL_NAME: group_replication_applier MEMBER_ID: f4112942-48ed-11e8-86c4-f57773daeb0a MEMBER_HOST: MacVM152.local MEMBER_PORT: 3306 MEMBER_STATE: ONLINE MEMBER_ROLE: SECONDARY MEMBER_VERSION: 8.0.11 3 rows in set (0.00 sec)

Or you can run:

# commands to run from MySQL prompt
SELECT * FROM performance_schema.replication_group_members;

mysql> SELECT * FROM performance_schema.replication_group_members; +---------------------------+--------------------------------------+------------------+-------------+--------------+-------------+----------------+ | CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION | +---------------------------+--------------------------------------+------------------+-------------+--------------+-------------+----------------+ | group_replication_applier | 60889f20-48ed-11e8-b6e2-0998e2a48fe0 | MacVM151-2.local | 3306 | ONLINE | PRIMARY | 8.0.11 | | group_replication_applier | bedc9968-48ee-11e8-9735-0a5899f91373 | MacVM153.local | 3306 | ONLINE | SECONDARY | 8.0.11 | | group_replication_applier | f4112942-48ed-11e8-86c4-f57773daeb0a | MacVM152.local | 3306 | ONLINE | SECONDARY | 8.0.11 | +---------------------------+--------------------------------------+------------------+-------------+--------------+-------------+----------------+ 3 rows in set (0.00 sec)

NOTICE: Be sure to change <font face="courier" color=bluegroup_replication_start_on_boot to <font face="courier" color=blueON in your my.ini or my.cnf configuration file for all of the servers as shown below.

(in the my.cnf or my.ini file)
group_replication_start_on_boot = ON

If the MEMBER_STATE of three servers is ONLINE, then you are finished and Group Replication has been installed!

How to Install Nextcloud with Nginx on Ubuntu 18.04 LTS

In this tutorial, we will show you how to install and configure the latest Nextcloud 13.0.2 release on an Ubuntu 18.04 server. We will run Nextcloud with a Nginx web server and PHP7.1-FPM and use MySQL server as the database system. Nextcloud is a free (Open Source) Dropbox-like software, a fork of the ownCloud project.

Mastering Continuent Clustering Series: Tuning for High-Latency Links

What if I want the cluster to be less sensitive to network, especially WAN latency?

Continuent Clustering supports having clusters at multiple sites with active-active replication meshing them together.

This is extraordinarily powerful, yet at times high network latency can make it harder for messaging between the sites to arrive in a timely manner.

This is evidenced by seeing the following in the Manager log files named tmsvc.log:

2018/07/08 16:51:05 | db3 | INFO [Rule_0604$u58$_DETECT_UNREACHABLE_REMOTE_SERVICE1555959201] - CONSEQUENCE: [Sun Jul 08 16:51:04 UTC 2018] CLUSTER global/omega(state=UNREACHABLE) ... 2018/07/08 16:51:42 | db3 | INFO [Rule_2025$u58$_REPORT_COMPONENT_STATE_TRANSITIONS1542395297] - CLUSTER 'omega@global' STATE TRANSITION UNREACHABLE => ONLINE

The delta is 37 seconds in the above example between state=UNREACHABLE and UNREACHABLE => ONLINE

The default timeout is 60 seconds.

If the delay above were longer than 60 seconds, one site would shun the other, and traffic would be blocked by the Connector proxy to the remote site.

This timeout may be tuned to be longer, however.

This is the policy.remote.service.shun.threshold setting, and the default value is 6.

Whatever this property is set to is multiplied by 10 seconds to come up with the final interval, so 60 seconds by default.

Find all gaps shown in the logs, figure out the time differences and then take the peak value in seconds, add 10 seconds as a buffer and then divide by 10. Take the INT of that and you have your new value.

Add property=policy.remote.service.shun.threshold={new_value} to your tungsten.ini and update your clusters!

This tuning will provide better cluster stability via insulation from high latency network link timeouts.

Questions? Contact us.

On MySQL and Intel Optane performance

Recently, Dimitri published the results of measuring MySQL 8.0 on Intel Optane storage device. In this blog post, I wanted to look at this in more detail and explore the performance of MySQL 8, MySQL 5.7 and Percona Server for MySQL using a similar set up. The Intel Optane is a very capable device, so I was puzzled that Dimitri chose MySQL options that are either not safe or not recommended for production workloads.

Since we have an Intel Optane in our labs, I wanted to run a similar benchmark, but using settings that we would recommend our customers to use, namely:

  • use innodb_checksum
  • use innodb_doublewrite
  • use binary logs with sync_binlog=1
  • enable (by default) Performance Schema

I still used

charset=latin1  (even though the default is utf8mb4 in MySQL 8) and I set a total size of InnoDB log files to 30GB (as in Dimitri’s benchmark). This setting allocates big InnoDB log files to ensure there is no pressure from adaptive flushing. Though I have concerns about how it works in MySQL 8, this is a topic for another research.

So let’s see how MySQL 8.0 performed with these settings, and compare it with MySQL 5.7 and Percona Server for MySQL 5.7.

I used an Intel Optane SSD 905P 960GB device on the server with 2 socket Intel(R) Xeon(R) CPU E5-2680 v3 @ 2.50GHz CPUs.

To highlight the performance difference I wanted to show, I used a single case: sysbench 8 tables 50M rows each (which is about ~120GB of data) and buffer pool 32GB. I ran sysbench oltp_read_write in 128 threads.

First, let’s review the results for MySQL 8 vs MySQL 5.7

After achieving a steady state – we can see that MySQL 8 does not have ANY performance improvements over MySQL 5.7.

Let’s compare this with Percona Server for MySQL 5.7

Percona Server for MySQL 5.7 shows about 60% performance improvement over both MySQL 5.7 and MySQL 8.

How did we achieve this? All our improvements are described here: https://www.percona.com/doc/percona-server/LATEST/performance/xtradb_performance_improvements_for_io-bound_highly-concurrent_workloads.html. In short:

  1. Parallel doublewrite.  In both MySQL 5.7 and MySQL 8 writes are serialized by writing to doublewrite.
  2. Multi-threaded LRU flusher. We reported and proposed a solution here https://bugs.mysql.com/bug.php?id=70500. However, Oracle have not incorporated the solution upstream.
  3. Single page eviction. This is another problematic area in MySQL’s flushing algorithm. The bug https://bugs.mysql.com/bug.php?id=81376 was reported over 2 years ago, but unfortunately it’s still overlooked.

Summarizing performance findings:

  • For Percona Server for MySQL during this workload, I observed 1.4 GB/sec  reads and 815 MB/sec  writes
  • For MySQL 5.7 and MySQL 8 the numbers are 824 MB/sec reads and  530 MB/sec writes.

My opinion is that Oracle focused on addressing the wrong performance problems in MySQL 8 and did not address the real issues. In this benchmark, using real production settings, MySQL 8 does not show any significant performance benefits over MySQL 5.7 for workloads characterized by heavy IO writes.

With this, I should admit that Intel Optane is a very performant storage. By comparison, on Intel 3600 SSD under the same workload, for Percona Server I am able to achieve only 2000 tps, which is 2.5x times slower than with Intel Optane.

Drawing some conclusions

So there are a few outcomes I can highlight:

  • Intel Optane is a very capable drive, it is easily the fastest of those we’ve tested so far
  • MySQL 8 is not able to utilize all the power of Intel Optane, unless you use unsafe settings (which to me is the equivalent of driving 200 MPH on a highway without working brakes)
  • Oracle has focused on addressing the wrong IO bottlenecks and has overlooked the real ones
  • To get all the benefits of Intel Optane performance, use a proper server—Percona Server for MySQL—which is able to utilize more IOPS from the device.

The post On MySQL and Intel Optane performance appeared first on Percona Database Performance Blog.

How to perform Schema Changes in MySQL & MariaDB in a Safe Way

Before you attempt to perform any schema changes on your production databases, you should make sure that you have a rock solid rollback plan; and that your change procedure has been successfully tested and validated in a separate environment. At the same time, it’s your responsibility to make sure that the change causes none or the least possible impact acceptable to the business. It’s definitely not an easy task.

In this article, we will take a look at how to perform database changes on MySQL and MariaDB in a controlled way. We will talk about some good habits in your day-to-day DBA work. We’ll focus on pre-requirements and tasks during the actual operations and problems that you may face when you deal with database schema changes. We will also talk about open source tools that may help you in the process.

Test and rollback scenarios Backup

There are many ways to lose your data. Schema upgrade failure is one of them. Unlike application code, you can’t drop a bundle of files and declare that a new version has been successfully deployed. You also can’t just put back an older set of files to rollback your changes. Of course, you can run another SQL script to change the database again, but there are cases when the only accurate way to roll back changes is by restoring the entire database from backup.

However, what if you can’t afford to rollback your database to the latest backup, or your maintenance window is not big enough (considering system performance), so you can’t perform a full database backup before the change?

One may have a sophisticated, redundant environment, but as long as data is modified in both primary and standby locations, there is not much to do about it. Many scripts can just be run once, or the changes are impossible to undo. Most of the SQL change code falls into two groups:

  • Run once – you can’t add the same column to the table twice.
  • Impossible to undo – once you’ve dropped that column, it’s gone. You could undoubtedly restore your database, but that’s not precisely an undo.

You can tackle this problem in at least two possible ways. One would be to enable the binary log and take a backup, which is compatible with PITR. Such backup has to be full, complete and consistent. For xtrabackup, as long as it contains a full dataset, it will be PITR-compatible. For mysqldump, there is an option to make it PITR-compatible too. For smaller changes, a variation of mysqldump backup would be to take only a subset of data to change. This can be done with --where option. The backup should be part of the planned maintenance.

mysqldump -u -p --lock-all-tables --where="WHERE employee_id=100" mydb employees> backup_table_tmp_change_07132018.sql

Another possibility is to use CREATE TABLE AS SELECT.

You can store data or simple structure changes in the form of a fixed temporary table. With this approach you will get a source if you need to rollback your changes. It may be quite handy if you don’t change much data. The rollback can be done by taking data out from it. If any failures occur while copying the data to the table, it is automatically dropped and not created, so make sure that your statement creates a copy you need.

Obviously, there are some limitations too.

Because the ordering of the rows in the underlying SELECT statements cannot always be determined, CREATE TABLE ... IGNORE SELECT and CREATE TABLE ... REPLACE SELECT are flagged as unsafe for statement-based replication. Such statements produce a warning in the error log when using statement-based mode and are written to the binary log using the row-based format when using MIXED mode.

A very simple example of such method could be:

CREATE TABLE tmp_employees_change_07132018 AS SELECT * FROM employees where employee_id=100; UPDATE employees SET salary=120000 WHERE employee_id=100; COMMMIT;

Another interesting option may be MariaDB flashback database. When a wrong update or delete happens, and you would like to revert to a state of the database (or just a table) at a certain point in time, you may use the flashback feature.

Point-in-time rollback enables DBAs to recover data faster by rolling back transactions to a previous point in time rather than performing a restore from a backup. Based on ROW-based DML events, flashback can transform the binary log and reverse purposes. That means it can help undo given row changes fast. For instance, it can change DELETE events to INSERTs and vice versa, and it will swap WHERE and SET parts of the UPDATE events. This simple idea can dramatically speed up recovery from certain types of mistakes or disasters. For those who are familiar with the Oracle database, it’s a well known feature. The limitation of MariaDB flashback is the lack of DDL support.

Create a delayed replication slave

Since version 5.6, MySQL supports delayed replication. A slave server can lag behind the master by at least a specified amount of time. The default delay is 0 seconds. Use the MASTER_DELAY option for CHANGE MASTER TO to set the delay to N seconds:

CHANGE MASTER TO MASTER_DELAY = N;

It would be a good option if you didn’t have time to prepare a proper recovery scenario. You need to have enough delay to notice the problematic change. The advantage of this approach is that you don’t need to restore your database to take out data needed to fix your change. Standby DB is up and running, ready to pick up data which minimizes the time needed.

Create an asynchronous slave which is not part of the cluster

When it comes to Galera cluster, testing changes is not easy. All nodes run the same data, and heavy load can harm flow control. So you not only need to check if changes applied successfully, but also what the impact to the cluster state was. To make your test procedure as close as possible to the production workload, you may want to add an asynchronous slave to your cluster and run your test there. The test will not impact synchronization between cluster nodes, because technically it’s not part of the cluster, but you will have an option to check it with real data. Such slave can be easily added from ClusterControl.

ClusterControl add asynchronous slave

As shown in the above screenshot, ClusterControl can automate the process of adding an asynchronous slave in a few ways. You can add the node to the cluster, delay the slave. To reduce the impact on the master, you can use an existing backup instead of the master as the data source when building the slave.

Clone database and measure time

A good test should be as close as possible to the production change. The best way to do this is to clone your existing environment.

ClusterControl Clone Cluster for test Perform changes via replication

To have better control over your changes, you can apply them on a slave server ahead of time and then do the switchover. For statement-based replication, this works fine, but for row-based replication, this can work up to a certain degree. Row-based replication enables extra columns to exist at the end of the table, so as long as it can write the first columns, it will be fine. First apply these setting to all slaves, then failover to one of the slaves and then implement the change to the master and attach that as a slave. If your modification involves inserting or removing a column in the middle of the table, it will work with row-based replication.

Operation

During the maintenance window, we do not want to have application traffic on the database. Sometimes it is hard to shut down all applications spread over the whole company. Alternatively, we want to allow only some specific hosts to access MySQL from remote (for example the monitoring system or the backup server). For this purpose, we can use the Linux packet filtering. To see what packet filtering rules are available, we can run the following command:

iptables -L INPUT -v

To close the MySQL port on all interfaces we use:

iptables -A INPUT -p tcp --dport mysql -j DROP

and to open the MySQL port again after the maintenance window:

iptables -D INPUT -p tcp --dport mysql -j DROP

For those without root access, you can change max_connection to 1 or 'skip networking'.

Logging

To get the logging process started, use the tee command at the MySQL client prompt, like this:

mysql> tee /tmp/my.out;

That command tells MySQL to log both the input and output of your current MySQL login session to a file named /tmp/my.out .Then execute your script file with source command.

To get a better idea of your execution times, you can combine it with the profiler feature. Start the profiler with

SET profiling = 1;

Then execute your Query with

SHOW PROFILES;

you see a list of queries the profiler has statistics for. So finally, you choose which query to examine with

SHOW PROFILE FOR QUERY 1; Schema migration tools

Many times, a straight ALTER on the master is not possible - most of the cases it causes lag on the slave, and this may not be acceptable to the applications. What can be done, though, is to execute the change in a rolling mode. You can start with slaves and, once the change is applied to the slave, migrate one of the slaves as a new master, demote the old master to a slave and execute the change on it.

A tool that may help with such a task is Percona’s pt-online-schema-change. Pt-online-schema-change is straightforward - it creates a temporary table with the desired new schema (for instance, if we added an index, or removed a column from a table). Then, it creates triggers on the old table. Those triggers are there to mirror changes that happen on the original table to the new table. Changes are mirrored during the schema change process. If a row is added to the original table, it is also added to the new one. It emulates the way that MySQL alters tables internally, but it works on a copy of the table you wish to alter. It means that the original table is not locked, and clients may continue to read and change data in it.

Related resources  Schema Management Tips for MySQL & MariaDB  Online schema change for MySQL & MariaDB - comparing GitHub’s gh-ost vs pt-online-schema-change  Online schema change with gh-ost - throttling and changing configuration at runtime  How to Overcome Accidental Data Deletion in MySQL & MariaDB  How to Recover MySQL Galera Cluster from an Asynchronous Slave?

Likewise, if a row is modified or deleted on the old table, it is also applied in the new table. Then, a background process of copying data (using LOW_PRIORITY INSERT) between old and new table begins. Once data has been copied, RENAME TABLE is executed.

Another intresting tool is gh-ost. Gh-ost creates a temporary table with the altered schema, just like pt-online-schema-change does. It executes INSERT queries, which use the following pattern to copy data from old to new table. Nevertheless it does not use triggers. Unfortunately triggers may be the source of many limitations. gh-ost uses the binary log stream to capture table changes and asynchronously applies them onto the ghost table. Once we verified that gh-ost can execute our schema change correctly, it’s time to actually execute it. Keep in mind that you may need to manually drop old tables that were created by gh-ost during the process of testing the migration. You can also use --initially-drop-ghost-table and --initially-drop-old-table flags to ask gh-ost to do it for you. The final command to execute is exactly the same as we used to test our change, we just added --execute to it.

pt-online-schema-change and gh-ost are very popular among Galera users. Nevertheless Galera has some additional options.The two methods Total Order Isolation (TOI) and Rolling Schema Upgrade (RSU) have both their pros and cons.

TOI - This is the default DDL replication method. The node that originates the writeset detects DDL at parsing time and sends out a replication event for the SQL statement before even starting the DDL processing. Schema upgrades run on all cluster nodes in the same total order sequence, preventing other transactions from committing for the duration of the operation. This method is good when you want your online schema upgrades to replicate through the cluster and don’t mind locking the entire table (similar to how default schema changes happened in MySQL).

SET GLOBAL wsrep_OSU_method='TOI';

RSU - perfom the schema upgrades locally. In this method, your writes are affecting only the node on which they are run. The changes do not replicate to the rest of the cluster.This method is good for non-conflicting operations and it will not slow down the cluster.

SET GLOBAL wsrep_OSU_method='RSU';

While the node processes the schema upgrade, it desynchronizes with the cluster. When it finishes processing the schema upgrade, it applies delayed replication events and synchronizes itself with the cluster. This could be a good option to run heavy index creations.

Conclusion

We presented here several different methods that may help you with planning your schema changes. Of course it all depends on your application and business requirements. You can design your change plan, perform necessary tests, but there is still a small chance that something will go wrong. According to Murphy’s law - “things will go wrong in any given situation, if you give them a chance”. So make sure you try out different ways of performing these changes, and pick the one that you are the most comfortable with.

Tags:  MySQL MariaDB schema change tips mysql replication galera cluster

Porting this Oracle MySQL feature to MariaDB would be great ;-)

Oracle has done a great technical work with MySQL. Specifically a nice job has been done around security. There is one useful feature that exists in Oracle MySQL and that currently does not exist in MariaDB. Oracle MySQL offers the possibility from within the server to generate asymetric key pairs. It is then possible use [...]

Why MySQL Stored Procedures, Functions and Triggers Are Bad For Performance

MySQL stored procedures, functions and triggers are tempting constructs for application developers. However, as I discovered, there can be an impact on database performance when using MySQL stored routines. Not being entirely sure of what I was seeing during a customer visit, I set out to create some simple tests to measure the impact of triggers on database performance. The outcome might surprise you.

Why stored routines are not optimal performance wise: short version

Recently, I worked with a customer to profile the performance of triggers and stored routines. What I’ve learned about stored routines: “dead” code (the code in a branch which will never run) can still significantly slow down the response time of a function/procedure/trigger. We will need to be careful to clean up what we do not need.

Profiling MySQL stored functions

Let’s compare these four simple stored functions (in MySQL 5.7):

Function 1:

CREATE DEFINER=`root`@`localhost` FUNCTION `func1`() RETURNS int(11) BEGIN declare r int default 0; RETURN r; END

This function simply declares a variable and returns it. It is a dummy function

Function 2:

CREATE DEFINER=`root`@`localhost` FUNCTION `func2`() RETURNS int(11) BEGIN declare r int default 0; IF 1=2 THEN select levenshtein_limit_n('test finc', 'test func', 1000) into r; END IF; RETURN r; END

This function calls another function, levenshtein_limit_n (calculates levenshtein distance). But wait: this code will never run – the condition IF 1=2 will never be true. So that is the same as function 1.

Function 3:

CREATE DEFINER=`root`@`localhost` FUNCTION `func3`() RETURNS int(11) BEGIN declare r int default 0; IF 1=2 THEN select levenshtein_limit_n('test finc', 'test func', 1) into r; END IF; IF 2=3 THEN select levenshtein_limit_n('test finc', 'test func', 10) into r; END IF; IF 3=4 THEN select levenshtein_limit_n('test finc', 'test func', 100) into r; END IF; IF 4=5 THEN select levenshtein_limit_n('test finc', 'test func', 1000) into r; END IF; RETURN r; END

Here there are four conditions and none of these conditions will be true: there are 4 calls of “dead” code. The result of the function call for function 3 will be the same as function 2 and function 1.

Function 4:

CREATE DEFINER=`root`@`localhost` FUNCTION `func3_nope`() RETURNS int(11) BEGIN declare r int default 0; IF 1=2 THEN select does_not_exit('test finc', 'test func', 1) into r; END IF; IF 2=3 THEN select does_not_exit('test finc', 'test func', 10) into r; END IF; IF 3=4 THEN select does_not_exit('test finc', 'test func', 100) into r; END IF; IF 4=5 THEN select does_not_exit('test finc', 'test func', 1000) into r; END IF; RETURN r; END

This is the same as function 3 but the function we are running does not exist. Well, it does not matter as the

select does_not_exit  will never run.

So all the functions will always return 0. We expect that the performance of these functions will be the same or very similar. Surprisingly it is not the case! To measure the performance I used the “benchmark” function to run the same function 1M times. Here are the results:

+-----------------------------+ | benchmark(1000000, func1()) | +-----------------------------+ | 0 | +-----------------------------+ 1 row in set (1.75 sec) +-----------------------------+ | benchmark(1000000, func2()) | +-----------------------------+ | 0 | +-----------------------------+ 1 row in set (2.45 sec) +-----------------------------+ | benchmark(1000000, func3()) | +-----------------------------+ | 0 | +-----------------------------+ 1 row in set (3.85 sec) +----------------------------------+ | benchmark(1000000, func3_nope()) | +----------------------------------+ | 0 | +----------------------------------+ 1 row in set (3.85 sec)

As we can see func3 (with four dead code calls which will never be executed, otherwise identical to func1) runs almost 3x slower compared to func1(); func3_nope() is identical in terms of response time to func3().

Visualizing all system calls from functions

To figure out what is happening inside the function calls I used performance_schema / sys schema to create a trace with ps_trace_thread() procedure

  1. Get the thread_id for the MySQL connection:
    mysql> select THREAD_ID from performance_schema.threads where processlist_id = connection_id(); +-----------+ | THREAD_ID | +-----------+ | 49 | +-----------+ 1 row in set (0.00 sec)
  2. Run ps_trace_thread in another connection passing the thread_id=49:
    mysql> CALL sys.ps_trace_thread(49, concat('/var/lib/mysql-files/stack-func1-run1.dot'), 10, 0, TRUE, TRUE, TRUE); +--------------------+ | summary | +--------------------+ | Disabled 0 threads | +--------------------+ 1 row in set (0.00 sec) +---------------------------------------------+ | Info | +---------------------------------------------+ | Data collection starting for THREAD_ID = 49 | +---------------------------------------------+ 1 row in set (0.00 sec)
  3. At that point I switched to the original connection (thread_id=49) and run:
    mysql> select func1(); +---------+ | func1() | +---------+ | 0 | +---------+ 1 row in set (0.00 sec)
  4. The sys.ps_trace_thread collected the data (for 10 seconds, during which I ran the select func1() ), then it finished its collection and created the dot file:
    +-----------------------------------------------------------------------+ | Info | +-----------------------------------------------------------------------+ | Stack trace written to /var/lib/mysql-files/stack-func3nope-new12.dot | +-----------------------------------------------------------------------+ 1 row in set (9.21 sec) +-------------------------------------------------------------------------------+ | Convert to PDF | +-------------------------------------------------------------------------------+ | dot -Tpdf -o /tmp/stack_49.pdf /var/lib/mysql-files/stack-func3nope-new12.dot | +-------------------------------------------------------------------------------+ 1 row in set (9.21 sec) +-------------------------------------------------------------------------------+ | Convert to PNG | +-------------------------------------------------------------------------------+ | dot -Tpng -o /tmp/stack_49.png /var/lib/mysql-files/stack-func3nope-new12.dot | +-------------------------------------------------------------------------------+ 1 row in set (9.21 sec) Query OK, 0 rows affected (9.45 sec)

I repeated these steps for all the functions above and then created charts of the commands.

Here are the results:

Func1()

Func2()

Func3()

 

As we can see there is a sp/jump_if_not call for every “if” check followed by an opening tables statement (which is quite interesting). So parsing the “IF” condition made a difference.

For MySQL 8.0 we can also see MySQL source code documentation for stored routines which documents how it is implemented. It reads:

Flow Analysis Optimizations
After code is generated, the low level sp_instr instructions are optimized. The optimization focuses on two areas:

Dead code removal,
Jump shortcut resolution.
These two optimizations are performed together, as they both are a problem involving flow analysis in the graph that represents the generated code.

The code that implements these optimizations is sp_head::optimize().

However, this does not explain why it executes “opening tables”. I have filed a bug.

When slow functions actually make a difference

Well, if we do not plan to run one million of those stored functions we will never even notice the difference. However, where it will make a difference is … inside a trigger. Let’s say that we have a trigger on a table: every time we update that table it executes a trigger to update another field. Here is an example: let’s say we have a table called “form” and we simply need to update its creation date:

mysql> update form set form_created_date = NOW() where form_id > 5000; Query OK, 65536 rows affected (0.31 sec) Rows matched: 65536 Changed: 65536 Warnings: 0

That is good and fast. Now we create a trigger which will call our dummy func1():

CREATE DEFINER=`root`@`localhost` TRIGGER `test`.`form_AFTER_UPDATE` AFTER UPDATE ON `form` FOR EACH ROW BEGIN declare r int default 0; select func1() into r; END

Now repeat the update. Remember: it does not change the result of the update as we do not really do anything inside the trigger.

mysql> update form set form_created_date = NOW() where form_id > 5000; Query OK, 65536 rows affected (0.90 sec) Rows matched: 65536 Changed: 65536 Warnings: 0

Just adding a dummy trigger will add 2x overhead: the next trigger, which does not even run a function, introduces a slowdown:

CREATE DEFINER=`root`@`localhost` TRIGGER `test`.`form_AFTER_UPDATE` AFTER UPDATE ON `form` FOR EACH ROW BEGIN declare r int default 0; END mysql> update form set form_created_date = NOW() where form_id > 5000; Query OK, 65536 rows affected (0.52 sec) Rows matched: 65536 Changed: 65536 Warnings: 0

Now, lets use func3 (which has “dead” code and is equivalent to func1):

CREATE DEFINER=`root`@`localhost` TRIGGER `test`.`form_AFTER_UPDATE` AFTER UPDATE ON `form` FOR EACH ROW BEGIN declare r int default 0; select func3() into r; END mysql> update form set form_created_date = NOW() where form_id > 5000; Query OK, 65536 rows affected (1.06 sec) Rows matched: 65536 Changed: 65536 Warnings: 0

However, running the code from the func3 inside the trigger (instead of calling a function) will speed up the update:

CREATE DEFINER=`root`@`localhost` TRIGGER `test`.`form_AFTER_UPDATE` AFTER UPDATE ON `form` FOR EACH ROW BEGIN declare r int default 0; IF 1=2 THEN select levenshtein_limit_n('test finc', 'test func', 1) into r; END IF; IF 2=3 THEN select levenshtein_limit_n('test finc', 'test func', 10) into r; END IF; IF 3=4 THEN select levenshtein_limit_n('test finc', 'test func', 100) into r; END IF; IF 4=5 THEN select levenshtein_limit_n('test finc', 'test func', 1000) into r; END IF; END mysql> update form set form_created_date = NOW() where form_id > 5000; Query OK, 65536 rows affected (0.66 sec) Rows matched: 65536 Changed: 65536 Warnings: 0

Memory allocation

Potentially, even if the code will never run, MySQL will still need to parse the stored routine—or trigger—code for every execution, which can potentially lead to a memory leak, as described in this bug.

Conclusion

Stored routines and trigger events are parsed when they are executed. Even “dead” code that will never run can significantly affect the performance of bulk operations (e.g. when running this inside the trigger). That also means that disabling a trigger by setting a “flag” (e.g.

if @trigger_disable = 0 then ... ) can still affect performance of bulk operations.

The post Why MySQL Stored Procedures, Functions and Triggers Are Bad For Performance appeared first on Percona Database Performance Blog.

Pages