Subscribe: Planet MySQL
Added By: Feedage Forager Feedage Grade B rated
Language: English
Rate this Feed
Rate this feedRate this feedRate this feedRate this feedRate this feed
Rate this feed 1 starRate this feed 2 starRate this feed 3 starRate this feed 4 starRate this feed 5 star

Comments (0)

Feed Details and Statistics Feed Statistics
Preview: Planet MySQL

Planet MySQL

Planet MySQL -

Published: Tue, 20 Mar 2018 23:37:43 +0000


Using Different Mount Points on PMM Docker Deployments

Tue, 20 Mar 2018 21:02:38 +0000

In this blog post, we’ll see how to use different mount points on PMM Docker deployments (Percona Monitoring and Management). This is useful if you want to use other mount points for the different directories, or even if you want to use a custom path that is not bound to Docker’s volumes directory (which is /var/lib/docker/volumes/ by default) within the same mount point. There are two ways in which you can achieve this: using symlinks after the pmm-data container is created modifying the docker create command to use different directories In the following examples, /pmm/ is used as the new base directory. One can, of course, choose different directories for each if needed. Also, remember to be aware of any SELinux or AppArmor policies you may have in place. Using symlinks For this, we need to follow these steps: Create the needed directories Create the pmm-data container Move contents from default Docker paths to the desired paths Create symlinks that point to the moved directories Let’s see this with some commands and outputs. In this example, we will use /pmm/ as if it were the new mount point: shell> mkdir /pmm/opt/ shell> mkdir /pmm/opt/prometheus shell> mkdir /pmm/var/lib/ shell> docker create   -v /opt/prometheus/data   -v /opt/consul-data   -v /var/lib/mysql   -v /var/lib/grafana   --name pmm-data   percona/pmm-server:1.7.0 /bin/true 4589cd1bf8ce365f8f62eab9f415eb14f1ce3a76b0123b7aad42e93385455303 shell> docker inspect pmm-data | egrep "Source|Destination" "Source": "/var/lib/docker/volumes/a191331f6be1a177003ef2fdeee53f92fc190dc67b0c402ee7b47b4461ffa522/_data", "Destination": "/opt/prometheus/data", "Source": "/var/lib/docker/volumes/7208317edff4565f649df294cfb05fc1888e6ab817c18abc5f036c419e364d4b/_data", "Destination": "/var/lib/grafana", "Source": "/var/lib/docker/volumes/547b3f083a0a33b6cd75eb72e2cc25c383f5d4db2d8a493b25eb43499e2f5807/_data", "Destination": "/var/lib/mysql", "Source": "/var/lib/docker/volumes/7473ac5d2dac4440ac94fae2faf4a63af95baaabed4b14d9414f499ae9b5761d/_data", "Destination": "/opt/consul-data", shell> DOCKER_CONSUL_DATA="/var/lib/docker/volumes/7473ac5d2dac4440ac94fae2faf4a63af95baaabed4b14d9414f499ae9b5761d/_data" shell> DOCKER_PROMETHEUS_DATA="/var/lib/docker/volumes/a191331f6be1a177003ef2fdeee53f92fc190dc67b0c402ee7b47b4461ffa522/_data" shell> DOCKER_GRAFANA_DATA="/var/lib/docker/volumes/7208317edff4565f649df294cfb05fc1888e6ab817c18abc5f036c419e364d4b/_data" shell> DOCKER_MYSQL_DATA="/var/lib/docker/volumes/547b3f083a0a33b6cd75eb72e2cc25c383f5d4db2d8a493b25eb43499e2f5807/_data" shell> mv $DOCKER_CONSUL_DATA /pmm/opt/consul-data shell> mv $DOCKER_PROMETHEUS_DATA /pmm/opt/prometheus/data shell> mv $DOCKER_GRAFANA_DATA /pmm/var/lib/grafana shell> mv $DOCKER_MYSQL_DATA /pmm/var/lib/mysql shell> ln -s /pmm/opt/consul-data $DOCKER_CONSUL_DATA shell> ln -s /pmm/opt/prometheus/data $DOCKER_PROMETHEUS_DATA shell> ln -s /pmm/var/lib/grafana $DOCKER_GRAFANA_DATA shell> ln -s /pmm/var/lib/mysql $DOCKER_MYSQL_DATA After this, we can start the pmm-server container (see below). Modifying the docker create command For this, we need to follow these other steps: Create the needed directories Create a temporary pmm-data container Copy its contents to the new locations, and delete it (the temporary container) Create the permanent pmm-data container with the modified paths (-v arguments) Fix ownership of files in the copied directories (to avoid errors when starting the pmm-server container later on) Let’s see this in practical terms again, assuming we want to use the /pmm/ mount point. shell> mkdir /pmm/opt/ shell> mkdir /pmm/opt/prometheus shell> mkdir /pmm/var/lib/ shell> docker create   -v /opt/prometheus/data   -v /opt/consul-data   -v /var/lib/mysql   -v /var/lib/grafana   --name pmm-data-temporary   percona/pmm-server:1.7.0 /bin/true 76249e1830c2a9c320466e41a454e9e80bf513e9b046e795ec41a33d75df5830 shell> docker cp pmm-data-temporary:/opt/prometheus/data /pmm/opt/prometheus/data sh[...]

Migrating to MySQL 8.0 without breaking old application

Tue, 20 Mar 2018 18:53:28 +0000

Recently I blogged about the new default authentication plugin in MySQL 8.0 and I got some comments complaining that this new authentication plugin is breaking half of applications. So first of all, if you are using an old connector or a connector (like the one for Go) not yet supporting caching_sha2_passwordas authentication plugin, you are still able to use the oldone. If you have created a new user for your application not supporting the new authentication method, you just have to run the following command (please use the right user account): ALTER USER 'username'@'hostname' IDENTIFIED WITH 'mysql_native_password' BY 'password'; Let’s got back to the blog post now. Situation The exercise of this blog consists in the migration of the MySQL server 5.5.59 used by Druapl 6.2  to MySQL 8.0 without migrating to the latest Drupal version. This is what we have now: So far so good MySQL Upgrade In the MySQL Manual, we propose 2 different strategies: Logical Upgrade In-Place Upgrade The logical method consists of making a logical dump and I restore it, I won’t cover it here. The in-place method is as far as I know the most common one. However, there is something very important that people tend to forget: “Upgrade that skips versions is not supported. For example, upgrading directly from MySQL 5.6 to 8.0 is not supported.” So know that this is clarified again, let’s continue with our plan. As we are using 5.5.59, the latest 5.5 version, we don’t need to upgrade the binaries to the latest 5.5, if we would use a older version of 5.5, I would have recommended to upgrade first to the latest version of the same major version too. Our first step is then to put our site in maintenance and then upgrade to the latest 5.6. MySQL 5.6 # yum update --enablerepo=mysql56-community --disablerepo=mysql57-community mysql-community-server ... Updated: mysql-community-server.x86_64 0:5.6.39-2.el7 Dependency Updated: mysql-community-client.x86_64 0:5.6.39-2.el7 mysql-community-common.x86_64 0:5.6.39-2.el7 mysql-community-libs.x86_64 0:5.6.39-2.el7 Complete! Perfect, let’s run the mandatory mysql_upgrade command: Looking for 'mysql' as: mysql Looking for 'mysqlcheck' as: mysqlcheck Running 'mysqlcheck with default connection arguments Running 'mysqlcheck with default connection arguments mysql.columns_priv OK mysql.db OK mysql.event OK ... mysql.time_zone_transition_type OK mysql.user OK Running 'mysql_fix_privilege_tables'... Running 'mysqlcheck with default connection arguments Running 'mysqlcheck with default connection arguments drupal.access OK drupal.actions OK drupal.actions_aid OK ... drupal.watchdog OK OK We are good, let’s put back the site online and check the home page again: MySQL 5.7 OK, let’s move on and upgrade to the latest 5.7: # yum upgrade mysql-community-server Dependency Installed: mysql-community-libs-compat.x86_64 0:5.7.21-1.el7 Updated: mysql-community-server.x86_64 0:5.7.21-1.el7 Dependency Updated: mysql-community-client.x86_64 0:5.7.21-1.el7 mysql-community-common.x86_64 0:5.7.21-1.el7 mysql-community-libs.x86_64 0:5.7.21-1.el7 Complete! # mysql_upgrade Checking if update is needed. Checking server version. Running queries to upgrade MySQL server. Checking system database. mysql.columns_priv OK mysql.db OK mysql.engine_cost [...]

Webinar Thursday, March 22, 2018: Percona XtraDB Cluster 5.7 with ProxySQL for Your MySQL High Availability and Clustering Needs

Tue, 20 Mar 2018 18:16:52 +0000

Please join Percona’s Ramesh Sivaraman (QA Engineer) and Krunal Bauskar (Software Engineer, Percona XtraDB Cluster Lead) as they present Percona XtraDB Cluster 5.7 with ProxySQL for Your MySQL High Availability and Clustering Needs on Thursday, March 22, 2018 at 8:30 am PDT (UTC-7) / 11:30 am EDT (UTC-4). Percona has developed Percona XtraDB Cluster (based on Galera Cluster) and integrated it with ProxySQL to address MySQL high availability and clustering. These two products working together provide a great out-of-the-box synchronous replication setup. In this webinar, we’ll look at why this is a great solution, and what types of deployments you should consider using it in. Register for the webinar now. Krunal is Percona XtraDB Cluster lead at Percona. He is responsible for day-to-day Percona XtraDB Cluster development, what goes into Percona XtraDB Cluster, bug fixes, releases, etc. Before joining Percona, he worked as part of InnoDB team at MySQL/Oracle. He authored most of the temporary table revamp work, undo log truncate, atomic truncate and a lot of other features. In the past, he was associated with Yahoo! Labs researching big data problems, and a database startup that is now part of Teradata. His interests mainly include data-management at any scale and he has been practicing it for more than decade. Ramesh joined the Percona QA Team in March 2014. Prior to joining Percona, he provided MySQL database support to various service- and product-based Internet companies. Ramesh’s professional interests include writing shell/Perl scripts to automate routine tasks, and new technology. Ramesh lives in Kerala, the southern part of India, close to his family. The post Webinar Thursday, March 22, 2018: Percona XtraDB Cluster 5.7 with ProxySQL for Your MySQL High Availability and Clustering Needs appeared first on Percona Database Performance Blog.

Updated: Become a ClusterControl DBA: Making your DB components HA via Load Balancers

Tue, 20 Mar 2018 11:21:31 +0000

Choosing your HA topology There are various ways to retain high availability with databases. You can use Virtual IPs (VRRP) to manage host availability, you can use resource managers like Zookeeper and Etcd to (re)configure your applications or use load balancers/proxies to distribute the workload over all available hosts. The Virtual IPs need either an application to manage them (MHA, Orchestrator), some scripting (Keepalived, Pacemaker/Corosync) or an engineer to manually fail over and the decision making in the process can become complex. The Virtual IP failover is a straightforward and simple process by removing the IP address from one host, assigning it to another and use arping to send a gratuitous ARP response. In theory a Virtual IP can be moved in a second but it will take a few seconds before the failover management application is sure the host has failed and acts accordingly. In reality this should be somewhere between 10 and 30 seconds. Another limitation of Virtual IPs is that some cloud providers do not allow you to manage your own Virtual IPs or assign them at all. E.g., Google does not allow you to do that on their compute nodes. Resource managers like Zookeeper and Etcd can monitor your databases and (re)configure your applications once a host fails or a slave gets promoted to master. In general this is a good idea but implementing your checks with Zookeeper and Etcd is a complex task. A load balancer or proxy will sit in between the application and the database host and work transparently as if the client would connect to the database host directly. Just like with the Virtual IP and resource managers, the load balancers and proxies also need to monitor the hosts and redirect the traffic if one host is down. ClusterControl supports two proxies: HAProxy and ProxySQL and both are supported for MySQL master-slave replication and Galera cluster. HAProxy and ProxySQL both have their own use cases, we will describe them in this post as well. Why do you need a load balancer? In theory you don’t need a load balancer but in practice you will prefer one. We’ll explain why. If you have virtual IPs setup, all you have to do is point your application to the correct (virtual) IP address and everything should be fine connection wise. But suppose you have scaled out the number of read replicas, you might want to provide virtual IPs for each of those read replicas as well because of maintenance or availability reasons. This might become a very large pool of virtual IPs that you have to manage. If one of those read replicas had a failure, you need to re-assign the virtual IP to another host or else your application will connect to either a host that is down or in worst case, a lagging server with stale data. Keeping the replication state to the application managing the virtual IPs is therefore necessary. Also for Galera there is a similar challenge: you can in theory add as many hosts as you’d like to your application config and pick one at random. The same problem arises when this host is down: you might end up connecting to an unavailable host. Also using all hosts for both reads and writes might also cause rollbacks due to the optimistic locking in Galera. If two connections try to write to the same row at the same time, one of them will receive a roll back. In case your workload has such concurrent updates, it is advised to only use one node in Galera to write to. Therefore you want a manager that keeps track of the internal state of your database cluster. Both HAProxy and ProxySQL will offer you the functionality to monitor the MySQL/MariaDB database hosts and keep state of your cluster and its topology. For replication setups, in case a slave replica is down, both HAProxy and ProxySQL can redistribute the connections to another host. But if a replication master is down, HAProxy will deny the connection and ProxySQL will give back a proper error to the client. For Galera setups, both load balancers can elect a maste[...]

MySQL Yum repo setups for commercial and community use cases

Tue, 20 Mar 2018 03:50:34 +0000

MySQL Package Management Options In this blog we will explore some interesting ways to install MySQL Community and Enterprise Edition binaries using your associated Linux package manager.  In this case we’ll look mostly at the Yum package manager on Oracle Linux.  The benefit of these package managers is that you can install software packages easily,… Read More »

ProxySQL new routing algorithm to handle thousands of hundreds of schemas/shards

Tue, 20 Mar 2018 00:30:00 +0000

ProxySQL is a service designed to scale and handle traffic in very large setups. In the past we already showed examples of how ProxySQL can handle thousands of MySQL servers, and millions (yes, millions!) of distinct users. Although, recently a new challenge raised: can ProxySQL perform routing based on MySQL schemaname for a vary large number of schemas? We know that in production setups ProxySQL is already performing routing to MySQL servers based on schemaname for few hundreds schemas, but can it handle thousands or more? Technically there is no limit on how many shards ProxySQL can handle, as long as there are rules for them. Tibor Korocz already pointed in a blog post that more query rules can affect performance. In case of query routing based on schemaname no regular expressions are involved (therefore each rule is not expensive), but the same principle applies: the more rules need to be evaluated, longer it takes to make a decision (in this case to perform the routing). Scope of this blog post is to understand the performance implication of performing routing based on the number of rules. To simplify the setup to run some benchmark, we used only 1 MySQL server as backend, and we created 50000 schemas: for i in `seq 10001 60000` ; do echo "CREATE DATABASE IF NOT EXISTS shard_$i;" done | mysql -u root To run the benchmark we used a various number of rules. For every test, we configured ProxySQL setting the right number of rules: for 100 schemas we created 100 rules, for 1000 schema 1000 rules, etc. For example, for 200 rules we used this configuration: ( echo "DELETE FROM mysql_query_rules; INSERT INTO mysql_query_rules (active,username,cache_ttl) VALUES (1,\"sbtest\",120000);" for i in `seq 10001 10200` ; do echo "INSERT INTO mysql_query_rules (active,username,schemaname,destination_hostgroup,apply) VALUES (1,\"sbtest\",\"shard_$i\",1,1);" done echo "LOAD MYSQL QUERY RULES TO RUNTIME;" ) | mysql -u admin -padmin -h -P6032 We then ran queries as the following: for j in `seq 1 50000` ; do echo "USE shard_$(($RANDOM%200+10001))" ; echo "SELECT 1;" done | mysql -u sbtest -psbtest -h -P6033 --ssl-mode=disabled -NB > /dev/null It is relevant to note that we aren’t interested in the total execution time (this is why we used this simple one-liner to generate traffic), but in the time spent inside the Query Processor. Because the execution time of the queries is not relevant, we also configured the query cache. To compute the time spent in the Query Processor we set variable mysql-stats_time_query_processor='true' and computed the value of status variable Query_Processor_time_nsec. This status variable measures the time spent inside the Query Processor, in nanoseconds. As we expected, the more query rules ProxySQL needs to evaluate, the longer it takes to compute the destination hostgroup for that schemaname. This results in latency before executing the query: The graph above shows that for 10 rules the average latency is around 1us (microsecond), for 100 rules the average latency is 2us , and that it gradually grows to 6us for 500 rules and 256us for 20000 rules. 6us average latency for 500 rules doesn’t seem to be an issue, but 256us (0.256ms) average latency for 20000 does seem like an issue. What is also important to note is that these values are the average values for all the shards. Routing decision for some shards are faster than others, depending in which order the rules are written. In fact, with 20000 rules the average latency for shard_10001 is 0.89us , while the average latency for shard_30000 is 581.1us ! Surely, this doesn’t seem a scalable solution. MySQL Query Rules Fast Routing ProxySQL 1.4.7 introduces a new routing capability that enhances what already configurable in mysql_query_rules. The new capability is configurable using table mysql_query_rules_fast_routing. This table is simpler compared to mysql_query_rules, and its purpose is simpl[...]

Percona XtraDB Cluster, MySQL Asynchronous Replication and log-slave-updates

Tue, 20 Mar 2018 00:06:06 +0000

Recently, I’ve been looking into issues with the interactions between MySQL asynchronous replication and Galera replication. In this blog post, I’d like to share what I’ve learned. MySQL asynchronous replication and Galera replication interactions are complicated due to the number of factors involved (Galera replication vs. asynchronous replication, replication filters, and row-based vs. statement-based replication). So as a start, I’ll look at an issue that came up with setting up an asynchronous replication channel between two Percona XtraDB Cluster (PXC) clusters. Here’s a view of the desired topology: The Problem We want to set up an asynchronous replication channel between two PXC clusters. We also set log-slave-updates on the async slave (PXC node 2a in the topology diagram). This is an interesting configuration and results in unexpected behavior as the replication depends on the node where the operation was performed. Let’s use CREATE TABLE as an example. Run CREATE TABLE on Node 1a.  The table replicates to Node 1b, but not to the nodes in cluster 2. Run CREATE TABLE on Node 1b. The table replicates to all nodes (both cluster 1 and cluster 2). Some background information Understanding the problem requires some knowledge of MySQL threads. However, as a simplification, I’ll group the threads into three groups: Main MySQL Client Thread: This thread handles the requests for the client connection (here the client is an external entity). Async Replication Threads: There are multiple threads in use here, some handle I/O, and some apply the updates, but we will view them as a single entity for simplicity. Galera Threads: There are also multiple threads here, similar to the Async Replication Threads. (The name Galera here refers to the underlying replication technology used by PXC.) For more information on MySQL threads, see Why is the data not replicating? In the first case (CREATE TABLE executed on Node1a) The table is replicated from Node1a -> Node 1b via Galera replication. The table is not replicated because the async replication threads are not picking up the changes. In the second case (CREATE TABLE executed on Node 1b) The table is replicated from Node1b -> Node 1a via Galera replication. The table is replicated from Node1b -> Node 2a via async replication. This differs from the first case because the statement is executed on the Main MySQL client thread.  The async replication threads pick up the changes and send them to Node 2a. The table is replicated from Node 2a -> Node 2b via Galera replication because log-slave-updates has been enabled on Node2a. That last part is the important bit. We can view the Galera replication threads as another set of asynchronous replication threads. So if data is coming in via async replication, they have to be made visible to Galera by log-slave-updates.  This is true in the other direction also: log-slave-updates must be enabled for Galera to supply data to async replication. This is very similar to chained replication The Solution In this scenario, the answer is to set log-slave-updates on Node 1b (the async master) and on Node 2a (the async slave). We set log-slave-updates on node 1b to allow the async threads to pickup the changes from the Galera threads. We set log-slave-updates on node 2a to allow the Galera threads to pickup the changes from the async threads. Starting with PXC 5.7.17, calling START SLAVE on a PXC node will return an error unless log-slave-updates is enabled. You must enable log-slave-updates on the node for data to be transferred between Galera and asynchronous replication. Recommendations/Best Practices If you plan to use MySQL asynchronous replication with Percona XtraDB Cluster (either as async master or slave), we rec[...]

How to Speed Up Pattern Matching Queries

Mon, 19 Mar 2018 23:07:12 +0000

From time to time I see pattern matching queries with conditions that look like this: “where fieldname like ‘%something%’ “. MySQL cannot use indexes for these kinds of queries, which means it has to do a table scan every single time. (That’s really only half true — there are the FullText indexes. In another blog post I will cover FullText indexes as well.) I recently was trying to find a solution, and my friend Charles Nagy reminded me of Trigrams. Let me show you the Trigram of the name Daniel: daniel: dan ani nie iel But how is this useful? Let me show you an example. You have the following email schema: CREATE TABLE `email` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `email` varchar(120) COLLATE utf8_unicode_ci NOT NULL DEFAULT '', PRIMARY KEY (`id`), KEY `idx_email` (`email`) ) ENGINE=InnoDB AUTO_INCREMENT=318459 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci With data like these: +--------+-------------------------------+ | id | email | +--------+-------------------------------+ | 8392 | | | 238396 | | | 286517 | | | 137291 | | | 291984 | | ... +--------+-------------------------------+ And we are looking for email addresses like ‘%n.pierre%’: mysql> select * from email where email like '%n.pierre%'; +--------+-------------------------------+ | id | email | +--------+-------------------------------+ | 90587 | | | 133352 | | | 118937 | | | 118579 | | | 237928 | | | 59526 | | | 278384 | | | 58625 | | | 306718 | | | 200608 | | | 238396 | | +--------+-------------------------------+ 11 rows in set (0.12 sec) mysql> explain select * from email where email like '%n.pierre%'G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: email partitions: NULL type: index possible_keys: NULL key: idx_email key_len: 362 ref: NULL rows: 332475 filtered: 11.11 Extra: Using where; Using index 1 row in set, 1 warning (0.00 sec) mysql> show session status like '%handler%'; +----------------------------+--------+ | Variable_name | Value | +----------------------------+--------+ | Handler_commit | 1 | | Handler_delete | 0 | | Handler_discover | 0 | | Handler_external_lock | 2 | | Handler_mrr_init | 0 | | Handler_prepare | 0 | | Handler_read_first | 1 | | Handler_read_key | 1 | | Handler_read_last | 0 | | Handler_read_next | 318458 | | Handler_read_prev | 0 | | Handler_read_rnd | 0 | | Handler_read_rnd_next | 0 | | Handler_rollback | 0 | | Handler_savepoint | 0 | | Handler_savepoint_rollback | 0 | | Handler_update | 0 | | Handler_write | 0 | +----------------------------+--------+ 18 rows in set (0.00 sec) There are 11 email addresses, but it has to scan the whole index (318458 rows). That’s not good! Let’s try and make it better. Trigram table I created a table like this: CREATE TABLE `email_trigram` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `email_id` int(10) unsigned NOT NULL, `trigram` char(3) COLLATE utf8_unicode_ci NOT NULL DEFAULT '', PRIMARY KEY (`id`), KEY `idx_email_id` [...]

Getting Started with ProxySQL - MySQL & MariaDB Load Balancing Tutorial

Mon, 19 Mar 2018 13:45:46 +0000

We’re excited to announce a major update to our tutorial “Database Load Balancing for MySQL and MariaDB with ProxySQL” ProxySQL is a lightweight yet complex protocol-aware proxy that sits between the MySQL clients and servers. It is a gate, which basically separates clients from databases, and is therefore an entry point used to access all the database servers. In this new update we’ve… Updated the information about how to best deploy ProxySQL via ClusterControl Revamped the section “Getting Started with ProxySQL” Added a new section on Data Masking Added new frequently asked questions (FAQs) Load balancing and high availability go hand-in-hand. ClusterControl makes it easy to deploy and configure several different load balancing technologies for MySQL and MariaDB with a point-and-click graphical interface, allowing you to easily try them out and see which ones work best for your unique needs. ClusterControl Single Console for Your Entire Database Infrastructure Find out what else is new in ClusterControl Install ClusterControl for FREE ClusterControl for ProxySQL Included in ClusterControl Advanced and Enterprise, ProxySQL enables MySQL, MariaDB and Percona XtraDB database systems to easily manage intense, high-traffic database applications without losing availability. ClusterControl offers advanced, point-and-click configuration management features for the load balancing technologies we support. We know the issues regularly faced and make it easy to customize and configure the load balancer for your unique application needs. We know load balancing and support many different technologies. ClusterControl has many things preconfigured to get you started with a couple of clicks. If you run into challenged we also provide resources and on-the-spot support to help ensure your configurations are running at peak performance. Related resources  ProxySQL for ClusterControl  Video: ProxySQL ClusterControl Demonstration  ProxySQL: All the Severalnines Resources ClusterControl delivers on an array of features to help deploy and manage ProxySQL Advanced Graphical Interface - ClusterControl provides the only GUI on the market for the easy deployment, configuration and management of ProxySQL. Point and Click deployment - With ClusterControl you’re able to apply point and click deployments to MySQL, MySQL replication, MySQL Cluster, Galera Cluster, MariaDB, MariaDB Galera Cluster, and Percona XtraDB technologies, as well the top related load balancers with HAProxy, MaxScale and ProxySQL. Suite of monitoring graphs - With comprehensive reports you have a clear view of data points like connections, queries, data transfer and utilization, and more. Configuration Management - Easily configure and manage your ProxySQL deployments with a simple UI. With ClusterControl you can create servers, re-orientate your setup, create users, set rules, manage query routing, and enable variable configurations. Make sure to check out the update tutorial today! Tags:  proxysql MySQL MariaDB database load balancing high availability


Mon, 19 Mar 2018 11:58:00 +0000

MySQL is again a part of the FOSSASIA - Free Open Source Summit Asia  2018 which will be hold on March 22-25, 2018 in Singapore. Do not miss to come to our booth in exhibition area as well as do not miss MySQL related talks in Database track... See some of them below and at Fossasia schedule: Saturday, March 24, 2018: 10:00-11:00 MySQL Community Gathering - come to join to the MySQL meetup or other community! Come to Lounge area of the Summit to meet Ricky Setyawan. 11:30-11:55 Database Lightning Talk by Ricky Setyawan, the Principal Sales Consultant 17:00-17:25 Breaking through with MySQL 8.0, by Ricky Setyawan, the Principal Sales Consultant Sunday, March 25, 2018: 10:00-10:25 Atomic DDL in MySQL 8.0 by Shipra Jain, the Principal Software Engineer 10:30-10:55 MySQL for Distributed transaction and Usage of JSON as a fusion between SQL & NOSQL by Ajo Robert, the Principal Member Technical Staff  11:00-11:25 Histograms and the way we use it in MySQL 8.0 by Amit Bhattacharya, the Senior SW Development Manager  13:00-13:25 What's new in MySQL Optimizer 8.0 by Chaithra M G the Principal MySQL SW Developer 13:30-13:55 MySQL : Improving Connection Security by Harin Nalin Vadodara the Principal MTS 14:00-14:25 New Replication Features in MySQL 8.0 by Venkatesh Duggirala the Senior Principal Member of Technical Staff 14:30-14:55 Improved Error Logging in MySQL 8.0 by Praveenkumar Hulakund the SW Developer 15:00-15:25 The State of the Art on MySQL Group Replication by Hemant Dangi the Senior Technical Staff 15:30-15:55 Enhanced XA Support for Replication in MySQL-5.7 by Nisha Gopalakrishnan the Principal Member of Technical Staff 16:00-16:25 MySQL Performance Schema - A great insight of running MySQL Server by Manyank Prasad the Principal Member of Technical Staff We are looking forward to talking to you at our booth in expo area or at the Training room 2-1 where the Database track is hold. 

NDB Cluster and disk columns

Sat, 17 Mar 2018 01:10:00 +0000

NDB is mainly an In-memory database. We have however also the possibility tostore non-indexed columns on disk. This data uses a page cache as anyother normal disk-based DBMS.Interestingly with the increases of memory sizes one could think thatdisk data becomes less important for MySQL Cluster. The answer is actuallythe opposite.The reason is again the HW development. NDB is designed with predictablelatency as a very basic requirement. In the past disks meant hard drives. Accesstime to a hard disk was several milliseconds at best. Given that our requirementwas to handle complex transactions within 10 milliseconds disk data storagewas out of the question.Modern HW is completely different, they use SSD devices, first attached throughthe SATA interface that enabled up to around 500 MByte per second anda few thousand IO operations per second (IOPS). The second step was theintroduction of SSD devices on the PCI bus. This lifted the performance up to morethan  1 GByte per second. These devices are extremely small and still very powerful.I have an Intel NUC at home that has two of those devices.Thus the performance difference between disk storage and RAM has decreased.The next step on the way was to change the storage protocol and introduce NVMedevices. These still use the same HW, but use a new standard that is designed forthe new type of storage devices. Given those devices we have now the ability toexecute millions of IOPS on a standard server box with access times of a few tensof microseconds.For NDB this means that this HW fits very well into the NDB architecture. The workwe did on developing the Partial LCP algorithm did also a lot of work on improvingour disk data implementation. We see more and more people that use disk datacolumns in NDB.The next step is even more interesting, this will bring storage into the memory bus andaccess times of around one microsecond. For NDB this disk storage can be treated asmemory to start with, thus making it possible to soon have multiple TBytes of memoryin standard boxes.Thus HW development is making the NDB engine more and more interesting to use.One notable example that uses disk data columns in NDB is HopsFS. They use thedisk data columns to store small files in the meta data server of the HopsFSimplementation of the Hadoop HDFS Name Server. This means much fasteraccess to small files. The tests they did showed that they could handled hundredsof thousands of file reads and writes per second even using fairly standard SSD diskson the servers.The implementation of disk data in NDB is done such that each row can have threeparts. The fixed memory part that is accessed quickly using a row id. The variablesized part that is accessed through a pointer from the fixed size part.The disk columns are also accessed through a reference in the fixed size part. Thisreference is an 8-bit value that refers to the page id and page index of the diskcolumns.Before we can access those pages we go through a page cache. The page cache wasimplemented on caching techniques that was state of the art a few years ago.The idea is quite simple. The page cache uses a normal hot page queue. Pages arebrought up in this queue when they are accessed. A single access will bring it up,but to be more permanent in the page cache a page has to be accessed several times.Now each page is represented in those queues by a page state record. The basisof the page cache algorithm is that a page can be represented in a page staterecord even if the page is not in the page cache.NDB has a configuration variable called DiskPageBufferEntries, by default this isset to 10. It is the multiplication factor of how many more pages we havepage state records compared to the amount of pages we have in the page cache.So for example if we have set DiskPageBufferMemory to 10 G[...]

Discovering rows that have been updated since last checkpoint

Fri, 16 Mar 2018 22:27:00 +0000

One important problem that requires a solution is to decide whethera row has been updated since the last checkpoint or not.Most implementations use some kind of mechanism that requires extramemory resources and/or CPU resources to handle this.NDB uses the fact that each row is already stamped with a timestamp.The timestamp is what we call a global checkpoint id. A new globalcheckpoint is created about once every 2 seconds (can be faster orslower by configuration).Thus we will overestimate the number of rows written since last checkpointwith a little bit, but with checkpoints taking a few minutes, the extra overheadof this is only around 1%.Thus when we scan rows we check the global checkpoint id of the row, ifit is bigger than the global checkpoint that the last checkpoint had fullycovered we will write the row as changed since last checkpoint. Actuallywe also have the same information on the page level, thus we can checkthe page header and very quickly scan past an entire page if it hasn't beenupdated since last checkpoint.The same type of scanning is used also to bring a restarting node up tosynch with the live node. This algorithm has been present in NDB sinceMySQL 5.1.

Partial LCPs and Read-only tables

Fri, 16 Mar 2018 22:09:00 +0000

In MySQL Cluster 7.5 we use Complete Checkpoints. In MySQL Cluster 7.6we implement an approach where we only checkpoint a part of the databasein each checkpoint.A special case is a checkpoint of a table partition where no changesat all have happened since the last checkpoint. In this case we implementeda special optimisation such that it is not necessary to checkpoint anythingat all for this table partition. It is only necessary to write a new LCPcontrol file which is 4 kBytes in size for each table partition (can grow to8 kBytes if the recovery will require more than 980 checkpoints torecover.This means that if your database contains a large set of read-only tables,there will be no need to checkpoint those tables at all. This featureis used also when setting EnablePartialLcp to false.

Partial LCPs and disk space

Fri, 16 Mar 2018 22:00:00 +0000

One of the main objectives of the new Partial LCP algorithm in MySQLCluster 7.6 is to keep up with the development of modern HW.I have already described in previous blogs how Partial LCP can handlenicely even database sizes of 10 TBytes of memory with a very modestload on the disk devices.Now modern HW has shifted from using hard drives to using SSDs.The original approach in NDB is assuming that the checkpoints andREDO logs are stored on hard drives. In MySQL Cluster 7.5 thedisk space required for the REDO log is that it is a bit larger than theDataMemory size. The reason is that we want to survive also whenloading massive amounts of data.In MySQL Cluster 7.5 we cannot remove any checkpoint files untila checkpoint is fully completed. This means that we require around4x the memory size of disk space for REDO logs and checkpoints.With hard drives this is not a problem at all. As an example mydevelopment box has 32 GBytes of memory with 2 TByte of diskspace. Thus 64x more disk space compared to the memory space.With modern servers this size difference between memory anddisks is decreasing. For example many cloud VMs only havea bit more than 2x the disk size compared to the memory size.So one goal of MySQL Cluster 7.6 is to fit in much less diskspace.The aim is to solve this with a three-thronged approach.1) Partial LCP means that we can execute the checkpoints muchfaster. Since REDO logs only need to be kept for around twocheckpoints this means a significant decrease of size requirementsfor REDO logs. The aim is to only need around 10% of the diskspace of memory for the REDO logs. This work is not completedin 7.6.4. As usual there are no guarantees when this work will becompleted.2) Using Partial LCP we can throw away old LCP files as soonas we have created a new recoverable LCP for the table partition.Thus it is no longer necessary to store 2 LCPs on disk. At thesame time there is some overhead related to Partial LCPs. By defaultsetting this overhead is 50% plus a bit more. Thus we should alwaysfit within about 1.6x times the memory size.It is possible to set EnablePartialLcp to false, in this case allcheckpoints will be Complete Checkpoints. This means morewrites to disk for checkpoints, but it will decrease the storagespace to around the same as the memory size.3) Using CompressedLCP set to 1 we can decrease LCP storageby another factor of 2-3x (usually around 2.7x). This feature hasexisted for a long time in NDB.Thus it should be possible to significantly decrease the requirementson storage space when running NDB using MySQL Cluster 7.6.

NDB Checkpoints and research on In-Memory Databases

Fri, 16 Mar 2018 20:56:00 +0000

I just read an article called Low-Overhead Asynchronous Checkpointing inMain-Memory Database Systems. It was mentioned in a course in DatabaseSystems at Carnegie-Mellon University, see here.In MySQL Cluster 7.6.4 we released a new variant of our checkpointing designedfor modern HW with TBytes of main memory. I think studying this implementationwill be very worthwhile both for users of NDB, but also for researchers in DBMSimplementations. It implements a new class of checkpoint algorithms that is currentlya research topic in the database research community.It was interesting to compare our approach that I called Partial LCP with approachestaken by other commercial in-memory databases and with the approach presentedin the paper.LCP is Local CheckPoint which is the name we use for our checkpoint protocolin NDB.The course presents a number of ideal properties of a checkpoint implementation.The first property is that doesn't slow down regular transaction processing.In the case of NDB we execute checkpoints at a steady pace which consumesaround 5-10% of the available CPU resources. This will decrease even more withthe implementation in 7.6.The second is that it doesn't introduce any latency spikes.NDB checkpointing both new and old executes in steps of at most 10-20microseconds. So there will be extremely small impact on latency oftransactions due to checkpointing.The third property is that it doesn't require excessive memory overhead.NDB checkpointing consumes a configurable buffer in each database thread. Theideal size of this is around 1 MByte. In addition we have a REDO log buffer thatis usually a bit bigger than that. That is all there is to it. There is no extra memoryspace needed for checkpointing rows. The checkpointing performs a normal scanof the rows and copies the memory content to the buffer and as soon as the bufferis full it writes it to disk using sequential disk writes.It is fair to say that NDB does a good job in handling those ideal properties.The course presents two variants called fuzzy checkpoints and consistent checkpoints.The course defines fuzzy checkpoints as a checkpoint that can write uncommitteddata. I would normally use the term fuzzy checkpoint to mean that the checkpointis not consistent at a database level, but can still be consistent on a row basis.Actually NDB is a mix of the definition provided in the course material. It is aconsistent checkpoint for each row. But different rows can be consistent at verydifferent points in time. So on a row basis NDB is consistent, but at the databaselevel the checkpoint is fuzzy. Thus to perform recovery one needs to install thecheckpoint and then apply the REDO log to get a consistent checkpoint restored.Next the course presents two variants called Complete Checkpoints and DeltaCheckpoints. Complete Checkpoint means that the entire database is written ineach checkpoint. Delta Checkpoint means that only changes are written in acheckpoint.This is where MySQL Cluster 7.6 differs from 7.5. 7.5 uses a Complete Checkpointscheme. 7.6 uses a Partial Checkpoint scheme.In my view the NDB variant is a third variant which is not complete and not aDelta Checkpoint. Partial means that it writes the Delta, that is it writes all changessince the last checkpoint. But it does also write a Complete Checkpoint for a partof the database, thus the name Partial Checkpoint. Thus it is similar to anincremental backup scheme.NDB can divide the database up in up to 2048 parts, each checkpoint can write0 parts (only if no changes occurred in the table partition since last checkpoint).It can write 1 part if the number of writes is very small, it can write all 2048 partsif almost all rows have been updated and[...]

Percona Toolkit 3.0.8 Is Now Available

Fri, 16 Mar 2018 15:31:52 +0000

Percona announces the release of Percona Toolkit 3.0.8 on March 16, 2018. Percona Toolkit is a collection of advanced open source command-line tools, developed and used by the Percona technical staff, that are engineered to perform a variety of MySQL®, MongoDB® and system tasks that are too difficult or complex to perform manually. With over 1,000,000 downloads, Percona Toolkit supports Percona Server for MySQL, MySQL, MariaDB®, Percona Server for MongoDB and MongoDB. Percona Toolkit, like all Percona software, is free and open source. You can download packages from the website or install from official repositories. This release includes the following changes: New Features: PT-1500: Added the --output=secure-slowlog option to pt-query-digestto replace queries in the output by their fingerprints. This provides the ability to sanitize a slow log. Bug Fixes: PT-1492:  pt-kill in version 3.0.7 ignores the value of the --busy-time option PT-1503: The post-install script fails on VM due to improper UUID file detection Help us improve our software quality by reporting any bugs you encounter using our bug tracking system. The post Percona Toolkit 3.0.8 Is Now Available appeared first on Percona Database Performance Blog.

This Week in Data with Colin Charles 32: Stack Overflow Developer Survey, SCALE16x and Interesting MySQL 8 Version Numbers

Fri, 16 Mar 2018 14:51:12 +0000

Join Percona Chief Evangelist Colin Charles as he covers happenings, gives pointers and provides musings on the open source database community. SCALE16x is over. Percona had a large showing — both Peter Zaitsev & myself had talks, and the booth in the expo hall saw Rick Golba, Marc Sherwood, and Dave Avery also pop by. The lead scanner suggests we had several hundred conversations — read Dave Avery’s summary. My talk went well, though during Q&A the number of questions I got about MariaDB Server was quite interesting (considering it wasn’t part of my talk!). It is clear people are concerned about compatibility (because I spent close to 45 minutes after my talk answering Q&A outside too). I got to catch up with Dave Stokes and asked him why there were version numbers being skipped in MySQL 8 (as noted in last week’s column). Now there’s a blog post explaining it: MySQL 8.0: It Goes to 11!. It has to do with version number alignment across the product line. This week we saw something cool come out of Stack Overflow: their Developer Survey Results 2018. There were over 100,000 developers participating in this survey, a marked increase from 2017 when they only had 64,000. About 66,264 respondents answered the question about what databases they use. MySQL is by far the most popular with 58.7% of the respondents saying they use it. This is followed by PostgreSQL getting 32.9%, MongoDB getting 25.9%, and MariaDB 13.4%. I’m surprised that Amazon RDS/Aurora got 5.1%. In 2017, the first year they introduced the database component, only 29,452 respondents participated, with 55.6% using MySQL, 26.5% using PostgreSQL, and 21% using MongoDB (MariaDB was not broken out last year). When it came to the most “loved/dread/wanted” databases, apparently 62% of respondents loved PostgreSQL, with 58.8% loving Amazon RDS/Aurora, 55.1% MongoDB, 53.3% MariaDB Server, and 48.7% only loving MySQL. In terms of dread, 51.3% dread MySQL, while only 46.7% dread MariaDB; MongoDB has 44.9% dreading it, and PostgreSQL only 38%. As for the most wanted databases? 18.6% for MongoDB, 11.4% for PostgreSQL, 7.5% for MySQL, and 3.4% for MariaDB Server. It’s clear MongoDB topping the list ensures they have a lot to celebrate, as evidenced by this: Stack Overflow Research of 100,000 Developers Finds MongoDB is the Most Wanted Database. (In 2017, 60.8% loved PostgreSQL, 55% MongoDB, and 49.6% for MySQL; MySQL was the 3rd most dreaded database with 50.4%, followed by 45% for MongoDB, and 39.2% for PostgreSQL; as for the most wanted, MongoDB won with 20.8%, PostgreSQL got second at 11.5%, and MySQL 8.5%). So if Stack Overflow surveys are an indication of usage, MySQL is still way more popular than anything else, including MariaDB Server regardless of its current distribution. Speaking of MariaDB, the MariaDB Foundation now accepts donations in cryptocurrencies. MongoDB Evolved is something you should totally check out. I wish something like this exists for MySQL, since tonnes of people ask questions, e.g. “Does MySQL support transactions?”, etc. Releases ProxySQL 1.4.7 dbdeployer release candidate – as Giuseppe Maxia says, Let’s get the word out: #MySQL Sandbox is being replaced by #dbdeployer. Link List zheap: a storage engine to provide better control over bloat – should interest PostgreSQL folk Cache amplification Comparison of Window Functions & CTEs in MySQL 8 vs MariaDB Recap of M|18, the MariaDB User Conference – Video Recordings Now Available Upcoming appearances FOSSASIA 2018 – Singapore – March 22-25 2018 Open Source Data Centre Conference – Berlin, Germany [...]

Shinguz: MySQL Environment MyEnv 2.0.0 has been released

Thu, 15 Mar 2018 20:33:51 +0000

FromDual has the pleasure to announce the release of the new version 2.0.0 of its popular MySQL, Galera Cluster and MariaDB multi-instance environment MyEnv. The new MyEnv can be downloaded here. In the inconceivable case that you find a bug in the MyEnv please report it to the FromDual bug tracker. Any feedback, statements and testimonials are welcome as well! Please send them to Upgrade from 1.1.x to 2.0.0 # cd ${HOME}/product # tar xf /download/myenv-2.0.0.tar.gz # rm -f myenv # ln -s myenv-2.0.0 myenv Plug-ins If you are using plug-ins for showMyEnvStatus create all the links in the new directory structure: cd ${HOME}/product/myenv ln -s ../../utl/oem_agent.php plg/showMyEnvStatus/ Upgrade of the instance directory structure From MyEnv v1 to v2 the directory structure of instances has fundamentally changed. Nevertheless MyEnv v2 works fine with MyEnv v1 directory structures. Old structure ~/data/instance1/ibdata1 ~/data/instance1/ib_logfile? ~/data/instance1/my.cnf ~/data/instance1/error.log ~/data/instance1/mysql ~/data/instance1/test~/data/mypprod/ ~/data/instance1/general.log ~/data/instance1/slow.log ~/data/instance1/binlog.0000?? ~/data/instance2/... New structure ~/database/instance1/binlog/binlog.0000?? ~/database/instance1/data/ibdata1 ~/database/instance1/data/ib_logfile? ~/database/instance1/data/mysql ~/database/instance1/data/test ~/database/instance1/etc/my.cnf ~/database/instance1/log/error.log ~/database/instance1/log/general.log ~/database/instance1/log/slow.log ~/database/instance1/tmp/ ~/database/instance2/... But over time you possibly want to migrate the old structure to the new one. The following steps describe how you upgrade MyEnv instance structure v1 to v2: mysql@chef:~ [mysql-57, 3320]> mypprod mysql@chef:~ [mypprod, 3309]> stop .. SUCCESS! mysql@chef:~ [mypprod, 3309]> mkdir ~/database/mypprod mysql@chef:~ [mypprod, 3309]> mkdir ~/database/mypprod/binlog ~/database/mypprod/data ~/database/mypprod/etc ~/database/mypprod/log ~/database/mypprod/tmp mysql@chef:~ [mypprod, 3309]> mv ~/data/mypprod/binary-log.* ~/database/mypprod/binlog/ mysql@chef:~ [mypprod, 3309]> mv ~/data/mypprod/my.cnf ~/database/mypprod/etc/ mysql@chef:~ [mypprod, 3309]> mv ~/data/mypprod/error.log ~/database/mypprod/log/ mysql@chef:~ [mypprod, 3309]> mv ~/data/mypprod/slow.log ~/database/mypprod/log/ mysql@chef:~ [mypprod, 3309]> mv ~/data/mypprod/general.log ~/database/mypprod/log/ mysql@chef:~ [mypprod, 3309]> mv ~/data/mypprod/* ~/database/mypprod/data/ mysql@chef:~ [mypprod, 3309]> rmdir ~/data/mypprod mysql@chef:~ [mypprod, 3309]> vi /etc/myenv/myenv.conf - datadir = /home/mysql/data/mypprod + datadir = /home/mysql/database/mypprod/data - my.cnf = /home/mysql/data/mypprod/my.cnf + my.cnf = /home/mysql/database/mypprod/etc/my.cnf + instancedir = /home/mysql/database/mypprod mysql@chef:~ [mypprod, 3309]> source ~/.bash_profile mysql@chef:~ [mypprod, 3309]> cde mysql@chef:~/database/mypprod/etc [mypprod, 3309]> vi my.cnf - log_bin = binary-log + log_bin = /home/mysql/database/mypprod/binlog/binary-log - datadir = /home/mysql/data/mypprod + datadir = /home/mysql/database/mypprod/data - tmpdir = /tmp + tmpdir = /home/mysql/database/mypprod/tmp - log_error = error.log + log_error [...]

MySQL 8.0: It Goes to 11!

Thu, 15 Mar 2018 19:49:04 +0000

MySQL has over the years grown into a considerable family of products and components. Years ago, someone running MySQL would typically have a single Server instance. Advanced users might have two and run replication. These days, MySQL is much more of a distributed system, offering advanced replication technology for HA, load balancing, systems for monitoring […]

Verifying Query Performance Using ProxySQL

Thu, 15 Mar 2018 18:42:13 +0000

In this blog post, we’ll look at how you can verify query performance using ProxySQL. In the previous blog post, I showed you how many information can you get from the “stats.stats_mysql_query_digest” table in ProxySQL. I also mentioned you could even collect and graph these metrics. I will show you this is not just theory, it is possible. These graphs could be very useful to understand the impact of the changes what you made on the query count or execution time. I used our all-time favorite benchmark tool called Sysbench. I was running the following query: UPDATE sbtest1 SET c=? WHERE k=? There was no index on “k” when I started the test. During the test, I added an index. We expect to see some changes in the graphs. I selected the “stats.stats_mysql_query_digest” into a file in every second, then I used Percona Monitoring and Management (PMM) to create graphs from the metrics. (I am going write another blog post on how can you use PMM to create graphs from any kind of metrics.) Without the index, the update was running only 2-3 times per second. By adding the index, it went up to 400-500 hundred. We can see the results immediately on the graph. Let’s see the average execution time: Without the index, it took 600000-700000 microseconds, which is around 0.7s. By adding an index, it dropped to 0.01s. This is a big win, but most importantly we can see the effects on the query response time and query count if we are making some changes to the schema, query or configuration as well. Conclusion If you already have a ProxySQL server collecting and graphing these metrics, they could be quite useful when you are optimizing your queries. They can help make sure you are moving in the right direction with your tunings/modifications. The post Verifying Query Performance Using ProxySQL appeared first on Percona Database Performance Blog.

ChickTech Austin PopUp Workshop: Database Basics with MySQL

Thu, 15 Mar 2018 14:56:00 +0000

This Saturday I will be teaching Database Basics with MySQL and there are literally just two seats  left!  The MySQL Community Team is always looking for ways to reach new audiences and we would like to make this class available to other groups (So let me know if you are interested). And six hours is a really short time so it only scratches the surface.  Maybe we also need some intermediate classes above and beyond the introduction.

Comparing Oracle RAC HA Solution to Galera Cluster for MySQL or MariaDB

Thu, 15 Mar 2018 10:42:48 +0000

Business has continuously desired to derive insights from information to make reliable, smarter, real-time, fact-based decisions. As firms rely more on data and databases, information and data processing is the core of many business operations and business decisions. The faith in the database is total. None of the day-to-day company services can run without the underlying database platforms. As a consequence, the necessity on scalability and performance of database system software is more critical than ever. The principal benefits of the clustered database system are scalability and high availability. In this blog, we will try to compare Oracle RAC and Galera Cluster in the light of these two aspects. Real Application Clusters (RAC) is Oracle’s premium solution to clustering Oracle databases and provides High Availability and Scalability. Galera Cluster is the most popular clustering technology for MySQL and MariaDB. Architecture overview Oracle RAC uses Oracle Clusterware software to bind multiple servers. Oracle Clusterware is a cluster management solution that is integrated with Oracle Database, but it can also be used with other services, not only the database. The Oracle Clusterware is an additional software installed on servers running the same operating system, which lets the servers to be chained together to operate as if they were one server. Oracle Clusterware watches the instance and automatically restarts it if a crash occurs. If your application is well designed, you may not experience any service interruption. Only a group of sessions (those connected to the failed instance) is affected by the failure. The blackout can be efficiently masked to the end user using advanced RAC features like Fast Application Notification and the Oracle client’s Fast Connection Failover. Oracle Clusterware controls node membership and prevents split brain symptoms in which two or more instances attempt to control the instance. Galera Cluster is a synchronous active-active database clustering technology for MySQL and MariaDB. Galera Cluster differs from what is known as Oracle’s MySQL Cluster - NDB. MariaDB cluster is based on the multi-master replication plugin provided by Codership. Since version 5.5, the Galera plugin (wsrep API) is an integral part of MariaDB. Percona XtraDB Cluster (PXC) is also based on the Galera plugin. The Galera plugin architecture stands on three core layers: certification, replication, and group communication framework. Certification layer prepares the write-sets and does the certification checks on them, guaranteeing that they can be applied. Replication layer manages the replication protocol and provides the total ordering capability. Group Communication Framework implements a plugin architecture which allows other systems to connect via gcomm back-end schema. To keep the state identical across the cluster, the wsrep API uses a Global Transaction ID. GTID unique identifier is created and associated with each transaction committed on the database node. In Oracle RAC, various database instances share access to resources such as data blocks in the buffer cache to enqueue data blocks. Access to the shared resources between RAC instances needs to be coordinated to avoid conflict. To organize shared access to these resources, the distributed cache maintains information such as data block ID, which RAC instance holds the current version of this data block, and the lock mode in which each instance contains the data block. Dat[...]

ProxySQL Series: Seamless Replication Switchover Using MHA

Thu, 15 Mar 2018 07:24:58 +0000

This is our second blog in the ProxySQL Series ( Blog I MySQL Replication Read-write Split up ). Will cover how to integrate ProxySQL with MHA to handle failover of Database servers. We already have Master – Slave replication setup behind ProxySQL from previous blog [ProxySQL On MySQL Replication] For this setup we have added one more node for MHA Manager , Which will keep eye on Master and Slave status. node5 ( , MHA Manager ProxySQL can be greatly configured with MHA for Highly available setup with zero downtime. MHA role in failover : MHA tool is used for failover.During failover, MHA promotes most updated slave (slave with most recent transactions) as new master and apply CHANGE MASTER command on new slave and change read_only flag on new master and slave. ProxySQL role in failover : When failover happened (due to crash or manual for any maintenance activity) ProxySQL will detect the change (checking read_only flag) and promotes new master server’s IP into writers hostgroup and start sending traffic on new master. Each row in mysql_replication_hostgroups table in proxysql represent a pair of writer_hostgroup and reader_hostgroup . ProxySQL will monitor the value of read_only from mysql_server_read_only_log for all the servers. If read_only=1 the host is copied/moved to the reader_hostgroup, while if read_only=0 the host is copied/moved to the writer_hostgroup . Installing MHA If replication is classic binlog/pos format based then install MHA node on all hosts involving (manager,master,slaves), for GTID based replication it has to be installed only on the manager node. Install MHA node : Install MHA on all DB nodes and MHA manager server. More information apt-get -y install libdbd-mysql-perl dpkg -i mha4mysql-node_0.56-0_all.deb Install MHA manager : Only install on MHA manager server. #dependencies apt-get install -y libdbi-perl libdbd-mysql-perl libconfig-tiny-perl liblog-dispatch-perl libparallel-forkmanager-perl libnet-amazon-ec2-perl dpkg -i mha4mysql-manager_0.56-0_all.deb Configuration changes : Changes only on node5 ( , MHA Manager : Create directories : mkdir -p /etc/mha/ /var/log/mha/ Config file : cat /etc/mha/cluster1.conf [server default] # mysql user and password user=root password=xxx # replication user password repl_user=repl repl_password=xxx remote_workdir=/var/tmp # working directory on the manager manager_workdir=/var/log/mha/ # manager log file manager_log=/var/log/mha/mha.log ping_interval=15 */As we don't have to deal with VIP's here, disable master_ip_failover_script */ #master_ip_failover_script=/usr/local/bin/master_ip_failover master_ip_online_change_script=/usr/local/bin/master_ip_online_change master_binlog_dir=/data/log/ secondary_check_script=/etc/mha/mha_prod/ report_script=/etc/mha/mha_prod/ master_pid_file=/var/run/mysqld/ ssh_user=root log_level=debug #set this to 0 if YOU ARE SURE THIS CAN"T BREAK YOUR REPLICATION check_repl_filter=1 [server1] hostname= port=3306 [server2] hostname= port=3306 [server3] hostname= port=3306 no_master=1 master_ip_failover : Script used to switch virtual IP address.master_ip_online_change : Script used in switchover when master is online or dead. NOTE: Don’t forget to comment out the “FIX ME” lines in the above scripts. Custom scripts : Below scripts are optional secondary_check_script It is always good to[...]

Saw Percona at SCaLE 16x? Come See Even More at Percona Live 2018!

Thu, 15 Mar 2018 02:33:18 +0000

Did you see Percona at SCaLE 16x? I spent a couple of days there learning about open source software, databases, and other interesting topics. You can get even more open source database information at Percona Live 2018. SCaLE is the largest community-run open-source and free software conference in North America. It is held annually in the greater Los Angeles area. This year’s event took place on March 8-11, 2018, at the Pasadena Convention Center. SCaLE 16X hosted 150 exhibitors this year, along with nearly 130 sessions, tutorials and special events. Percona has been attending now for a number of years, and this year was no exception. Besides our booth in the Exhibit Hall, we had two speakers giving three different talks: Peter Zaitsev, Percona CEO and Founder Using MySQL for Distributed Database Architectures In modern data architectures, we’re increasingly moving from single node design systems to distributed architectures using multiple nodes – often spread across multiple databases and multiple continents. Such architectures bring many benefits (such as scalability and resiliency), but can also bring a lot of pain if not correctly architected. In this presentation, we looked into how we can use MySQL to engineer such systems. Firstly, we looked into the application data requirements that can shape which distributed architectures will work for an application, and what are their benefits and tradeoffs. Then we looked into how to implement the architectures with MySQL, using conventional and proven options such as MySQL Replication, as well as newer options such as: MySQL Multi-Source Replication MySQL Group Replication Percona XtraDB Cluster and Galera Application-driven replication using Kafka Finally, since a common cause of production problems is a misunderstanding of how distributed systems are designed to behave during failure, we examined what can commonly happen to cause architecture scenarios to fail. Why We’re Excited About MySQL 8.0 There are many great new features in MySQL 8.0, but how exactly can they help your applications? This session took a practical look at MySQL 8.0 features and improvements. We looked at the bugs, issues and limitations of previous MySQL versions and how MySQL 8.0 addresses them. It also covered what you can do with MySQL 8.0 that you couldn’t before. Colin Charles Capacity Planning for your Data Stores Imagine a ticket sales website that does normal events like an M2M concert, but also occasionally sells tickets to the very popular play Harry Potter and the Cursed Child. This is a perfect capacity planning example. Selling tickets requires that you never sell more tickets than you actually have. You want to load-balance your queries, to shard your data stores and split reads and writes. You need to determine where the system bottlenecks, so you need a baseline for your regular traffic. The website must be able to handle the increased load for extremely popular performances, but you don’t want to buy servers that aren’t doing anything for much of the time. (This is also why the cloud is so popular today.) Colin Charles explored storage capacity planning for OLTP and data warehousing uses and explains how metrics collection helps you plan your requirements. Coupled with the elastic nature of clouds, you should never have an error establishing database connection. Along the way, Colin also covered tools such as Box Anemometer, innotop, t[...]

Adding Custom Graphs and Dashboards to Percona Monitoring and Management

Wed, 14 Mar 2018 23:29:30 +0000

In this blog post, we’ll look at how to create PMM custom graphs and dashboards to track what you need to see in your database. Percona Monitoring and Management (PMM)‘s default set of graphs is pretty complete: it covers most of the stuff a DBA requires to fully visualize database servers. However, sometimes custom information is needed in graphical form. Otherwise, you just feel your PMM deployment is a missing a graph. Recently, a customer request came in asking for a better understanding of a specific metric: table growth, or more specifically the daily table growth (in bytes) for the last 30 days. The graph we came up with looks like this: . . .which graphs the information that comes from this query: increase(mysql_info_schema_table_size{instance="$host",component="data_length"}[1d]) But what does that query mean, and how do I create one myself? I’m glad you asked! Let’s go deep into the technical details! Before creating any graph, we must ensure that we have the data that will represent graphically. So, the first step is to ensure data collection. Data collection This data is already collected by the Percona mysqld_exporter, as defined in the “Collector Flags” table from the GitHub repo: Cool! Now we need a Prometheus query in order to get the relevant data. Luckily, the Prometheus documentation is very helpful and we came up with a query in no time. Prometheus query What do we need for the query? In this case, it is a metric, a label and a time range. Every PMM deployment has access to the Prometheus console by adding “/prometheus” to the URL. The console is incredibly helpful when playing with queries. The console looks like this: The metric The time series values collected by the exporter are stored in the metrics inside of Prometheus. For our case, the metric name is called mysql_info_schema_table_size, which I figured out by using the Prometheus console “Expression” text input and its autocomplete feature. This shows you the options available as you’re writing. All the metrics collected by mysqld_export start with “mysql”. The label Labels are different per metric, but they are intuitively named. We need the instance and component labels. Instance is the hostname and component is equivalent to the column name of a MySQL table. The component we need is “data_length”. The time frame This is easy: since is a daily value, the time frame is 1d.  The time frame is not mandatory, but it is a parameter asked for by the function we’re going to use to calculate the increase, which is called increase(). That’s how we ended up with the query that feeds the metrics, which end up in here: You will notice it’s using a variable: $host. We define that variable in the dashboard creation, explained below. PMM dashboard PMM best practice is to take a copy of the existing dashboard using Setting > Save as…, since edits to Percona-provided dashboards are not preserved during upgrades. In this example, we will start with an empty dashboard. Adding a new dashboard is as easy as clicking the “New” button from the Grafana dropdown menu: After that, you choose the type of element that you want on a new row, which is a Graph in this case: We like to use variables for our graphs – changing which server we analyze, for example. To add variables to the dashboard, we need t[...]

Basic Internal Troubleshooting Tools for MySQL Server Webinar: Q & A

Wed, 14 Mar 2018 23:26:47 +0000

In this blog, I will provide answers to the Q & A for the Basic Internal Troubleshooting Tools for MySQL Server webinar. First, I want to thank everybody for attending my February 15, 2018, webinar on troubleshooting tools for MySQL. The recording and slides for the webinar are available here. Below is the list of your questions that I was unable to answer fully during the webinar. Q: How do we prevent the schema prefix from appearing in the show create view. This is causing issue with restore on another server with a different DB. See the issue here and reproducible test case: A: I shortened the example in order to fit it in this blog: mysql> create table t1(f1 int); Query OK, 0 rows affected (3.47 sec) mysql> create view v1 as select * from t1; Query OK, 0 rows affected (0.21 sec) mysql> show create view v1G *************************** 1. row *************************** View: v1 Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t1`.`f1` AS `f1` from `t1` character_set_client: utf8 collation_connection: utf8_general_ci 1 row in set (0.00 sec) mysql> select * from information_schema.views where table_schema='test'G *************************** 1. row *************************** TABLE_CATALOG: def TABLE_SCHEMA: test TABLE_NAME: v1 VIEW_DEFINITION: select `test`.`t1`.`f1` AS `f1` from `test`.`t1` CHECK_OPTION: NONE IS_UPDATABLE: YES DEFINER: root@localhost SECURITY_TYPE: DEFINER CHARACTER_SET_CLIENT: utf8 COLLATION_CONNECTION: utf8_general_ci 1 row in set (0.00 sec) The issue you experienced happened because even if you created a view as SELECT foo FROM table1;, it is stored as SELECT foo FROM your_schema.table1;. You can see it if you query the *.frm  file for the view:sveta@Thinkie:~/build/ps-5.7/mysql-test$ cat var/mysqld.1/data/test/v1.frm TYPE=VIEW query=select `test`.`t1`.`f1` AS `f1` from `test`.`t1` md5=5840f59d1287385629fcb9b948e53d96 updatable=1 algorithm=0 definer_user=root definer_host=localhost suid=2 with_check_option=0 timestamp=2018-02-24 10:27:45 create-version=1 source=select * from t1 client_cs_name=utf8 connection_cl_name=utf8_general_ci view_body_utf8=select `test`.`t1`.`f1` AS `f1` from `test`.`t1` You cannot prevent the schema prefix from being stored. If you restore the view on a different server with a different database name, you should edit the view definition manually. If you already restored the view that points to a non-existent schema, just recreate it. VIEW is metadata only and does not hold any data, so this operation is non-blocking and will run momentarily. Q: What is thread/sql/compress_gtid_table in performance_schema.threads? A: thread/sql/compress_gtid_table  is name of the instrument. You can read this and other instruments as below: thread/ is a group of instruments. In this case, it is the instruments that are visible in the THREADS table. thread/sql/ is the group of instruments that are part of the server kernel code. If you are not familiar with MySQL source tree, download the source code tarball and check its content. The main components are: sql  – server kernel storage – where storage engines code located (storage/innobase  is InnoDB code, storage/myis[...]

Recap of M|18, the MariaDB User Conference – Video Recordings Now Available

Wed, 14 Mar 2018 18:17:00 +0000

Recap of M|18, the MariaDB User Conference – Video Recordings Now Available MariaDB Team Wed, 03/14/2018 - 14:17 Thank you to everyone who joined us at our second annual MariaDB user conference, M|18, in New York City on February 26 and 27. DBAs, open source enthusiasts, engineers, executives and more from all over the world came together to explore and learn. Couldn’t make the event or want to relive your favorite session? Watch 40+ M|18 session recordings on demand.   Session highlights The welcome keynote from MariaDB’s CEO, Michael Howard, announced several new initiatives, including MariaDB Labs, a research division focused on solving extreme challenges within the database industry on the topics of machine learning; distributed computing; and next-generation chips, storage and memory. “Corporate Banking and Future-Ready Technology,” with Ng Peng Khim and Joan Tay from DBS (Development Bank of Singapore), provided an update on the bank’s journey from Oracle Enterprise to MariaDB TX. “Massive Scale with MariaDB,” with ServiceNow’s Tim Yim, revealed that the company has a mind-blowing 85,000 MariaDB databases that ServiceNow manages with a custom “multi-instance deployment” implementation. “How We Made the Move to MariaDB at FNI,” with William Woods from Financial Network, Inc., shared the considerations, including security, that led FNI to migrate from Oracle to MariaDB. “MariaDB AX Panel Discussion on Analytical Use Cases,” featuring Aziz Vahora from Pinger, Jack Sprague from Center of Information Management, and Patrice Linel from Genus Plc., provided an opportunity to hear directly from MariaDB AX users from diverse industries and learn why they chose MariaDB. “How Facebook Migrated to MyRocks,” with Facebook’s Yoshinori Matsunobu, covered how Facebook created the MyRocks storage engine (which is in RC for MariaDB Server 10.3) to replace InnoDB in the Facebook users database, and highlighted MyRocks features being developed. “Panel Discussion: Open Source in the Enterprise,” featured Bill Montgomery of Red Hat, Dheeraj Golla of Copart, and Phil Mazza of Financial Network, Inc., sharing why they chose to adopt open source, the business and technical challenges they faced and how other enterprises can succeed with open source software. “How Copart Switched to MariaDB and Reduced Costs During Growth,” featured Dheeraj Golla and Pravin Malali of Copart explaining why Copart chose to standardize on MariaDB TX, how they convinced operational and development teams to adopt it, and how they’re now evaluating MariaDB AX as a replacement for their current analytics database.   Networking with games Learning opportunities abounded at M|18, all while having fun. At the opening-night party and closing reception, attendees enjoyed food, drink and conversation – plus a little good-natured competition.   #MARIADBM18 Thanks to the attendees and speakers, M|18 was trending on Twitter. Here are a few of our favorite conference tweets. Had great time at conference. Helped to reinforce architectural changes needed in our company and how mariadb suite helps. #MARIADBM18 — Adam A. Lang (@AdamALang)  Have had a blast at #MARIADBM18 - lots learnt and even more to think about. Thanks #Mariadb — Tudor Davies (@DerBroader71)  Thank you Mar[...]

MySQL Community Awards 2018: Nominations close March 15

Wed, 14 Mar 2018 17:43:02 +0000

If you’ve been delaying nominating someone or something for a MySQL Community Award, now is the time to submit it. You can submit it quickly via a google form, or go through the trouble of tweeting or emailing it. After March 15, the Committee will being voting. As a reminder: there are categories for Community Contributor (a person), Application, and Corporate Contributor. This is a fantastic way to honor new and old community members for the work they do – often in their spare time, and to give thanks to the corporations that help the community. The original post with more details is here:

MariaDB Connector/J 2.2.3 and 1.7.3 now available

Wed, 14 Mar 2018 16:48:51 +0000

MariaDB Connector/J 2.2.3 and 1.7.3 now available dbart Wed, 03/14/2018 - 12:48 The MariaDB project is pleased to announce the immediate availability of MariaDB Connector/J 2.2.3 and MariaDB Connector/J 1.7.3. See the release notes and changelogs for details and visit to download. Download MariaDB Connector/J 2.2.3 Release Notes Changelog About MariaDB Connector/J Download MariaDB Connector/J 1.7.3 Release Notes Changelog About MariaDB Connector/J Community MariaDB Releases The MariaDB project is pleased to announce the immediate availability of MariaDB Connector/J 2.2.3 and MariaDB Connector/J 1.7.3. See the release notes and changelogs for details. Login or Register to post comments

Releasing ProxySQL 1.4.7

Wed, 14 Mar 2018 14:45:00 +0000

Proudly announcing the release of the latest stable release of ProxySQL 1.4.7 as of the 14th March 2018. ProxySQL is a high performance, high availability, protocol aware proxy for MySQL. It can be downloaded here, and freely usable and accessible according to GPL license. ProxySQL 1.4.7 includes a number of important improvements and bug fixes including: New features: Added MySQL Query Rules Fast Routing which extends MySQL Query Rules to provide immediate routing based on user & schema (especially important when supporting a large number of schemas). Details of this feature will be described in a future blog post. Added new metrics for hostgroup manager and frontend queries, specifically: MyHGM_myconnpoll_destroy MyHGM_myconnpoll_get MyHGM_myconnpoll_get_ok MyHGM_myconnpoll_push MyHGM_myconnpoll_reset Queries_frontends_bytes_recv Queries_frontends_bytes_sent Implemented querying of stats_mysql_global status variables using SELECT @@ syntax #1375 Added the monitor_read_only_max_timeout_count variable to allow multiple attempts on read only check #1206 Bug fixes: Add rw lock to ensure metadata aren't updated while running STMT_PREPARE_RESPONSE MyComQueryCmd not initialized could cause crash #1370 Incorrect parsing of SET NAMES ... COLLATE #1357 Embed main-bundle.min.css in web UI #1354 Handle additional whitespace parse error case on SET commands #1373 Reset connection creation time on CHANGE USER #1393 Added fix for utf8_unicode_ci in MariaDB Client Connector C #1396 Fixed a bug where a server disappears when RO=1 becomes RO=0 (similar to #1039) A special thanks to all the people that reports bugs: this makes each version of ProxySQL better than the previous one. Please report any bugs or feature requests on github issue tracker

MySQL Security – User Account Locking

Wed, 14 Mar 2018 14:03:39 +0000

For security reasons some context require you to setup a user account locking policy. Thus an unauthorized user is not able (anymore) to login to the MySQL server. In this 3rd article of the MySQL 5.7 Security series, we will see how to [un]lock a user account.

MySQL 8.0 : Digest Query Samples in Performance_Schema

Wed, 14 Mar 2018 13:23:24 +0000

Today, yet another blog post about improvements in MySQL 8.0 related to Performance_Schema. Before MySQL 8.0 it was not always easy to get an example of the queries you could find in Performance_Schema when looking for statements summaries. You had to link several tables (even from sys) to achieve this goal as I explained it in this post. Now in MySQL 8.0, we have changed the table events_statements_summary_by_digest. This table now contains 6 extra columns: QUANTILE_95 : stores the 95th percentile of the statement latency, in picoseconds. QUANTILE_99 : stores the 99th percentile of the statement latency, in picoseconds. QUANTILE_999 : stores the 99.9th percentile of the statement latency, in picoseconds. QUERY_SAMPLE_TEXT : captures a query sample that can be used with EXPLAIN to get a query plan. QUERY_SAMPLE_SEEN : stores the timestamp of the query. QUERY_SAMPLE_TIMER_WAIT : stores the query sample execution time. FIRST_SEEN and LAST_SEEN have also been modified to use fractional seconds. The previous definition was: Field: LAST_SEEN Type: timestamp Null: NO Key: Default: 0000-00-00 00:00:00 Extra: Now it’s Field: LAST_SEEN Type: timestamp(6) Null: NO Key: Default: 0000-00-00 00:00:00.000000 Extra: The main goal is to capture a full example query like it was made in production with some key information about this query example and to make it easily accessible. Edit: I forgot something important that the famous daddy of SYS and Performance_Schema expert reported to me: performance_schema_max_digest_sample_age Indeed, you can control the sampling for the queries in events_statements_summary_by_digest table. By default if the sample statement is for the same query digest is older than 60 seconds (default value of performance_schema_max_digest_sample_age) then it gets replaced by the new statement. Even if the new statement wait time is less than the one already in the table. If the old statement is not too old, it gets replaced only if the new statement as a greater wait_time.

The Multi-Source GTID Replication Maze

Tue, 13 Mar 2018 21:56:23 +0000

In this blog post, we’ll look at how to navigate some of the complexities of multi-source GTID replication. GTID replication is often a real challenge for DBAs, especially if this has to do with multi-source GTID replication. A while back, I came across a really interesting customer environment with shards where multi-master, multi-source, multi-threaded MySQL 5.6 MIXED replication was active. This is a highly complex environment that has both pros and cons, introducing risks as a trade-off for specific customer requirements. This is the set up of part of this environment: I started looking into this setup when a statement broke replication between db1 and db10. Replication broke due to a statement executed on a schema that was not present on db10. This also resulted in changes originating from db1 to not being pushed down to db100 as db10, as we stopped the replication thread (for db1 channel). On the other hand, replication was not stopped on db2 because the schema in question was present on db2. Replication between db2 and db20 was broken as well because the schema was not present in db20. In order to fix db1->db10 replication, four GTID sets were injected in db10. Here are some interesting blog posts regarding how to handle/fix GTID replication issues: After injecting the GTID sets, we started replication again and everything ran fine.   After that, we had to check the db2->db20 replication, which, as I’ve already said, was broken as well. In this case, injecting only the first GTID trx into db20 instead of all of those causing issues on db10 was enough! You may wonder how this is possible. Right? The answer is that the rest of them were replicated from db10 to db20, although the channel was not the same. Another strange thing is the fact that although the replication thread for the db2->db20 channel was stopped (broken), checking the slave status on db20 showed that Executed_Gtid_Set was moving for all channels even though Retrieved_Gtid_Set for the broken one was stopped! So what was happening there? This raised my curiosity, so I decided to do some further investigation and created scenarios regarding other strange things that could happen. An interesting one was about the replication filters. In our case, I thought “What would happen in the following scenario … ?” Let’s say we write a row from db1 to db123.table789. This row is replicated to db10 (let’s say using channel 1) and to db2 (let’s say using channel2). On channel 1, we filter out the db123.% tables, on channel2 we don’t. db1 writes the row and the entry to the binary log. db2 writes the row after reading the entry from the binary log and subsequently writes the entry to its own binary log and replicates this change to db20. This change is also replicated to db10. So now, on db10 (depending on which channel finds the GTID first) it either gets filtered on channel1 a[...]

Webinar Thursday, March 15, 2018: Basic External MySQL Troubleshooting Tools

Tue, 13 Mar 2018 20:38:45 +0000

Please join Percona’s Principal Support Engineer, Sveta Smirnova, as she presents Basic External MySQL Troubleshooting Tools on March 15, 2018 at 10:00 am PDT (UTC-7) / 1:00 pm EDT (UTC-4). In my troubleshooting webinar series, I normally like to discuss built-in instruments available via the SQL interface. While they are effective and help to understand what is going on, external tools are also designed to make life of a database administrator easier. In this webinar, I will discuss the external tools, toolkits and graphical instruments most valued by Support teams and customers. I will show the main advantages of these tools, and provide examples on how to effectively use them. I will cover Percona Toolkit, MySQL Utilities, MySQL Sandbox, Percona Monitoring and Management (PMM) and a few other instruments. Register for the webinar now. Sveta Smirnova, Principal Technical Services Engineer Sveta joined Percona in 2015. Her main professional interests are problem-solving, working with tricky issues, bugs, finding patterns that can quickly solve typical issues, and teaching others how to deal with MySQL issues, bugs and gotchas effectively. Before joining Percona Sveta worked as Support Engineer in the MySQL Bugs Analysis Support Group in MySQL AB-Sun-Oracle. She is the author of the book “MySQL Troubleshooting” and JSON UDF functions for MySQL.

MySQL 8.0 : New Error Logging

Tue, 13 Mar 2018 14:23:45 +0000

With MySQL 8.0, the error logging subsystem has been redesigned to use the new component architecture. Thanks to this new redesign, now the log events can be filtered, the output can be sent to multiple destinations (different formats like JSON). All that is controlled by system variables. This work gives the possibility for a log event to become the raw material for log processing by more modern and automated systems like filebeat for beats, kibana, etc… Let’s check the default configuration: mysql> select * from global_variables where VARIABLE_NAME like 'log_error_%'; +---------------------+----------------------------------------+ | VARIABLE_NAME | VARIABLE_VALUE | +---------------------+----------------------------------------+ | log_error_services | log_filter_internal; log_sink_internal | | log_error_verbosity | 2 | +---------------------+----------------------------------------+ This means that log events will follow the following flow: first pass through log_filter_internal (built-in filter component), then through log_sink_internal (buit-in log writer component). To enable a log component you need to use INSTALL COMPONENT command and set the log_error_services global variable as wished. To disable it use  UNINSTALL COMPONENT. Currently the available log components are in lib/plugins: LOG OUTPUT To specify a new output format, you need to use a log writer component (sink). So let’s try to use one. To load a component , you need its URN. This is ‘file://‘ + the filename without the .so extension. Example, to load the writer to json component, you enable it like this: mysql> INSTALL COMPONENT 'file://component_log_sink_json'; Query OK, 0 rows affected (0.14 sec) mysql> SET GLOBAL log_error_services = 'log_filter_internal; log_sink_internal; log_sink_json'; Query OK, 0 rows affected (0.01 sec) mysql> select * from global_variables where VARIABLE_NAME like 'log_error_%'; +---------------------+-------------------------------------------------------+ | VARIABLE_NAME | VARIABLE_VALUE | +---------------------+-------------------------------------------------------+ | log_error_services | log_filter_internal; log_sink_internal; log_sink_json | | log_error_verbosity | 2 | +---------------------+-------------------------------------------------------+ 2 rows in set (0.00 sec) Now if I generate an entry, I will have the error in the standard error log file and also in a new json file (having the name of the error log, specified in log_error variable) with a number and the .json extension. More info here in the manual. Let’s have a look at the new entry generated by loading the group_replication plugin in this sandbox instance. in the traditional error log: 2018-03-13T09:13:45.846708Z 24 [ERROR] [MY-011596] [Repl] Plugin group_replication reported: 'binlog_checksum should be NONE for Group Replication' 2[...]

Windowing Function Tutorials

Tue, 13 Mar 2018 14:18:00 +0000

Windowing Functions are new to MySQL with Version 8.  I have been very lucky in the past few days to find two excellent resources that I want to share with you.  Both are worth the time and effort to study.At the Southern California Linux Expo last week, Bruce Momjian of EnterpriseDB and PostgreSQL fame gave an amazing tutorial on Common Table Expressions and Windowing Functions (slides for both at  Bruce is an amazing presenter and I highly recommend going to his sessions at conferences. So, what can a MySQL-er learn from a Postrgrestian?Plenty.In this case the two databases are using SQL and the features that turn that language from descriptive to imperative. But showing how these features is very hard.  Bruce said it took him six years to finish the Windowing Function presentation.  And the results are impressive.You will have to make some changes to the presented code as MySQL does not have sequences -- create a table named x, with a single int column named x, and fill it with the numbers one through 10 for the first windowing function examples.  The big item to study is the various partitioning terminology which can get confusing but is spelled out explicitly in this tutorial.The next tutorial is from Alex Yeskov and can be found at and is a great addendum to the other tutorial.  He stresses learning by doing and do he does!!His examples include calculating revenue growth, running totals, dealing with duplicated data, finding the top N rows, and examining repeat customer purchases. Doing these two tutorials will not turn you into a windowing function expert.  But you will have a better understanding of how they work and that will make you a better SQL programmer

Most popular databases in 2018 according to StackOverflow survey

Tue, 13 Mar 2018 09:49:32 +0000

The survey results from StackOverflow’s developers survey are already here, and we can now declare the most popular databases for 2018. Without further ado, let’s look into the results: So what can we learn from these results? The most popular database is MySQL, and not by far comes SQL Server. This wasn’t very surprising, as they were the top two databases last year as well. This year, more than half of the respondents (58.7%) are using MySQL. It seems RDBMS databases and specifically MySQL are here to stay for at least few years ahead. SQL Server has a large market share as well (41.2%) This is also an increase from last year (30.8%). Microsoft is pushing SQL Server with their Windows Servers, and that way gaining significant market share. In addition, recently Microsoft introduced some AI capabilities in SQL Server over Azure, so we believe it will gain them some more new customers wishing to optimize their database using some artificial intelligence. PostgreSQL is gaining lots of traction in the last few years. Developers working with Postgres are very pleased with the product, both in terms of capabilities and performance. We believe this trend will continue and we’ll see larger usage rates next year. RDBMS databases are still significantly more common than NoSQL databases such as MongoDB. Relatively new technologies are starting to gain market share in the databases world – Redis (first release at 2009) and Cassandra (first release at 2008). Almost 1/4 of all programmers (21.6%) are using SQLite, which is a lite SQL database which is based on a single file. This database has large popularity in small desktop applications and applications where an embedded database is required (for example, mobile phone apps). One might argue that many beginner developers are using MySQL and that’s why the numbers are so high. Well, if you’ll look at the same chart but only for Professional Developers, the numbers look very similar. To summarize, RDBMS and MySQL are still very popular among tech companies and programmers. NoSQL databases are probably not here to replace those needs, but to be used to solve different requirements. Disclaimer: charts and data copyrights are of StackOverflow. EverSQL is in no way associated with StackOverflow.

Updated: Become a ClusterControl DBA: Managing your Database Configurations

Tue, 13 Mar 2018 09:08:13 +0000

In the past five posts of the blog series, we covered deployment of clustering/replication (MySQL / Galera, MySQL Replication, MongoDB & PostgreSQL), management & monitoring of your existing databases and clusters, performance monitoring and health, how to make your setup highly available through HAProxy and MaxScale and in the last post, how to prepare yourself for disasters by scheduling backups. Since ClusterControl 1.2.11, we made major enhancements to the database configuration manager. The new version allows changing of parameters on multiple database hosts at the same time and, if possible, changing their values at runtime. We featured the new MySQL Configuration Management in a Tips & Tricks blog post, but this blog post will go more in depth and cover Configuration Management within ClusterControl for MySQL, PostgreSQL and MongoDB. ClusterControl Configuration management The configuration management interface can be found under Manage > Configurations. From here, you can view or change the configurations of your database nodes and other tools that ClusterControl manages. ClusterControl will import the latest configuration from all nodes and overwrite previous copies made. Currently there is no historical data kept. If you’d rather like to manually edit the config files directly on the nodes, you can re-import the altered configuration by pressing the Import button. And last but not least: you can create or edit configuration templates. These templates are used whenever you deploy new nodes in your cluster. Of course any changes made to the templates will not retroactively applied to the already deployed nodes that were created using these templates. MySQL Configuration Management As previously mentioned, the MySQL configuration management got a complete overhaul in ClusterControl 1.2.11. The interface is now more intuitive. When changing the parameters ClusterControl checks whether the parameter actually exists. This ensures your configuration will not deny startup of MySQL due to parameters that don’t exist. From Manage -> Configurations, you will find an overview of all config files used within the selected cluster, including load balancer nodes. We use a tree structure to easily view hosts and their respective configuration files. At the bottom of the tree, you will find the configuration templates available for this cluster. Changing parameters Suppose we need to change a simple parameter like the maximum number of allowed connections (max_connections), we can simply change this parameter at runtime. First select the hosts to apply this change to. Then select the section you want to change. In most cases, you will want to change the MYSQLD section. If you would like to change the default character set for MySQL, you will have to change that in both MYSQLD and client sections. If necessary you can also create a new section by simply typing the new section name. This will create a new section in the my.cnf. Once we change a parameter and set its new value [...]

Presentation: Ansible is our Wishbone

Tue, 13 Mar 2018 08:40:11 +0000

This presentation was made at LSPE event in Bangalore (India) held at Walmart labs on 10-03-2018. This presentation focuses how we have harnessed the power of Ansible at Mydbops.    

The weird messages Anthology of MySQL – Episode 1

Tue, 13 Mar 2018 07:42:07 +0000

Hi dear community. Today I want to share with you some funny and weird error messages which can be extremely annoying So let’s begin: CREATE temporary TABLE `sbtest1_temp` ( `id` int(11) NOT NULL AUTO_INCREMENT, `k` int(11) NOT NULL DEFAULT '0', `c` char(120) NOT NULL DEFAULT '', `pad` char(60) NOT NULL DEFAULT '', `json_test_v` json GENERATED ALWAYS AS (json_array(`k`,`c`,`pad`)) VIRTUAL, `json_test_s` json GENERATED ALWAYS AS (json_array(`k`,`c`,`pad`)) STORED, `json_test_index` varchar(255) GENERATED ALWAYS AS (json_array(`k`,`c`,`pad`)) STORED, PRIMARY KEY (`id`), KEY `k_1` (`k`), KEY `json_test_index` (`json_test_index`) ) ENGINE=InnoDB AUTO_INCREMENT=1001 DEFAULT CHARSET=latin1 COMPRESSION='lz4' ENCRYPTION='Y' ERROR 1478 (HY000): InnoDB: Unsupported encryption option for temporary tables. Okay, let’s disable it: CREATE temporary TABLE `sbtest1_temp` ( `id` int(11) NOT NULL AUTO_INCREMENT, `k` int(11) NOT NULL DEFAULT '0', `c` char(120) NOT NULL DEFAULT '', `pad` char(60) NOT NULL DEFAULT '', `json_test_v` json GENERATED ALWAYS AS (json_array(`k`,`c`,`pad`)) VIRTUAL, `json_test_s` json GENERATED ALWAYS AS (json_array(`k`,`c`,`pad`)) STORED, `json_test_index` varchar(255) GENERATED ALWAYS AS (json_array(`k`,`c`,`pad`)) STORED, PRIMARY KEY (`id`), KEY `k_1` (`k`), KEY `json_test_index` (`json_test_index`) ) ENGINE=InnoDB AUTO_INCREMENT=1001 DEFAULT CHARSET=latin1 COMPRESSION='lz4' ENCRYPTION='N' ERROR 1031 (HY000): Table storage engine for 'sbtest1_temp' doesn't have this option Hmm, table storage engine is InnoDB so it should have this option and in fact which option? Maybe it is compression? Let’s disable it also: CREATE temporary TABLE `sbtest1_temp` ( `id` int(11) NOT NULL AUTO_INCREMENT, `k` int(11) NOT NULL DEFAULT '0', `c` char(120) NOT NULL DEFAULT '', `pad` char(60) NOT NULL DEFAULT '', `json_test_v` json GENERATED ALWAYS AS (json_array(`k`,`c`,`pad`)) VIRTUAL, `json_test_s` json GENERATED ALWAYS AS (json_array(`k`,`c`,`pad`)) STORED, `json_test_index` varchar(255) GENERATED ALWAYS AS (json_array(`k`,`c`,`pad`)) STORED, PRIMARY KEY (`id`), KEY `k_1` (`k`), KEY `json_test_index` (`json_test_index`) ) ENGINE=InnoDB AUTO_INCREMENT=1001 DEFAULT CHARSET=latin1 COMPRESSION='none' ENCRYPTION='N' ERROR 1112 (42000): Table 'sbtest1_temp' uses an extension that doesn't exist in this MySQL version Great, now it is much more clear Everything should exist in this MySQL version because it is the latest. So after testing bunch of combinations it turned out it was due to COMPRESSION=’none’ statement. If you exclude it from create statement: CREATE temporary TABLE `sbtest1_temp` ( `id` int(11) NOT NULL AUTO_INCREMENT, `k` int(11) NOT NULL DEFAULT '0', `c` char(120) NOT NULL DEFAULT '', `pad` char(60) NOT NULL DEFAULT '', `json_test_v` json GENERATED ALWAYS AS (json_array(`k`,`c`,`pad`)) VIRTUAL, `json_test_s` json GENERATED ALWAYS AS (json_array(`k`,`c`,`pad`)) STORED, `json_test_index`[...]

Password reuse policy in MySQL 8.0

Tue, 13 Mar 2018 07:39:04 +0000

MySQL has various kinds of password policy enforcement tools: a password can expire (even automatically), can be forced to be of a certain length, contain amounts of various types of characters and be checked against a dictionary of common passwords or the user account name itself.…

MySQL Connector/Java 5.1.46 GA has been released

Mon, 12 Mar 2018 23:36:32 +0000

Dear MySQL Users, MySQL Connector/J 5.1.46, a maintenance release of the production 5.1 branch has been released. Connector/J is the Type-IV pure-Java JDBC driver for MySQL. MySQL Connector Java is available in source and binary form from the Connector/J download pages at and mirror sites as well as Maven-2 repositories. MySQL Connector Java (Commercial) is already available for download on the My Oracle Support (MOS) website. This release will shortly be available on eDelivery (OSDC). As always, we recommend that you check the “CHANGES” file in the download archive to be aware of changes in behavior that might affect your application. MySQL Connector/J 5.1.46 includes the following general bug fixes and improvements, also available in more detail on Changes in MySQL Connector/J 5.1.46 (2018-03-12) Version 5.1.46 is a maintenance release of the production 5.1 branch. It is suitable for use with MySQL Server versions 5.5, 5.6, 5.7, and 8.0. It supports the Java Database Connectivity (JDBC) 4.2 API. Functionality Added or Changed * Because Connector/J restricted TLS versions to v1.1 and below by default when connecting to MySQL Community Server 8.0 (which used to be compiled with yaSSL by default and thus supporting only TLS v1.1 and below), it failed to connect to to a MySQL 8.0.4 Community Server (which has been compiled with OpenSSL by default and thus supports TLS v1.2) that was configured to only allow TLS v1.2 connections. TLS v1.2 is now enabled for connections with MySQL Community Server 8.0.4 and later. (Bug #27374581) * The bundle for Connector/J 5.1 delivered by Oracle now contains an additional jar package with the name mysql-connector-java-5.1.ver.jar (mysql-connector-java-commercial-5.1.ver.jar for commercial bundles). It is identical with the other jar package with the original package named mysql-connector-java-5.1.ver-bin.jar (mysql-connector-java-commercial-5.1.ver-bin.jar for commercial bundles), except for its more Maven-friendly file name. (Bug #27231383) * The lower bound for the connection property packetDebugBufferSize has been changed to 1, to avoid the connection errors that occur when the value is set to 0. (Bug #26819691) * For multi-host connections, when a MySQL Server was configured with autocommit=0, Connection.getAutoCommit() did not return the correct value. This was because useLocalSessionState=true was assumed for multi-host connections, which might not be the case, resulting thus in inconsistent session states. With this fix, by default, Connector/J executes some extra queries in the connection synchronization process to guarantee con[...]

MySQL 8.0 : meta-data added to Performance_Schema’s Instruments

Mon, 12 Mar 2018 20:56:26 +0000

In MySQL 8.0, the engineers have added useful meta-data to the table SETUP_INSTRUMENT. This table lists the classes of instrumented objects for which events can be collected. To the NAME, ENABLES and TIMED columns, were added PROPERTIES, VOLATILITY and DOCUMENTATION. Let’s have a quick look at these new columns: PROPERTIES can have the following values global_statistics: only global summaries are available for this instrument. Example: memory/performance_schema/metadata_locks that return the memory used for table performance_schema.metadata_locks mutable: only applicable for statement instruments as they can “mutate” into a more specific one. Example: statement/abstract/relay_log that returns the new event just read from the relay log. progress: applied only to stage instruments, it reports progress data. Example: stage/sql/copy to tmp table singleton: instruments having a single instance. Example: wait/synch/mutex/sql/LOCK_error_log, like most global mutex locks this lock on error log is a singleton. user: instrument related to user workload. Example: the instrument on idle VOLATILITY This define the life or creation occurrence of the instrument. The possible values from low to high are: 0 : unknown 1 : permanent 2 : provisioning 3 : ddl 4 : cache 5 : session 6 : transaction 7 : query 8 : intra_query For example, wait/synch/mutex/sql/THD::LOCK_thd_query as a volatility of 5, which means this mutex is created each time a session connects and destroyed when the session disconnects. There is no point then to enable an instrument for an object already created. DOCUMENTATION Finally, now there is a documentation column describing the purpose of the instrument. Currently there are 80 instruments documented with the help of that column. This is an example: NAME: memory/performance_schema/prepared_statements_instances ENABLED: YES TIMED: NULL PROPERTIES: global_statistics VOLATILITY: 1 DOCUMENTATION: Memory used for table performance_schema.prepared_statements_instances All this is explained in details in the MySQL’s manual. Enjoy MySQL 8.0 and I wish you a pleasant discovery of all the new features !

dbdeployer release candidate

Mon, 12 Mar 2018 20:22:00 +0000

The latest release of dbdeployer is possibly the last one with a leading 0. If no serious bugs are found in the next two weeks, the next release will bear a glorious 1.0. Latest news The decision to get out of the stream of pre-releases that were published until now comes because I have implemented all the features that I wanted to add: mainly, all the ones that I wished to add to MySQL-Sandbox but it would have been too hard: concurrent deployment and deletion group replication; multi-source replication; Full customisation Centralised administration. Container-based tests; Mock tests that can run without any real database server. The latest addition is the ability of running multi-source topologies. Now we can run four topologies: master-slave is the default topology. It will install one master and two slaves. More slaves can be added with the option --nodes. group will deploy three peer nodes in group replication. If you want to use a single primary deployment, add the option --single-primary. Available for MySQL 5.7 and later. fan-in is the opposite of master-slave. Here we have one slave and several masters. This topology requires MySQL 5.7 or higher.all-masters is a special case of fan-in, where all nodes are masters and are also slaves of all nodes. It is possible to tune the flow of data in multi-source topologies. The default for fan-in is three nodes, where 1 and 2 are masters, and 2 are slaves. You can change the predefined settings by providing the list of components: $ dbdeployer deploy replication \ --topology=fan-in \ --nodes=5 \ --master-list="1 2 3" \ --slave-list="4 5" \ 8.0.4 \ --concurrent In the above example, we get 5 nodes instead of 3. The first three are master (--master-list="1 2 3") and the last two are slaves (--slave-list="4 5") which will receive data from all the masters. There is a test automatically generated to test replication flow. In our case it shows the following: $ ~/sandboxes/fan_in_msb_8_0_4/test_replication# master 1# master 2# master 3# slave 4ok - '3' == '3' - Slaves received tables from all masters# slave 5ok - '3' == '3' - Slaves received tables from all masters# pass: 2# fail: 0 The first three lines show that each master has done something. In our case, each master has created a different table. Slaves in nodes 5 and 6 then count how many tables they found, and if they got the tables from all masters, the test succeeds.Note that for all-masters topology there is no need to specify master-list or slave-list. In fact, those lists will be auto-generated, and they will both include all deployed nodes. What now? Once I make sure that the current features are reasonably safe (I will only write more tests for the next 10~15 days) I will publish the first (non-pre) release of dbdeployer. From that moment, I'd like to follow the recommendations of the Semantic Versioning: The initial version will be 1[...]

Online Schema Change for Tables with Triggers.

Mon, 12 Mar 2018 05:53:40 +0000

In this post, We will learn how to handle online schema change if the table has triggers. In PXC, an alter can be made directly ( TOI ) on tables with less than a 1G ( by default) , but on a 20GB or 200GB table we need some downtime to do ( RSU ). Pt-osc is a good choice for Percona Cluster/Galera. By default percona toolkit’s pt-online-schema-change will create After “insert / update / delete” triggers for maintaining the sync between the shadow and the original table. pt-online-schema-change process flow: Check out the complete slides for effective MySQL administration here If the tables has triggers already then pt-osc wont work well in those cases. It was an limitation with online schema changes. Still MySQL 5.6, We cannot create multiple triggers for the same event and type. From Documentation: There cannot be multiple triggers for a given table that have the same trigger event and action time. For example, you cannot have two BEFORE UPDATE triggers for a table. But you can have a BEFORE UPDATE and a BEFORE INSERT trigger, or a BEFORE UPDATE and an AFTER UPDATE trigger. On this case, We will have to drop the triggers before starting online schema change. And re-create the triggers after completion of online schema change. In a production environment it’s a complex operation to perform and requires a downtime. On MySQL 5.6.32: [root@mysql-5.6.32 ~]# pt-online-schema-change --version pt-online-schema-change 3.0.6 [root@mysql-5.6.32 ~]# pt-online-schema-change D=mydbops,t=employees,h=localhost \ --user=root --alter "drop column test,add column test text" \ --no-version-check --execute The table `mydbops`.`employees` has triggers. This tool needs to create its own triggers, so the table cannot already have triggers. From MySQL 5.7.2, A table can hold multiple triggers. From Documentation: It is possible to define multiple triggers for a given table that have the same trigger event and action time. For example, you can have two BEFORE UPDATE triggers for a table. Complete list of new features of MySQL 5.7 here. This relaxed the complexity of implementation of pt-osc support for tables with triggers. pt-online-schema-change – v3.0.4, released on 2017-08-02 came with an option –preserve-triggers. Which added a 5.7 only feature, To allow pt-osc to handle OSC operation even the table has triggers. We can find interesting discussions and implementation complexities in the following ticket Even Gh-ost won’t work for PXC without locking the table in MySQL 5.7. Issues On MySQL 5.7.19: [root@mysql-5.7.19 ~]# pt-online-schema-change --version pt-online-schema-change 3.0.6 [root@mysql-5.7.19 ~]# pt-online-schema-change D=mydbops,t=employees,h=localhost \ --user=root --alter "drop column test,add column test text" \ --no-version-check --preserve-triggers --execute Operation, tries, wait: analyze[...]

External Tables + Merge

Mon, 12 Mar 2018 03:16:16 +0000

This is an example of how you would upload data from a flat file, or Comma Separated Value (CSV) file. It’s important to note that in the file upload you are transferring information that doesn’t have surrogate key values by leveraing joins inside a MERGE statement. Step #1 : Create a virtual directory You can create a virtual directory without a physical directory but it won’t work when you try to access it. Therefore, you should create the physical directory first. Assuming you’ve created a /u01/app/oracle/upload file directory on the Windows platform, you can then create a virtual directory and grant permissions to the student user as the SYS privileged user. The syntax for these steps is: CREATE DIRECTORY upload AS '/u01/app/oracle/upload'; GRANT READ, WRITE ON DIRECTORY upload TO student; Step #2 : Position your CSV file in the physical directory After creating the virtual directory, copy the following contents into a file named kingdom_import.csv in the /u01/app/oracle/upload directory or folder. If you attempt to do this in Windows, you need to disable Windows UAC before performing this step. Place the following in the kingdom_import.csv file. The trailing commas aren’t too meaningful in Oracle but they’re very helpful if you use the file in MySQL. A key element in creating this files requires that you avoid trailing line returns at the bottom of the file because they’re inserted as null values. There should be no lines after the last row of data. 'Narnia',77600,'Peter the Magnificent','20-MAR-1272','19-JUN-1292', 'Narnia',77600,'Edmund the Just','20-MAR-1272','19-JUN-1292', 'Narnia',77600,'Susan the Gentle','20-MAR-1272','19-JUN-1292', 'Narnia',77600,'Lucy the Valiant','20-MAR-1272','19-JUN-1292', 'Narnia',42100,'Peter the Magnificent','12-APR-1531','31-MAY-1531', 'Narnia',42100,'Edmund the Just','12-APR-1531','31-MAY-1531', 'Narnia',42100,'Susan the Gentle','12-APR-1531','31-MAY-1531', 'Narnia',42100,'Lucy the Valiant','12-APR-1531','31-MAY-1531', 'Camelot',15200,'King Arthur','10-MAR-0631','12-DEC-0686', 'Camelot',15200,'Sir Lionel','10-MAR-0631','12-DEC-0686', 'Camelot',15200,'Sir Bors','10-MAR-0631','12-DEC-0635', 'Camelot',15200,'Sir Bors','10-MAR-0640','12-DEC-0686', 'Camelot',15200,'Sir Galahad','10-MAR-0631','12-DEC-0686', 'Camelot',15200,'Sir Gawain','10-MAR-0631','12-DEC-0686', 'Camelot',15200,'Sir Tristram','10-MAR-0631','12-DEC-0686', 'Camelot',15200,'Sir Percival','10-MAR-0631','12-DEC-0686', 'Camelot',15200,'Sir Lancelot','30-SEP-0670','12-DEC-0682', Step #3 : Reconnect as the student user Disconnect and connect as the student user, or reconnect as the student user. The reconnect syntax that protects your password is: CONNECT student@xe Step #4 : Run the script that creates tables and sequences Copy the following into a create_kingdom_upload.sql file withi[...]

Concurrent sandbox deployment

Sun, 11 Mar 2018 22:16:00 +0000

Version 0.3.0 of dbdeployer has gained the ability of deploying multiple sandboxes concurrently. Whenever we deploy a group of sandboxes (replication, multiple) we can use the --concurrent flag, telling dbdeployer that it should run operations concurrently. What happens when a single sandbox gets deployed? There are six sets of operations: Create the sandbox directory and write down its scripts; Run the initialisation script; Start the database server; Run the pre-grants SQL commands (if any;) Load the grants; Run the post-grants SQL commands (if any;) When several sandboxes are deployed concurrently, dbdeployer runs only the first step, and then creates a list of commands with an associated priority index. These commands are assembled for every sandbox, and then executed concurrently for every step. The sequence of events for a deployment of three sandboxes in replication would be like this: Create the sandbox skeleton for every sandbox; Initialise all database servers; start all the servers; run the pre-grants, grants, post-grants scripts. Runs the group initialisation script (start master and slaves, or setup group replication). Depending on the computer architecture, the server version, and the number of nodes, the speed of deployment can increase from 2 to 5 times. Let's see an example: $ time dbdeployer deploy replication 5.7.21[...]real 0m13.789suser 0m1.143ssys 0m1.873s$ time dbdeployer deploy replication 5.7.21 --concurrent[...]real 0m7.780suser 0m1.329ssys 0m1.811s There is a significant speed increase. The gain rises sharply if we use an higher number of nodes. $ time dbdeployer deploy replication 5.7.21 --nodes=5[...]real 0m23.425suser 0m1.923ssys 0m3.106s$ time dbdeployer deploy replication 5.7.21 \ --nodes=5 --concurrent[...]real 0m7.686suser 0m2.248ssys 0m2.777s As we can see, the time for deploying 5 nodes is roughly the same used for 3 nodes. While the sequential operations take time proportionally with the number of nodes, the concurrent task stays almost constant. Things a re a bit different for group replication, as the group initialisation (which happens after all the servers are up and running) takes more time than the simple master/slave deployment, and can't be easily reduced using the current code. A similar optimisation happens when we delete multiple sandboxes. Here the operation is at sandbox level (1 replication cluster = 1 sandbox) not at server level, and for that reason the gain is less sharp. Still, operations are noticeably faster. There is room for improvement, but I have seen that the total testing time for dbdeployer test suite has dropped from 26 to 15 minutes. I think it was a week end well spent.

Checking User Threads With gdb in MySQL 5.7+

Sun, 11 Mar 2018 14:42:00 +0000

In one of my gdb-related posts last year I noted that there is no more simple global list of user threads in MySQL 5.7+:"I had highlighted Global_THD_manager singleton also as during my next gdb sessions I had found out that simple global list of threads is also gone and in 5.7 everything is done via that Global_THD_manager. This is a topic for some other post, though."In that post and many times later when I had to deal with MySQL 5.7+ I just checked OS threads one by one in gdb using thread  1 ... thread N commands. This is not efficient at best, as I also hit numerous background threads that I often do not care about. So, a couple of weeks ago I finally decided to get back to this topic and find out how to check just user threads one by one in recent MySQL versions. I had a nice hint by Shane Bester on how to get information about $i-th thread (that he shared in one of his comments to my Facebook post):set $value = (THD**)(Global_THD_manager::thd_manager-> + (sizeof(THD**) * $i))I've attached gdb to an instance of Percona Server 5.7.x that I had running in my CentOS 6.9 VM and tried few commands to check types and content of the Global_THD_manager elements: (gdb) p Global_THD_manager::thd_manager$1 = (Global_THD_manager *) 0x7fab087fd000(gdb) p Global_THD_manager::thd_manager->thd_list$2 = {m_size = 2, m_capacity = 500, m_buff = {{      data = "\000\060b\344\252\177\000\000\000\220i\344\252\177\000\000\000\200x\344\252\177", '\000' , align = {}}},  m_array_ptr = 0x7fab087fd010, m_psi_key = 0} So, we see that internally there is some array of elements thd_list with m_size items (2 in my case) probably stored in some pre-allocated buffer of m_capacity (500) elements, stored in The type of elements is not clear, but we can try Shane's hint and assume that they are of type THD**. Let's try to check what we see there after type castings:(gdb) p (THD**)(Global_THD_manager::thd_manager->$4 = (THD **) 0x7fab087fd010(gdb) p  *(THD**)(Global_THD_manager::thd_manager->$5 = (THD *) 0x7faae4623000(gdb) p  **(THD**)(Global_THD_manager::thd_manager->$6 = { = {    _vptr.MDL_context_owner = 0x1c51f50}, = {...So, we get reasonable addresses and when we dereference the resulting THD** pointer twice we indeed get a structure that looks like THD of MySQL 5.7+ (it's very different, say, in MariaDB 10.1.x), with reasonable content (that is huge and skipped above).I've tried to get processlist id of thread based on findings of that post using intermediate gdb variables:(gdb) set $ppthd = (THD**)(Global_THD_manager::thd_manager-> p *($ppthd)[...]

This Week in Data with Colin Charles 31: Meltdown/Spectre Performance Regressions and Percona Live 2018

Sat, 10 Mar 2018 00:25:08 +0000

Join Percona Chief Evangelist Colin Charles as he covers happenings, gives pointers and provides musings on the open source database community. Have you been following the Meltdown/Spectre performance regressions? Some of the best blog posts have been coming from Brendan Gregg, who’s keynoting at Percona Live this year. We’ve also got Scott Simpson from Upwork giving a keynote about how and why they use MongoDB. This is in addition to all the other fun talks we have, so please register now. Don’t forget to also book your hotel room! Even though the Percona Live conference now covers much more than just MySQL, it’s worth noting that the MySQL Community Awards 2018: Call for Nominations! is happening now. You have until Friday, March 15, 2018, to make a nomination. Winners get into the Hall of Fame. Yes, I am also on the committee to make selections. Another highlight: Open-sourcing a 10x reduction in Apache Cassandra tail latency by Dikang Gu of Instagram (Facebook). This is again thanks to RocksDB. Check out Rocksandra, and don’t forget to register for Percona Live to see the talk: Cassandra on RocksDB. This week, I spent some time at Percona Headquarters in Raleigh, North Carolina. The building from the outside is pictured well in Google Maps. I thought it might be fun to show you a few photos (the office is huge with quite a handful working there despite the fact that Percona is largely remote). Percona awards and bookshelf, featuring some very antique encyclopedias.  Peter Zaitsev, Percona CEO, outside his office (no, it is not an open office plan – everyone has rooms, including visitors like myself).  We’re all at SCALE16x now – so come see our talks (Peter Zaitsev and I are both speaking), and we have a booth where you can say hello to Rick Golba, Marc Sherwood and Dave Avery. Releases Percona XtraDB Cluster 5.7.21-29.26 Percona Monitoring and Management 1.8.1 – improvements for ProxySQL, and improvements for Amazon RDS. Percona Server for MongoDB 3.4.13-2.11 – MongoRocks fixes. MySQL 8.0.5 community edition preview for Oracle Linux 7 for ARM64 (preview) – we don’t have 8.0.5 yet as of this writing (8.0.4rc is the most current), but glancing at the documentation, there are also some unused version numbers. Amazon Aurora with MySQL Compatibility is Available in the Asia Pacific (Singapore) Region – they only announced the 3rd AZ in mid-January, and now you have Aurora MySQL in Singapore too! Link List MySQL security for real users – a great read from Giuseppe Maxia, also the bit about the disconnect between developers and the user community TPCC-Like Workload for Sysbench 1.0 – Nice to be able to have a TPC-C like workload in sysbench. Migrating MySQL Users to Amazon RDS HTTP Ana[...]

MySQL : command delimiter curiosity – go & ego

Fri, 09 Mar 2018 22:21:07 +0000

Recently, I received a question related to GO as delimter to send a query. The user got some generated statements from a third party tool that looked like this: /* CreateTable VersionInfo */ CREATE TABLE VersionInfo (Version BIGINT NOT NULL) ENGINE = INNODB GO /* VersionMigration migrated */ /* VersionUniqueMigration migrating ========================================== */ /* CreateIndex VersionInfo (Version) */ CREATE UNIQUE INDEX UC_Version ON VersionInfo (Version ASC) GO and so on… To be honest I was lost ?! I never heard about that syntax and I was convinced that this was not valid (and you?)… But in fact it is ! It’s the long command name for \g and EGO is the one for \G. You can try help in the client (see the manual) and you will see it: ego (\G) Send command to mysql server, display result vertically. ... go (\g) Send command to mysql server. I wasn’t aware of this… So I tried of course: node1 [localhost] {root} ((none)) > select now() -> go -> so it failed, it expected ‘;‘, ‘\g‘ or ‘\G‘ to send the command… Then I found this bug #69534 where the solution was explained: --named-commands. In fact the client needs to be started with this option to enable the long named commands, see the manual. The manual is not that clear as it takes for example \q and quit… but in fact both are working even when --named-commands is not enabled. Let’s try starting the client with that option to see if it works: node1 [localhost] {msandbox} ((none)) > select now() -> go +---------------------+ | now() | +---------------------+ | 2018-03-09 22:49:04 | +---------------------+ 1 row in set (0.00 sec) Wuuhuuu It works \o/ So I learned something about MySQL that was completely NOT new. I don’t know how useful this can be other than for a quiz but at least if you have a tool generating statements like these ones, now you know how to use them even without replacing all GO by ‘;‘.