Planet MySQL

Percona Live from the Emerald Isle: Containerised Dolphins, MySQL Load Balancers, MongoDB Management & more - for plenty of 9s

Yes, it’s that time of year again: the Percona Live Europe Conference is just around the corner.

And we’ll be broadcasting live from the Emerald Isle!

Quite literally, since we’re planning to be on our Twitter and Facebook channels during the course of the conference, so do make sure to tune in (if you’re not attending in person). And this year’s location is indeed Dublin, Ireland, so expect lots of banter and a bit of craic.

More specifically though, the Severalnines team will be well represented with three talks and a booth in the exhibition area. So do come and meet us there. If you haven’t registered yet, there’s still time to do so on the conference website: https://www.percona.com/live/e17/

Our talks at the conference:

Ashraf Sharif, Senior Support Engineer will be talking about MySQL on Docker and containerised dolphins (which only sounds like a good idea in the database world).

Krzysztof Książek, Senior Support Engineer, will share his knowledge and experience on all things MySQL load balancing.

And Ruairí Newman, also Senior Support Engineer, will be discussing what some of the main considerations are to think through when looking at automating and managing MongoDB - including a closer look at MongoDB Ops Manager and ClusterControl.

Related resources  MySQL on Docker - Understanding the Basics  ClusterControl for MongoDB  MySQL Load Balancing

And since we don’t solely employ Senior Support Engineers at Severalnines, you’ll be pleased to know that Andrada Enache, Sales Manager, and myself will also be present to talk open source database management with ClusterControl at our booth.

This year’s conference agenda looks pretty exciting overall with a wide enough range of topics, so there’ll be something of interest for every open source database aficionado out there.

See you in Dublin ;-)

Tags:  percona live docker MongoDB MySQL

Heads Up: The List of Replication Defaults That Have Changed in 8.0.2

In development milestone release (DMR) version 8.0.2 we are changing several replication options. The motivation behind this is simply that we want our users to enjoy default installations with the best efficient setup, configuration and performance. We also don’t want users to struggle getting them to work optimally.…

Always Verify Examples When Comparing DB Products (PostgreSQL and MySQL)

In this blog post, I’ll look at a comparison of PostgreSQL and MySQL.

I came across a post from Hans-Juergen Schoenig, a Postgres consultant at Cybertec. In it, he dismissed MySQL and showed Postgres as better. While his post ignores most of the reasons why MySQL is better, I will focus on where his post is less than accurate. Testing for MySQL was done with Percona Server 5.7, defaults.

Mr. Schoenig complains that MySQL changes data types automatically. He claims inserting 1234.5678 into a numeric(4, 2) column on Postgres produces an error, and that MySQL just rounds the number to fit. In my testing I found this to be a false claim:

mysql> CREATE TABLE data ( -> id integer NOT NULL, -> data numeric(4, 2)); Query OK, 0 rows affected (0.07 sec) mysql> INSERT INTO data VALUES (1, 1234.5678); ERROR 1264 (22003): Out of range value for column 'data' at row 1

His next claim is that MySQL allows updating a key column to NULL and silently changes it to 0. This is also false:

mysql> INSERT INTO data VALUES (1, 12); Query OK, 1 row affected (0.00 sec) mysql> UPDATE data SET id = NULL WHERE id = 1; ERROR 1048 (23000): Column 'id' cannot be null

In the original post, we never see the warnings and so don’t have the full details of his environment. Since he didn’t specify which version he was testing on, I will point out that MySQL 5.7 does a far better job out-of-the-box handling your data than 5.6 does, and SQL Mode has existed in MySQL for ages. Any user could set it to

STRICT_ALL|TRANS_TABLES and get the behavior that is now default in 5.7.

The author is also focusing on a narrow issue, using it to say Postgres is better. I feel this is misleading. I could point out factors in MySQL that are better than in Postgres as well.

This is another case of “don’t necessarily take our word for it”. A simple test of what you see on a blog can help you understand how things work in your environment and why.

IO-bound table scan performance for MyRocks, InnoDB and TokuDB

I used sysbench to compare IO-bound table scan performance for MyRocks, InnoDB and TokuDB. Tests were run on a large server with fast SSD, Intel NUCs with SSD and an Intel NUC with disk. I call this IO-bound because for all tests the table was larger than RAM.

tl;dr
  • MyRocks can be more than 2X slower than for InnoDB. 
  • InnoDB in 5.7 does better than in 5.6
  • TokuDB without compression is comparable to InnoDB without compression and does much better than InnoDB when prefetching is enabled.
  • Compression usually has a small impact on scan performance for MyRocks with zstd and a much larger impact for TokuDB with zlib. I wonder how much of this is a measure of zstd vs zlib.
  • Scans were usually slower for all engines after fragmentation but the impact was larger for MyRocks and TokuDB than for InnoDB.

Configuration

I used my sysbench helper scripts with my sysbench branch. For tests with X tables there was 1 connection per table doing a full scan and when X > 1 the scans were concurrent. The scan time was measured twice -- first immediately after the load and index step and then after many updates have been applied. The second measurement was done to show the impact of fragmentation on scan performance.

I repeated tests on different hardware:
  • 48core.ssd - server has 48 HW threads, fast SSD and 50gb of RAM. Tests were done with 8 tables and 100M rows/table and then 1 table with 800M rows.
  • i3.ssd - a core i3 Intel NUC with Samsung 850 SSD, 8gb of RAM and 4 HW threads. The test used 2 tables and 80M rows/table.
  • i3.disk - a core i3 Intel NUC with 1 disk, 8gb of RAM and 4 HW threads. The test used 1 table and 160M rows/table.
I repeated tests for MyRocks, InnoDB and TokuDB:
  • I compiled MyRocks on August 15 with git hash 0d76ae. The MyRocks tests were done without compression and with zstd compression (myrocks.none, myrocks.zstd). I did one test for MyRocks with a binary that did not use special instructions to make crc32 faster (myrocks.none.slowcrc) and learned that fast crc doesn't make a difference on this test. It would be a bigger deal for an IO-bound test doing point queries.
  • I used TokuDB from Percona Server 5.7.17. The TokuDB tests were done without compression and with zlib compression. I tried tokudb_disable_prefetching ON and OFF (toku5717.none, toku5717.none.prefetch), but I have been setting this to ON for my OLTP benchmarks because enabling it ruined some OLTP results.
  • I used InnoDB from upstream 5.6.35 and 5.7.17. The performance_schema was enabled. The InnoDB tests did not use compression. 

Results

The results below list the number of seconds to scan the table(s) and the time relative to InnoDB from MySQL 5.6.35. For the relative time a value greater than 1 means the engine is slower than InnoDB. These values are reported for pre and post where pre is the measurement taken immediately after loading the table and creating the secondary index and post is the measurement taken after applying random updates to the table(s).

See tl;dr above for what I learned from these results.

Large server


These are results from 8 tables with 100M rows/table and then 1 table and 800M rows/table on the large server.

48core.ssd - 8t x 100m
pre     pre     post    post    engine
secs    ratio   secs    ratio
221     2.302   246     2.256   myrocks.none
201     2.093   211     1.935   myrocks.zstd
 96     1.000   109     1.000   inno5635
 75     0.781    86     0.788   inno5717
 67     0.697    94     0.862   touk5717.none
 39     0.406    69     0.633   toku5717.none.prefetch
190     1.979   224     2.055   toku5717.zlib

48core.ssd - 1t x 800m
pre     pre     post    post    engine
secs    ratio   secs    ratio
 638    1.065   1032    1.627   myrocks.none
 916    1.529   1063    1.676   myrocks.zstd
 599    1.000    634    1.000   inno5635
 434    0.724    449    0.708   inno5717
 513    0.856    735    1.159   toku5717.none
 249    0.415    502    0.791   toku5717.none.prefetch
1525    2.545   1776    2.801   toku5717.zlib

Intel NUC

These are results from the Intel NUC using SSD and then a disk.

i3.ssd - 2t x 80m
pre     pre     post    post    engine
secs    ratio   secs    ratio
181     1.448   192     1.560   myrocks.none
182     1.456   189     1.536   myrocks.none.slowcrc
219     1.752   238     1.934   myrocks.zstd
125     1.000   123     1.000   inno5635
114     0.912   107     0.869   inno5717

i3.disk - 1t x 160m
pre     pre     post    post    engine
secs    ratio   secs    ratio
330     1.304   348     1.343   myrocks.none
432     1.707   451     1.741   myrocks.zstd
253     1.000   259     1.000   inno5635
257     1.015   261     1.007   inno5717

Charts

Below are charts from the large server tests for 8 tables & 100M rows/table and then 1 table with 800M rows.

Lesson 02: Installing MySQL

Notes/errata/updates for Chapter 2:
See the official book errata at http://tahaghoghi.com/LearningMySQL/errata.php – Chapter 2 includes pages 9-93 (but we skip pages 83-92). It seems like a lot of pages, however you will skip the operating systems that do not apply to you. Do NOT compile or install from tarball or source; just use the packages that are pre-made. You will want the latest version of MySQL, which at the time of this writing is MySQL 5.7.

You should install the latest version of MySQL, which can be downloaded from http://dev.mysql.com/downloads/mysql/ If you want to install something else, you can install MariaDB or Percona’s patched version.

Note that you do NOT need to install Apache, Perl or PHP. You can skip pages 83-92.

On p. 10, it says that “The MySQL Manual says that you can get a performance increase of up to 30 percent if you compile the code with the ideal settings for your environment.” This was true up through MySQL 5.1 (see http://dev.mysql.com/doc/refman/5.1/en/compile-and-link-options.html) However, with MySQL 5.5 and newer, this is no longer true.

On p. 62, it talks about checking to make sure the mysql user and group are on the machine by using NetInfo Manager. The NetInfo Manager was taken out of Mac OS X Leopard (10.7) and above. Just skip the paragraph starting “To check using the NetInfo Manager”, and proceed to the paragraph with “You can instead check these settings from the shell prompt.”

On p. 71, it talks about the MySQL Migration Toolkit and says it’s part of the “MySQL GUI Tools Bundle”. These days, it’s part of MySQL Workbench.

On p. 75 -78, the book talks about setting your path, which probably should be under “configuration” instead of the troubleshooting section…you might think once you get MySQL installed, you can skip that section, but you need to read it anyway.

On p. 93, it lists http://forge.mysql.com as a resource, but that website has been deprecated since the book was published.

Topics covered:
Installing MySQL on Linux, Mac OS X and Windows.

Verifying packages with MD5

Configuring a new server

Reference/Quick Links for MySQL Marinate

Upcoming Webinar Thursday, September 7: Using PMM to Troubleshoot MySQL Performance Issues

Join Percona’s Product Manager, Michael Coburn as he presents Using Percona Monitoring and Management to Troubleshoot MySQL Performance Issues on Thursday, September 7, 2017, at 10:00 am PDT / 1:00 pm EDT (UTC-7).

Reserve Your Spot

 

Successful applications often become limited by MySQL performance. Michael will show you how to get great MySQL performance using Percona Monitoring and Management (PMM). There will be a demonstration of how to leverage the combination of the query analytics and metrics monitor when troubleshooting MySQL performance issues. We’ll review the essential components of PMM, and use some of the most common database slowness cases as examples of where to look and what to do.

By the end of the webinar you will have a better understanding of:

  • Query metrics, including bytes sent, lock time, rows sent, and more
  • Metrics monitoring
  • How to identify MySQL performance issues
  • Point-in-time visibility and historical trending of database performance

Register for the webinar here.

Michael Coburn, Product Manager Michael joined Percona as a Consultant in 2012 after having worked with high volume stock photography websites and email service provider platforms. WIth a foundation in systems administration, Michael enjoys working with SAN technologies and high availability solutions. A Canadian, Michael currently lives in the Nicoya, Costa Rica area with his wife, two children, and two dogs.

MyRocks Experimental Now Available with Percona Server for MySQL 5.7.19-17

Percona, in collaboration with Facebook, is proud to announce the first experimental release of MyRocks in Percona Server for MySQL 5.7, with packages.

Back in October of 2016, Peter Zaitsev announced that we were going to port MyRocks from Facebook MySQL to Percona Server for MySQL.

Then in April 2017, Vadim Tkachenko announced the availability of experimental builds of Percona Server for MySQL with the MyRocks storage engine.

Now in September 2017, we are pleased to announce the first full experimental release of MyRocks with packages for Percona Server for MySQL 5.7.19-17.

The basis of the MyRocks storage engine is the RocksDB key-value store, which is a log-structured merge-tree (or LSM). It uses much less space, and has a much smaller write volume (write amplification) compared to a B+ tree database implementation such as InnoDB. As a result, MyRocks has the following advantages compared to other storage engines, if your workload uses fast storage (such as SSD):

  • Requires less storage space
  • Provides more storage endurance
  • Ensures better IO capacity

Percona MyRocks is distributed as a separate package that can be enabled as a plugin for Percona Server for MySQL 5.7.19-17.

WARNING: Percona MyRocks is currently considered experimental and is not yet recommended for production use.

We are providing packages for most popular 64-bit Linux distributions:

  • Debian 8 (“jessie”)
  • Debian 9 (“stretch”)
  • Ubuntu 14.04 LTS (Trusty Tahr)
  • Ubuntu 16.04 LTS (Xenial Xerus)
  • Ubuntu 16.10 (Yakkety Yak)
  • Ubuntu 17.04 (Zesty Zapus)
  • Red Hat Enterprise Linux or CentOS 6 (Santiago)
  • Red Hat Enterprise Linux or CentOS 7 (Maipo)

Installation instructions can be found here.

Due to the differences between Facebook MySQL 5.6.35 and Percona Server for MySQL 5.7, there are some behavioral differences and additional limitations. Some of these are documented here.

We encourage you to install and experiment with MyRocks for Percona Server for MySQL and join the discussion here.

Any issues that you might find can be searched for and reported here.

We thank the RocksDB and MyRocks development teams at Facebook for providing the foundation and assistance in developing MyRocks for Percona Server for MySQL. Without their efforts, this would not have been possible.

Timing load & index for sysbench tables

This post compares MyRocks, InnoDB and TokuDB on the time required to load and index a table for sysbench.

tl;dr
  • MyRocks, InnoDB and TokuDB have similar load performance although there is a regression for InnoDB from 5.6 to 5.7 to 8.x
  • InnoDB create index is much faster starting in 5.7

Configuration

I used my sysbench helper scripts with my sysbench branch and configured it to create 1 table with 800M rows. The binlog was enabled but sync on commit was disabled for the binlog and database log. The sysbench client shared the host with mysqld. The host has 48 HW threads, 50gb of RAM for the OS and MySQL and fast SSD. The test table is larger than RAM but it will take me a few days to get details on that. The test was repeated for MyRocks, InnoDB and TokuDB. I continue to use the IO-bound setup as described previously.
  • I compiled MyRocks on August 15 with git hash 0d76ae. The MyRocks tests were done without compression and with zstd compression. 
  • I used TokuDB from Percona Server 5.7.17. The TokuDB tests were done without compression and with zlib compression. I tried tokudb_disable_prefetching ON and OFF, but I have been setting this to ON for my OLTP benchmarks. 
  • I used InnoDB from upstream 5.6.35, 5.7.17, 8.0.1 and 8.0.2. For 8.x I used latin1/latin1_swedish_ci charset/collation. The performance_schema was enabled. The InnoDB tests did not use compression. 

A sample command line for sysbench is:
bash all.sh 1 800000000 180 300 180 innodb 1 0 /bin/mysql none /sysbench10 /dbdir
Results

The load is in PK order and there are no secondary indexes. Engines have similar performance although there is a slow regression for InnoDB with each new release and there is a big regression from 8.0.1 to 8.0.2 which I hope will be fixed when 8.x approaches GA. The ratio is the time to load for the engine divided by the time to load for InnoDB from MySQL 5.6.35.

load    load    engine
secs    ratio
 7266   1.000   inno5635
 7833   1.078   inno5717
 8286   1.140   inno801
10516   1.447   inno802
 7640   1.051   myrocks.none
 7810   1.074   myrocks.zstd
 7558   1.040   toku5717.none
 7494   1.031   toku5717.none.prefetch
 7726   1.063   toku5717.zlib 

Create index performance has more diversity. The table is larger than RAM, some of it will be read from storage and engines with compression (MyRocks.zstd, toku5717.zlib) suffer from decompression latency. The prefetch option doesn't help TokuDB in this case. InnoDB create index performance got much faster starting in 5.7. The ratio is the create index time for the engine divided by the time for InnoDB from MySQL 5.6.35.

create  create  engine
secs    ratio
 3565   1.000   inno5635
 1904   0.534   inno5717
 1961   0.550   inno801
 1966   0.551   inno802
 3321   0.931   myrocks.none
 3802   1.066   myrocks.zstd
 9817   2.753   toku5717.none
 9855   2.764   toku5717.none.prefetch
10731   3.010   toku5717.zlib

Charts

These charts have the same data as the tables above.

Write-heavy workloads with MyRocks

MyRocks is based on RocksDB and RocksDB is write-optimized, so why don't write-heavy workloads always run faster on MyRocks than on InnoDB? I will start with a summary of MyRocks versus InnoDB: MyRocks has better space & write efficiency, frequently has better write latency and sometimes has better read latency. Better space & write efficiency means you use less SSD and it will last longer.

Don't forget that better write and space efficiency with MyRocks can lead to better read latency. When a database engine does fewer writes to storage there is more IO capacity available for reads which is a big deal with disk and can be a big deal with slower SSD. When a database engine uses less space in storage then it is likely to cache more data in RAM and have better cache hit rates on reads. Many of the benchmarks that I run use uniform distribution for key generation and won't show the benefit from better cache hit rates.

With RocksDB write usually means a call to Put and Put is a blind-write. As shown by many benchmark results, a blind write can be very fast with RocksDB - insert data into memtable, optionally flush the WAL to the OS page cache, optionally force the WAL to persistent storage. But SQL update and insert statements usually need much more than a blind-write and the reads done by MyRocks can explain why some write-heavy workloads are faster with InnoDB. Things that get in the way include:
  • Pluggable storage engine APIs are slow to adopt blind-write optimizations
  • The modified row count must be returned from an update statement 
  • Enforcement of the PK and unique constraints
  • Secondary index maintenance
Pluggable storage engine APIs are slow to adopt blind-write optimizations. I don't blame MySQL and MongoDB for this because such optimizations are limited to write-optimized engines. But MyRocks and MongoRocks are here and I expect that WiredTiger/MongoDB will eventually have an LSM in production. M*Rocks engines can use the merge operator for this. I don't expect blind-write optimizations to ever be implemented for an update-in-place b-tree.

The modified row count must be returned for an update statement and that requires evaluation of the where clause. For RocksDB this requires reads -- from the memtable, maybe from the LSM tree, OS page cache and storage. Reads from storage and the OS page cache might require decompression. This is a lot more work than a blind-write. The usage of blind-write optimizations will result in statements that are update-like and insert-like because the application programmers must be aware of the semantic differences -- modified row count won't be returned, constraint violations won't be acknowledged. I think it is worth doing.

Unique constraints must be enforced for the PK and unique secondary indexes. With the exception of an auto-increment column, this requires a read from the index to confirm the value does not exist. In the best case updates & inserts are in key order and the structures to be searched are cached in RAM -- otherwise this requires reads from the OS page cache and/or storage and might require decompression.

Secondary indexes must be maintained as part of the update & insert statement processing. For an insert this means that a new index entry will be inserted. For an update that requires index maintenance the existing index entry will be deleted and a new one will be inserted. With a b-tree the leaf page(s) will be read (possibly from storage), made dirty in the buffer pool and eventually those pages will be written back to storage. With RocksDB non-unique secondary index maintenance is read-free and blind-writes are done to put the new index entry and possibly delete-mark the old index entry. See the previous paragraph if there is a unique secondary index.

On Open Source Databases. Interview with Peter Zaitsev

“To be competitive with non-open-source cloud deployment options, open source databases need to invest in “ease-of-use.” There is no tolerance for complexity in many development teams as we move to “ops-less” deployment models.” –Peter Zaitsev

I have interviewed Peter Zaitsev, Co-Founder and CEO of Percona.
In this interview, Peter talks about the Open Source Databases market; the Cloud; the scalability challenges at Facebook; compares MySQL, MariaDB, and MongoDB; and presents Percona’s contribution to the MySQL and MongoDB ecosystems.

RVZ

Q1. What are the main technical challenges in obtaining application scaling?

Peter Zaitsev: When it comes to scaling, there are different types. There is a Facebook/Google/Alibaba/Amazon scale: these giants are pushing boundaries, and usually are solving very complicated engineering problems at a scale where solutions aren’t easy or known. This often means finding edge cases that break things like hardware, operating system kernels and the database. As such, these companies not only need to build a very large-scale infrastructures, with a high level of automation, but also ensure it is robust enough to handle these kinds of issues with limited user impact. A great deal of hardware and software deployment practices must to be in place for such installations.

While these “extreme-scale” applications are very interesting and get a lot of publicity at tech events and in tech publications, this is a very small portion of all the scenarios out there. The vast majority of applications are running at the medium to high scale, where implementing best practices gets you the scalability you need.

When it comes to MySQL, perhaps the most important question is when you need to “shard.” Sharding — while used by every application at extreme scale — isn’t a simple “out-of-the-box” feature in MySQL. It often requires a lot of engineering effort to correctly implement it.

While sharding is sometimes required, you should really examine whether it is necessary for your application. A single MySQL instance can easily handle hundreds of thousands per second (or more) of moderately complicated queries, and Terabytes of data. Pair that with MemcacheD or Redis caching, MySQL Replication or more advanced solutions such as Percona XtraDB Cluster or Amazon Aurora, and you can cover the transactional (operational) database needs for applications of a very significant scale.

Besides making such high-level architecture choices, you of course need to also ensure that you exercise basic database hygiene. Ensure that you’re using the correct hardware (or cloud instance type), the right MySQL and operating system version and configuration, have a well-designed schema and good indexes. You also want to ensure good capacity planning, so that when you want to take your system to the next scale and begin to thoroughly look at it you’re not caught by surprise.

Q2. Why did Facebook create MyRocks, a new flash-optimized transactional storage engine on top of RocksDB storage engine for MySQL?

Peter Zaitsev: The Facebook Team is the most qualified to answer this question. However, I imagine that at Facebook scale being efficient is very important because it helps to drive the costs down. If your hot data is in the cache when it is important, your database is efficient at handling writes — thus you want a “write-optimized engine.”
If you use Flash storage, you also care about two things:

      – A high level of compression since Flash storage is much more expensive than spinning disk.

– You are also interested in writing as little to the storage as possible, as the more you write the faster it wears out (and needs to be replaced).

RocksDB and MyRocks are able to achieve all of these goals. As an LSM-based storage engine, writes (especially Inserts) are very fast — even for giant data sizes. They’re also much better suited for achieving high levels of compression than InnoDB.

This Blog Post by Mark Callaghan has many interesting details, including this table which shows MyRocks having better performance, write amplification and compression for Facebook’s workload than InnoDB.

Q3. Beringei is Facebook’s open source, in-memory time series database. According to Facebook, large-scale monitoring systems cannot handle large-scale analysis in real time because the query performance is too slow. What is your take on this?

Peter Zaitsev: Facebook operates at extreme scale, so it is no surprise the conventional systems don’t scale well enough or aren’t efficient enough for Facebook’s needs.

I’m very excited Facebook has released Beringei as open source. Beringei itself is a relatively low-end storage engine that is hard to use for a majority of users, but I hope it gets integrated with other open source projects and provides a full-blown high-performance monitoring solution. Integrating it with Prometheus would be a great fit for solutions with extreme data ingestion rates and very high metric cardinality.

Q4. How do you see the market for open source databases evolving?

Peter Zaitsev: The last decade has seen a lot of open source database engines built, offering a lot of different data models, persistence options, high availability options, etc. Some of them were build as open source from scratch, while others were released as open source after years of being proprietary engines — with the most recent example being CMDB2 by Bloomberg. I think this heavy competition is great for pushing innovation forward, and is very exciting! For example, I think if that if MongoDB hadn’t shown how many developers love a document-oriented data model, we might never of seen MySQL Document Store in the MySQL ecosystem.

With all this variety, I think there will be a lot of consolidation and only a small fraction of these new technologies really getting wide adoption. Many will either have niche deployments, or will be an idea breeding ground that gets incorporated into more popular database technologies.

I do not think SQL will “die” anytime soon, even though it is many decades old. But I also don’t think we will see it being the dominant “database” language, as it has been since the turn of millennia.

The interesting disruptive force for open source technologies is the cloud. It will be very interesting for me to see how things evolve. With pay-for-use models of the cloud, the “free” (as in beer) part of open source does not apply in the same way. This reduces incentives to move to open source databases.

To be competitive with non-open-source cloud deployment options, open source databases need to invest in “ease-of-use.” There is no tolerance for complexity in many development teams as we move to “ops-less” deployment models.

Q5. In your opinion what are the pros and cons of MySQL vs. MariaDB?

Peter Zaitsev: While tracing it roots to MySQL, MariaDB is quickly becoming a very different database.
It implements some features MySQL doesn’t, but also leaves out others (MySQL Document Store and Group Replication) or implements them in a different way (JSON support and Replication GTIDs).

From the MySQL side, we have Oracle’s financial backing and engineering. You might dislike Oracle, but I think you agree they know a thing or two about database engineering. MySQL is also far more popular, and as such more battle-tested than MariaDB.

MySQL is developed by a single company (Oracle) and does not have as many external contributors compared to MariaDB — which has its own pluses and minuses.

MySQL is “open core,” meaning some components are available only in the proprietary version, such as Enterprise Authentication, Enterprise Scalability, and others. Alternatives for a number of these features are available in Percona Server for MySQL though (which is completely open source). MariaDB Server itself is completely open source, through there are other components that aren’t that you might need to build a full solution — namely MaxScale.

Another thing MariaDB has going for it is that it is included in a number of Linux distributions. Many new users will be getting their first “MySQL” experience with MariaDB.

For additional insight into MariaDB, MySQL and Percona Server for MySQL, you can check out this recent article

Q6. What’s new in the MySQL and MongoDB ecosystem?

Peter Zaitsev: This could be its own and rather large article! With MySQL, we’re very excited to see what is coming in MySQL 8. There should be a lot of great changes in pretty much every area, ranging from the optimizer to retiring a lot of architectural debt (some of it 20 years old). MySQL Group Replication and MySQL InnoDB Cluster, while still early in their maturity, are very interesting products.

For MongoDB we’re very excited about MongoDB 3.4, which has been taking steps to be a more enterprise ready database with features like collation support and high-performance sharding. A number of these features are only available in the Enterprise version of MongoDB, such as external authentication, auditing and log redaction. This is where Percona Server for MongoDB 3.4 comes in handy, by providing open source alternatives for the most valuable Enterprise-only features.

For both MySQL and MongoDB, we’re very excited about RocksDB-based storage engines. MyRocks and MongoRocks both offer outstanding performance and efficiency for certain workloads.

Q7. Anything else you wish to add?

Peter Zaitsev: I would like to use this opportunity to highlight Percona’s contribution to the MySQL and MongoDB ecosystems by mentioning two of our open source products that I’m very excited about.

First, Percona XtraDB Cluster 5.7.
While this has been around for about a year, we just completed a major performance improvement effort that allowed us to increase performance up to 10x. I’m not talking about improving some very exotic workloads: these performance improvements are achieved in very typical high-concurrency environments!

I’m also very excited about our Percona Monitoring and Management product, which is unique in being the only fully packaged open source monitoring solution specifically built for MySQL and MongoDB. It is a newer product that has been available for less than a year, but we’re seeing great momentum in adoption in the community. We are focusing many of our resources to improving it and making it more effective.

———————

Peter Zaitsev co-founded Percona and assumed the role of CEO in 2006. As one of the foremost experts on MySQL strategy and optimization, Peter leveraged both his technical vision and entrepreneurial skills to grow Percona from a two-person shop to one of the most respected open source companies in the business. With more than 150 professionals in 29 countries, Peter’s venture now serves over 3000 customers – including the “who’s who” of Internet giants, large enterprises and many exciting startups. Percona was named to the Inc. 5000 in 2013, 2014, 2015 and 2016.

Peter was an early employee at MySQL AB, eventually leading the company’s High Performance Group. A serial entrepreneur, Peter co-founded his first startup while attending Moscow State University where he majored in Computer Science. Peter is a co-author of High Performance MySQL: Optimization, Backups, and Replication, one of the most popular books on MySQL performance. Peter frequently speaks as an expert lecturer at MySQL and related conferences, and regularly posts on the Percona Data Performance Blog. He has also been tapped as a contributor to Fortune and DZone, and his recent ebook Practical MySQL Performance Optimization Volume 1 is one of percona.com’s most popular downloads.
————————-

Resources

Percona, in collaboration with Facebook, announced the first experimental release of MyRocks in Percona Server for MySQL 5.7, with packages. September 6, 2017

eBook, “Practical MySQL Performance Optimization,” by Percona CEO Peter Zaitsev and Principal Consultant Alexander Rubin. (LINK to DOWNLOAD, registration required)

MySQL vs MongoDB – When to Use Which Technology. Peter Zaitsev, June 22, 2017

Percona Live Open Source Database Conference Europe, Dublin, Ireland. September 25 – 27, 2017

Percona Monitoring and Management (PMM) Graphs Explained: MongoDB with RocksDB, By Tim Vaillancourt,JUNE 18, 2017

Related Posts

On Apache Ignite, Apache Spark and MySQL. Interview with Nikita Ivanov. ODBMS Industry Watch, 2017-06-30

On the new developments in Apache Spark and Hadoop. Interview with Amr Awadallah. ODBMS Industry Watch,2017-03-13

On in-memory, key-value data stores. Ofer Bengal and Yiftach Shoolman. ODBMS Industry Watch, 2017-02-13

follow us on Twitter: @odbmsorg

##

Automatic Partition Maintenance in MariaDB

Automatic Partition Maintenance in MariaDB geoff_montee_g Tue, 09/05/2017 - 14:30

A MariaDB Support customer recently asked how they could automatically drop old partitions after 6 months. MariaDB does not have a mechanism to do this automatically out-of-the-box, but it is not too difficult to create a custom stored procedure and an event to call the procedure on the desired schedule. In fact, it is also possible to go even further and create a stored procedure that can also automatically add new partitions. In this blog post, I will show how to write stored procedures that perform these tasks.

Partitioned table definition

For this demonstration, I'll use a table definition based on one from MySQL's documentation on range partitioning, with some minor changes:

DROP TABLE IF EXISTS db1.quarterly_report_status; CREATE TABLE db1.quarterly_report_status ( report_id INT NOT NULL, report_status VARCHAR(20) NOT NULL, report_updated TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ) ENGINE=InnoDB PARTITION BY RANGE ( UNIX_TIMESTAMP(report_updated) ) ( PARTITION p_first VALUES LESS THAN ( UNIX_TIMESTAMP('2016-10-01 00:00:00')), PARTITION p201610 VALUES LESS THAN ( UNIX_TIMESTAMP('2016-11-01 00:00:00')), PARTITION p201611 VALUES LESS THAN ( UNIX_TIMESTAMP('2016-12-01 00:00:00')), PARTITION p201612 VALUES LESS THAN ( UNIX_TIMESTAMP('2017-01-01 00:00:00')), PARTITION p201701 VALUES LESS THAN ( UNIX_TIMESTAMP('2017-02-01 00:00:00')), PARTITION p201702 VALUES LESS THAN ( UNIX_TIMESTAMP('2017-03-01 00:00:00')), PARTITION p201703 VALUES LESS THAN ( UNIX_TIMESTAMP('2017-04-01 00:00:00')), PARTITION p201704 VALUES LESS THAN ( UNIX_TIMESTAMP('2017-05-01 00:00:00')), PARTITION p201705 VALUES LESS THAN ( UNIX_TIMESTAMP('2017-06-01 00:00:00')), PARTITION p201706 VALUES LESS THAN ( UNIX_TIMESTAMP('2017-07-01 00:00:00')), PARTITION p201707 VALUES LESS THAN ( UNIX_TIMESTAMP('2017-08-01 00:00:00')), PARTITION p201708 VALUES LESS THAN ( UNIX_TIMESTAMP('2017-09-01 00:00:00')), PARTITION p_future VALUES LESS THAN (MAXVALUE) );

The most significant change is that the partition naming scheme is based on the date. This will allow us to more easily determine which partitions to remove.

Stored procedure definition (create new partitions)

The stored procedure itself contains some comments that explain what it does, so I will let the code speak for itself, for the most part. One noteworthy item to point out is that we are not doing ALTER TABLE ... ADD PARTITION. This is because the partition p_future already covers the end range up to MAXVALUE, so we actually need to do ALTER TABLE ... REORGANIZE PARTITION instead.

DROP PROCEDURE IF EXISTS db1.create_new_partitions; DELIMITER $$ CREATE PROCEDURE db1.create_new_partitions(p_schema varchar(64), p_table varchar(64), p_months_to_add int) LANGUAGE SQL NOT DETERMINISTIC SQL SECURITY INVOKER BEGIN DECLARE done INT DEFAULT FALSE; DECLARE current_partition_name varchar(64); DECLARE current_partition_ts int; -- We'll use this cursor later to check -- whether a particular already exists. -- @partition_name_to_add will be -- set later. DECLARE cur1 CURSOR FOR SELECT partition_name FROM information_schema.partitions WHERE TABLE_SCHEMA = p_schema AND TABLE_NAME = p_table AND PARTITION_NAME != 'p_first' AND PARTITION_NAME != 'p_future' AND PARTITION_NAME = @partition_name_to_add; -- We'll also use this cursor later -- to query our temporary table. DECLARE cur2 CURSOR FOR SELECT partition_name, partition_range_ts FROM partitions_to_add; DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE; DROP TEMPORARY TABLE IF EXISTS partitions_to_add; CREATE TEMPORARY TABLE partitions_to_add ( partition_name varchar(64), partition_range_ts int ); SET @partitions_added = FALSE; SET @months_ahead = 0; -- Let's go through a loop and add each month individually between -- the current month and the month p_months_to_add in the future. WHILE @months_ahead

Let's try running the new procedure:

MariaDB [db1]> SHOW CREATE TABLE db1.quarterly_report_status\G *************************** 1. row *************************** Table: quarterly_report_status Create Table: CREATE TABLE `quarterly_report_status` ( `report_id` int(11) NOT NULL, `report_status` varchar(20) NOT NULL, `report_updated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ) ENGINE=InnoDB DEFAULT CHARSET=latin1 /*!50100 PARTITION BY RANGE ( UNIX_TIMESTAMP(report_updated)) (PARTITION p_first VALUES LESS THAN (1475294400) ENGINE = InnoDB, PARTITION p201610 VALUES LESS THAN (1477972800) ENGINE = InnoDB, PARTITION p201611 VALUES LESS THAN (1480568400) ENGINE = InnoDB, PARTITION p201612 VALUES LESS THAN (1483246800) ENGINE = InnoDB, PARTITION p201701 VALUES LESS THAN (1485925200) ENGINE = InnoDB, PARTITION p201702 VALUES LESS THAN (1488344400) ENGINE = InnoDB, PARTITION p201703 VALUES LESS THAN (1491019200) ENGINE = InnoDB, PARTITION p201704 VALUES LESS THAN (1493611200) ENGINE = InnoDB, PARTITION p201705 VALUES LESS THAN (1496289600) ENGINE = InnoDB, PARTITION p201706 VALUES LESS THAN (1498881600) ENGINE = InnoDB, PARTITION p201707 VALUES LESS THAN (1501560000) ENGINE = InnoDB, PARTITION p201708 VALUES LESS THAN (1504238400) ENGINE = InnoDB, PARTITION p_future VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */ 1 row in set (0.00 sec) MariaDB [db1]> CALL db1.create_new_partitions('db1', 'quarterly_report_status', 3); +--------------------------------------------------------+ | CONCAT('Creating partition: ', @partition_name_to_add) | +--------------------------------------------------------+ | Creating partition: p201709 | +--------------------------------------------------------+ 1 row in set (0.01 sec) +--------------------------------------------------------+ | CONCAT('Creating partition: ', @partition_name_to_add) | +--------------------------------------------------------+ | Creating partition: p201710 | +--------------------------------------------------------+ 1 row in set (0.02 sec) +--------------------------------------------------------+ | CONCAT('Creating partition: ', @partition_name_to_add) | +--------------------------------------------------------+ | Creating partition: p201711 | +--------------------------------------------------------+ 1 row in set (0.02 sec) Query OK, 0 rows affected (0.09 sec) MariaDB [db1]> SHOW CREATE TABLE db1.quarterly_report_status\G *************************** 1. row *************************** Table: quarterly_report_status Create Table: CREATE TABLE `quarterly_report_status` ( `report_id` int(11) NOT NULL, `report_status` varchar(20) NOT NULL, `report_updated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ) ENGINE=InnoDB DEFAULT CHARSET=latin1 /*!50100 PARTITION BY RANGE ( UNIX_TIMESTAMP(report_updated)) (PARTITION p_first VALUES LESS THAN (1475294400) ENGINE = InnoDB, PARTITION p201610 VALUES LESS THAN (1477972800) ENGINE = InnoDB, PARTITION p201611 VALUES LESS THAN (1480568400) ENGINE = InnoDB, PARTITION p201612 VALUES LESS THAN (1483246800) ENGINE = InnoDB, PARTITION p201701 VALUES LESS THAN (1485925200) ENGINE = InnoDB, PARTITION p201702 VALUES LESS THAN (1488344400) ENGINE = InnoDB, PARTITION p201703 VALUES LESS THAN (1491019200) ENGINE = InnoDB, PARTITION p201704 VALUES LESS THAN (1493611200) ENGINE = InnoDB, PARTITION p201705 VALUES LESS THAN (1496289600) ENGINE = InnoDB, PARTITION p201706 VALUES LESS THAN (1498881600) ENGINE = InnoDB, PARTITION p201707 VALUES LESS THAN (1501560000) ENGINE = InnoDB, PARTITION p201708 VALUES LESS THAN (1504238400) ENGINE = InnoDB, PARTITION p201709 VALUES LESS THAN (1506830400) ENGINE = InnoDB, PARTITION p201710 VALUES LESS THAN (1509508800) ENGINE = InnoDB, PARTITION p201711 VALUES LESS THAN (1512104400) ENGINE = InnoDB, PARTITION p_future VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */ 1 row in set (0.00 sec)

We can see that it appears to be working as expected.

Stored procedure definition (drop old partitions)

This additional stored procedure also contains some comments that explain what it does, so I will let the code speak for itself, for the most part. One noteworthy item to point out is that the stored procedure drops all old partitions individually with ALTER TABLE ... DROP PARTITION, and then it increases the range of the p_first partition with ALTER TABLE ... REORGANIZE PARTITION, so that it fills in the gap left behind.

DROP PROCEDURE IF EXISTS db1.drop_old_partitions; DELIMITER $$ CREATE PROCEDURE db1.drop_old_partitions(p_schema varchar(64), p_table varchar(64), p_months_to_keep int, p_seconds_to_sleep int) LANGUAGE SQL NOT DETERMINISTIC SQL SECURITY INVOKER BEGIN DECLARE done INT DEFAULT FALSE; DECLARE current_partition_name varchar(64); -- We'll use this cursor later to get -- the list of partitions to drop. -- @last_partition_name_to_keep will be -- set later. DECLARE cur1 CURSOR FOR SELECT partition_name FROM information_schema.partitions WHERE TABLE_SCHEMA = p_schema AND TABLE_NAME = p_table AND PARTITION_NAME != 'p_first' AND PARTITION_NAME != 'p_future' AND PARTITION_NAME 0 THEN SELECT CONCAT('Sleeping for ', p_seconds_to_sleep, ' seconds'); SELECT SLEEP(p_seconds_to_sleep); END IF; SELECT CONCAT('Dropping partition: ', current_partition_name); -- First we build the ALTER TABLE query. SET @schema = p_schema; SET @table = p_table; SET @partition = current_partition_name; SET @q = 'SELECT CONCAT(''ALTER TABLE '', @schema, ''.'', @table, '' DROP PARTITION '', @partition) INTO @query'; PREPARE st FROM @q; EXECUTE st; DEALLOCATE PREPARE st; -- And then we prepare and execute the ALTER TABLE query. PREPARE st FROM @query; EXECUTE st; DEALLOCATE PREPARE st; SET @first = FALSE; END LOOP; CLOSE cur1; -- If no partitions were dropped, then we can also skip this. IF ! @first THEN -- Then we need to get the date of the new first partition. -- We need the date in UNIX timestamp format. SET @q = 'SELECT DATE_FORMAT(@last_month_to_keep, ''%Y-%m-01 00:00:00'') INTO @new_first_partition_date'; PREPARE st FROM @q; EXECUTE st; DEALLOCATE PREPARE st; SELECT UNIX_TIMESTAMP(@new_first_partition_date) INTO @new_first_partition_ts; -- We also need to get the date of the second partition -- since the second partition is also needed for REORGANIZE PARTITION. SET @q = 'SELECT DATE_ADD(@new_first_partition_date, INTERVAL 1 MONTH) INTO @second_partition_date'; PREPARE st FROM @q; EXECUTE st; DEALLOCATE PREPARE st; SELECT UNIX_TIMESTAMP(@second_partition_date) INTO @second_partition_ts; SELECT CONCAT('Reorganizing first and second partitions. first partition date = ', @new_first_partition_date, ', second partition date = ', @second_partition_date); -- Then we build the ALTER TABLE query. SET @schema = p_schema; SET @table = p_table; SET @q = 'SELECT CONCAT(''ALTER TABLE '', @schema, ''.'', @table, '' REORGANIZE PARTITION p_first, '', @last_partition_name_to_keep, '' INTO ( PARTITION p_first VALUES LESS THAN ( '', @new_first_partition_ts, '' ), PARTITION '', @last_partition_name_to_keep, '' VALUES LESS THAN ( '', @second_partition_ts, '' ) ) '') INTO @query'; PREPARE st FROM @q; EXECUTE st; DEALLOCATE PREPARE st; -- And then we prepare and execute the ALTER TABLE query. PREPARE st FROM @query; EXECUTE st; DEALLOCATE PREPARE st; END IF; END$$ DELIMITER ;

Let's try running the new procedure:

MariaDB [db1]> SHOW CREATE TABLE db1.quarterly_report_status\G *************************** 1. row *************************** Table: quarterly_report_status Create Table: CREATE TABLE `quarterly_report_status` ( `report_id` int(11) NOT NULL, `report_status` varchar(20) NOT NULL, `report_updated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ) ENGINE=InnoDB DEFAULT CHARSET=latin1 /*!50100 PARTITION BY RANGE ( UNIX_TIMESTAMP(report_updated)) (PARTITION p_first VALUES LESS THAN (1475294400) ENGINE = InnoDB, PARTITION p201610 VALUES LESS THAN (1477972800) ENGINE = InnoDB, PARTITION p201611 VALUES LESS THAN (1480568400) ENGINE = InnoDB, PARTITION p201612 VALUES LESS THAN (1483246800) ENGINE = InnoDB, PARTITION p201701 VALUES LESS THAN (1485925200) ENGINE = InnoDB, PARTITION p201702 VALUES LESS THAN (1488344400) ENGINE = InnoDB, PARTITION p201703 VALUES LESS THAN (1491019200) ENGINE = InnoDB, PARTITION p201704 VALUES LESS THAN (1493611200) ENGINE = InnoDB, PARTITION p201705 VALUES LESS THAN (1496289600) ENGINE = InnoDB, PARTITION p201706 VALUES LESS THAN (1498881600) ENGINE = InnoDB, PARTITION p201707 VALUES LESS THAN (1501560000) ENGINE = InnoDB, PARTITION p201708 VALUES LESS THAN (1504238400) ENGINE = InnoDB, PARTITION p_future VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */ 1 row in set (0.00 sec) MariaDB [db1]> CALL db1.drop_old_partitions('db1', 'quarterly_report_status', 6, 5); +--------------------------------------------------------------------------+ | CONCAT('Dropping all partitions before: ', @last_partition_name_to_keep) | +--------------------------------------------------------------------------+ | Dropping all partitions before: p201702 | +--------------------------------------------------------------------------+ 1 row in set (0.00 sec) +--------------------------------------------------------+ | CONCAT('Dropping partition: ', current_partition_name) | +--------------------------------------------------------+ | Dropping partition: p201610 | +--------------------------------------------------------+ 1 row in set (0.00 sec) +---------------------------------------------------------+ | CONCAT('Sleeping for ', p_seconds_to_sleep, ' seconds') | +---------------------------------------------------------+ | Sleeping for 5 seconds | +---------------------------------------------------------+ 1 row in set (0.02 sec) +---------------------------+ | SLEEP(p_seconds_to_sleep) | +---------------------------+ | 0 | +---------------------------+ 1 row in set (5.02 sec) +--------------------------------------------------------+ | CONCAT('Dropping partition: ', current_partition_name) | +--------------------------------------------------------+ | Dropping partition: p201611 | +--------------------------------------------------------+ 1 row in set (5.02 sec) +---------------------------------------------------------+ | CONCAT('Sleeping for ', p_seconds_to_sleep, ' seconds') | +---------------------------------------------------------+ | Sleeping for 5 seconds | +---------------------------------------------------------+ 1 row in set (5.03 sec) +---------------------------+ | SLEEP(p_seconds_to_sleep) | +---------------------------+ | 0 | +---------------------------+ 1 row in set (10.03 sec) +--------------------------------------------------------+ | CONCAT('Dropping partition: ', current_partition_name) | +--------------------------------------------------------+ | Dropping partition: p201612 | +--------------------------------------------------------+ 1 row in set (10.03 sec) +---------------------------------------------------------+ | CONCAT('Sleeping for ', p_seconds_to_sleep, ' seconds') | +---------------------------------------------------------+ | Sleeping for 5 seconds | +---------------------------------------------------------+ 1 row in set (10.05 sec) +---------------------------+ | SLEEP(p_seconds_to_sleep) | +---------------------------+ | 0 | +---------------------------+ 1 row in set (15.05 sec) +--------------------------------------------------------+ | CONCAT('Dropping partition: ', current_partition_name) | +--------------------------------------------------------+ | Dropping partition: p201701 | +--------------------------------------------------------+ 1 row in set (15.05 sec) +--------------------------------------------------------------------------------------------------------------------------------------------------------------+ | CONCAT('Reorganizing first and second partitions. first partition date = ', @new_first_partition_date, ', second partition date = ', @second_partition_date) | +--------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Reorganizing first and second partitions. first partition date = 2017-02-01 00:00:00, second partition date = 2017-03-01 00:00:00 | +--------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (15.06 sec) Query OK, 0 rows affected (15.11 sec) MariaDB [db1]> SHOW CREATE TABLE db1.quarterly_report_status\G *************************** 1. row *************************** Table: quarterly_report_status Create Table: CREATE TABLE `quarterly_report_status` ( `report_id` int(11) NOT NULL, `report_status` varchar(20) NOT NULL, `report_updated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ) ENGINE=InnoDB DEFAULT CHARSET=latin1 /*!50100 PARTITION BY RANGE ( UNIX_TIMESTAMP(report_updated)) (PARTITION p_first VALUES LESS THAN (1485925200) ENGINE = InnoDB, PARTITION p201702 VALUES LESS THAN (1488344400) ENGINE = InnoDB, PARTITION p201703 VALUES LESS THAN (1491019200) ENGINE = InnoDB, PARTITION p201704 VALUES LESS THAN (1493611200) ENGINE = InnoDB, PARTITION p201705 VALUES LESS THAN (1496289600) ENGINE = InnoDB, PARTITION p201706 VALUES LESS THAN (1498881600) ENGINE = InnoDB, PARTITION p201707 VALUES LESS THAN (1501560000) ENGINE = InnoDB, PARTITION p201708 VALUES LESS THAN (1504238400) ENGINE = InnoDB, PARTITION p_future VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */ 1 row in set (0.00 sec)

We can see that our changes seem to be working as expected. In addition to old partitions being dropped, we can also see that p_first's date range was updated.

Stored procedure definition (tie other procedures together)

It is probably going to be preferable in most cases to perform all partition maintenance at the same time. Therefore, we can create another stored procedure that calls our other two stored procedures. This is fairly straight forward.

DROP PROCEDURE IF EXISTS db1.perform_partition_maintenance; DELIMITER $$ CREATE PROCEDURE db1.perform_partition_maintenance(p_schema varchar(64), p_table varchar(64), p_months_to_add int, p_months_to_keep int, p_seconds_to_sleep int) LANGUAGE SQL NOT DETERMINISTIC SQL SECURITY INVOKER BEGIN CALL db1.drop_old_partitions(p_schema, p_table, p_months_to_keep, p_seconds_to_sleep); CALL db1.create_new_partitions(p_schema, p_table, p_months_to_add); END$$ DELIMITER ;

Let's reset our partitioned table to its original state, and then let's try running our new stored procedure.

MariaDB [db1]> SHOW CREATE TABLE db1.quarterly_report_status\G *************************** 1. row *************************** Table: quarterly_report_status Create Table: CREATE TABLE `quarterly_report_status` ( `report_id` int(11) NOT NULL, `report_status` varchar(20) NOT NULL, `report_updated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ) ENGINE=InnoDB DEFAULT CHARSET=latin1 /*!50100 PARTITION BY RANGE ( UNIX_TIMESTAMP(report_updated)) (PARTITION p_first VALUES LESS THAN (1475294400) ENGINE = InnoDB, PARTITION p201610 VALUES LESS THAN (1477972800) ENGINE = InnoDB, PARTITION p201611 VALUES LESS THAN (1480568400) ENGINE = InnoDB, PARTITION p201612 VALUES LESS THAN (1483246800) ENGINE = InnoDB, PARTITION p201701 VALUES LESS THAN (1485925200) ENGINE = InnoDB, PARTITION p201702 VALUES LESS THAN (1488344400) ENGINE = InnoDB, PARTITION p201703 VALUES LESS THAN (1491019200) ENGINE = InnoDB, PARTITION p201704 VALUES LESS THAN (1493611200) ENGINE = InnoDB, PARTITION p201705 VALUES LESS THAN (1496289600) ENGINE = InnoDB, PARTITION p201706 VALUES LESS THAN (1498881600) ENGINE = InnoDB, PARTITION p201707 VALUES LESS THAN (1501560000) ENGINE = InnoDB, PARTITION p201708 VALUES LESS THAN (1504238400) ENGINE = InnoDB, PARTITION p_future VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */ 1 row in set (0.00 sec) MariaDB [db1]> CALL db1.perform_partition_maintenance('db1', 'quarterly_report_status', 3, 6, 5); +--------------------------------------------------------------------------+ | CONCAT('Dropping all partitions before: ', @last_partition_name_to_keep) | +--------------------------------------------------------------------------+ | Dropping all partitions before: p201702 | +--------------------------------------------------------------------------+ 1 row in set (0.00 sec) +--------------------------------------------------------+ | CONCAT('Dropping partition: ', current_partition_name) | +--------------------------------------------------------+ | Dropping partition: p201610 | +--------------------------------------------------------+ 1 row in set (0.00 sec) +---------------------------------------------------------+ | CONCAT('Sleeping for ', p_seconds_to_sleep, ' seconds') | +---------------------------------------------------------+ | Sleeping for 5 seconds | +---------------------------------------------------------+ 1 row in set (0.02 sec) +---------------------------+ | SLEEP(p_seconds_to_sleep) | +---------------------------+ | 0 | +---------------------------+ 1 row in set (5.02 sec) +--------------------------------------------------------+ | CONCAT('Dropping partition: ', current_partition_name) | +--------------------------------------------------------+ | Dropping partition: p201611 | +--------------------------------------------------------+ 1 row in set (5.02 sec) +---------------------------------------------------------+ | CONCAT('Sleeping for ', p_seconds_to_sleep, ' seconds') | +---------------------------------------------------------+ | Sleeping for 5 seconds | +---------------------------------------------------------+ 1 row in set (5.03 sec) +---------------------------+ | SLEEP(p_seconds_to_sleep) | +---------------------------+ | 0 | +---------------------------+ 1 row in set (10.03 sec) +--------------------------------------------------------+ | CONCAT('Dropping partition: ', current_partition_name) | +--------------------------------------------------------+ | Dropping partition: p201612 | +--------------------------------------------------------+ 1 row in set (10.03 sec) +---------------------------------------------------------+ | CONCAT('Sleeping for ', p_seconds_to_sleep, ' seconds') | +---------------------------------------------------------+ | Sleeping for 5 seconds | +---------------------------------------------------------+ 1 row in set (10.06 sec) +---------------------------+ | SLEEP(p_seconds_to_sleep) | +---------------------------+ | 0 | +---------------------------+ 1 row in set (15.06 sec) +--------------------------------------------------------+ | CONCAT('Dropping partition: ', current_partition_name) | +--------------------------------------------------------+ | Dropping partition: p201701 | +--------------------------------------------------------+ 1 row in set (15.06 sec) +--------------------------------------------------------------------------------------------------------------------------------------------------------------+ | CONCAT('Reorganizing first and second partitions. first partition date = ', @new_first_partition_date, ', second partition date = ', @second_partition_date) | +--------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Reorganizing first and second partitions. first partition date = 2017-02-01 00:00:00, second partition date = 2017-03-01 00:00:00 | +--------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (15.08 sec) +--------------------------------------------------------+ | CONCAT('Creating partition: ', @partition_name_to_add) | +--------------------------------------------------------+ | Creating partition: p201709 | +--------------------------------------------------------+ 1 row in set (15.16 sec) +--------------------------------------------------------+ | CONCAT('Creating partition: ', @partition_name_to_add) | +--------------------------------------------------------+ | Creating partition: p201710 | +--------------------------------------------------------+ 1 row in set (15.17 sec) +--------------------------------------------------------+ | CONCAT('Creating partition: ', @partition_name_to_add) | +--------------------------------------------------------+ | Creating partition: p201711 | +--------------------------------------------------------+ 1 row in set (15.17 sec) Query OK, 0 rows affected (15.26 sec) MariaDB [db1]> SHOW CREATE TABLE db1.quarterly_report_status\G *************************** 1. row *************************** Table: quarterly_report_status Create Table: CREATE TABLE `quarterly_report_status` ( `report_id` int(11) NOT NULL, `report_status` varchar(20) NOT NULL, `report_updated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ) ENGINE=InnoDB DEFAULT CHARSET=latin1 /*!50100 PARTITION BY RANGE ( UNIX_TIMESTAMP(report_updated)) (PARTITION p_first VALUES LESS THAN (1485925200) ENGINE = InnoDB, PARTITION p201702 VALUES LESS THAN (1488344400) ENGINE = InnoDB, PARTITION p201703 VALUES LESS THAN (1491019200) ENGINE = InnoDB, PARTITION p201704 VALUES LESS THAN (1493611200) ENGINE = InnoDB, PARTITION p201705 VALUES LESS THAN (1496289600) ENGINE = InnoDB, PARTITION p201706 VALUES LESS THAN (1498881600) ENGINE = InnoDB, PARTITION p201707 VALUES LESS THAN (1501560000) ENGINE = InnoDB, PARTITION p201708 VALUES LESS THAN (1504238400) ENGINE = InnoDB, PARTITION p201709 VALUES LESS THAN (1506830400) ENGINE = InnoDB, PARTITION p201710 VALUES LESS THAN (1509508800) ENGINE = InnoDB, PARTITION p201711 VALUES LESS THAN (1512104400) ENGINE = InnoDB, PARTITION p_future VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */ 1 row in set (0.00 sec)

This stored procedure also seems to be working as expected.

Running the procedure more often than necessary

It should be noted that these stored procedures can be run more often than is necessary. If the procedures are run when no partitions need to be added or deleted, then the procedure will not perform any work. Let's reset our table definition and try it out.

MariaDB [db1]> CALL db1.perform_partition_maintenance('db1', 'quarterly_report_status', 3, 6, 5); +--------------------------------------------------------------------------+ | CONCAT('Dropping all partitions before: ', @last_partition_name_to_keep) | +--------------------------------------------------------------------------+ | Dropping all partitions before: p201702 | +--------------------------------------------------------------------------+ 1 row in set (0.00 sec) +--------------------------------------------------------+ | CONCAT('Dropping partition: ', current_partition_name) | +--------------------------------------------------------+ | Dropping partition: p201610 | +--------------------------------------------------------+ 1 row in set (0.00 sec) +---------------------------------------------------------+ | CONCAT('Sleeping for ', p_seconds_to_sleep, ' seconds') | +---------------------------------------------------------+ | Sleeping for 5 seconds | +---------------------------------------------------------+ 1 row in set (0.03 sec) +---------------------------+ | SLEEP(p_seconds_to_sleep) | +---------------------------+ | 0 | +---------------------------+ 1 row in set (5.03 sec) +--------------------------------------------------------+ | CONCAT('Dropping partition: ', current_partition_name) | +--------------------------------------------------------+ | Dropping partition: p201611 | +--------------------------------------------------------+ 1 row in set (5.03 sec) +---------------------------------------------------------+ | CONCAT('Sleeping for ', p_seconds_to_sleep, ' seconds') | +---------------------------------------------------------+ | Sleeping for 5 seconds | +---------------------------------------------------------+ 1 row in set (5.06 sec) +---------------------------+ | SLEEP(p_seconds_to_sleep) | +---------------------------+ | 0 | +---------------------------+ 1 row in set (10.06 sec) +--------------------------------------------------------+ | CONCAT('Dropping partition: ', current_partition_name) | +--------------------------------------------------------+ | Dropping partition: p201612 | +--------------------------------------------------------+ 1 row in set (10.06 sec) +---------------------------------------------------------+ | CONCAT('Sleeping for ', p_seconds_to_sleep, ' seconds') | +---------------------------------------------------------+ | Sleeping for 5 seconds | +---------------------------------------------------------+ 1 row in set (10.08 sec) +---------------------------+ | SLEEP(p_seconds_to_sleep) | +---------------------------+ | 0 | +---------------------------+ 1 row in set (15.09 sec) +--------------------------------------------------------+ | CONCAT('Dropping partition: ', current_partition_name) | +--------------------------------------------------------+ | Dropping partition: p201701 | +--------------------------------------------------------+ 1 row in set (15.09 sec) +--------------------------------------------------------------------------------------------------------------------------------------------------------------+ | CONCAT('Reorganizing first and second partitions. first partition date = ', @new_first_partition_date, ', second partition date = ', @second_partition_date) | +--------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Reorganizing first and second partitions. first partition date = 2017-02-01 00:00:00, second partition date = 2017-03-01 00:00:00 | +--------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (15.11 sec) +--------------------------------------------------------+ | CONCAT('Creating partition: ', @partition_name_to_add) | +--------------------------------------------------------+ | Creating partition: p201709 | +--------------------------------------------------------+ 1 row in set (15.18 sec) +--------------------------------------------------------+ | CONCAT('Creating partition: ', @partition_name_to_add) | +--------------------------------------------------------+ | Creating partition: p201710 | +--------------------------------------------------------+ 1 row in set (15.18 sec) +--------------------------------------------------------+ | CONCAT('Creating partition: ', @partition_name_to_add) | +--------------------------------------------------------+ | Creating partition: p201711 | +--------------------------------------------------------+ 1 row in set (15.18 sec) Query OK, 0 rows affected (15.28 sec) MariaDB [db1]> CALL db1.perform_partition_maintenance('db1', 'quarterly_report_status', 3, 6, 5); +--------------------------------------------------------------------------+ | CONCAT('Dropping all partitions before: ', @last_partition_name_to_keep) | +--------------------------------------------------------------------------+ | Dropping all partitions before: p201702 | +--------------------------------------------------------------------------+ 1 row in set (0.01 sec) Query OK, 0 rows affected (0.02 sec)

As we can see from the above output, the procedure did not perform any work the second time.

Event definition

We want our stored procedure to run automatically every month, so we can use an event to do that. Before testing the event, we need to do two things:

  • We need to recreate the table with the original definition, so that it has all of the original partitions.
  • We need to ensure that event_scheduler=ON is set, and if not, we need to set it.
MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE 'event_scheduler'; +-----------------+-------+ | Variable_name | Value | +-----------------+-------+ | event_scheduler | OFF | +-----------------+-------+ 1 row in set (0.00 sec) MariaDB [(none)]> SET GLOBAL event_scheduler=ON; Query OK, 0 rows affected (0.00 sec) MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE 'event_scheduler'; +-----------------+-------+ | Variable_name | Value | +-----------------+-------+ | event_scheduler | ON | +-----------------+-------+ 1 row in set (0.00 sec)

And then we can run the following:

DROP EVENT db1.monthly_perform_partition_maintenance_event; CREATE EVENT db1.monthly_perform_partition_maintenance_event ON SCHEDULE EVERY 1 MONTH STARTS NOW() DO CALL db1.perform_partition_maintenance('db1', 'quarterly_report_status', 3, 6, 5);

However, there's another great change that we can make here. It might not be ideal to only run the procedure once per month, because if the procedure fails for whatever reason, then it might not get another chance to run again until the next month. For that reason, it might be better to run the procedure more often, such as once per day. As mentioned above, the procedure will only do work when partition maintenance is actually necessary, so it should not cause any issues to execute the procedure more often.

If we wanted to run the procedure once per day, then the event definition would become:

DROP EVENT db1.monthly_perform_partition_maintenance_event; CREATE EVENT db1.monthly_perform_partition_maintenance_event ON SCHEDULE EVERY 1 DAY STARTS NOW() DO CALL db1.perform_partition_maintenance('db1', 'quarterly_report_status', 3, 6, 5); Conclusion

Thanks to the flexibility of stored procedures and events, it is relatively easy to automatically perform partition maintenance in MariaDB. Has anyone else implemented something like this?

A MariaDB Support customer recently asked how they could automatically drop old partitions after 6 months. MariaDB does not have a mechanism to do this automatically out-of-the-box, but it is not too difficult to create a custom stored procedure and an event to call the procedure on the desired schedule. In fact, it is also possible to go even further and create a stored procedure that can also automatically add new partitions. In this blog post, I will show how to write stored procedures that perform these tasks.

Shlomi Noach

Tue, 09/05/2017 - 13:49

Partition management via common_schema

A shameless plug to common_schema's `sql_range_partitions` view, which generates the correct `DROP` statements for purging old partitions, as well as `CREATE` statements for creating the next partitions: sql_range_partitions

Also see Ike Walker's blog post on this topic: http://mechanics.flite.com/blog/2016/03/28/simplifying-mysql-partition-management-using-common-schema/

Login or Register to post comments

Webinar Wednesday, September 6, 2017: Percona Roadmap and Software News Update – Q3 2017

Come and listen to Percona CEO Peter Zaitsev on Wednesday, September 6, 2017 at 10am PT / 1pm ET (UTC-7) discuss the Percona roadmap, as well as what’s new in Percona open source software.

Reserve Your Spot

 

During this webinar Peter will talk about newly released features in Percona software, show a few quick demos and share with you highlights from the Percona open source software roadmap. This discussion will cover Percona Server for MySQL and MongoDB, Percona XtraBackup, Percona Toolkit, Percona XtraDB Cluster and Percona Monitoring and Management.

Peter will also talk about new developments in Percona commercial services and finish with a Q&A.

Register for the webinar before seats fill up for this exciting webinar Wednesday, September 6, 2017 at 10am PT / 1pm ET (UTC-7).

Peter Zaitsev, Percona CEO and Co-Founder Peter Zaitsev co-founded Percona and assumed the role of CEO in 2006. As one of the foremost experts on MySQL strategy and optimization, Peter leveraged both his technical vision and entrepreneurial skills to grow Percona from a two-person shop to one of the most respected open source companies in the business. With over 140 professionals in 30+ countries, Peter’s venture now serves over 3000 customers – including the “who’s who” of internet giants, large enterprises and many exciting startups. Percona was named to the Inc. 5000 in 2013, 2014, 2015 and 2016. Peter was an early employee at MySQL AB, eventually leading the company’s High Performance Group. A serial entrepreneur, Peter co-founded his first startup while attending Moscow State University, where he majored in Computer Science. Peter is a co-author of High Performance MySQL: Optimization, Backups, and Replication, one of the most popular books on MySQL performance. Peter frequently speaks as an expert lecturer at MySQL and related conferences, and regularly posts on the Percona Database Performance Blog. Fortune and DZone have both tapped Peter as a contributor, and his recent ebook Practical MySQL Performance Optimization is one of percona.com’s most popular downloads.

In-memory sysbench and a small server

I repeated the in-memory sysbench test on a small server and the results are similar to what I measured on the large server.

tl;dr
  • MyRocks does worse than InnoDB and the difference is larger on read-heavy tests.
  • InnoDB QPS tends to decrease after 5.6.35
  • InnoDB range scans are more efficient after 5.6.35

Configuration

I used core i5 Intel NUC servers with an SSD, my sysbench helper scripts and sysbench branch. The test was run with 4 tables and 1M rows/table. Somewhat accurate my.cnf files are here. The test was run for MyRocks compiled on August 15 with git hash 0d76ae and InnoDB from upstream 5.6.35, 5.7.17 and 8.0.2. The binlog was enabled but sync on commit was disabled for the binlog and database log. InnoDB 8.0.2 used latin1 charset and latin1_swedish_ci collation to match earlier releases. The sysbench client and mysqld shared the host.

Results

All of the data is here. Below I share the QPS ratios to compare QPS for MyRocks and InnoDB 5.7.17 with InnoDB 5.6.35. The results are similar to the results from in-memory sysbench on a large server.

QPS ratio:
* rocks = QPS for MyRocks / QPS for InnoDB 5.6.35
* inno = QPS for InnoDB 5.7.17 / QPS for InnoDB 5.6.35

1 connection
rocks   inno
0.391   0.966   update-inlist
0.723   0.911   update-one
0.965   1.042   update-index
0.592   0.908   update-nonindex
0.651   0.915   update-nonindex-special
0.700   0.893   delete-only
0.628   1.122   read-write.range100
0.838   1.495   read-write.range10000
0.680   1.155   read-only.range100
0.826   1.438   read-only.range10000
0.655   0.882   point-query
0.247   0.966   random-points
0.287   0.944   hot-points
0.696   0.884   insert-only
-----   -----
0.634   1.037   average

Now live! MySQL on Docker: Understanding the Basics - The Webinar

We’re excited to announce the live version of our blog ‘MySQL on Docker: Understanding the Basics’, which will be presented by its author, our colleague Ashraf Sharif, on September 27th during this new webinar.

With 100K+ views to date, ‘MySQL on Docker: Understanding the Basics’ has become a popular go-to resource for MySQL users worldwide who are looking to get an initial understanding of and start experimenting with Docker.

So if you’re looking at taking your first steps with Docker for MySQL then this webinar is made for you :-)

Docker is quickly becoming mainstream as a method to package and deploy self-sufficient applications in primarily stateless Linux containers. This could be a challenge though for a stateful service like a database. As a database user you might be asking yourself: How do I best configure MySQL in a container environment? What can go wrong? Should I even run my databases in a container environment? How does performance compare with e.g. running on virtual machines or bare-metal servers? How do I manage replicated or clustered setups, where multiple containers need to be created, upgraded and made highly available? We’ll look at helping you answer these questions with our new Docker webinar series.

Check out the agenda and sign up for its first installment below, we look forward to “seeing” you there.

Date, Time & Registration Europe/MEA/APAC

Wednesday, September 27th at 09:00 BST / 10:00 CEST (Germany, France, Sweden)

Register Now

North America/LatAm

Wednesday, September 27th at 09:00 PST (US) / 12:00 EST (US)

Register Now

Agenda Related resources  MySQL on Docker: Understanding the Basics - The Blog  Severalnines on Docker Hub

This webinar is for MySQL users who are Docker beginners and who would like to understand the basics of running a MySQL container on Docker. We are going to cover:

  • Docker and its components
  • Concept and terminology
  • How a Docker container works
  • Advantages and disadvantages
  • Stateless vs stateful
  • Docker images for MySQL
  • Running a simple MySQL container
  • The ClusterControl Docker image
  • Severalnines on Docker Hub
Speaker

Ashraf Sharif is System Support Engineer at Severalnines. He was previously involved in hosting world and LAMP stack, where he worked as principal consultant and head of support team and delivered clustering solutions for large websites in the South East Asia region. His professional interests are on system scalability and high availability.

Tags:  docker MySQL containers Database

Backing up MySQL on Windows: options? I

Backing up MySQL on Windows: options?

I had a recurring consultation this summer with a client who is running a stand-alone MySQL database with a ton of data and wanted to simply have a backup system for that data. The client is very risk-averse and didn’t want anything too experimental. I knew for a huge database, I didn’t want to run mysqldump on the database on a regular basis if I could avoid it and always prefer using XtraBackup.

I haven’t worked on Windows in years, and had assumed there were binaries for XtraBackup, but as with so many things with Windows, there are a million assumptions I had made prior that I have learned the hard way throughout the summer.

I did find Vadim’s excellent article using a Docker container to run a container with XtraBackup, and bind mounts of both the datadir and the backup directory so the container can run a backup and have the backup show up on the host where specified in the arguments. This is certainly the way I gravitate to as I find containers a great solution to so many problems. However, again, I mentioned the client wanted something that isn’t perceived to be to experimental.

WSL: Windows Subsystem for Linux

Microsoft certainly is a different company than it used to be. Microsoft has really put a lot of work into being part of Open Source, VSCode, the container ecosystem, Kubernetes, Open-Sourcing PowerShell, etc. Also one can consider the context of Microsoft being serious about attracting developers to their platform. In addition to these other efforts, my friend James Hancock, an expert in all things Windows, told me about WSL, the Windows Subsystem for Linux.

I had always known that Windows had this idea of a base kernel with subsytems, both a Windows subsystem as well as a POSIX subsystem. I hadn’t used Windows much in years, so I didn’t know the status of what you could do with Windows in current times.

WSL was an effort inside of Microsoft researching real time translation of Linux system calls into Windows OS system calls, and derived from their work in working with Android apps to run on Windows 10 Mobile.

What is WSL really, in terms of how it runs and what one can do with it? WSL is not CygWin nor MinGW.

Those are great tools, but not a complete subsystem like WSL that integrates into the Windows OS

WSL is not a container nor VM running Linux

WSL is part of the OS, a subsystem just like Windows itself

WSL is currently like having Ubuntu on Windows

When you run WSL, you have an Ubuntu installation you can do all the same things for the most part you would if you
were running on a VM or bare metal. There consists a regular Linux file system with Windows fixed drives mounted as /mnt/<drive letter> so that you have access to you Windows files as well.

It’s also an excellent development platform as now you can develop both for Windows or Linux.

Using WSL

It’s part of the Windows OS, so it just needs to be set up to run.

Basically, it can be enabled through enabling developer mode and turning on the WSL feature in Settings->Update & Security, as detailed in this article

Or using a simple PowerShell command as detailed in this article

Enable-WindowsOptionalFeature -Online -FeatureName Microsoft-Windows-Subsystem-Linux

It can also be run as a specific user if desired

LxRun.exe /setdefaultuser <new_name> What does WSL mean for XtraBackup?

Since there aren’t any Windows binaries XtraBackup, one has to either use a Docker container or WSL.

For WSL, it is Ubuntu. So, to install XtraBackup for WSL, install XtraBackup as it would be installed on Ubuntu! Yes, that easy.

Setting up XtraBackup on WSL

The current Ubuntu version for WSL is xenial, so that is the version, using lsb-release to get the right package version.The lsb package will need to be installed to make it work.

apt-get update apt-get install lsb wget https://repo.percona.com/apt/percona-release_0.1-4.$(lsb_release -sc)_all.deb sudo dpkg -i percona-release_0.1-4.$(lsb_release -sc)_all.deb sudo apt-get update sudo apt-get install percona-xtrabackup-24

The MySQL version on the server was installed from Bitnami’s WAMP package which installs MySQL in C:\Bitnami\wampstack-7.1.7-0\mysql\data, but we modified the datadir to be in C:\mysqldata, which is accessible as /mnt/c/mysqldata in WSL.

The first thing is to decide where to back up data.

IMPORTANT: the current version of Windows, WSL cannot access USB drives, so you if data needs to be on a detachable USB drive, it’ll need to be copied from the attached storage to the USB drive. The current insider release does address this and soon this problem won’t exist on regular releases.

For a backup directory, In our case, we chose C:\backups for simplicity.

mkdir /mnt/c/backups

So, with a running MySQL instance and a place to create backups, XtraBackup can be run via innobackupex

innobackupex --datadir=/mnt/c/mysqldata /mnt/c/backups innobackupex --apply-log /mnt/c/backups/2017-09-xxx

And That’s it! Now you can have yet another way to back up MySQL on Windows!

![xtrabackup wsl](/assets/wsl4.

MySQL Cluster in Docker: quick step by step

I just wanted to share the ‘how to’ in getting to know MySQL Cluster a bit more, via Docker, as mentioned in the docker store install guide:

https://store.docker.com/community/images/mysql/mysql-cluster.

all I’m doing here is replicating what’s mentioned there. Hopefully someone might find it interesting, if considering wanting to get a MySQL Cluster up and running to see how it works.

For more info on MySQL in Docker, I’d highly recommend looking at this blog:

http://datacharmer.blogspot.com/2015/10/mysql-docker-operations-part-1-getting.html

(Thanks to Giuseppe here!)

So, what’s needed? Here we go.

Get the cluster image from docker:

$ docker pull mysql/mysql-cluster

Create the private network we’ll need:

$ docker network create cluster --subnet=192.168.0.0/16

Start the management node:

$ docker run -d --net=cluster --name=management1 --ip=192.168.0.2 mysql/mysql-cluster ndb_mgmd

Start the datanodes:

$ docker run -d --net=cluster --name=ndb1 --ip=192.168.0.3 mysql/mysql-cluster ndbd $ docker run -d --net=cluster --name=ndb2 --ip=192.168.0.4 mysql/mysql-cluster ndbd

And finally the MySQL server node:

$ docker run -d --net=cluster --name=mysql1 --ip=192.168.0.10 -e MYSQL_RANDOM_ROOT_PASSWORD=true mysql/mysql-cluster mysqld

Change the root password on the mysqld node:

$ docker logs mysql1 2>&1 | grep PASSWORD $ docker exec -it mysql1 mysql -uroot -p mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'MyNewPass';

And last but not least, see how the cluster is running from the management node:

$ docker run -it --net=cluster mysql/mysql-cluster ndb_mgm ndb_mgm> show

Obviously, now we can connect to the sqlnode and create a database using the NDB storage engine, and enter some rows in the tables and take it from there:

$ docker exec -it mysql1 mysql -uroot -p mysql> create database nexus; use nexus; mysql> create table replicant ... engine=NDBCLUSTER;

Hope this helps someone! Thanks for reading.


Running XtraBackup on Windows using WSL

Backing up MySQL on Windows: options?

I had a recurring consultation this summer with a client who is running a stand-alone MySQL database with a ton of data and wanted to simply have a backup system for that data. The client is very risk-averse and didn’t want anything too experimental. I knew for a huge database, I didn’t want to run mysqldump on the database on a regular basis if I could avoid it and always prefer using XtraBackup.

I haven’t worked on Windows in years, and had assumed there were binaries for XtraBackup, but as with so many things with Windows, there are a million assumptions I had made prior that I have learned the hard way throughout the summer.

I did find Vadim’s excellent article using a Docker container to run a container with XtraBackup, and bind mounts of both the datadir and the backup directory so the container can run a backup and have the backup show up on the host where specified in the arguments. This is certainly the way I gravitate to as I find containers a great solution to so many problems. However, again, I mentioned the client wanted something that isn’t perceived to be to experimental, so I wanted to explore using something that was “part” of Windows.

WSL: Windows Subsystem for Linux

Microsoft certainly is a different company than it used to be. Microsoft has really put a lot of work into being part of Open Source, VSCode, the container ecosystem, Kubernetes, Open-Sourcing PowerShell, etc. Also one can consider the context of Microsoft being serious about attracting developers to their platform. In addition to these other efforts, my friend James Hancock, an expert in all things Windows, told me about WSL, the Windows Subsystem for Linux.

I had always known that Windows had this idea of a base kernel with subsytems, both a Windows subsystem as well as a POSIX subsystem. I hadn’t used Windows much in years, so I didn’t know the status of what you could do with Windows in current times.

WSL was an effort inside of Microsoft researching real time translation of Linux system calls into Windows OS system calls, and derived from their work in working with Android apps to run on Windows 10 Mobile.

What is WSL really?

Throughout the years, there have been a number of ways to run “unix” or posix commands on Windows with some sort of bash shell for those who are Linux people to get by when working on Windows. WSL is much more than that though.

WSL is not CygWin nor MinGW.

Those are great tools, but not a complete subsystem like WSL that integrates into the Windows OS

WSL is not a container nor VM running Linux

WSL is part of the OS, a subsystem just like Windows itself and equal footing and not just an accessory program

WSL is currently like having Ubuntu on Windows

When you run WSL, you have an Ubuntu installation you can do all the same things for the most part you would if you were running on a VM or bare metal.

On WSL, there exists a regular Linux file system with Windows fixed drives mounted as /mnt/<drive letter> so that you have access to you Windows files as well.

It’s also an excellent development platform as now one can develop both for Windows or Linux.

Using WSL

It’s part of the Windows OS, so it just needs to be set up to run.

Basically, it can be enabled through enabling developer mode and turning on the WSL feature in Settings->Update & Security, as detailed in this article

Or using a simple PowerShell command as detailed in this article

Enable-WindowsOptionalFeature -Online -FeatureName Microsoft-Windows-Subsystem-Linux

It can also be run as a specific user if desired

LxRun.exe /setdefaultuser <new_name> What does WSL mean for XtraBackup?

Since there aren’t any Windows binaries XtraBackup, one has to either use a Docker container or WSL.

For WSL, it is Ubuntu. So, to install XtraBackup for WSL, install XtraBackup as it would be installed on Ubuntu! Yes, that easy.

Setting up XtraBackup on WSL

The current Ubuntu version for WSL is xenial, so that is the version, using lsb-release to get the right package version.The lsb package will need to be installed to make it work.

apt-get update apt-get install lsb wget https://repo.percona.com/apt/percona-release_0.1-4.$(lsb_release -sc)_all.deb sudo dpkg -i percona-release_0.1-4.$(lsb_release -sc)_all.deb sudo apt-get update sudo apt-get install percona-xtrabackup-24

The MySQL version on the server was installed from Bitnami’s WAMP package which installs MySQL in C:\Bitnami\wampstack-7.1.7-0\mysql\data, but we modified the datadir to be in C:\mysqldata, which is accessible as /mnt/c/mysqldata in WSL.

The first thing is to decide where to back up data.

IMPORTANT: the current version of Windows, WSL cannot access USB drives, so you if data needs to be on a detachable USB drive, it’ll need to be copied from the attached storage to the USB drive. The current insider release does address this and soon this problem won’t exist on regular releases.

For a backup directory, In our case, we chose C:\backups for simplicity.

mkdir /mnt/c/backups

So, with a running MySQL instance and a place to create backups, XtraBackup can be run via innobackupex

innobackupex --datadir=/mnt/c/mysqldata /mnt/c/backups innobackupex --apply-log /mnt/c/backups/2017-09-xxx

And That’s it! Now you can have yet another way to back up MySQL on Windows!

MySQL Support Engineer's Chronicles, Issue #8

This week is special and full of anniversaries for me. This week 5 years ago I left Oracle behind and joined Percona... Same week 5 years ago I had written something about MySQL in this blog for the first time in my life. 5 years ago I've created my Facebook account that I actively (ab)use for discussing work-related issues. So, to summarize, it's a five years anniversary of my coming out as a MySQL Entomologist, somebody who writes and speaks about MySQL and bugs in MySQL in public! These 5 years were mostly awesome.

I decided to celebrate with yet another post in this relatively new series and summarize in short what interesting things I studied, noticed or had to work on this week while providing support to customers of all kinds of MySQL.

This week started for me with the need to find out why mariabackup fails on Windows, for one of customers. If you missed it, MariaDB created a tool for online backup based on Percona's XtraBackup that supports few additional features (like data at rest encryption of MariaDB Server) and works on Windows as well, included it into MariaDB server and even declared it "Stable" as of MariaDB 10.1.26. In the process of working on that problem I had to use procmon tool, based on this KB article. The root cause of the problem was NTFS compression used for the target directory (see MDEV-13691 by Vladislav Vaintroub , who forces lazy me to improve my rudimentary Windows user skills from time to time, for some related details). So, better do not use NTFS compression of the backup destination if you need to back up big enough (50G+) tables. I really enjoyed working with procmon that helped to find out what could cause (somewhat random) "error 22" failures.

I was (positively) surprised to find out there there is a MariaDB KB article on such a specific topic as troubleshooting on Windows. Besides this one, I had to use the following KB articles while working with customers this week:
and found something new (for me) there. I never cared to find out what join_cache_level is used for previously, for example.

Besides mariabackup, this week I had to discuss various problems related to backing up TokuDB tables, so you should expect my blog posts related to this topic soon.

My colleague Geoff Montee published a useful post this week, "Automatically Dropping Old Partitions in MySQL and MariaDB". Make sure to check comments and his followup. Geoff had also reported nice Bug #87589 - "Documentation incorrectly states that LOAD DATA LOCAL INFILE does not use tmpdir", that is still "Open" for some reason.

During such a great week I had to report some MySQL bug, so I did it. Check Bug #87624 - "Wrong statement-based binary logging for CONCAT() in stored procedure". I've seen all these "Illegal mix of collations..." way too often over years.

Other bugs that attracted my attention were:
  • Bug #84108 - "Mysql 5.7.14 hangs and kills itself during drop database statement". This bug should probably become "Open" and properly processed, as current "Duplicate" status is questionable, at best. Arjen Lentz attracted mine (and not only mine) attention to this old enough and improperly handled bug report.
  • Bug #87619 - "InnoDB partition table will lock into the near record as a condition in the use". Nice to see this regression bug "Verified" after all. It seems native partitioning in MySQL 5.7 came with some cost of extra/different locking.
Time to stop writing and prepare for further celebrations, fun and anniversaries. Stay tuned!





In-memory sysbench, a larger server and contention - part 2

In this post I document performance problems in MyRocks, InnoDB and TokuDB using in-memory sysbench on a large server. I previously shared results for in-memory sysbench with less and more contention. In this post I explain the tests where QPS drops significantly when moving from a test with 8 tables to 1 table. In a future post I will repeat the analysis for IO-bound sysbench. Note that while I don't include InnoDB from MySQL 8.0.2 in this analysis, it is similar to 5.7.17.

Which engines lose QPS because of contention on the sysbench 1 table tests at high concurrency?
  • all engines lose QPS on the update-one test
  • InnoDB and TokuDB lose QPS on the random-points test. MyRocks does not.
  • all engines lose QPS on the hot-points test
  • InnoDB and TokuDB lose QPS on the insert-only test. MyRocks does not.
While I previously wrote that in-memory sysbench is the worst-case for MyRocks, it is interesting to find a few cases where MyRocks does better than InnoDB.
Configuration

I compare results from in-memory sysbench using 8 tables and 1 table. There is more contention for internal data structures and rows when sysbench uses 1 table rather than 8 for tests run with mid and high concurrency. I explained these tests in previous posts on sysbench with 8 tables and 1 table. I repeated tests using 1 to 64 clients on a server with 48 HW threads and I consider 32 or more clients to be high concurrency, 8 clients to be mid concurrency and 1 client to be low concurrency.

I run many (~10) sysbench tests (microbenchmarks) because modern sysbench makes that easy with Lua (thanks Alexey). Here I show tests where QPS at high concurrency suffers for tests with 1 table because with fewer tables there is more contention to internal data structures, database pages and rows. The tests for which contention is a problem are update-one, random-points, hot-points and insert-only. My usage of sysbench is explained here but I will briefly describe these tests:
  • update-one - all updates are to the same row in each table (the row with id=1). For the test with 1 table there is only one row that gets all updates which becomes a hot spot.
  • random-points - each query is a SELECT statement with an in-list that matches 100 rows by equality on the PK. The test used uniform distribution to generate the keys to find so there are no row hot spots, but there is a table hot spot when sysbench is run with one table.
  • hot-points - like random-points but this searches for the same 100 keys in every query. So this has a row hot spots.
  • insert-only - inserts are done in PK order. Secondary index maintenance is required and values for that column (k) are inserted in random order. There can be hot spots on the right-hand side of the PK index.

Guide to results

Below I share QPS for each test at low, mid and high concurrency where low is 1 connection, mid is 8 connections and high is 48 connections. The database is cached and sysbench shares the server with mysqld. There is no think time in the sysbench client when running a test, there are no stalls for reads from storage because all data can fit in the database cache and there are a few chances for stalls on writes.
For each test I list the QPS at 1, 8 and 48 connections twice - first for sysbench run with 8 tables and then for it run with 1 table. When using 8 tables there are 1M rows/table and with 1 table there is 8M rows in that table. I used MyRocks based on MySQL 5.6.35, InnoDB from upstream 5.6.35 and 5.7.17 and then TokuDB from Percona Server 5.7.17.

After the QPS results there is a section that lists QPS ratios where I highlight how QPS drops when moving from 8 tables to 1 table. When the QPS ratio is less than 1.0 there might be a performance problem.
update-one
For this test the QPS ratio section has the QPS for the engine at 1 table divided by the QPS for the engine at 8 tables. For this test all engines have a problem at mid and high concurrency as the QPS ratios are less than 0.5. Can I be happy that MyRocks suffers the least? This is a hard problem to fix because updates to one row must be serialized. For all tests the binlog was enabled and sync-on-commit was disabled for the binlog and database log. I hope that commutative updates are eventually supported in MyRocks to improve QPS for concurrent updates to a few rows.
QPS
1       8       48      concurrency/engine - 8 tables  8672   43342   39902   myrocks 10472   49717   52468   inno5635  9670   51181   62626   inno5717  2912   13736   19551   toku5717 - 1 table  9072   17348   13055   myrocks 10521   17092   13288   inno5635  9535   14411   13019   inno5717  2926    3254    3077   toku5717
QPS ratio rocks   inno56  inno57  toku 1.046   1.004   0.986   1.004   1 connection - low concurrency 0.400   0.343   0.281   0.236   8 connections - mid concurrency 0.327   0.253   0.207   0.157   48 connections - high concurrency
random-points
For this test the QPS ratio section has the QPS for the engine at 1 table divided by the QPS for the engine at 8 tables. For this test MyRocks does not have a problem for 1 table while InnoDB and TokuDB have a small problem at mid concurrency and a big problem at high concurrency. PMP output for TokuDB with 1 table & 48 connections is here and shows mutex contention. PMP output for InnoDB with 1 table & 48 connections is here and shows contention on rw-locks.
QPS 1       8       48      concurrency/engine - 8 tables  897     6871   23189   myrocks 2028    12693   16358   inno5635 1872    13925   47773   inno5717 1529    11824   36786   toku5717 - 1 table  972     7411   25003   myrocks 1910    10313   12239   inno5635 1764    11931   17690   inno5717 1400     8669    8401   toku5717
QPS ratio rocks   inno56  inno57  toku 1.083   0.941   0.942   0.915   1 connection - low concurrency 1.078   0.812   0.856   0.733   8 connections - mid concurrency 1.078   0.748   0.370   0.228   48 connections - high concurrency
hot-points
For this test the QPS ratio section is different than the above. The section here has two sets of numbers -- one for 8 tables and one for 1 table. The values are the QPS for the test divided by the QPS for the random-points test at 8 tables. When the value is less than one the engine gets less QPS than expected for this test.
For both 8 tables and 1 table all engines get less QPS on the hot-points test than on the random-points test. The loss is much greater for the 1 table test than the 8 table test. I filed issue 674 for MyRocks to make this better, but it really is an issue with RocksDB and mutex contention in the sharded LRU. PMP output for TokuDB with 1 table and 48 connections is here and it looks like the same problem as for random-points. PMP output for InnoDB with 1 table and 48 connections is here and the problem might be the same as in random-points.
QPS 1       8       48      concurrency/engine
- 8 tables 1376    10256   28762   myrocks 2863    13588   15630   inno5635 2579    17899   50430   inno5717 1989    14091   36737   toku5717 - 1 table 1577     8489    8691   myrocks 2845     8787   10947   inno5635 2574    11904   16505   inno5717 1802     7318    7788   toku5717
QPS ratio for 8 tables rocks   inno56  inno57  toku 1.534   1.411   1.377   1.300   1 connection - low concurrency 1.492   1.070   1.285   1.191   8 connections - mid concurrency 1.240   0.955   1.055   0.998   48 connections - high concurrency
QPS ratio for 1 table rocks   inno56  inno57  toku 1.758   1.402   1.375   1.178   1 connection - low concurrency 1.235   0.692   0.854   0.618   8 connections - mid concurrency 0.374   0.669   0.345   0.211   48 connections - high concurrency
insert-only
For this test the QPS ratio section has the QPS for the engine at 1 table divided by the QPS for the engine at 8 tables. For this test MyRocks does not lose QPS while InnoDB and TokuDB do. For all tests the binlog was enabled and sync-on-commit was disabled for the binlog and database log. While I used PMP to explain the performance problems above I won't do that here for TokuDB and InnoDB.
QPS 1       8       48      concurrency/engine
- 8 tables  9144   46466    65777  myrocks 12317   59811    59971  inno5635 10539   61522   115598  inno5717  3199   17164    34043  toku5717 - 1 table  9329   47629    67704  myrocks 12273   55445    37180  inno5635 10529   61235    59690  inno5717  3156   17193    25754  toku5717
QPS ratio rocks   inno56  inno57  toku 1.020   0.996   0.999   0.986   1 connection -low concurrency 1.025   0.927   0.995   1.001   8 connections - mid concurrency 1.029   0.619   0.516   0.756   48 connections - high concurrency

This Week in Data with Colin Charles #4: Percona Server for MySQL with MyRocks

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

Percona Live Europe Dublin

Have you registered for Percona Live Europe Dublin? We’ve more or less finalized the schedule, and the conference grid looks 100% full. We’re four weeks away, so I suggest you register ASAP!

I should also mention that no event can be pulled off without sponsors, so thank you sponsors of Percona Live Europe 2017. I sincerely hope to see more sign up. Feel free to ask me more about it, or just check out our sponsor prospectus.

Releases
  • MariaDB/MySQL Replication Manager 1.1.1 release. There was recently a talk accepted at Percona Live Europe 2017 that referenced “MRM”. I was asked about it, and I think this tool needs more marketing! MRM is a high availability solution to manage MariaDB 10.x and MySQL and Percona Server for MySQL 5.7 GTID replication topologies. It has a new 1.1.1 release that provides improvements for MariaDB Server and MariaDB MaxScale (this tool itself gained MySQL GTID support back in April 2017). Do you use MRM?
  • Percona Server 5.7.19-17 is now released! Why is this exciting? Because it comes with the MyRocks storage engine! Yes, it is experimental, and no, it isn’t recommended for production – but why not get started with the MyRocks Introduction? I tried the installation guide and got everything started very quickly. Read about the current limitations and differences between Percona MyRocks and Facebook MyRocks (considering you’ll really want to use MyRocks in a shipping release – Facebook’s MyRocks requires compiling their tree, and this is really not the recommended way to get going!).
Link List Upcoming Appearances

Percona’s web site tracks community events, so check that out and see where to listen to Perconians speak. My upcoming appearances are:

  1. db tech show case Tokyo 2017. 5-7 September 2017, Tokyo, Japan
  2. Open Source Summit North America. 11-14 September 2017, Los Angeles, CA, USA
  3. Percona Live Europe Dublin. 25-27 September 2017, Dublin, Ireland
  4. Velocity Europe. 17-20 October 2017, London, UK
  5. Open Source Summit Europe. 23-26 October 2017, Prague, Czech Republic

I’ve been spending time on writing my db tech showcase talk. Will you be in Tokyo, Japan next week? Want to meet up? Don’t hesitate to drop me an email: colin.charles@percona.com.

Feedback

bet365 now purchases Basho assets. The good news for Riak users? “It is our intention to open source all of Basho’s products and all of the source code that they have been working on.” The Register covers this, too.

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

Pages