]]>
]]>

You are here

Latest MySQL Performance Blog posts

Subscribe to Latest MySQL Performance Blog posts feed
Percona's MySQL & InnoDB performance and scalability blog
Updated: 1 hour 26 min ago

MongoDB / TokuMX plugin for LinkBench (Part 1)

8 hours 54 min ago

There’s no benchmark for how life’s “supposed” to happen. There is no ideal world for you to wait around for. The world is always just what it is now, it’s up to you how you respond to it.”
― Isaac Marion, Warm Bodies

At one time or another, most of us have heard some version of this question: “Sure the system does fine in the benchmarks, but can it perform in production?”. Benchmark software developers have tried to address this issue in their systems. Features such as configurable workloads and scripting interfaces help to tailor benchmarks to various scenarios, but still require an expert to properly implement them.

A brief overview of LinkBench

The LinkBench benchmark was developed by Tim Armstrong with the guidance and help of a team from Facebook during his internship there. It takes a different approach to the challenge of simulating the real world. LinkBench is designed from the ground up as a replica of the data operations of Facebook’s social graph. By implementing an identical data model along with business methods and workloads directly proportionate the those used in the production social graph, LinkBench can effectively duplicate the data load that will be seen in a production social networking application.

Anatomy of a Social Graph – The Data Model

With this deceptively simple schema, a very robust application can be built.

The nodetable defines an object or end-point within the social graph. Examples of nodes include users, posts, comments, replies, albums, photos, groups, etc… The node’s type attribute is the magic that determines what the node represents and the data attribute contains the object itself (up to 16mb).

The linktable is a generic association or bridge table allowing any two nodes to be associated in a specific way. The secret sauce in this case, is the link_type attribute which represents a specific relationship between any two nodes.  Examples of links include users being friends, a user liking a post of another user, a user that is tagged in another user’s photo and so on.

The third table, counttable is very important for performance and scalability in a social network. It maintains counts of a given link type for a node. Counts are transactionally updated whenever an operation that could potentially alter the count occurs. This small investment in the form of an additional write operation pays off by allowing for quick access to the number of likes, shares, posts, friends and other associations between nodes. Without the count table, the application would have to continuously query the database to retrieve up-to-date count information for various relationships creating a tremendous amount of system load.

The Social Graph Controller

As you can see, the model is very simple. The real magic in the social graph lies in the controller.  LinkBench simulates the controller<->model interface through it’s workload configuration. The included configuration is based on actual production measurements of data payload size and distribution, node and link ‘temperature’ (popularity) and logged operation mix over a period of days.

The Social Graph In Use

Implementation of MongoDB / TokuMX plugin for LinkBench


LinkBench is designed to be customizable and extensible in order to test new persistence technologies and architecture designs.  A new database plugin can be developed by extending the abstract class com.facebook.LinkBench.LinkStore and/or implementing the interface com.facebook.LinkBench.NodeStore.  There is also a combination com.facebook.LinkBench.GraphStore class that can be sub-classed for a combination of both LinkStore and NodeStore.  One disadvantage of the current implementation is that it is up to the plugin developer to follow all of the business requirements of the social graph in the plugin.  This requires careful auditing of each plugin to insure that it has been implemented to specification.  To assure a 1-to-1 parity with the MySQL plugin, I used it as a base and converted the methods to MongoDB one at a time carefully translating each operation.

Along the way, I’ve learned a lot about NoSQL and MongoDB in particular and dispelled a few myths that I had about NoSQL.  I will save that for another article.  Let me talk about a few design decisions I made while implementing the plugin.

  • Compatibility – In order to provide comparisons, the LinkBench plugin maintains compatibility with MongoDB 2.x, TokuMX 2.x, MongoDB 3.x and TokuMXse (RC)
  • TransactionsMVCC concurrency is used in the LinkBench MySQL plugin. In order to maintain this capability I implemented new configuration transaction_support_level which allows the Benchmark to run with no transaction support, MVCC only if supported or simulated transactions using the Two Phase Commit strategy documented on the MongoDB site.
  • Schema –  The relationship between nodes and links does not facilitate the use of embedded documents.   It would be possible to embed count documents under node, however it probably isn’t worth the the extra complexity and network traffic that would be generated.  I opted to leave the schema flat.

In Part 2 I will dive into the LinkBench Java code a bit to show the comparison between the MySQL plugin the MongoDB plugin.

External Links

The post MongoDB / TokuMX plugin for LinkBench (Part 1) appeared first on MySQL Performance Blog.

Percona XtraBackup 2.3.1-beta1 is now available

May 20, 2015 - 11:10am

Percona is glad to announce the release of Percona XtraBackup 2.3.1-beta1 on May 20th 2015. Downloads are available from our download site here. This BETA release, will be available in Debian testing and CentOS testing repositories.

This is an BETA quality release and it is not intended for production. If you want a high quality, Generally Available release, the current Stable version should be used (currently 2.2.10 in the 2.2 series at the time of writing).

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 contains all of the features and bug fixes in Percona XtraBackup 2.2.10, plus the following:

New Features:

  • innobackupex script has been rewritten in C and it’s set as the symlink for xtrabackup. innobackupex still supports all features and syntax as 2.2 version did, but it is now deprecated and will be removed in next major release. Syntax for new features will not be added to the innobackupex, only to the xtrabackup. xtrabackup now also copies MyISAM tables and supports every feature of innobackupex. Syntax for features previously unique to innobackupex (option names and allowed values) remains the same for xtrabackup.
  • Percona XtraBackup can now read swift parameters from a [xbcloud] section from the .my.cnf file in the users home directory or alternatively from the global configuration file /etc/my.cnf. This makes it more convenient to use and avoids passing the sensitive data, such as --swift-key, on the command line.
  • Percona XtraBackup now supports different authentication options for Swift.
  • Percona XtraBackup now supports partial download of the cloud backup.
  • Options: --lock-wait-query-type, --lock-wait-threshold and --lock-wait-timeout have been renamed to --ftwrl-wait-query-type, --ftwrl-wait-threshold and --ftwrl-wait-timeout respectively.

Bugs Fixed:

  • innobackupex didn’t work correctly when credentials were specified in .mylogin.cnf. Bug fixed #1388122.
  • Options --decrypt and --decompress didn’t work with xtrabackup binary. Bug fixed #1452307.
  • Percona XtraBackup now executes an extra FLUSH TABLES before executing FLUSH TABLES WITH READ LOCK to potentially lower the impact from FLUSH TABLES WITH READ LOCK. Bug fixed #1277403.
  • innobackupex didn’t read user,password options from ~/.my.cnf file. Bug fixed #1092235.
  • innobackupex was always reporting the original version of the innobackup script from InnoDB Hot Backup. Bug fixed #1092380.

Release notes with all the bugfixes for Percona XtraBackup 2.3.1-beta1 are available in our online documentation. Bugs can be reported on the launchpad bug tracker. Percona XtraBackup is an open source, free MySQL hot backup software that performs non-blocking backups for InnoDB and XtraDB databases.

The post Percona XtraBackup 2.3.1-beta1 is now available appeared first on MySQL Performance Blog.

Like my MySQL QA series? Here’s how to record your own!

May 19, 2015 - 6:55am

Professional Screen Recording / Screencast Template 1280×720

Whilst not directly related to MySQL, it may be helpful to post (and open source at the same time) the HD screen recording/screencast template I’ve been using to create the MySQL QA series.

If you’ve never recorded a screencast before, or if you’d like to improve your screen recordings, this short blog post will get you started/help you along the way!

 

Here’s what you need;
1. Software Budget: $0 (Really! All required software is free, provided you have Microsoft Windows to use it on)
2. A reasonable quality headset. I can recommend (and use) a Microsoft LifeChat LX-3000 or similar (~$40)
3. The attached template (right-click the image above and select ‘Save Link/Image As…’ or similar)

Here’s how to get started;
1. Install Microsoft Visual C++ 2010 Redistributable Package (x86) from here.
2. Install CamStudio 2.7 or higher. CamStudio can be downloaded from http://camstudio.org/
3. Install CamStudioCodec 1.5 or higher (a good quality codec, available from the same website as in step 2).
4. Use a 1600×900 or higher screen resolution to match the template size (or you can also edit the template).
5. Set the Professional Screen Recording / Screencast Template (right click & save) as your background.
6. Make the settings/follow the instructions as per the template (tip: read everything first before you start).
7. You can scale your to-be-recorded window to match the black frame (and one of the settings as shown in the template makes CamStudio match the recording size exactly to that frame). The black frame ensures that any slight pixel-mismatch still shows nicely in the resulting video.
8. You may like to install a screen marking tool like DemoHelper or ScreenMarker.

If you followed the steps above you will have already covered the blue (top right) “Installation/use” steps nr’s 1-5, and you can now continue with the red steps 1-8 (and read the additional blue tips 1-4).

There’s quite a bit to it, but if you get it setup right the quality will be great!

I hope to see more technical how-to video’s on this and other blogs in the future!

Enjoy!

The post Like my MySQL QA series? Here’s how to record your own! appeared first on MySQL Performance Blog.

Percona security update: oCERT and SSL improvements

May 18, 2015 - 10:56am

We have recently become a member of oCERT to aid in allowing responsible disclosure for Percona products and services as can be seen on their members page.

We are presently working on the verbiage for the responsible disclosure program, and we are also investigating establishing a bug bounty program. In the mean time you can refer to our security contact page which will be updated as more information becomes available.

Secondly as you have quiet possibly noticed www.percona.com now enforces SSL and requests are redirected to https://www.percona.com should a http request be made.

This is but one small part of the continuing security initiative here at Percona and one I am happy to finally announce completion of as it had been on the “list” for some time.

The current SSL configuration follows best practices such as those laid out in the Mozilla Security Server Side TLS wiki entry, and as such gains an A+ rating from Qualys’ SSLLabs.com

There are of course still improvements to be made, and we are incrementally deploying those as they are completed and pass QA which sometimes leads to unavoidable delays. I would like to thank isvsecwatch for their report (which came in near the end of the overhaul process) and their patience in the extended time it took to get it into production.

The post Percona security update: oCERT and SSL improvements appeared first on MySQL Performance Blog.

MySQL QA Episode 2: Build a MySQL server – Git, Bazaar, compiling & build tools

May 15, 2015 - 12:00am

Welcome to MySQL QA Episode 2: Build a MySQL Server – Git, Bazaar (bzr), Compiling, and Build Tools

In this episode you’ll learn how to build Percona Server and/or MySQL Server for QA purposes & more in this short 25 minute tutorial.

In HD quality (set your player to 720p!)

To watch the other episodes in this series, see the MySQL QA & Bash Linux Training Series post. If you missed MySQL QA Episode 1, it was titled “Bash/GNU Tools & Linux Upskill & Scripting Fun.” You are read it here.

If you have any questions or comments, please leave them below.

The post MySQL QA Episode 2: Build a MySQL server – Git, Bazaar, compiling & build tools appeared first on MySQL Performance Blog.

How Percona Support handles bugs

May 11, 2015 - 3:00am

One of the great values of a Percona Support contract is that we provide bug fixes for covered software, and not just support in terms of advice on how to use it. This is the skill which is most likely missing from in-house for most customers, as it requires a team with code knowledge to build and test infrastructure – something only a few companies can afford to invest in.

There is a lot of misunderstanding about bugs. What is a bug? What is a feature? What is a repeatable bug? How will Percona troubleshoot the bug? In this post I will answer some of the questions about this.

Bugs vs. Features ⎼ One thing a lot of people have a hard time understanding is the difference between a bug and a feature, or when software was designed to work a certain way which might be unwelcome. There is a gray line here, but you need to expect that some of the things you consider to be bugs will be seen as behavior-change features and will be considered as such.

Unfixable Bugs ⎼ There are some behaviors that any sane person would call a bug, but which arise from design limitations or oversight that are impossible to fix in the current GA version without introducing changes that would destabilize it. Such bugs will need to be fixed in the next major GA release or sometimes even further in the future. Some bugs are not bugs at all but rather design tradeoffs made. These can’t be “fixed” unless different design tradeoffs are chosen.

Workaround ⎼ There are going to be unexpected behaviors, unfixable bugs and bugs that take awhile to fix, so your first practical response to running into the bug is often finding a workaround which does not expose it. The Percona Support team will help find a workaround that causes minimal impact to your business, but be prepared: changes to the application, deployed version, schema or configuration will often be required.

Emergencies ⎼ When you have an emergency, our focus is to restore the system to working order. In a complex system a bug fix can often not be delivered in a short period of time, which typically means finding a workaround.

Bug Turnaround It is not possible to guarantee the turnaround on a bug fix, as all bugs are different. Some bugs are rather trivial and we might be able to provide a hotfix 24 hours after we have a repeatable test case. In other cases the bug might be complicated and take weeks of engineering to fix or even might be impossible to fix in the current GA version.

Verified Bug Fixes When you submit the bug we have to verify if it is actually being a bug. In many cases it might be intended behavior; in others, a user mistake. It is also possible that the behavior has happened once and can’t be repeated. Having a repeatable test case that reveals the bug is the best way to have a bug fixed quickly. You might be able to create a repeatable test case, or our support team might be able to help you create the test case.

Sporadic Bugs These are very hard bug types that happen sporadically over a period of time. For example, you might have a system crash once every 3 months with no way to repeat it. The cause of such bugs can be very complicated; for example, a buffer overrun in one piece of code can cause corruption and crash in another place hours later. There are a number of diagnostic tools that exist for such bugs, but generally they take quite awhile to resolve. In addition, without a repeatable test case, it is often impossible to verify that the proposed fix actually resolves the bug.

Environmental Bugs ⎼ Some bugs are caused by what can be called your environment. It could be some hardware bugs or incompatibilities, a build not quite compatible with your version of  operating system, operating system bugs, etc. In some cases we can very clearly point to the environment problems. In others we can suspect the environment is an issue and we may ask you to see if the bug also happens in another environment, such as different hardware or OS installation.

Hot Fixes As our default policy we fix bugs in the next release of our software so it can go through the full QA cycle, be properly documented, etc. If you have implemented a workaround and you can wait until the next release, this is the best choice. If not, with the Percona Platinum Support contract, we can provide you with a hotfix that is a special build containing the version of the software you’re running, and with only the bug fix of interest applied. Hotfixes are especially helpful if you’re not looking to do a full software upgrade – requiring several revisions – but want to validate the fix with the minimum possible changes. Hotfixes might also be different from the final bug fix that goes into the GA release. With hotfixes, our goal is to provide a working solution for you faster. Afterward we may optimize or re-architect the code, come up with better option names, etc.

Bug Diagnostics Depending on the nature of the bug there are multiple tools that our support team will use for diagnostics – finding a way to fix the bug. To set expectations correctly, it can be a very involved process, where you might need to provide a lot of information or try things on your system, such as:

  • Test case. If you have a test case that can be repeated by the Percona team to trigger the bug, the diagnostic problem is solved from the customer side. Internal debugging starts at this point. It might not be easy to get to that.
  • If we have a crash that we can’t repeat on our system we often will ask you to enable “core” file, or run the program under a debugger so we can get more information when the crash happens.
  • If the problem is related to performance, you should be ready to gather both MySQL information such as EXPLAIN, status counters, information from performance schema, etc., along with system level information such as pt-pmp output,  pt-stalk,  oprofile, perf, etc.
  • If the problem is a “deadlock,” we often need information from gdb about the full state of the system. Information from processlist, performance_schema, SHOW ENGINE INNODB STATUS can also be helpful.
  • It can be very helpful when you have a test system on which you can repeat the problem in your environment and where you can experiment without impacting production. It is not possible in all cases, but is very helpful.
  • Sometimes, for hard-to-repeat bugs, we will need to run a special diagnostics build that provides us with additional debug information. In others, we might need to run a debug build or do a run under valgrind or other software designed to catch bugs. It often has a large performance impact, so it is good to see how your workload can be scaled down in order for this to be feasible.
  • Depending on your environment we might need to login to troubleshoot your bug or might request that you upload the data needed to repeat the bug in our lab (assuming it is not too sensitive). In cases where direct login is not possible, we can help you to come to a repeatable test case via phone, chat, or email. Using screen sharing can also be very helpful.

Bugs and Non-Percona Software ⎼ Percona Support covers some software not produced by Percona. For open source software, if it is not exempt from bug fix support, we will provide the custom build with a bug fix as well as provide the suggested fix to the software maintainer for its possible inclusion in the next release. For example, if we find a bug in the MySQL Community Edition, we will pass our suggested fix to the MySQL Engineering team at Oracle. For other software that is not open source, such as Amazon RDS, we can help to facilitate creation and submission of a repeatable test case and workaround, but we can’t provide a fix as we do not have access to the source code.

In Conclusion When I think about software bugs, I find some good parallels with human “bugs” (diseases).  Some issues are trivial to diagnose and the fix is obvious. Others might be very hard to diagnose I guess many of us have been in a situation where you visit doctor after doctor and tell them your symptoms, and they run tests but still can’t figure out what’s wrong. Once a diagnosis is done, though, it is not always given the “fix” available or feasible, and while a complete solution is preferred, sometimes we have to settle for “managing” the disease, which is our parallel to implementing changes and settling for a workaround. So in the same way as human doctors, we can’t guarantee we will get to the root of every problem, or if we do, that we will be able to fix every one of them. However, as with having good doctors – having us on your team will maximize the chance of successful resolution.

The post How Percona Support handles bugs appeared first on MySQL Performance Blog.

Percona Server 5.6.24-72.2 is now available

May 8, 2015 - 1:55pm

Percona is glad to announce the release of Percona Server 5.6.24-72.2 on May 8, 2015. Download the latest version from the Percona web site or from the Percona Software Repositories.

Based on MySQL 5.6.24, including all the bug fixes in it, Percona Server 5.6.24-72.2 is the current GA release in the Percona Server 5.6 series. Percona Server is open-source and free – and 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.24-72.2 milestone on Launchpad.

New Features:

  • TokuDB storage engine package has been updated to version 7.5.7.

Bugs Fixed:

  • A server binary as distributed in binary tarballs could fail to load on different systems due to an unsatisfied libssl.so.6 dynamic library dependency. This was fixed by replacing the single binary tarball with multiple tarballs depending on the OpenSSL library available in the distribution: 1) ssl100 – for all Debian/Ubuntu versions except Squeeze/Lucid (libssl.so.1.0.0 => /usr/lib/x86_64-linux-gnu/libssl.so.1.0.0 (0x00007f2e389a5000)); 2) ssl098 – only for Debian Squeeze and Ubuntu Lucid (libssl.so.0.9.8 => /usr/lib/libssl.so.0.9.8 (0x00007f9b30db6000)); 3) ssl101 – for CentOS 6 and CentOS 7 (libssl.so.10 => /usr/lib64/libssl.so.10 (0x00007facbe8c4000)); 4) ssl098e – to be used only for CentOS 5 (libssl.so.6 => /lib64/libssl.so.6 (0x00002aed5b64d000)). Bug fixed #1172916.
  • Executing a stored procedure containing a subquery would leak memory. Bug fixed #1380985 (upstream #76349).
  • A slave server restart could cause a 1755 slave SQL thread error if multi-threaded slave was enabled. This is a regression introduced by fix for bug #1331586 in 5.6.21-70.0. Bug fixed #1380985.
  • A string literal containing an invalid UTF-8 sequence could be treated as falsely equal to a UTF-8 column value with no invalid sequences. This could cause invalid query results. Bug fixed #1247218 by a fix ported from MariaDB (MDEV-7649).
  • Percona Server .deb binaries were built without fast mutexes. Bug fixed #1433980.
  • Installing or uninstalling the Audit Log Plugin would crash the server if the audit_log_file variable was pointing to an inaccessible path. Bug fixed #1435606.
  • The audit_log_file would point to random memory area if the Audit Log Plugin was not loaded into server, and then installed with INSTALL PLUGIN, and my.cnf contained audit_log_file setting. Bug fixed #1437505.
  • A specific trigger execution on the master server could cause a slave assertion error under row-based replication. The trigger would satisfy the following conditions: 1) it sets a savepoint; 2) it declares a condition handler which releases this savepoint; 3) the trigger execution passes through the condition handler. Bug fixed #1438990 (upstream #76727).
  • Percona Server client packages were built with with EditLine instead of Readline. This was causing history file produced by the client no longer easy to read. Further, a client built with EditLine could display incorrectly on PuTTY SSH client after its window resize. Bugs fixed #1266386, #1296192 and #1332822 (upstream #63130, upstream #72108 and #69991).
  • Unlocking a table while holding the backup binlog lock would cause an implicit erroneous backup lock release, and a subsequent server crash or hang at the later explicit backup lock release request. Bug fixed #1371827.
  • Initializing slave threads or executing CHANGE MASTER TO statement would crash a debug build if autocommit was disabled and at least one of slave info tables were configured as tables. Bug fixed #1393682.

Other bugs fixed: #1372263 (upstream #72080), #1436138 (upstream #76505), #1182949 (upstream #69453), #1111203 (upstream #68291), and #1384566 (upstream #74615).

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

The post Percona Server 5.6.24-72.2 is now available appeared first on MySQL Performance Blog.

Percona Server 5.5.43-37.2 is now available

May 8, 2015 - 1:55pm


Percona is glad to announce the release of Percona Server 5.5.43-37.2 on May 8, 2015. Based on MySQL 5.5.43, including all the bug fixes in it, Percona Server 5.5.43-37.2 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.43-37.2 milestone on Launchpad. Downloads are available here and from the Percona Software Repositories.

Bugs Fixed:

  • A server binary as distributed in binary tarballs could fail to load on different systems due to an unsatisfied libssl.so.6 dynamic library dependency. This was fixed by replacing the single binary tarball with multiple tarballs depending on the OpenSSL library available in the distribution: 1) ssl100 – for all Debian/Ubuntu versions except Squeeze/Lucid (libssl.so.1.0.0 => /usr/lib/x86_64-linux-gnu/libssl.so.1.0.0 (0x00007f2e389a5000)); 2) ssl098 – only for Debian Squeeze and Ubuntu Lucid (libssl.so.0.9.8 => /usr/lib/libssl.so.0.9.8 (0x00007f9b30db6000)); 3) ssl101 – for CentOS 6 and CentOS 7 (libssl.so.10 => /usr/lib64/libssl.so.10 (0x00007facbe8c4000)); 4) ssl098e – to be used only for CentOS 5 (libssl.so.6 => /lib64/libssl.so.6 (0x00002aed5b64d000)). Bug fixed #1172916.
  • mysql_install_db would make the server produce an “Error in my_thread_global_end(): 1 threads didn't exit” error message. While this error does not prevent mysql_install_db from completing successfully, its presence might cause any mysql_install_db-calling script to return an error as well. This is a regression introduced by backporting fix for bug #1319904. Bug fixed #1402074.
  • A string literal containing an invalid UTF-8 sequence could be treated as falsely equal to a UTF-8 column value with no invalid sequences. This could cause invalid query results. Bug fixed #1247218 by a fix ported from MariaDB (MDEV-7649).
  • Percona Server .deb binaries were built without fast mutexes. Bug fixed #1433980.
  • Installing or uninstalling the Audit Log Plugin would crash the server if the audit_log_file variable was pointing to an inaccessible path. Bug fixed #1435606.
  • The audit_log_file variable would point to random memory area if the Audit Log Plugin was not loaded into server, and then installed with INSTALL PLUGIN, and my.cnf contained audit_log_file setting. Bug fixed #1437505.
  • Percona Server client .deb packages were built with with EditLine instead of Readline. Further, a client built with EditLine could display incorrectly on PuTTY SSH client after its window resize. Bugs fixed #1266386 and #1332822 (upstream #63130 and #69991).

Other bugs fixed: #1436138 (upstream #76505).

(Please also note that Percona Server 5.6 series is the latest General Availability series and current GA release is 5.6.24-72.2.)

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

The post Percona Server 5.5.43-37.2 is now available appeared first on MySQL Performance Blog.

MongoDB with Percona TokuMXse – experimental build RC5 is available!

May 8, 2015 - 9:43am

While our engineering team is working on finalizing the TokuMXse storage engine, I want to provide an experimental build that you can try and test MongoDB 3.0 with our storage engine.

It is available here
percona.com/downloads/TESTING/Percona-TokuMXse-rc5/percona-tokumxse-3.0.3pre-rc5.tar.gz

To start MongoDB with TokuMXse storage engine use:

mongod --storageEngine=tokuft

I am looking for your feedback!

The post MongoDB with Percona TokuMXse – experimental build RC5 is available! appeared first on MySQL Performance Blog.

MySQL indexing 101: a challenging single-table query

May 8, 2015 - 12:00am

We discussed in an earlier post how to design indexes for many types of queries using a single table. Here is a real-world example of the challenges you will face when trying to optimize queries: two similar queries, but one is performing a full table scan while the other one is using the index we specially created for these queries. Bug or expected behavior? Read on!

Our two similar queries

# Q1 mysql> explain select col1, col2 from t where ts >= '2015-04-30 00:00:00'; +----+-------------+---------------+------+---------------+------+---------+------+---------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+---------------+------+---------------+------+---------+------+---------+-------------+ | 1 | SIMPLE | t | ALL | ts | NULL | NULL | NULL | 4111896 | Using where | +----+-------------+---------------+------+---------------+------+---------+------+---------+-------------+ # Q2 mysql> explain select count(*) from t where ts >='2015-04-30 00:00:00'; +----+-------------+---------------+-------+---------------+--------------+---------+------+---------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+---------------+-------+---------------+--------------+---------+------+---------+--------------------------+ | 1 | SIMPLE | t | range | ts | ts | 5 | NULL | 1809458 | Using where; Using index | +----+-------------+---------------+-------+---------------+--------------+---------+------+---------+--------------------------+

Q1 runs a full-table scan while Q2 is using the index on ts, which by the way is covering – See Using index in the Extra field. Why such different execution plans?

Let’s try to understand what happens with Q1.

This is a query with a single inequality on the ts field and we have an index on ts. The optimizer tries to see if this index is usable (possible_keys field), this is all very logical. Now if we look at the rows field for Q1 and Q2, we can see that the index would allow us to only read 45% of the records (1.8M out of 4.1M). Granted, this is not excellent but this should be much better than a full table scan anyway, right?

If you think so, read carefully what’s next. Because this assumption is simply not correct!

Estimating the cost of an execution plan (simplified)

First of all, the optimizer does not know if data or indexes are in memory or need to be read from disk, it will simply assume everything is on disk. What it does know however is that sequential reads are much faster than random reads.

So let’s execute Q1 with the index on ts. Step 1 is to perform a range scan on this index to identify the 1.8M records that match the condition: this is a sequential read, so this is quite fast. However now step 2 is to get the col1 and col2 fields for each record that match the condition. The index provides the primary key value for each matching record so we will have to run a primary key lookup for each matching record.

Here is the issue: 1.8M primary key lookups is equivalent to 1.8M random reads, therefore this will take a lot of time. Much more time than sequentially reading the full table (which means doing a full scan of the primary key because we are using InnoDB here).

Contrast that with how Q2 can be executed with the index on ts. Step 1 is the same: identify the 1.8M matching records. But the difference is: there’s no step 2! That’s why we call this index a ‘covering index': we don’t need to run point queries on the primary key to get extra fields. So this time, using the index on ts is much more efficient than reading the full table (which again would mean that we would do a full-table scan of the primary key).

Now there is one more thing to understand: a full-table scan is a sequential operation when you think about it from a logical point of view, however the InnoDB pages are certainly not stored sequentially on disk. So at the disk level, a full table is more like multiple random reads than a single large sequential read.

However it is still much faster than a very large number or point query and it’s easy to understand why: when you read a 16KB page for a full table scan, all records will be used. While when you read a 16KB page for a random read, you might only use a single record. So in the worst case, reading 1.8M records will require 1.8M random reads while reading the full table with 4M records will only require 100K random reads – the full table scan is still an order of magnitude faster.

Optimizing our query

Now that we’ve understood why the optimizer chose a full table scan for Q1, is there a way to make it run faster by using an index? If we can create a covering index, we will no longer need the expensive primary key lookups. Then the optimizer is very likely to choose this index over a full table scan. Creating such a covering index is easy:

ALTER TABLE t ADD INDEX idx_ts_col1_col2 (ts, col1, col2);

Some of you may object that because we have an inequality on ts, the other columns cannot be used. This would be true if we had conditions on col1 or col2 in the WHERE clause, but that does not apply here since we’re only adding these extra columns to get a covering index.

Conclusion

Understanding how indexes can be used to filter, sort or cover is paramount to be able to optimize queries, even simple ones. Understanding (even approximately) how a query is run according to a given execution plan is also very useful. Otherwise you will sometimes be puzzled by the decisions made by the optimizer.

Also note that beginning in MySQL 5.7, the cost model can be tuned. This can help the optimizer make better decisions: for instance random reads are far cheaper on fast storage than on regular disks.

The post MySQL indexing 101: a challenging single-table query appeared first on MySQL Performance Blog.

Introducing email reports for Percona Cloud Tools!

May 7, 2015 - 3:00am

For those PCT users who forget to regularly check query performance, you can now get server query reports delivered to your email daily or weekly. The report contains a digest of the most critical performance information related to a particular MySQL instance, enabling you to act on any recent issues. There are convenient links that open the corresponding view of the PCT Query Analytics module.

Server summary snapshot

The report also provides a list of ten slowest queries and ten queries with the highest load during the period. It is important to compare these lists, because some of the slowest queries may occur only several times during the period (so they probably do not overload the server), while seemingly quick queries may run millions of times and put a lot of unnecessary overhead, which you could investigate and fix.

List of slowest queries

List of queries with highest load

Finally, the report also provides information about new queries that you add to MySQL. This enables you to estimate the efficiency of a new query and see how it compares to the worst performing ones.

List of new queries

Reports are enabled by default for some of our customers. We will gradually enable them for more PCT users every week to make sure that the new feature rolls out smoothly. You can manage reports in the PCT web UI under Configure > Reports at https://cloud.percona.com/org/user-reports-settings.

By default, you will receive a separate weekly report for each MySQL instance in your infrastructure. If you add a new MySQL instance, weekly reports will be automatically enabled for it.

To disable all reports, clear the Enable server query reports check box. If you do not want to enable reports for new MySQL instances that you add, clear the Automatically receive reports for new MySQL instances check box.

You can enable reports for specific MySQL instances, as well as select between daily and weekly period. Disable reports for servers that do not require regular attention and enable weekly reports for more critical servers. Use daily reports for the most active servers, where you expect to have the most amount of change and load.

If you do not have a Percona Cloud Tools account yet, register at cloud.percona.com and get regular query reports by email!

The post Introducing email reports for Percona Cloud Tools! appeared first on MySQL Performance Blog.

Percona Security Advisory CVE-2015-1027

May 6, 2015 - 7:52am
Contents
  1. Summary
  2. Analysis
  3. Mitigating factors
  4. P.O.C
  5. Acknowledgments
Summary

During a code audit performed internally at Percona, we discovered a
viable information disclosure attack when coupled with a MITM attack
in which percona-toolkit and xtrabackup perl components could be
coerced into returning additional MySQL configuration information.
The vulnerability has since been closed.

Timeline

2014-12-16 Initial research, proof of concept exploitation and report completion
2015-01-07 CVE reservation request to Mitre, LP 1408375
2015-01-10 CVE-2015-1027 assigned
2015-01-16 Initial fix code completion, testing against POC verified fix
2015-01-23 Internal notification of impending 2.2.13 release of Percona-toolkit
2015-01-26 2.2.13 percona toolkit released: blog post
2015-02-17 2.2.9 xtrabackup released: blog post
2015-05-06 Publication of this document

Analysis

The vulnerability exists in the –version-check functionality of the
perl scripts (LP 1408375), whilst the fix implemented for CVE-2014-2029
(LP 1279502) did patch the arbitrary command execution, MySQL
configuration information may still be exfiltrated by this method.

The normal HTTP/HTTPS conversation is as follows during a –version-check
call.

GET / HTTP/1.1
User-Agent: HTTP-Micro/0.01
Connection: close
Host: v.percona.com

HTTP/1.0 200 OK
Date: Mon, 15 Dec 2014 13:43:12 GMT
Server: Apache
Set-Cookie: PHPSESSID=bjtu6oic82g07rgr9b5906qrg1; path=/
cache-control: no-cache
Content-Length: 144
Vary: Accept-Encoding
Connection: close
Content-Type: text/plain; charset=UTF-8
X-Pad: avoid browser bug

OS;os_version
MySQL;mysql_variable;version_comment,version
Perl;perl_version
DBD::mysql;perl_module_version
Percona::Toolkit;perl_module_version

POST / HTTP/1.1
User-Agent: HTTP-Micro/0.01
Content-Type: application/octet-stream
Connection: close
X-Percona-Toolkit-Tool: pt-online-schema-change
Content-Length: 287
Host: v.percona.com

d6ca3fd0c3a3b462ff2b83436dda495e;DBD::mysql;4.021
1b6f35cca661d68ad4dfceeebfaf502e;MySQL;(Debian) 5.5.40-0+wheezy1
d6ca3fd0c3a3b462ff2b83436dda495e;OS;Debian GNU/Linux Kali Linux 1.0.9
d6ca3fd0c3a3b462ff2b83436dda495e;Percona::Toolkit;2.2.12
d6ca3fd0c3a3b462ff2b83436dda495e;Perl;5.14.2

HTTP/1.0 200 OK
Date: Mon, 15 Dec 2014 13:43:13 GMT
Server: Apache
Set-Cookie: PHPSESSID=nnm4bs99gef0rhepdnclpin233; path=/
cache-control: no-cache
Content-Length: 0
Vary: Accept-Encoding
Connection: close
Content-Type: text/plain; charset=UTF-8
X-Pad: avoid browser bug

The issue centers around the interpretation of the response string

MySQL;mysql_variable;version_comment,version

This could be modified to extract additional information, for example the
ssl_key path.

MySQL;mysql_variable;version_comment,version,ssl_key

The program flow for –version-check is as follows
pingback -> parse_server_response -> get_versions -> sub_for_type.
The issue is the literal lookup of MySQL variables version_comment,
version, ssl_key (version 2.2.13 hard codes these to version_comment,version).

There also exists an issue with silent HTTP “downgrade” when SSL connection fails
in versions < 2.2.13.

7077 my $protocol = 'https'; # optimistic, but...
7078 eval { require IO::Socket::SSL; };
7079 if ( $EVAL_ERROR ) {
7080 PTDEBUG && _d($EVAL_ERROR);
7081 $protocol = 'http';
7082 }

Mitigating factors

This does require an existing presence in order to perform
the MITM attack, and spoof responses from v.percona.com.

This attack is limited to disclosing MySQL configuration information only, no data ex-filtration is known via this method at this time.

Debian && Ubuntu distribution packagers disabled this code in response to CVE-2014-2029

POCPython stand alone

Github GIST

MSF Module

Gihub GIST

Acknowledgements
  • Frank C – Percona (percona-toolkit dev)
  • Alexey K – Percona (percona-xtrabackup dev)
  • Peter S – Percona (Opensource director)
  • David B – Percona (ISA)
  • Andrea B – oCERT

The post Percona Security Advisory CVE-2015-1027 appeared first on MySQL Performance Blog.

Peter Zaitsev hits the road for East Coast MySQL Meetup tour

May 6, 2015 - 3:00am

Percona CEO Peter Zaitsev and Big Data guru Alexander Rubin will be speaking at Meetups along the East Coast next week with stops in Boston (May 11), New York City (May 12), Philadelphia (May 13) and Baltimore (May 14).

Dubbed the “MySQL Whistle-Stop Tour” since they’ll be traveling city to city via Amtrak, Peter will be speaking about last month’s Tokutek acquisition with audience Q&A, followed by a short presentation on “PXC – Next Generation HA for MySQL.”

Alexander will then discuss “advanced MySQL query tuning,” explaining how tuning MySQL queries and indexes can significantly increase the performance of your application and decrease response times.

And now for more detail around the talks….

Percona XtraDB Cluster (PXC) is a replacement for conventional MySQL master/slave architectures to eliminate replication lag and achieve a highly available masterless cluster of MySQL servers. In his talk Peter will discuss:

  • HA Solutions for MySQL
  • What PXC Has to Offer
  • Architecture Details
  • When to PXC and when not to PXC

And as I mentioned above, Peter will also talk about Tokutek, which has delivered Big Data processing power across two of the most important Open Source data management platforms: MySQL and MongoDB. The acquisition, which includes the Tokutek distribution of MongoDB, called TokuMX, positions Percona to offer design, service, support and remote management for both MySQL and a market-leading, ACID-compliant NoSQL database from one of the most trusted companies in the industry.

Alexander’s discussion on advanced MySQL query tuning will be focused on tuning the “usual suspects…” Queries with “Group By”, “Order By” and subqueries. These query types are usually under performing in MySQL and add an additional load because MySQL may need to create a temporary table(s) or perform a filesort. He’ll also share MySQL 5.6 features that can increase MySQL query performance for subqueries and “order by” queries.

We’ll also be raffling off one pass per meetup to the Percona Live Amsterdam (Sept. 21-22) conference, along with some cool Percona t-shirts. Tasty food and beverages will be complimentary… and you are also welcome to hang out and have a beer with Peter and Alexander after the meetup.

A huge thanks to our hosts, the meetup organizers!

Would your meetup group like to have a visit from Peter, Alexander and/or other Percona experts? Just let me know in the comments section below and I’ll be happy to see if we can make it happen. Percona is also happy to help with sponsorship of your meetups.

The post Peter Zaitsev hits the road for East Coast MySQL Meetup tour appeared first on MySQL Performance Blog.

MongoDB’s flexible schema: How to fix write amplification

May 5, 2015 - 8:56am

Being schemaless is one of the key features of MongoDB. On the bright side this allows developers to easily modify the schema of their collections without waiting for the database to be ready to accept a new schema. However schemaless is not free and one of the drawbacks is write amplification. Let’s focus on that topic.

Write amplification?

The link between schema and write amplification is not obvious at first sight. So let’s first look at a table in the relational world:

mysql> SELECT * FROM user LIMIT 2; +----+-------+------------+-----------+-----------+----------------------------------+---------+-----------------------------------+------------+------------+ | id | login | first_name | last_name | city | country | zipcode | address | password | birth_year | +----+-------+------------+-----------+-----------+----------------------------------+---------+-----------------------------------+------------+------------+ | 1 | arcu | Vernon | Chloe | Paulista | Cook Islands | 28529 | P.O. Box 369, 1464 Ac Rd. | SSC44GZL5R | 1970 | | 2 | quis | Rogan | Lewis | Nashville | Saint Vincent and The Grenadines | H3T 3S6 | P.O. Box 636, 5236 Elementum, Av. | TSY29YRN6R | 1983 | +----+-------+------------+-----------+-----------+----------------------------------+---------+-----------------------------------+------------+------------+

As all records have exactly the same fields, the field names are stored once in a separate file (.frm file). So the field names is metadata while the value of each field for each record is of course data.

Now let’s look at an equivalent collection in MongoDB:

{ { "login": "arcu", "first_name": "Vernon", "last_name": "Chloe", "city": "Paulista", "country": "Cook Islands", "zipcode": "28529", "address": "P.O. Box 369, 1464 Ac Rd.", "password": "SSC44GZL5R", "birth_year": 1970 }, { "login": "quis", "first_name": "Rogan", "last_name": "Lewis", "city": "Nashville", "country": "Saint Vincent and The Grenadines", "zipcode": "H3T 3S6", "address": "P.O. Box 636, 5236 Elementum, Av.", "password": "TSY29YRN6R", "birth_year": 1983 } }

One difference with a table in the relational world is that MongoDB doesn’t know which fields each document will have. Therefore field names are data, not metadata and they must be stored with each document.

Then the question is: how large is the overhead in terms of disk space? To have an idea, I inserted 10M such records in an InnoDB table (adding an index on password and on birth_year to make the table look like a real table): the size on disk is around 1.4GB.

I also inserted the exact same 10M records in a MongoDB collection using the regular MMAPv1 storage engine, again adding an index on password and on birth_year, and this time the size on disk is … 2.97GB!

Of course it is not an apples-to-apples comparison as the InnoDB storage format and the MongoDB storage format are not identical. However a 100% difference is still significant.

Compression

One way to deal with write amplification is to use compression. With MongoDB 3.0, the WiredTiger storage engine is available and one of its benefits is compression (default algorithm: snappy). Percona TokuMX also has built-in compression using zlib by default.

Rebuilding the collection with 10M documents and the 2 indexes now gives the following results:
WiredTiger: 1.14GB
TokuMX: 736MB

This is a 2.5x to 4x data size reduction, pretty good!

WiredTiger also provides zlib compression and in this case the collection is only 691MB. However CPU usage is much higher compared to snappy so zlib will not be usable in all situations.

Conclusion

MongoDB schemaless design is attractive but it comes with several tradeoffs. Write amplification is one of them and using either WiredTiger with MongoDB 3.0 or Percona TokuMX is a very simple way to fix the issue.

The post MongoDB’s flexible schema: How to fix write amplification appeared first on MySQL Performance Blog.

Keep your MySQL data in sync when using Tungsten Replicator

May 4, 2015 - 3:00am

MySQL replication isn’t perfect and sometimes our data gets out of sync, either by a failure in replication or human intervention. We are all familiar with Percona Toolkit’s pt-table-checksum and pt-table-sync to help us check and fix data inconsistencies – but imagine the following scenario where we mix regular replication with the Tungsten Replicator:

We have regular replication going from master (db1) to 4 slaves (db2, db3, db4 and db5), but also we find that db3 is also master of db4 and db5 using Tungsten replication for 1 database called test. This setup is currently working this way because it was deployed some time ago when multi-source replication was not possible using regular MySQL replication. This is now a working feature in MariaDB 10 and also a feature coming with the new MySQL 5.7 (not released yet)… in our case it is what it is

So how do we checksum and sync data when we have this scenario? Well we can still achieve it with these tools but we need to consider some extra actions:

pt-table-checksum  

First of all we need to understand that this tool was designed to checksum tables against a regular MySQL replication environment, so we need to take special care on how to avoid checksum errors by considering replication lag (yes Tungsten replication may still suffer replication lag). We also need to instruct the tool to discover slaves via dsn because the tool is designed to discover replicas using regular replication. This can be done by using the –plugin function.

My colleague Kenny already wrote an article about this some time ago but let’s revisit it to put some graphics around our case. In order to make pt-table-checksum work properly within Tungsten replicator environment we need to:
– Configure the –plugin flag using this plugin to check replication lag.
– Use –recursion-method=dsn to avoid auto-discover of slaves.

[root@db3]$ pt-table-checksum --replicate=percona.cksums  --create-replicate-table  --no-check-replication-filters  --no-check-binlog-format --recursion-method=dsn=h=db1,D=percona,t=dsns  --plugin=/home/mysql/bin/pt-plugin-tungsten_replicator.pl --check-interval=5  --max-lag=10  -d test Created plugin from /home/mysql/bin/pt-plugin-tungsten_replicator.pl. PLUGIN get_slave_lag: Using Tungsten Replicator to check replication lag Checksumming test.table1: 2% 18:14 remain Checksumming test.table1: 5% 16:25 remain Checksumming test.table1: 9% 15:06 remain Checksumming test.table1: 12% 14:25 remain Replica lag is 2823 seconds on db5 Waiting. Checksumming test.table1: 99% 14:25 remain TS ERRORS DIFFS ROWS CHUNKS SKIPPED TIME TABLE 04-28T14:17:19 0 13 279560873 4178 0 9604.892 test.table1

So far so good. We have implemented a good plugin that allows us to perform checksums considering replication lag, and we found differences that we need to take care of, let’s see how to do it.

pt-table-sync

pt-table-sync is the tool we need to fix data differences but in this case we 2 problems:
1- pt-table-sync doesn’t support –recursion-method=dsn, so we need to pass hostnames to be synced as parameter. A feature request to add this recursion method can be found here (hopefully it will be added soon). This means we will need to sync each slave separately.
2- Because of 1 we can’t use –replicate flags so pt-table-sync will need to re run checksums again to find and fix differences. If checksum found differences in more than 1 table I’d recommend running the sync in separate steps, pt-table-sync modifies data. We don’t want to blindly ask it to fix our servers, right?

That being said I’d recommend running pt-table-sync with –print flag first just to make sure the sync process is going to do what we want it to do, as follows:

[root@db3]$ pt-table-sync --print --verbose --databases test -t table1 --no-foreign-key-checks h=db3 h=db4 # Syncing h=db4 # DELETE REPLACE INSERT UPDATE ALGORITHM START END EXIT DATABASE.TABLE .... UPDATE `test`.`table1` SET `id`='2677', `status`='open', `created`='2015-04-27 02:22:33', `created_by`='8', `updated`='2015-04-27 02:22:33', WHERE `ix_id`='9585' LIMIT 1 /*percona-toolkit src_db:test src_tbl:table1 src_dsn:h=db3 dst_db:test dst_tbl:table1 dst_dsn:h=db4 lock:0 transaction:1 changing_src:0 replicate:0 bidirectional:0 pid:16135 user:mysql host:db3*/; UPDATE `test`.`table1` SET `id`='10528', `status`='open', `created`='2015-04-27 08:22:21', `created_by`='8', `updated`='2015-04-28 10:22:55', WHERE `ix_id`='9586' LIMIT 1 /*percona-toolkit src_db:test src_tbl:table1 src_dsn:h=db3 dst_db:test dst_tbl:table1 dst_dsn:h=db4 lock:0 transaction:1 changing_src:0 replicate:0 bidirectional:0 pid:16135 user:mysql host:db3*/; UPDATE `test`.`table1` SET `id`='8118', `status`='open', `created`='2015-04-27 18:22:20', `created_by`='8', `updated`='2015-04-28 10:22:55', WHERE `ix_id`='9587' LIMIT 1 /*percona-toolkit src_db:test src_tbl:table1 src_dsn:h=db3 dst_db:test dst_tbl:table1 dst_dsn:h=db4 lock:0 transaction:1 changing_src:0 replicate:0 bidirectional:0 pid:16135 user:mysql host:db3*/; UPDATE `test`.`table1` SET `id`='1279', `status`='open', `created`='2015-04-28 06:22:16', `created_by`='8', `updated`='2015-04-28 10:22:55', WHERE `ix_id`='9588' LIMIT 1 /*percona-toolkit src_db:test src_tbl:table1 src_dsn:h=db3 dst_db:test dst_tbl:table1 dst_dsn:h=db4 lock:0 transaction:1 changing_src:0 replicate:0 bidirectional:0 pid:16135 user:mysql host:db3*/; .... # 0 0 0 31195 Chunk 11:11:11 11:11:12 2 test.table1

Now that we are good to go, we will switch –print to –execute

[root@db3]$ pt-table-sync --execute --verbose --databases test -t table1 --no-foreign-key-checks h=db3 h=db4 # Syncing h=db4 # DELETE REPLACE INSERT UPDATE ALGORITHM START END EXIT DATABASE.TABLE # 0 0 0 31195 Nibble 13:26:19 14:48:54 2 test.table1

And voila: data is in sync now.

Conclusions

Tungsten Replicator is a useful tool to deploy these kind of scenarios, with no need to upgrade/change MySQL version – but it still has some tricks to avoid data inconsistencies. General recommendations on good replication practices still applies here, i.e. not allowing users to run write commands on slaves and so on.

Having this in mind we can still have issues with our data but now with an extra small effort we can keep things in good health without much pain.

The post Keep your MySQL data in sync when using Tungsten Replicator appeared first on MySQL Performance Blog.

LinkBenchX: benchmark based on arrival request rate

May 1, 2015 - 12:00am

An idea for a benchmark based on the “arrival request” rate that I wrote about in a post headlined “Introducing new type of benchmark” back in 2012 was implemented in Sysbench. However, Sysbench provides only a simple workload, so to be able to compare InnoDB with TokuDB, and later MongoDB with Percona TokuMX, I wanted to use more complicated scenarios. (Both TokuDB and TokuMX are part of Percona’s product line, in the case you missed Tokutek now part of the Percona family.)

Thanks to Facebook – they provide LinkBench, a benchmark that emulates the social graph database workload. I made modifications to LinkBench, which are available here: https://github.com/vadimtk/linkbenchX. The summary of modifications is

  • Instead of generating events in a loop, we generate events with requestrate and send the event for execution to one of available Requester thread.
  • At the start, we establish N (requesters) connections to database, which are idle by default, and just waiting for an incoming event to execute.
  • The main output of the benchmark is 99% response time for ADD_LINK (INSERT + UPDATE request) and GET_LINKS_LIST (range SELECT request) operations.
  • The related output is Concurrency, that is how many Requester threads are active during the time period.
  • Ability to report stats frequently (5-10 sec interval); so we can see a trend and a stability of the result.

Also, I provide a Java package, ready to execute, so you do not need to compile from source code. It is available on the release page at https://github.com/vadimtk/linkbenchX/releases

So the main focus of the benchmark is the response time and its stability over time.

For an example, let’s see how TokuDB performs under different request rates (this was a quick run to demonstrate the benchmark abilities, not to provide numbers for TokuDB).

First graph is the 99% response time (in milliseconds), measured each 10 sec, for arrival rate 5000, 10000 and 15000 operations/sec:

or, to smooth spikes, the same graph, but with Log 10 scale for axe Y:

So there are two observations: the response time increases with an increase in the arrival rate (as it supposed to be), and there are periodical spikes in the response time.

And now we can graph Concurrency (how many Threads are busy working on requests)…

…with an explainable observation that more threads are needed to handle bigger arrival rates, and also during spikes all available 200 threads (it is configurable) become busy.

I am looking to adopt LinkBenchX to run an identical workload against MongoDB.
The current schema is simple

CREATE TABLE `linktable` ( `id1` bigint(20) unsigned NOT NULL DEFAULT '0', `id2` bigint(20) unsigned NOT NULL DEFAULT '0', `link_type` bigint(20) unsigned NOT NULL DEFAULT '0', `visibility` tinyint(3) NOT NULL DEFAULT '0', `data` varchar(255) NOT NULL DEFAULT '', `time` bigint(20) unsigned NOT NULL DEFAULT '0', `version` int(11) unsigned NOT NULL DEFAULT '0', PRIMARY KEY (link_type, `id1`,`id2`), KEY `id1_type` (`id1`,`link_type`,`visibility`,`time`,`id2`,`version`,`data`) ) ENGINE=TokuDB DEFAULT CHARSET=latin1; CREATE TABLE `counttable` ( `id` bigint(20) unsigned NOT NULL DEFAULT '0', `link_type` bigint(20) unsigned NOT NULL DEFAULT '0', `count` int(10) unsigned NOT NULL DEFAULT '0', `time` bigint(20) unsigned NOT NULL DEFAULT '0', `version` bigint(20) unsigned NOT NULL DEFAULT '0', PRIMARY KEY (`id`,`link_type`) ) ENGINE=TokuDB DEFAULT CHARSET=latin1; CREATE TABLE `nodetable` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `type` int(10) unsigned NOT NULL, `version` bigint(20) unsigned NOT NULL, `time` int(10) unsigned NOT NULL, `data` mediumtext NOT NULL, PRIMARY KEY(`id`) ) ENGINE=TokuDB DEFAULT CHARSET=latin1;

I am open for suggestions as to what is the proper design of documents for MongoDB – please leave your recommendations in the comments.

The post LinkBenchX: benchmark based on arrival request rate appeared first on MySQL Performance Blog.

Optimizer hints in MySQL 5.7.7 – The missed manual

April 30, 2015 - 12:00am

In version MySQL 5.7.7 Oracle presented a new promising feature: optimizer hints. However it did not publish any documentation about the hints. The only note which I found in the user manual about the hints is:

  • It is now possible to provide hints to the optimizer by including /*+ ... */ comments following the SELECT, INSERT, REPLACE, UPDATE, or DELETE keyword of SQL statements. Such statements can also be used with EXPLAIN. Examples:
    SELECT /*+ NO_RANGE_OPTIMIZATION(t3 PRIMARY, f2_idx) */ f1 FROM t3 WHERE f1 > 30 AND f1 < 33; SELECT /*+ BKA(t1, t2) */ * FROM t1 INNER JOIN t2 WHERE ...; SELECT /*+ NO_ICP(t1) */ * FROM t1 WHERE ...;

There are also three worklogs: WL #3996, WL #8016 and WL #8017. But they describe the general concept and do not have much information about which optimizations can be used and how. More light on this provided by slide 59 from Øystein Grøvlen’s session at Percona Live. But that’s all: no “official” full list of possible optimizations, no use cases… nothing.

I tried to sort it out myself.

My first finding is the fact that slide #59 really lists six of seven possible index hints. Confirmation for this exists in one of two new files under sql directory of MySQL source tree, created for this new feature.

$cat sql/opt_hints.h ... /** Hint types, MAX_HINT_ENUM should be always last. This enum should be synchronized with opt_hint_info array(see opt_hints.cc). */ enum opt_hints_enum { BKA_HINT_ENUM= 0, BNL_HINT_ENUM, ICP_HINT_ENUM, MRR_HINT_ENUM, NO_RANGE_HINT_ENUM, MAX_EXEC_TIME_HINT_ENUM, QB_NAME_HINT_ENUM, MAX_HINT_ENUM };

Looking into file sql/opt_hints.cc we can find out what these optimizations give not much choice: either enable or disable.

$cat sql/opt_hints.cc ... struct st_opt_hint_info opt_hint_info[]= { {"BKA", true, true}, {"BNL", true, true}, {"ICP", true, true}, {"MRR", true, true}, {"NO_RANGE_OPTIMIZATION", true, true}, {"MAX_EXECUTION_TIME", false, false}, {"QB_NAME", false, false}, {0, 0, 0} };

A choice for the way to include hints into SQL statements: inside comments with sign “+”/*+ NO_RANGE_OPTIMIZATION(t3 PRIMARY, f2_idx) */, – is compatible with style of optimizer hints which Oracle uses.

We actually had access to these hints before: they were accessible via variable optimizer_switch. At least such optimizations like BKA, BNL, ICP, MRR. But with new syntax we cannot only modify this access globally or per session, but can turn on or off particular optimization for a single table and column in the query. I can demonstrate it on this quite artificial but always accessible example:

mysql> use mysql Database changed mysql> explain select * from user where host in ('%', '127.0.0.1'); +----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-----------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-----------------------+ | 1 | SIMPLE | user | NULL | range | PRIMARY | PRIMARY | 180 | NULL | 2 | 100.00 | Using index condition | +----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-----------------------+ 1 row in set, 1 warning (0.01 sec) mysql> explain select /*+ NO_RANGE_OPTIMIZATION(user PRIMARY) */ * from user where host in ('%', '127.0.0.1'); +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ | 1 | SIMPLE | user | NULL | ALL | PRIMARY | NULL | NULL | NULL | 5 | 40.00 | Using where | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ 1 row in set, 1 warning (0.00 sec)

I used one more hint, which we could not turn on or off directly earlier: range optimization.

One more “intuitively” documented feature is the ability to turn on or off a particular optimization. This works only for BKA, BNL, ICP and MRR: you can specify NO_BKA(table[[, table]…]), NO_BNL(table[[, table]…]), NO_ICP(table indexes[[, table indexes]…]) and NO_MRR(table indexes[[, table indexes]…]) to avoid using these algorithms for particular table or index in the JOIN.

MAX_EXECUTION_TIME does not require any table or key name inside. Instead you need to specify maximum time in milliseconds which query should run:

mysql> select /*+ MAX_EXECUTION_TIME(1000) */ sleep(1) from user; ERROR 3024 (HY000): Query execution was interrupted, max_statement_time exceeded mysql> select /*+ MAX_EXECUTION_TIME(10000) */ sleep(1) from user; +----------+ | sleep(1) | +----------+ | 0 | | 0 | | 0 | | 0 | | 0 | +----------+ 5 rows in set (5.00 sec)

QB_NAME is more complicated. WL #8017 tells us this is custom context. But what is this? The answer is in the MySQL test suite! Tests for optimizer hints exist in file t/opt_hints.test For QB_NAME very first entry is query:

EXPLAIN SELECT /*+ NO_ICP(t3@qb1 f3_idx) */ f2 FROM (SELECT /*+ QB_NAME(QB1) */ f2, f3, f1 FROM t3 WHERE f1 > 2 AND f3 = 'poiu') AS TD WHERE TD.f1 > 2 AND TD.f3 = 'poiu';

So we can specify custom QB_NAME for any subquery and specify optimizer hint only for this context.

To conclude this quick overview I want to show a practical example of when query hints are really needed. Last week I worked on an issue where a customer upgraded from MySQL version 5.5 to 5.6 and found some of their queries started to work slower than before. I wrote an answer which could sound funny, but still remains correct: “One of the reasons for such behavior is optimizer  improvements. While they all are made for better performance, some queries – optimized for older versions – can start working slower than before.”

To demonstrate a public example of such a query I will use my favorite source of information: MySQL Community Bugs Database. In a search for Optimizer regression bugs that are still not fixed we can find bug #68919 demonstrating regression in case the MRR algorithm is used for queries with LIMIT. In run queries, shown in the bug report, we will see a huge difference:

mysql> SELECT * FROM t1 WHERE i1>=42 AND i2<=42 LIMIT 1; +----+----+----+----+ | pk | i1 | i2 | i3 | +----+----+----+----+ | 42 | 42 | 42 | 42 | +----+----+----+----+ 1 row in set (6.88 sec) mysql> explain SELECT * FROM t1 WHERE i1>=42 AND i2<=42 LIMIT 1; +----+-------------+-------+------------+-------+---------------+------+---------+------+---------+----------+----------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+------+---------+------+---------+----------+----------------------------------+ | 1 | SIMPLE | t1 | NULL | range | idx | idx | 4 | NULL | 9999958 | 33.33 | Using index condition; Using MRR | +----+-------------+-------+------------+-------+---------------+------+---------+------+---------+----------+----------------------------------+ 1 row in set, 1 warning (0.00 sec) mysql> SELECT /*+ NO_MRR(t1) */ * FROM t1 WHERE i1>=42 AND i2<=42 LIMIT 1; +----+----+----+----+ | pk | i1 | i2 | i3 | +----+----+----+----+ | 42 | 42 | 42 | 42 | +----+----+----+----+ 1 row in set (0.00 sec)

With MRR query execution takes 6.88 seconds and 0 if MRR is not used! But the bug report itself suggests usingoptimizer_switch="mrr=off";as a workaround. And this will work perfectly well if you are OK to runSET optimizer_switch="mrr=off";every time you are running a query which will take advantage of having it OFF. With optimizer hints you can have one or another algorithm to be ON for particular table in the query and OFF for another one. I, again, took quite an artificial example, but it demonstrates the method:

mysql> explain select /*+ MRR(dept_emp) */ * from dept_emp where to_date in (select /*+ NO_MRR(salaries)*/ to_date from salaries where salary >40000 and salary <45000) and emp_no >10100 and emp_no < 30200 and dept_no in ('d005', 'd006','d007'); +----+--------------+-------------+------------+--------+------------------------+------------+---------+----------------------------+---------+----------+-----------------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+--------------+-------------+------------+--------+------------------------+------------+---------+----------------------------+---------+----------+-----------------------------------------------+ | 1 | SIMPLE | dept_emp | NULL | range | PRIMARY,emp_no,dept_no | dept_no | 8 | NULL | 10578 | 100.00 | Using index condition; Using where; Using MRR | | 1 | SIMPLE | <subquery2> | NULL | eq_ref | <auto_key> | <auto_key> | 3 | employees.dept_emp.to_date | 1 | 100.00 | NULL | | 2 | MATERIALIZED | salaries | NULL | ALL | salary | NULL | NULL | NULL | 2838533 | 17.88 | Using where | +----+--------------+-------------+------------+--------+------------------------+------------+---------+----------------------------+---------+----------+-----------------------------------------------+ 3 rows in set, 1 warning (0.00 sec)

 

The post Optimizer hints in MySQL 5.7.7 – The missed manual appeared first on MySQL Performance Blog.

Optimizer hints in MySQL 5.7.7 – The missed manual

April 30, 2015 - 12:00am

In version MySQL 5.7.7 Oracle presented a new promising feature: optimizer hints. However it did not publish any documentation about the hints. The only note which I found in the user manual about the hints is:

  • It is now possible to provide hints to the optimizer by including /*+ ... */ comments following the SELECT, INSERT, REPLACE, UPDATE, or DELETE keyword of SQL statements. Such statements can also be used with EXPLAIN. Examples:
    SELECT /*+ NO_RANGE_OPTIMIZATION(t3 PRIMARY, f2_idx) */ f1 FROM t3 WHERE f1 > 30 AND f1 < 33; SELECT /*+ BKA(t1, t2) */ * FROM t1 INNER JOIN t2 WHERE ...; SELECT /*+ NO_ICP(t1) */ * FROM t1 WHERE ...;

There are also three worklogs: WL #3996, WL #8016 and WL #8017. But they describe the general concept and do not have much information about which optimizations can be used and how. More light on this provided by slide 59 from Øystein Grøvlen’s session at Percona Live. But that’s all: no “official” full list of possible optimizations, no use cases… nothing.

I tried to sort it out myself.

My first finding is the fact that slide #59 really lists six of seven possible index hints. Confirmation for this exists in one of two new files under sql directory of MySQL source tree, created for this new feature.

$cat sql/opt_hints.h ... /** Hint types, MAX_HINT_ENUM should be always last. This enum should be synchronized with opt_hint_info array(see opt_hints.cc). */ enum opt_hints_enum { BKA_HINT_ENUM= 0, BNL_HINT_ENUM, ICP_HINT_ENUM, MRR_HINT_ENUM, NO_RANGE_HINT_ENUM, MAX_EXEC_TIME_HINT_ENUM, QB_NAME_HINT_ENUM, MAX_HINT_ENUM };

Looking into file sql/opt_hints.cc we can find out what these optimizations give not much choice: either enable or disable.

$cat sql/opt_hints.cc ... struct st_opt_hint_info opt_hint_info[]= { {"BKA", true, true}, {"BNL", true, true}, {"ICP", true, true}, {"MRR", true, true}, {"NO_RANGE_OPTIMIZATION", true, true}, {"MAX_EXECUTION_TIME", false, false}, {"QB_NAME", false, false}, {0, 0, 0} };

A choice for the way to include hints into SQL statements: inside comments with sign “+”/*+ NO_RANGE_OPTIMIZATION(t3 PRIMARY, f2_idx) */, – is compatible with style of optimizer hints which Oracle uses.

We actually had access to these hints before: they were accessible via variable optimizer_switch. At least such optimizations like BKA, BNL, ICP, MRR. But with new syntax we cannot only modify this access globally or per session, but can turn on or off particular optimization for a single table and column in the query. I can demonstrate it on this quite artificial but always accessible example:

mysql> use mysql Database changed mysql> explain select * from user where host in ('%', '127.0.0.1'); +----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-----------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-----------------------+ | 1 | SIMPLE | user | NULL | range | PRIMARY | PRIMARY | 180 | NULL | 2 | 100.00 | Using index condition | +----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-----------------------+ 1 row in set, 1 warning (0.01 sec) mysql> explain select /*+ NO_RANGE_OPTIMIZATION(user PRIMARY) */ * from user where host in ('%', '127.0.0.1'); +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ | 1 | SIMPLE | user | NULL | ALL | PRIMARY | NULL | NULL | NULL | 5 | 40.00 | Using where | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ 1 row in set, 1 warning (0.00 sec)

I used one more hint, which we could not turn on or off directly earlier: range optimization.

One more “intuitively” documented feature is the ability to turn on or off a particular optimization. This works only for BKA, BNL, ICP and MRR: you can specify NO_BKA(table[[, table]…]), NO_BNL(table[[, table]…]), NO_ICP(table indexes[[, table indexes]…]) and NO_MRR(table indexes[[, table indexes]…]) to avoid using these algorithms for particular table or index in the JOIN.

MAX_EXECUTION_TIME does not require any table or key name inside. Instead you need to specify maximum time in milliseconds which query should run:

mysql> select /*+ MAX_EXECUTION_TIME(1000) */ sleep(1) from user; ERROR 3024 (HY000): Query execution was interrupted, max_statement_time exceeded mysql> select /*+ MAX_EXECUTION_TIME(10000) */ sleep(1) from user; +----------+ | sleep(1) | +----------+ | 0 | | 0 | | 0 | | 0 | | 0 | +----------+ 5 rows in set (5.00 sec)

QB_NAME is more complicated. WL #8017 tells us this is custom context. But what is this? The answer is in the MySQL test suite! Tests for optimizer hints exist in file t/opt_hints.test For QB_NAME very first entry is query:

EXPLAIN SELECT /*+ NO_ICP(t3@qb1 f3_idx) */ f2 FROM (SELECT /*+ QB_NAME(QB1) */ f2, f3, f1 FROM t3 WHERE f1 > 2 AND f3 = 'poiu') AS TD WHERE TD.f1 > 2 AND TD.f3 = 'poiu';

So we can specify custom QB_NAME for any subquery and specify optimizer hint only for this context.

To conclude this quick overview I want to show a practical example of when query hints are really needed. Last week I worked on an issue where a customer upgraded from MySQL version 5.5 to 5.6 and found some of their queries started to work slower than before. I wrote an answer which could sound funny, but still remains correct: “One of the reasons for such behavior is optimizer  improvements. While they all are made for better performance, some queries – optimized for older versions – can start working slower than before.”

To demonstrate a public example of such a query I will use my favorite source of information: MySQL Community Bugs Database. In a search for Optimizer regression bugs that are still not fixed we can find bug #68919 demonstrating regression in case the MRR algorithm is used for queries with LIMIT. In run queries, shown in the bug report, we will see a huge difference:

mysql> SELECT * FROM t1 WHERE i1>=42 AND i2<=42 LIMIT 1; +----+----+----+----+ | pk | i1 | i2 | i3 | +----+----+----+----+ | 42 | 42 | 42 | 42 | +----+----+----+----+ 1 row in set (6.88 sec) mysql> explain SELECT * FROM t1 WHERE i1>=42 AND i2<=42 LIMIT 1; +----+-------------+-------+------------+-------+---------------+------+---------+------+---------+----------+----------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+------+---------+------+---------+----------+----------------------------------+ | 1 | SIMPLE | t1 | NULL | range | idx | idx | 4 | NULL | 9999958 | 33.33 | Using index condition; Using MRR | +----+-------------+-------+------------+-------+---------------+------+---------+------+---------+----------+----------------------------------+ 1 row in set, 1 warning (0.00 sec) mysql> SELECT /*+ NO_MRR(t1) */ * FROM t1 WHERE i1>=42 AND i2<=42 LIMIT 1; +----+----+----+----+ | pk | i1 | i2 | i3 | +----+----+----+----+ | 42 | 42 | 42 | 42 | +----+----+----+----+ 1 row in set (0.00 sec)

With MRR query execution takes 6.88 seconds and 0 if MRR is not used! But the bug report itself suggests usingoptimizer_switch="mrr=off";as a workaround. And this will work perfectly well if you are OK to runSET optimizer_switch="mrr=off";every time you are running a query which will take advantage of having it OFF. With optimizer hints you can have one or another algorithm to be ON for particular table in the query and OFF for another one. I, again, took quite an artificial example, but it demonstrates the method:

mysql> explain select /*+ MRR(dept_emp) */ * from dept_emp where to_date in (select /*+ NO_MRR(salaries)*/ to_date from salaries where salary >40000 and salary <45000) and emp_no >10100 and emp_no < 30200 and dept_no in ('d005', 'd006','d007'); +----+--------------+-------------+------------+--------+------------------------+------------+---------+----------------------------+---------+----------+-----------------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+--------------+-------------+------------+--------+------------------------+------------+---------+----------------------------+---------+----------+-----------------------------------------------+ | 1 | SIMPLE | dept_emp | NULL | range | PRIMARY,emp_no,dept_no | dept_no | 8 | NULL | 10578 | 100.00 | Using index condition; Using where; Using MRR | | 1 | SIMPLE | <subquery2> | NULL | eq_ref | <auto_key> | <auto_key> | 3 | employees.dept_emp.to_date | 1 | 100.00 | NULL | | 2 | MATERIALIZED | salaries | NULL | ALL | salary | NULL | NULL | NULL | 2838533 | 17.88 | Using where | +----+--------------+-------------+------------+--------+------------------------+------------+---------+----------------------------+---------+----------+-----------------------------------------------+ 3 rows in set, 1 warning (0.00 sec)

 

The post Optimizer hints in MySQL 5.7.7 – The missed manual appeared first on MySQL Performance Blog.

Generated (Virtual) Columns in MySQL 5.7 (labs)

April 29, 2015 - 3:00am

About 2 weeks ago Oracle published the MySQL 5.7.7-labs-json version which includes a very interesting feature called “Generated columns” (also know as Virtual or Computed columns). MariaDB has a similar feature as well: Virtual (Computed) Columns.

The idea is very simple: if we store a column

`FlightDate` date

in our table we may want to filter or group by year(FlightDate), month(FlightDate) or even dayofweek(FlightDate). The “brute-force” approach: use the above Date and Time MySQL functions in the query; however it will prevent MySQL from using an index (see below). Generated columns will allow you to declare a “Virtual”, non-stored column which is computed based on the existing field; you can then add index on that virtual column, so the query will use that index.

Here is the original example:

CREATE TABLE `ontime` ( `id` int(11) NOT NULL AUTO_INCREMENT, `FlightDate` date DEFAULT NULL, `Carrier` char(2) DEFAULT NULL, `OriginAirportID` int(11) DEFAULT NULL, `OriginCityName` varchar(100) DEFAULT NULL, `OriginState` char(2) DEFAULT NULL, `DestAirportID` int(11) DEFAULT NULL, `DestCityName` varchar(100) DEFAULT NULL, `DestState` char(2) DEFAULT NULL, `DepDelayMinutes` int(11) DEFAULT NULL, `ArrDelayMinutes` int(11) DEFAULT NULL, `Cancelled` tinyint(4) DEFAULT NULL, `CancellationCode` char(1) DEFAULT NULL, `Diverted` tinyint(4) DEFAULT NULL, PRIMARY KEY (`id`), KEY `FlightDate` (`FlightDate`) ) ENGINE=InnoDB

Now I want to find all flights on Sundays (in 2013) and group by airline.

mysql> EXPLAIN SELECT carrier, count(*) FROM ontime_sm WHERE dayofweek(FlightDate) = 7 group by carrier *************************** 1. row *************************** id: 1 select_type: SIMPLE table: ontime_sm type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 151253427 Extra: Using where; Using temporary; Using filesort Results: 32 rows in set (1 min 57.93 sec)

The problem here is: MySQL will not be able to use index when you use a function which will “extract” something from the column. The standard approach is to “materialize” the column:

ALTER TABLE ontime_sm ADD Flight_dayofweek tinyint NOT NULL;

Then we will need to load data into that by running “UPDATE ontime_sm SET Flight_dayofweek = dayofweek(flight_date)”. After that we will also need to change the application to support that additional column or use a trigger to update the column. Here is the trigger example:

CREATE DEFINER = CURRENT_USER TRIGGER ontime_insert BEFORE INSERT ON ontime_sm_triggers FOR EACH ROW SET NEW.Flight_dayofweek = dayofweek(NEW.FlightDate);

One problem with the trigger is that it is slow. In my simple example it took almost 2x slower to “copy” the table using “insert into ontime_sm_copy select * from ontime_sm” when the trigger was on.

The Generated Columns from MySQL 5.7.7-labs-json version (only this version supports it on the time of writing) solves this problem. Here is the example which demonstrate its use:

CREATE TABLE `ontime_sm_virtual` ( `id` int(11) NOT NULL AUTO_INCREMENT, `FlightDate` date DEFAULT NULL, `Carrier` char(2) DEFAULT NULL, `OriginAirportID` int(11) DEFAULT NULL, `OriginCityName` varchar(100) DEFAULT NULL, `OriginState` char(2) DEFAULT NULL, `DestAirportID` int(11) DEFAULT NULL, `DestCityName` varchar(100) DEFAULT NULL, `DestState` char(2) DEFAULT NULL, `DepDelayMinutes` int(11) DEFAULT NULL, `ArrDelayMinutes` int(11) DEFAULT NULL, `Cancelled` tinyint(4) DEFAULT NULL, `CancellationCode` char(1) DEFAULT NULL, `Diverted` tinyint(4) DEFAULT NULL, `CRSElapsedTime` int(11) DEFAULT NULL, `ActualElapsedTime` int(11) DEFAULT NULL, `AirTime` int(11) DEFAULT NULL, `Flights` int(11) DEFAULT NULL, `Distance` int(11) DEFAULT NULL, `Flight_dayofweek` tinyint(4) GENERATED ALWAYS AS (dayofweek(FlightDate)) VIRTUAL, PRIMARY KEY (`id`), KEY `Flight_dayofweek` (`Flight_dayofweek`), ) ENGINE=InnoDB

Here we add Flight_dayofweek tinyint(4) GENERATED ALWAYS AS (dayofweek(FlightDate)) VIRTUAL column and index it.

Now MySQL can use this index:

mysql> EXPLAIN SELECT carrier, count(*) FROM ontime_sm_virtual WHERE Flight_dayofweek = 7 group by carrier *************************** 1. row *************************** id: 1 select_type: SIMPLE table: ontime_sm_virtual partitions: NULL type: ref possible_keys: Flight_dayofweek key: Flight_dayofweek key_len: 2 ref: const rows: 165409 filtered: 100.00 Extra: Using where; Using temporary; Using filesort

To further increase performance of this query we want to add a combined index on (Flight_dayofweek, carrier) so MySQL will avoid creating temporary table. However it is not currently supported:

mysql> alter table ontime_sm_virtual add key comb(Flight_dayofweek, carrier); ERROR 3105 (HY000): 'Virtual generated column combines with other columns to be indexed together' is not supported for generated columns.

We can add an index on 2 generated columns thou, which is good. So a trick here will be to create a “dummy” virtual column on “carrier” and index 2 of those columns:

mysql> alter table ontime_sm_virtual add Carrier_virtual char(2) GENERATED ALWAYS AS (Carrier) VIRTUAL; Query OK, 0 rows affected (0.43 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> alter table ontime_sm_virtual add key comb(Flight_dayofweek, Carrier_virtual); Query OK, 999999 rows affected (36.79 sec) Records: 999999 Duplicates: 0 Warnings: 0 mysql> EXPLAIN SELECT Carrier_virtual, count(*) FROM ontime_sm_virtual WHERE Flight_dayofweek = 7 group by Carrier_virtual *************************** 1. row *************************** id: 1 select_type: SIMPLE table: ontime_sm_virtual partitions: NULL type: ref possible_keys: Flight_dayofweek,comb key: comb key_len: 2 ref: const rows: 141223 filtered: 100.00 Extra: Using where; Using index

Now MySQL will use an index and completely avoid the filesort.

The last, but not the least: loading data to the table with generated columns is significantly faster compared to loading it into the same table with triggers:

mysql> insert into ontime_sm_triggers (id, YearD, FlightDate, Carrier, OriginAirportID, OriginCityName, OriginState, DestAirportID, DestCityName, DestState, DepDelayMinutes, ArrDelayMinutes, Cancelled, CancellationCode,Diverted, CRSElapsedTime, ActualElapsedTime, AirTime, Flights, Distance) select * from ontime_sm; Query OK, 999999 rows affected (27.86 sec) Records: 999999 Duplicates: 0 Warnings: 0 mysql> insert into ontime_sm_virtual (id, YearD, FlightDate, Carrier, OriginAirportID, OriginCityName, OriginState, DestAirportID, DestCityName, DestState, DepDelayMinutes, ArrDelayMinutes, Cancelled, CancellationCode,Diverted, CRSElapsedTime, ActualElapsedTime, AirTime, Flights, Distance) select * from ontime_sm; Query OK, 999999 rows affected (16.29 sec) Records: 999999 Duplicates: 0 Warnings: 0

Now the big disappointment: all operations with generated columns are not online right now.

mysql> alter table ontime_sm_virtual add Flight_year year GENERATED ALWAYS AS (year(FlightDate)) VIRTUAL, add key (Flight_year), lock=NONE; ERROR 1846 (0A000): LOCK=NONE is not supported. Reason: '%s' is not supported for generated columns.. Try LOCK=SHARED. mysql> alter table ontime_sm_virtual add key (Flight_year), lock=NONE; ERROR 1846 (0A000): LOCK=NONE is not supported. Reason: '%s' is not supported for generated columns.. Try LOCK=SHARED.

I hope it will be fixed in the future releases.

Conclusion

Generated columns feature is very useful. Imagine an ability to add a column + index for any “logical” piece of data without actually duplicating the data. And this can be any function: date/time/calendar, text (extract(), reverse(), metaphone()) or anything else. I hope this feature will be available in MySQL 5.7 GA. Finally, I wish adding a generated column and index can be online (it is not right now).

More information:

The post Generated (Virtual) Columns in MySQL 5.7 (labs) appeared first on MySQL Performance Blog.

Generated (Virtual) Columns in MySQL 5.7 (labs)

April 29, 2015 - 3:00am

About 2 weeks ago Oracle published the MySQL 5.7.7-labs-json version which includes a very interesting feature called “Generated columns” (also know as Virtual or Computed columns). MariaDB has a similar feature as well: Virtual (Computed) Columns.

The idea is very simple: if we store a column

`FlightDate` date

in our table we may want to filter or group by year(FlightDate), month(FlightDate) or even dayofweek(FlightDate). The “brute-force” approach: use the above Date and Time MySQL functions in the query; however it will prevent MySQL from using an index (see below). Generated columns will allow you to declare a “Virtual”, non-stored column which is computed based on the existing field; you can then add index on that virtual column, so the query will use that index.

Here is the original example:

CREATE TABLE `ontime` ( `id` int(11) NOT NULL AUTO_INCREMENT, `FlightDate` date DEFAULT NULL, `Carrier` char(2) DEFAULT NULL, `OriginAirportID` int(11) DEFAULT NULL, `OriginCityName` varchar(100) DEFAULT NULL, `OriginState` char(2) DEFAULT NULL, `DestAirportID` int(11) DEFAULT NULL, `DestCityName` varchar(100) DEFAULT NULL, `DestState` char(2) DEFAULT NULL, `DepDelayMinutes` int(11) DEFAULT NULL, `ArrDelayMinutes` int(11) DEFAULT NULL, `Cancelled` tinyint(4) DEFAULT NULL, `CancellationCode` char(1) DEFAULT NULL, `Diverted` tinyint(4) DEFAULT NULL, PRIMARY KEY (`id`), KEY `FlightDate` (`FlightDate`) ) ENGINE=InnoDB

Now I want to find all flights on Sundays (in 2013) and group by airline.

mysql> EXPLAIN SELECT carrier, count(*) FROM ontime_sm WHERE dayofweek(FlightDate) = 7 group by carrier *************************** 1. row *************************** id: 1 select_type: SIMPLE table: ontime_sm type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 151253427 Extra: Using where; Using temporary; Using filesort Results: 32 rows in set (1 min 57.93 sec)

The problem here is: MySQL will not be able to use index when you use a function which will “extract” something from the column. The standard approach is to “materialize” the column:

ALTER TABLE ontime_sm ADD Flight_dayofweek tinyint NOT NULL;

Then we will need to load data into that by running “UPDATE ontime_sm SET Flight_dayofweek = dayofweek(flight_date)”. After that we will also need to change the application to support that additional column or use a trigger to update the column. Here is the trigger example:

CREATE DEFINER = CURRENT_USER TRIGGER ontime_insert BEFORE INSERT ON ontime_sm_triggers FOR EACH ROW SET NEW.Flight_dayofweek = dayofweek(NEW.FlightDate);

One problem with the trigger is that it is slow. In my simple example it took almost 2x slower to “copy” the table using “insert into ontime_sm_copy select * from ontime_sm” when the trigger was on.

The Generated Columns from MySQL 5.7.7-labs-json version (only this version supports it on the time of writing) solves this problem. Here is the example which demonstrate its use:

CREATE TABLE `ontime_sm_virtual` ( `id` int(11) NOT NULL AUTO_INCREMENT, `FlightDate` date DEFAULT NULL, `Carrier` char(2) DEFAULT NULL, `OriginAirportID` int(11) DEFAULT NULL, `OriginCityName` varchar(100) DEFAULT NULL, `OriginState` char(2) DEFAULT NULL, `DestAirportID` int(11) DEFAULT NULL, `DestCityName` varchar(100) DEFAULT NULL, `DestState` char(2) DEFAULT NULL, `DepDelayMinutes` int(11) DEFAULT NULL, `ArrDelayMinutes` int(11) DEFAULT NULL, `Cancelled` tinyint(4) DEFAULT NULL, `CancellationCode` char(1) DEFAULT NULL, `Diverted` tinyint(4) DEFAULT NULL, `CRSElapsedTime` int(11) DEFAULT NULL, `ActualElapsedTime` int(11) DEFAULT NULL, `AirTime` int(11) DEFAULT NULL, `Flights` int(11) DEFAULT NULL, `Distance` int(11) DEFAULT NULL, `Flight_dayofweek` tinyint(4) GENERATED ALWAYS AS (dayofweek(FlightDate)) VIRTUAL, PRIMARY KEY (`id`), KEY `Flight_dayofweek` (`Flight_dayofweek`), ) ENGINE=InnoDB

Here we add Flight_dayofweek tinyint(4) GENERATED ALWAYS AS (dayofweek(FlightDate)) VIRTUAL column and index it.

Now MySQL can use this index:

mysql> EXPLAIN SELECT carrier, count(*) FROM ontime_sm_virtual WHERE Flight_dayofweek = 7 group by carrier *************************** 1. row *************************** id: 1 select_type: SIMPLE table: ontime_sm_virtual partitions: NULL type: ref possible_keys: Flight_dayofweek key: Flight_dayofweek key_len: 2 ref: const rows: 165409 filtered: 100.00 Extra: Using where; Using temporary; Using filesort

To further increase performance of this query we want to add a combined index on (Flight_dayofweek, carrier) so MySQL will avoid creating temporary table. However it is not currently supported:

mysql> alter table ontime_sm_virtual add key comb(Flight_dayofweek, carrier); ERROR 3105 (HY000): 'Virtual generated column combines with other columns to be indexed together' is not supported for generated columns.

We can add an index on 2 generated columns thou, which is good. So a trick here will be to create a “dummy” virtual column on “carrier” and index 2 of those columns:

mysql> alter table ontime_sm_virtual add Carrier_virtual char(2) GENERATED ALWAYS AS (Carrier) VIRTUAL; Query OK, 0 rows affected (0.43 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> alter table ontime_sm_virtual add key comb(Flight_dayofweek, Carrier_virtual); Query OK, 999999 rows affected (36.79 sec) Records: 999999 Duplicates: 0 Warnings: 0 mysql> EXPLAIN SELECT Carrier_virtual, count(*) FROM ontime_sm_virtual WHERE Flight_dayofweek = 7 group by Carrier_virtual *************************** 1. row *************************** id: 1 select_type: SIMPLE table: ontime_sm_virtual partitions: NULL type: ref possible_keys: Flight_dayofweek,comb key: comb key_len: 2 ref: const rows: 141223 filtered: 100.00 Extra: Using where; Using index

Now MySQL will use an index and completely avoid the filesort.

The last, but not the least: loading data to the table with generated columns is significantly faster compared to loading it into the same table with triggers:

mysql> insert into ontime_sm_triggers (id, YearD, FlightDate, Carrier, OriginAirportID, OriginCityName, OriginState, DestAirportID, DestCityName, DestState, DepDelayMinutes, ArrDelayMinutes, Cancelled, CancellationCode,Diverted, CRSElapsedTime, ActualElapsedTime, AirTime, Flights, Distance) select * from ontime_sm; Query OK, 999999 rows affected (27.86 sec) Records: 999999 Duplicates: 0 Warnings: 0 mysql> insert into ontime_sm_virtual (id, YearD, FlightDate, Carrier, OriginAirportID, OriginCityName, OriginState, DestAirportID, DestCityName, DestState, DepDelayMinutes, ArrDelayMinutes, Cancelled, CancellationCode,Diverted, CRSElapsedTime, ActualElapsedTime, AirTime, Flights, Distance) select * from ontime_sm; Query OK, 999999 rows affected (16.29 sec) Records: 999999 Duplicates: 0 Warnings: 0

Now the big disappointment: all operations with generated columns are not online right now.

mysql> alter table ontime_sm_virtual add Flight_year year GENERATED ALWAYS AS (year(FlightDate)) VIRTUAL, add key (Flight_year), lock=NONE; ERROR 1846 (0A000): LOCK=NONE is not supported. Reason: '%s' is not supported for generated columns.. Try LOCK=SHARED. mysql> alter table ontime_sm_virtual add key (Flight_year), lock=NONE; ERROR 1846 (0A000): LOCK=NONE is not supported. Reason: '%s' is not supported for generated columns.. Try LOCK=SHARED.

I hope it will be fixed in the future releases.

Conclusion

Generated columns feature is very useful. Imagine an ability to add a column + index for any “logical” piece of data without actually duplicating the data. And this can be any function: date/time/calendar, text (extract(), reverse(), metaphone()) or anything else. I hope this feature will be available in MySQL 5.7 GA. Finally, I wish adding a generated column and index can be online (it is not right now).

More information:

The post Generated (Virtual) Columns in MySQL 5.7 (labs) appeared first on MySQL Performance Blog.

Pages

]]>