Planet MySQL

This Week in Data with Colin Charles 22: CPU vulnerabilities and looking forward to 2018

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

Happy New Year. Here’s to 2018 being a great year in the open source database world. What is in store for us? Probably: MySQL 8.0 and MariaDB Server 10.3 as generally available. What will we see in the rest of the space? Clouds? All I know is that we move fast, and it’s going to be fun to see what unfolds.

The biggest news this week may not necessarily be database related; it focused on CPU security vulnerabilities and the potential slowdowns of your servers once the updates are applied. Please do read Meltdown and Spectre: CPU Security Vulnerabilities. Peter Zaitsev himself, got quoted in Bloomberg:

Peter Zaitsev, the co-founder and chief executive officer of Percona, a Raleigh, North Carolina-based company that helps businesses set and manage large computer databases, said that firms running such databases might see a 10 to 20 percent slowdown in performance from the patches being issued. He said this was not enough to cause major disruptions for most applications. He also said that subsequent versions of the patch would likely further reduce any performance impacts.

He also said that in cases where a company has a server completely dedicated to a single application there was likely no need to implement the patch as these machines are not susceptible to the attacks researchers have discovered.

Now that we’re all looking at 2018, I also recommend reading A Look at Ten New Database Systems Released in 2017 – featuring TimescaleDB, Azure Cosmos DB, Spanner, Neptune, YugaByte, Peloton, JanusGraph, Aurora Serverless, TileDB, and Memgraph. Also, don’t forget to read What lies ahead for data in 2018 – interesting thoughts on making graph/time series data easier, data partnerships, machine learning, and a lot more.

Releases
  • Percona Toolkit 3.0.6 – now with better support for pt-table-sync and MyRocks, pt-stalk checks the RocksDB status, pt-mysql-summary now expanded to include RocksDB information, and more!
  • MariaDB Server 10.2.12 – improvements in InnoDB, like shutdowns not being blocked by large transaction rollback, and more.
Link List Upcoming appearances
  • FOSDEM 2018 – Brussels, Belgium – February 3-4 2018
  • SCALE16x – Pasadena, California, USA – March 8-11 2018
Feedback

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

Finding out the MySQL performance regression due to kernel mitigation for Meltdown CPU vulnerability

Update: I included the results for when PCID is disabled, for comparison, as a worse case scenario.

After learning about Meltdown and Spectre, I waited patiently to get a fix from my OS vendor. However, there were several reports of performance impact due to the kernel mitigation- for example on the PostgresQL developers mailing list there was reports of up to 23% throughput loss; Red Hat engineers report a regression range of 1-20%, but setting OLTP systems as the worse type of workload. As it will be highly dependent on the hardware and workload, I decided of doing some test myself for the use cases I need.

My setup

It is similar to that of my previous tests:

Hardware -desktop grade, no Xeon or proper RAID:

  • Intel(R) Core(TM) i7-4790K CPU @ 4.0GHz (x86_64 Quad-core with hyperthreading) with PCID support (disabling pcid with “nopcid” kernel command line will also be tested)
  • 32 GB of RAM
  • Single, desktop-grade, Samsung SSD 850 PRO 512GB

OS and configuration:

  • Debian GNU/Linux 9.3 “Stretch”, comparing kernels:
    • 4.9.0-4-amd64 #1 SMP Debian 4.9.65-3+deb9u1 (no mitigation)
    • 4.9.0-5-amd64 #1 SMP Debian 4.9.65-3+deb9u2 (latest kernel with security updates backported, including pti enabled according to security-announces)
  • datadir formatted as xfs, mounted with noatime option, all on top of LVM
  • MariaDB Server 10.1.30 compiled from source, queried locally through unix socket

The tests performed:

  • The single-thread write with LOAD DATA
  • A read-only sysbench with 8 and 64 threads
The results

LOAD DATA (single thread)

We have been measuring LOAD DATA performance of a single OpenStreetMap table (CSV file) in several previous tests as we detected a regression on some MySQL versions with single-thread write load. I believe it could be a interesting place to start. I tested both the default configuration and another more similar to WMF production:

Load time rows/s Unpatched Kernel, default configuration 229.4±1s 203754 Patched Kernel, default configuration 227.8±2.5s 205185 Patched Kernel, nopcid, default configuration 227.9±1.6s 205099 Unpatched Kernel, WMF configuration 163.5±1s 285878 Patched Kernel, WMF configuration 163.3±1s 286229 Patched Kernel, nopcid, WMF configuration 165.1±1.3s 283108

No meaningful regressions are observed in this case between the default patched and unpatched kernels- the variability is within the measured error. The nopcid could be showing some overhead, but the overhead (around 1%) is barely above the measuring error. The nopcid option is interesting not because the hardware support, but because of the kernel support- backporting it could be a no-option for older distro versions, as Moritz says on the comments.

It is interesting to notice, although offtopic, that while the results with the WMF “optimized” configuration have become better compared to previous years results (due, most likely, to improved CPU and memory resources); the defaults have become worse- a reminder that defaults are not a good metric for comparison.

This is not a surprising result, a single thread is not a real OLTP workload, and more time will be wasted on io waits than the necessary syscalls.

RO-OLTP

Let’s try with a different workload- let’s use a proper benchmarking tool, create a table and perform point selects with it, with 2 different levels of concurrency- 8 threads and 64 threads:

sysbench --test=oltp --oltp-table-size=1000000 --mysql-db=test --mysql-user=test prepare sysbench --test=oltp --oltp-table-size=1000000 --mysql-db=test --mysql-user=test --max-time=120 --oltp-read-only=on --max-requests=0 --num-threads={8, 64} run

TPS SELECTs/s 95 percentile of latency(ms) Unpatched Kernel, 8 threads 7333±30 100953±1000 1.15±0.05 Patched Kernel, 8 threads 6867±150 96140±2000 1.20±0.01 Patched Kernel, nopcid, 8 threads 6637±20 92915±200 1.27±0.05 Unpatched kernel, 64 threads 7298±50 102176±1000 43.21±0.15 Patched Kernel, 64 threads 6768±40 94747±1000 43.66±0.15 Patched Kernel, nopcid, 64 threads 6648±10 93073±100 43.96±0.10

In this case we can observe around a 4-7% regression in throughput if pcid is enabled. If pcid is disabled, they increase up to 9-10% bad, but not as bad as the warned by some “up to 20%”. If you are in my situation, and upgrade to stretch would be worth to get the pcid support.

Further testing would be required to check at what level of concurrency or what kind of workloads will work better or worse with the extra work for context switch. It will be interesting to measure it with production traffic, too, as some of the above could be nullified when network latencies are added to the mix. Further patches can also change the way mitigation works, plus probably things like having PCID support is helping transparently on all modern hardware.

Have you detected a larger regression? Are you going to patch all your databases right away? Tell me at @jynus.

Meltdown and Spectre: CPU Security Vulnerabilities

In this blog post, we examine the recent revelations about CPU security vulnerabilities.

The beginning of the new year also brings to light fresh and new CPU security vulnerabilities. Today’s big offenders originate on the hardware side – more specifically, the CPU. The reported hardware kernel bugs allow for direct access to data held in the computer/server’s memory, which in turn might leak sensitive data. Some of the most popular CPUs affected by these bugs are Intel, AMD and ARM.

The most important thing to know is that this vulnerability is not exploitable remotely, and requires that someone execute the malicious code locally. However, take extra precaution when running in virtualized environments (see below for more information).

A full overview (including a technical, in-depth explanation) can be found here: https://meltdownattack.com/.

These three CVEs refer to the issues:

Although the problems originate in hardware, you can mitigate the security issues by using updated operating system kernel versions. Patches specific to database servers such as Percona Server for MySQL, Percona Server for MongoDB, Percona XtraDB Cluster and others are unlikely.

Fixes in Various Operating Systems

Fixes and patches are available for Windows and MacOS. Not all major Linux distributions at the time of this post have released patches (though this is expected to evolve rapidly):

Security Impact

As mentioned above, this vulnerability is not exploitable remotely. It requires malicious code to be executed locally. An attacker must have either obtained unprivileged shell access or be able to load malicious code through other applications to be able to access memory from other processes (including MySQL’s memory).

To potentially exploit the vulnerability through MySQL, an attacker theoretically needs to gain access to a MySQL user account that has SUPER privileges. The attacker could then load UDF functions that contain the malicious code in order to access memory from the MySQL Server and other processes.
In MongoDB a similar behavior would need to use eval().

Cloud Providers, Virtualization and Containers

Some hypervisors are affected by this as they might access memory from other virtual machines. Containers are affected as well, as they can share the same kernel space.

More information (source):

Performance Impact

As a general rule, Percona always recommends installing the latest security patches. In this case, however, the decision to immediately apply the patch is complicated by the reported performance impact after doing so. These patches might affect database performance!

RedHat mentions the expected impact to be measurable, and Phoronix.com saw a noticeable hit for both PostgreSQL and Redis.

At this time, Percona does not have conclusive results on how much performance impact you might expect on your databases. We’re working on getting some benchmarks results published shortly. Check back soon!

2017 Year in Review at VividCortex

It’s easy to observe (pun intended) in the rear-view mirror. Hindsight bias aside, 2017 was a big year for VividCortex and our customers! We shipped lots of features and made tons of progress on the business. Here’s a brief overview of some of our proudest moments from 2017.

Enhanced PostgreSQL support

We love PostgreSQL, and we released lots of enhancements for it! Here’s some of the stuff we did for our PostgreSQL customers:

  • Supported the great work the Postgres community did on version 10 and all its new features
  • Made VividCortex awesome for our customers who use CitusDB
  • Added support to monitor PgBouncer, which nearly everyone who’s using Postgres in a mission-critical environment is using by default
  • Added SQL query analysis to provide that extra level of database-specific insight, so you get help really understanding what your SQL does
  • Added support for collecting and analyzing lots more data. You can now rank, sort, slice-and-dice so many things: Queries, Databases and Verbs by Shared Blocks Hit, Shared Blocks Read, Shared Blocks Dirtied, Shared Blocks Written, Local Blocks Hit, Local Blocks Read, Local Blocks Dirtied, Local Blocks Written, Temp Blocks Read, Temp Blocks Written, Block Read Time and Block Write Time. And that's not all, you can rank and profile Verbs and Users too!
Released Many MongoDB Improvements

We spent a ton of time expanding our support for our MongoDB customers. A few of the many things we improved:

  • Index analysis, a key pain point for MongoDB, which relies heavily on indexes for performance
  • Automatic discovery of the full MongoDB scale-out configuration, including support for discovering and monitoring mongod, mongos, and config servers
  • Auto-discovery of replication sets and clusters
  • A Node Group view to visualize the cluster hierarchy
  • Deep profiling capabilities for missing indexes, locking performance, data size, index size, and looking for blocking and most frequent queries in db.currentOp() -- plus a bunch more!
  • Cursor operations, EXPLAIN, new expert dashboards, and more
  • In-app help documentation tooltips to help you understand all those MongoDB metrics and what they mean

 

Released Extended Support for Amazon RDS

We added support for enhanced metrics in Amazon RDS, so now you get CloudWatch, host metrics, and tons more detail. This is super helpful when trying to debug black-box behaviors on RDS!


Released Expert Insights

In October we released Expert Insights for PostgreSQL, MongoDB, and MySQL. These features automatically and continually monitor your databases with dozens of rules and checks. They essentially continually test your database’s state and behavior to provide best practice recommendations about configuration, security, and query construction. Now you don’t have to manually review everything, because VividCortex never sleeps!

  Improved Charts and Dashboards

We released a bunch of updates to the Charts section in 2017. KPI dashboards for MongoDB and PostgreSQL are new chart categories with key performance indicator (KPI) metrics preselected by our brainiacs. They show you the most important performance and health information at a glance. Built by experts, so you don’t have to do all that work yourself:

 

The new "System Resources by Hosts" category of charts plots several hosts on each chart so you can easily spot outliers. In the following screenshot, you can immediately see one of the hosts has much higher CPU utilization than the others:

There’s more. We redesigned Charts and Dashboards for a better experience with hundreds or thousands of hosts: new summarized charts scale to infinity and beyond, and you can make your own custom dashboards. Customize away!

New Funding, SOC-2 Compliance, And More!

In August we closed our $8.5M Series A-1 led by Osage Venture Partners with the participation of Bull City Venture Partners, New Enterprise Associates (NEA), and Battery Ventures. We’re thrilled to have recruited such a great group of investors to help us on our way! We’re using the funding to hire and we’re doubling down on our investments in product improvements.

In December we completed SOC 2 Type I certification. This was a massive effort involving almost everyone in the company. We value our customers and we work hard to keep everyone safe!

The most rewarding and important things we did in 2017 were for our customers. There’s too much to list in detail, but we invite you to read their stories. Here’s a few: Shopify, SendGrid, and DraftKings. If you just want the highlights, here’s a quick video that covers a lot of what our customers say about us.

2017 was a productive year for us. In 2018 we’re looking forward to more of the same: shipping more features for our users, keeping up with all of the database news, and seeing everyone at conferences! Here’s to a great 2018 from all of us at VividCortex. See you there -- and if you haven't experienced VividCortex yet, give it a try!

MariaDB Server 10.2.12 now available

MariaDB Server 10.2.12 now available dbart Thu, 01/04/2018 - 12:56

The MariaDB project is pleased to announce the immediate availability of MariaDB Server 10.2.12. See the release notes and changelog for details and visit mariadb.com/downloads to download.

Download MariaDB Server 10.2.12

Release Notes Changelog What is MariaDB 10.2?

The MariaDB project is pleased to announce the immediate availability of MariaDB Server 10.2.12. See the release notes and changelog for details.

Login or Register to post comments

Percona Toolkit 3.0.6 Is Now Available

Percona announces the release of Percona Toolkit 3.0.6 on January 4, 2018.

Percona Toolkit is a collection of advanced command-line tools that perform a variety of MySQL and MongoDB server and system tasks too difficult or complex for DBAs to perform manually. Percona Toolkit, like all Percona software, is free and open source.

You download packages from the website or install from official repositories.

This release includes the following changes:

New Features:
  • PT-221: Improve pt-table-sync support for MyRocks
  • PT-218: pt-stalk now checks the RocksDB status
  • PT-214: pt-mysql-summary contains the RocksDB section
  • PT-205: pt-osc shows a message if trying to set the engine to rocksdb and binlog_format != row
  • PT-204: pt-table-checksum skips RocksDB tables.
Bug fixes:
  • PT-234: The general log parser cannot handle time stamps which include time zones
  • PT-229: pt-online-schema-change does not retry on a deadlock error when using Percona Server 5.7
  • PT-225: pt-table-checksum ignores generated columns

A DBA Analyses 'The Phoenix Project'

Last year, I read 'The Phoenix Project'. I liked it and as an IT manager in the past, I did experience high blood pressure during the SEV1 scenarios in the book.



I also liked the way DevOps methodology helped solve issues with IT as well as help the company succeed overall.
As a DBA, however, I did have some things that didn't make sense to me about this story.  Bare in mind that the two major incidents in the book were database related. So in this post, I would like to jot down some things I have noticed and how they could have been solved looking at them from a different lens.
Caution, Spoiler Alert

Incident No.1 - Tokenisation
In the first incident, a 3rd party supplier ran a script against the database to tokenise some personal data. This was related to an issue that information security highlighted, but had the result of effecting HR and accounting.
In the book, there is a complaint that there was no test environment to see if this script would have any negative effects on other parts of the organisation. 
Now to me, this does make sense and at the same time, makes no sense at all.
If you meant, that back in the day, it was hard to get full environments setup to test changes on your application servers, then you would probably be right. Today, perhaps based on the methodology that this book introduces, you probably do have those environments setup: either virtualised or in a container. 

Testing Database
What doesn't make sense to me is that is not having a test database. Now reading through the book, there are mentions of Oracle database and some MS SQL databases. As a mainly MySQL DBA, I have not always worked on those databases, but I have worked next to people who have. My observation is, if you were to have an Oracle database, you would almost certainly have other dev/test/UAT/staging/pre-prod database servers as well.  Why do I think this? If you can afford to pay for an Oracle database, you would probably get more testing databases under the same license. License being the most expensive part when using Oracle.   So a testing database to test things that may effect the precious and expensive database server is almost a certainty.

DBA as a Gatekeeper
Now it seems shocking to me that the DBA had not been involved in the process to validate this 3rd party script. Old school Oracle DBAs are involved in everything that happens on their servers.  Need a user on the database? goto the DBA.  Need a database server for a new app? please fill these in triplicates, detailing what would be the projected usage for the next 5 years.  In most companies, an Oracle DBAs may even setup integration between other products like Oracle HR and finance.  So how could you have run something that significant against the database without their knowledge is beyond me.
Assuming that a database field had in fact been messed up, then Oracle DBAs have a TON of really enviable backup and restore features.  They can query a table to view all the backups that are available to restore from and choose the point-in-time that is closest to what they need. A DBA could simply restore the database, fetch the specific table that had its column changed and apply it to the to production database.  Its more than one table? Restore the database, go over the changes in the logs a point-in-time and skip the parts the conversion script applied.

It seems to me that the authors wrote the book based on their own experiences, but those experiences occurred in companies that had no DBAs. Not having a DBA is a product of start ups, not old school 1500-person car-parts manufacturers.

Incident No.2 - Conversion
There was a crippling database issue to do with a database conversion that was needed along side some new code roll out. The issue caused a 2 day - break out the hand held receipt machine - downtime to the system.

Works on My Laptop
During the initial investigation, a developer said something along the lines of 'it worked fine on my laptop' when describing the performance of the database conversion scripts. The problem was that on production, it was x1000 slower. Now, I have written about how to not be the one that deploys that slow query to production before and this really states that situation. Apparently, they still didn't have a database testing environment to test it against.
However, on the topic above of 'DBA as a gatekeeper': Why didn't the DBA review the conversion scripts or was involved in the the code review process for SQL statements? It could be that there wasn't any in the company.
Another point was that they couldn't cancel the conversion after they started and noticed how slow it was. If this was within a transaction or a single alter table statement, why not? If too many things have changed, could they not restore the database to a point-in-time before the changes were made? Was the conversion x1000 slow instead of maybe x10 slow, because of a foreign key check that could have been turned off? A DBA would have given you those options.

Project Unicorn
After the hero turns things around and things begin to pickup, they decide to start a separate project to add predictive features to the main project. In it, they decided to bypass seeking permission for database changes and create a new database where they copied production data into it from several locations. I very much like this approach and it falls in line with the reactive micro services pattern
This would make this book ahead of its time. Instead of managing one main database (although, they did mention in the book that had a couple of dozen database servers) for the website, they can break it up into several database servers, based on functionality. What is required is to use tools - and I would believe in 2012, they meant ETL tools - to migrate the needed data into these new database servers. 
This would still need a DBA though or at the very least, a data engineer with an ops background, as you now need to:
  • Data model new environments based on data from old ones
  • Create and maintain data pipelines
  • Monitor for errors and fix data that didn't make it 
  • Fix data drift and re-sync data across servers

In addition, you now need to backup, monitor the availability and performance of these additional database servers.
So while it adds complexity to the backend and you are now moving from simple database maintenance to a more data architecture role, it is the way forward. Certainly the only way to have proper micro services with their own single-purpose and loosely coupled data stores.


it might have been better if they just hired a DBA to solve thier DB issues.— GuybrushThreepwoodⓋ (@jonathan_ukc) January 6, 2017

;)— Kevin Behr (@kevinbehr) January 8, 2017


Announcing ClusterControl 1.5.1 - Featuring Backup Encryption for MySQL, MongoDB & PostgreSQL

What better way to start a new year than with a new product release?

Today we are excited to announce the 1.5.1 release of ClusterControl - the all-inclusive database management system that lets you easily deploy, monitor, manage and scale highly available open source databases - and load balancers - in any environment: on-premise or in the cloud.

ClusterControl 1.5.1 features encryption of backups for MySQL, MongoDB and PostgreSQL, a new topology viewer, support for MongoDB 3.4, several user experience improvements and more!

Feature Highlights Related resources  ClusterControl Change Logs  ClusterControl Upgrade Instructions  Announcing ClusterControl 1.5 Full Backup and Restore Encryption for these supported backup methods
  • mysqldump, xtrabackup (MySQL)
  • pg_dump, pg_basebackup (PostgreSQL)
  • mongodump (MongoDB)

New Topology View (BETA) shows your replication topology (including load balancers) for your entire cluster to help you visualize your setup.

  • MySQL Replication Topology
  • MySQL Galera Topology
Improved MongoDB Support
  • Support for MongoDB v3.4
  • Fix to add back restore from backup
  • Multiple NICs support. Management/public IPs for monitoring connections and data/private IPs for replication traffic
Misc

Improved user experience featuring a new left-side navigation that includes:

  • Global settings breakout to make it easier to find settings related to a specific feature
  • Quick node actions that allow you to quickly perform actions on your node
ClusterControl Single Console for Your Entire Database Infrastructure Find out what else is new in ClusterControl Install ClusterControl for FREE View Release Details and Resources Improving Database Security: Backup & Restore Encryption

ClusterControl 1.5 introduces another step to ensuring your databases are kept secure and protected.

Backup & restore encryption means that backups are encrypted at rest using AES-256 CBC algorithm. An auto generated key will be stored in the cluster's configuration file under /etc/cmon.d. The backup files are transferred in encrypted format. Users can now secure their backups for offsite or cloud storage with the flip of a checkbox. This feature is available for select backup methods for MySQL, MongoDB & PostgreSQL.

New Topology View (beta)

This exciting new feature provides an “overhead” topology view of your entire cluster, including load balancers. While in beta, this feature currently supports MySQL Replication and Galera topologies. With this new feature, you can drag and drop to perform node actions. For example, you can drag a replication slave on top of a master node - which will prompt you to either rebuild the slave or change the replication master.

Improved User Experience

The new Left Side Navigation and the new quick actions and settings that accompany it mark the first major redesign to the ClusterControl interface in some time. ClusterControl offers a vast array of functionality, so much so that it can sometimes be overwhelming to the novice. This addition of the new navigation allows the user quick access to what they need on a regular basis and the new node quick actions lets users quickly run common commands and requests right from the navigation.

Download the new ClusterControl or request a demo.

Tags:  clustercontrol backup security encryption MySQL MongoDB PostgreSQL database management

Case in Point: A Year of Customer Experience with Percona

In 2017 we have welcomed many new customers into the Percona fold. It’s always interesting to find out what challenges the Percona team helped them to address and how they chose their relationship with Percona. As unbiased champions of open source database software, our consultancy, support and managed services staff apply their expertise across a wide range of technologies. Here are just a few stories from the past year.

Scaling applications on Amazon RDS the right way

Specializing in on-demand transportation services, Grab needed a high-availability, high performing database engine to serve their rapidly growing application. Grab operates in over 30 densely populated cities in six countries of Southeast Asia. Although they had implemented a split database with Redis caching, they felt there was still room – and a necessity – to improve performance.

Maximum RDS hardware size had been reached, so Percona consultants worked with Grab to rearchitect the database design and reduce its overall footprint. The database management team were left with a solution that was manageable and with the capacity to expand.

“[Percona] proposed solutions that met our scaling and performance needs. The suggested changes were implemented fast, with no database downtime, and we saw an immediate improvement in our performance.” – Edwin Law, Data Engineering Lead, Grab

Managed database services

A B2C business with upward of 25 million subscribers, deal-brokering site Pepper.com has over 500 million page views a month. Data security is an issue at the forefront of their business. When their DBA left to take on a new challenge, the company was left with a dilemma common to many companies that have grown exponentially from a small, committed core of staff. With business continuity relying on a single resource, they knew they had to mitigate exposure to this risk, and Percona’s Managed Services offered a perfect solution.

Contracting out their database management provides 24-hour coverage for their databases, 365 days a year. Furthermore, rather than having to rely on one person’s knowledge and availability, in-depth expertise and a wide pool of knowledge are available if problems arise.

“Our subscribers expect deals that get posted on Pepper.com to not just be valid but scrutinized and rated by the community. Guaranteeing database performance is key to making sure our web applications are responsive and up-to-date. Percona Care Ultimate helps us to achieve these objectives.” – Pavel Genov, Head of Software Development at Pepper.com

Maximising database uptime

For Open Sky the challenge was to ensure maximum uptime. Their business model demands high availability with revenue directly tied to uptime. Using both MongoDB and MySQL in their architecture, hiring Percona to help with their DBA work made absolute sense, since Percona maintains distributions of these products.

Many smaller companies find it hard to justify the expense of a full-time DBA, never mind a whole team. Depending too much on a single person is an obvious candidate for a single point of failure. With high availability at the core of their business, not having DBA cover is a risky strategy.

Achieving outstanding database and query performance

LMAX Exchange also needed high availability, but was looking for very high performance too. Clients create ad hoc reports using vast amounts of data. With their logo incorporating the phrase “Speed Price Transparency”, their reporting system had something to live up to. As with all of our engagements, we carried out an initial audit of their existing systems. Through that were able to make some recommendations off the bat for improvements to their database configuration. The key to this exercise was to deliver important performance gains without the need for wholesale application upheaval. Percona support services help keep them on track.

Preparing for database growth

Alkemics’ challenge was that as a SaaS provider operating in a competitive industry and B2B, not only their own success but that of their customers relies on their delivery of an optimal service. The company was gearing for growth and needed to make sure that their database systems could support their ambitions. With that in mind, they hired Percona to carry out an audit of their database management and replication process. The database tuning recommendations once implemented led to a 45% decrease in memory usage. Along with overall improvements in query and database performance, Alkemics gained confidence in their ability to sustain planned growth.

Cloud Database Environments

Another factor influencing the need for optimization is cloud delivery, and the work with Alkemics illustrates what our team can achieve. During 2017, the number of applications using cloud infrastructure – like AWS, Microsoft Azure, and Google Cloud – increased rapidly, and we expect this trend to continue in 2018. With many cloud services charging solely on the basis of resource usage – disk space, memory, CPU, and IO all being factors – the DBA’s role has come into sharp focus in the context of cost. Percona Monitoring and Management underwent a substantial development, and in 1.5.3 incorporated support for Amazon RDS Aurora.

Optimizing database resource usage and cloud footprint

Lookout uses the Amazon Web Services (AWS) platform with Aurora as their main database software. Percona’s expertise with databases in the cloud helped Lookout to reduce their cloud footprint and, therefore, their operational costs. As a mobile security company, maximum availability in a secure environment is a core business function. As well as Percona’s database audit and optimization services, Lookout leverage Percona Monitoring and Management in the development of a resilient, pro-active approach to cloud management.

Check out our webinar with Lookout as well!

Summing up our customers’ stories

A common theme is that – whatever the size of their business – our customers are pushing the boundaries of technology with their projects and ambitions. This makes it an exciting place to work for Percona’s consultants, support, and technical account managers. Every new client brings a new challenge, an opportunity to look at the technology from a fresh angle and the chance to extend their knowledge.

If you are the kind of person that would enjoy this kind of challenge, then don’t forget we’re hiring!

Benchmarking ProxySQL 1.4.4

Comparing ProxySQL 1.4.3 vs. 1.4.4

ProxySQL 1.4.4 has recently been released (GA on Dec 20, 2017) which naturally leads to the question “What performance gains are to be expected when moving to the new version?”. In this article we compare performance between 1.4.3 and 1.4.4 in a CPU bound workload. The instances are configured with the default settings for the initial benchmark and then again after tuning one specific variable, mysql-max_stmts_per_connection, which can lead to substantial performance gains.

Lets first discuss what the ProxySQL variable mysql-max_stmts_per_connection affects and how it is evaluated. ProxySQL maintains a counter for each backend connection which increments each time a statement is prepared on that connection. Just before the connection is returned to the pool, this counter is evaluated against mysql-max_stmts_per_connection, and if the threshold is exceeded then the connection is closed (behaviour up to version 1.4.3) else the connection is reset (behaviour starting version 1.4.4 in order to improve the efficiency of connection handling as pointed out in Percona’s blog post regarding: XtraDB Cluster best practices for AWS).

Note: The mysql-max_stmts_per_connection variable is configured to 20 by default and can be tuned up however keep in mind that when increasing mysql-max_stmts_per_connection you may need to also increase the value of the MySQL variable max_prepared_stmt_count which has a maximum limit of 1048576.

In the graphs below performance is compared between ProxySQL version 1.4.3 and 1.4.4 using default values and two benchmarks for each (one with mysql-max_stmts_per_connection set to 20 [default] and another with the variable set to 100). A Sysbench benchmark was executed for 300 seconds with 64x threads performing a mixture of point and range selects on 10x tables consisting of 40,000,000 rows each running on a 3x node Percona XtraDB Cluster each running on 40x cores and 1 Gbps NICs.

The key averages for each benchmark are as follows:

Metric 1.4.3
(default) 1.4.4
(default) 1.4.3
(mspc 100) 1.4.4
(mspc 100) QPS (average) 126179.53 141689.68 155537.27 163209.93 Latency (average) 14.67 13.10 11.93 11.37 Latency (95th percentile) 18.28 16.41 15.55 14.73
  • It is interesting to note that ProxySQL 1.4.4 is 13% faster out of the box with the default settings compared to ProxySQL 1.4.3.
  • It is also quite interesting to see that when mysql-max_stmts_per_connection is tuned to 100, and for this specific workload, ProxySQL 1.4.3 in itself could perform 24% faster!
  • With ProxySQL 1.4.4 we can see that when mysql-max_stmts_per_connection is tuned to 100 performance is 15% faster, however this is still 5% faster than ProxySQL 1.4.3 when tuned as the code is more efficient in the newer release.
  • A similar trend can be seen in terms of latency between the various versions of ProxySQL and the tuning of the mysql-max_stmts_per_connection variable.
  • Once again ProxySQL 1.4.4 exhibits the lowest amount of latency (especially when mysql-max_stmts_per_connection is tuned higher than the default value).
  • Naturally the effects of the mysql-max_stmts_per_connection variable largely depend on your workload and a synthetic read only sysbench workload serves more for comparative purposes.

Based on the above benchmarks it is fair to say that ProxySQL 1.4.4 has more consistent and efficient performance with regards to 1.4.3 resulting in at least a 13% improvement in average QPS and a 15% improvement in terms of average Latency out of the box.

### For reference, the command used to benchmark was: sysbench --threads=64 /usr/share/sysbench/oltp_read_only.lua --tables=10 --table-size=40000000 --report-interval=5 --rand-type=pareto --forced-shutdown=1 --time=300 --events=0 --percentile=95 --mysql-user=sbtest --mysql-password=$pw --mysql-db=sbtest10t40M --mysql-storage-engine=INNODB --mysql-host=127.0.0.1 --mysql-port=6033 --point-selects=25 --range_size=5 --skip_trx=on run

Authored by: Nick Vyzas

Top 4 Reasons Companies Won't Fix Their Database Issues

When I consult at a company, I aim to identify issues with their database and give options on how to solve them.
However, sometimes implementing those solutions may be a more lengthy process than it needs to be and sometimes they may not be implemented at all. During my career, I have observed some reasons as to why that might happen within organizations.

Obviously, the following observations will never happen at your company. I am just writing about them so that you might notice them in other places.

1. Legacy code 
People don't like to have anything to do with legacy code. It’s painful. It’s difficult. It’s risky to change. It runs business critical functions. Worse of all, they didn’t write it. This can be a problem as often, the most cripling database issues require changes to legacy code.

2. New Technologies or Methods
People don’t like you to introduce any new technologies they don’t want to learn and maintain. Not even different methods in technologies already being used. No fancy upgrades to the DB server, no new load balancers and certainly don’t start using SQL statements in the code over their existing ORM.

3. Old Technologies or Methods
In a complete polar opposite, people in tech organisations don’t like you to introduce boring technologies. What would be the point of introducing boring (yet tested) technologies when they could be playing around with shiny new ones. There is a caveat to this - groups prefer it when other groups they depend on (let’s say developers depend on ops) choose to use boring and tested technologies. Just not for themselves. And vice versa.

4. Management Involvement
Last, but certainly not least, no one from upper management will get involved in resolving these issues and push forward solutions. No project/product manager/agile-coach will be assigned to chase up issues. As far as they are concerned, this is an engineering issue and as engineers, you need to sort it out yourselves. Only 'change requests' from the business, have managers around it.


Final Thoughts
After some years of analysing database systems for performance issues, I am finally realising that I should also analyse human systems for performance issues.


An update on Write Set (parallel replication) bug fix in MySQL 8.0

In my MySQL Parallel Replication session at Percona Live Santa Clara 2017, I talked about a bug in Write Set tracking for parallel replication (Bug#86078).  At the time, I did not fully understand what was going wrong but since then, we (Engineers at Oracle and me) understood what happened and the bug is supposed to be fixed in MySQL 8.0.4.  This journey thought me interesting MySQL behavior and

Two New MySQL Books!

There are two new MySQL books both from Apress Press. One is an in depth master course on the subject and the other is a quick introduction.


ProMySQL NDB Cluster is subtitled Master the MySQL Cluster Lifecycle and at nearly 700 pages it is vital resource to anyone that runs or is thinking about running NDB Cluster. The authors, Jesper Wisborg Krogh and Mikiya Okuno, have distilled their vast knowledge of this difficult subject in a detail packed but easily readable book.  MySQL Cluster is much more complex in many areas than a regular MySQL server and here you will find all those details. If you run MySQL NDB Cluster then you need this book. The partitioning information in chapter 2 is worth the price of the book alone.  I am only a third of the way through the book and have found it as clear and concise as any technical book I have read and it is actually an easy read. 

MariaDB and MySQL Common Table Expressions and Window Functions Revealed by Daniel Bartholomew is a slender introduction to CTEs and Window functions.  If you were raised on MySQL and do not know either subject well, then I highly recommend this book.  CTEs are going to have a big impact on the way developers write sub queries and may cause self joins to become extinct.  Windowing functions will bring a new eave of analytical analysis to MySQL. This book is just over 100 pages and has useful examples for novices in either area. 

More books! Well yes, there are more MySQL books in the works so save your pocket change in order to buy them when they appear.

Fun with Bugs #59 - On MySQL Bug Reports I am Subscribed to, Part II

New Year (that starts on Monday!) gives a good opportunity to change something in our lives, start doing something new, better or different. Let's assume I failed with all these so far, as I am again posting about MySQL bugs here.

Since my previous post on this topic I've subscribed to 15 more MySQL bugs, and being on a combination of public holidays and vacation now gives me a good opportunity to review these bug reports.

Here they are, starting from the most recent:
  • Bug #89065 - "sync_binlog=1 on a busy server and slow binary log filesystem stalls slaves". I do not remember seeing multiple threads in "Finished reading one binlog; switching to next binlog" state, but it would be interesting to see this bug report processed properly.
  • Bug #89051 - "EXPLAIN output is different for same content depending when index was added". The way optimizer decides on "range" vs "ref" access is always interesting. Here, based on a recent comment by Øystein Grøvlen, the bug is actually that "Cost values are not correct when optimizer switch from ref-access to range-access in order to use more key parts".
  • Bug #88914 - "Potential null pointer dereference at pointer node->undo_recs (row0purge.cc)". It's funny to see many bugs becoming private as "security" ones and, at the same time, this bug, where reporter suspects it is exploitable, being "Open" and ignored for more than two weeks...
  • Bug #88891 - "Filtered replication leaves GTID holes with create database if not exists". I can not even explain how much I "like" all kinds of GTIDs I have to deal with, especially such a long lists of GTIDs that may be created in cases described in this report.
  • Bug #88863 - "COUNT(*) can sometimes return bogus value". Now, this is a really funny bug! It must be some race condition, and I'd really prefer to see this bug fixed soon.
  • Bug #88844 - "MySQL crash with InnoDB assertion failure in file pars0pars.cc". Nice crash (that I've never seen before) quickly reproduced by Shane Bester.
  • Bug #88834 - "Uneven slowdown on systems with many users". What can be better to speed up connection than checking the list of users one by one, especially when there are thousands of users?
  • Bug #88827 - "innodb uses too much space in the PK for concurrent inserts into the same table". As Mark Callaghan put it:
    "I am not sure my reproduction details will ever satisfy Sinisa but I don't mind if you don't fix this because I care more about MyRocks today and this bug makes MyRocks look better."We (Facebook's MySQL, MariaDB and Percona server users) do have MyRocks, but why poor Oracle MySQL users should suffer? Let's hope Sinisa Milivojevic will process the bug fast, with all the details clarified there :)
  • Bug #88791 - "Binary log for generated column contains new value as WHERE clause, not old value". Generated columns and binary logging, what could went wrong?
  • Bug #88764 - ""ALTER TABLE MODIFY..." takes time even if leaving table as is". Any simple test cases they come to my mind do NOT let to reproduce this problem, but I feel some potential as soon as more exotic cases like partitioning or data directory settings are considered. Let's wait for bug reporter to clarify.
  • Bug #88720 - "Inconsistent and unsafe FLUSH behavior in terms of replication". Nice summary of problems from Przemyslaw Malkowski. One more reason for me to hate GTIDs, honestly.
  • Bug #88694 - "MySQL accepts wildcard for database name for table level grant but won't use it". One more problem with privileges reported by Daniël van Eeden.
  • Bug #88674  - "Regression CREATE TBL from 5.7.17 to 20 (part #2: innodb_file_per_table = OFF)." and Bug #88673 - "Regression CREATE TBL from 5.7.17 to 20 (part #1: innodb_file_per_table = ON)." - these two bugs were reported by Jean-François Gagné and clearly show some things that are done wrong by Oracle when fixing (private, "security") bugs...
  • Bug #88671 - "ALL + BNL chosen over REF in query plan with a simple SELECT + JOIN". In this case optimizer (probably) does not take costs into account properly when choosing block nested loop join vs usual "ref" index access. Maybe just a matter of missing/wrong statistics also. It would be interesting to find out eventually.
  • Bug #88666 - "I_S FILES : all rows are displayed whatever the user privileges". Yet another bug report from Jocelyn Fournier. I am actually surprised with a number of bugs related to privileges that I notice recently.
  • Bug #88633 - "Auto_increment value on a table is less than max(id) can happen". It seems only MySQL 5.7.x is affected, but not 5.6.x.
  • Bug #88623 - "Modifying virtually generated column is not online". May be by design, but still surprising.
  • Bug #88534 - "XA may lost prepared transaction and cause different between master and slave". XA transactions with MySQL is still a sure way to disaster, I think. See Bug #87526 also that should appear in the next part of this series...
Stay tuned to more posts about MySQL bugs from me in the New Year of 2018!

Generated Columns and ProxySQL Instead of Referenced Tables

In this post, we’ll look at how to improve queries using generated columns and ProxySQL instead of implementing a referenced table.

Developers and architects don’t always have the time or complete information to properly analyze and design a database. That is why we see tables with more fields than needed, or with incorrect types. The best solution is implementing a change in the database schema and/or application level. In this post, we’ll look at an example of generated columns (using a char field) instead of creating a referenced table, and how using generated columns and ProxySQL avoids changes at the application level.

For this example, I will be using the film table of the Sakila database (with some changes). The original film table had a language_id as tinyint, which refers to the language table:

CREATE TABLE film ( film_id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT, title VARCHAR(255) NOT NULL, description TEXT DEFAULT NULL, release_year YEAR DEFAULT NULL, language_id TINYINT UNSIGNED NOT NULL, original_language_id TINYINT UNSIGNED DEFAULT NULL, ..... )ENGINE=InnoDB DEFAULT CHARSET=utf8;

I simplified the design of the table and added the language field as a char(20), as it is in table language:

CREATE TABLE `film` ( `film_id` int(10) unsigned NOT NULL AUTO_INCREMENT, `title` varchar(255) NOT NULL, `description` text, `release_year` year(4) DEFAULT NULL, `language` char(20) NOT NULL, `rating` enum('G','PG','PG-13','R','NC-17') DEFAULT 'G', PRIMARY KEY (`film_id`), KEY `idx_title` (`title`) ) ENGINE=InnoDB AUTO_INCREMENT=1001 DEFAULT CHARSET=utf8

We inserted 1M records and executed a query that filters by language:

mysql> select count(*) from film where language ='Mandarin'; +----------+ | count(*) | +----------+ | 256000 | +----------+ 1 row in set (0.92 sec) mysql> explain select count(*) from film where language ='Mandarin'; +----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------------+ | 1 | SIMPLE | film | NULL | ALL | NULL | NULL | NULL | NULL | 1014813 | 10.00 | Using where | +----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------------+ 1 row in set, 1 warning (0.02 sec)

The explain plan shows that it is performing a full table scan. Adding an index over language speeds up the query from 0.92 sec to 0.09 sec:

mysql> ALTER TABLE film ADD INDEX (language); Query OK, 0 rows affected (4.87 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> select count(*) from film where language ='Mandarin'; +----------+ | count(*) | +----------+ | 256000 | +----------+ 1 row in set (0.09 sec)

And the explain plan shows that it is using the language index:

*************************** 1. row *************************** id: 1 select_type: SIMPLE table: new_film partitions: NULL type: ref possible_keys: language key: language key_len: 60 ref: const rows: 245136 filtered: 100.00 Extra: Using index

Now, we are going to add a generated column and an index over this new field:

ALTER TABLE film ADD COLUMN language_id tinyint GENERATED ALWAYS AS ( CASE language WHEN 'English' THEN 1 WHEN 'Italian' THEN 2 WHEN 'Japanese' THEN 3 WHEN 'Mandarin' THEN 4 ELSE 0 END ) VIRTUAL ; ALTER TABLE film ADD INDEX (language_id);

Finally, we are going to add a rule in ProxySQL with the replace pattern:

replace into mysql_query_rules (rule_id,match_pattern,replace_pattern,apply,active) values (3, "(.*)FROM(.*)film(.*)WHERE(.*)language ='(.*)'(.*)", "1 FROM 2 film 3 WHERE 4 language_id = ( CASE '5' WHEN 'English' THEN 1 WHEN 'Italian' THEN 2 WHEN 'Japanese' THEN 3 WHEN 'Mandarin' THEN 4 ELSE 0 END ) 6",1,1); LOAD MYSQL QUERY RULES TO RUNTIME;

Now, when we execute the query through ProxySQL, it will replace:

SELECT count(*) FROM film WHERE language ='Mandarin';

With:

SELECT count(*) FROM film WHERE language_id = ( CASE 'Mandarin' WHEN 'English' THEN 1 WHEN 'Italian' THEN 2 WHEN 'Japanese' THEN 3 WHEN 'Mandarin' THEN 4 ELSE 0 END )

The explain plan shows it is using the language_id index:

mysql> explain SELECT count(*) FROM film WHERE language ='Mandarin' G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: film partitions: NULL type: ref possible_keys: language_id key: language_id key_len: 2 ref: const rows: 507142 filtered: 100.00 Extra: Using index

And that the query time decreases to 0.06 sec:

mysql> SELECT count(*) FROM film WHERE language ='Mandarin'; +----------+ | count(*) | +----------+ | 256000 | +----------+ 1 row in set (0.06 sec)

This speed up (from 0.09 to 0.06) on the select is because it uses a smaller index, as you can see in the key_len of the explain plan. Performing a range scan over entries of 64 bytes is slower than performing a range scan over entries of 8 bytes (the amount of data to read is less). For instance, the index_length with just the language index is 36274176 (34.6MB), while over the language_id is 14172160 (13.5MB).

Conclusion

Generated columns are not a long-term solution. The long-term solution is to add a table, or have the application internally use a referenced table. However, I found it particularly useful or practical in two scenarios:

  1. When database performance is dropping and change at the application level is not a priority for the business. This effort can be implemented by the DBA team without any other intervention.
  2. When you must show the performance improvement that the change will make before modifying anything at the application level.

Cons, you need to install ProxySQL (if you are testing, you can install it just on the test server), which adds a new layer (more complex) to your infrastructure (you need to use MySQL 5.7 at least, as well). Another thing to take into account is that the writes on the table will be a bit slower because of the newly generated columns and the new indexes.

Premier Open Source Database Conference Call for Papers closing January 12 2018

The call for papers for Percona Live Santa Clara 2018 was extended till January 12 2018. This means you still have time to get a submission in.

Topics of interest: MySQL, MongoDB, PostgreSQL & other open source databases. Don’t forget all the upcoming databases too (there’s a long list at db-engines).

I think to be fair, in the catch all “other”, we should also be thinking a lot about things like containerisation (Docker), Kubernetes, Mesosphere, the cloud (Amazon AWS RDS, Microsoft Azure, Google Cloud SQL, etc.), analytics (ClickHouse, MariaDB ColumnStore), and a lot more. Basically anything that would benefit an audience of database geeks whom are looking at it from all aspects.

That’s not to say case studies shouldn’t be considered. People always love to hear about stories from the trenches. This is your chance to talk about just that.

My 2018 Databases Wishlist

Well, the most important wishes I have for 2018 are a bit out of topic for this blog: forms of organisation without a formal authority, schools not teaching religions, and so on. But in this post, I will write about databases… as usual.

So, here is my whishlist, for what it matters.

More research on Learned Indexes

If you don’t know what I’m talking about, see this paper. Having a data structure faster than B-Trees is exciting. But of course I’d like to see also considerations on write performance.

Progress on using ML for database tuning

See this article. I don’t think that Machine Learning will ever be able to replace (good) DBAs, but having a tool which suggests tuning based on real workload sounds great. It can be a valid help for DBAs.

More research on stored functions transformation

Stored functions are useful but slow. But see this paper. It seems it is possible to transform imperative programs to queries, improving the complexity by some orders of magnitude.

On a side note, MariaDB implemented a lot of syntax from Oracle for stored procedures. While this sounds like a precise commercial strategy, the technical improvement on this area is great. Still, what I’d like to see is better performance, as well as support for external languages.

Galera 4

Let me be clear, I didn’t read any announcement that Galera 4 will be released this year. But they announced exciting news over time, and still the new version isn’t here. At some point, it should be released (hopefully).

Transactional DDL in the MySQL ecosystem

MySQL 8.0 has support for atomic DDL statements. They did it in a good way: it’s engine independent and, while it uses InnoDB information_schema tables, any engine is free to add support for this feature. They stated that this is the basis for transactional DDL, but we are not yet there. MariaDB has a task for transactional DDL.

EDIT: Thanks to Valerii Kravchuk for pointing me MDEV-11424 – Instant ALTER TABLE of failure-free record format changes. It is clearly worth adding it to my wishlist: please Maria, get it done!

Engines, engines, engines

RocksDB is great, please consolidate it. TokuDB can improve in many ways, please don’t stop investing on it. Next version of SPIDER will be in MariaDB 10.3, I hope that the development will be a bit more active in the future.

Don’t kill MyISAM. It is still useful in some cases. For Catawiki use cases, I find it better than InnoDB for temporary tables. Also JFG has a great use case example.

More progress on Tarantool and CockroachDB

Tarantool is a great database, originally NoSQL. It is extremely scriptable (actually it can be seen as a Lua application server) and its modules allow to read and write data from a wide variety of data sources, including MySQL replication. Recently, SQL support has been added.

CockroachDB is an open source RDBMS design to scale geographically. It uses distributed transaction. It also allows to tune the redundancy of data at table level and define replication zones.

Great conferences

I will be both at M18 (I’m not sponsored by my company, but I chosen to go anyway) and Percona Live. At M18 I will give a talk titled Somewhere between schema and schemaless. Of course I also submitted proposal for Percona Live, let’s see if they get accepted.

Percona Database Performance Blog Year in Review: Top Blog Posts

Let’s look at some of the most popular Percona Database Performance Blog posts in 2017.

The closing of a year lends itself to looking back. And making lists. With the Percona Database Performance Blog, Percona staff and leadership work hard to provide the open source community with insights, technical support, predictions and metrics around multiple open source database software technologies. We’ve had over three and a half million visits to the blog in 2017: thank you! We look forward to providing you with even better articles, news and information in 2018.

As 2017 moves into 2018, let’s take a quick look back at some of the most popular posts on the blog this year.

Top 10 Most Read

These posts had the most number of views (working down from the highest):

Millions of Queries per Second: PostgreSQL and MySQL’s Peaceful Battle at Today’s Demanding Workloads

Straight-up comparisons of different open source databases’ ability to handle today’s workload levels are always popular. Can open source databases cope with millions of queries per second? Many open source advocates would answer “yes.” However, assertions aren’t enough for well-grounded proof. That’s why in this blog post, Sveta Smirnova (Principal Technical  Services Engineer) shared the benchmark testing results from Alexander Korotkov (CEO of Development, Postgres Professional) and herself. The comparative research of PostgreSQL 9.6 and MySQL 5.7 performance is especially valuable for environments with multiple databases.

MySQL vs. MariaDB: Reality Check

Another highly viewed blog is Colin Charles (Chief Technology Evangelist) comparison of MariaDB vs. MySQL (including Percona Server for MySQL). The goal of this blog post is to evaluate, at a higher level, MySQL, MariaDB and Percona Server for MySQL side-by-side to better inform the adoption decision making process. It is largely an unofficial response to published comments from the MariaDB Corporation.

Column Store Database Benchmarks: MariaDB ColumnStore vs. Clickhouse vs. Apache Spark

Alexander Rubin’s (Principal Consultant in Architecture & Projects) blog shares some column store database benchmark results, and compares the query performance of MariaDB ColumnStore v. 1.0.7 (based on InfiniDB), Clickhouse and Apache Spark.The purpose of the benchmark is to see how these three solutions work on a single big server, with many CPU cores and large amounts of RAM. Both systems are massively parallel (MPP) database systems, so they should use many cores for SELECT queries.

Dealing with MySQL Error Code 1215: “Cannot add foreign key constraint”

Our Support customers often come to us with things like “My database deployment fails with error 1215”, “Am trying to create a foreign key and can’t get it working” or “Why am I unable to create a constraint?” Marcos Albe (Principal Support Engineer) looks at how to resolve MySQL error code 1215: “Cannot add foreign key constraint.”

ZFS from a MySQL perspective

This post (first of a series) from Yves Trudeau (Principal Consultant) explores and investigates the use of ZFS with MySQL to see when ZFS can help solve real problems. He examines ZFS and tries to draw parallels with the architecture of MySQL to better understand how ZFS works and behaves.

Overview of Different MySQL Replication Solutions

Although information is plentiful, replication is often incompletely understood, or completely misunderstood. This blog post by Dimitri Vanoverbeke (Solution Engineer) reviews some of the MySQL replication concepts that are part of the MySQL environment (and Percona Server for MySQL specifically). He clarifies some of the misconceptions people have about replication.

Quest for Better Replication in MySQL: Galera vs. Group Replication

Thanks to the Galera plugin, founded by the Codership team, we’ve had the choice between asynchronous and virtually synchronous replication in the MySQL ecosystem for quite a few years already. Moreover, we can choose between at least three software providers: Codership, MariaDB and Percona, each with its own Galera implementation. The situation recently became much more interesting when MySQL Group Replication went into GA (stable) stage in December 2016.

In this post, Przemysław Malkowski (Principal Support Engineer) points out a couple of interesting differences between Group Replication and Galera, which should be helpful to those considering switching from one to another (or if they are planning to test them).

Using NVMe Command Line Tools to Check NVMe Flash Health

Checking the NVMe flash health from the command line for both older and new drives is fairly straightforward. But what does the health information mean? In this blog post, Peter Zaitsev (CEO) investigates both how to check the NVMe flash health on both older and new hardware, as well as a breakdown of the collected information.

ClickHouse: New Open Source Columnar Database

In this blog post, Alexander Rubin (Principal Consultant in Architecture & Projects) decided to try ClickHouse: an open source column-oriented database management system developed by Yandex (it currently powers Yandex.Metrica, the world’s second-largest web analytics platform). He runs the database using Wikipedia page statistics as a data source and compares the results to a previous test of Apache Spark and MySQL.

Setup ProxySQL for High Availability (not a Single Point of Failure)

Percona has had a lot of opportunities to present and discuss a very powerful tool that is more and more used in the architectures supporting MySQL: ProxySQL. In this blog, Marco Tusa (High Availability Practice Manager) looks at how to set up ProxySQL for high availability.

Honorable Mention

MongoDB 3.4 Views

Views are often used in relational databases to achieve both data security and a high level of abstraction, making it easier to retrieve data. In this blog post, Adamo Tonete (Senior Support Engineer) looks at how to set up views in MongoDB 3.4.

Top 10 Most Commented

These posts generated some healthy discussions (not surprisingly, this list overlaps with the first):

Posts Worth Revisiting

Don’t miss these great posts that have excellent information on important topics:

Have a great end of the year celebration, and we look forward to providing more great blog posts in 2018.

This Week in Data with Colin Charles 21: Looking Back on 2017 in the Open Source Database Community

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

The end of the year is upon us, and this column is being penned a little earlier than usual as I enjoy a quick bout of wrapping up for the year.

We’ve had a lot of things happen in the database world. It’s clear that open source is everywhere, the cloud is large, and we’ve seen a lot of interesting movements in our MySQL world, from the standpoint of corporate contributions too. GitHub continues to enhance Orchestrator, and gh-ost keeps on getting better; Facebook has ensured MyRocks is now mainstream in both MariaDB Server and Percona Server for MySQL distributions; Alibaba Cloud and Tencent have been contributing to make MariaDB Server better.

We’ve seen the year end with MySQL 8.0 release candidate ready, as well as MariaDB Server 10.3 Beta. We had MariaDB Server 10.2 go GA in May 2017, and we’ve seen a steady stream of improvements in MySQL & Percona Server 5.7 releases.

Vitess I think is still a bigger deal than it’s made out to be. Slack talking about their use case at Percona Live Dublin was good. ProxySQL is a shining star for proxies.

We’ve seen some database vendors pass on — RethinkDB, with very promising technology, started in 2009, raised $12.2m, but finally ended up at Stripe with the code open and relicensed thanks to the Cloud Native Computing Foundation. We’ve seen Basho (makers of Riak), start in 2008, raised $61.2m, ended up at Bet365 and Riak is all open source now.

We’ve seen MongoDB IPO, MariaDB Corporation raise more money (some reported $98.2m in total), and more money still streams into the database world, like for BlazingDB in the GPU space and so on.

What do I look forward to in 2018? Oracle compatibility and PL/SQL in MariaDB Server 10.3 should be interesting. I’ve heard a lot of buzz around Apache Kafka. I expect we’ll see more manageability in MySQL. And also, fun to note that MMAPv1 in MongoDB has been deprecated before MyISAM in MySQL.

Right before the New Year, it is interesting to look at some proxy statistics of database usage, via the WordPress Statistics. Head down to the MySQL versions, and you’ll note that 5.5 gets 41.9% of users (this could be MySQL/Percona Server for MySQL or MariaDB Server), but you’ll also notice at a close second comes 5.6 at 39.8% of the users (this is only MySQL or Percona Server for MySQL). 5.7 gets 6.4% of the users as the 3rd most popular option, followed by 5.1 at 4.3% of users of WordPress (these folk desperately need to upgrade). 10.0 gets 1.6%, while 10.1 gets 3.8% (these are all MariaDB Server versions only). So the death of MySQL has greatly been exaggerated — people like it, people use it, and I can only imagine if more distributions ship 5.7 or 8.0, this could be a win for MySQL.

Releases Link List Upcoming appearances
  • FOSDEM 2018 – Brussels, Belgium – February 3-4 2018
  • SCALE16x – Pasadena, California, USA – March 8-11 2018
Feedback

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

 

Monitoring Amazon Aurora with PMM

In this blog, we’ll look at how to monitor Amazon Aurora using Percona Monitoring and Management (PMM).

One impact of the growth of web services such as AWS on DBAs is the need to understand and report on database and application resource usage. DBAs have always had to monitor for performance-related usability issues, which may have led to investment in larger servers. However, services such as Aurora operate pricing models based on usage. Performance issues can be more closely attributed to rising costs, so badly performing systems more visibly affect the bottom line. DBA has become a cost center!

With that in mind, from Percona Monitoring and Management (PMM) 1.5 onwards, we delivered features that provide enhanced support for the performance monitoring of Amazon Aurora instances. “[Aurora] is a fully managed, MySQL- and PostgreSQL-compatible, relational database engine.” (Amazon docs).

Here’s a walkthrough of some of the key features in PMM that could benefit DBAs managing Aurora, and eliminate the need for additional monitoring nodes. The images here, pulled up from our demo system, display a seven-day overview of one of our test instances. You can focus tightly on the past five minutes or pan out to the last five years, depending on your analysis requirements.

Starting with this dashboard summary, which appears at the top of the screen, PMM displays an instant summary on the “health” of your instance.

Engine uptime and CPU Utilization are helpfully highlighted in green, reinforcing a healthy status. Amber or red alerts means pay attention to the instance and whether there have been issues in the recent past.

In this case, we can see that Aurora was last restarted 2.1 weeks ago.

The next three statistics in this section cover the current status of the instance at the time you loaded the dashboard. Aurora is using ~15% of the available CPU resources. Your application is asking approximately ~400 queries per second.There is  370MiB of unused RAM at the current time.

Aurora Costs

The next four graphs are closely aligned with running costs. In creating these graphs, PMM calls on Amazon’s CloudWatch service. This provides up to one million API calls per month as part of your service, without additional charge (December 2017). A refresh of these graphs uses 15 calls, and you can configure the refresh frequency.

Three of these graphs represent CPU Utilization, Network Traffic and Memory Usage. As a DBA you are likely to be familiar with these terms. PMM displays these as a metric series over time.

The fourth graph of CPU Credit Usage, top right, covers usage credit and applies specifically to Aurora MySQL db.t2.small and db.t2.medium instances. There are two parameters in play here. CPUCreditBalance represents the number of CPU credits accumulated during the specified time, allocated to the instance’s virtual CPU.  CPUCreditUsage represents the actual CPU used during the same period. In essence, the gap between the two indicates the capacity of the instance to operate beyond its baseline performance level at a given rate.

Our demo instance, which runs a synthetic workload based on sysbench, is showing a regular usage pattern and accumulating credit. It’s no surprise that we are not bursting our CPU requests. This pattern of use is less likely to apply to a real-life application.

By the way, clicking on any graph in PMM displays it in full screen, so we don’t restrict your view to a quadrant of the screen.

Aurora MySQL

Next up, there’s a set of four graphs representing MySQL specific metrics. These show information that’s important to DBAs as a matter of course. They take on more significance if you’re seeking opportunities to tune applications to minimise costs.

The Percona Database Performance blog has lots of blog posts that cover these metrics in more detail, and we may well revisit them in due course in the context of Aurora MySQL. For this review of PMM, it’s enough to say that each of these would have its place in tuning with a view to improving your overall efficiency.

Summary

Since charges for web services are based on usage, the role of the DBA is in the spotlight. You need to optimize usage, and PMM 1.5 can help you achieve that for Amazon Aurora thanks to these features:

  • Dedicated Aurora dashboard which offers maximum visibility into key database characteristics
  • Simple configuration of key Aurora settings via a web interface.
  • We renamed Amazon RDS OS Metrics to Amazon RDS / Amazon MySQL Metrics. This better reflects the Amazon Data Services that we support.

And did you know that beyond providing PMM, Percona has built a reputation for MySQL and now Amazon Aurora proficiency? We recently worked with Lookout to increase efficiency and more effectively use their Amazon Aurora deployment to save Lookout hundreds of thousands of dollars! If you think you could benefit from this kind of Amazon Aurora or other MySQL assistance, please contact us!

Pages