Five reasons why vertical scalability matters

The latest benchmarks show that MySQL 5.7 is now able to scale to 60 cores, which is quite an incredible feat when you compare to the 4-8 core scaling of MySQL 5.1 just a few years ago. These improvements are the result of a lot of heavy lifting to reorganize internal locking structures, and I have an earlier blog post on what is a mutex anyway? which may help serve as an introduction.

While I consider horizontal scaling and projects like MySQL Fabric to be very important, it should be stated that horizontal and vertical scaling are really orthogonal choices. That is to say that a given database technology should ideally support both options, and today I wanted to zoom in on some of the advantages I see with being able to scale vertically:

  1. Having more cores offers more consistent performance. Think of a single CPU as like shopping at a convenience store with one person on the checkout. The experience is very good when there is nobody else in line, but it degrades very quickly when just a couple of shoppers are lined up before you.

    To add to that, even if you know that there are an average of 60 customers per hour, you can not expect them to arrive at an equal distribution of one customer per minute. What usually happens is a more random pattern (see Poisson distribution) of arrivals.

    The solution to this problem is to have more staff available at the checkout. The more staff available, the less variance in the time that it takes to serve a customer. Similarly, having multiple CPUs means that query times will degrade much nicer as there are subtle spikes in load.

  2. Simplified debugging and performance characteristics. For some applications, where the lifetime growth requirements can be answered by a single server (or single master, multiple HA slaves), having a single primary server can be beneficial.

    The number of transactions/second that a single-server can now respond to is also much higher than it used to be. Dimitri’s 5.7 Sysbench OLTP_RW shows 15K transactions/second (or over 500K point select queries/second), and prematurely introducing architectural-complexity through horizontal scaling may increase the effort required to troubleshoot problems. In some cases it may also artificially prevent desirable features such as strong consistency (ACID).

  3. Good insurance for the unknown. Some applications grow in unexpected ways, and being able to scale up offers a great upgrade path that is less likely to change performance characteristics than horizontal scaling, and require fewer application changes.

    Often the cost of higher-end commodity hardware is less than that of custom-development time. I once experienced the case where a legacy application that was planned for decommission started having performance problems. Paying for the biggest EC2 instance type was worth it for a few months, and we probably would have paid more if other options were available (they were not at the time, but are now).

  4. Increased efficiency at scale. That is to say that even with automation, it is easier to manage 1000 16-core instances than it is to manage 16000 single core instances.

  5. An alternative consolidation strategy to virtualization. For some organizations, backing up and maintaining many small database servers presents operational complexity, even when the underlying servers lie on virtualized hardware. Having a larger single database instance with many database schemas can offer an alternative that may be easier to manage.

    I concede that there is some functionality missing on the MySQL-side to truly realize this potential, since it would be nice to be able to set more fine-grained quotas per application and limit the ability to accidentally Denial of Service other applications. However, some headway has been made with performance_schema now able to instrument things that were previously not possible. Most notably, in MySQL 5.7, memory can now instrumented per user.

Conclusion

I wanted to close with an example of how the horizontal and vertical should work together. Lets say that you operate a SaaS application with millions of users, and have the option to either massively-shard or use a single larger server. Both options may be sub-optimal:

Vertical Scaling Horizontal Scaling

PRO: Having vertical scalability allows each user to have some burstable performance that can be absorbed by larger hardware.

CON: It is possible that an extremely busy users will impact all other users, creating an all-eggs-in-one-basket scenario.

PRO: Multiple horizontal shards allows some natural fencing where the extreme spikes can be contained to group of users. This assumes that indivual users do not need cross-shard queries, otherwise it hinges on my point above under “Simplified debugging and performance characteristics”.

CON: If the application is excessively horizontally scaled, some of the busier users may have a bad experience as their shard becomes overloaded too quickly. Quite often these busy users can be the ones with the most revenue associated to them.

By horizontally scaling across many vertically scalable servers, you can get closer to get the best of both worlds.

Installing MySQL 5.6 on Ubuntu 14.04 (Trusty Tahr)

Ubuntu 14.04 defaults to MySQL 5.5, but also has MySQL 5.6 available for installation from the universe archive. Installing 5.6 is as easy as specifying the version, apt-get install mysql-server-5.6:


root@ubuntu1404:~# apt-get install mysql-server-5.6
Reading package lists... Done
Building dependency tree
Reading state information... Done
The following extra packages will be installed:
libaio1 libdbd-mysql-perl libdbi-perl libhtml-template-perl libmysqlclient18 libterm-readkey-perl mysql-client-5.6 mysql-client-core-5.6 mysql-common
mysql-common-5.6 mysql-server-core-5.6
Suggested packages:
libclone-perl libmldbm-perl libnet-daemon-perl libplrpc-perl libsql-statement-perl libipc-sharedcache-perl mailx tinyca
The following NEW packages will be installed:
libaio1 libdbd-mysql-perl libdbi-perl libhtml-template-perl libmysqlclient18 libterm-readkey-perl mysql-client-5.6 mysql-client-core-5.6 mysql-common
mysql-common-5.6 mysql-server-5.6 mysql-server-core-5.6
0 upgraded, 12 newly installed, 0 to remove and 0 not upgraded.
Need to get 21.5 MB of archives.
After this operation, 164 MB of additional disk space will be used.
Do you want to continue? [Y/n] Y
Get:1 http://mirrors.digitalocean.com/ubuntu/ trusty/main libaio1 amd64 0.3.109-4 [6,364 B]
Get:2 http://mirrors.digitalocean.com/ubuntu/ trusty/main mysql-common all 5.5.35+dfsg-1ubuntu1 [14.1 kB]
Get:3 http://mirrors.digitalocean.com/ubuntu/ trusty/main libmysqlclient18 amd64 5.5.35+dfsg-1ubuntu1 [593 kB]
Get:4 http://mirrors.digitalocean.com/ubuntu/ trusty/main libdbi-perl amd64 1.630-1 [879 kB]
Get:5 http://mirrors.digitalocean.com/ubuntu/ trusty/main libdbd-mysql-perl amd64 4.025-1 [99.3 kB]
Get:6 http://mirrors.digitalocean.com/ubuntu/ trusty/main libterm-readkey-perl amd64 2.31-1 [27.4 kB]
Get:7 http://mirrors.digitalocean.com/ubuntu/ trusty/universe mysql-client-core-5.6 amd64 5.6.16-1~exp1 [4,084 kB]
Get:8 http://mirrors.digitalocean.com/ubuntu/ trusty/universe mysql-client-5.6 amd64 5.6.16-1~exp1 [5,591 kB]
Get:9 http://mirrors.digitalocean.com/ubuntu/ trusty/universe mysql-server-core-5.6 amd64 5.6.16-1~exp1 [4,333 kB]
Get:10 http://mirrors.digitalocean.com/ubuntu/ trusty/universe mysql-server-5.6 amd64 5.6.16-1~exp1 [5,839 kB]
Get:11 http://mirrors.digitalocean.com/ubuntu/ trusty/main libhtml-template-perl all 2.95-1 [65.5 kB]
Get:12 http://mirrors.digitalocean.com/ubuntu/ trusty/universe mysql-common-5.6 all 5.6.16-1~exp1 [14.9 kB]
Fetched 21.5 MB in 5s (3,760 kB/s)
Preconfiguring packages ...
Selecting previously unselected package libaio1:amd64.
(Reading database ... 59014 files and directories currently installed.)
Preparing to unpack .../libaio1_0.3.109-4_amd64.deb ...
Unpacking libaio1:amd64 (0.3.109-4) ...
Selecting previously unselected package mysql-common.
Preparing to unpack .../mysql-common_5.5.35+dfsg-1ubuntu1_all.deb ...
Unpacking mysql-common (5.5.35+dfsg-1ubuntu1) ...
Selecting previously unselected package libmysqlclient18:amd64.
Preparing to unpack .../libmysqlclient18_5.5.35+dfsg-1ubuntu1_amd64.deb ...
Unpacking libmysqlclient18:amd64 (5.5.35+dfsg-1ubuntu1) ...
Selecting previously unselected package libdbi-perl.
Preparing to unpack .../libdbi-perl_1.630-1_amd64.deb ...
Unpacking libdbi-perl (1.630-1) ...
Selecting previously unselected package libdbd-mysql-perl.
Preparing to unpack .../libdbd-mysql-perl_4.025-1_amd64.deb ...
Unpacking libdbd-mysql-perl (4.025-1) ...
Selecting previously unselected package libterm-readkey-perl.
Preparing to unpack .../libterm-readkey-perl_2.31-1_amd64.deb ...
Unpacking libterm-readkey-perl (2.31-1) ...
Selecting previously unselected package mysql-client-core-5.6.
Preparing to unpack .../mysql-client-core-5.6_5.6.16-1~exp1_amd64.deb ...
Unpacking mysql-client-core-5.6 (5.6.16-1~exp1) ...
Selecting previously unselected package mysql-client-5.6.
Preparing to unpack .../mysql-client-5.6_5.6.16-1~exp1_amd64.deb ...
Unpacking mysql-client-5.6 (5.6.16-1~exp1) ...
Selecting previously unselected package mysql-server-core-5.6.
Preparing to unpack .../mysql-server-core-5.6_5.6.16-1~exp1_amd64.deb ...
Unpacking mysql-server-core-5.6 (5.6.16-1~exp1) ...
Processing triggers for man-db (2.6.7.1-1) ...
Setting up mysql-common (5.5.35+dfsg-1ubuntu1) ...
Selecting previously unselected package mysql-server-5.6.
(Reading database ... 59386 files and directories currently installed.)
Preparing to unpack .../mysql-server-5.6_5.6.16-1~exp1_amd64.deb ...
Unpacking mysql-server-5.6 (5.6.16-1~exp1) ...
Selecting previously unselected package libhtml-template-perl.
Preparing to unpack .../libhtml-template-perl_2.95-1_all.deb ...
Unpacking libhtml-template-perl (2.95-1) ...
Selecting previously unselected package mysql-common-5.6.
Preparing to unpack .../mysql-common-5.6_5.6.16-1~exp1_all.deb ...
Unpacking mysql-common-5.6 (5.6.16-1~exp1) ...
Processing triggers for ureadahead (0.100.0-16) ...
ureadahead will be reprofiled on next reboot
Processing triggers for man-db (2.6.7.1-1) ...
Setting up libaio1:amd64 (0.3.109-4) ...
Setting up libmysqlclient18:amd64 (5.5.35+dfsg-1ubuntu1) ...
Setting up libdbi-perl (1.630-1) ...
Setting up libdbd-mysql-perl (4.025-1) ...
Setting up libterm-readkey-perl (2.31-1) ...
Setting up mysql-client-core-5.6 (5.6.16-1~exp1) ...
Setting up mysql-client-5.6 (5.6.16-1~exp1) ...
Setting up mysql-server-core-5.6 (5.6.16-1~exp1) ...
Setting up mysql-server-5.6 (5.6.16-1~exp1) ...
2014-04-21 11:50:07 0 [Warning] Using unique option prefix key_buffer instead of key_buffer_size is deprecated and will be removed in a future release. Please use the full name instead.
2014-04-21 11:50:07 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
mysql start/running, process 2355
Setting up libhtml-template-perl (2.95-1) ...
Setting up mysql-common-5.6 (5.6.16-1~exp1) ...
Processing triggers for libc-bin (2.19-0ubuntu6) ...
Processing triggers for ureadahead (0.100.0-16) ...

And then:


root@ubuntu1404:~# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 45
Server version: 5.6.16-1~exp1 (Ubuntu)

Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> select version();
+---------------+
| version() |
+---------------+
| 5.6.16-1~exp1 |
+---------------+
1 row in set (0.00 sec)

Update: A slightly more detailed tutorial for this is available here. Thank you internet!

MySQL 5.6.17 Community Release Notes

Thank you to the MySQL Community, on behalf of the MySQL team @ Oracle. Your bug reports, testcases and patches have helped create a better MySQL 5.6.17.

In particular:

  • Thanks to Anthony Pong for reporting a confusing error message when mysql_install_db could not locate the required Perl modules. Bug #69844.
  • Thanks to Jervin Real for reporting a recently introduced performance regression with compressed InnoDB tables. Bug #71436.
  • Thanks to Laurynas Biveinis for reporting a race condition in InnoDB on shutdown. Bug #70430.
  • Thanks to Jervin Real for reporting that innodb_data_file_path could not be specified in kilobytes, as was indicated in the manual. Bug #68282.
  • Thanks to Elena Stepanova for reporting that InnoDB miscalculates auto-increment after changing auto_increment_increment. Bug #65225.
  • Thanks to qinglin zhang for reporting a memory leak in the slave sql thread, plus providing a suggested patch. While we did not end up using qinglin’s patch, we value the contribution. Bug #71197.
  • Thanks to 徹 赤松 for reporting that SHOW SLAVE STATUS incorrectly reported the master’s SSL information. Bug #70866.
  • Thanks to Yoshinori Matsunobu for reporting an issue where a slave can’t continue replication after the master’s crash recovery Bug #70669
  • Thanks to Erik Hoekstra for pointing out that using slave-parallel-workers may result in the error log being spammed with warnings. Bug #68429.
  • Thanks to Ovais Tariq for identifying that slaves with an additional auto-increment were not updated correctly. Bug #69680.
  • Thanks to Simon Mudd for suggesting that modifications to performance_schema tables should not be replicated. Bug #67159.
  • Thanks to Miguel Angel Nieto for identifying that temporary files created by binlog cache are not cleaned up. Bug #66237.
  • Thanks to hickey liu for reporting a race-condition in semi-sync replication. Bug #66411.
  • Thanks to Yoshinori Matsunobu for reporting excessive mutex contention in semi-sync replication. Bug #70218.
  • Thanks to honza horak for suggesting that mysqld –help should exit with a zero error code. Bug #70058.
  • Thanks to Jonathan Weaver for identifying that MySQL community edition client programs could not connect to MySQL enterprise servers with SSL enabled. Bug #68788.
  • Thanks to Jørgen Thomsen for reporting that mysqldump did not support the secure-auth parameter. Bug #69051.
  • Thanks to Raghavendra Prabhu for reporting an assertion when running Random Query Generator (RQG). Bug #69969.
  • Thanks to Tim McLaughlin for reporting that wrong results could be returned in a SELECT DISTINCT…GROUP BY query. We also thank Tim for reducing the bug to a minimal testcase. Bug #70657.
  • Thanks to Hartmut Holzgraefe for suggesting improvements to warnings written to the error log for invalid collations. Bug #68144.
  • Thanks to Ralf Adams for reporting that wrong results could be returned when using ALL() and GROUP BY. Ralf also provided a minimal testcase, which was useful in reproducing the issue. Bug #71244.
  • Thanks to Elena Stepanova for identifying that unquoted file names for variable values are accepted but parsed incorrectly. Bug #69703.
  • Thanks to Valeriy Kravchuk for suggesting that innodb_ft_result_search_limit should have a predictable maximum value. Bug #71554.

Thank you again to all the community contributors listed above. In particular, the MySQL team would like to call out the names that appear more than once in this release:

Jervin Real (2), Yoshinori Matsunobu (2), Elena Stepanova (2)

If I missed a name here, please let me know!

- Morgan

Percona Live Highlights

Last week marked my first April-MySQL-Conf since 2009, and now that I’m back home I wanted to reflect on some of my personal highlights.

percona-live-keynote
Photo Credit: @miguel2angel

  • Tomas’ keynote on Wednesday morning was awesome. It felt great to be part of the team at Oracle that announced a release 2x faster than MySQL 5.6 and 3x faster than MySQL 5.5. The Parser+Optimizer+InnoDB GIS labs release is also a great sign of things to come.
  • Hosting the meet the MySQL team @ Oracle BOF Wednesday night, and answering community questions with my colleagues on our engineering team. It was a pleasant surprise to have Mark Callaghan (Facebook) and Peter Zaitsev (Percona) come and join in as well.
  • Attending some of my colleague’s sessions and collating feedback from attendees. In Luis Soares’ what’s new in replication session, we received a couple of great suggestions which we will certainly investigate in more detail.
  • Learning how to use new tools. For me, this will be the R programming language (something I had heard of many times before, but never taken a closer look at). It was also great to see MySQL Workbench’s Visual Explain in Bill Karwin’s query optimization talk.
  • Seeing our ecosystem expand. The two new technologies that I am most excited about are VividCortex and WebScaleSQL. With WebScaleSQL, it’s a great endorsement to see it based on MySQL 5.6.

I’m back home this week, then on to vacation next week!