The MySQL 5.7.8 Release Candidate is Available

The MySQL Development team is very happy to announce that MySQL 5.7.8, the second 5.7 Release Candidate (RC2), is now available for download at dev.mysql.com (use the “Development Releases” tab).

We have fixed over 500 bugs in 5.7.8. This is on top of what we delivered in 5.7.7 and on top of bug fixes up-merged from 5.6. We have also added JSON support, which we previewed in a Labs release back in April. You can find the full list of changes and bug fixes in the 5.7.8 release notes. Here are some highlights. Enjoy!

Adding JSON Support to MySQL

With the newly added JSON support in MySQL, you can now combine the flexibility of NoSQL with the strength of a relational database.

JSON datatype and binary storage format (WL#8132) — This work by Knut Anders Hatlen develops a binary format that allows the server to efficiently store, retrieve and search JSON data. This work enhances CREATE TABLE and ALTER TABLE so that they can create JSON columns, and it extends the Field class to allow INSERT to and SELECT from JSON typed fields.

Server side JSON functions (WL#7909) — This work by Richard Hillegas and Dag Wanvik introduces a set of a built-in JSON functions. This work lets users construct JSON data values from other relational data, extract relational data from JSON data values, introspect the structure of JSON values and text (validity, length, depth, keys), search within, and manipulate JSON data.

JSON comparator (WL#8249) — This work by Knut Anders Hatlen introduces the JSON comparator, similar to the DATE/TIME/DATETIME comparator, which allow comparisons of JSON scalars vs SQL constants, and JSON scalars vs JSON scalars. The comparator relies on the DOM support added in the scope of WL#7909. The comparator converts the SQL constant to a JSON scalar and then compares the values.

Ordering of scalar JSON values (WL#8539)  — This work by Knut Anders Hatlen implements a function that produces the sorting keys that the internal filesort function needs in order to properly sort JSON values. When ordering scalar JSON values with ORDER BY, they will be returned in the order defined by the JSON comparator in WL#8249.

Expression analyzer for generated columns (WL#8170) — This work by Evgeny Potempkin allows our range and ref optimizers to find opportunities to use any indexes defined over generated columns. One intended use case for this feature is to allow the creation and automatic use of indexes on JSON Documents.

Virtual Columns

B-tree Index Support on non-materialized virtual columns  (WL#8149, WL#8114) — This work by Jimmy Yang supports the creation of secondary indexes on non-materialized virtual columns, as well as the usage of these indexes for fast computed-value retrieval and searches. The non-materialized virtual column support is described in WL#8114 and WL#411. These were designed in such way that they are not present in actual InnoDB index records, but their metadata is registered with InnoDB system tables and metadata caches. Virtual columns provide flexibility and space savings for the table, and more importantly, adding/dropping such columns does not require a table rebuild. These behaviors make it a much better choice for storing and processing non-relational data such as JSON. However, since the columns themselves are not materialized, a scan and search could be slower than on regular (materialized) columns. With this worklog, the virtual column value is materialized in the secondary index, thus making it much easier for value searches and processing. Thus this work greatly increases the practical value of virtual columns. With this work, creating an index on virtual generated columns also becomes an ONLINE operation.

Support SEs to create index on virtual generated columns (WL#8227) — This work by Benny Wang implements server layer support for WL#8149, for example storing information about generated columns in the internal Field::gcol_info data structure.

Callback for computation of virtual column index values from InnoDB purge threads  (WL#8481) — This work by Jon Olav Hauglid provides a server layer function that can be called by InnoDB purge threads. Background: InnoDB needs to be able to compute virtual column index values in order to implement WL#8149 (B-tree Index Support on non-materialized virtual columns). Generally this is done from connection threads (see WL#8227). However, it also needs to happen from InnoDB purge threads, and these threads do not correspond to connections/sessions and thus don’t have THDs or access to TABLE objects. This work provides the necessary server layer callback which enables the purge threads to make the necessary computations. There are no user-observable changes by this worklog, it is only relevant in conjunction with WL#8149 and WL#8227.

Page Compression

InnoDB: Transparent page compression (WL#7696) — This work by Sunny Bains implements compression at the InnoDB layer. The feature works on any OS/file system combination that supports sparse files and has “punch hole” support (e.g. the FALLOC_FL_PUNCH_HOLE flag to fallocate). The high level idea is rather simple—given a 16K page we compress it using your favorite compression algorithm and write out only the compressed data. After writing out the data we “punch a hole” to release the unused part of the original 16K block back to the file system.

User Management

Increase the length of a user name (WL#2284) — This work by Robert Golebiowski increases the maximum length of MySQL user names from 16 to 32 characters.

Support IF [NOT] EXISTS clause in CREATE/DROP USER (WL#8540) — This work by Todd Farmer implements the IF [NOT] EXISTS clause in CREATE USER and DROP USER statements. This will allow for distribution of accounts using replication without triggering replication failures in the event of (intentionally) non-synchronized accounts within the replication group. It also simplifies user scripting of account management operations. See also: the feature request in Bug#15287.

Add server-side option to require secure transport (WL#7709) — This work by Todd Farmer adds the --require_secure_transport server option, which causes the MySQL Server to refuse any TCP/IP connection not using SSL. Before this work, the MySQL Server had only provided a mechanism to require SSL for individual user accounts using the REQUIRE SSL clause in various account management statements.

Control Storage Engine Usage

Provide an option to reject creation of user tables for a list of storage engines (WL#8594) — This work by Thayumanavar Sachithanantha provides a command-line option called --disabled-storage-engines which provides the means for a DBA to supply a list of storage engines to be disallowed, thus preventing users from using any particular storage engine (by mistake). This is done to support the use case where the customer has a policy to prevent the usage of, for example, MyISAM within their environment.

Super Read Only

Super-read-only that also blocks SUPER users (WL#6799) — This work by Todd Farmer introduces a new option, --super_read_only, which supplements the --read_only option. When --super_read_only is set to ON (which also automatically sets --read_only to ON) then the server also becomes READ-ONLY for admin users with the SUPER privilege.

New Data Export Utility

mysqlpump: Extend mysqldump functionalities (WL#7755) — This work by Bharathy Satish implements a new MySQL Server utility inspired by — but not 100% compatible with — mysqldump. The main feature of this new tool is the ability to parallelize backup and restore operations. (The existing mysqldump will continue to be supported.)

Cost Model

IO aware cost estimate function for data access (WL#7340) — This work by Olav Sandstå extends the optimizer cost model to use the estimates from the storage engines about approximately how much of a table’s rows and indexes are present in memory. The optimizer will then use different cost constants for calculating the cost of accessing data that is in memory and data that needs to be read from disk. In the initial implementation, these two cost constants have the same default value but can be changed by the server administrator to make the optimizer use different costs for data in a memory buffer versus data needed to be read from disk. Note that currently, the estimates about whether data is in memory or needs to be read from disk is just based on heuristics. The accuracy of these estimates will be greatly improved when support for these estimates are implemented by the individual storage engines.

Optimizer Hints

Hints for subquery strategies (WL#8244) — This work by Øystein Grøvlen adds hints for controlling subquery execution strategies. This includes whether or not to use semi-join, which semi-join strategy to use, and, in case semi-join is not used, whether to use subquery materialization or the in-to-exists transformation.  This work uses the new syntax and infrastructure for hints provided by WL#8016 and WL#8017. Note that this work makes it possible to prevent any specific semi-join strategy, including the Duplicate Weed-out strategy which cannot be turned off using --optimizer_switch.

Observability / Monitoring

Instrumentation of connection type (WL#7729) — This work by Todd Farmer exposes information about connection types via standard interfaces—the PERFORMANCE_SCHEMA.THREADS table, the Audit log API, the Audit log file, and the General Query Log. Until now MySQL has not provided DBA visibility into the types of connections being established and used; e.g. to distinguish SSL connections from unencrypted TCP/IP connections, or socket, shared memory or named pipe connections.

InnoDB: Implement Information_Schema.Files (WL#7943) — This work by Kevin Lewis provides data for the INFORMATION_SCHEMA.FILES table, for all of the InnoDB datafiles that are in its internal cache. The INFORMATION_SCHEMA.FILES table contains fields to describe a tablespace file, including some statistical details. See also Bug#76182 reported by Marco Tusa.

PERFORMANCE SCHEMA, HISTORY PER THREAD (WL#7795) — This work by Marc Alff enables turning the history ON or OFF on a per thread basis. Until now, global consumer flags have been used to control whether or not to log history events. These flags are global to the server, so that the collection of historical data for different threads was all or nothing. With this feature, the DBA can now specify which sessions, accounts, users, and hosts for which they wish to collect historical data, separatly from turning the instrumentation ON or OFF. This allows the DBA to control more precisely what events are logged in the history tables, thus decreasing the runtime overhead when historical data is needed only for a subset of the instrumented sessions, as well as reducing unwanted noise in the Performance Schema tables—events_waits_history_long, events_stages_history_long, events_statements_history_long, events_transactions_history_long—which facilitate troubleshooting on busy servers (that also generate a lot of events).

Fabric Support

Detect transaction boundaries (WL#6631)  — This work by Tatjana Nurnberg adds a server flag to detect that a new transaction has started. A new system variable called @@session_track_transaction_state has been introduced. In a load-balanced setup, it is necessary to know when a statement resulted in the start of a new transaction, which would then allow connectors to switch to using a different connection from the connection pool. The critical case to detect is when the transaction is “fresh” and does not have any reads or writes attached to it yet. This is the only case where a connector can switch to using a different connection. If a statement starts a new transaction and starts adding reads or writes to it, it is not possible to move the connection since it would imply a necessary ROLLBACK.

Server version token and check (WL#6940) — This work by Vamsikrishna Bhagi introduces a general synchronization mechanism based on DBA/Application defined tokens. A user with the SUPER privilege can set global tokens in a server (for example some ID) and clients can set session tokens. Before executing a query the session token is compared with the global token and an error generated when there is a discrepancy between the two.

Locking service for read/write named locks (WL#8161) — This work by Jon Olav Hauglid provides a locking service to protect global and session version token lists needed by WL#6940.

Refactoring

Refactoring of the protocol class (WL#7126) — This work by Catalin Besleaga makes an initial step towards decoupling the client-server protocol from SQL processing.

Remove fastmutex from the server sources (WL#4601) — This work by Jon Olav Hauglid removes a home grown spin-lock mutex implementation— called “fast mutex”—from the server sources because of its many shortcomings.

Test Infrastructure

Plugin(s) to test services of server (WL#8462) — This work by Horst Hunger introduces a framework for writing test plugins, for the purpose of testing new service APIs.

Changes to Defaults

Turn STRICT_MODE submodes ON by Default (WL#8596) — This work by Abhishek Ranjan reverts changes done by WL#7467, bringing back the individual NO_ZERO_DATE, NO_ZERO_IN_DATE, ERROR_FOR_DIVISION_BY_ZERO sql modes. These modes will instead be added to the set of sql modes which are enabled by default. WL#7467 merged those sql modes into STRICT mode. This added more checks to STRICT mode, causing some statements which used to pass in 5.6 with a warning in STRICT mode to to fail in 5.7. This in turn caused problems in some upgrade scenarios that could not be easily worked around. See also Bug#75439 reported by Simon Mudd.

Increase table_open_cache_instances compiled default (WL#8397) — This work by Praveenkumar Hulakund increases the --table_open_cache_instances compiled-in default from 1 (old) to 16 (new). This will improve performance on multi-core systems.

Enabled multiple page cleaners and purge threads by default in 5.7 (WL#8316) — This work by Bin Su changes the compiled-in defaults for --innodb_page_cleaners and --innodb_purge_threads from 1 (old) to 4 (new). This will improve performance and stability on multi-core systems.

Deprecation and Removal

Remove sql-bench from the 5.7 server code (WL#8406) — This work by Terje Røsten removes the sql-bench code from the 5.7 server code tree. We decided to remove it because it has not been well maintained for recent versions, and we are no longer using it internally as part of our testing process. See also Morgan Tocker’s blog post here.

Remove use of DATADIR in mysqld_safe for setting MYSQL_HOME in 5.7 (WL#7150) — This work by Yashwant Sahu removes the use of DATADIR in mysqld_safe. A warning about this had existed since MySQL 5.0. From now on, if MYSQL_HOME is not set, then it will be set to BASEDIR.

Bug Fixes

We have received a lot of valuable feedback from the MySQL community throughout the 5.7 DMR cycle. This input is invaluable, and our community members deserve a large THANK YOU! Here are some highlights of the community reported bugs that are now fixed in 5.7.8 RC2:

  • Bug#75913, Bug#76446, Bug#76346, Bug#76437, Bug#76436, Bug#76432, Bug#76434, Bug#76445, Bug#76424, Bug#76425, Bug#76419, Bug#76416, Bug#76406, Bug#75782, Bug#75736 — All reported by Roel Van de Paar
  • Bug#74891, Bug#76625, Bug#74833, Bug#74854, Bug#74843, Bug#72806, Bug#72807, Bug#72805 — All reported by Stewart Smith
  • Bug#73953: left join to view with <> test causing too many results — Reported by David Norman
  • Bug#77276: force index not working with groupby+orderby — Reported by bombzj bombzj
  • Bug#76748: the server crash when test st_intersects with st_buffer — Reported by zkong kong
  • Bug#76401: Can’t distinguish secure_file_priv = NULL and “” — Reported by tsubasa tanaka
  • Bug#76329: COLLATE option not accepted in generated column definition — Reported by Mario Beck
  • Bug#76328: Generated columns not printed correctly in SHOW CREATE TABLE — Reported by Mario Beck
  • Bug#76237: LOAD DATA INFILE ignores a specific row silently under Db Charset is utf8 — Reported by tsubasa tanaka
  • Bug#76182: The new created tablespace is not reported in the INFORMATION_SCHEMA — Reported by Marco Tusa
  • Bug#76164: InnoDB FTS with MeCab parser prints empty error message — Reported by tsubasa tanaka
  • Bug#75995: mysqld –help –verbose tries to lock files — Reported by Daniël van Eeden
  • Bug#75595: Compute InnoDB redo log block checksums faster — Reported by Laurynas Biveinis
  • Bug#75829: ST_Centroid produces incorrect results with MultiPolygon — Reported by Bryan Blakey
  • Bug#75539: max_allowed_packet error destroys original data — Reported by Oli Sennhauser
  • Bug#75372: incorrect code(or indenting) — Reported by Joshua Rogers
  • Bug#3083: User length limitation — Reported by Dracul Vlad
  • Bug#74177: –slave-preserve-commit-order causes slave to deadlock and break for some querie — Reported by Kristian Nielsen
  • Bug#74253: gtid_next with empty transactions does not work inside stored procedures — Reported by Davi Arnaut
  • Bug#70860: –tc-heuristic-recover option values are broken — Reported by Laurynas Biveinis
  • Bug#69425: St_Intersection produces incorrect results — Reported by Ivan Balashov
  • Bug#69538: Spatial geometry function returns wrong result — Reported by Vaclav Novotny
  • Bug#72056: Wrong comparsion on big DECIMAL values — Reported by Artem Zaytsev
  • Bug#69903: Stack corruption in vio_io_wait on Mac OS X — Reported by Sergei Glushchenko
  • Bug#67014: View evaluation is incorrect when joining to view that selects a literal — Reported by Luke Stevens
  • Bug#67300: table left join view, unmatched rows problem where view contains an if — Reported by David Norman
  • Bug#65936: wrong results on table left join view — Reported by Richard Kojedzinszky
  • Bug#55265: mysqlslap Option –auto-generate-sql-secondary-indexes doesnt work — Reported by Andreas Braza
  • Bug#37703: MySQL performance with and without Fast Mutexes using Sysbench Workload — Reported by Harita Chilukuri

You can also learn about the work that we did in 5.7, leading up to this second release candidate, through this series of milestone blog posts: 5.7.1, 5.7.2, 5.7.3, 5.7.4, 5.7.5, 5.7.65.7.7.

That’s it for now, and thanks for using MySQL!

New Optimizer Hints in MySQL

The MySQL optimizer has a well known special system variable—optimizer_switch—which allows control of various optimizer modes such as index condition pushdown (ICP), batched key access, etc. One big disadvantage of using the optimizer_switch, however, is that changing its value is an extra step in the query execution. Another disadvantage of the optimizer_switch is that it affects the entire statement whereas sometimes it’s desirable or necessary to change only the behavior of a specific table or query block. In order to address these issues and improve usability, new hints were added to allow for greater and more fine-grained control of optimizer behavior directly within SQL statements.

A few key points regarding the new hints:

  • Comment syntax /*+ */ is used for the new hints.
  • Multiple hints can be specified within the same comment.
  • A query block can have only one comment containing hints, and that comment must directly follow the SELECT, UPDATE, INSERT, REPLACE, or DELETE keyword.
  • Incorrect and/or invalid syntax within a hint will cause a warning.
  • The first conflicting hint will take effect, while subsequent conflicting/duplicate hints are ignored with a warning.
  • Multilevel hints are supported. For example, a hint can affect only a specific table or an individual query block.

The following hints were added in MySQL 5.7.7:

  • BKA, NO_BKA — control use of the Batched Key Access algorithm for a particular table or query block.
  • BNL, NO_BNL — control use of the Block Nested-Loop algorithm for a particular table or query block.
  • MRR, NO_MRR — control the Multi-Range Read strategy for a particular index or table.
  • NO_ICP — disables the use of index condition pushdown for a particular index or table.
  • NO_RANGE_OPTIMIZATION — disables the use of the range access for a particular index or table.
  • MAX_EXECUTION_TIME — sets the statement execution timeout at N milliseconds.
  • QB_NAME — an auxiliary hint for naming a particular query block. This name can then be used in the later hint specification in order to greater simplify the use of hints in complex compound statements.

In MySQL 5.7.8, Oystein Grovlen added hints for controlling the subquery optimizer strategies:

  • SEMIJOIN, NO_SEMIJOIN — enable or disable the named semi-join strategy.
  • SUBQUERY — affects whether to use subquery materialization or IN-to-EXISTS transformations.

For additional details on the new hint work, please see the new section within the manual.

I hope that you find the new hint work helpful! If you have any questions or encounter any problems with the new hints, please let us know here in the comments, open a bug report at bugs.mysql.com, or open a support ticket.

THANK YOU for using MySQL!

 

Improved Server Defaults in 5.7

Morgan and I started an initiative a while back to improve the “out of the box” configuration and behavior defaults for MySQL. Working closely with the Community, we were able to come up with a good list of improvements. This work really began to bear fruit starting with the MySQL 5.7.7 release. I wanted to quickly run down what was changed, and why it provides a better MySQL experience for the average user and installation.

Replication Improvements

  • Please see Sujatha’s excellent blog post for the details.

InnoDB Improvements

  • innodb_checksum_algorithm — We changed the default from “innodb” to “crc32″. With CRC32 hardware accelerations available in most server machines today, this should offer a modest overall performance boost.
  • innodb_page_cleaners — We changed the default from 1 to 4. This makes the process of flushing dirty pages from the buffer pool multi-threaded by default (although it will be automatically resized down if your innodb_buffer_pool_instances is lower). This should help to improve overall performance on busy systems.
  • innodb_purge_threads — We changed the default from 1 to 4. This makes the process of purges—removing obsolete values from indexes and physically removing rows that were marked for deletion by previous DELETE statements—multi-threaded by default. This should also improve overall performance on busy systems.
  • innodb_strict_mode — We changed the default from OFF to ON. This brings InnoDB in line with our overall goal of making MySQL behavior more strict by default, thus helping you ensure the integrity, validity, and durability of your data. This also falls in line with a parallel goal of making MySQL more SQL standard compliant by default.
  • Warm InnoDB Buffer Pool — With the following changes you will maintain a warm cache through restarts of MySQL, retaining the “hottest” 25% of the pages from the Buffer Pool: innodb_buffer_pool_dump_at_shutdown, innodb_buffer_pool_load_at_startup, and innodb_buffer_pool_dump_pct. For additional information, see this excellent blog post by Tony. This helps to lessen the application performance impacts of MySQL restarts.
  • innodb_file_format — We’ve made the newest file format—Barracuda—the default. This eliminates any limitations on the row formats that you can use. This makes all available features, such as compression, available by default (avoiding unnecessary MySQL restarts) and results in a better overall user experience.
  • innodb_large_prefix — This increases the limit on index key prefixes from 767 bytes, up to 3072 bytes. This is important, especially as more and more users default to Unicode character sets.

Performance Schema Improvements

  • Enabling Additional Consumers — We’ve enabled the events_statements_history and events_transactions_history consumers by default. This provides very helpful information to DBAs needing to know what statements and transactions have executed recently on the system. This information is invaluable when tracking down myriad problems (anything related to what queries were being executed at a given time). And thanks to the ongoing efforts to lower the overhead of Performance Schema, we were able to enable these with very minimal impact on performance (see Tarique’s excellent blog post for more information regarding overhead).

Security Improvements

  • sql_mode — We made NO_AUTO_CREATE_USER a default. This is to prevent the GRANT statement from unintentionally and automatically creating new user accounts without any authentication information having been specified/provided. This is part of our larger effort around making MySQL safer by default (improved security and data safety), as well as offering more standards compliant behavior by default.
  • In addition, the test database and anonymous user accounts will no longer be created as part of a MySQL installation.

Runtime and General Improvements

  • table_open_cache_instances — This option is meant to reduce contention on the table cache on systems with many CPU hardware threads (vCPUs: a combination of sockets, cores, and threads). Now that even commodity desktop machines often have 16 hardware threads (2 cpu sockets, each cpu with 4 cores, and each core with 2 threads via SMT/Hyperthreading), we’ve set the default to 16 as this will offer improved performance on modern hardware.
  • sql_mode — The STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO, NO_ZERO_DATE, and NO_ZERO_IN_DATE sql modes are now enabled as a compiled in default. This has the effect of raising errors for data that would be truncated, is out of range, or otherwise invalid. This is a key part of our dual initiatives to 1. provide greater data validity and integrity guarantees by default 2. offer more SQL standard compliant behavior by default.
    (Note: prior to MySQL 5.7.8 all of these settings were merged into the single STRICT_TRANS_TABLES mode, but as a result of community feedback, they have been de-coupled again as in MySQL 5.6 and earlier releases.)
  • show_compatibility_56 — In 5.7 we’ve improved the behavior of SHOW commands, both in adding additional information but also in providing a clean delineation between the GLOBAL and SESSION contexts (which was the result of several oddities and bug reports). In order to better support backwards compatibility concerns when needed, we also introduced a new option called show_compatibility_56 which now defaults to OFF in 5.7.8. For compatibility with earlier releases, a user may wish to turn this setting to ON.
  • log_warnings — We changed the default value from 1 to 2, effectively increasing the verbosity of the error log. Please note that 5.7 also deprecates this configuration setting in favor of the newer log_error_verbosity option.

Optimizer Improvements

  • sql_mode — The ONLY_FULL_GROUP_BY behavior has been greatly improved, and it’s now enabled by default. You can read more about these changes in Guilhem’s excellent blog post.
  • eq_range_index_dive_limit — We changed the default from 10 to 200. This should offer better overall behavior for most cases. You can read more about this change in Jorgen’s excellent blog post.
  • We introduced 2 new optimizer switches called condition_fanout_filter and derived_merge, which are also now enabled by default. In most cases users will always want to leave these optimizations on as they will provide better performance. However, there are specific subqueries which will not support derived merge and these special cases have been documented under ‘sql changes’ in the 5.7 upgrade notes.

If you have any questions about these changes, or recommendations for others, please let us know in the comments here.

That’s it for now. As always, THANK YOU for using MySQL. And in this case, helping to make it even better!

MySQL Enterprise Audit : Parsing Audit Information From Log Files, Inserting Into MySQL Table

The MySQL Enterprise Audit plug-in is part of the MySQL Enterprise Edition (available through a paid license). Basically, Enterprise Audit tracks everything that is happening on your MySQL server, and can be used to protect/detect the misuse of information, and to meet popular compliance regulations including HIPAA, Sarbanes-Oxley, and the PCI Data Security Standard.

MySQL Enterprise Audit uses the open MySQL Audit API to enable standard, policy-based monitoring and logging of connection and query activity executed on specific MySQL servers. Designed to meet the Oracle audit specification, MySQL Enterprise Audit provides an out of box, easy to use auditing and compliance solution for applications that are governed by both internal and external regulatory guidelines.

When installed, the audit plugin enables MySQL Server to produce a log file containing an audit record of server activity. The log contents include when clients connect and disconnect, and what actions they perform while connected, such as which databases and tables they access.
(from https://dev.mysql.com/doc/refman/5.6/en/audit-log-plugin.html)

When you enable MySQL Enterprise Audit, log files are generated in your MySQL data directory. You can then use tools like MySQL Workbench (Enterprise Edition), mysqlauditgrep, or Oracle Audit Vault to import the log data, view the contents, perform searches, and generate reports.

I was talking with a client, and he wanted to know if the audit data could be stored in a table. Currently (as of MySQL 5.6.25), the audit information is stored as XML in the audit log files. So, I decided to write a quick Perl script that would parse the XML log files and insert the information into a MySQL database. You will need to set the size of your audit log files in your my.cnf or my.ini configuration file via the audit_log_rotate_on_size variable. You might need to adjust the size of your log files based upon database activity and how well the script parses the log files. If your log files are very large, the Perl script might have issues processing it, and you might want to decrease the size of your log files and run the script more frequently.


CAVEAT
Enterprise Audit does require a license from MySQL. If you are interested in an Enterprise subscription, contact me via the comment section below. If you are an Enterprise customer, you will need to configure Enterprise Audit first. See the Enterprise Audit online documentation page for more information, or contact MySQL Support.


For the data fields, I used the audit log file format information found at The Audit Log File page on MySQL.com.

My MySQL server doesn’t have a lot of activity, so I tried to configure the size of the data fields as best as possible to accommodate the possible size of the data in each field. There may be instances where you will have to increase the size of these fields or change their data types. The largest field is the SQL_TEXT field which will contain your SQL statements. Every table has a max row size of 65,535 bytes. So, the largest possible size of the SQL_TEXT field could be for this example is around 63,200 bytes (65,535 bytes minus the sum of the size of all of the other fields, and minus the 1-byte or 2-byte length prefix used for each varchar field). In this example, the SQL_TEXT field is set to 8,096 bytes, so you may need to increase or decrease this value.

I used varchar data types for each field, excluding the primary key field named ID. I did not spend a lot of time on the database schema, so you might want to modify it a little. I am sure that some of the fields are integers, but I did not have enough data in my log files to positively determine all of the possible values for each field. I did read the online manual, and it stated that CONNECTION_ID, SERVER_ID, STATUS, STATUS_CODE and VERSION were unsigned integers – but I left them as varchar.


NOTICE
This script requires the use of the new format for the audit log files, which is available in MySQL versions 5.6.20 or later.


I created a database along with two tables; one to store the log file information, and a history table to keep track of what files had already been parsed and inserted into MySQL, as well as the number of log file entries. The CREATE DATABASE and CREATE TABLE syntax is as follows:

The Perl script finds the non-active log files (which end in .xml – example: audit.log.14357895017796690.xml), parses the data, creates an SQL file with INSERT statements, imports the data via the mysql command-line program, and then moves the log file(s) and SQL file(s) to a directory. The history table records what files have been processed, so you don’t accidentally process the same file twice.

In the beginning of the Perl script, there are several values you need to replace to match your system. The values are under the section titled “values needed”. Here is the Perl script (named audit.pl):

It should not matter where you execute audit.pl, as long as you have correctly entered the required values in the script. You might get errors if you try to run this script on a log file that has not been rotated, which is the current log file in your MySQL data directory. The current log file is named audit.log.

The script will only work on files ending in .xml. For testing, I used four small (and identical) audit log files:

I have commented-out the print statements in the Perl script, but if you uncomment them, running the script gives you this output for each log file:

After running my test script, the following data is what is in the audit_history table:

And here is an example of one line from the audit_parsed table.

After parsing the log files, you can then write your own queries for searching through your audit data. You can even include this script in cron, so it runs and parses your files automatically. But as always, test this script and use it with caution before putting it in a production environment. You could also modify the Perl script to filter out values you did not want or need to store.

If you do use this script or if you have any suggestions or other questions, please leave a comment below.

Hopefully this has been interesting and serves as a quick example of what’s possible in processing the MySQL Enterprise Audit logs. That’s it for now, as always, THANK YOU for using MySQL!

 


Tony Darnell is a Principal Sales Consultant for MySQL, a division of Oracle, Inc. MySQL is the world’s most popular open-source database program. Tony may be reached at info [at] ScriptingMySQL.com and on LinkedIn.

Building a Better CREATE USER Command

Prior to MySQL 5.7, the CREATE USER command had a number of limitations:

  • No way to set both authentication plugin and password
  • No way to disable a user
  • No way to define user resource limitations
  • No way to set a non-default password expiration policy
  • No way to require SSL/x509

All of these things could be done through other means, but typically involved other statements, such as GRANT commands. Starting with MySQL 5.7.6, these can all be done through a new and improved CREATE USER syntax.

Continue reading Building a Better CREATE USER Command

Removal and Deprecation in MySQL 5.7

With the shipment of the first release candidate (RC) of MySQL 5.7, the next major version of the server is rapidly shaping up. Over the course of the nearly two and a half years that have passed since 5.6 went GA, we have put a lot of work into streamlining the server code in order to ease the burden of developing and maintaining such a large product and codebase.

An important aspect of this work is deprecation and removal. To explain the terms, deprecating a feature means that we signal to the outside world that “this feature is available now, but it will be removed in a future release, so please adjust your use case accordingly”. Removing a feature means just that – in one version the feature is available, but then it is gone, and if you try to use it, you will get an error message saying that the feature is unknown.

Generally we don’t remove features in existing GA releases, but we reserve the right to deprecate features that we will later remove in a development release (which is often the upcoming GA).

Earlier, the approach here was quite conservative: broadly speaking, the rule was to put in deprecation warnings in version N, and only remove in version N+2. However, in 5.7, we have seen a more proactive attitude towards both deprecation and removal, in that we in some cases have created deprecation warnings for features in 5.6 point releases and then removed the features in 5.7.

This blog post gives a summary of all the deprecations and removals that have taken place in MySQL 5.7. Most of these have already been covered in the various milestone blog posts that Geir Høydalsvik has published.

Features that have been removed

InnoDB monitoring features

The magic of the built-in InnoDB table names: innodb_monitor, innodb_lock_monitor, innodb_tablespace_monitor, innodb_table_monitor, and innodb_mem_validate have been removed. Historically, CREATE TABLE on one of these names (inside any database schema) has set flags inside InnoDB to enable additional debug output to the error log. DROP TABLE has cleared the flags and stopped the output. This mechanism was awkward, and it was difficult to maintain with current and future refactoring plans in InnoDB, so it was removed. Going forward,

  1. Instead of innodb_monitor, one can use SET GLOBAL innodb_status_output=ON|OFF
  2. Instead of innodb_lock_monitor, use
    • SET GLOBAL innodb_status_output=ON|OFF
    • SET GLOBAL innodb_status_output_locks=ON|OFF
  3. There is no direct replacement for the two tables innodb_tablespace_monitor and innodb_table_monitor, but information is available in INFORMATION_SCHEMA.SYS_TABLES and INFORMATION_SCHEMA.SYS_TABLESPACES. We also plan to add more information to INFORMATION_SCHEMA.FILES.
  4. innodb_mem_validate was completely removed, as the functionality was only present when the UNIV_MEM_DEBUG flag was set at compile time, something which is not even done for normal debug builds.

(Remove innodb_*monitor tables in 5.7, WL#7377, see also Morgan Tocker’s blog post)

Old Passwords

In MySQL 5.6 we deprecated passwords that used the older pre-4.1 password hashing format. Support for these passwords was removed in 5.7:

  • The server-side mysql_old_password authentication plugin is removed. Accounts that use this plugin are disabled at startup and the server writes an “unknown plugin” message to the error log.
  • The client-side mysql_old_password authentication plugin is removed from the C client library.
  • The --secure-auth option to the server and client programs is the default, but is now a no-op. It is deprecated and will be removed in a future MySQL release.
  • The --skip-secure-auth option to the server and client programs is no longer supported and using it produces an error.
  • The secure_auth system variable permits only a value of 1; a value of 0 is no longer permitted.
  • For the old_passwords system variable, a value of 1 (produce pre-4.1 hashes) is no longer permitted.
  • The OLD_PASSWORD() function is removed.

(Remove the deprecated old_password plugin, WL#8006, see also Morgan Tocker’s blog post)

SQL syntax

In 5.7 we have removed IGNORE for ALTER TABLE. We made this decision after Morgan’s blog post seeking community advice on this topic. We decided to remove IGNORE because it caused problems with foreign keys (rows removed in parent table), prohibits true online CREATE UNIQUE INDEX, and caused problems for replication. (Remove IGNORE for ALTER TABLE in 5.7, WL#7395)

The SQL modes, ERROR_FOR_DIVISION_BY_ZERO, NO_ZERO_DATE, NO_ZERO_IN_DATE SQL MODES are folded into STRICT MODE in 5.7. Unfortunately, as reported by Simon Mudd in bug#75439, this change breaks replication, and we are currently reconsidering this decision. (Remove ERROR_FOR_DIVISION_BY_ZERO, NO_ZERO_DATE, NO_ZERO_IN_DATE SQL MODES and make their functionality part of STRICT MODE in 5.7, WL#7467, see also Morgan Tocker’s blog post)

We removed the support for INSERT DELAYED in 5.7 and onwards. INSERT DELAYED has a lot of limitations and issues attached to it, and with InnoDB as default engine it becomes even less relevant. We decided to keep the INSERT/REPLACE DELAYED syntax, but convert it to INSERT/REPLACE with a warning before execution. (Remove INSERT DELAYED, WL#6073)

In 5.7, we removed all remnants of YEAR(2) related code. Issues with the YEAR(2) data type include ambiguity of displayed values, and possible loss of information when values are dumped and reloaded or converted to strings. Overall we removed the support for YEAR(2) in 5.6, but we kept some limited support for old YEAR(2) columns to allow for upgrade procedures to MySQL 5.6. Even this limited support is now gone in 5.7. (Remove limited YEAR(2) support, WL#6263)

Options and Variables

We have deprecated unique option prefixes in 5.5 and 5.6, and from 5.7 we only accept full option names. The old behavior has been found to be confusing and non-future proof. For example, the --compress option could be given to mysqldump as --compr, but not as --comp because the latter was ambiguous. However, adding extra options through plugins could cause prefixes in scripts to be unreliable even across server restarts. (Deprecate Unique Option Prefixes, WL#6978)

The timed_mutexes system variable was deprecated in 5.5/5.6 and removed in 5.7. This system variable had already been disconnected from any underlying InnoDB code, so it no longer served any purpose. (Deprecate and remove the timed_mutexes system variable, WL#7436)

The server options which disable InnoDB have been removed. The system tables are being moved from MyISAM to InnoDB, so in 5.7+ it will not be possible to run the MySQL server without InnoDB (which is the default storage engine since 5.5). (Deprecate skip-innodb in 5.6 and remove it in 5.7, WL#7976)

The variables innodb_use_sys_malloc and innodb_additional_mem_pool_size system variable were removed in 5.7 because it simplified InnoDB scalability efforts – the InnoDB home grown memory manager was not as good as jemalloc or tcmalloc, since the latter two are more scalable. (Remove innodb_use_sys_malloc & innodb_additional_mem_ pool_size system variable in 5.7, WL#7628)

The innodb_mirrored_log_groups system variable was deprecated in 5.6 and removed in 5.7. The allowed range of the parameter innodb_mirrored_log_groups was 1..10, but there has always been (at least since 3.23.53) a check within InnoDB startup that refuses any value except 1. This is not meaningful and thus we decided to remove it. (Remove innodb_mirrored_log_groups, WL#6808)

The innodb_file_io_threads system variable, which was deprecated in 5.5, was removed in 5.7 (Remove the innodb_file_io_threads variable in 5.7, WL#7149)

The storage_engine system variable was deprecated in 5.5 and finally removed in 5.7. You should instead use the default_storage_engine system variable. (Remove the storage_engine system variable in 5.7, WL#7148)

The --basedir and --datadir options to mysql_upgrade were no longer in use, and they have been removed (WL#7010).

Outdated Command Line Programs

This work was triggered by a blog post from my colleague Todd Farmer, with the title Spring cleaning useless clients, where he asked about the relevance of a number of clients that we shipped with the MySQL Server.

As a consequence, and after some more feedback from a blog post by Morgan Tocker, the following clients were deprecated in 5.6 and have been removed in 5.7: mysqlaccess (WL#7620), mysql_convert_table_format (WL#7034), mysql_fix_extensions (WL#7036), mysql_find_rows.sh (WL#7621), mysql_setpermission (WL#7033), msql2mysql (WL#7035), mysqlbug (WL#7689), mysql_zap and mysql_waitpid (WL#7826), mysqlhotcopy (WL#7854)

Various Removals

The old custom Windows symlink implementation has been removed in MySQL 5.7. Starting from Windows Vista/Server 2008, a native symlink mechanism is supported by Windows (see the mklink command). MySQL will now use the native Windows mechanism going forward. (Remove support for .sym files, WL#6561)

Features that have been deprecated (but not removed)

In 5.7, we have replaced the EOF packet with the new OK packet in the MySQL client-server protocol. The EOF and OK packets serve the same purpose — to mark the end of a query execution result. The protocol is kept backward compatible as clients and servers have one additional capability flag to denote their support for the new OK packet. When this capability flag is in place, any SQL statement which produces a result set will send an OK packet as a marker to indicate the end of data rather than an EOF packet. (Deprecate the EOF packet, WL#7766, see also Morgan Tocker’s blog post)

In 5.6 we deprecated SHOW PROFILE in favor of the Performance Schema instrumentation. In 5.7, we have completed this work by deprecating the INFORMATION_SCHEMA.PROFILING table. See also Bug#67525, reported by Peter Laursen. (Deprecate the INFORMATION_SCHEMA.PROFILING Table, WL#6802)

As part of the increased GIS effort, we’ve started cleaning up the name space for GIS functions. This is done by deprecating, removing, and adding function names (aliases) in order to make the naming scheme consistent and to bring MySQL in line with the OGC standard and other major DBMSs.

With the exception of the constructor functions that have the same names as the data types (Point, LineString, Polygon, MultiPoint, MultiLineString, MultiPolygon and GeometryCollection) and functions that do calculations on minimum bounding rectangles (MBRs), all spatial functions now have names prefixed by the standard “ST_” (spatio-temporal) convention, and function names without that prefix have been deprecated. This also means that it is a lot easier to take your queries from other SQL/MM compliant database systems and run them on your MySQL databases. (Consistent naming scheme for GIS functions, WL#8055, see also the blog post from Erik Frøseth, Spring cleaning in the GIS namespace)

The ENCRYPT(), DES_ENCRYPT() and DES_DECRYPT() functions based on the Data Encryption Standard (DES) have been deprecated in favor of Advanced Encryption Standard (AES) based functions, because AES provides much better security. (Deprecate the ENCRYPT, DES_ENCRYPT and DES_DECRYPT functions, WL#8126)

We now trigger a warning (ER_WARN_DEPRECATED_SYNTAX) upon the execution of the ENCODE() or DECODE() functions. (Deprecate ENCODE()/DECODE() in 5.7, WL#6984)

We have deprecated the SET PASSWORD syntax and the PASSWORD() function. The existing ALTER USER statement is modified to cover the deprecated functionality. The PASSWORD() function was originally introduced as a way to manually update the mysql.user table. This is generally a bad idea and we want to leave the task of managing user authentication properties exclusively to using the ALTER USER statement which automatically determines which authentication plugin should be used and then adjusts the password algorithm accordingly. (Deprecate PASSWORD() and extend ALTER USER syntax to manage authentication attributes, WL#6409)

We have deprecated the following 4 InnoDB parameters: innodb_large_prefix, innodb_file_format, innodb_file_format_check and innodb_file_format_max.

The parameter innodb_large_prefix was introduced in MySQL 5.5, so that users could avoid accidentally creating tables that would be incompatible with the InnoDB Plugin in MySQL 5.1. Now that MySQL 5.1 is not supported any more, it makes no sense to use any other value than innodb_large_prefix = ON.

The innodb_file_format family of parameters only made sense for the InnoDB Plugin in MySQL 5.1 when it was distributed separately from MySQL. Its purpose was to allow users to downgrade to the built-in InnoDB in MySQL 5.1, to avoid using incompatible format when testing the 5.1 plugin. Since MySQL 5.1, the InnoDB formats have changed, but the innodb_file_format has not been bumped. (InnoDB: Change defaults and deprecate settings in next 5.7 release, WL#7703)

In 5.7, the EXTENDED and PARTITIONS keyword of the EXPLAIN syntax have been deprecated. Currently, EXPLAIN has EXTENDED and PARTITIONS flags that show additional info compared to regular EXPLAIN output. However, EXPLAIN JSON already behaves like those flags and prints all available information. After deprecation regular EXPLAIN will behave exactly as if those flags are given – all additional columns and warnings are printed automatically. (Deprecate EXTENDED and PARTITIONS flags of EXPLAIN, WL#7027, see also Morgan Tocker’s blog post)

We have deprecated the collation_database and character_set_database system variables in 5.7. (Deprecate GLOBAL @@collation_database, @@character_set_database; assignment of SESSION counterparts, WL#3811, see also Bug#35357, Bug#27208, and Bug#27687 as well as Morgan Tocker’s blog post)

The conversion of pre MySQL 5.1 encoded database/schema names has been deprecated in 5.7. (Deprecate the conversion of pre MySQL 5.1 encoded database names, WL#8186).

The sync_frm system variable in 5.7 has been deprecated in 5.7 (Deprecate and remove the sync_frm sysvar, WL#8216)

The @@session.gtid_executed system variable has been deprecated in 5.7. This variable contains the set of GTIDs currently stored in the transaction cache. However, its value can be completely determined from the value of the system variable gtid_next, so it is of limited practical value. Also, it is not very well named, and it was decided to deprecate it. (Deprecate @@session.gtid_executed, WL#7518)

Getting Started With MySQL & JSON on Windows

MySQL is getting native support for JSON.  This blog post will show you how to quickly get the MySQL server with these new features running on your Windows rig and how to write a small C# program in Visual Studio 2015 that stores a JSON document using the new native JSON data type.

Schema or Schemaless

The upcoming 5.7 version of MySQL introduces a ton of new features, some of which I am quite excited about—in particular the native JSON support and Generated Columns. I think these can be very powerful for rapid development where the database is constantly changing as I add features and refactor existing ones. With the introduction of the new JSON data type I can combine schema and schemaless data.

Schema bound or schemaless with with ACID features, combined with transparent and easy data model changes? Yes, thank you, both please.

 

Installing Docker on Windows

The first step is to get the JSON labs release version of the MySQL server running on our computer. The easiest way is to have Docker do this for us. If you already have Docker installed (or Boot2Docker) then you can skip this step.

 

Installing the MySQL JSON Labs Release

Now that we have Docker installed, lets start it up. Simply double click the new desktop icon. This will then take us into a shell.

boot2docker_running

 

Now install the MySQL JSON Labs release using some Docker magic:

You can then use this command to verify that the server is up and running:

Which should read something like this:

 

Connecting to the Server Using the MySQL Client

Let’s then start a bash process inside the same container that the new MySQL server is running in. This will give us access to the mysql command-line client which we can then use to interact with the MySQL server.

At the Docker prompt enter docker exec -it ml bash, this give us a bash shell inside the container. From here enter mysql -p to connect to the MySQL server. The password is ‘my-secret-pw‘ or whatever you chose at install.

 

Trying out the New Native JSON Type

All righty then, finally we can play around with the new JSON type. It has a lot more to offer than simply storing JSON data, heck one could do that in a TEXT/BLOB or VARCHAR/CHAR type. The JSON data type enforces validation and fast look ups of sub-elements inside of the JSON document.

 

 

Exposing the Server Port to the OS

We now have a MySQL server running on a virtual Linux server inside VirtualBox. The problem is that this MySQL server is living inside a docker container completely cut off from any outside access, but we want to interact with it from the our Windows host. So, what we need to do is to tell Docker to expose the server port to VirtualBox, and then tell VirtualBox to expose that port to Windows.

We already did the first step by using the “-p 3306:3306” parameter when we installed the mysql/mysql-labs:5.7.7-json container.  Read more about docker run -p if you are interested in knowing all of the gory details.

The next step requires starting VirtualBox.  We have exposed the server port in Docker to VirtualBox, now we need to tell VirtualBox to pass that port along to Windows. You should have a start icon on the desktop after installing Boot2Docker.

Go to Settings -> Network -> Port forwarding and add the following rule:
blog-mysql-vbox-port-forward

This is a good time to open a command shell and check if you see port 3306 being listened on.

 

Create a Project in Visual Studio 2015

So in case you haven’t been paying attention—Microsoft has released a free and fully featured version of their IDE which I recommend you get if you’re even remotely interested in developing on the Windows platform (or even for mobile and Web development).

Head over to www.visualstudio.com and grab it.  I use the community edition.

Once you have it installed, then create a new project called JsonTest (for example), and to keep it simple choose a project of type ‘Console Application’.

 

Add MySQL Connector/Net to the Project

Now we want to communicate with the MySQL server that is running in our Docker container from our C# program, so what we need is MySQL Connector/Net, this is a pure C# library for exactly this purpose.

  1. Connector/Net ReferenceIn Solution Explorer, right click on ‘References’ to bring up the menu
  2. Choose “Manage nuget packages” from the menu.
  3. Type in ‘mysql’ in the search bar
  4. Choose the ‘MySql.Data ADO.Net driver for MySQL’.
  5. Press the install button

 

C# Code for Using the New JSON Data Type

This is a simple example program of how to create a table with a JSON type column, INSERT a couple of JSON Documents, and then finally to read them back from the database.

Notice line 36, here I do a small trick to avoid getting MySqlException ‘Unknown data type’, because this is a Labs release that contains functionality which is slightly ahead of the current generation of connectors. So to work around the fact that the connector is clueless about the new JSON type, I CAST the type to a CHAR(40) column in the SELECT statement.  

Copy & paste the program below into your own project and hit CTRL-F5 to run it:

You should then get the following result:

As you can see, we’ve successfully created a table in MySQL 5.7 which has a JSON column, we’ve then INSERTED two JSON Documents into that table, and finally we’ve queried the table to get JSON Documents back.

That’s it for now. I hope that this brief introduction was helpful! Please stay tuned as we add additional helpful information regarding working with JSON in MySQL 5.7. As always, THANK YOU for using MySQL!

Improving the Performance of MySQL on Windows

In this blog entry I’d like to describe how you might be able to improve how MySQL performs on Windows by ensuring that you take advantage of a Windows specific configuration setting.

On Unix systems, MySQL programs treat the localhost host name specially. For connections to localhost, MySQL programs attempt to connect to the local server by using a Unix socket file, which has some performance advantages over a TCP/IP connection. Windows does not support Unix sockets, however, and hence does not benefit from this optimisation.

However, the use of shared memory connections on Windows can offer significant performance improvements over the use of TCP/IP connections. Shared memory connections are obviously only useful when both the MySQL client and server processes are executing on the same machine, but when they are the performance benefits of using shared memory connections can be helpful. To enable shared memory connections, you would use the shared_memory system variable.

The following screenshot compares the sysbench OLTP read write test performance when using shared memory connections versus when using TCP connections with MySQL 5.6.24 on Windows Server 2012 R2. The graph shows the average number of transactions per second (TPS) measured by sysbench at a variety of thread counts. The TPS value averaged over five runs is plotted at each thread count, with each run taking 300 seconds.

Graph comparing Shared Memory and TCP connection performance using MySQL 5.6.24
Comparing shared memory and TCP connection performance using MySQL 5.6.24

The uppermost red plot in the graph above shows the results obtained when using shared memory connections between sysbench and MySQL. The lower black plot shows the results obtained when using TCP connections.

Note that as well as giving improved performance when using shared memory connections, the shared memory connections results show reduced variability (apart from the results at 512 threads).

Obviously, changing to a more efficient communication path between the MySQL server and client will only make a dramatic difference like that shown above when the MySQL server isn’t spending a lot of time processing queries and doing file I/O. So if you use MySQL on Windows in a standalone configuration (with the application clients and the MySQL server on the same machine) and switch from using TCP connections to shared memory connections, then the performance improvement that you experience may not be as great.

The test machine used to produce the results shown above is a Sun Fire X4170 M2 Server with 24 logical CPUs running at 2930MHz.

The following graph shows the results of running the same TCP vs shared memory connection tests using the MySQL 5.7.7 release candidate:

Graph showing shared Memory vs TCP connection performance using MySQL 5.7.7rc
Comparing shared memory and TCP connection performance using MySQL 5.7.7rc

Note that the MySQL 5.7.7 release candidate shows better performance at high thread counts than MySQL 5.6.24 in the graphs above. Profiling the shared memory test runs at 1024 threads for the different versions of MySQL reveals that the second “hottest” function in MySQL 5.6.24 at this thread count is RtlpEnterCriticalSectionContended, whereas this function has dropped to fourth place in the MySQL 5.7.7 profile. A call to RtlpEnterCriticalSectionContended on Windows indicates contention for a critical section (a multithreading lock).

The reduction in lock contention with MySQL 5.7.7 fits nicely with the performance and scalability improvements in MySQL 5.7 mentioned in Geir’s blog entry: What’s new in MySQL 5.7 (First Release Candidate).

The tests used to produce the results shown above are part of the regular performance tests that run daily in the Oracle test labs against the latest builds of MySQL on various platforms. The results of these daily performance tests are automatically checked for performance regressions and failures and engineers are alerted if any are found.

One of the characteristics of a good performance test is repeatability. In order to reduce the variability of the TPS results reported by sysbench on the machine used for the test results, the sysbench process was affinitized to use CPUs 0,1,2,3,12,13,14,15 and the MySQLD process was affinitized to use CPUs 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23. These CPU affinities were arrived at by experimenting to find the affinity settings that provided the highest and most stable TPS results.

Microsoft has also introduced an enhancement to the “loopback” TCP/IP performance on Windows 8 and Windows 2012 via the “TCP Loopback Fast Path” (see http://blogs.technet.com/b/wincat/archive/2012/12/05/fast-tcp-loopback-performance-and-low-latency-with-windows-server-2012-tcp-loopback-fast-path.aspx ). The graph below shows the results I obtained when I experimented with a patch on the communications library used by MySQL 5.7.6 m16 and sysbench that set the SIO_LOOPBACK_FAST_PATH option to 1.

Graph showing Fast Loopback TCP vs standard TCP connection performance using MySQL 5.7.6-m16
Comparing Fast Loopback TCP and standard TCP connection performance using MySQL 5.7.6-m16

The lower black plot shows the results from the “normal” unpatched TCP connections. The upper red plot shows the results when using the communications library patched with the “Loopback Fast Path” setting.

Note that the performance gain from using the “TCP Loopback Fast Path” patch over normal TCP connections is not as significant as that available from switching to shared memory connections. The data in the graph above is also not directly comparable with the previous graphs showing the shared memory connection performance as the CPU affinity settings differ.

In my next post, I hope to show how some previously undocumented settings can further improve MySQL’s performance on Windows. So please stay tuned!

As always, thank you for using MySQL!

The InnoDB Change Buffer

One of the challenges in storage engine design is random I/O during a write operation. In InnoDB, a table will have one clustered index and zero or more secondary indexes.  Each of these indexes is a B-tree.  When a record is inserted into a table, the record is first inserted into clustered index and then into each of the secondary indexes.  So, the resulting I/O operation will be randomly distributed across the disk.  The I/O pattern is similarly random for update and delete operations. To mitigate this problem, the InnoDB storage engine uses a special data structure called the change buffer (previously known as the insert buffer, which is while you will see ibuf and IBUF used in various internal names).

The change buffer is another B-tree, with the ability to hold the record of any secondary index.  It is also referred to as a universal tree in the source code.  There is only one change buffer within InnoDB and it is persisted in the system tablespace.  The root page of this change buffer tree is fixed at FSP_IBUF_TREE_ROOT_PAGE_NO (which is equal to 4) in the system tablespace (which has space id of 0). When the server is started, the change buffer tree is loaded by making use of this fixed page number.   You can refer to the ibuf_init_at_db_start() function for further details.

The total size of the change buffer is configurable and is designed to ensure that the complete change buffer tree can reside in main memory.   The size of the change buffer is configured using the innodb_change_buffer_max_size system variable.

Overview of Change Buffering

Change buffering is applicable only to non-unique secondary indexes (NUSI).  InnoDB buffers 3 types of operations on NUSI: insert, delete marking, and delete.  These operations are enumerated by ibuf_op_t within InnoDB:

One important point to remember is that the change buffering is leaf page oriented. A particular operation to NUSI is buffered in the change buffer only if the relevant non-root leaf page of the NUSI is not already available in the buffer pool.  This means that the buffered change is predefined to happen in a particular leaf page of a NUSI within the InnoDB system.  This makes it necessary to track the free space available in the NUSI leaf pages.  This tracking is necessary because merging these buffered operations to the NUSI leaf page must not result in a B-tree page split or B-tree page merge.

Special Change Buffer Fields

When a NUSI record is buffered in the change buffer, 4 special change buffer fields are added to the beginnning of the NUSI record.  Each of these 4 fields and their contents are explained below.  The primary key of the change buffer tree is then {space_id, page_no, count}, where the count helps to maintain the order in which the change is buffered for that particular page.  The change buffer row format has evolved over a period of time and the following table provides information for MySQL 5.5+:

Field NumberMacro NameField LengthField Description
1IBUF_REC_FIELD_SPACE4 bytesThe identifier of the space in which NUSI exists.
2IBUF_REC_FIELD_MARKER1 bytesIndicates whether the change buffer row format is old or new. If present (and zero), row format is newer (MySQL 4.1+)
3IBUF_REC_FIELD_PAGE4 bytesThe leaf page number of the NUSI to which the buffered row belongs.
4IBUF_REC_FIELD_METADATA2 bytesCounter field, used to sort records within a (space_id, page_no) in the order they were added.
1 byteOperation type (ibuf_op_t).
1 byteRow format flag. If 0, the user index record is in REDUNDANT row format. If IBUF_REC_COMPACT, then user index record is in COMPACT row format.
Variable length. DATA_NEW_ORDER_NULL_TYPE_BUF_SIZE (which is 6) bytes per field.Type information affecting the alphabetical ordering of the fields and the storage size of an SQL NULL value.
5IBUF_REC_FIELD_USERthe first user field.

The row format of the change buffer records themselves are always REDUNDANT.

Change Buffer Bitmap Page

The free space information for each page is tracked in predefined pages called the change buffer bitmap page, which is also known as the ibuf bitmap page.  These pages always follow the extent descriptor pages.  The following table gives the predefined page numbers of the change buffer bitmap pages:

Page Size in Kilobytes (KB)Extent Size in PagesExtent Descriptor Page NumbersChange Buffer Bitmap Pages (ibuf bitmap pages)Number of pages described by one ibuf bitmap page
42560, 4096, 8192, 12288, ...1, 4097, 8193, 12289, ...4096
81280, 8192,16384, 24576, ...1, 8193, 16385, 24577, ...8192
16640, 16384, 32768, 49152, ...1, 16385, 32769, 49153, ...16384
32640, 32768, 65536, ...1, 32769, 65537, ...32768
64640, 65536, 131072, ...1, 65537, 131073, ...65536

The page number 1 is also referred to as the FSP_IBUF_BITMAP_OFFSET. These change buffer bitmap pages help to answer the following questions quickly:

  • Does the given page have any buffered changes in the ibuf (insert/change buffer) tree?  This question will be asked when a page is read into the buffer pool.  The buffered changes will be merged to the actual page before putting it into the buffer pool.
  • Does the given page have enough free space so that a change can be buffered? This question will be asked when we want to modify a leaf page of NUSI and it is not already available in the buffer pool.

In the next section we will see at the information stored in the ibuf bitmap page which helps to answer above questions.

Information Stored in Change Buffer Bitmap Page

The change buffer bitmap page uses 4 bits (IBUF_BITS_PER_PAGE) to describe each page. It contains an array of such 4 bits describing each of the pages.  This whole array is called the “ibuf bitmap” (insert/change buffer bitmap).  This array begins after the page header at an offset equal to IBUF_BITMAP (which is equal to 94). Given a page number, the ibuf bitmap page that contains the 4 bit information on the given page can be calculated as: ulint bitmap_page_no = FSP_IBUF_BITMAP_OFFSET + ((page_no / page_size) * page_size); .

You can refer to the ibuf_bitmap_page_no_calc() function for more details on the complete calculation.  Likewise, given a page number, the offset within the change buffer bitmap page contains the 4 bits that can be used easily for the calculations.  I leave this as an exercise to the reader (refer to function ibuf_bitmap_page_get_bits_low() for further info). The following table provides details about these 4 bits:

BitValueDescription
IBUF_BITMAP_FREE0The first two bits are used to represent the free space available in the leaf page of the NUSI.
IBUF_BITMAP_BUFFERED2The third bit if set means that the leaf page has buffered entries in the change buffer.
IBUF_BITMAP_IBUF3The fourth bit if set means that this page is part of the change buffer.

This means that only 2 bits are available to store the free space information of the page.  There are only 4 possible values: 0, 1, 2, 3.  Using these 2 bits, we try to encode the free space information for a page.  The rule is as follows — there must be at least UNIV_PAGE_SIZE / IBUF_PAGE_SIZE_PER_FREE_SPACE bytes of free space for the change buffer to be used:

Tracking Free Space of Pages

Before an insert operation (IBUF_OP_INSERT) is buffered, the free space available in the target NUSI leaf page is approximately calculated using the information available in the change buffer bitmap page. This conversion is done in the ibuf_index_page_calc_free_from_bits() function and the formula used is:

The following table provides the conversions done from the encoded value found within the change buffer bitmap page to a meaningful value in bytes:

Page Size in Kilo Bytes (KB)Free Space Information from IBUF bitmap page
(ibuf_code)
The free space available in NUSI leaf page (approximately assumed)
400 bytes
1128 bytes
2256 bytes
3512 bytes
1600 bytes
1512 bytes
21024 bytes
32048 bytes

Using this information, we can determine if the record to be buffered will fit into the page or not.  If there is enough space then the insert will be buffered.  Using this approach, we ensure that merging these records to the target NUSI will not result in a page split.

Updating Free Space Information

After buffering an insert or delete operation, the free space information in the change buffer bitmap page must be updated accordingly (a delete mark operation will not change the free space information).  To update the free space information we need to convert the free space in bytes back to the IBUF encoded value.  This is done in the ibuf_index_page_calc_free_bits() function using the following formula:

In the above formula, max_ins_size is the maximum insert size (maximum free space) available in the page after page re-organization.

Record Count in NUSI Leaf Pages

In the case of a purge operation  (IBUF_OP_DELETE),  we need to ensure that the number of records in the target NUSI leaf page doesn’t go to zero because in InnoDB, the leaf pages of B-tree are not allowed to become empty.  They must have at least 1 record. Since the number of records in the target NUSI page is unknown (because it is not loaded into the buffer pool yet), the buffered insert operations are taken into account.  If 1 insert operation is buffered then it is assumed that the target NUSI page has 1 record, and if 2 insert operations are buffered then it is assumed that the target NUSI page has 2 records and so on.  In this way the number of records in the target NUSI page is calculated.  Based on this calculated record count, a purge operation is either buffered or not.  This means that if there are no buffered insert or delete-mark operations, then no purge operations can be buffered.

Merging the Buffered Changes to Target NUSI Page

The changes to NUSI leaf pages are buffered in the change buffer if the NUSI leaf page is not already in the buffer pool. These buffered operations are merged back into the actual NUSI leaf page under various circumstances:

  1. When these NUSI leaf pages are subsequently read into the buffer pool, the buffered operations will be merged.  A NUSI leaf page could be read into the buffer pool during an index lookup, an index scan or because of read ahead.
  2. When the master thread of InnoDB periodically does change buffer merges by calling ibuf_merge_in_background().
  3. When there are too many operations buffered for a particular NUSI leaf page.
  4. When the change buffer tree reaches its maximum allowed size.

The change buffer merge operation is initiated by calling the ibuf_merge_in_background() or ibuf_contract() functions.   The change buffer merges are done either in the foreground or in the background. The foreground change buffer merges are done as part of a DML operation and hence will affect the performance experienced by the end user.  The background change buffer merges are instead done periodically when there is less activity in the server.

We ensure that the change buffer merge does not result in a B-tree page split or page merge operation. It also shouldn’t result in an empty leaf page.  Before the target NUSI leaf page are placed into the buffer pool, the buffered changes are applied to them. Once the buffered changes are merged for a page, its associated 4 bits of information in the change buffer bitmap page are also updated.

Conclusion

This article provided an overview of the change buffer subsystem of InnoDB.  It explained the additional fields that are added to a secondary index record before storing it within the change buffer.  It provided information about how the change buffer keeps track of free space information of the NUSI leaf pages by making use of the change buffer bitmap page.  It also explained the need to calculate the number of records in the NUSI leaf pages so that it doesn’t become empty.

Thanks to Marko Makela for reviewing this article and helping to make it more accurate. If you have any questions, please feel free to post a comment below.

That’s all for now. THANK YOU for using MySQL!

 

Using Perl and MySQL to Automatically Respond to Retweets on Twitter

In my previous post, I showed you a way to store tweets in MySQL, and then use Perl to automatically publish them on Twitter.

In this post, we will look at automatically sending a “thank you” to people who retweet your tweets — and we will be using Perl and MySQL again.

Just like in the first post, you will need to register your application with Twitter via apps.twitter.com, and obtain the following:

One caveat: Twitter has a rate limit on how often you may connect with your application — depending upon what you are trying to do. See the Rate Limiting and Rate Limits API docs for more information. What this means is that if you are going to put this into a cron job (or use some other automated scheduler), I wouldn’t run it more than once every 15 minutes or so.

We will also be using the same tables we created in the first posttweets and history — as well as a new table named retweets. The retweets table will contain all of the user names and tweet ID’s for those retweets we have discovered and for which we’ve already sent a “thank you” tweet response.

The Perl script will connect to your tweet history table and retrieve a set of your tweet ID’s, with the most recent tweet first. The script will then connect to Twitter and check to see if there are any retweets for each given ID. If an existing retweet is found, then the script will check your retweets table to see if you have already thanked the user for their retweet. If this is a new retweet, then the script will connect to Twitter, send the “thank you” message to that user, and finally insert the user name and tweet ID combination into the retweets table. This will ensure that you do not send repeat “thank you” responses.

Here is a flow chart to help explain what the script does:

We will be using the retweets(id) API call to see if a tweet ID was retweeted, and then we will send the “thank you” tweet via the update API call. You can find more information about the Perl Twitter module at Net::Twitter::Lite::WithAPIv1_1.

First we will need to create the retweets table, where we will store the information about our tweets that were retweeted. Here is the CREATE TABLE statement for the retweets table:

Then you will need to edit the below script and insert the consumer_key, consumer_secret, access_token, and access_token_secret values for your application (which you get when registering your app with Twitter), and edit the accessTweets file (more on that shortly) used by the ConnectToMySql subroutine. (You may also want to comment-out the debug style “print” calls).

In the ConnectToMySql subroutine, I store the MySQL login credentials in a text file one directory below where my Perl script is located. This accessTweets file should contain the following information:

I tested this on two Twitter accounts and everything worked well for me, but please let me know if you have any problems. Please keep in mind, however, that I am not the best Perl programmer, nor am I an expert on the Twitter API, so there are likely better/easier way to do this.

That’s it for now. I hope that this was interesting and may prove helpful. THANK YOU for using MySQL!

 


Tony Darnell is a Principal Sales Consultant for MySQL, a division of Oracle, Inc. MySQL is the world’s most popular open-source database program. Tony may be reached at info [at] ScriptingMySQL.com and on LinkedIn.