Apr
16
2019
--

Percona Universities in South America Next Week

Percona University April 2019

Percona University April 2019There’s just one week to go before the first of this year’s Percona University events in South America. We’re really pleased with the lineup for all three events. We’re also incredibly happy with the response that we have had from the community. While we realize that a free event is… well… free… you are still giving up your time and travel. We place great value on that and we’re making sure that you’ll be hearing quality technical talks from Percona and our guest speakers. Most of the talks will be presented in Spanish – Portuguese in Brazil – although slides will be made available in English.

In fact, the events have been so popular that it’s quite possible that by the time you read this we’ll be operating a wait list for Montevideo (Tuesday April 23), Buenos Aires (Thursday, April 25), and São Paulo (Saturday, April 27).

A request…

So that others don’t miss out on this (rare!) opportunity, if you have reserved a place and can no longer attend, please can you cancel your booking? You can do that after logging into your eventbrite (or MeetUp) account.

After all… we want to make sure that these events are so successful that our CEO, Peter Zaitsev, will find the idea of returning to South America to present at a future series of events completely irresistible.

To wrap up this blog post, let me mention that for the Montevideo event, we’ll have some guest speakers from other companies, to make the content even better; and remember there is a raffle at the end, in which you can get a signed copy of the High Performance MySQL book signed by Peter himself (and other goodies to be discussed ;)).

Looking forward to seeing you next Tuesday!

Apr
15
2019
--

A Truly Open Source Database Conference

companies represented by speakers at percona live 2019

Percona Live 2019Many of our regular attendees already know that the Percona Live Open Source Database Conference is not all about Percona software or, indeed, all about Percona. However, with moving to a new city—Austin, TX— we have realized that it’s something we ought to shout out loud and clear. Our conference really is technology agnostic! As long as submissions were related to open source databases then they were candidate for selection.

We have thirteen tracks at this year’s conference including a track entitled “Other Open Source Databases” which we are presenting alongside tracks dedicated to MySQL®, MariaDB®, MongoDB®, and PostgreSQL. And that’s not all. While most talks are technology-oriented, we also have tracks that are highly relevant if you are managing technology aspects of your business. For those still considering the impact of GDPR you’ll be able to hear talks about other issues relating to compliance and data security that you might well want to get to grips with. Or perhaps consider the talks oriented towards business and enterprise. Maybe you are looking to minimize your license costs by moving from proprietary to open source databases? In which case our migration track might be for you. There are five more tracks for you to discover… why not take a look?

We’d like to thank the volunteer conference committee again for their contributions in developing this fantastic, diverse, and intriguing program!

Companies represented by speakers at Percona Live 2019

Also, of course, not all of the talks are given by Percona speakers. As you can see from this graphic at least sixty companies are represented by speakers at the event, including some huge names not just in the open source space but in the tech space as a whole. Anyone heard of Facebook? Uber? Oracle? Walmart? MailChimp? Alibaba… I won’t list all sixty names, but you get the idea! In fact, both Facebook and Alibaba are sponsoring their own tracks at this year’s conference, alongside PingCap presenting a track dedicated to TiDB. Don’t miss out! Our advanced rate registration ends on Sunday April 21 after which the price moves to standard registration rate. Don’t delay…

Register Now

companies represented by speakers at percona live 2019

Sponsors

We base Percona Live events in major cities, use premium venues, and sponsor our own staff to speak…Percona Live is an expensive production and we heavily subsidize the tickets. We are eternally grateful to our sponsors who share the costs of keeping Percona Live special. Without their support it would be very difficult to host an event of this quality and scale.

Diamond sponsors

continuent

VividCortex

Platinum sponsors

Veritas Logo

AWS

Gold sponsors

EnterpriseDB

Silver Sponsors

mysql
altinity
PingCAP
SmartStyle
Alibaba
facebook

Branding sponsors

bloomberg

Media sponsors

Austin Technology Council

Thanks again to all of our sponsors, we appreciate your support!

Apr
11
2019
--

MySQL 8.0 Architecture and Enhancement Webinar: Q & A

MySQL 8.0 Architecture and Enhancement

MySQL 8.0 Architecture and EnhancementIn this blog, I will provide answers to the Q & A for the MySQL 8.0 Architecture and Enhancement webinar.

First, I want to thank everybody for attending my April 9, 2019, webinar. The recording and slides are available here. Below is the list of your questions that I was unable to answer fully during the webinar.

Q: What kind of Encryption levels are provided into MySQL 8.0?

The MySQL data-at-rest encryption feature supports the Advanced Encryption Standard (AES) block-based encryption algorithm.

In MySQL block_encryption_mode variable controls the block encryption mode. The default setting is aes-128-ecb. Set this option to aes-256-cbc, for example, under the [mysqld] option group in the MySQL configuration file (/etc/my.cnf):

[mysqld]
block_encryption_mode=aes-256-cbc

Q: At what frequency does the redo log buffer flush the changes to disk? When is the commit variable set to zero?

Here’s an overview:

  • innodb_flush_log_at_trx_commit variable can be configured to set flush frequency in MySQL 5.7 and 8.0 it’s set to 1 by default.
  • innodb_flush_log_at_trx_commit =0  logs are written and flushed to disk once per second. Transactions for which logs have not been flushed can be lost in a crash.
  • innodb_flush_log_at_trx_commit =1 is required for full ACID compliance. Logs are written and flushed to disk at each transaction commit. Default Setting.
  • innodb_flush_log_at_trx_commit =2 logs are written after each transaction commit and flushed to disk once per second. Transactions for which logs have not been flushed can be lost in a crash.

Q: How are persistent variables reset?

Using the RESET PERSIST command we can remove persisted global system variable settings from the mysqld-auto.cnf.

Example:

mysql > SET PERSIST binlog_encryption=ON;
Query OK, 0 rows affected (0.00 sec)
$ cat data/mysqld-auto.cnf
{ "Version" : 1 , "mysql_server" : { "mysql_server_static_options" : { "binlog_encryption" : { "Value" : "ON" , "Metadata" : { "Timestamp" : 1554896858076255 , "User" : "msandbox" , "Host" : "localhost" } } } } }
MySQL > RESET PERSIST binlog_encryption;
Query OK, 0 rows affected (0.00 sec)
$ cat data/mysqld-auto.cnf
{ "Version" : 1 , "mysql_server" : { } }

To reset all persistent variables use following command.

mysql > RESET PERSIST;
Query OK, 0 rows affected (0.00 sec)

Q: Does pt-config-diff work with these persistent vs. my.cnf variable settings?

No, it will not work. Due to config format differences in these files.

$ pt-config-diff ./my.sandbox.cnf data/mysqld-auto.cnf
Cannot auto-detect the MySQL config format at /home/lalit/perl5/bin/pt-config-diff line 3010.

Q: Regarding the UNDO Tablespace, do we have any specific retention to follow?

This is not required because in MySQL 8.0, the innodb_undo_log_truncate variable is enabled by default. It will perform an automatic truncate operation on the UNDO tablespace. When undo tablespaces exceed the threshold value defined by innodb_max_undo_log_size (default value is 1024 MiB) they are marked for truncation.

Truncating the undo tablespace performs the following action:

  1. Performs deactivation of undo tablespace
  2. Truncation of undo tablespace
  3. Reactivation of undo tablespaces

NOTE:  Truncating undo logs that reside in the system tablespace is not supported. 

Q: Corrupted data on disk where the secondary indexes don’t match to the primary?

I’ll be happy to respond to this, but I’ll need a little bit more information… feel free to add more detail to the comments section and I’ll see if I can provide some insight.

Thanks for attending this webinar on MySQL 8.0 Architecture and Enhancement Webinar . You can find the slides and a recording here.

 

Apr
10
2019
--

How to Add More Nodes to an Existing ProxySQL Cluster

proxysql on application instances

In my previous post, some time ago, I wrote about the new cluster feature of ProxySQL. For that post, we were working with three nodes, now we’ll work with even more! If you’ve installed one ProxySQL per application instance and would like to work up to more, then this post is for you. If this is new to you, though, read my earlier post first for more context.

Check the image below to understand the structure of “one ProxySQL per application”. This means you have ProxySQL installed, and your application (Java, PHP, Apache server etc) in the same VM (virtual machine).

Schematic of a ProxySQL cluster

Having taken a look at that you probably have a few questions, such as:

  • What happens if you have 20 nodes synced and you now need to add 100 or more nodes?
  • How can I sync the new nodes without introducing errors?

Don’t be scared, it’s a simple process.

Remember there are only four tables which can be synced over the cluster. These tables are:

  • mysql_query_rules
  • mysql_servers
  • mysql_users
  • proxysql_servers

From a new proxysql cluster installation

After a fresh installation all those four tables are empty. That means if we configure it as a new node in a cluster, all rows in those tables will be copied instantly.

Generally the installation is straightforward.

Now ProxySQL is up and all the configuration are in default settings

Connect to the ProxySQL console:

mysql -uadmin -padmin -h127.0.0.1 -P6032 --prompt='\u (\d)>'

Now there are two steps remaining:

  • Configure global_variables table
  • Configure proxysql_servers table

How to configure the global_variables table

Below is an example showing the minimal parameters to set – you can change the username and password according to your needs.

You can copy and paste the username and passwords from the current cluster and monitoring process by running the next command in a node of the current cluster:

select * from global_variables where variable_name in ('admin-admin_credentials', 'admin-cluster_password', 'mysql-monitor_password', 'admin-cluster_username', 'mysql-monitor_username');

You can update the parameters of the current node by using this as a template:

update global_variables set variable_value='<REPLACE-HERE>' where variable_name='admin-admin_credentials';
update global_variables set variable_value='<REPLACE-HERE>' where variable_name='admin-cluster_username';
update global_variables set variable_value='<REPLACE-HERE>' where variable_name='admin-cluster_password';
update global_variables set variable_value='<REPLACE-HERE>' where variable_name='mysql-monitor_username';
update global_variables set variable_value='<REPLACE-HERE>' where variable_name='mysql-monitor_password';
update global_variables set variable_value=1000 where variable_name='admin-cluster_check_interval_ms';
update global_variables set variable_value=10 where variable_name='admin-cluster_check_status_frequency';
update global_variables set variable_value='true' where variable_name='admin-cluster_mysql_query_rules_save_to_disk';
update global_variables set variable_value='true' where variable_name='admin-cluster_mysql_servers_save_to_disk';
update global_variables set variable_value='true' where variable_name='admin-cluster_mysql_users_save_to_disk';
update global_variables set variable_value='true' where variable_name='admin-cluster_proxysql_servers_save_to_disk';
update global_variables set variable_value=3 where variable_name='admin-cluster_mysql_query_rules_diffs_before_sync';
update global_variables set variable_value=3 where variable_name='admin-cluster_mysql_servers_diffs_before_sync';
update global_variables set variable_value=3 where variable_name='admin-cluster_mysql_users_diffs_before_sync';
update global_variables set variable_value=3 where variable_name='admin-cluster_proxysql_servers_diffs_before_sync';
load admin variables to RUNTIME;
save admin variables to disk;

Configure “proxysql_servers” table

At this point you need keep in mind that you will need to INSERT into this table “all” the IPs from the other ProxySQL nodes.

Why so? Because this table will have a new epoch time and this process will overwrite the rest of the nodes listed by its last table update.

In our example, let’s assume the IP of the new node is 10.0.1.3 (i.e. node 3, below)

INSERT INTO proxysql_servers (hostname,port,weight,comment) VALUES ('10.0.1.1',6032,0,'p1');
INSERT INTO proxysql_servers (hostname,port,weight,comment) VALUES ('10.0.1.2',6032,0,'p2');
INSERT INTO proxysql_servers (hostname,port,weight,comment) VALUES ('10.0.1.3',6032,0,'p3');
LOAD PROXYSQL SERVERS TO RUNTIME;
SAVE PROXYSQL SERVERS TO DISK;

If you already have many ProxySQL servers in the cluster, you can run mysqldump as this will help speed up this process.

If that’s the case, you need to find the most up to date node and use mysqldump to export the data from the proxysql_servers table.

How would you find this node? There are a few stats tables in ProxySQL, and in this case we can use two of these to help identify the right node.

SELECT stats_proxysql_servers_checksums.hostname, stats_proxysql_servers_metrics.Uptime_s, stats_proxysql_servers_checksums.port, stats_proxysql_servers_checksums.name, stats_proxysql_servers_checksums.version, FROM_UNIXTIME(stats_proxysql_servers_checksums.epoch) epoch, stats_proxysql_servers_checksums.checksum, stats_proxysql_servers_checksums.diff_check FROM stats_proxysql_servers_metrics JOIN stats_proxysql_servers_checksums ON stats_proxysql_servers_checksums.hostname = stats_proxysql_servers_metrics.hostname WHERE stats_proxysql_servers_metrics.Uptime_s > 0 ORDER BY epoch DESC

Here’s an example output

+------------+----------+------+-------------------+---------+---------------------+--------------------+------------+
| hostname   | Uptime_s | port | name              | version | epoch               | checksum           | diff_check |
+------------+----------+------+-------------------+---------+---------------------+--------------------+------------+
| 10.0.1.1   | 1190     | 6032 | mysql_users       | 2       | 2019-04-04 12:04:21 | 0xDB07AC7A298E1690 | 0          |
| 10.0.1.2   | 2210     | 6032 | mysql_users       | 2       | 2019-04-04 12:04:18 | 0xDB07AC7A298E1690 | 0          |
| 10.0.1.1   | 1190     | 6032 | mysql_query_rules | 1       | 2019-04-04 12:00:07 | 0xBC63D734643857A5 | 0          |
| 10.0.1.1   | 1190     | 6032 | mysql_servers     | 1       | 2019-04-04 12:00:07 | 0x0000000000000000 | 0          |
| 10.0.1.1   | 1190     | 6032 | proxysql_servers  | 1       | 2019-04-04 12:00:07 | 0x233638C097DE6190 | 0          |
| 10.0.1.2   | 2210     | 6032 | mysql_query_rules | 1       | 2019-04-04 11:43:13 | 0xBC63D734643857A5 | 0          |
| 10.0.1.2   | 2210     | 6032 | mysql_servers     | 1       | 2019-04-04 11:43:13 | 0x0000000000000000 | 0          |
| 10.0.1.2   | 2210     | 6032 | proxysql_servers  | 1       | 2019-04-04 11:43:13 | 0x233638C097DE6190 | 0          |
| 10.0.1.2   | 2210     | 6032 | admin_variables   | 0       | 1970-01-01 00:00:00 |                    | 0          |
| 10.0.1.2   | 2210     | 6032 | mysql_variables   | 0       | 1970-01-01 00:00:00 |                    | 0          |
| 10.0.1.1   | 1190     | 6032 | admin_variables   | 0       | 1970-01-01 00:00:00 |                    | 0          |
| 10.0.1.1   | 1190     | 6032 | mysql_variables   | 0       | 1970-01-01 00:00:00 |                    | 0          |
+------------+----------+------+-------------------+---------+---------------------+--------------------+------------+

For each table, we can see different versions, each related to table changes.  In this case, we need to look for the latest epoch time for the table “proxysql_servers“. In this example , above, we can see that the server with the IP address of  10.0.1.1 is the latest version.  We can now run the next command to get a backup of all the IP data from the current cluster

mysqldump --host=127.0.0.1 --port=6032 --skip-opt --no-create-info --no-tablespaces --skip-triggers --skip-events main proxysql_servers > proxysql_servers.sql

Now, copy the output to the new node and import into proxysql_servers table. Here’s an example: the data has been exported to the file proxysql_servers.sql which we’ll now load to the new node:

source proxysql_servers.sql
LOAD PROXYSQL SERVERS TO RUNTIME;
SAVE PROXYSQL SERVERS TO DISK;

You can run the next SELECTs to verify that the new node has the data from the current cluster, and in doing so ensure that the nodes are in sync as expected:

select * from mysql_query_rules;
select * from mysql_servers;
select * from mysql_users ;
select * from proxysql_servers;

How can we check if there are errors in the synchronization process?

Run the next command to fetch data from the table stats_proxysql_servers_checksums:

SELECT hostname, port, name, version, FROM_UNIXTIME(epoch) epoch, checksum, diff_check FROM stats_proxysql_servers_checksums  ORDER BY epoch;

Using our example, here’s the data saved in stats_proxysql_servers_checksums for the proxysql_servers table

admin ((none))>SELECT hostname, port, name, version, FROM_UNIXTIME(epoch) epoch, checksum, diff_check FROM stats_proxysql_servers_checksums  ORDER BY epoch;
+------------+------+-------------------+---------+---------------------+--------------------+------------+---------------------+
| hostname   | port | name              | version | epoch               | checksum           | diff_check | DATETIME('NOW')     |
+------------+------+-------------------+---------+---------------------+--------------------+------------+---------------------+
...
| 10.0.1.1   | 6032 | proxysql_servers  | 2       | 2019-03-25 13:36:17 | 0xC7D7443B96FC2A94 | 92         | 2019-03-25 13:38:31 |
| 10.0.1.2   | 6032 | proxysql_servers  | 2       | 2019-03-25 13:36:34 | 0xC7D7443B96FC2A94 | 92         | 2019-03-25 13:38:31 |
...
| 10.0.1.3   | 6032 | proxysql_servers  | 2       | 2019-03-25 13:37:00 | 0x233638C097DE6190 | 0          | 2019-03-25 13:38:31 |
...
+------------+------+-------------------+---------+---------------------+--------------------+------------+---------------------+

As we can see in the column “diff” there are some differences in the checksum of the other nodes: our new node (IP 10.0.1.3) has the checksum 0x233638C097DE6190 where as nodes 1 and 2 both show 0xC7D7443B96FC2A94

This indicates that these nodes have different data. Is this correct? Well, yes, in this case, since in our example the nodes 1 and 2 were created on the current ProxySQL cluster

How do you fix this?

Well, you connect to the node 1 and INSERT the data for node 3 (the new node).

This change will propagate the changes over the current cluster – node 2 and node 3 – and will update the table proxysql_servers.

INSERT INTO proxysql_servers (hostname,port,weight,comment) VALUES ('10.0.1.3',6032,0,'p3');
LOAD PROXYSQL SERVERS TO RUNTIME;

What happens if the new nodes already exist in the proxysql_servers table of the current cluster?

You can add the new ProxySQL node IPs to the current cluster before you start installing and configuring the new nodes.

In fact, this works fine and without any issues. The steps I went through, above, are the same, but when you check for differences in the table stats_proxysql_servers_checksums you should find there are none, and that the “diff” column should be displayed as 0.

How does ProxySQL monitor other ProxySQL nodes to sync locally?

Here’s a link to the explanation from the ProxySQL wiki page https://github.com/sysown/proxysql/wiki/ProxySQL-Cluster

Proxies monitor each other, so they immediately recognize that when a checksum of a configuration changes, the configuration has changed. It’s possible that the remote peer’s configuration and its own configuration were changed at the same time, or within a short period of time. So the proxy must also check its own status.

When proxies find differences:

  • If its own version is 1 , find the peer with version > 1 and with the highest epoch, and sync immediately
  • If its own version is greater than 1, starts counting for how many checks they differ
    • when the number of checks in which they differ is greater than cluster__name___diffs_before_sync and cluster__name__diffs_before_sync itself is greater than 0, find the peer with version > 1 and with the highest epoch, and sync immediately.

Note: it is possible that a difference is detected against one node, but the sync is performed against a different node. Since ProxySQL bases the sync on the node with the highest epoch, it is expected that all the nodes will converge.

To perform the syncing process:

These next steps run automatically when other nodes detect a change: this is an example for the table mysql_users

The same connection that’s used to perform the health check is used to execute a series of SELECTstatements in the form of SELECT _list_of_columns_ FROM runtime_module

SELECT username, password, active, use_ssl, default_hostgroup, default_schema, schema_locked, transaction_persistent, fast_forward, backend, frontend, max_connections FROM runtime_mysql_users;

In the node where it detected a difference in the checksum, it will run the next statements automatically:

DELETE FROM mysql_servers;
INSERT INTO mysql_users(username, password, active, use_ssl, default_hostgroup, default_schema, schema_locked, transaction_persistent, fast_forward, backend, frontend, max_connections) VALUES ('user_app','secret_password', 1, 0, 10, '', 0, 1, 0, 0, 1, 1000);
LOAD MYSQL USERS TO RUNTIME;
SAVE MYSQL USERS TO DISK;

Be careful:

After you’ve added the new ProxySQL node to the cluster, all four tables will sync immediately using the node with the highest epoch time as the basis for sync. Any changes to the tables previously added to the cluster, will propagate and overwrite the cluster

Summary

ProxySQL cluster is a powerful tool, and it’s being used more and more in production environments. If you haven’t tried it before, I’d recommend that you start testing ProxySQL cluster with a view to improving your application’s service. Hope you found this post helpful!

 

Apr
09
2019
--

Upcoming Webinar Wed 4/10: Extending and Customizing Percona Monitoring and Management

Percona Monitoring and Management 1.17.0

Percona Monitoring and Management 1.17.0Please join Percona’s Product Manager, Michael Coburn, as he presents his talk Extending and Customizing Percona Monitoring and Management on April 10th, 2019 at 10:00 AM PDT (UTC-7) / 1:00 PM EDT (UTC-4).

Register Now

Do you already run stock PMM in your environment and want to learn how you extend the PMM platform? If so, come learn about:

1. Dashboard Customizations
* How to create a custom dashboard from existing graphs, or build Cross Server Dashboards
2. External Exporters – Monitor any service, anywhere!
* Adding an exporter, view the data in data exploration, to deploying a working Dashboard
3. Working with custom queries (MySQL and PostgreSQL)
* Execute SELECT statements against your database and store in Prometheus
* Build Dashboards relevant to your environment
4. Customizing Exporter Options
* Enable deactivated functionality that applies to your environment
5. Using Grafana Alerting
* Moreover, how to set up channels (SMTP, Slack, etc)
* What’s more, how to configure thresholds and alerts
6. Using MySQL/PostgreSQL Data Source
* Also, execute SELECT statements against your database and plot your application metrics

In order to learn more, register for Extending and Customizing Percona Monitoring and Management.

Apr
08
2019
--

Upcoming Webinar Tues 4/9: MySQL 8.0 Architecture and Enhancement

MySQL 8.0 Architecture and Enhancement

MySQL 8.0 Architecture and EnhancementPlease join Percona’s Bug Analyst, Lalit Choudhary as he presents his talk MySQL 8.0 Architecture and Enhancement on Tuesday, April 9th, 2019 at 6:00 AM PDT (UTC-7) / 9:00 AM EDT (UTC-4).

Register Now

The release of MySQL 8.0 offers much more to users compared to previous releases. There are major changes in architecture as well as adding differentiating features, and improvements to manage the database more efficiently.

In our talk we will go through, MySQL 8.0 architecture:
* On Disk
* In-memory

Examples and use cases will be shared showing the new features introduced in MySQL 8.0.

Register for MySQL 8.0 Architecture and Enhancement to learn more.

Apr
03
2019
--

Running MySQL / Percona Server in Kubernetes with a Custom Config

modify MySQL config in Kubernetes

modify MySQL config in KubernetesAs we continue the development of our Percona Operators to simplify database deployment in Kubernetes (Percona Server for MongoDB Operator 0.3.0 and Percona XtraDB Cluster Operator 0.3.0), one very popular question I get is: how does deployment in Kubernetes affect MySQL performance? Is there a big performance penalty? So I plan to look at how to measure and compare the performance of Kubernetes deployments to bare metal deployments. Kubernetes manages a lot of infrastructure resources like network, storage, cpu, and memory, so we need to look individually at different components.

To begin: I plan to run a single MySQL (Percona Server) instances in a Kubernetes deployment, and use local storage (fast NMVe device). I also want to customize my MySQL configuration, as the one that is supplied in public images are pretty much all set to defaults.

Let’s take a look at how we can customize it.

  1. We are going to use a public Percona Server docker image “percona:ps-8.0.15-5”, it will deploy the latest version (at the time of writing) Percona Server for MySQL 8.0.15
  2. We will deploy this on a specific node and will assign specific local storage to use for MySQL data
  3. We’ll set up a custom configuration for MySQL.

Setting up Kubernetes

Here’s an example yaml file:

=====================
apiVersion: v1
kind: Service
metadata:
  name: mysql
spec:
  ports:
  - port: 3306
  selector:
    app: mysql
  clusterIP: None
---
apiVersion: apps/v1
kind: Deployment
metadata:
  name: mysql
  spec:
  selector:
    matchLabels:
      app: mysql
    strategy:
      type: Recreate
    template:
      metadata:
        labels:
          app: mysql
      spec:
        nodeSelector:
          kubernetes.io/hostname: smblade01
        volumes:
          - name: mysql-persistent-storage
            hostPath:
              path: /mnt/fast/mysql
              type: Directory
        containers:
        - image: percona:ps-8.0.15-5
          name: mysql
          env:
            # Use secret in real usage
          - name: MYSQL_ROOT_PASSWORD
            value: password
          ports:
          - containerPort: 3306
            name: mysql
          volumeMounts:
          - name: mysql-persistent-storage
            mountPath: /var/lib/mysql
===============

There is a lot of typical Kubernetes boilerplate to create a deployment, but the most important parts to note:

  • We choose the node where to deploy with nodeSelector (lines 28–29).
  • We allocate the local storage for MySQL volume with hostPath (lines 31–34).

After deploying this, we make sure the Pod is running

Kubectl get pods
NAME                      READY    STATUS     RESTARTS   AGE     IP             NODE         NOMINATED NODE    READINESS GATES
mysql-d74d5d459-b6zrs     1/1      Running    0          3m8s    192.168.1.15   smblade01    <none>            <none>

Set up MySQL to access fast storage and modify the default config for performance

Now as we are running MySQL on a dedicated node with fast storage, we want to customize the MySQL configuration to allocate a big buffer pool and adjust its IO settings.

As I said, a downloaded image will most likely run with default settings and there is no straightforward way to pass our custom my.cnf to deployment. I’ll show you how to resolve this now.

The default my.cnf contains the directive

!includedir /etc/my.cnf.d

So the solution for the custom my.cnf is as follows:

  • Create a Kubernetes configmap from our custom my.cnf. Here’s how to achieve that:
kubectl create configmap mysql-config --from-file=my.cnf

  • Define yaml to load the configmap into the volume that mapped to /etc/my.cnf.d (nb lines 23–26 and 40-41).
apiVersion: apps/v1 # for versions before 1.9.0 use apps/v1beta2
kind: Deployment
metadata:
  name: mysql
spec:
  selector:
    matchLabels:
    app: mysql
  strategy:
    type: Recreate
  template:
    metadata:
      labels:
        app: mysql
    spec:
      nodeSelector:
        kubernetes.io/hostname: smblade01
      volumes:
        - name: mysql-persistent-storage
          hostPath:
            path: /mnt/fast/mysql
            type: Directory
       - name: config-volume
          configMap:
            name: mysql-config
            optional: true
      containers:
      - image: percona:ps-8
        name: mysql
        env:
          # Use secret in real usage
        - name: MYSQL_ROOT_PASSWORD
          value: password
        ports:
        - containerPort: 3306
          name: mysql
        volumeMounts:
        - name: mysql-persistent-storage
          mountPath: /var/lib/mysql
        - name: config-volume
          mountPath: /etc/my.cnf.d

And here’s our example my.cnf:

[mysqld]
skip-log-bin
ssl=0
table_open_cache = 200000
table_open_cache_instances=64
back_log=3500
max_connections=4000
innodb_file_per_table
innodb_log_file_size=10G
innodb_log_files_in_group=2
innodb_log_buffer_size=64M
innodb_open_files=4000
innodb_buffer_pool_size= 100G
innodb_buffer_pool_instances=8
innodb_flush_log_at_trx_commit = 1
innodb_doublewrite=1
innodb_flush_method = O_DIRECT
innodb_file_per_table = 1
innodb_io_capacity=2000
innodb_io_capacity_max=4000
innodb_flush_neighbors = 0
innodb_use_native_aio=1
join_buffer_size=256K
sort_buffer_size=256K

When we deploy this yaml, we will have a MySQL instance running on a dedicated box with fast local storage, big log files, and 100GB allocated for its InnoDB buffer pool.

Now we’re set to proceed to our performance measurements. Stay tuned!


Photo by Joseph Barrientos on Unsplash

Apr
02
2019
--

Percona XtraDB Cluster Operator 0.3.0 Early Access Release Is Now Available

Percona XtraDB Cluster Operator

Percona announces the release of Percona XtraDB Cluster Operator 0.3.0 early access.

The Percona XtraDB Cluster Operator simplifies the deployment and management of Percona XtraDB Cluster in a Kubernetes or OpenShift environment. It extends the Kubernetes API with a new custom resource for deploying, configuring and managing the application through the whole life cycle.Percona XtraDB Cluster Operator

You can install the Percona XtraDB Cluster Operator on Kubernetes or OpenShift. While the operator does not support all the Percona XtraDB Cluster features in this early access release, instructions on how to install and configure it are already available along with the operator source code, hosted in our Github repository.

The Percona XtraDB Cluster Operator is an early access release. Percona doesn’t recommend it for production environments.

New features

Improvements

Fixed Bugs

  • CLOUD-148: Pod Disruption Budget code caused the wrong configuration to be applied for ProxySQL and had lack of multiple availability zones support.
  • CLOUD-138: The restore-backup.sh script was exiting with an error because its code was not taking into account images version numbers.
  • CLOUD-118: The backup recovery job was unable to start if Persistent Volume for backup and Persistent Volume for Pod-0 were placed in different availability zones.

Percona XtraDB Cluster is an open source, cost-effective and robust clustering solution for businesses. It integrates Percona Server for MySQL with the Galera replication library to produce a highly-available and scalable MySQL® cluster complete with synchronous multi-master replication, zero data loss and automatic node provisioning using Percona XtraBackup.

Help us improve our software quality by reporting any bugs you encounter using our bug tracking system.

Apr
02
2019
--

Simple STONITH with ProxySQL and Orchestrator

3 DC Orchestrator ProxySQL

Distributed systems are hard – I just want to echo that. In MySQL, we have quite a number of options to run highly available systems. However, real fault tolerant systems are difficult to achieve.

Take for example a common use case of multi-DC replication where Orchestrator is responsible for managing the topology, while ProxySQL takes care of the routing/proxying to the correct server, as illustrated below. A rare case you might encounter is that the primary MySQL

node01

on DC1 might have a blip of a couple of seconds. Because Orchestrator uses an adaptive health check – not only the node itself but also consults its replicas – it can react really fast and promote the node in DC2.

Why is this problematic?

The problem occurs when

node01

resolves its temporary issue. A race condition could occur within ProxySQL that could mark it back as read-write. You can increase an “offline” period within ProxySQL to make sure Orchestrator rediscovers the node first. Hopefully, it will set it to read-only immediately, but what we want is an extra layer of predictable behavior. This normally comes in the form of STONITH – by taking the other node out of action, we practically reduce the risk of conflict close to zero.

The solution

Orchestrator supports hooks to do this, but we can also do it easily with ProxySQL using its built in scheduler. In this case, we create a script where Orchestrator is consulted frequently for any nodes recently marked as

downtimed

, and we also mark them as such in ProxySQL. The script proxy-oc-tool.sh can be found on Github.

What does this script do? In the case of our topology above:

  • If for any reason, connections to MySQL on
    node01

    fail, Orchestrator will pick

    node02

      as the new primary.

  • Since
    node01

    is unreachable –  cannot modify

    read_only

    nor update replication – it will be marked as

    downtimed

    with

    lost-in-recovery

    as the reason.

  • If
    node01

    comes back online, and ProxySQL sees it before the next Orchestrator check, it can rejoin the pool. Then it’s possible that you have two writeable nodes in the hostgroup.

  • To prevent the condition above, as soon as the node is marked with downtime from Orchestrator, the script proxy-oc-tool.sh will mark it
    OFFLINE_SOFT

    so it never rejoins the

    writer_hostgroup

      in ProxySQL.

  • Once an operator fixes
    node01

    i.e. reattaches as a replica and removes the

    downtimed

    mark, the script proxy-oc-tool.sh will mark it back

    ONLINE

      automatically.

  • Additionally, if DC1 gets completely disconnected from DC2 and AWS, the script will not be able to reach Orchestrator’s raft-leader and will set all nodes to
    OFFLINE_SOFT

    preventing isolated writes on DC1.

Adding the script to ProxySQL is simple. First you download and set permissions. I placed the script in

/usr/bin/

– but you can put it anywhere accessible by the ProxySQL process.

wget https://gist.githubusercontent.com/dotmanila/1a78ef67da86473c70c7c55d3f6fda89/raw/b671fed06686803e626c1541b69a2a9d20e6bce5/proxy-oc-tool.sh
chmod 0755 proxy-oc-tool.sh
mv proxy-oc-tool.sh /usr/bin/

Note, you will need to edit some variables in the script i.e.

ORCHESTRATOR_PATH

 .

Then load into the scheduler:

INSERT INTO scheduler (interval_ms, filename)
  VALUES (5000, '/usr/bin/proxy-oc-tool.sh');
LOAD SCHEDULER TO RUNTIME;
SAVE SCHEDULER TO DISK;

I’ve set the interval to five seconds since inside ProxySQL, a shunned node will need about 10 seconds before the next read-only check is done. This way, this script is still ahead of ProxySQL and is able to mark the dead node as

OFFLINE_SOFT

 .

Because this is the simple version, there are obvious additional improvements to be made in the script like using scheduler args to specify and

ORCHESTRATOR_PATH

implement error checking.

Mar
29
2019
--

PostgreSQL: Access ClickHouse, One of the Fastest Column DBMSs, With clickhousedb_fdw

Database management systems are meant to house data but, occasionally, they may need to talk with another DBMS. For example, to access an external server which may be hosting a different DBMS. With heterogeneous environments becoming more and more common, a bridge between the servers is established. We call this bridge a “Foreign Data Wrapper” (FDW). PostgreSQL completed its support of SQL/MED (SQL Management of External Data) with release 9.3 in 2013. A foreign data wrapper is a shared library that is loaded by a PostgreSQL server. It enables the creation of foreign tables in PostgreSQL that act as proxies for another data source.

When you query a foreign table, Postgres passes the request to the associated foreign data wrapper. The FDW creates the connection and retrieves or updates the data in the external data store. Since PostgreSQL planner is involved in all of this process as well, it may perform certain operations like aggregate or joins on the data when retrieved from the data source. I cover some of these later in this post.

ClickHouse Database

ClickHouse is an open source column based database management system which claims to be 100–1,000x faster than traditional approaches, capable of processing of more than a billion rows in less than a second.

clickhousedb_fdw

clickhousedb_fdw is an open source project – GPLv2 licensed – from Percona. Here’s the link for GitHub project repository:

https://github.com/Percona-Lab/clickhousedb_fdw

It is an FDW for ClickHouse that allows you to SELECT from, and INSERT INTO, a ClickHouse database from within a PostgreSQL v11 server.

The FDW supports advanced features like aggregate pushdown and joins pushdown. These significantly improve performance by utilizing the remote server’s resources for these resource intensive operations.

If you would like to follow this post and try the FDW between Postgres and ClickHouse, you can download and set up the ontime dataset for ClickHouse.  After following the instructions, the test that you have the desired data. The ClickHouse client is a client CLI for the ClickHouse Database.

Prepare Data for ClickHouse

Now the data is ready in ClickHouse, the next step is to set up PostgreSQL. We need to create a ClickHouse foreign server, user mapping, and foreign tables.

Install the clickhousedb_fdw extension

There are manual ways to install the clickhousedb_fdw, but clickhousedb_fdw uses PostgreSQL’s coolest extension install feature. By just entering a SQL command you can use the extension:

CREATE EXTENSION clickhousedb_fdw;

CREATE SERVER clickhouse_svr FOREIGN DATA WRAPPER clickhousedb_fdw
OPTIONS(dbname 'test_database', driver '/use/lib/libclickhouseodbc.so');

CREATE USER MAPPING FOR CURRENT_USER SERVER clickhouse_svr;

CREATE FOREIGN TABLE clickhouse_tbl_ontime (  "Year" Int,  "Quarter" Int8,  "Month" Int8,  "DayofMonth" Int8,  "DayOfWeek" Int8,  "FlightDate" Date,  "UniqueCarrier" Varchar(7),  "AirlineID" Int,  "Carrier" Varchar(2),  "TailNum" text,  "FlightNum" text,  "OriginAirportID" Int,  "OriginAirportSeqID" Int,  "OriginCityMarketID" Int,  "Origin" Varchar(5),  "OriginCityName" text,  "OriginState" Varchar(2),  "OriginStateFips" text,  "OriginStateName" text,  "OriginWac" Int,  "DestAirportID" Int,  "DestAirportSeqID" Int,  "DestCityMarketID" Int,  "Dest" Varchar(5),  "DestCityName" text,  "DestState" Varchar(2),  "DestStateFips" text,  "DestStateName" text,  "DestWac" Int,  "CRSDepTime" Int,  "DepTime" Int,  "DepDelay" Int,  "DepDelayMinutes" Int,  "DepDel15" Int,  "DepartureDelayGroups" text,  "DepTimeBlk" text,  "TaxiOut" Int,  "WheelsOff" Int,  "WheelsOn" Int,  "TaxiIn" Int,  "CRSArrTime" Int,  "ArrTime" Int,  "ArrDelay" Int,  "ArrDelayMinutes" Int,  "ArrDel15" Int,  "ArrivalDelayGroups" Int,  "ArrTimeBlk" text,  "Cancelled" Int8,  "CancellationCode" Varchar(1),  "Diverted" Int8,  "CRSElapsedTime" Int,  "ActualElapsedTime" Int,  "AirTime" Int,  "Flights" Int,  "Distance" Int,  "DistanceGroup" Int8,  "CarrierDelay" Int,  "WeatherDelay" Int,  "NASDelay" Int,  "SecurityDelay" Int,  "LateAircraftDelay" Int,  "FirstDepTime" text,  "TotalAddGTime" text,  "LongestAddGTime" text,  "DivAirportLandings" text,  "DivReachedDest" text,  "DivActualElapsedTime" text,  "DivArrDelay" text,  "DivDistance" text,  "Div1Airport" text,  "Div1AirportID" Int,  "Div1AirportSeqID" Int,  "Div1WheelsOn" text,  "Div1TotalGTime" text,  "Div1LongestGTime" text,  "Div1WheelsOff" text,  "Div1TailNum" text,  "Div2Airport" text,  "Div2AirportID" Int,  "Div2AirportSeqID" Int,  "Div2WheelsOn" text,  "Div2TotalGTime" text,  "Div2LongestGTime" text,"Div2WheelsOff" text,  "Div2TailNum" text,  "Div3Airport" text,  "Div3AirportID" Int,  "Div3AirportSeqID" Int,  "Div3WheelsOn" text,  "Div3TotalGTime" text,  "Div3LongestGTime" text,  "Div3WheelsOff" text,  "Div3TailNum" text,  "Div4Airport" text,  "Div4AirportID" Int,  "Div4AirportSeqID" Int,  "Div4WheelsOn" text,  "Div4TotalGTime" text,  "Div4LongestGTime" text,  "Div4WheelsOff" text,  "Div4TailNum" text,  "Div5Airport" text,  "Div5AirportID" Int,  "Div5AirportSeqID" Int,  "Div5WheelsOn" text,  "Div5TotalGTime" text,  "Div5LongestGTime" text,  "Div5WheelsOff" text,  "Div5TailNum" text) server clickhouse_svr options(table_name 'ontime');

postgres=# SELECT a."Year", c1/c2 as Value FROM ( select "Year", count(*)*1000 as c1          
           FROM clickhouse_tbl_ontime          
           WHERE "DepDelay">10 GROUP BY "Year") a                        
           INNER JOIN (select "Year", count(*) as c2 from clickhouse_tbl_ontime          
           GROUP BY "Year" ) b on a."Year"=b."Year" LIMIT 3;
Year |   value    
------+------------
1987 |        199
1988 | 5202096000
1989 | 5041199000
(3 rows)

Performance Features

PostgreSQL has improved foreign data wrapper processing by added the pushdown feature. Push down improves performance significantly, as the processing of data takes place earlier in the processing chain. Push down abilities include:

  • Operator and function Pushdown
  • Predicate Pushdown
  • Aggregate Pushdown
  • Join Pushdown

Operator and function Pushdown

The function and operators send to Clickhouse instead of calculating and filtering at the PostgreSQL end.

postgres=# EXPLAIN VERBOSE SELECT avg("DepDelay") FROM clickhouse_tbl_ontime WHERE "DepDelay" <10; 
           Foreign Scan  (cost=1.00..-1.00 rows=1000 width=32) Output: (avg("DepDelay"))  
           Relations: Aggregate on (clickhouse_tbl_ontime)  
           Remote SQL: SELECT avg("DepDelay") FROM "default".ontime WHERE (("DepDelay" < 10))(4 rows)

Predicate Pushdown

Instead of filtering the data at PostgreSQL, clickhousedb_fdw send the predicate to Clikhouse Database.

postgres=# EXPLAIN VERBOSE SELECT "Year" FROM clickhouse_tbl_ontime WHERE "Year"=1989;                                  
           Foreign Scan on public.clickhouse_tbl_ontime  Output: "Year"  
           Remote SQL: SELECT "Year" FROM "default".ontime WHERE (("Year" = 1989)

Aggregate Pushdown

Aggregate push down is a new feature of PostgreSQL FDW. There are currently very few foreign data wrappers that support aggregate push down – clickhousedb_fdw is one of them. Planner decides which aggregates are pushed down and which aren’t. Here is an example for both cases.

postgres=# EXPLAIN VERBOSE SELECT count(*) FROM clickhouse_tbl_ontime;
          Foreign Scan (cost=1.00..-1.00 rows=1000 width=8)
          Output: (count(*)) Relations: Aggregate on (clickhouse_tbl_ontime)
          Remote SQL: SELECT count(*) FROM "default".ontime

Join Pushdown

Again, this is a new feature in PostgreSQL FDW, and our clickhousedb_fdw also supports join push down. Here’s an example of that.

postgres=# EXPLAIN VERBOSE SELECT a."Year"
                           FROM clickhouse_tbl_ontime a
                           LEFT JOIN clickhouse_tbl_ontime b ON a."Year" = b."Year";
        Foreign Scan (cost=1.00..-1.00 rows=1000 width=50);
        Output: a."Year" Relations: (clickhouse_tbl_ontime a) LEFT JOIN (clickhouse_tbl_ontime b)
        Remote SQL: SELECT r1."Year" FROM&nbsp; "default".ontime r1 ALL LEFT JOIN "default".ontime r2 ON (((r1."Year" = r2."Year")))

Percona’s support for PostgreSQL

As part of our commitment to being unbiased champions of the open source database eco-system, Percona offers support for PostgreSQL – you can read more about that here. And as you can see, as part of our support commitment, we’re now developing our own open source PostgreSQL projects such as the clickhousedb_fdw. Subscribe to the blog to be amongst the first to know of PostgreSQL and other open source projects from Percona.

As an author of the new clickhousdb_fdw – as well as other  FDWs – I’d be really happy to hear of your use cases and your experience of using this feature.


Photo by Hidde Rensink on Unsplash

Powered by WordPress | Theme: Aeros 2.0 by TheBuckmaker.com