MySQL

Percona Server 5.7 performance improvements

MySQL Performance Blog - Thu, 2016-03-17 16:32

In this blog post, we’ll be discussing Percona Server 5.7 performance improvements.

Starting from the Percona Server 5.6 release, we’ve introduced several significant changes that help address performance problems for highly-concurrent I/O-bound workloads. Some of our research and improvements were re-implemented for MySQL 5.7 – one of the best MySQL releases. But even though MySQL 5.7 showed progress in various aspects of scalability and performance, we’ve found that it’s possible to push I/O bound workload limits even further.

Percona Server 5.7.11 currently has two major performance features in this area:

  • Multi-threaded LRU flusher. In a limited form, this feature exists in Percona Server 5.6. We split the LRU flusher thread out of the existing page cleaner thread, and it is now solely tasked with flushing the flush list. Along with several other important changes, this notably improved I/O bound workload performance. MySQL 5.7 has also made a step forward by introducing a pool of page cleaner threads that should help improve parallelism in flushing. However, we believe that the current approach is not good enough – especially for LRU flushing. In one of our next Percona Server 5.7 performance improvements posts, we’re going to describe aspects of MT flushing, and why it’s especially important to have an independent MT LRU flusher.
  • Parallel doublewrite buffer. For ages, MySQL has had only one doublewrite buffer for flushing data pages. So even if you had several threads for flushing you couldn’t efficiently use them – doublewrite quickly became a bottleneck. We’ve changed that by attaching two doublewrite buffers to each buffer pool instance: one for each type of page flushing (LRU and flush list). This completely avoids any doublewrite contention, regardless of the flusher thread count. We’ve also moved the doublewrite buffer out of the system tablespace so you can now configure its location.

Now let’s review the results of a sysbench OLTP_RW, I/O-bound scenario. Below are the key settings that we used in our test:

  • dataset 100GB
  • innodb_buffer_pool_size=25GB
  • innodb_doublwrite=1
  • innodb_flush_log_at_trx_commit=1

While evaluating MySQL 5.7 RC we observed a performance drop in I/O-bound workloads, and it looked very similar to MySQL 5.6 behavior. The reason for the drop is the lack of free pages in the buffer pool. Page cleaner threads are unable to perform enough LRU flushing to keep up with the demand, and the query threads resort to performing single page flushes. This results in increased contention between all the of the flushing structures (especially the doublewrite buffer).

For ages (Vadim discussed this ten years ago!) InnoDB has had a universal workaround for most scalability issues: the innodb_thread_concurrency system variable. It allows you to limit the number of active threads within InnoDB and reduce shared resource contention. However, it comes with a trade-off in that the maximum possible performance is also limited.

To understand the effect, we ran the test two times with two different InnoDB concurrency settings:

  • innodb_thread_concurrency=0: with this default value Percona Server 5.7 shows the best results, while MySQL 5.7 shows sharply decreasing performance with more than 64 concurrent clients.
  • innodb_thread_concurrency=64: limiting the number of threads inside InnoDB affects throughput for Percona Server slightly (with a small drop from the default setting), but for MySQL that setting change is a huge help. There were no drops in performance after 64 threads, and it’s able to maintain this performance level up to 4k threads (with some variance).

To understand the details better, let’s zoom into the test run with 512 threads:

The charts above show that contentions significantly affect unrestricted concurrency throughput, but affect latency even worse. Limiting concurrency helps to address contentions, but even with this setting Percona Server shows 15-25% better.

Below you can see the contention situation for each of the above runs. The graphs show total accumulated waiting time across all threads per synchronization object (per second). For example, the absolute hottest object across all graphs is the doublewrite mutex in MySQL-5.7.11 (without thread concurrency limitation). It has about 17 seconds of wait time across 512 client threads for each second of run time.

 

mysql server settings

innodb_log_file_size=10G innodb_doublewrite=1 innodb_flush_log_at_trx_commit=1 innodb_buffer_pool_instances=8 innodb_change_buffering=none innodb_adaptive_hash_index=OFF innodb_flush_method=O_DIRECT innodb_flush_neighbors=0 innodb_read_io_threads=8 innodb_write_io_threads=8 innodb_lru_scan_depth=8192 innodb_io_capacity=15000 innodb_io_capacity_max=25000 loose-innodb-page-cleaners=4 table_open_cache_instances=64 table_open_cache=5000 loose-innodb-log_checksum-algorithm=crc32 loose-innodb-checksum-algorithm=strict_crc32 max_connections=50000 skip_name_resolve=ON loose-performance_schema=ON loose-performance-schema-instrument='wait/synch/%=ON',

Conclusion

If you are already testing 5.7, consider giving Percona Server a spin – especially if your workload is I/O bound. We’ve worked hard on Percona Server 5.7 performance improvements. In upcoming posts, we will delve into the technical details of our LRU flushing and doublewrite buffer changes.

Categories: MySQL

Percona XtraBackup 2.3.4 is now available

MySQL Performance Blog - Thu, 2016-03-17 14:48

Percona is glad to announce the release of Percona XtraBackup 2.3.4 on March 17th, 2016. Downloads are available from our download site or Percona Software Repositories.

Percona XtraBackup enables MySQL backups without blocking user queries, making it ideal for companies with large data sets and mission-critical applications that cannot tolerate long periods of downtime. Offered free as an open source solution, Percona XtraBackup drives down backup costs while providing unique features for MySQL backups.

This release is the current GA (Generally Available) stable release in the 2.3 series.

Bugs Fixed:

  • Percona XtraBackup didn’t respect variables stored in backup-my.cnf unless it was specified. One needed to specify --defaults-file=backup-my.cnf for options to be respected. Bug fixed #1527081.
  • Percona XtraBackup didn’t abort the backup if innobackupex --rsync completed with error. Bug fixed #1537256.
  • When backup was taken on MariaDB 10 with GTID enabled, Percona XtraBackup didn’t store gtid_slave_pos in xtrabackup_slave_info file but logged it only to STDERR. Bug fixed #1404484.
  • Percona XtraBackup was silently skipping extra arguments. Bug fixed #1533542 (Fungo Wang).
  • Percona XtraBackup refuses client connecting to server if it uses old (pre-4.1.1) protocol. To disable this check in order and to allow backing up servers with legacy passwords still set, new xtrabackup --skip-secure-auth option has been implemented. Bug fixed #1508450.

Release notes with all the bugfixes for Percona XtraBackup 2.3.4 are available in our online documentation. Bugs can be reported on the launchpad bug tracker.

Categories: MySQL

Percona Live featured talk with Konstantin Osipov: Tarantool – a Lua Based Database Engine and In-Memory Data Grid

MySQL Performance Blog - Wed, 2016-03-16 20:35

Welcome to the next installment of our talks with Percona Live Data Performance Conference 2016 speakers! In this series of blogs, we’ll highlight some of the speakers that will be at this year’s conference, as well as discuss the technologies and outlooks of the speakers themselves. Make sure to read to the end to get a special Percona Live registration bonus! This blog is Percona Live featured talk with Konstantin Osipov.

In this installment, we’ll meet Konstantin Osipov, Developer and Architect at Tarantool.org. His talk is Tarantool – a Lua based database engine and in-memory data grid, and will focus on a task queue application using Tarantool as an application server and a database.

I got a chance to discuss Konstantin’s talk with him:

Percona: Give me a brief history of yourself: how you got into database development, where you work, what you love about it.

Konstantin: I am a computer science graduate of the Moscow State University, 2001. In 2003 I joined MySQL AB (at that time a small Swedish company!) as a server engineer. The first release I took part in was MySQL 4.1, and I stayed with the company till MySQL 5.5 (released by Oracle).

MySQL journey was a bumpy and fun ride. First of all, it was a lot of work to make a quality product. I still have a souvenir clock from 2008 with the inscription “666 bugs fixed” (the actual number of bugs fixed in 5.1 before it became generally available). As MySQL development shifted focus from multi-engine architecture to better integration with InnoDB, I left since I felt my technical vision was at odds with the product priorities as set by Oracle.

Though I left MySQL team, I didn’t leave the open source community. MySQL 5.7 ships with a feature I contributed (new user level locks).

Tarantool, the project I joined in 2010, endorses the open source values not only with its source code license (it’s 2-clause BSD) but also in the governance of the trademark and in working with the contributors.

It’s a shot at a few big ideas that have been emergent for quite a while:

  • It’s an in-memory database since more and more workloads are fitting in memory and can be served with in-memory This lowers latency, and makes it more predictable – at a lower cost.
  • It’s built around a shared-nothing architecture to perform better on current and future hardware that is massively parallel.
  • We’re one of the few databases following the tenets of Michael’s Stonebraker paper “The End of an Architectural Era (It’s Time for a Complete Rewrite).”
  • It’s highly extensible. Tarantool has pluggable storage, making it relatively easy to add custom storage engines. It has a public programming API which we use to implement many of the features of the product.

Having said that, myself and the team have always had the focus on production quality software. Mail.Ru, the founder of the project and its anchor customer, has thousands of instances in production use, many of which are on mission critical business paths.

Perhaps, if put into a single sentence, this is an attempt to apply good engineering to some of the past decade’s great ideas circulating in the open source and academic communities.

Percona: Your talk is going to be on “Tarantool – a Lua based database engine and in-memory data grid.” So, I don’t know that Tarantool is as common a solution as MySQL or MongoDB – can you describe it relation to how it compares to other database engines?

Konstantin: We have placed ourselves quite a bit apart from other databases by the way we make database technology available to the end user. From a user perspective, Tarantool is not a database management server – it’s a programming language. Tarantool is a Lua execution environment and is fully compatible with Lua syntax, packages and tools. A typical application for Tarantool is a custom business logic first, and persistent and replicated state second.

In a typical deployment, Tarantool replaces not another database but a custom C++ application server with its database back-end (whatever it is). Some users compare us to Redis, others to RabbitMQ, and others use Tarantool to replace multiple overloaded MySQL instances.

Percona: Where does it work best? What types of workloads is it suited for and why would you suggest it as a solution, as opposed to something more “mainstream”?

Konstantin: Our main use is as an in-memory database which is a reliable, primary data source. You might want to put all data in memory for many reasons:

  • To provide better end-user experience, by lowering the latency and making it stable
  • To scale to serve hundreds of thousands of requests
  • Your application requires complicated business logic while demands on performance are still near real-time.

Many beginner users confuse in-memory database technology and caching, be it built into a database engine or standalone. The key to understanding the difference is the observation that many modern workloads are CPU or concurrency bound.

In the famous paper “OLTP Through the Looking Glass, and What We Found There,” by Stavros Harizopoulos et al, the authors demonstrate that 88% of the execution time of a disk-based database is in activities, not fetching the data from disk.

Tarantool perhaps does not solve different problems, but it does unleash the power of hardware – making it possible to solve existing data problems at higher performance and lower cost.

Percona: What would you say Tarantool needs to do to compete with some of the more recognized solutions? What keeps you up at night with regard to the future of Tarantool?

Konstantin: I prefer working on the future to talking about it! For the past five years, Tarantool has been focused on making the single core transaction processing great. In future releases, we will put more focus on making the technology more useful to a broader community, and this includes better support for clustering, query languages, management software, etc.

Percona: What are you most looking forward to at Percona Live Data Performance Conference 2016?

Konstantin: MySQL has always been a technological diversity pioneer, with different storage engines and replication technology. So I really love the shift to more broad data performance needs. I can’t stress it enough.

I also see this as a transition for MySQL and the community into a new era of diverse data management approaches. And thanks to this shift, Tarantool got a chance to get known by a great community of DBAs and data engineers.

I am also looking forward to the high-quality content. At conferences, my personal agenda is always less marketing and more education. I love that the conference is by engineers, for engineers. In talks, I’m always looking forward to learning the how not what, and I think Percona’s conference has a lot of it. I try to make my own talks follow this spirit as well.

You can read more about Tarantool and Konstantin’s thoughts about it at his personal blog on LiveJournal, or at the Tarantool Facebook page. He also tweets regularly on Twitter.

Come and see the Percona Live featured talk with Konstantin Osipov. To see Konstantin’s talk, register for Percona Live Data Performance Conference 2016. Use the code “FeaturedTalk” and receive $100 off the current registration price!

The Percona Live Data Performance Conference is the premier open source event for the data performance ecosystem. It is the place to be for the open source community as well as businesses that thrive in the MySQL, NoSQL, cloud, big data and Internet of Things (IoT) marketplaces. Attendees include DBAs, sysadmins, developers, architects, CTOs, CEOs, and vendors from around the world.

The Percona Live Data Performance Conference will be April 18-21 at the Hyatt Regency Santa Clara & The Santa Clara Convention Center.

Categories: MySQL

Change user password in MySQL 5.7 with “plugin: auth_socket”

MySQL Performance Blog - Wed, 2016-03-16 12:00

In this blog, we’ll discuss how to use “plugin: auth_socket” to change user password in MySQL 5.7. In

In Debian/Ubuntu it is pretty common to install MySQL/Percona Server with an empty password for the root user. After everything is configured and tested, then a password is set. This is not a good practice in production servers (or soon-to-be production servers), but you can do it for your own test servers. With regards to authentication, things have changed a bit in 5.7, and methods that worked before now need a different procedure.

Let’s say that you install 5.7 and don’t specify a password. You will see the following:

SELECT User, Host, HEX(authentication_string) FROM mysql.user; +-----------+-----------+------------------------------------------------------------------------------------+ | User | Host | HEX(authentication_string) | +-----------+-----------+------------------------------------------------------------------------------------+ | root | localhost | |

OK, the password is empty. Let’s change it:

mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'test'; mysql> SELECT User, Host, HEX(authentication_string) FROM mysql.user; +-----------+-----------+------------------------------------------------------------------------------------+ | User | Host | HEX(authentication_string) | +-----------+-----------+------------------------------------------------------------------------------------+ | root | localhost | |

That doesn’t work, it’s still empty:

mysql> SET PASSWORD FOR 'root'@'localhost' = PASSWORD('test'); mysql> SELECT User, Host, HEX(authentication_string) FROM mysql.user; +-----------+-----------+------------------------------------------------------------------------------------+ | User | Host | HEX(authentication_string) | +-----------+-----------+------------------------------------------------------------------------------------+ | root | localhost | |

But why? Let’s check the warnings:

mysql> show warnings; +---------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Level | Code | Message | +---------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Warning | 1287 | 'SET PASSWORD FOR = PASSWORD('')' is deprecated and will be removed in a future release. Please use SET PASSWORD FOR = '' instead | | Note | 1699 | SET PASSWORD has no significance for user 'root'@'localhost' as authentication plugin does not support it. | +---------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

The problem is in the note coded 1699. This user is using an authentication plugin that doesn’t support a password. Which one could it be?

mysql> SELECT * from user where User="root"G *************************** 1. row *************************** Host: localhost User: root [...] plugin: auth_socket [...]

Ok, auth_socket. If you install 5.7 and don’t provide a password to the root user, it will use the auth_socket plugin. That plugin doesn’t care and doesn’t need a password. It just checks if the user is connecting using a UNIX socket and then compares the username.

If we want to configure a password, we need to change the plugin and set the password at the same time, in the same command. First changing the plugin and then setting the password won’t work, and it will fall back to auth_socket again. So, run:

So, the correct way to do this is to run the following:

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'test';

And now, it works

mysql> SELECT User, Host, HEX(authentication_string) FROM mysql.user; +-----------+-----------+------------------------------------------------------------------------------------+ | User | Host | HEX(authentication_string) | +-----------+-----------+------------------------------------------------------------------------------------+ | root | localhost | 2A39344244434542453139303833434532413146393539464430324639363443374146344346433239 |

If your deployments use empty passwords, and you change them later on, remember to update your scripts/recipes!

Categories: MySQL

Update for Percona Live 2016 Attendees: Fun Events, Don’t Miss Out!

MySQL Performance Blog - Tue, 2016-03-15 21:02

The Percona Live Data Performance Conference 2016 is approaching quickly! If you haven’t registered yet, click here to pick up your tickets. There are several options: full registration, 101 Crash Courses, Keynote passes, etc. Register now!

For the people already going, we wanted to update you on some of the events that are going to be taking place:

  • The community dinner (sponsored by Pythian) will take place on Tuesday, April 19th. This is a fun night for the whole MySQL community at large – everyone is welcome to attend. The dinner will once again be held at Pedros. Tickets are $40 each. Registering early is highly encouraged, as this fills up quickly! Don’t get shut out, confirm you spot now!
  • The community networking reception is on Wednesday night, April 20. This event from 5:30pm-7pm is an excellent time to walk around and talk to all the attendees, sponsors and speakers – while enjoying delicious appetizers and cocktails. Following the reception, will be the Percona Community Game Night (back by popular demand)! This event was a big hit last year, and we look forward to a great time this year. If you miss the reception, more food and drinks will be available at Game Night!
  • The community award ceremony will wrap up Thursday morning’s keynotes (April 21). The MySQL Community Awards is a community-based initiative, with the goal of publicly recognizing contributors to the MySQL ecosystem. The categories can be found here. Come and celebrate your fellow community members!
  • Don’t miss out on photographer Julian Cash. Julian has taken portraits at the MySQL convention for years (2015, 2013, 2008, 2009, 2006). This year, he’s orchestrating something different: light painting photos on the convention room floor itself! People can watch the process inside the light painting booth via a large TV screen. Examples of these amazing creations can be found here.

We look forward to seeing you at Percona Live Data Performance Conference 2016!

Categories: MySQL

Percona Server 5.7.11-4 is now available

MySQL Performance Blog - Tue, 2016-03-15 14:32

Percona is glad to announce the GA release of Percona Server 5.7.11-4 on March 15, 2016. Download the latest version from the Percona web site or from the Percona Software Repositories.

Based on MySQL 5.7.11, including all the bug fixes in it, Percona Server 5.7.11-4 is the current GA release in the Percona Server 5.7 series. All of Percona’s software is open-source and free, all the details of the release can be found in the 5.7.11-4 milestone at Launchpad.

New Features:

Bugs Fixed:

  • If pid-file option wasn’t specified with the full path, Ubuntu/Debian sysvinit script wouldn’t notice if the server is actually running which would lead to timeout or in some cases even hang. Bug fixed #1549333.
  • Buffer pool may fail to remove dirty pages for a particular tablesspace from the flush list, as requested by, for example, DROP TABLE or TRUNCATE TABLE commands. This could lead to a crash. Bug fixed #1552673.
  • Audit Log Plugin worker thread may crash on write call writing fewer bytes than requested. Bug fixed #1552682 (upstream #80606).
  • Percona Server 5.7 systemd script now takes the last option specified in my.cnf if the same option is specified multiple times. Previously it would try to take all values which would break the script and server would fail to start. Bug fixed #1554976.
  • mysqldumpslow script has been removed because it was not compatible with Percona Server extended slow query log format. Please use pt-query-digest from Percona Toolkit instead. Bug fixed #856910.

Other bugs fixed: #1521120, #1549301 (upstream #80496), and #1554043 (upstream #80607).

Release notes for Percona Server 5.7.11-4 are available in the online documentation. Please report any bugs on the launchpad bug tracker .

Categories: MySQL

Percona XtraDB Cluster in a high latency network environment

MySQL Performance Blog - Tue, 2016-03-15 02:01

In this blog, we’ll discuss how Percona XtraDB Cluster handled a high latency network environment.

Recently I was working in an environment where Percona XtraDB Cluster was running over a 10GB network, but one of the nodes was located in a distant location and the ping time was higher than what you would typically expect.

For example, the following shows the ping time between nodes in my local cluster:

ping 172.16.0.1 -s 1024 PING 172.16.0.1 (172.16.0.1) 1024(1052) bytes of data. 1032 bytes from 172.16.0.1: icmp_seq=1 ttl=64 time=0.144 ms 1032 bytes from 172.16.0.1: icmp_seq=2 ttl=64 time=0.110 ms 1032 bytes from 172.16.0.1: icmp_seq=3 ttl=64 time=0.109 ms 1032 bytes from 172.16.0.1: icmp_seq=4 ttl=64 time=0.125 ms

Generally speaking, you can say that the ping time was 0.1ms.

Now let’s say one node has a ping time of 7ms. Percona XtraDB Cluster with the default settings does not handle this case very well. There is, however, some good news: a small configuration change can improve things dramatically – you just need to know what to change!

Let’s review this case, and for the test I’ll use following sysbench:

sysbench --test=tests/db/oltp.lua --oltp_tables_count=100 --oltp_table_size=1000000 --num-threads=50 --mysql-host=172.16.0.4 --mysql-user=sbtest --oltp-read-only=off --max-time=3600 --max-requests=0 --report-interval=10 --rand-type=uniform --rand-init=on run

For the first example, all the nodes have equal latency (0.1ms), and for the second example we’ll introduce a 7ms latency to one of the nodes. You can easily do this in Linux with the following command:

# Add 7ms delay for network packets tc qdisc add dev eno2 root netem delay 7ms # to remove delay use: # tc qdisc del dev eno2 root netem

So now let’s  compare both the throughput and the response time for both cases:

Or in numbers:

 Setup  Throughput (average), tps  95% response time (average), ms  No latency  2698  27.43  7ms latency  739  571.40

As you can see, that is a huge difference! The variance in throughput and response time is also significant. Apparently there are two variables responsible for that:

The evs.send_window variable defines the maximum number of data packets in replication at a time. For WAN setups, the variable can be set to a considerably higher value than the default (for example, 512).

So now let’s start a cluster with --wsrep_provider_options="evs.send_window=512;evs.user_send_window=512"  in the 7ms node case.

How do the throughput and response time change? Let’s see:

Or in numeric format:

 Setup  Throughput (average), tps  95% response time(average), ms  No latency  2698  27.43  7ms latency  739  571.40  7ms latency – Optimized  2346  46.85

We can see that there is still some performance penalty. In the end, it is impossible to mitigate the high response time from one node,
but that’s a big improvement compared to default settings.

So if you have a high-bandwidth network with a node that shows a high ping time, consider changing evs.send_window and evs.user_send_window.

Categories: MySQL

Percona Server for MongoDB 3.0.9-1.4 is now available

MySQL Performance Blog - Fri, 2016-03-11 10:20

Percona is pleased to announce the release of Percona Server for MongoDB 3.0.9-1.4 on March 11, 2016. Download the latest version from the Percona web site or from the Percona Software Repositories.

Percona Server for MongoDB is an enhanced, open source, fully compatible, highly scalable, zero-maintenance downtime database supporting the MongoDB v3.0 protocol and drivers. Based on MongoDB 3.0.9, it extends MongoDB with MongoRocks and PerconaFT storage engines, as well as features like external authentication and audit logging. Percona Server for MongoDB requires no changes to MongoDB applications or code.

 

NOTE: The MongoRocks storage engine is still under development. There is currently no officially released version of MongoRocks that can be recommended for production.

Bugs Fixed

  • PSMDB-56: Fixed failure of audit logging in debug build.

Other Improvements

Percona Server for MongoDB 3.0.9-1.4 release notes are available in the official documentation.

Categories: MySQL

Introduction to MySQL Troubleshooting Webinar: Q & A for How to Create a Test Setup

MySQL Performance Blog - Thu, 2016-03-10 20:23

Thank you for attending my March 2 MySQL troubleshooting webinar “Introduction to troubleshooting: How to create test setup,” which is the first in the “MySQL Troubleshooting Webinar” series. The recording and slides for the webinar are available here. Here is the list of your questions that I wasn’t able to answer during the webinar, with responses.

Q: “Copy Option file from Production to test server,” which option file we are talking about?

A: The MySQL configuration file, usually /etc/my.cnf . See also this manual.

Q: For future seminars, what environment we need to setup?

A: There is no special environment, but I will use a standard employee database for many examples. If you want to run it you can install it in advance. It is also better to have MySQL version 5.7, because I will speak about features that don’t exist in older versions.

Q: Hello, may I have a copy of the presentation as a future reference?

A: Yes, you can find it here

Q: What is the difference between mysqldump and mysqldbcopy and mysql sandbox ? Which one is better for this?

A: MySQL Sandbox is set of utilities that will help you to quickly install one or more MySQL servers in the same host, either standalone or in groups. It extracts *tar.gz archive, creates default directories, initializes the system database, creates default configuration files and aliases to utilities, but it does not dump or load data for you. mysqldump is just a backup program that does logical backup of your databases. mysqldbcopy copies database objects between two servers, or between two databases on the same server, without creating intermediary dump file. These are completely different tools that should be used for different purposes: MySQL Sandbox to quickly install from *tar.gz package into custom directory, mysqldump to make a dump of your data and mysqldbcopy to quickly clone a database.

Q: Open source project. Will MySQL  behave like a Java program when garbage collections hang temporally execution?

A: This is not normal behavior, but can happen in a high-load environment. For example, when InnoDB starts purging the history list aggressively and a bunch of new queries comes in. You can read about issues with InnoDB flushing and purge in these posts: 1, 2.

The InnoDB development team works on making these stalls smaller from version to version, though.

Q: What is the effect of disk speed? Why is MySQL software affected by disk speed ? I thought that the faster a disk is, the faster the database is…?

A: I believe you are asking about innodb_io_capacity option, and why it is not dynamically set inside InnoDB. This option sets an upper limit of IO activity for background tasks that happen inside InnoDB (when it flushes data to disk, for example). This is basically “how many pages per second to flush?” Faster disks can perform more operations per second than slower disks, therefore bigger values are suited for faster disks and smaller for slower. Read this blog post, it has greater detail about how InnoDB flushing works. Regarding why this number is not calculated by InnoDB itself, you should, of course, ask InnoDB developers (I can only speculate!). But I think it’s because desired disk speed is not something “built in stone” by the manufacturer. It often depends on various factors.

Regarding why this number is not calculated by InnoDB itself,  you should, of course, ask InnoDB developers, because I can only speculate. But I think this is because desired disk speed is not something “built in stone” by the manufacturer, but can depend on many factors. For example, if you run MySQL on a non-dedicated server,  you probably would want to leave some resources for other software.Another case is if you have to temporarily save backups on the same filesystem where data directory is located.

Q: For innodb_io_capacity, what specifically does that limit? IOPS that can be initiated?

A: It specifies how many pages per second InnoDB can flush or merge from the change buffer in the background. Actually, the documentation for this option is very good, and I recommend you use it.

Q: You are focusing on InnoDB. But what’s about the others? MyISAM for example? Is it possible that my applications would work faster on MyISAM instead of InnoDB?

A: In MySQL 5.7, only in one case: point-select workloads, which means a single select that reads a row by its primary key. You will find benchmarks comparing MyISAM vs InnoDB in this post by Dimitri Kravtchuk. Follow the links in the beginning of the post to find the benchmarks for earlier versions. I will mention MyISAM in future webinars. I am also thinking about making webinar about storage engines specifics and probably will do separate webinar for MyISAM.

I will address MyISAM in future webinars. I am also thinking about making a webinar about storage engines specifics, and probably will do a separate webinar for MyISAM.

Q: mysqldbcopy? Is it available with Linux Ubuntu version?

A: Yes. MySQL Utilities is a set of Python programs, so they work everywhere Python is installed. There is a separate package for Ubuntu.

Q: What do you use to keep data synced between the master/slaves/dev environments?

A: If you want to keep data synced, you simply need to set up a master-slave environment. If you want to sync tables that already exist, but one of them has slightly outdated data, you can use pt-table-sync.

Q: For the binary backup do we have to do both of those things or just the cp?

A: Any of the commands from the slide work: either cp or XtraBackup.

Q: How we could identify slow queries? 

A: I will run a separate webinar called “Introduction to Troubleshooting Performance” where I will describe this in detail, but usually you can find such queries in the slow query log.

Q: Have you more dedicated recommendations for All-Flash storages or/and Amazon?

A: Regarding test setup: no. But if you run MySQL server on Amazon and want to test it on a local machine or just a cheaper instance, pay attention to slides about how to avoid limitations.

Q: No offense but so far too much time wasted on slides that convey things we already know…let’s get right to the main reason for the webinar!

A: Well, this was introductory webinar. I am planning to have about 16. The next two will also be introductory: “Introduction to MySQL Troubleshooting: Basic Techniques” and “Intro to Troubleshooting Performance: What Affects Query Execution” (exact date TBD). If you are an experienced user, you can re-join starting from the fourth webinar where I will speak about specifics like storage engines, replication and options.

Q: Is there a way to simulate the production load to the test server for better troubleshooting with MySQL?  I got that we restore the data. However I am keen to simulate the load with all the queries that were running on production to test.

A: This is good question, and I believe that many people who do QA or troubleshooting  want such a tool. The tool exists: this is QueryPlayback (formerly known Percona Playback), designed by Percona. But the issue with all such tools is that they aren’t perfect. For example, QueryPlayback is hampered by these pretty serious bugs: 1, 2. Also, replaying queries that modify data doesn’t always work if you did not have a backup taken prior to the query run in production. This means you still need to adjust your tests manually. You can also use scriptable benchmarks tools, such as SysBench, to emulate the load similar to one you have in production.

Q: Does innodb_thread_concurrency depend on the CPU cores (like quad core, hex core etc.) or just number of CPUs on the host?

A: Yes, it depends on the number of CPU cores.

Q: After converting a table from MyISAM to InnoDB, I’m finding the same queries to be significantly slower (all else same). Is there a common/obvious reason for this to happen?

A: The main differences between MyISAM and InnoDB are:

  • InnoDB is transactional and MyISAM is not. This means what every query you run on InnoDB is part of transaction. If this is an update query or if you use a version earlier than 5.6, InnoDB has to make a snapshot of all rows this query accesses to be able to restore it if the query fails.
  • InnoDB and MyISAM use different locking models
  • InnoDB does not support such options as Concurrent Inserts , Low Priority Updates or Delayed Inserts.
  • InnoDB has its own options for tuning to better performance, which are quite low by default

Q: How do I identify how many cores MySQL is currently using?

A: You should use OS-level tools, such as `top -H -p PID_OF_MYSQL`

Q: What is the performance impact while reducing the buffer poll when the chunk size is the default (128MB) in MySQL 5.7?

A: innodb_buffer_pool_chunk_size controls the size of chunks in which a resizing operation will happen. It should affect performance in the same way as copying smaller files compared with copying a bigger file in your environment: copying bigger files requires more memory, but copying many smaller files requires more operations.

Q: Why do queries, especially desc table_name type of queries, sometimes get stuck at open tables thread state? Even increasing the table cache and open files will not release hung queries until we restart MySQL.

A: This depends on many factors: if the OS allows mysqld to have enough file descriptors to handle so many tables open in parallel (check output of ulimit -n , taken for the user you run mysqld as) to other overtaxed resources, or bug in MySQL.

Q: Is it possible to extract one table from the binary backup (taken with xtrabackup or something similar) ?

A: Yes, this is possible for tables created with the option innodb-file-per-table=1 . Instructions for XtraBackup are here. XtraBackup supports extracting a single table from any type of backup. MySQL Enterprise Backup, instead, requires you to use option --use-tts (with MySQL server 5.6 or newer) in order to perform partial restore. Instructions are here.

Q; Is there any direct command to measure disk speeds in rpm or in some other way on Linux?

A: I usually use ioping.

Q: So it’s OK to use mysqldump when the dataset is about few hundred GB big?

A: Yes.

 

Categories: MySQL

Percona Live featured talk with Sugu Sougoumarane – Vitess: The Complete Story

MySQL Performance Blog - Thu, 2016-03-10 18:03

Welcome to the next installment of our talks with Percona Live Data Performance Conference 2016 speakers! In this series of blogs, we’ll highlight some of the speakers that will be at this year’s conference, as well as discuss the technologies and outlooks of the speakers themselves. Make sure to read to the end to get a special Percona Live registration bonus!

In this installment, our Percona Live featured talk with Sugu Sougoumarane, Infrastructure & Storage Engineer at YouTube is about Vitess: The Complete Story. I had a chance to speak with Sugu and learn a bit more about YouTube and Vitess:

Percona: Give me a brief history of yourself: how you got into database development, where you work, what you love about it.

Sugu: My involvement with databases goes back to Informix in the 90s. This was during the 4GL and client-server days. I was part of the development team for a product called NewEra.

I later joined PayPal, where we used Oracle and eventually scaled it to the biggest machine money could buy. I have to say that I’m still a fan of the mighty hash join. During my time there, I wrote the system that balanced the books, which helped me gain some unique perspectives on consistency. Word on the street is that the tool is still in use.

These experiences at PayPal influenced the founders of YouTube to try a different approach: scaling with commodity hardware. When I joined YouTube, the only MySQL database we had was just beginning to run out of steam, and we boldly executed the first resharding in our lives. It took an entire night of master downtime, but we survived. These experiences eventually led to the birth of Vitess.

Percona: Your talk is going to be on “Vitess: The Complete Story.” How has Vitess moved from a YouTube fix to a viable enterprise data solution?

Sugu: This was around 2010. YouTube was growing, not only organically, but also internally. There were more engineers writing code that could potentially impair the database, and our tolerance for downtime was also decreasing. It was obvious that this combination was not sustainable. My colleague (Mike Solomon) and I agreed that we had to come up with something that would leap ahead of the curve instead of just fighting fires. When we finally built the initial feature list, it was obvious that we were addressing problems that are common to all growing organizations.

This led us to make the decision to develop this project as open source, which had a serendipitous payback: every feature that YouTube needed had to be implemented in a generic fashion. App-specific shortcuts were generally not allowed. We still develop every feature in open source first, which we would then import to make it work for YouTube.

Aside from our architectural and design philosophy, our collaboration with Kubernetes over the last two years means anyone can now run Vitess the way YouTube does: in a dynamically-scaled container cluster. We’ve had engineers dedicated to deployment and manageability on a public cloud, making the platform ready for general consumption.

Percona: Why move to a cloud-based storage solution anyway? What are the advantages and disadvantages?

Sugu: In general, a big advantage of cloud solutions is easy horizontal scalability – tuning capacity by simply dumping more commodity servers in the mix. For storage engines, the problem is that application complexity and operational overhead tend to scale up along with the number of database instances. A cloud-native storage solution like Vitess hides the complexity of horizontal scalability from both app developers and database operators. Thousands of servers can look like one to both dev and ops. With Kubernetes, Vitess even becomes agnostic to the underlying choice of cloud platform, providing cloud flexibility with no vendor lock-in.

Percona: What are the roadblocks cloud data becoming the default? What are the issues about cloud data storage that keep you up at night?

Sugu: Cloud technologies are beginning to coalesce around ideas like immutable infrastructure and ephemeral, dynamically-scheduled workloads. Instead of changing a server, you dynamically request a new one, and the old one disappears. These ideas work great for stateless app servers but represent unique challenges for storage engines. It turns out that many of these challenges are ones we faced at YouTube as we moved Vitess from private data centers into Google’s global container cluster. So we know cloud-native data storage works at scale, but now we have to prove that it works just as well on public cloud.

Percona: What are you most looking forward to at Percona Live Data Performance Conference 2016?

Sugu: I feel like I still don’t know MySQL well enough. I’m hoping to learn more about its internals and new features. I’m also looking forward to learning more about today’s data challenges that companies are facing, and hear about the creative ways people are solving them.

Want to find out more about Sugu Sougoumarane, Vitess, and YouTube? Subscribe to the Vitess blog site, and check out the Vitess main page.

To hear Sugu’s talk on Vitess: The Complete Story, register for Percona Live Data Performance Conference 2016. Use the code “FeaturedTalk” and receive $100 off the current registration price!

The Percona Live Data Performance Conference is the premier open source event for the data performance ecosystem. It is the place to be for the open source community as well as businesses that thrive in the MySQL, NoSQL, cloud, big data and Internet of Things (IoT) marketplaces. Attendees include DBAs, sysadmins, developers, architects, CTOs, CEOs, and vendors from around the world.

The Percona Live Data Performance Conference will be April 18-21 at the Hyatt Regency Santa Clara & The Santa Clara Convention Center.

 

Categories: MySQL

What to expect at Percona Live 2016!

MySQL Performance Blog - Wed, 2016-03-09 22:22

Whew! We’ve just about have all the i’s dotted and t’s crossed for the upcoming Percona Live Data Performance Conference in Santa Clara, April 18-21. We’re looking forward to seeing you there. We’re posting this blog to let you know what to expect at Percona Live 2016.

Just to keep you posted on what we’ve got in store at Percona Live 2016, below are some of the highlights that we have planned:

  1. More talks and speakers than ever before: 180 breakout sessions, 16 tutorials, 10 keynote speakers, and 2 101 crash courses (MySQL and MongoDB). You should find something that appeals to you with our expanded set of conference tracks, including:
  • MongoDB
  • NoSQL
  • MySQL
  • Data in the Cloud
  • Replication
  • Performance
  • Non-MySQL
  • Culture/Case Studies
  • Operation
  • Linux Performance
  • Monitoring
  1. Tons of great networking opportunities with two huge receptions. And back by popular demand, we will again host the Percona Game Night after the networking reception on Wednesday night (4/20). It was the hit of the conference last year. This year, we’re expanding and adding in even more hardcore games to last year’s favorites (like the “meltdown!”).
  2. Lots of fun social engagements, like the Birds of a Feather sessions on Monday night. These will be followed by the community dinner at Pedros. Please register here if you plan on attending the dinner.
  3. A conference mobile app! This year, have the entire agenda in the palm of your hands! Download link for the Percona Live Data Performance Conference app coming soon!
  4. Fun photographer Julian Cash will once again be at the conference to take unique and exciting pictures. This year he has something slightly different up his sleeve – prepare to be impressed!

We’re looking forward to seeing you at Percona Live 2016 and participating with you in all the amazing activities. If you haven’t yet, register here for the Percona Live Data Performance Conference.

Categories: MySQL

Evaluating Database Compression Methods

MySQL Performance Blog - Wed, 2016-03-09 16:16

Vadim Tkachenko and I have been working with Fractal Tree® storage engines (Fractal Tree engines are available in Percona Server for MySQL and MongoDB as TokuDB and PerconaFT, respectively). While doing so, we’ve become interested evaluating database compression methods, to see how to make compression algorithms work even better than they do currently.  

In this blog post, I will discuss what we found in our compression research.

Introduction

Before we get to evaluation database compression methods, let’s review what compression properties are most relevant to databases in general. The first thing to consider is compression and decompression performance. Databases tend to be very sensitive to decompression performance, as it is often done in the “foreground” – adding to client response latency. Compression performance, on the other hand, is less critical because it can typically run in the background without adding client latency. It can, however, cause an issue if the database fills its data compression queue and “chokes.” The database workload also affects compression performance demands. If the data is loaded only once and essentially becomes read only, it might make sense to spend extra time compressing it – as long as the better compression ratio is achieved without impact to decompression speed.

The next important thing to consider is the compression block size, which can significantly affect compression ratio and performance. In some cases, the compression block size is fixed. Most InnoDB installations, for example, use a 16KB block size. In MySQL 5.7 it is possible to change block size from 4KB to 64KB, but since this setting applies to the whole MySQL instance it isn’t commonly used. TokuDB and PerconaFT allow a much more flexible compression block size configuration. Larger compression block sizes tend to give a better compression ratio and may be more optimal for sequential scan workloads, but if you’re accessing data in a random fashion you may see significant overhead as the complete block typically must be decompressed.

Of course, compression will also depend on the data you’re compressing, and different algorithms may be more optimal at handling different types of data. Additionally, different data structures in databases may structure data more or less optimally for compression. For example, if a database already implements prefix compression for data in the indexes, indexes are likely to be less compressible with block compression systems.

Let’s examine what choices we have when it comes to the compression algorithm selection and configuration. Typically for a given block size – which is essentially a database configuration setting – you will have a choice to select the compression algorithm (such as zlib), the library version and the compression level.  

Comparing different algorithms was tough until lzbench was introduced. Izbench allows for a simple comparison of different compression libraries through a single interface.

For our test, we loaded different kinds of data in an uncompressed InnoDB table and then used it as a source for lzbench:

./lzbench -equicklz,1/zstd,1/snappy/lzma,1/zlib,1/lz4,1/brotli,1 -o3 -b16 data.ibd

This method is a good way to represent database structures and is likely to be more realistic than testing compression on the source text files.

All results shown here are for “OnTime Air Performance.” We tried a variety of data, and even though the numbers varied the main outcomes are the same. You can see results for our other data types in this document.

The results for compression are heavily CPU dependent. All the data below is single thread compression benchmarks run on an Intel Xeon E5-2643 v2 @ 3.5Ghz.

Below are some of the most interesting results we found.

Comparing Compression Algorithms

Using a standard 16KB block size and a low level of compression, we can see that there is a huge variety of compression and decompression speed. The results ranged from 30MB per second for LZMA to more than 1GB per second for LZ4 for compression, and 100MB per second and  3.5GB per second for decompression (for the same pair).

Now let’s look at the compression ratios achieved.

You can see a large variety of outcomes for this data set as well, with ratios ranging from 1.89:1 (LZ4) to  6.57:1 (LZMA). Notice how the fastest and slowest compression libraries achieve the worst and best compression: better compression generally comes at disproportionately more CPU usage. Achieving 3.5 times more compression (LZMA) requires spending 37 times more CPU resources. This ratio, though, is not at all fixed: for example, Brotli provides 2.9 times better compression at 9 times higher CPU cost while Snappy manages to provide 1.9 times better compression than LZ4 with only 1.7 times more CPU cost.

Another interesting compression algorithm property is how much faster decompression is than compression. It is interesting to see there is not as large a variance between compression algorithms, which implies that the default compression level is chosen in such a way that compression is 2 to 3.5 times slower than decompression.

Block Size Impact on Compression

Now let’s look at how the compression block size affects compression and decompression performance.

On-Time Performance Data Compression Speed vs Block Size (MB/sec) Compression Method 4KB 16KB 64KB 128KB 256KB 256KB/4KB quicklz 1.5.0 level 1 128.62 299.42 467.9 518.97 550.8 4.28 zstd v0.4.1 level 1 177.77 304.16 357.38 396.65 396.02 2.23 snappy 1.1.3 674.99 644.08 622.24 626.79 629.83 0.93 lzma 9.38 level 1 18.65 30.23 36.43 37.44 38.01 2.04 zlib 1.2.8 level 1 64.73 110.34 128.85 124.74 124.1 1.92 lz4 r131 996.11 1114.35 1167.11 1067.69 1043.86 1.05 brotli 2015-10-29 level 1 64.92 123.92 170.52 177.1 179.51 2.77

 

On-Time Performance Data Compression Speed vs Block Size (MB/sec) Compression Method 4KB 16KB 64KB 128KB 256KB 256KB/4KB quicklz 1.5.0 level 1 128.62 299.42 467.9 518.97 550.8 4.28 zstd v0.4.1 level 1 177.77 304.16 357.38 396.65 396.02 2.23 snappy 1.1.3 674.99 644.08 622.24 626.79 629.83 0.93 lzma 9.38 level 1 18.65 30.23 36.43 37.44 38.01 2.04 zlib 1.2.8 level 1 64.73 110.34 128.85 124.74 124.1 1.92 lz4 r131 996.11 1114.35 1167.11 1067.69 1043.86 1.05 brotli 2015-10-29 level 1 64.92 123.92 170.52 177.1 179.51 2.77

If we look at compression and decompression speed versus block size, we can see that there is a difference both for compression and decompression, and that it depends a lot on the compression algorithm. QuickLZ, using these settings, compresses 4.3 times faster with 256KB blocks rather than 4KB blocks. It is interesting that LZ4, which I would consider a “similar” fast compression algorithm, is not at all similar, demonstrating only minimal changes in compression and decompression performance with increased block size.

Snappy is perhaps the most curious compression algorithm of them all. It has lower performance when compressing and decompressing larger blocks.

Let’s examine how compression ratio varies with different block sizes.

On-Time Performance Data DeCompression Ratio vs Block Size Compression Method 4KB 16KB 64KB 128KB 256KB 256KB/4KB quicklz 1.5.0 level 1 3.09 3.91 4.56 4.79 4.97 1.61 zstd v0.4.1 level 1 3.95 5.24 6.41 6.82 7.17 1.82 snappy 1.1.3 2.98 3.65 4.21 4.21 4.21 1.41 lzma 9.38 level 1 4.86 6.57 7.96 8.43 8.71 1.79 zlib 1.2.8 level 1 3.79 4.73 5.33 5.44 5.50 1.45 lz4 r131 1.75 1.89 1.99 2.00 2.01 1.15 brotli 2015-10-29 level 1 4.12 5.47 6.61 7.00 7.35 1.78

We can see all the compression libraries perform better with larger block sizes, though how much better varies. LZ4 only benefits a little from larger blocks, with only a 15% better compression ratio between 4KB to 256KB, while Zstd, Brotli and LZMA all get about an 80% better compression ratio with large block sizes. This is another area where I would expect results to be data dependent. With highly repetitive data, gains are likely to be more significant with larger block sizes.

Compression library gains from larger block sizes decrease as the base block sizes increase. For example most compression libraries are able to get at least a 20% better compression ratio going from 4KB to 16KB block size, however going from 64KB to 256KB only allows for a 4-6% better compression ratio – at least for this data set.

Compression Level Impact

Now let’s review what the compression level does to compression performance and ratios.

Compression Method 1 2 3 4 5 6 7 8 9 Max zstd v0.4.1 404.25 415.92 235.32 217.69 207.01 146.96 124.08 94.93 82.43 21.87 lzma 9.38 39.1 37.96 36.52 35.07 30.85 3.69 3.69 3.69 3.69 3.69 zlib 1.2.8 120.25 114.52 84.14 76.91 53.97 33.06 25.94 14.77 6.92 6.92 brotli 2015-10-29 172.97 179.71 148.3 135.66 119.74 56.08 50.13 29.4 35.46 0.39

Note. Not every compression algorithm provides level selection, so we’re only looking at the ZSTD, LZMA, ZLIB and BROTLI compression libraries. Also, not every library provides ten compression levels. If more than ten levels were available, the first nine and the maximum compression level were tested. If less than ten levels were available (like LZMA), the result for the maximum compression level was used to fill the gaps.

As you might expect, higher compression levels generally mean slower compression. For most compression libraries, the difference between the fastest and slowest compression level is 10-20 times – with the exception of Brotli where the highest compression level means really slow compression (more than 400 times slower than fastest compression).

Compression Method 1 2 3 4 5 6 7 8 9 Max zstd v0.4.1 827.61 848.71 729.93 809.72 796.61 904.85 906.55 843.01 894.91 893.31 lzma 9.38 128.91 142.28 148.57 148.72 148.75 157.67 157.67 157.67 157.67 157.67 zlib 1.2.8 386.59 404.28 434.77 415.5 418.28 438.07 441.02 448.56 453.64 453.64 brotli 2015-10-29 476.89 481.89 543.69 534.24 512.68 505.55 513.24 517.55 521.84 499.26

This is where things get really interesting. With a higher compression level, decompression speed doesn’t change much – if anything becomes higher. If you think about it, it makes sense: during the compression phase we’re searching for patterns in data and building some sort of dictionary, and extensive pattern searches can be very slow. Decompression, however, just restores the data using the same dictionary and doesn’t need much time finding data patterns. The smaller the compressed data size is, the better the performance should be.

Let’s examine the compression ratio.

Compression Method 1 2 3 4 5 6 7 8 9 Max zstd v0.4.1 7.17 7.20 6.98 7.05 7.11 7.62 7.76 7.89 7.89 8.16 lzma 9.38 8.20 8.71 8.95 8.96 8.96 10.45 10.45 10.45 10.45 10.45 zlib 1.2.8 5.50 5.71 5.97 6.27 6.50 6.80 6.88 7.01 7.09 7.09 brotli 2015-10-29 7.35 7.35 7.41 7.46 7.51 8.70 8.76 8.80 8.83 10.36

As we can see, higher compression levels indeed improve the compression ratio most of the time.  The ZSTD library seems to be some strange exception where a higher level of compression does not always mean a better ratio. We can also see that BROTLI’s extremely slow compression mode can really produce a significant boost to compression, getting it to the level of compression LZMA achieves – quite an accomplishment.   

Different compression levels don’t have the same effect on compression ratios as different compression methods do. While we saw a 3.5 times compression rate difference between LZ4 and LZMA, the highest compression rate difference between the fastest and slowest mode is 1.4x for Brotli – with 20-30% improvement in compression ratio more likely.

An important point, however, is that the compression ratio improvement from higher compression levels comes at no decompression slowdown – in contrast to using a more complicated compression algorithm to achieve better compression.

In practice, this means having control over the compression level is very important, especially for workloads where data is written once and read frequently. In that case, you could choose a higher compression leves rather than change and recompress the data frequently. Another factor is that the compression level is very easy to change dynamically, unlike the compression algorithm. In theory, a database engine could dynamically choose the compression level based on the workload – a higher compression level can be used if there are a lot of CPU resources available, and a lower compression level can be used if the system can’t keep up with compressing data.

Records

It is interesting to note a few records generated from all of these tests. Among all the methods tried, the lowest compression ratio was LZ4 with a 4KB block size, providing a 1.75 compression ratio. The highest ration was LZMA with a 256K block size, providing a maximum compression ratio of 10.45.

LZ4 is fastest both in compression and decompression, showing the best compression speed of 1167MB per second with a 64KB block size, and a decompression speed of 3666MB per second with a 16KB block size.   

LZMA appears to generally be the slowest compression and decompression, compressing at 0.88MB per second with a 16KB block size and the maximum compression level. It decompresses at 82MB per second with a 4KB block size. Only Brotli at the highest compression level compressed data slower (at 0.39MB per second).

Looking at these we see three orders of magnitude difference in compression performance, and 50 times in decompression performance. This demonstrates how the right compression algorithms choices and settings can make or break compression for your application.

Recommendations

  • Snappy looks like a great and fast compression algorithm, offering a pretty decent compression with performance that is likely to be good enough for most workloads.
  • Zstd is new and not yet 100% stable, but once it is it will be a great replacement to zlib as a general purpose compression algorithm. It gives a better compression ratio and better compression and decompression speed. At higher levels of compression, it is able to get close to LZMA’s compression ratio, at least for some kinds of data, while having a much better decompression speed.
  • LZMA remains the choice when you want the highest compression ratio at all costs. However, it will be all costs indeed! LZMA is slow both for compression and decompression. More often than not, LZMA is chosen without a clear understanding how slow it is, leading to performance issues.
  • Generally, it’s better to get the compression ratio you’re looking for by adjusting the compression level rather than by the type of algorithm, as the compression level affects compression performance more – and may even positively impact decompression performance.
  • If your system allows it, choosing larger block sizes can be best for your workload. Larger block sizes generally have a better compression ratio and a better compression and decompression speed. However, if you have many “random” data lookups, constant decompression of large blocks of data is likely to negate all of those benefits.  
  • In our tests, block sizes up to 64K provided the most benefit, with further increases showing minimal impact. It is possible, however, that these diminishing returns on adjusting the block size upward depend significantly on the data type.

Data and Details

Raw results, data sources and additional details for our evaluation of database compression methods are available in Google Docs.

Categories: MySQL

Orchestrator: MySQL Replication Topology Manager

MySQL Performance Blog - Tue, 2016-03-08 22:42

This blog post discusses Orchestrator: MySQL Replication Topology Manager.

What is Orchestrator?

Orchestrator is a replication topology manager for MySQL.

It has many great features:

  • The topology and status of the replication tree is automatically detected and monitored
  • Either a GUI, CLI or API can be used to check the status and perform operations
  • Supports automatic failover of the master, and the replication tree can be fixed when servers in the tree fail – either manually or automatically
  • It is not dependent on any specific version or flavor of MySQL (MySQL, Percona Server, MariaDB or even MaxScale binlog servers)
  • Orchestrator supports many different types of topologies, from a single master -> slave  to complex multi-layered replication trees consisting of hundreds of servers
  • Orchestrator can make topology changes and will do so based on the state at that moment; it does not require a configuration to be defined with what corresponds to the database topology
  • The GUI is not only there to report the status – one of the cooler things you can do is change replication just by doing a drag and drop in the web interface (of course you can do this and much more through the CLI and API as well)

Here’s a gif that demonstrates this (click on an image to see a larger version):

Orchestrator’s manual is quite extensive and detailed, so the goal of this blogpost is not to go through every installation and configuration step. It will just give a global overview on how Orchestrator works, while mentioning some important and interesting settings.

How Does It Work?

Orchestrator is a go application (binaries, including rpm  and deb  packages are available for download).

It requires it’s own MySQL database as a backend server to store all information related to the Orchestrator managed database cluster topologies.

There should be at least one Orchestrator daemon, but it is recommended to run many Orchestrator daemons on different servers at the same time – they will all use the same backend database but only one Orchestrator is going to be “active” at any given moment in time. (You can check who is active under the Status  menu on the web interface, or in the database in the active_node  table.)

Using MySQL As Database Backend, Isn’t That A SPOF?

If the Orchestrator MySQL database is gone, it doesn’t mean the monitored MySQL clusters stop working. Orchestrator just won’t be able to control the replication topologies anymore. This is similar to how MHA works: everything will work but you can not perform a failover until MHA is back up again.

At this moment, it’s required to have a MySQL backend and there is no clear/tested support for having this in high availability (HA) as well. This might change in the future.

Database Server Installation Requirements

Orchestrator only needs a MySQL user with limited privileges (SUPER, PROCESS, REPLICATION SLAVE, RELOAD) to connect to the database servers. With those permissions, it is able to check the replication status of the node and perform replication changes if necessary. It supports different ways of replication: binlog file positions, MySQL&MariaDB GTID, Pseudo GTID and Binlog servers.

There is no need to install any extra software on the database servers.

Automatic Master Failure Recovery

One example of what Orchestrator can do is promote a slave if a master is down. It will choose the most up to date slave to be promoted.

Let’s see what it looks like:

In this test we lost rep1 (master) and Orchestrator promoted rep4  to be the new master, and started replicating the other servers from the new master.

With the default settings, if rep1 comes back rep4  is going to continue the replication from rep1. This behavior can be changed with the setting ApplyMySQLPromotionAfterMasterFailover:True in the configuration.

Command Line Interface

Orchestrator has a nice command line interface too. Here are some examples:

Print the topology:

> orchestrator -c topology -i rep1:3306 cli rep1:3306 [OK,5.6.27-75.0-log,ROW,>>] + rep2:3306 [OK,5.6.27-75.0-log,ROW,>>,GTID] + rep3:3306 [OK,5.6.27-75.0-log,ROW,>>,GTID] + rep4:3306 [OK,5.6.27-75.0-log,ROW,>>,GTID] + rep5:3306 [OK,5.6.27-75.0-log,ROW,>>,GTID]

Move a slave:

orchestrator -c relocate -i rep2:3306 -d rep4:3306

Print the topology again:

> orchestrator -c topology -i rep1:3306 cli rep1:3306 [OK,5.6.27-75.0-log,ROW,>>] + rep3:3306 [OK,5.6.27-75.0-log,ROW,>>,GTID] + rep4:3306 [OK,5.6.27-75.0-log,ROW,>>,GTID] + rep2:3306 [OK,5.6.27-75.0-log,ROW,>>,GTID] + rep5:3306 [OK,5.6.27-75.0-log,ROW,>>,GTID]

As we can see, rep2  now is replicating from rep4 .

Long Queries

One nice addition to the GUI is how it displays slow queries on all servers inside the replication tree. You can even kill bad queries from within the GUI.

Orchestrator Configuration Settings

Orchestrator’s daemon configuration can be found in /etc/orchestrator.conf.json. There are many configuration options, some of which we elaborate here:

  • SlaveLagQuery  – Custom queries can be defined to check slave lag.
  • AgentAutoDiscover  – If set to True , Orchestrator will auto-discover the topology.
  • HTTPAuthPassword  and HTTPAuthUser  –  Avoids everybody being able to access the Web GUI and change your topology.
  • RecoveryPeriodBlockSeconds  – Avoids flapping.
  • RecoverMasterClusterFilters  –  Defines which clusters should auto failover/recover.
  • PreFailoverProcesses  – Orchestrator will execute this command before the failover.
  • PostFailoverProcesses  – Orchestrator will execute this command after the failover.
  • ApplyMySQLPromotionAfterMasterFailover  –  Detaches the promoted slave after failover.
  • DataCenterPattern  – If there are multiple data centers, you can mark them using a pattern (they will get different colors in the GUI):
Limitations

While being a very feature-rich application, there are still some missing features and limitations of which we should be aware.

One of the key missing features is that there is no easy way to promote a slave to be the new master. This could be useful in scenarios where the master server has to be upgraded, there is a planned failover, etc. (this is a known feature request).

Some known limitations:
  • Slaves can not be manually promoted to be a master
  • Does not support multi-source replication
  • Does not support all types of parallel replication
  • At this moment, combining this with Percona XtraDB Cluster (Galera) is not supported
Is Orchestrator Your High Availability Solution?

In order to integrate this in your HA architecture or include in your fail-over processes you still need to manage many aspects manually, which can all be done by using the different hooks available in Orchestrator:

  • Updating application connectivity:
    • VIP handling,
    • Updating DNS
    • Updating Proxy server (MaxScale , HAProxy , ProxySQL…) connections.
  • Automatically setting slaves to read only to avoid writes happening on non-masters and causing data inconsistencies
  • Fencing (STONITH) of the dead master, to avoid split-brain in case a crashed master comes back online (and applications still try to connect to it)
  • If semi-synchronous replication needs to be used to avoid data loss in case of master failure, this has to be manually added to the hooks as well

The work that needs to be done is comparable to having a setup with MHA or MySQLFailover.

This post also doesn’t completely describe the decision process that Orchestrator takes to determine if a server is down or not. The way we understand it right now, one active Orchestrator node will make the decision if a node is down or not. It does check a broken node’s slaves replication state to determine if Orchestrator isn’t the only one losing connectivity (in which it should just do nothing with the production servers). This is already a big improvement compared to MySQLFailover, MHA or even MaxScale’s failoverscripts, but it still might cause some problems in some cases (more information can be found on Shlomi Noach’s blog).

Summary

The amount of flexibility and power and fun that this tool gives you with a very simple installation process is yet to be matched. Shlomi Noach did a great job developing this at Outbrain, Booking.com and now at GitHub.

If you are looking for MySQL Topology Manager, Orchestrator is definitely worth looking at.

Categories: MySQL

JSON document fast lookup with MySQL 5.7

MySQL Performance Blog - Mon, 2016-03-07 23:43

In this blog post, we’ll discuss JSON document fast lookup with MySQL 5.7.

Recently I attended Morgan Tocker’s talk on MySQL 5.7 and JSON at FOSDEM, and I found it awesome.

I learned some great information from the talk. Let me share one of them here: a very useful trick if you plan to store JSON documents in your MySQL database and want to retrieve the documents from some attribute’s values. So let’s look at how to do JSON document fast lookup with MySQL 5.7!

In this short example, I show you how we can speed up this type of search using JSON functions and virtual columns.

This our test table:

Table: test_features Create Table: CREATE TABLE `test_features` ( `id` int(11) NOT NULL AUTO_INCREMENT, `feature` json NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=206561 DEFAULT CHARSET=latin1 mysql> show table status like 'test_features'G *************************** 1. row *************************** Name: test_features Engine: InnoDB Version: 10 Row_format: Dynamic Rows: 171828 Avg_row_length: 1340 Data_length: 230326272 Max_data_length: 0 Index_length: 0 Data_free: 3145728 Auto_increment: 206561 Create_time: 2016-03-01 15:22:34 Update_time: 2016-03-01 15:23:20 Check_time: NULL Collation: latin1_swedish_ci Checksum: NULL Create_options: Comment:

We can see the data length is almost 230M:

+--------------------+--------+-------+-------+-------+------------+---------+ | TABLE | ENGINE | ROWS | DATA | IDX | TOTAL SIZE | IDXFRAC | +--------------------+--------+-------+-------+-------+------------+---------+ | json.test_features | InnoDB | 0.17M | 0.21G | 0.00G | 0.21G | 0.00 | +--------------------+--------+-------+-------+-------+------------+---------+ -rw-r----- 1 mysql mysql 228M Mar 1 15:23 /var/lib/mysql/json/test_features.ibd

As an example here is one record (the data is coming from https://github.com/zemirco/sf-city-lots-json):

{ "type": "Feature", "geometry": { "type": "Polygon", "coordinates": [ [ [ -122.41983177253881, 37.80720512387136, 0 ], ... [ -122.41983177253881, 37.80720512387136, 0 ] ] ] }, "properties": { "TO_ST": "600", "BLKLOT": "0010001", "STREET": "BEACH", "FROM_ST": "600", "LOT_NUM": "001", "ST_TYPE": "ST", "ODD_EVEN": "E", "BLOCK_NUM": "0010", "MAPBLKLOT": "0010001" } }

Now let’s try to find all records where the street is “BEACH”. “Street” is part of the array attribute properties.

mysql> SELECT count(*) FROM test_features WHERE feature->"$.properties.STREET" = 'BEACH'; +----------+ | count(*) | +----------+ | 208 | +----------+ 1 row in set (0.21 sec) mysql> explain SELECT count(*) FROM test_features WHERE feature->"$.properties.STREET" = 'BEACH'G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: test_features partitions: NULL type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 171828 filtered: 100.00 Extra: Using where 1 row in set, 1 warning (0.00 sec)

As you can see, we perform a full table scan to achieve this.

With MySQL, we have the possibility of using virtually generated columns. Let’s create one for the streets:

mysql> ALTER TABLE test_features ADD COLUMN street VARCHAR(30) GENERATED ALWAYS AS (json_unquote(json_extract(`feature`,'$.properties.STREET'))) VIRTUAL;

I use “json_unquote()” to avoid to add the JSON string quotes in the column, and later in the index.

You can verify the size of the table on disk, and you will see this doesn’t increase (as it’s a virtual column).

Even if we can now use the “street” column in the search, that won’t help. We still need to add an index on it:

mysql> ALTER TABLE test_features ADD KEY `street` (`street`);

And now we can see that the size is larger, because we have added the size of the index:

-rw-r----- 1 mysql mysql 232M Mar 1 15:48 /var/lib/mysql/json/test_features.ibd

Now we can try to run the query like this:

mysql> SELECT count(*) FROM test_features WHERE street = 'BEACH'; +----------+ | count(*) | +----------+ | 208 | +----------+ 1 row in set (0.00 sec)

Let’s have a look at the Query Execution Plan:

mysql> explain SELECT count(*) FROM test_features WHERE street = 'BEACH'G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: test_features partitions: NULL type: ref possible_keys: street key: street key_len: 33 ref: const rows: 208 filtered: 100.00 Extra: Using index

And finally we can verify this in the statistics available in sys schema:

mysql> select * from sys.schema_index_statistics where table_name='test_features'G *************************** 1. row *************************** table_schema: json table_name: test_features index_name: street rows_selected: 208 select_latency: 72.59 us rows_inserted: 0 insert_latency: 0 ps rows_updated: 0 update_latency: 0 ps rows_deleted: 0 delete_latency: 0 ps *************************** 2. row *************************** table_schema: json table_name: test_features index_name: PRIMARY rows_selected: 0 select_latency: 0 ps rows_inserted: 0 insert_latency: 0 ps rows_updated: 0 update_latency: 0 ps rows_deleted: 0 delete_latency: 0 ps 2 rows in set (0.00 sec)

As you can see, this is very fast. If you already know how you want to retrieve data out of your JSON document, it’s very easy to add such indexes in MySQL.

Categories: MySQL

Percona Toolkit 2.2.17 is now available

MySQL Performance Blog - Mon, 2016-03-07 16:31

Percona is pleased to announce the availability of Percona Toolkit 2.2.17.  Released March 7, 2016. Percona Toolkit is a collection of advanced command-line tools to perform a variety of MySQL server and system tasks that are too difficult or complex for DBAs to perform manually. Percona Toolkit, like all Percona software, is free and open source.

This release is the current GA (Generally Available) stable release in the 2.2 series. It includes multiple bug fixes for pt-table-checksum with better support for Percona XtraDB Cluster, various other fixes, as well as MySQL 5.7 general compatibility. Full details are below. Downloads are available here and from the Percona Software Repositories.

New Features:

  • Percona Toolkit 2.2.17 has implemented general compatibility with MySQL 5.7 tools, documentation and test suite.

Bugs Fixed:

  • Bug 1523685: pt-online-schema-change invalid recursion method where a comma was interpreted as the separation of two DSN methods has been fixed.
  • Bugs 1480719 and 1536305: The current version of Perl on supported distributions has implemented stricter checks for arguments provided to sprintf. This could cause warnings when pt-query-digest and pt-table-checksum were being run.
  • Bug 1498128: pt-online-schema-change would fail with an error if the table being altered has foreign key constraints where some start with an underscore and some don’t.
  • Bug 1336734: pt-online-schema-change has implemented new --null-to-non-null flag which can be used to convert NULL columns to NOT NULL.
  • Bug 1362942: pt-slave-restart would fail to run on MariaDB 10.0.13 due to a different implementation of GTID.
  • Bug 1389041: pt-table-checksum had a high likelihood to skip a table when row count was around chunk-size * chunk-size-limit value. To address this issue a new --slave-skip-tolerance option has been implemented.
  • Bug 1506748: pt-online-schema-change could not set the SQL_MODE by using the --set-vars option, preventing some use case schema changes that require it.
  • Bug 1523730: pt-show-grants didn’t sort the column-level privileges.
  • Bug 1526105: pt-online-schema-change would fail if used with –no-drop-old-table option after ten times. The issue would arise because there was an accumulation of tables that have already had their names extended, the code would retry ten times to append an underscore, each time finding an old table with that number of underscores appended.
  • Bug 1529411: pt-mysql-summary was displaying incorrect information about Fast Server Restarts for Percona Server 5.6.
  • pt-stalk shell collect module was confusing the new MySQL variable binlog_error_action with the log_error variable.

Details of the release can be found in the release notes and the 2.2.17 milestone at Launchpad. Bugs can be reported on the Percona Toolkit launchpad bug tracker.

Categories: MySQL

Percona Server 5.6.29-76.2 is now available

MySQL Performance Blog - Mon, 2016-03-07 15:40

Percona is glad to announce the release of Percona Server 5.6.29-76.2 on March 7, 2016. Download the latest version from the Percona web site or from the Percona Software Repositories.

Based on MySQL 5.6.29, including all the bug fixes in it, Percona Server 5.6.29-76.2 is the current GA release in the Percona Server 5.6 series. Percona Server is open-source and free – this is the latest release of our enhanced, drop-in replacement for MySQL. Complete details of this release can be found in the 5.6.29-76.2 milestone on Launchpad.

Bugs Fixed:

  • With Expanded Fast Index Creation enabled, DDL queries involving InnoDB temporary tables would cause later queries on the same tables to produce warnings that their indexes were not found in the index translation table. Bug fixed #1233431.
  • Package upgrade on Ubuntu would run mysql_install_db even though data directory already existed. Bug fixed #1457614.
  • Package upgrade on Ubuntu and Debian could reset the GTID number sequence when post-install script was restarting the service. Bug fixed #1507812.
  • Starting MySQL with systemctl would fail with timeout if the socket was specified with a custom path. Bug fixed #1534825.
  • Write-heavy workload with a small buffer pool could lead to a deadlock when free buffers are exhausted. Bug fixed #1521905.
  • libjemalloc.so.1 was missing from binary tarball. Bug fixed #1537129.
  • mysqldumpslow script has been removed because it was not compatible with Percona Server extended slow query log format. Please use pt-query-digest from Percona Toolkit instead. Bug fixed #856910.
  • When cmake/make/make_binary_distribution workflow was used to produce binary tarballs it would produce tarballs with mysql-... naming instead of percona-server-.... Bug fixed #1540385.
  • Cardinality of partitioned TokuDB tables became inaccurate after the changes introduced by TokuDB Background ANALYZE TABLE feature in Percona Server 5.6.27-76.0. Bug fixed #925.
  • Running the TRUNCATE TABLE while TokuDB Background ANALYZE TABLE is enabled could lead to a server crash once analyze job tries to access the truncated table. Bug fixed #938.
  • Added proper memory cleanup if for some reason a TokuDB table is unable to be opened from a dead closed state. This prevents an assertion from happening the next time the table is attempted to be opened. Bug fixed #917.

Other bugs fixed: #898, #1521120 and #1534246.

Release notes for Percona Server 5.6.29-76.2 are available in the online documentation. Please report any bugs on the launchpad bug tracker .

Categories: MySQL

How to Mitigate DROWN CVE-2016-0800

MySQL Performance Blog - Fri, 2016-03-04 23:50

This blog post will discuss how to Mitigate DROWN CVE-2016-0800.

Unless you’ve been living in a cave you’ll have heard (or likely to hear about soon) the drown attack. From the Red Hat site:

“A padding oracle flaw was found in the Secure Sockets Layer version 2.0 (SSLv2) protocol. An attacker can potentially use this flaw to decrypt RSA-encrypted cipher text from a connection using a newer SSL/TLS protocol version, allowing them to decrypt such connections. This cross-protocol attack is publicly referred to as DROWN.

Find out more about CVE-2016-0800 from the MITRE CVE dictionary dictionary and NIST NVD.”

The following graphic should help explain the vulnerability:

In short, disable SSLv2 if you do not need it  (similar to the way SSLv3 was disabled due to POODLE).

So how about those services?

  • MySQL uses TLS1.0 for versions < 5.7.10
  • MySQL uses a configuration TLS version when using >= 5.7.10
  • MongoDB uses a configuration variable for the TLS for version when using >= 3.0.7

Please respond in the comments with any questions!

Categories: MySQL

Virtual columns in MySQL and MariaDB

MySQL Performance Blog - Fri, 2016-03-04 20:14

In this blog post, we’ll compare virtual columns in MySQL and MariaDB.

Virtual columns are one of my top features in MySQL 5.7: they can store a value that is derived from one or several other fields in the same table in a new field. It’s a very good way to build a functional index. This feature has been available in MariaDB for some time, so let’s compare the two and see if they are equivalent. We’ll look at different aspects for this comparison.

Documentation

The MariaDB documentation is very easy to find.

Finding the documentation for virtual columns in 5.7 is a bit more challenging. Here is the best link I’ve found.

The MariaDB documentation isn’t  clear when you should use a persistent column rather than a virtual one. If you read carefully, you’ll see that indexes are only supported on persistent columns, but the pros and cons of both options could have been better presented.

For MySQL there is one interesting paragraph listing the potential use cases for stored columns and virtual columns. This paragraph is not super visible, but the gist of it is “always use a virtual column except if the value is too expensive to evaluate on the fly.” Note that you don’t need to use a stored column to index it in 5.7.

Syntax

Creating a virtual column is very similar in both systems:

ALTER TABLE sbtest1 ADD reverse_pad char(60) GENERATED ALWAYS AS (reverse(pad)) VIRTUAL;

Note that NOT NULL is not supported with MariaDB while it’s allowed in 5.7:

# MariaDB 10.0 MariaDB [db1]> ALTER TABLE sbtest1 ADD reverse_pad char(60) GENERATED ALWAYS AS (reverse(pad)) VIRTUAL NOT NULL; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'NOT NULL' at line 1 # 5.7 mysql> ALTER TABLE sbtest1 ADD reverse_pad char(60) GENERATED ALWAYS AS (reverse(pad)) VIRTUAL NOT NULL; Query OK, 0 rows affected (0.07 sec) Records: 0 Duplicates: 0 Warnings: 0

When creating a materialized virtual column, the syntax is unfortunately not identical: MariaDB has PERSISTENT columns while 5.7 has STORED columns. It doesn’t look like a big deal, but it’s another item to add to a check list before a migration.

Adding a virtual column

# 5.7 ALTER TABLE sbtest1 ADD reverse_pad char(60) GENERATED ALWAYS AS (reverse(pad)) VIRTUAL NOT NULL; Query OK, 0 rows affected (0.03 sec)

Great! Creating the column is only a metadata change, so it runs nearly instantly whatever the size of the table is.

With MariaDB, it’s quite different:

# MariaDB 10.0 ALTER TABLE sbtest1 ADD reverse_pad char(60) GENERATED ALWAYS AS (reverse(pad)) VIRTUAL; Query OK, 0 rows affected (7 min 8.50 sec)

Yes, a full table rebuild was needed. And if we are running some sysbench insert workload, we can easily see that this is not an online rebuild – for around 1/3 of the schema change, writes were stalled:

Indexing

That’s probably one of the most striking differences: with MariaDB a column must be PERSISTENT for it to be indexed. This is not necessary in MySQL 5.7. The only situation when an indexed column in 5.7 must be STORED is when it’s a primary key.

When it comes to adding an index on several columns, some being regular columns and some being virtual columns, both versions allow this action:

# 5.7 mysql> ALTER TABLE sbtest1 ADD INDEX k_rev (k, reverse_pad); Query OK, 0 rows affected (2 min 38.14 sec) # MariaDB 10.0 MariaDB [db1]> ALTER TABLE sbtest1 ADD INDEX k_rev (k, reverse_pad); Query OK, 10187085 rows affected (4 min 43.76 sec)

The big difference though is that adding the index is performed online in 5.7, while it’s a blocking operation in MariaDB 10.

Conclusion

While at first sight MariaDB 10 and MySQL 5.7 offer very similar features with virtual columns, the reality is quite different: for virtual columns in MySQL and MariaDB the syntax is not exactly the same, adding a virtual column is not done the same way and indexing sets different constraints. The MySQL 5.7 implementation seems more polished for a production usage with large tables and/or heavy traffic.

Categories: MySQL

Percona Server 5.5.48-37.8 is now available

MySQL Performance Blog - Fri, 2016-03-04 15:18


Percona is glad to announce the release of Percona Server 5.5.48-37.8 on March 4, 2016. Based on MySQL 5.5.48, including all the bug fixes in it, Percona Server 5.5.48-37.8 is now the current stable release in the 5.5 series.

Percona Server is open-source and free. Details of the release can be found in the 5.5.48-37.8 milestone on Launchpad. Downloads are available here and from the Percona Software Repositories.

Bugs Fixed:

  • With Expanded Fast Index Creation enabled, DDL queries involving InnoDB temporary tables would cause later queries on the same tables to produce warnings that their indexes were not found in the index translation table. Bug fixed #1233431.
  • Package upgrade on Ubuntu would run mysql_install_db even though data directory already existed. Bug fixed #1457614.
  • Starting MySQL with systemctl would fail with a timeout if the socket was specified with a custom path. Bug fixed #1534825.
  • mysqldumpslow script has been removed because it was not compatible with Percona Server extended slow query log format. Please use pt-query-digest from Percona Toolkit instead. Bug fixed #856910.
  • When cmake/make/make_binary_distribution workflow was used to produce binary tarballs it would produce tarballs with mysql-... naming instead of percona-server-.... Bug fixed #1540385.

Other bugs fixed: #1521120 and #1534246.

Release notes for Percona Server 5.5.48-37.8 are available in our online documentation. Bugs can be reported on the launchpad bug tracker.

Categories: MySQL

Deals of a “Live” time: $5 Percona Live Keynote Passes!

MySQL Performance Blog - Thu, 2016-03-03 17:55

The Percona Live Data Performance Conference 2016 in Santa Clara, California is just around the corner: April 18-21. We’re busy getting things ready to make sure everybody gets the most out of their time there. As part of that, we have some news and a couple of outstanding offers for you! $5 Percona Live keynote passes and $101 101 crash courses.

Percona Live MySQL Conference 2016 Keynote Speakers

We are extremely pleased with this year’s keynote speakers. We have a wide assortment of industry leaders and experts presenting a great set of topics, as well as special guest speaker Bill Nye!

The schedule is as follows:

Day 1

9:00 AM – Peter Zaitsev, Percona, opens Percona Live Data Performance Conference 2016.

9:20 AM – Chad Jones, Deep Information Science will present “Transcending database tuning problems: How machine learning helps DBAs play more ping pong,” which will show how unsupervised machine learning based on resource, workload and information modeling can predictively and continuously tune databases.

9:50 AM – Bill Nye, Scientist and Media Personality, will present “Things to Keep a Nye On,” where he’ll discuss the importance of science, critical thinking, and reason.

Day 2

9:00 AM – Patrick McFadin, DataStax, will present “Take back the power in your cloud applications with Apache Cassandra,” where he’ll talk about Apache Cassandra cloud applications.

9:25 AM – Experts from Deep Information Science, RocksDB, Red Hat, Intel and Percona will present “Data in the Cloud Keynote Panel: Cloudy with a chance of running out of disk space?  Or Sunny times ahead?” where they will discuss how new technologies, revamped products and a never ending stream of idea’s aim to improve the performance and manageability of cloud-based data, and where the industry is heading.

9:50 AM – Tomas Ulin, Oracle, will present “MySQL: Combining SQL and NoSQL,” which will explore how MySQL can deliver on the promises of NoSQL, while keeping all the proven benefits of SQL.

10:15 AM – Mark Callaghan, RocksDB, will present “MyRocks, MongoRocks and RocksDB,” and explain how and why MyRocks provides better performance, efficiency and compression using real and synthetic workloads

Day 3

9:00 AM – Peter Zaitsev, Percona, will present “Winning with Open Source Databases,” and will demonstrate how Percona is a true open source partner that helps you optimize your database performance to better run your business.

9:30 AM – Anurag Gupta, Amazon Web Services, will present “AWS Big Data Services:  Reimagining Big Data Processing in the Cloud,” which will provide an under-the-hood view of some of the most popular Big Data services at AWS including Amazon DynamoDB, Amazon Redshift, Amazon EMR and Amazon Aurora.

9:55 AM – Community Award Ceremony

$5 Keynote Passes

To help make the keynotes and the community events accessible to the greatest number of community members, we are once again offering  $5 Percona Live keynote passes for the Percona Live Data Performance Conference 2016. A keynote pass provides access to the keynote addresses, Birds of a Feather sessions, the exhibit floor, and the Community Networking Reception on Thursday night. The first 100 people who register for an Expo-Only pass (new registrations only) using the discount code “KEY” will be able to register for just $5.

$101 101 Passes

For a limited time, you can get access to the 101 Crash Courses for only $101!

Percona Live is once again hosting Crash Courses for developers, systems administrators, and other technical resources. This year, we’ve compacted the training into a single day, and are offering two options: MySQL 101 and MongoDB 101!

If you use code Single101, you can get either the MySQL or MongoDB crash course track for $101. If you want to get both tracks for $202, use code Double202.

This deal expires soon, so reserve your spot now!

End of Advanced Rate Pricing

Just a quick note: advanced rate pricing is set to expire March 6th! If you haven’t secured your reservation, do now before rates go up! It’s a fantastic deal, but it won’t last for much longer.

Reserve your place here.

Categories: MySQL
Syndicate content