Planet MySQL

Measuring MySQL Performance in Kubernetes

In my previous post Running MySQL/Percona Server in Kubernetes with a Custom Config I’ve looked at how to set up MySQL in Kubernetes to utilize system resources fully. Today I want to measure if there is any performance overhead of running MySQL in Kubernetes, and show what challenges I faced trying to measure it.

I will use a very simple CPU bound benchmark to measure MySQL performance in OLTP read-only workload:

sysbench oltp_read_only --report-interval=1 --time=1800 --threads=56 --tables=10 --table-size=10000000 --mysql-user=sbtest --mysql-password=sbtest --mysql-socket=/var/lib/mysql/mysql.sock run

The hardware is as follows:

Supermicro server

  • Intel(R) Xeon(R) CPU E5-2683 v3 @ 2.00GHz
  • 2 sockets / 28 cores / 56 threads
  • Memory: 256GB of RAM

The most interesting number there is 28 cores / 56 threads.  Please keep this in mind; we will need this later.

So let’s see the MySQL performance in the bare metal setup:

[ 607s ] thds: 56 tps: 22154.20 qps: 354451.12 (r/w/o: 310143.73/0.00/44307.39) lat (ms,95%): 2.61 err/s: 0.00 reconn/s: 0.00 [ 608s ] thds: 56 tps: 22247.80 qps: 355955.88 (r/w/o: 311461.27/0.00/44494.61) lat (ms,95%): 2.61 err/s: 0.00 reconn/s: 0.00 [ 609s ] thds: 56 tps: 21984.01 qps: 351641.13 (r/w/o: 307672.12/0.00/43969.02) lat (ms,95%): 2.66 err/s: 0.00 reconn/s: 0.00

So we can get about 22000 qps on this server.

Now, let’s see what we can get if the same server runs a Kubernetes node and we deploy the Percona server image on this node. I will use a modified image of Percona Server 8, which already includes sysbench inside.

You can find my image here: https://hub.docker.com/r/vadimtk/ps-8-vadim

And I use the following deployment yaml :

apiVersion: v1 kind: Service metadata: name: mysql spec: selector: app: mysql ports: - name: mysql port: 3306 protocol: TCP targetPort: 3306 --- 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/data/mysql type: Directory - name: config-volume configMap: name: mysql-config optional: true containers: - image: vadimtk/ps-8-vadim imagePullPolicy: Always 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

The most important part here is that we deploy our image on smblade01 node (the same one I ran the bare metal benchmark).

Let’s see what kind of performance we get using this setup. The number I’ve got:

[ 605s ] thds: 56 tps: 10561.88 qps: 169045.04 (r/w/o: 147921.29/0.00/21123.76) lat (ms,95%): 12.98 err/s: 0.00 reconn/s: 0.00 [ 606s ] thds: 56 tps: 10552.00 qps: 168790.98 (r/w/o: 147685.98/0.00/21105.00) lat (ms,95%): 15.83 err/s: 0.00 reconn/s: 0.00 [ 607s ] thds: 56 tps: 10566.00 qps: 169073.97 (r/w/o: 147942.97/0.00/21131.00) lat (ms,95%): 5.77 err/s: 0.00 reconn/s: 0.00 [ 608s ] thds: 56 tps: 10581.08 qps: 169359.21 (r/w/o: 148195.06/0.00/21164.15) lat (ms,95%): 5.47 err/s: 0.00 reconn/s: 0.00 [ 609s ] thds: 56 tps: 12873.80 qps: 205861.77 (r/w/o: 180116.17/0.00/25745.60) lat (ms,95%): 5.37 err/s: 0.00 reconn/s: 0.00 [ 610s ] thds: 56 tps: 20196.89 qps: 323184.24 (r/w/o: 282789.46/0.00/40394.78) lat (ms,95%): 3.02 err/s: 0.00 reconn/s: 0.00 [ 611s ] thds: 56 tps: 18033.21 qps: 288487.30 (r/w/o: 252421.88/0.00/36065.41) lat (ms,95%): 5.28 err/s: 0.00 reconn/s: 0.00 [ 612s ] thds: 56 tps: 11444.08 qps: 183129.22 (r/w/o: 160241.06/0.00/22888.15) lat (ms,95%): 5.37 err/s: 0.00 reconn/s: 0.00 [ 613s ] thds: 56 tps: 10597.96 qps: 169511.35 (r/w/o: 148316.43/0.00/21194.92) lat (ms,95%): 5.57 err/s: 0.00 reconn/s: 0.00 [ 614s ] thds: 56 tps: 10566.00 qps: 169103.93 (r/w/o: 147969.94/0.00/21133.99) lat (ms,95%): 5.67 err/s: 0.00 reconn/s: 0.00 [ 615s ] thds: 56 tps: 10640.07 qps: 170227.13 (r/w/o: 148948.99/0.00/21278.14) lat (ms,95%): 5.47 err/s: 0.00 reconn/s: 0.00 [ 616s ] thds: 56 tps: 10579.04 qps: 169264.66 (r/w/o: 148106.58/0.00/21158.08) lat (ms,95%): 5.47 err/s: 0.00 reconn/s: 0.00

You can see the numbers vary a lot, from 10550 tps to 20196 tps, with the most time being in the 10000tps range.
That’s quite disappointing. Basically, we lost half of the throughput by moving to the Kubernetes node.

But don’t panic, we can improve this. But first, we need to understand why this happens.

The answer lies in how Kubernetes applies Quality of Service for Pods. By default (if CPU or Memory limits are not defined) the QoS is BestEffort, which leads to the results we see above. To allocate all CPU resources, we need to make sure QoS Guaranteed. For this, we add the following to the image definition:

resources: requests: cpu: "55500m" memory: "150Gi" limits: cpu: "55500m" memory: "150Gi"

These are somewhat funny lines to define CPU limits. As you remember we have 56 threads, so initially I tried to set limits:

cpu: "56" , but it did not work as Kubernetes was not able to start the pod with the error Insufficient CPU. I guess Kubernetes allocates a few CPU percentages for the internal needs.

So the line

cpu: "55500m"  works, which means we allocate 55.5 CPU for Percona Server.

Let’s see what results we can have with Guaranteed QoS:

[ 883s ] thds: 56 tps: 20320.06 qps: 325145.96 (r/w/o: 284504.84/0.00/40641.12) lat (ms,95%): 2.81 err/s: 0.00 reconn/s: 0.00 [ 884s ] thds: 56 tps: 20908.89 qps: 334587.21 (r/w/o: 292769.43/0.00/41817.78) lat (ms,95%): 2.81 err/s: 0.00 reconn/s: 0.00 [ 885s ] thds: 56 tps: 20529.03 qps: 328459.46 (r/w/o: 287402.40/0.00/41057.06) lat (ms,95%): 2.81 err/s: 0.00 reconn/s: 0.00 [ 886s ] thds: 56 tps: 17567.75 qps: 281051.03 (r/w/o: 245914.53/0.00/35136.50) lat (ms,95%): 5.47 err/s: 0.00 reconn/s: 0.00 [ 887s ] thds: 56 tps: 18036.82 qps: 288509.07 (r/w/o: 252437.44/0.00/36071.63) lat (ms,95%): 5.47 err/s: 0.00 reconn/s: 0.00 [ 888s ] thds: 56 tps: 18398.23 qps: 294399.67 (r/w/o: 257603.21/0.00/36796.46) lat (ms,95%): 5.47 err/s: 0.00 reconn/s: 0.00 [ 889s ] thds: 56 tps: 18402.90 qps: 294484.45 (r/w/o: 257677.65/0.00/36806.81) lat (ms,95%): 5.47 err/s: 0.00 reconn/s: 0.00 [ 890s ] thds: 56 tps: 19428.12 qps: 310787.86 (r/w/o: 271934.63/0.00/38853.23) lat (ms,95%): 5.37 err/s: 0.00 reconn/s: 0.00 [ 891s ] thds: 56 tps: 19848.69 qps: 317646.11 (r/w/o: 277947.73/0.00/39698.39) lat (ms,95%): 5.28 err/s: 0.00 reconn/s: 0.00 [ 892s ] thds: 56 tps: 20457.28 qps: 327333.49 (r/w/o: 286417.93/0.00/40915.56) lat (ms,95%): 2.86 err/s: 0.00 reconn/s: 0.00

This is much better (mostly ranging in 20000 tps), but we still do not get to 22000 tps.

I do not have the full explanation of why there is still a 10% performance loss, but it might be related to this issue. And I see there is a work in progress to improve Guaranteed QoS performance but it was not merged into the mainstream releases yet. Hopefully, it will be in one of the next releases.

Conclusions:

  • Out of the box, you may see quite bad performance when deploying in Kubernetes POD
  • To improve your experience you need to make sure you use Guaranteed QoS. Unfortunately, Kubernetes does not make it easy. You need to manually set the number of CPU threads, which is not always obvious if you use dynamic cloud instances.
  • With Guaranteed QoS there is still a performance overhead of 10%, but I guess this is the cost we have to accept at the moment.

OpenSSL FIPS support in MySQL 8.0

Many products use OpenSSL, but for the most part, without choosing to incorporate the OpenSSL FIPS (US Federal Information Processing Standards) module. However it may be that running OpenSSL and using FIPs mode is something you should seriously consider and implement.…

Facebook Twitter Google+ LinkedIn

Meet Codership, the makers of Galera Cluster at Percona Live Austin 2019

After a short hiatus, we hope to meet and see you at Percona Live Austin 2019 (28-30 May 2019), as we have sponsored the event and have a booth in the expo hall, in addition to having some talks.

Our CEO and co-founder Seppo Jaakola will have a talk titled Galera Cluster New Features, happening in room Texas 5, on Wednesday at 11.55AM – 12.45PM. It will be a very interesting talk as Galera Cluster 4 features have made there way into MariaDB Server 10.4, and you can expect to hear a little more about when Codership, the engineers and makers of Galera Cluster will provide a MySQL version.

If you happen to sign up for tutorials, do not miss Expert MariaDB: Harness the Strengths of MariaDB Server by Colin Charles as part of the tutorial involves setting up a bootstrapped three node MariaDB Galera Cluster 10.4 with the Galera 4 replication library and learning about the other unique features it has.

For those interested in Galera Cluster, don’t forget there are a few other talks about Galera Cluster in the program, including one by Yahoo! Japan titled Immutable Database Infrastructure with Percona XtraDB Cluster which should be a great story about how they deploy a lot (think hundreds!) of Galera Cluster nodes in production.

Our booth will always have the wonderful Larisa Urse manning it, and we want to talk to you all about what you need from Galera Cluster, roadmaps, plans, how you use Percona XtraDB Cluster (PXC) (based on Galera Cluster), and more. We will have great conversations, and a whole bunch more planned for the conference including participating in the Passport Program — so dropping by our booth, talking to us, and get that coveted stamp, and you will be in the running to win a pair of Bose noise cancelling headphones. Don’t forget that we have great support and consulting services too, so come talk to us to find out more!


Updating My AWS CodeBuild Project from Ubuntu 14.04 to 18.04

I received a cheery notification email (twice) from AWS that they’re deprecating Ubuntu 14.04 on CodeBuild:

Ubuntu 14.04 EOL Notification for AWS CodeBuild

Hello,

As of April 2019, Ubuntu 14.04 has reached end of life (EOL). We will be removing it as an option in the CodeBuild console on May 15, 2019. Existing CodeBuild projects configured to use Ubuntu 14.04 will continue to work after this date but we highly recommend that customers update their projects to use “aws/codebuild/standard” build images, which are based on Ubuntu 18.04.

Note that all offered programming language runtimes are already included in the Ubuntu 18.04 image, as described in Docker Images Provided by CodeBuild. Therefore, it is no longer necessary to select runtimes separately.

Sincerely, Amazon Web Services

It’s nice the old ones will continue to work, however they gave 9 days notice that it won’t be available on the console any more. This will certainly break some workflows where projects are build and destroyed regularly. They don’t say if it creation will be removed from the API too, but if it will then some workflows with automated creation through e.g. CloudFormation will also break.

Anyway, on with the story. Checking the Personal Health Dashboard (PHD) link at the bottom, I saw the notification there too:

Personal Health Dashboard doesn’t email you automatically for every event. Thankfully, someone at AWS checked the box for “this notification is actually important so email the suckers.”

(If you want email notifications for everything on PHD, it’s not so easy as ticking a box. You have to roll up your sleeves and set it up via CloudWatch Events!)

I have just one CodeBuild project on my AWS account, and it’s running a script checking for Python package updates on my open source repositories. It’s a hack that it’s on CodeBuild in the first place, it’s just because it takes too long to fit within AWS Lambda’s limits.

After reading the linked guide “Docker Images Provided by CodeBuild”, I discovered all I needed to do was change to aws/codebuild/standard:2.0:

My first fix didn’t work when I ran it. I saw this error in the logs:

[Container] 2019/05/08 10:56:08 Waiting for agent ping [Container] 2019/05/08 10:56:10 Waiting for DOWNLOAD_SOURCE [Container] 2019/05/08 10:56:11 Phase is DOWNLOAD_SOURCE [Container] 2019/05/08 10:56:11 CODEBUILD_SRC_DIR=/codebuild/output/src870287925/src [Container] 2019/05/08 10:56:11 YAML location is /codebuild/readonly/buildspec.yml [Container] 2019/05/08 10:56:11 Processing environment variables [Container] 2019/05/08 10:56:11 Moving to directory /codebuild/output/src870287925/src [Container] 2019/05/08 10:56:11 Phase complete: DOWNLOAD_SOURCE State: FAILED [Container] 2019/05/08 10:56:11 Phase context status code: YAML_FILE_ERROR Message: This build image requires selecting at least one runtime version.

Sad face!

The new image works differently, and AWS didn’t describe this in the announcement. The runtimes are “already included” but you still need to “install” them. You do this with the runtime-versions key in the install phase of the buildspec - I needed to install Python 3.7 to run my script.

I changed my AWS::CodeBuild::Project resource in the Environment.Image and Source.BuildSpec properties:

PipCheckerBuildProject: Type: AWS::CodeBuild::Project Properties: Name: pip-checker Description: Checks pip Artifacts: Type: NO_ARTIFACTS ServiceRole: !ImportValue LambdaLogAndEmailIAMRoleArn Environment: Type: LINUX_CONTAINER ComputeType: BUILD_GENERAL1_SMALL Image: aws/codebuild/standard:2.0 Source: Type: S3 Location: !Sub ${S3Bucket}/${S3Key} BuildSpec: !Sub | version: 0.2 phases: install: runtime-versions: python: 3.7 commands: - pip3 install -r requirements.txt - apt-get update - apt-get install -y mysql-client build: commands: - python pip_checker.py TimeoutInMinutes: 60

That is, I applied this diff:

@@ -80,7 +80,7 @@ Resources: Environment: Type: LINUX_CONTAINER ComputeType: BUILD_GENERAL1_SMALL - Image: aws/codebuild/python:3.7.1 + Image: aws/codebuild/standard:2.0 Source: Type: S3 Location: !Sub ${S3Bucket}/${S3Key} @@ -88,6 +88,8 @@ Resources: version: 0.2 phases: install: + runtime-versions: + python: 3.7 commands: - pip3 install -r requirements.txt - apt-get update

I deployed this and tested the script, it worked the second time.

Phew.

Fin

Hope this helps you with this or similar CodeBuild upgrades,

—Adam

My Open Source projects that still live on

I have recently created a new library called libMariaS3 for a couple of teams at MariaDB which provides a simple C API to Amazon’s S3. This was created because we needed a library which could link to GPLv2 software and Amazon’s own library is Apache 2.0 licensed which is incompatible.

It is not a perfect code base because I had a very short amount of time to get the first release out but it was a very fun project to work on. It led me to take a quick look this morning at a couple of other things I have created in the past to see where they are at today.

libdrizzle-redux

Around 5 years ago I worked for HP’s Advanced Technology Group and I worked on several different Open Source projects during that time. One of those projects was called libAttachSQL which was a very fast, asynchronous, Apache 2.0 licensed C connector for MySQL servers.

This project wasn’t very successful but the ideas were born out of another project I created which was almost a complete re-write of libdrizzle.

libdrizzle was a BSD licensed high-performance MySQL compatible connector which was part of the Drizzle project (Drizzle itself being a microkernel fork of MySQL). When development of Drizzle stalled I took on developing libdrizzle under a new name “libdrizzle-redux”.

Work commitments got in the way and development stalled, but I was contacted one day by someone who was very interested in the work I did in that project. I helped them setup a fork and gave them the general direction I was going with the project. Today libdrizzle-redux is still developed by a company called Sociomantic and the GitHub page can be found here.

Jenkins Job Builder

In early 2012 I was a member of the core OpenStack Infrastructure team. Jenkins was used as the CI platform then and we needed a way to make minor changes to a few hundred Jenkins jobs at a time. Every time we did this something broke and releases were stalled as a result. We needed a more automated way of making changes to Jenkins jobs and I created something which was eventually called “Jenkins Job Builder” to solve this. It had a different name originally but was changed very early on.

I soon moved on to lead another project in OpenStack which was HP’s LBaaS project called “Libra”. This unfortunately didn’t live on but a lot of the design ideas can be seen in the Octavia project.

As for Jenkins Job Builder? I’m told that many companies use it today and I’ve found it in the repositories of Ubuntu and Fedora. Although I suspect a lot has changed since my original code for the project.

Honorable Mention – mydumper

One honourable mention I can think of is “mydumper“. I did not create the project but I wrote a large amount of the code and led the maintenance for a while. The lead developer of this is now Max Bubenick and it appears to be still maintained.

I get a lot of people contacting me asking me to write certain patches to mydumper. Maybe one day I will, unfortunately work commitments often get in the way of Open Source projects I would like to work on.

How to Execute and Manage MySQL Backups for Oracle DBA’s

Migrating from Oracle database to open source can bring a number of benefits. The lower cost of ownership is tempting, and pushes a lot of companies to migrate. At the same time DevOps, SysOps or DBA’s need to keep tight SLA’s to address business needs.

One of the key concerns when you plan data migration to another database, especially open source is to how to avoid data loss. It’s not too far fetched that someone accidentally deleted part of the database, someone forgot to include a WHERE clause in a DELETE query or run DROP TABLE accidentally. The question is how to recover from such situations.

Things like that may and will happen, it is inevitable but the impact can be disastrous. As somebody said, “It’s all fun and games until backup fails”. The most valuable asset cannot be compromised. Period.

The fear of the unknown is natural if you are not familiar with new technology. In fact, the knowledge of Oracle database solutions, reliability and great features which Oracle Recovery Manager (RMAN) offers can discourage you or your team to migrate to a new database system. We like to use things we know, so why migrate when our current solution works. Who knows how many projects were put on hold because the team or individual was not convinced about the new technology?

Logical Backups (exp/imp, expdp/impdb) Related resources  Migration from Oracle Database to MariaDB - A Deep Dive  How to Manage MySQL - for Oracle DBAs  Migrating from Oracle Database to MariaDB - What You Should Know

According to MySQL documentation, logical backup is “a backup that reproduces table structure and data, without copying the actual data files.” This definition can apply to both MySQL and Oracle worlds. The same is “why” and “when” you will use the logical backup.

Logical backups are a good option when we know what data will be modified so you can backup only the part you need. It simplifies potential restore in terms of time and complexity. It’s also very useful if we need to move some portion of small/medium size data set and copy back to another system (often on a different database version). Oracle use export utilities like exp and expdp to read database data and then export it into a file at the operating system level. You can then import the data back into a database using the import utilities imp or impdp.

The Oracle Export Utilities gives us a lot of options to choose what data that needs to be exported. You will definitely not find the same number of features with mysql, but most of the needs are covered and the rest can be done with additional scripting or external tools (check mydumper).

MySQL comes with a package of tools that offer very basic functionality. They are mysqldump, mysqlpump (the modern version of mysqldump that has native support for parallelization) and MySQL client which can be used to extract data to a flat file.

Below you can find several examples of how to use them:

Backup database structure only

mysqldump --no-data -h localhost -u root -ppassword mydatabase > mydatabase_backup.sql

Backup table structure

mysqldump --no-data --single- transaction -h localhost -u root -ppassword mydatabase table1 table2 > mydatabase_backup.sql

Backup specific rows

mysqldump -h localhost --single- transaction -u root -ppassword mydatabase table_name --where="date_created='2019-05-07'" > table_with_specific_rows_dump.sql

Importing the Table

mysql -u username -p -D dbname < tableName.sql

The above command will stop load if an error occurs.

If you load data directly from the mysql client, the errors will be ignored and the client will proceed

mysql> source tableName.sql

To log output, you need to use

mysql> tee import_tableName.log

You can find all flags explained under below links:

If you plan to use logical backup across different database versions, make sure you have the right collation setup. The following statement can be used to check the default character set and collation for a given database:

USE mydatabase; SELECT @@character_set_database, @@collation_database;

Another way to retrieve the collation_database system variable is to use the SHOW VARIABLES.

SHOW VARIABLES LIKE 'collation%';

Because of the limitations of the mysql dump, we often have to modify the output. An example of such modification can be a need to remove some lines. Fortunately, we have the flexibility of viewing and modifying the output using standard text tools before restoring. Tools like awk, grep, sed can become your friend. Below is a simple example of how to remove the third line from the dump file.

sed -i '1,3d' file.txt

The possibilities are endless. This is something that we will not find with Oracle as data is written in binary format.

There are a few things you need to consider when you execute logical mysql. One of the main limitations is pure support of parallelism and the object locking.

Logical backup considerations

When such backup is executed, the following steps will be performed.

  • LOCK TABLE table.
  • SHOW CREATE TABLE table.
  • SELECT * FROM table INTO OUTFILE temporary file.
  • Write the contents of the temporary file to the end of the dump file.
  • UNLOCK TABLES

By default mysqldump doesn’t include routines and events in its output - you have to explicitly set --routines and --events flags.

Another important consideration is an engine that you use to store your data. Hopefully these days most of productions systems use ACID compliant engine called InnoDB. Older engine MyISAM had to lock all tables to ensure consistency. This is when FLUSH TABLES WITH READ LOCK was executed. Unfortunately, it is the only way to guarantee a consistent snapshot of MyISAM tables while the MySQL server is running. This will make the MySQL server become read-only until UNLOCK TABLES is executed.

For tables on InnoDB storage engine, it is recommended to use --single- transaction option. MySQL then produces a checkpoint that allows the dump to capture all data prior to the checkpoint while receiving incoming changes.

The --single-transaction option of mysqldump does not do FLUSH TABLES WITH READ LOCK. It causes mysqldump to set up a REPEATABLE READ transaction for all tables being dumped.

A mysqldump backup is much slower than Oracle tools exp, expdp. Mysqldump is a single-threaded tool and this is its most significant drawback - performance is ok for small databases but it quickly becomes unacceptable if the data set grows to tens of gigabytes.

  • START TRANSACTION WITH CONSISTENT SNAPSHOT.
  • For each database schema and table, a dump performs these steps:
    • SHOW CREATE TABLE table.
    • SELECT * FROM table INTO OUTFILE temporary file.
    • Write the contents of the temporary file to the end of the dump file.
  • COMMIT.
Physical backups (RMAN)

Fortunately, most of the limitations of logical backup can be solved with Percona Xtrabackup tool. Percona XtraBackup is the most popular, open-source, MySQL/MariaDB hot backup software that performs non-blocking backups for InnoDB and XtraDB databases. It falls into the physical backup category, which consists of exact copies of the MySQL data directory and files underneath it.

It’s the same category of tools like Oracle RMAN. RMAN comes as part of the database software, XtraBackup needs to be downloaded separately. Xtrabackup is available as rpm and deb package and supports only Linux platforms. The installation is very simple:

$ wget https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-8.0.4/binary/redhat/7/x86_64/percona-XtraBackup-80-8.0.4-1.el7.x86_64.rpm $ yum localinstall percona-XtraBackup-80-8.0.4-1.el7.x86_64.rpm

XtraBackup does not lock your database during the backup process. For large databases (100+ GB), it provides much better restoration time as compared to mysqldump. The restoration process involves preparing MySQL data from the backup files, before replacing or switching it with the current data directory on the target node.

Percona XtraBackup works by remembering the log sequence number (LSN) when it starts and then copying away the data files to another location. Copying data takes some time, and if the files are changing, they reflect the state of the database at different points in time. At the same time, XtraBackup runs a background process that keeps an eye on the transaction log (aka redo log) files, and copies changes from it. This has to be done continually because the transaction logs are written in a round-robin fashion, and can be reused after a while. XtraBackup needs the transaction log records for every change to the data files since it began execution.

When XtraBackup is installed you can finally perform your first physical backups.

xtrabackup --user=root --password=PASSWORD --backup --target-dir=/u01/backups/

Another useful option which MySQL administrators do is the streaming of backup to another server. Such stream can be performed with the use of xbstream tool, like on the below example:

Start a listener on the external server on the preferable port (in this example 1984)

nc -l 1984 | pigz -cd - | pv | xbstream -x -C /u01/backups

Run backup and transfer to an external host

innobackupex --user=root --password=PASSWORD --stream=xbstream /var/tmp | pigz | pv | nc external_host.com 1984

As you may notice restore process is divided into two major steps (similar to Oracle). The steps are restored (copy back) and recovery (apply log).

XtraBackup --copy-back --target-dir=/var/lib/data innobackupex --apply-log --use-memory=[values in MB or GB] /var/lib/data

The difference is that we can only perform recovery to the point when the backup was taken. To apply changes after the backup we need to do it manually.

Point in Time Restore (RMAN recovery)

In Oracle, RMAN does all the steps when we perform recovery of the database. It can be done either to SCN or time or based on the backup data set.

RMAN> run { allocate channel dev1 type disk; set until time "to_date('2019-05-07:00:00:00', 'yyyy-mm-dd:hh24:mi:ss')"; restore database; recover database; }

In mysql, we need another tool to perform to extract data from binary logs (similar to Oracle’s archivelogs) mysqlbinlog. mysqlbinlog can read the binary logs and convert them to files. What we need to do is

The basic procedure would be

  • Restore full backup
  • Restore incremental backups
  • To identify the start and end times for recovery (that could be the end of backup and the position number before unfortunately drop table).
  • Convert necessary binglogs to SQL and apply newly created SQL files in the proper sequence - make sure to run a single mysqlbinlog command. > mysqlbinlog binlog.000001 binlog.000002 | mysql -u root -p
Encrypt Backups (Oracle Wallet)

Percona XtraBackup can be used to encrypt or decrypt local or streaming backups with xbstream option to add another layer of protection to the backups. Both --encrypt-key option and --encryptkey-file option can be used to specify the encryption key. Encryption keys can be generated with commands like

$ openssl rand -base64 24 $ bWuYY6FxIPp3Vg5EDWAxoXlmEFqxUqz1

This value then can be used as the encryption key. Example of the innobackupex command using the --encrypt-key:

$ innobackupex --encrypt=AES256 --encrypt-key=”bWuYY6FxIPp3Vg5EDWAxoXlmEFqxUqz1” /storage/backups/encrypted

To decrypt, simply use the --decrypt option with appropriate --encrypt-key:

$ innobackupex --decrypt=AES256 --encrypt-key=”bWuYY6FxIPp3Vg5EDWAxoXlmEFqxUqz1” /storage/backups/encrypted/2019-05-08_11-10-09/ Backup policies

There is no build in backup policy functionality either in MySQL/MariaDB or even Percona’s tool. If you would like to manage your MySQL logical or physical backups you can use ClusterControl for that.

ClusterControl is the all-inclusive open source database management system for users with mixed environments. It provides advanced backup management functionality for MySQL or MariaDB.

With ClusterControl you can:

  • Create backup policies
  • Monitor backup status, executions, and servers without backups
  • Execute backups and restores (including a point in time recovery)
  • Control backup retention
  • Save backups in cloud storage
  • Validate backups (full test with the restore on the standalone server)
  • Encrypt backups
  • Compress backups
  • And many others
ClusterControl: Backup Management Keep backups in the cloud

Organizations have historically deployed tape backup solutions as a means to protect
data from failures. However, the emergence of public cloud computing has also enabled new models with lower TCO than what has traditionally been available. It makes no business sense to abstract the cost of a DR solution from the design of it, so organizations have to implement the right level of protection at the lowest possible cost.

The cloud has changed the data backup industry. Because of its affordable price point, smaller businesses have an offsite solution that backs up all of their data (and yes, make sure it is encrypted). Both Oracle and MySQL does not offer built-in cloud storage solutions. Instead you can use the tools provided by Cloud vendors. An example here could be s3.

aws s3 cp severalnines.sql s3://severalnine-sbucket/mysql_backups Conclusion

There are a number of ways to backup your database, but it is important to review business needs before deciding on a backup strategy. As you can see there are many similarities between MySQL and Oracle backups which hopefully can meet you your SLA’s.

Always make sure that you practice these commands. Not only when you are new to the technology but whenever DBMS becomes unusable so you know what to do.

If you would like to learn more about MySQL please check our whitepaper The DevOps Guide to Database Backups for MySQL and MariaDB.

Tags:  MySQL backup management oracle

Not enforcing SSL on CloudSQL, really !

When creating a MySQL CloudSQL instance, SSL connections are not enforced by default and you get below in the Connections tab of the Google Cloud Platform console.  Is this a problem ?  Some people might think no, but I do not agree with them.  And if I am writing this post, you can probably guess that there is a lot to say about this subject.  Read on for the details. When creating a MySQL

SSH Differences Between Staging and INI Configuration Methods

The Question Recently, a customer asked us:

If we move to using the INI configuration method instead of staging, would password-less SSH still be required?

The Answer The answer is both “Yes” and “No”

No, for installation and updates/upgrades specifically. Since INI-based configurations force the tpm command to act upon the local host only for installs and updates/upgrades, password-less SSH is not required.

Yes, because there are certain commands that do rely upon password-less SSH to function. These are:

  • tungsten_provision_slave
  • prov-sl.sh
  • multi_trepctl
  • tpm diag (pre-6.0.5)
  • tpm diag --hosts (>= 6.0.5)
  • Any tpm-based backup and restore operations that involve a remote node
Summary The Wrap-Up

In this blog post we discussed the SSH differences between the Staging and INI configuration methods.

To learn about Continuent solutions in general, check out https://www.continuent.com/solutions

The Library Please read the docs!

For more information about monitoring Tungsten clusters, please visit https://docs.continuent.com.

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

For more information, please visit https://www.continuent.com/solutions

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

ProxySQL 2.0.3 and updated proxysql-admin tool

ProxySQL 2.0.3, released by ProxySQL, is now available for download in the Percona Repository along with an updated version of Percona’s proxysql-admin tool.

ProxySQL is a high-performance proxy, currently for MySQL,  and database servers in the MySQL ecosystem (like Percona Server for MySQL and MariaDB). It acts as an intermediary for client requests seeking resources from the database. René Cannaò created ProxySQL for DBAs as a means of solving complex replication topology issues.

The ProxySQL 2.0.3 source and binary packages available from the Percona download page for ProxySQL include ProxySQL Admin – a tool developed by Percona to configure Percona XtraDB Cluster nodes into ProxySQL. Docker images for release 2.0.3 are available as well. You can download the original ProxySQL from GitHub. GitHub hosts the documentation in the wiki format.

With ProxySQL 2.0.3, the proxysql-admin tool now uses the native ProxySQL support for Percona XtraDB Cluster and does not require custom bash scripts to keep track of PXC status.  As a result, proxysql_galera_checker and proxysql_node_monitor have been removed.

Improvements
  • The proxysql-admin tool is MySQL 8.0 compatible
Added Features
  • New option --use-ssl to use SSL for connections between ProxySQL and the backend database servers
  • New option --max-transactions-behind to determine the maximum number of writesets that can be queued before the node is SHUNNED to avoid stale reads. The default value is 100
  • New operation --update-cluster to update the cluster membership by adding server nodes as found. (Note that nodes are added but not removed).  The --writer-hg option may be used to specify which galera hostgroup to update. The --remove-all-servers option instructs to remove all servers from the mysql_servers table before updating the cluster.
  • Hostgroups can now be specified on the command-line: --writer-hg, --reader-hg, --backup-writer-hg, and --offline-hg.
    Previously, these host groups were only read from the configuration file.
  • The --enable and --update-cluster options used simultaneously have special meaning. If the cluster has not been enabled, then --enable is run.  If the cluster has already been enabled, then --update-cluster is run.
  • New command --is-enabled to see if a cluster has been enabled. This command checks for the existence of a row in the mysql_galera_hostgroups table.  The --writer-hg option may be used to specify the writer hostgroup used to search the mysql_galera_hostgroups table.
  • New command --status to display galera hostgroup information. This command lists all rows in the current mysql_galera_hostgroups table as well as all servers that belong to these hostgroups.  With the --writer-hg option, only the information for the galera hostgroup with that writer hostgroup is displayed.
Changed Features
  • Setting --node-check-interval now changes the ProxySQL global variable mysql-monitor_galera_healthcheck_interval
    Note that this is a global variable, not a per-cluster variable.
  • The option --write-node now takes only a single address as a parameter. In the singlewrite mode we only set the weight if --write-node specifies address:port.  A priority list of addresses is no longer accepted.
  • The option --writers-as-readers option now accepts a different set of values. Due to changes in the behavior of ProxySQL between version 1.4 and version 2.0 related to Galera support, the values of --writers-as-readers have been changed.  This option now accepts the following values: yes, no, and backup.
    yes: writers, backup-writers, and read-only nodes can act as readers.
    no: only read-only nodes can act as readers.
    backup: only backup-writers can act as readers.
  • The commands --syncusers, --sync-multi-cluster-users, --adduser, and --disable can now use the --writer-hg option.
  • The command --disable removes all users associated with the galera cluster hostgroups. Previously, this command only removed the users with the CLUSTER_APP_USERNAME.
  • The command --disable now accepts the --writer-hg option to disable the Galera cluster associated with that hostgroup overriding the value specified in the configuration file.
Removed Features
  • Asynchronous slave reader support has been removed: the --include-slaves option is not supported.
  • A list of nodes in the priority order is no longer supported. Only a single node is supported at this time.
  • Since the galera_proxysql_checker and galera_node_monitor scripts are no longer run in the scheduler, automatic cluster membership updates are not supported.
  • Checking the pxc_maint_mode variable is no longer supported
  • Using desynced nodes if no other nodes are available is no longer supported.
  • The server status is no longer maintained in the mysql_servers table.
Limitations
  • With --writers-as-readers=backup read-only nodes are not allowed.
    This a limitation of ProxySQL 2.0.  Note that backup is the default value of --writers-as-readers when --mode=singlewrite

ProxySQL is available under Open Source license GPLv3.

Percona Server for MySQL 8.0.15-6 Is Now Available

Percona announces the release of Percona Server for MySQL 8.0.15-6 on May 7, 2019 (downloads are available here and from the Percona Software Repositories).

This release is based on MySQL 8.0.14 and 8.0.15. It includes all bug fixes in these releases. Percona Server for MySQL 8.0.15-6 is now the current GA release in the 8.0 series. All of Percona’s software is open-source and free.

Percona Server for MySQL 8.0 includes all the features available in MySQL 8.0 Community Edition in addition to enterprise-grade features developed by Percona. For a list of highlighted features from both MySQL 8.0 and Percona Server for MySQL 8.0, please see the GA release announcement.

New Features:

  • The server part of MyRocks cross-engine consistent physical backups has been implemented by introducing rocksdb_disable_file_deletions and rocksdb_create_temporary_checkpoint session variables. These variables are intended to be used by backup tools. Prolonged use or other misuse can have serious side effects to the server instance.
  • RocksDB WAL file information can now be seen in the performance_schema.log_status table.

Bugs Fixed:

Note:

If you are upgrading from 5.7 to 8.0, please ensure that you read the upgrade guide and the document Changed in Percona Server for MySQL 8.0.

Find the release notes for Percona Server for MySQL 8.0.15-6 in our online documentation. Report bugs in the Jira bug tracker.

Shinguz: FromDual Ops Center for MariaDB and MySQL 0.9.1 has been released

FromDual has the pleasure to announce the release of the new version 0.9.1 of its popular FromDual Ops Center for MariaDB and MySQL focmm.

The FromDual Ops Center for MariaDB and MySQL (focmm) helps DBA's and System Administrators to manage MariaDB and MySQL database farms. Ops Center makes DBA and Admins life easier!

The main task of Ops Center is to support you in your daily MySQL and MariaDB operation tasks. More information about FromDual Ops Center you can find here.

Download

The new FromDual Ops Center for MariaDB and MySQL (focmm) can be downloaded from here. How to install and use focmm is documented in the Ops Center User Guide.

In the inconceivable case that you find a bug in the FromDual Ops Center for MariaDB and MySQL please report it to the FromDual bug tracker or just send us an email.

Any feedback, statements and testimonials are welcome as well! Please send them to feedback@fromdual.com.

Installation of Ops Center 0.9.1

A complete guide on how to install FromDual Ops Center you can find in the Ops Center User Guide.

Upgrade from 0.3 or 0.9.0 to 0.9.1

Upgrade from 0.3 or 0.9.0 to 0.9.1 should happen automatically. Please do a backup of your Ops Center Instance before you upgrade! Please also check Upgrading.

Changes in Ops Center 0.9.1 Upgrade
  • Sever upgrade bug fixed which prohibited installation of v0.9.0.
Build and Packaging
  • RPM package for RHEL/CentOS 7 is available now.
  • DEB package for Ubuntu 18.04 LTS is available now.
  • SElinux Policy Package file added.
  • COMMIT tag was not replaced correctly during build. This is fixed now.
Taxonomy upgrade extras:  Operations release Backup failover Restore

MySQL InnoDB Cluster : Recovery Process Monitoring with the MySQL Shell Reporting Framework

As explained in this previous post, it’s now (since 8.0.16) possible to use the MySQL Shell Reporting Framework to monitor MySQL InnoDB Cluster.

Additionally, when a member of the MySQL InnoDB Cluster’s Group leaves the group for any reason, or when a new node is added from a backup, this member needs to sync up with the other nodes of the cluster. This process is called the Distributed Recovery.

During the Distributed Recovery, the joiner receives from a donor all the missing transactions using asynchronous replication on a dedicated channel.

It’s of course also possible to monitor the progress of this recovery process by calculating how many transactions have still to be applied locally.

You can download the report file and uncompress it in ~/.mysqlsh/init.d/:

gr_recovery_progressDownload

This report must be run only when connected to the joiner:

We can see that as soon as we reach 0, the node finishes the Recovery Process and joins the Group.

Exposing MyRocks Internals Via System Variables: Part 3, Compaction

In this blog post, we continue our series of exploring MyRocks mechanics by looking at the configurable server variables and column family options. In our last post, I explained at a high level how data moves from immutable memtables to disk. In this post, we’re going to talk about what happens to that data as it moves through the compaction process.

What is Compaction?

One of the philosophies of MyRocks is “write the data quickly and sort out data organization later”, which is pretty far removed from engines like InnoDB that take the approach of “continuously organize data on disk so it’s optimal as soon as possible”. MyRocks implements its philosophy in a way that is heavily reliant on a process called ‘compaction’.

Let’s pick up where we left off with that first persistent flush from immutable memtable to data file and see how compaction comes into play. You may have noticed in a few of the variable topics we mentioned how data initially flushed from memory ends up in compaction layer 0. This is the topmost layer in a multi-layered approach to storing your data.

MyRocks’ aim is to have data files that are sorted where you don’t have record overlap across data files. The first file will have a range of records (example: 1 – 100), then the next file would have the next range (example: 101 – 250), and so on. This really isn’t possible when data gets flushed into L0 because in order for that to occur, the flushing threads would have to be aware of what’s going on in all the other immutable memtables that are in scope, which could be in the process of being flushed by other concurrent flushing threads. So in order to get around this, we don’t bother with checking for data overlap in L0, but that’s not going to work for us for very long.

Once level 0 hits a certain size threshold it will start merging data down into the next compaction level down. Level 0 (L0) will feed into Level 1 (L1) and this is where we start removing overlapping data and make sure that sorting is recognized across the compaction layer. The compaction process from L0 to L1 works a little like this:

  1. MyRocks recognizes that the threshold for the amount of data allowed in L0 has been reached and calls for a thread to start compaction from L0 to L1.
  2. The compaction thread wakes up and checks the data files in L0 and picks one to merge into L1. Let’s say, for example, that the file that it picks has keys ranging from 0 – 150.
  3. The thread then checks files that are in L1 that contain records 0 – 150. Let’s say in this example that there are two files that contain these records.

  4. The thread reads the L0 file to be compacted and the two L1 files that it needs to merge the data with and performs a merge sort, making sure that duplicate records are removed.
  5. The thread writes new file(s) in L1 comprising of the newly sorted and deduplicated data.

  6. Old files that were read in from L0 and L1 are removed.

As this process continues, more and more files will be created in L1, until eventually L1 it will hit its size threshold and a new thread will be called to compact from L1 to L2. The process of compacting from L1 to L2 is the same as L0 to L1 but with two key differences.

Firstly, when data is compacted from L0 to L1, as mentioned before, there can be overlapping data. When you compact from L1 to L2, this isn’t a problem as overlapping and duplicate data will not exist by the time it has reached L1.

Secondly, when data is compacted from L0 to L1, due to the nature of the overlapping data, compaction has to be single threaded. As I mentioned before, threads that flush and compact have to be aware of the contents of other threads, or else they cannot ensure a lack of data overlap or duplication. Once data resides in L1, there is no longer any concerns about data overlap, so when you compact from L1 to L2 you can distribute the load over multiple threads.

Eventually, as you compact files from L1 to L2, you’ll hit the threshold for the data size of L2 and it will start to compact to L3 and so on and so on. The process of compacting from L2 to L3 and all subsequent compactions work exactly like the compaction from L1 to L2. This will go all the way down to the bottom most compaction layer, which by default is L6. Given that each compaction layer is larger than the preceding layer (ten times by default), your data set will eventually look like this.

I should note one thing that is misleading about the diagram above is that it doesn’t reflect the default 1:10 scale between compaction layers, but it does show the default nature of L1 being the same size as L0.

If you’re interested in learning about how MyRocks’ leveled compaction implementation selects data files for compaction you can read more about it here on the RocksDB Wiki.

When you compare this to other storage engines that update data files as data is updated, this all seems quite strange, right? So why compact?

The whole point here is the get that data written as quickly as possible and in this case that’s achieved by doing direct writes to L0 and then not having to update data files directly, but instead used data changes in a log structured format and merge them together.

Not having to update files in place is also a huge advantage when it comes to using bloom filters. We’ll cover bloom filters in a future post in this series and I don’t want to get too far ahead of myself here, just keep that in your pocket for now and we’ll come back to it.

So the next question you might ask yourself is, “Why is each compaction layer larger than the one that preceded it?”. This really comes down to file access. If each compaction layer was the same size and not large enough, you would have a lot of contention for files being read by compaction processes. Instead, the compaction layers get larger, thus reducing the odds of file contention during compaction.

Finally, you might ask, “Well… what if my data size is larger than the threshold of my bottom most compaction layer? Do I stop writing data?”. The answer is no. The thresholds in this case are simply triggers that start the compaction. MyRocks will put a lot of emphasis on keeping compaction layers under their thresholds, and you can run into problems if you can’t flush from L0 to L1 fast enough. But when it comes to the bottom-most compaction layer, it can grow as large as it needs to in order to support your full data set.

So now that you know more about the whats and whys of compaction, let’s dig a little deeper and look at the variables that are associated with its mechanics.

Variables and CF_OPTIONS Rocksdb_max_background_jobs

The aim of MyRocks is to allow for concurrent compaction jobs so you can write quickly initially and then organize the data later. The variable rocksdb_max_background_jobs was mentioned in my previous blog post about persisting data from memory to disk. The variable is also being mentioned in this blog post as compaction jobs are also considered to be background operations. As such, this variable will limit the max number of compactions jobs that can be run at one time.

Background jobs created for compaction and flushing are global in the sense that you cannot reserve jobs for specific column families. With MyRocks, you don’t have an option to spread your data files over multiple directories or storage platforms, but you can in RocksDB. I’m pointing this out because if you were to put different column families on different storage platforms, you may be bound to the slowest storage platform during compaction as documented in this bug report created for the RocksDB project. I don’t know if we’ll get an option to distribute our column families over different directories in MyRocks, but I feel this serves as a good example of considerations you need to make when dealing with global variables that have an impact across all column families.

Default: 2

Using the default value of two ensures that there will be enough available background processes for at least one flush and one compaction.

Rocksdb_max_subcompactions

You can use multiple threads to help support a single compaction process. These threads are called subcompactions. You can even use subcompactions to help with compaction from L0 to L1, which, as I noted above, is a single job process. The rocksdb_max_subcompactions variable allows you to designate the maximum number of subcompaction threads that are allowed for each compaction job.

Default: 1

Rocksdb_compaction_readahead_size

Compaction has the ability to prefetch data from files in order to ensure greater amounts of sequential reads. This variable allows you to specify the amount of data that should be pre-fetched from the data files that are participating in the compaction input process.

Default: 0

If you are using an SSD, you may not see a performance increase by attempting to establish more sequential reads as part of the compaction process. However, if you are using spinning media it’s recommended to set this value to 2Mb.

Setting this variable to a non-zero value will automatically set variable rocksdb_new_table_reader_for_compaction_inputs to ON if it’s not already in that state. Be sure to check the information about rocksdb_new_table_reader_for_compaction_inputs below so you know the impact of enabling that variable before setting rocksdb_compaction_readahead_size to a non-zero value.

Rocksdb_new_table_reader_for_compaction_inputs

Compaction jobs are going to need to read from multiple files during the input process. For example, if you are doing a compaction from L2 to L3 you first need to access the L2 file that’s going to be compacted, and then all the L3 files that overlap with the key range of the L2 file.

The variable rocksdb_new_table_reader_for_compaction_inputs allows you to work with one file descriptor per file that needs to be accessed. For those of you who are not familiar, a file descriptor is a computational resource used to access / read from a file. Using one file descriptor for multiple files means that you need to have that descriptor rotate through the files to get the info that it needs. Using multiple descriptors means you can access multiple files simultaneously, thus parallelizing the process.

Default: OFF

I would be inclined to test enabling this variable, but you have to be mindful of the fact that multiple file descriptors means more memory usage. I would test this in a non-production environment in order to determine the estimated memory footprint increase prior to implementing in production.

CF_OPTIONS target_file_size_base & target_file_size_multiplier

Once the compaction input process is complete, the compaction output process will create a series of new files in the N+1 compaction layer where N is the layer where compaction was initiated. The size of those files is determined by several factors.

The size of files in the L0 compaction layer is going to be the same size as the write_buffer_size variable (the size of the memtable) multiplied by the column family option min_write_buffer_number_to_merge (this is the number of immutable memtables that must be created prior to a flush).

The size of files in the L1 compaction layer is defined by the column family option target_file_size_base.

The size of the files in the L2 compaction layer or greater is defined by the column family option target_file_size_multiplier. Each layer’s file size will be X times greater than the one that came before it, where X is defined by the value of column family option target_file_size_multiplier. For example, if the L1 compaction layer files are set to be 64Mb and the CF option target_file_size_multiplier is set to 2, files in the L2 compaction layer would be 128Mb, files in the L3 compaction layer would be 256Mb, and so on.

Default:

  • CF option target_file_size_base: 67108864 (64Mb)
  • CF option target_file_size_multiplier: 1

Assuming you are using the default value for write_buffer_size (64Mb) as well, this means that all data files across all compaction layers will be 64Mb.

I think that 64Mb is a good place to start for L0 and L1 files considering that smaller files should result in faster compaction jobs, which could be important considering the single job nature of compaction from L0 to L1.

As you move to greater compaction levels, you may want to consider the tradeoffs of increasing file sizes. In the case that you are doing a read that is working with a specific key value, you’ll have a lot fewer files to inspect if the key is present (based on min and max values), but in the case that you need to scan a file, you may be checking a lot more data than needed. Also, if you’re using larger files, it’s possible that compaction could take longer as it has a lot more data to merge and sort.

Rocksdb_rate_limiter_bytes_per_sec & Rocksdb_use_direct_io_for_flush_and_compaction

Rocksdb_rate_limiter_bytes_per_sec and rocksdb_use_direct_io_for_flush_and_compaction were both mentioned in my post about persisting data in memory to disk, and I would encourage you to go back and read to about them in that post if you haven’t done so already. I figured they deserved a mention in this post as well considering that they also apply to the compaction output process.

Rocksdb_delete_obsolete_files_period_micros

Once compaction has finished and it’s created the new data files on disk, it can delete the files that it used for compaction input as they have duplicate copies of the data that are contained within the files created by the compaction output. Remember, the whole point of compaction is to keep sequential data with as few copies of the data as possible so to reduce the number of files that need to be accessed during read operations.

The problem is that a file that is pending deletion may be in a state where it’s being accessed by another thread at the time that compaction is done with it, and as such it cannot delete the file until it’s no longer being used by other processes, but it must eventually be deleted.

The variable rocksdb_delete_obsolete_files_period_micros specifies the time period to delete obsolete files assuming they weren’t already deleted as part of compaction process.

Default: 21600000000 (6 hours)

I would be inclined to leave this variable in its default state unless I had an environment with heavy writes, long running range reads, and a large data set. With this being the case you may have a number of files that can’t be deleted during compaction and I don’t know if I would want to wait 6 hours to be rid of them.

In contrast, if this was a system with known critical long running queries or transactions, I might be inclined to increase this value.

CF_OPTION level0_file_num_compaction_trigger

So now that we know how files are read and written by compaction, it’s good to understand when compaction occurs. This will occur at different times for each compaction layer and is dependent upon the amount of data stored in each layer.

Compaction starts at L0 and works it was down to the bottommost compaction layer. The compaction trigger threshold for L0 to L1 is defined by the column family option level0_file_num_compaction_trigger. When the number of files in L0 reaches this number, MyRocks will call a job to start comaction from L0 to L1. You can determine how large the L0 compaction layer will be, in bytes, by multiplying the values of variable write_buffer_size, column family option min_write_buffer_number_to_merge, and column family option level0_file_num_compaction_trigger.

Default: 4

I would recommend leaving this variable at its default value. As noted above, compaction from L0 to L1 is a single job, and compaction from L1 to L2 cannot occur while this is ongoing. The larger your L0 compaction layer is, the longer compaction from L0 to L1 will take and the more likely you’ll see interruptions for jobs trying to run compaction from L1 to L2.

CF_OPTION max_bytes_for_level_base

The compaction trigger threshold for L1 to L2 is defined by the column family option max_bytes_for_level_base. When L1 reaches the number of bytes defined by this option, it will trigger a compaction job for L1 to L2, assuming that there is not on ongoing compaction job from L0 to L1.

Default: 268435456 (256Mb)

The recommendation is to keep L1 the same size as L0. I noted how to calculate the size of L0 in the CF_OPTION level0_file_num_compaction_trigger section. If you change the size of L0, be sure to change this column family option to match.

CF_OPTION max_bytes_for_level_multiplier

The compaction trigger threshold for L2 and greater is defined by the column family option max_bytes_for_level_multiplier. L2 will be X times greater than L1 where X is the value of this variable, L3 will be X times greater than L2, and so on.

Default: 10

CF OPTION num_levels

MyRocks will continue to write files lower and lower into compaction layers until it hits the maximum compaction layer, which is defined by this column family option. Keep in mind that compaction layers start at L0, so if this value is 7 then the maximum compaction layer is L6.

Default: 7

There is some fairly complex math that you can use to determine how many compaction levels you need to minimize write amplification, but I think it goes outside of the scope of this blog series. If you want to know more about this I would recommend this blog post by Mark Callaghan which provided the details and was then followed up by this blog post which offered a few corrections.

CF OPTION level_compaction_dynamic_level_bytes

In the sections above I’ve described how MyRocks determines the size of each compaction layer. By default the rules will be to use the max_bytes_for_level_base to establish the size of the L1 compaction layer and then make each subsequent compaction layer X times larger than the previous where X is the value of the variable max_bytes_for_level_multiplier. However, there is another way that the size of each compaction layer can be determined and that’s when we shift from the standard definition to the dynamic definition.

In the dynamic definition of compaction level sizing, the size of each compaction level is determined by the amount of data in the bottom most compaction layer, as opposed to using the top most compaction layer like we would if we were using the standard definition.

The dynamic framework is first defined by using the max_bytes_for_level_base and max_bytes_for_level_multiplier variables, just like standard mode. For example, if we have max_bytes_for_level_base set to 268435456 (256Mb), max_bytes_for_level_multiplier set to 10, and num_levels set to 6, the following would be the maximum target compaction thresholds would be defined as follows.

L0: 256Mb (assuming you use default values noted in the level0_file_num_compaction_trigger section above)

L1: 256Mb

L2: 2.5 Gig

L3: 25 Gig

L4: 250 Gig

L5: 2.44 Tb

L6: 24.41 Tb

This defines the maximum compaction threshold. However the actual compaction threshold is changing dynamically based on the value of the total size of data in the bottom most compaction layer. Each compaction layer above it would be X/10 times the value of the compaction layer beneath it. To illustrate this, let’s use another example. Let’s use the exact tame variables, but assume there is 256 Gb of data in the L6 compaction layer. Each compaction layer threshold would be as follows.

L0: 256Mb (assuming you use default values noted in the level0_file_num_compaction_trigger section above)

L1: 2 Mb

L2: 26 Mb

L3: 262 Mb

L4: 2.56Gb

L5: 25.6 Gb

L6: 256 Gb

Assuming that the variables for data file sizes are default, the data file size would be 64Mb when it starts at L0. Given that L1 and L2 do not have a threshold size large enough to accommodate 64Mb, compaction will skip L1 and L2 and compact to L3 directly.

The major difference here is that under standard compaction, the data starts at L0, then fills L1, then L2, then L3, and so on until compaction starts to fill L6. With the dynamic method, the opposite is true. Data starts in L0 (which it always does) and then will start to fill L6, then L5 once its threshold exceeds the data file size, then L4 once its threshold exceeds the data file size, and so on.

You can think of the standard method like a top-down data fill method where dynamic fills that data a lot like the bottom half of an hourglass – the bottom has to fill to some extent before sand can rest on top of it. Somewhat like the diagram below:

Default: Off

I’m going to be covering compression and bloom filters in a later post and I think it would be best to review this section again after reading that post. The reason is that you can configure your column family to take on different characteristics for bloom filters and compression at different levels of compaction.

The main point here is that if you are customizing the characteristics of each compaction layer, then you’re going to need to take that into account if you enable this variable. However, if you choose not to implement compression or bloom filtering (not recommended), you may want to consider enabling this variable as it offers greater assurance of a stable LSM-tree structure.

Rocksdb_compaction_sequential_deletes_file_size

Compaction layer size thresholds aren’t the only thing that can trigger compaction. There are two other triggers and the first of the two is an examination of how much sequential data in a data file is marked for deletion. If you have deleted a large batch of rows, you can save yourself a bit of overhead in the read process by removing data files that contain the records to be deleted as well as the data file that contains the delete markers.

This process occurs in multiple steps, the first of which is designated by the variable Rocksdb_compaction_sequential_deletes_file_size. Any file larger than the value of this variable will be checked for delete markers.

Default: 0 (bytes)

The default value disables this feature. Also, per the documentation, you can set this variable to -1 which, per the source code, also disables the feature.

With all the rest of the file size variables and column family options set to default, every data file in the data set should be approximately 64Mb. If you change the nature of file sizing, you may want to adjust this accordingly so only some files get checked, depending on your use case.

The only reason why I would consider disabling this feature is to reduce write amplification. The more compaction jobs that are triggered, the more write I/O will be experienced.

Rocksdb_compaction_sequential_deletes_window

The next part of the process is to read the data file in search of sequential deletes. There has to be a certain number of sequential deletes and those sequential deletes need to be within a specific range (which is called a window in this case). That range of records is defined by the variable rocksdb_compaction_sequential_deletes_window.

Default: 0 (disabled)

Rocksdb_compaction_sequential_deletes

Within the window, the number of sequential delete markers that need to be present in order to trigger a compaction job is defined by the variable Rocksdb_compaction_sequential_deletes.

Default: 0 (disabled)

Rocksdb_compaction_sequential_deletes_count_sd

There are two kinds of delete markers that can exist in the MyRocks data file. A delete(), which is the standard marker to delete the record, and a SingleDelete() which deletes the most recent entry for a record so long as the single delete marker and the record itself line up during compaction. The means that old versions of the record may still be available during read processes after the single delete operation is performed.

The variable rocksdb_compaction_sequential_deletes_count_sd determines if single delete markers should be counted when scanning for sequential delete markers in data files.

Default: OFF

Rocksdb_compact_cf

The last mechanism that can be used to trigger compaction is manual compaction. Using variables in MyRocks, you can manually initiate compaction processes. The first variable being noted is rocksdb_compact_cf. Using this variable you can pass in the name of a column family and MyRocks will do a full compaction of the dataset from L0 all the way to the bottommost compaction layer. During my tests, I have found that this does not block incoming reads or writes much in the same way that automated compactions process also are non-blocking.

Default: Blank

When you pass in the name of a column family, the variable will automatically clear again. Considering that nature of how you interact with this variable, it’s not really a variable at all, but just a way to manually trigger the compaction process.

Rocksdb_force_flush_memtable_and_lzero_now

The other option for performing manual compaction is to call the variable rocksdb_force_flush_memtable_and_lzero_now. A very similar variable, rocksdb_force_flush_memtable_now, was mentioned in my earlier post about persisting in-memory data to disk as a way to flush memtables to L0. This variable work similarly, but adds a step of compacting L0 files to L1 for all column families.

Default: OFF

Much like rocksdb_compact_cf, the value of this variable will return to OFF once it has completed the requested flush and compaction, so it really doesn’t work like a variable but just a way to manually trigger a flush and L0 compaction.

Compaction Stats

There is a group of metrics available specific to compaction that’s available in the information_schema table ROCKSDB_COMPACTION_STATS. This will provide a set of compaction stats for each column family that you have in your data set. Instead of listing them all here, as there are many, I would recommend checking this page in the RocksDB wiki in order to get a better understanding of what is available to you.

Associated Metrics

Here are some of the metrics you should be paying attention to when it comes to compaction:

You can find the following information using system status variables.

  • Rocksdb_compact_read_bytes: The number of bytes that have been read by a compaction input process since the last MySQL restart.
  • Rocksdb_compact_write_bytes: The number of bytes that have been written by a compaction process since the last MySQL restart.
  • Rocksdb_compaction_key_drop_new: The number of times a record was removed by compaction due to a newer version of the record being available since the last MySQL restart.
  • Rocksdb_compaction_key_drop_obsolete: The number of times a record was removed by compaction due to the record no longer existing since the last MySQL restart.
  • Rocksdb_stall_l0_file_count_limit_slowdowns: The number of times that write slowdowns occurred due to compaction layer L0 being close to full since the last MySQL restart.
  • Rocksdb_stall_l0_file_count_limit_stops: The number of times that write stalls occurred due to compaction layer L0 being full since the last MySQL restart.
  • Rocksdb_stall_locked_l0_file_count_limit_slowdowns: The number of times that write slowdowns occurred due to compaction layer L0 being close to full at a time when compaction from L0 was already in progress since the last MySQL restart.
  • Rocksdb_stall_locked_l0_file_count_limit_stops: The number of times that write stalls occurred due to compaction layer L0 being full at a time when compaction from L0 was already in progress since the last MySQL restart.

In the information_schema.ROCKSDB_CFSTATS table you can find the following information about each column family.

  • COMPACTION_PENDING: Shows the current number of pending compaction requests

In the performance_schema, you may find the following setup instrument to be helpful.

  • wait/synch/mutex/rocksdb/signal manual compaction: Shows the amount of mutex time wait during the manual initiation of compaction.

In addition to the metrics noted above, I would recommend you take time to review the following tables as they all contain a wealth of compaction related metrics as well as how to link your tables and column families to data files on disk.

  • information_schema.ROCKSDB_COMPACTION_STATS
  • information_schema.ROCKSDB_DDL
  • information_schema.ROCKSDB_INDEX_FILE_MAP
Conclusion

In this post we covered the details regarding compaction, what it is, and why it’s needed. Compaction is a critical part of the MyRocks solution so I would recommend familiarizing yourself with this system as best you can before considering the implementation of MyRocks as part of your data platform.

Stay tuned for my next post where I’m going to cover compression and bloom filters!

MariaDB Track at Percona Live

Less than one month left until Percona Live. This time the Committee work was a bit unusual. Instead of having one big committee for the whole conference we had a few mini-committees, each responsible for a track. Each independent mini-committee, in turn, had a leader who was responsible for the whole process. I led the MariaDB track. In this post, I want to explain how we worked, which topics we have chosen, and why.

For MariaDB, we had seven slots: five for 50-minutes talks, two for 25-minutes talks and 19 submissions. We had to reject two out of three proposals. We also had to decide how many topics the program should cover. My aim here was to use the MariaDB track to demonstrate as many MariaDB unique features as possible. I also wanted to have as many speakers as possible, considering the number of slots we had available.

The committee agreed, and we tried our best for the program to cover the various topics. If someone sent us two or more proposals, we choose only one to allow more speakers to attend.

We also looked to identify gaps in submitted sessions. For example, if we wanted for a topic to be covered and no one sent a proposal with such a subject, we invited potential speakers and asked them to submit with that topic in mind. Or we asked those who already submitted similar talks to improve them.

In the end, we have five 50-minutes sessions, one MariaDB session in the MySQL track, two 25-minutes sessions, one tutorial, and one keynote. All of them are by different speakers.

The Program

The first MariaDB event will be a tutorial: “Expert MariaDB: Harness the Strengths of MariaDB Server” by Colin Charles on Tuesday, May 28

Colin started his MySQL career as a Community Engineer back in the MySQL AB times. He worked on numerous MySQL events, both big and small, including Percona Live’s predecessor, O’Reilly’s MySQL Conference and Expo. Colin joined Monty Program Ab, and MariaDB Corporation as a Chief Evangelist, then spent two years as Chief Evangelist at Percona. Now he is an independent consultant at his own company GrokOpen.

Colin will not only talk about unique MariaDB features up to version 10.4, but will also help you try all of them out. This tutorial is a must-attend for everyone interested in MariaDB.

Next day: Wednesday, May 29 – the first conference day – will be the MariaDB Track day.

MariaDB talks will start from the keynote by Vicentiu Ciorbaru about new MariaDB features in version 10.4. He will highlight all the significant additions in this version.

Vicentiu started his career at MariaDB Foundation as a very talented Google Summer of Code student. His first project was Roles. Then he worked a lot on MariaDB Optimizer, bug fixes, and code maintenance. At the same time, he discovered a talent for public speaking, and now he is the face of MariaDB Foundation.

We at the committee had a hard choice: either to accept his 50-minutes session proposal or ask him to make a keynote. This decision was not easy, because a keynote is shorter than 50 minutes. At the same time, though, everyone at the conference will be able to see it. Brand new features of version 10.4 are a very important topic. Therefore, we decided that it would be best to have Vicentiu as a keynote speaker.

Morning sessions

Sessions will start with a talk by Alexander Rubin “Opensource Column Store Databases: MariaDB ColumnStore vs. ClickHouse” Alex began his MySQL career as a web developer, then joined MySQL AB as a consultant. He then moved to Percona as Principal Architect. It was our loss when he left Percona to start applying his recommendations himself on behalf of a medical startup VirtualHealth! During his career as a MySQL consultant, he tried all the sexiest database products, loaded terabytes of data into them, ran the deadly intensive loads. He is the one who knows best about database strengths and weaknesses. I would recommend his session to everyone who is considering a column store solution.

Next talk is “Galera Cluster New Features” by Seppo Jaakola. This session is about the long-awaited Galera 4 library. Seppo is one of three founders of Codership Oy: the company which brought us Galera library. Before the year 2007, when the Galera library was first released, MySQL users had to choose between asynchronous replication and asynchronous replication (that’s not a typo). Seppo brought us a solution which allowed us to continue using InnoDB in the style we were used to using while writing to all nodes. The Galera library looks after the data consistency. After more than ten years the product is mature and leaving its competitors far behind. The new version brings us streaming replication technology and other improvements which relax usage limitations and make Galera Cluster more stable. I recommend this session for everyone who looks forward to a synchronous replication future.

Afternoon sessions

After the lunch break, we will meet MariaDB users Sandeep Jangra and Andre Van Looveren who will show how they use MariaDB at Walmart in their talk “Lessons Learned Building a Fully Automated Database Platform as a Service Using Open Source Technologies in the Cloud”. Sandeep and Andre manage more than 6000 MariaDB installations. In addition to setting up automation, they have experience with migration and upgrade. This talk will be an excellent case study, which I recommend to attend everyone who is considering implementing automation for a farm of MariaDB or MySQL servers.

Next topic is “MariaDB Security Features and Best Practices” by Robert Bindar.  Robert is a server Developer at MariaDB Foundation. He will cover best security practices for MariaDB deployment, including the latest security features, added to version 10.4

At 4:15 pm we will have two MariaDB topics in parallel

“MariaDB and MySQL – What Statistics Optimizer Needs Or When and How Not to Use Indexes” by Sergei Golubchik – a Member of the MariaDB Foundation Board – discovers optimization techniques which are often ignored in favor of indexes. Sergei worked on MySQL, and then on MariaDB, from their very first days. I’ve known him since 2006 when I joined the MySQL team. Each time when I am in trouble to find out how a particular piece of code works, just a couple of words from Sergei help to solve the issue! He has an encyclopedic knowledge on both MariaDB and MySQL databases. In this session, Sergei will explain which statistics optimizer we can use in addition to indexes. While he will focus on specific MariaDB features he will cover MySQL too. Spoiler: these are not only histograms!

Backups in the MySQL track…

In the parallel MySQL track, Iwo Panowicz and Juan Pablo Arruti will speak about backups in their “Percona XtraBackup vs. Mariabackup vs. MySQL Enterprise Backup” Iwo and Juan Pablo are Support Engineers at Percona. Iwo joined Percona two years ago, and now he is one of the most senior engineers in the EMEA team. Linux, PMM, analyzing core files, engineering best practices: Iwo is well equipped to answer all these and many more questions. Juan Pablo works in the American Support team for everything around MariaDB and MySQL: replication, backup, performance issues, data corruption… Through their support work, Iwo and Juan Pablo have had plenty of chances to find out strengths and weaknesses of different backup solutions.

Three tools, which they will cover in the talk, can be used to make a physical backup of MySQL and MariaDB databases, and this is the fastest and best recommended way to work with an actively used server. But what is the difference? When and why should you prefer one instrument over another? Iwo and Juan Pablo will answer these questions.

At the end of the day we will have two 25-minute sessions

Jim Tommaney will present “Tips and Tricks with MariaDB ColumnStore”. Unlike Alex Rubin, who is an end user of ColumnStore databases, Jim is from another side: development. Thus his insights into MariaDB ColumnStore could be fascinating. If you are considering ColumnStore: this topic is a must-go!

Daniel Black will close the day with his talk “Squash That Old Bug”. This topic is the one I personally am looking forward to the most! Not only because I stick with bugs. But, well… the lists of accepted patches which Daniel’s posts to MariaDB and to MySQL servers are impressive. Especially when you know how strict is the quality control for external patches in MariaDB and MySQL! In his talk, Daniel is going to help you to start contributing yourself. And to do it successfully, so your patches are accepted. This session is very important for anyone who has asked themselves why one or another MariaDB or MySQL bug has not been fixed for a long time. I do not know a single user who has not asked that question!

Conclusion

This blog about MariaDB track at Percona Live covers eight sessions, one keynote, one tutorial, 12 speakers, seven mini-committee members – two of whom are also speakers. We worked hard, and continue to work hard, to bring you great MariaDB program.

I cannot wait for the show to begin!


Photo by shannon VanDenHeuvel on Unsplash

Connector/Python Connection Attributes

MySQL Server has since version 5.6 supported connection attributes for the clients. This has allowed a client to provide information such as which program or connector the client is, the client version, the license, etc. The database administrator can use this information for example to verify whether all clients have been upgraded, which client is executing a particular query, and so forth.

In MySQL 8.0.16 this feature has been included for the X DevAPI in the MySQL connectors as well, including MySQL Connector/Python which I will cover in this blog. First though, let’s take a look at how the attributes are exposed in MySQL Server.

The built-in MySQL Connector/Python connection attributesConnection Attributes in MySQL Server

The connection attributes are made available in MySQL Server through two tables within the Performance Schema: session_account_connect_attrs and session_connect_attrs. The two tables have the same definition – the only difference is for which connections they show the connection attributes.

The session_account_connect_attrs table includes the attributes for connections using the same user account as for the one querying the table. This is useful if you want to grant permission for a user to check the attributes for their own connections but not for other connections.

On the other hand, session_connect_attrs shows the attributes for all connections. This is useful for the administrator to check the attributes for all users.

Information

It is up to the client what attributes they want to expose and the values they provide. In that sense, you can only trust the attributes to the same extend that you trust the clients to submit correct values.

The tables have four columns:

  • PROCESSLIST_ID: This is the same ID as in SHOW PROCESSLIST or the PROCESSLIST_ID column in performance_schema.threads.
  • ATTR_NAME: This is the name of the attribute, for example _client_name.
  • ATTR_VALUE: This is the value for the attribute, for example mysql-connector-python.
  • ORDINAL_POSITION: The attributes have an order. The ordinal position specifies the position of the attribute. The first attribute for the connection has ordinal position 0, the next 1, and so forth.

The PROCESSLIST_ID and ATTR_NAME together form the primary key of the tables.

Now that you know how to query the table, let’s take a look at how it works with MySQL Connector/Python.

Connector/Python Attributes

There are essentially three different ways to use the connection attributes from MySQL Connector/Python. You can have them disabled. This is the default and means that no attributes will be provided. You can enable them and use the default attributes. And finally, there is support for providing custom attributes. Let’s look at each of the two cases where attributes are enabled.

Book

If you want to learn more about MySQL Connector/Python, then I have written MySQL Connector/Python Revealed published by Apress. The book both covers the traditional Python Database API (PEP 249) and the X DevAPI which is new as of MySQL 8.

The book is available from Apress (print and DRM free ePub+PDF), Amazon (print and Kindle), Barnes & Noble (print), and others.

Enabled with Default Attributes

If you want your Python program to connect to MySQL using the default attributes, all you need to do is to set the connection-attributes option to True. You can do that in one of two ways depending on how you set the connection arguments

If you use a dictionary of arguments, you add connection-attributes as a key with the value set to True:

import mysqlx connect_args = { "host": "127.0.0.1", "port": 33060, "user": "pyuser", "password": "Py@pp4Demo", "connection-attributes": True, }; db = mysqlx.get_session(**connect_args) p_s = db.get_schema("performance_schema") attr = p_s.get_table("session_account_connect_attrs") stmt = attr.select() stmt.where("PROCESSLIST_ID = CONNECTION_ID()") stmt.order_by("ORDINAL_POSITION") result = stmt.execute() print("+------+-----------------+------------------------+---------+") print("| P_ID | ATTR_NAME | ATTR_VALUE | ORDINAL |") print("+------+-----------------+------------------------+---------+") fmt = "| {0:4d} | {1:<15s} | {2:<22s} | {3:7d} |" for row in result.fetch_all(): print(fmt.format(row[0], row[1], row[2], row[3])) print("+------+-----------------+------------------------+---------+") db.close()

The program creates the connection, then queries the performance_schema.session_account_connect_attrs table using the crud methods. Finally, the result is printed (note that the PROCESSLIST_ID and ORDINAL_POSITION columns have had their names shortened in the output to make the output less wide – the process list ID will differ in your output):

+------+-----------------+------------------------+---------+ | P_ID | ATTR_NAME | ATTR_VALUE | ORDINAL | +------+-----------------+------------------------+---------+ | 45 | _pid | 19944 | 0 | | 45 | _platform | x86_64 | 1 | | 45 | _os | Windows-10.0.17134 | 2 | | 45 | _source_host | MY-LAPTOP | 3 | | 45 | _client_name | mysql-connector-python | 4 | | 45 | _client_version | 8.0.16 | 5 | | 45 | _client_license | GPL-2.0 | 6 | +------+-----------------+------------------------+---------+

Notice that all of the attribute names start with an underscore. That means it is a built-in attribute. Attribute names starting with an underscore are reserved and can only be set by MySQL itself.

You can also connect using a URI, in that case the connection is made like the following example:

import mysqlx import urllib uri = "mysqlx://{0}:{1}@{2}:{3}/?connection-attributes=True".format( "pyuser", urllib.parse.quote('Py@pp4Demo', safe=''), "127.0.0.1", "33060" ) db = mysqlx.get_session(uri)

The username, password, host, and port are added through the format() function to make the code less wide. The important thing here is the connection-attributes=True. You can also leave out =True as mentioning the connection-attributes option without any value is the same as enabling it.

What do you do, if you want to add your own customer attributes? That is supported as well.

Custom Attributes

This far the connection-attributes option has just been set to True. However, it also supports taking a list or dictionary as the argument. That can be used to set your own custom attributes.

The easiest way to understand this is to see an example:

import mysqlx attributes = { "application": "my_test_app", "foo": "bar", "foobar": "", } connect_args = { "host": "127.0.0.1", "port": 33060, "user": "pyuser", "password": "Py@pp4Demo", "connection-attributes": attributes, }; db = mysqlx.get_session(**connect_args) p_s = db.get_schema("performance_schema") attr = p_s.get_table("session_account_connect_attrs") stmt = attr.select() stmt.where("PROCESSLIST_ID = CONNECTION_ID()") stmt.order_by("ORDINAL_POSITION") result = stmt.execute() print("+------+-----------------+------------------------+---------+") print("| P_ID | ATTR_NAME | ATTR_VALUE | ORDINAL |") print("+------+-----------------+------------------------+---------+") fmt = "| {0:4d} | {1:<15s} | {2:<22s} | {3:7d} |" for row in result.fetch_all(): value = row[2] if row[2] is not None else "" print(fmt.format(row[0], row[1], value, row[3])) print("+------+-----------------+------------------------+---------+") db.close()

Notice in line 29 that it is checked whether the attribute value is None (NULL in SQL). When the attribute value is an empty string or no value like for the foobar attribute, it is returned as None in the result set.

Alternatively, you can specify the same three connection attributes using a list:

attributes = [ "application=my_test_app", "foo=bar", "foobar", ] connect_args = { "host": "127.0.0.1", "port": 33060, "user": "pyuser", "password": "Py@pp4Demo", "connection-attributes": attributes, };

You can also use an URI of course. You need to use the list syntax for that:

uri = "mysqlx://{0}:{1}@{2}:{3}/" \ + "?connection-attributes=[application=my_test_app,foo=bar,foobar]".format( "pyuser", urllib.parse.quote('Py@pp4Demo', safe=''), "127.0.0.1", "33060", )

These all give the same result (the process list ID will differ):

+------+-----------------+------------------------+---------+ | P_ID | ATTR_NAME | ATTR_VALUE | ORDINAL | +------+-----------------+------------------------+---------+ | 74 | _pid | 20704 | 0 | | 74 | _platform | x86_64 | 1 | | 74 | _os | Windows-10.0.17134 | 2 | | 74 | _source_host | MY-LAPTOP | 3 | | 74 | _client_name | mysql-connector-python | 4 | | 74 | _client_version | 8.0.16 | 5 | | 74 | _client_license | GPL-2.0 | 6 | | 74 | application | my_test_app | 7 | | 74 | foo | bar | 8 | | 74 | foobar | | 9 | +------+-----------------+------------------------+---------+

Notice that the built-in attributes are still included and the custom attributes have been added at the end.

That concludes this introduction to connection attributes with the MySQL Connector/Python X DevAPI. I will recommend to enable them by default even if just for the built-in attributes. The attributes can be very handy when debugging issues on the server.

Constant-Folding Optimization in MySQL 8.0

In MySQL 8.0.16 the optimizer has improved again! Comparisons of columns of numeric types with constant values are checked and folded or removed for invalid or out-of-rage values. The goal is to speed up query execution.

Percona Live Presents: The First Ever TiDB Track

The PingCAP team has always been a strong supporter of Percona and the wider open source database community. As the people who work day in and day out on TiDB, an open source NewSQL database with MySQL compatibility, open source database is what gets us in the morning, and there’s no better place to share that passion than Percona Live.

At this year’s Percona Live in Austin, Texas, we are particularly excited to bring you a full track of talks and demo on the latest development in TiDB during Day 1 of the conference.

Who would benefit from the TiDB track

The TiDB track is designed to share with developers, DBAs, and practitioners in general technical know-hows, reproducible benchmarks (no benchmark-eting), and best practices on how TiDB can solve their problems. There are 7 talks total by folks from PingCAP and Intel that cover the full gamut of how you can test, migrate, and use TiDB in the cloud to solve technical problems and deliver business value. Here’s a run down of the talk topics:

Phew! That’s a lot. I hope you are excited to join us for this track. As Peter Zaitsev and Morgan Tocker (one of the TiDB track speakers) noted in a recent Percona webinar, there’s a lot TiDB can do to help scale MySQL while avoiding common manual sharding issues. This track will peel the onion to show you all the fun stuff under the hood.

Whose presentations do you look forward to?

Besides the TiDB track, there are many other presentations we are excited about. In particular, I look forward to attending Stacy Yuan and Yashada Jadhav of PayPal’s talk on MySQL Security and Standardization, and Vinicius Grippa of Percona’s presentation on enhancing MySQL Security.

See you soon in Austin!

The post Percona Live Presents: The First Ever TiDB Track appeared first on Percona Community Blog.

MySQL Connectors 8.0.16 – Time to Catch up!

HI again! It’s been some time since I wrote and shared some of our updates with you. My intention back then was to give you an update with every release — if only all my plans worked out! However we’ve now had 3 releases since last I shared an update with you so it’s time that I updated everyone on what we’ve been working on.

All of our connectors (well except for ODBC) implement our new X DevAPI and so I’ll briefly go over these features and then break out some of the major connector specific features included.

X DevAPI

Handling of default schema

We had some inconsistencies regarding how a default schema given on a connection string was handled by some of our connectors. If a user gives a schema on the connection string — such as this – it was expected that myschema would be the default schema set on the session. This would mean that any SQL issued against that session would work against that schema. Not all connectors were handling this the correct way.

var session = mysqlx.getSession("mysqlx://localhost/myschema") Various API standardization efforts

With many of our releases we’ll included various API standardizing efforts. We are always comparing our connectors and trying to make sure that the “core” API is essentially the same among all of them. When we see differences we schedule updates to correct those. Our goal is that if you understand the X DevAPI in one language then you can understand it in all the languages.

Prepared Statement support

With 8.0.16 we released prepared statement support when working with our X DevAPI/Document Store connectors. The first thing you may notice is that there isn’t any specific API for accessing prepared statements. This fits in with our general philosophy of letting the connectors “do the right thing” for the user rather than force the user to always write code. In this case the connector will notice that you are executing a “preparable” query more than once and will automatically make use of prepared statements for you. As of right now there is no way to configure or adjust this behavior. You should see a significant performance improvement from 8.0.15 to 8.0.16 when doing something like a loop of inserts or updates.

Connection Pooling Enhancements

An enhancement was included in 8.0.16 to improve our connection pooling by speeding how our connectors could “reset” the internal connection to the server. This is entirely an internal improvement. Developers will see this as a faster connection pool.

Connection Attributes

Connection attributes are a feature where you can pass in key value pairs when a connection is made. These values will be stored in the server performance schema and can be queried later as a way of “profiling” your application. Think of it simply as “tagging” your connections. These values can be given when the pool is created or when old style sessions are opened. Here is an example code snippet giving some connection attributes when opening an old-style session:

var session = mysqlx.getSession("mysqlx://localhost/schema ?connection-attributes=[key1=value1,key2=value2]") Connector specific Features

Now I’d like to make a quick list of some of the larger changes we made to our connectors that are *not* connected to the X DevAPI. These are not all of the changes in our connectors. Each connector puts out a release announcement giving the entire change list with each release.

  • Connector/CPP
    • Support for MSVC 2017
    • Improved internal string handling
    • Improved our CMaked-based build system
  • Connector/Python
    • Removed support for Django versions less then 1.11
  • Connector/Net
    • Can now use SSL certificates in the PEM file format

As always, we really appreciate your willingness to work with our products and look forward to your feedback.

Pages