Identifying Insecure Connections

A key theme of the MySQL Server 5.7 release is much improved security. Earlier releases of MySQL 5.7 have introduced features supporting this initiative including automatic generation and detection of TLS key material and client-side preference for TLS connections. The recent MySQL 5.7.8 release builds upon this and provides additional monitoring and audit capabilities that make it easy to answer the question: “How secure are my client connections?”.

Continue reading Identifying Insecure Connections

InnoDB Transparent Page Compression

Astute readers will note that InnoDB already had compression since the MySQL 5.1 plugin. We are using the terminology of ‘Page Compression’ to describe the new offering that will ship with MySQL 5.7, and ‘InnoDB Compression’ for the earlier offering.

First a brief history and explanation of the current motivation.

InnoDB Compression

Compressed tables were introduced in 2008 as part of the InnoDB plugin for MySQL 5.1. By adding compression, InnoDB is able to more efficiently make use of modern hardware such as fast SSD devices. Facebook was an early adopter of this technology. They did extensive analysis and made lots of improvements, most of which were later pushed to the upstream MySQL code as well.

The code changes required to get the old InnoDB compression to work properly were extensive and complex. Its tentacles are everywhere—I think that just about every module inside InnoDB has had modifications done to it in order to make it work properly when compression is in use. This complexity has its challenges, both in terms of maintainability and when it comes to improving the feature. We have been debating internally about what we should do about this over the long run. As much as we would like to redesign and rewrite the entire old compression code, it is impractical. Therefore we are only fixing issues in the old compression code reported by customers. We think there are better ways to solve the underlying performance and compression problems around B-Trees. For example by adding support for other types of indexes e.g. LSM tree and/or BW-Tree or some variation of the two. We also want to change our page and row formats so that we have the flexibility to add new indexing algorithms as required by the changing landscape and at the same time maintaining full backward compatibility. As we continue to develop and improve InnoDB, we also think that the write amplification and compression efficacy problems need to be fixed more substantially rather than tinkering around the edges of our old compression codebase.

Introducing InnoDB Page Compression

When FusionIO (now part of SanDisk) floated the idea around transparent page compression, the appeal was in its simplicity—the changes to the code base were very localised and more importantly it complements the existing InnoDB compression as both transparent page compression and the old compression can coexist in the same server instance. Users can then choose the compression scheme that makes more sense for their use case, even on a table-by-table basis.

From a high level, transparent page compression is a simple page transformation:

Write : Page -> Transform -> Write transformed page to disk -> Punch hole

Read  : Page from disk -> Transform -> Original Page

When we put these abstractions in place, it was immediately obvious that we could apply any type of transformation to the page including encryption/decryption and other things as we move forward. It is then trivial to add support for a new compression algorithm, among other things. Also, MySQL 5.7 already had the multiple dedicated dirty page flushing threads feature. This existing 5.7 feature was a natural fit for offloading the “transformation” to a dedicated background thread before writing the page to disk thus parallelizing the compression and  the “hole punch” after the write to disk. By contrast, with the old InnoDB compression the compress/decompress/recompress operations are done in the query threads (mostly), and it would take a mini-series of blog posts to explain how it works, even when assuming that you’re already very familiar with the InnoDB internals.

To use the new Transparent Page Compression feature the operating system and file system must support sparse files and hole punching.

Linux

Several popular Linux file systems already support the hole punching feature. For example: XFS since Linux 2.6.38, ext4 since Linux3.0, tmpfs (/dev/shm) since Linux 3.5, and Btrfs since Linux 3.7.

Windows

While this feature is supported on Windows, it may not provide much practical value “out of the box”. The issue is in the way NTFS clustering is designed.

On Windows the block size is not used as the allocation unit for sparse files. The underlying infrastructure for sparse files on NTFS is based on NTFS compression. The hole punch is done on a “compression unit” and this compression unit is derived from the cluster size (see the table below). This means that by default you cannot punch a hole if the cluster size >= 8K. Here’s a breakdown for smaller cluster sizes:

Cluster SizeCompression Unit
512 Bytes8K Bytes
1K Bytes16K Bytes
2K Bytes32K Bytes
4K Bytes64K Bytes

The default NTFS cluster size is 4K bytes, which means that the compression unit size is 64K bytes. Therefore, unless the user has created the file system with a smaller cluster size and used larger InnoDB page sizes, there is little practical benefit from transparent compression “out of the box”.

Using the Feature

In the current release we support two compression algorithms, ZLib and LZ4. We introduced a new table level attribute that is stored in the .frm file. The new attribute is:
COMPRESSION := ( "zlib" | "lz4" | "none")

If you create a new table and you want to enable transparent page compression on it you can use the following syntax:
CREATE TABLE T (C INT) COMPRESSION="lz4";

If later you want to disable the compression you can do that with:
ALTER TABLE T COMPRESSION="none";

Note that the above ALTER TABLE will only result in a meta-data change. This is because inside InnoDB COMPRESSION is a page level attribute. The Implication is that a tablespace can contain pages that could be a mix of any supported compression algorithm. If you want to force the conversion of every page then you need to force it by invoking:
OPTIMIZE TABLE T;

 

Tuning

Remember to set the  innodb-page-cleaners to a suitable value, in my tests I’ve used a value of 32. Configuring for reads is not that straight forward. InnoDB can do both sync and  async reads. Sync reads are done by the query threads. Async reads are done during read ahead, one downside with read ahead is that  even if the page is not used it will be decompressed. Since the async read and decompress is done by the background IO read threads, it should not be too much of an issue in practice. Sync read threads will use the page otherwise they wouldn’t be doing a sync read in the first place. One current problem with converting a sync read to an async read is the InnoDB IO completion logic. If If there are too many async read requests, it can cause a lot of contention on the buffer pool and page latches.

Monitoring

With hole punching the file size shown by ls -l displays the logical file size and not the actual allocated size on the block device. This is a generic issue with sparse files. Users can now query the INNODB_SYS_TABLESPACES information schema table in order to get both the logical and the actual allocated size.

The following additional columns have been added to that Information Schema view: FS_BLOCK_SIZE, FILE_SIZE, ALLOCATED_SIZE, and COMPRESSION.

  • FS_BLOCK_SIZE is the file system block size
  • FILE_SIZE is the file logical size, the one that you see with ls -l
  • ALLOCATED_SIZE is the actual allocated size on on the block device where the filesystem resides
  • COMPRESSION is the current compression algorithm setting, if any

Note: As mentioned earlier, the COMPRESSION value is the current tablespace setting and it doesn’t guarantee that all pages currently in the tablespace have that format.

Here are some examples:

 

Limitations

Nothing is perfect. There are always potential issues and it is best to be aware of them up front.

Usage

Currently you cannot compress shared system tablespaces (for example the system tablepsace and the UNDO tablespaces) nor general tablespaces, and it will silently ignore pages that belong to spatial (R-Tree) indexes. The first two are more like artificial limitations from an InnoDB perspective. Inside InnoDB it doesn’t make any difference whether it is a shared tablespace or an UNDO log. It is a page level attribute. The problem is because we store the compression table attributes in a .frm file and the .frm file infrastructure doesn’t know about InnoDB tablespaces. The spatial index (R-Tree) limitation is because both features use the same 8 bytes within the header file. The first is a limitation due to MySQL legacy issues and difficult to fix. The latter is probably fixable by changing the format of the compressed page for R-Tree indexes, but we decided not to introduce two formats for the the same feature. Many of these limitations will go away with the new Data Dictionary and related work.

Space savings

The granularity of the block size on many file systems by default is 4K. This means that even if the data in the InnoDB data block compresses down from 16K to say 1K we cannot punch a hole and release the 15K of empty space on the block, but rather only 12K. By contract, for file systems that use a much smaller size of 512 bytes by default, this works extremely well (e.g. DirectFS/NVMFS). There is a trend on the hardware side to move from 512 byte sectors to larger sectors e.g. 4K, as larger sector sizes usually result in faster writes provided the writes are the same size as the sector size. FusionIO devices provide the ability to set this size during the formatting phase. Perhaps other vendors will follow and allow this flexibility too. Then one can decide on the size vs speed trade off during capacity planning.

Copying files from one system to another

If you copy files on a host that has hole punching support and the files are compressed using the transparent page compression feature, then on the destination host the files could “expand” and fill the holes. Special care must be taken when copying such files. (rsync generally has good support for copying sparse files, while scp does not.)

ALTER TABLE/OPTIMIZE TABLE

If you use the ALTER TABLE syntax to change the compression algorithm of a tablespace or you disable transparent page compression, this will only result in a meta-data change. The actual pages will be modified when they are next written. To force a change to all of the pages, you can use OPTIMIZE TABLE.

File system fragmentation

The file system can get fragmented due to hole punching releasing blocks back to the file system free list. This has two ramifications:

  1. Sequential scans can actually end up as random IO. This is particularly problematic with HDDs.
  2. It may increase the FS free list management overhead. In our (shorter) tests using XFS I haven’t seen any major problem, but one should do an IO intensive test over the course of several months to be sure.

Implementation Details

We repurposed 8 bytes in the InnoDB page header, which exists on every page in every tablespace file. The repurposed field is the “FLUSH_LSN” (see definition of the page header as a C struct below). This field, prior to the MySQL 5.7 R-Tree implementation being added, was only valid for the system tablespace’s (ID: 0) first page (ID: 0).  For all other pages it was unused.

flush_lsn is interpreted as meta_t below. Before writing the page to disk we compress the page and set the various fields in the meta_t structure for later deserialization when we read the page from disk.

Readers will notice that the size type is a uint16_t. This limits the page size to 64K bytes. This is a generic limitation in the current InnoDB page layout, not a limitation introduced  by this new feature. Issues like this are the reason why we want to rethink our long term objectives and fix them by doing more structural changes.

Performance

The first test uses FusionIO hardware along with their NVMFS file system. When I ran my first set of tests using the new compression last week, it was a little underwhelming to say the least. I hadn’t run any performance tests for over a year and a half, and a lot has changed since then. The numbers were not very good, the compression was roughly the same as the older compression and the Linkbench requests per second were quite similar to the old compression, just a little bit better, nothing compelling. I think it could be due to NUMA issues in the NVMFS driver as the specific hardware that I tested on is one of our newer machines with 4 sockets.

I did some profiling using Linux Perf but nothing stood out, the biggest CPU hog was Linkbench. I played around with the Java garbage collector settings, but it made no difference. Then I started looking for any latching overheads that may have been introduced in the final version. I noticed that the hole punching call was invoked under the protection of the AIO mutex. After moving the code out of that mutex, there was only a very small improvement. After a bit of head scratching, I tried to decompose the problem into smaller pieces. We don’t really do much in this feature, we compress the page, write the page to disk and then punch a hole to release the unused disk blocks. Next I disabled the hole punching stage and the requests per second went up 500%, this was encouraging.  Were we making too many hole punching calls? It turns out we were. We only need to punch a hole if the compressed length is less than the previous length on disk. With this fix (which is not in the current 5.7.8 RC) the numbers were again looking much better.

One observation that I would like to make is that the compression/decompression doesn’t seem to be that expensive, about 15% overhead. The hole punching in NVMFS seems to have a bigger impact on the numbers, approximately 25% on NVMFS.

The host specs are:

Below are the important my.cnf settings from my tests. I won’t claim that this is a highly tuned configuration, it’s just what I used. As long as it is the same for all three tests, I don’t think it should matter much:

I then ran three tests:

  1. No compression with the default page size of 16K.
  2. Old compression with an 8K page size.
  3. New compression with default page size of 16K and COMPRESSION="lz4".

The Linkbench commands used to load and test were:


LinkBench Load Times

The loading times when using the new compression were quite similar to the loading times without any compression, while the old compression quite slow by comparison.

The first test was on NVMFS:
 

LikBench Load Time in Seconds

The next test was on a slower Intel SSD using EXT4:
EXTLoad

LinkBench Request Times

The old compression gave the worst result again. It is 3x times slower than the new compression. I also think that since we haven’t even really begun to optimise the new compression code yet, we can squeeze some more juice out of it.

This is on NVMFS:

LinkBench Requests Per Second

This is on a slower Intel SSD using EXT4:

EXT4RequestsI was quite surprised with this result and it needs more analysis. The new transparent compression actually performed better than with no compression. I was not expecting this :-). Perhaps the new compression gives better numbers  because it writes less data to the disk compared to the uncompressed table. I don’t know for sure yet.

Space Savings

So it performs better, but does it save on disk space? The old compression performs a little better here, but only by about 1.5%.

The is using NVMFS:LinkBench Data Files On Disk Size

This is with the Intel SSD using EXT4:SpaceUsedEXT4

The old compression saves more space in our second test. This is likely because the block size on the EXT4 file system was set at the default of 4K. Next I want to check if using a smaller block size helps us and provides the better requests/per second numbers.

Conclusion

This feature adds more choice for our users. On the right hardware and file system combination you can get up to a 300% performance boost and get the same space savings you get with the older compression.

Please let us know what you think of this new feature. We’d love to hear your feedback! If you encounter any problems with this new feature, 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!.

 

Virtual Columns and Effective Functional Indexes in InnoDB

In April, I wrote a blog post introducing the new InnoDB virtual column and effective functional index work for the JSON lab release. Now the feature is officially in MySQL 5.7.8. It’s worth revisiting the topic again in order to write about what is in the 5.7 Release Candidate, along with providing some additional examples. I also ran some quick performance tests comparing some simple operations on a table with and without virtual columns and indexes, comparing them with those using materialized generated columns.

Key Features

The earlier article already described the design in detail. If you have not already read it, I would recommend that you check it out first. I won’t go into all of the design detail again here, but I will restate some key points:

  1. The virtual columns are no longer materialized in the table. They are truly “virtual”, which means they are NOT stored in InnoDB rows (InnoDB stores the table data in its primary key records—a clustered index—so it means that virtual columns will not be present in InnoDB primary key records), thus decreasing the overall size of the table. This then allows for faster table scans and other large operations.
  2. Since they are truly “virtual”, adding and dropping virtual columns does not require a table rebuild. Rather, it only requires a quick system table update that registers the new metadata. This makes the schema changes simple and fast.
  3. Creating an index on a virtual column (only secondary indexes are allowed) will essentially “materialize” the virtual column in the index records as the computed values are then stored in the secondary index, but the values are not stored in the primary key (clustered index). So the table itself is still small, and you can quickly look up the computed (and stored) virtual column values in the secondary index.
  4. Once an index is created on a virtual column, the value for such a virtual column is MVCC logged so as to avoid unnecessary re-computation of the generated column value later when we have to perform a rollback or purge operation. However, since its purpose is in maintaining the secondary index only, we will only log only up to a certain limited length of the data in order to save space, since our index has a key length limitation of 767 bytes for COMPACT and REDUNDANT for formats, and 3072 bytes for DYNAMIC and COMPRESSED row formats.

Changes Since the Lab Release

There are a few noteworthy and useful changes/additions since the initial Lab release:

  1. A single “functional index” can now be created on a combination of both virtual columns and non-virtual generated columns. That is, you can create a composite index on a mix of virtual and non-virtual generated columns.
  2. Users can create functional indexes ONLINE using the in-place algorithm so that DML statements can still be processed while the index is being created. In order to achieve that, the virtual column values used within the concurrent DML statements are computed and logged while the index is being created, and later replayed on the functional index.
  3. Users can create virtual columns based on other virtual columns, and then index them.
  4. The next improvement is less visible to user, but still worth mentioning. It is about enhancements to the purge related activities on indexed virtual columns. A new callback (WL#8841) provides a server layer function that can be called by InnoDB purge threads to compute virtual column index values. Generally this computation is done from connection threads (or sessions), however, since internal InnoDB purge threads do not correspond to connections/sessions and thus don’t have THDs or access to TABLE objects, this work was necessary in order to provide a server layer callback which enables the purge threads to make the necessary computations.

Limitations

There are still some notable restrictions around the “functional indexes”, some of which will be lifted by later work:

  1. Primary Keys cannot be added on virtual columns.
  2. You cannot create a spatial or fulltext index on virtual columns (this limitation will eventually be lifted).
  3. A virtual index cannot be used as a foreign key.
  4. You cannot create virtual columns on non-repeatable/non-deterministic functions. For example:
  5. Adding and dropping of virtual columns can be done in-place or online only when done as single operations by themselves, and not when combined with other table alterations. This limit will be removed later, but you can always go around it by keeping the in-place ADD/DROP virtual columns operations contained within a separate DDL statement.

A Few More Examples

In the previous blog post we have an example on how to use a “functional index” in conjunction with some JSON functions. Users can essentially use any functions for virtual columns, except for those that are non-deterministic (such as NOW()). So let’s next walk through some additional examples using some non-JSON functions:

  1. Indexes on XML fields
  2. Indexes on Geometry calculations

    Here is an example showing how you can quickly add a virtual column that stores the distance (in meters) between two geographic points or (LONGITUDE, LATITUDE) coordinate pairs:

    First we’ll create a table with some geography data in it:

    Now you want to measure the distance (in meters) between the two points. You can quickly ADD a virtual column and then index it, all without having to rebuild the table:

    Now you can query this table easily and quickly using this new virtual column and its index:

  3. String manipulation

    You can also add some columns that use any of the string manipulation functions. For example:

Lastly, it’s worth noting that you can also create your own custom functions (or UDFs) and use those as the basis for your virtual columns.

Some Quick Performance Benchmarks

As expected, there will be some additional write cost when using an index on a virtual column due to the necessary computation of the virtual columns when they need to be materialized (e.g. INSERT or UPDATE), in other words the costs will be associated with creating and maintaining the index. If the column does not have a functional index, however, then the cost will instead be associated with reads as the value will need to be materialized any time that the row is examined. The added cost is also directly related to the complexity of the computation functions used.

However, even with such additional costs, using virtual columns and “functional indexes” can still be far better than creating the table with such STORED generated columns as the latter materialize the data in the clustered index (primary key), thus resulting in a larger table (both on disk and in memory).

These quick tests are conducted on 3 types of table:

  1. A table with virtual columns:

    This table has 4 normal columns and 5 VIRTUAL columns. We make the computation function used very simple so as to minimize the impact from the function itself.
  2. A “normal” table without a any generated columns at all (neither VIRTUAL or STORED):

  3. A table with materialized or STORED generated columns:

We then use the following procedure to INSERT rows into each of these tables:

All tests were conducted on a 48 core x86_64 GNU/Linux machine, with a 10GB InnoDB buffer pool. All tests were run with a single thread. Each number comes from averages of over 3 runs. Here are the results:

  1. Insertion without an index:
    Insert of 500,000 row Insert of 1,000,000 row
    Table 1. with virtual column (t) 3 min 24.65 sec 6 min 59.91 sec
    Table 2. without virtual column (t_nv) 3 min 21.41 sec 6 min 31.82 sec
    Table 3. with materialized column 4 min 25.58 sec 8 min 43.66 sec

    So for insertion into tables without any secondary index, the time is very similar for a table without any generated columns at all and the one with VIRTUAL columns. The latter does not have those columns materialized, so the amount of data inserted is exactly the same for the first two tables. However, if the columns are materialized/stored (as in table 3), the time will take substantially longer.

    One thing to note is that even though the time taken for table 1 and table 2 are very similar, table 1 insertion still takes slightly longer. This is due an issue that some unnecessary computation is still done for a table with virtual columns, this will be fixed soon.

  2. Creating an index

    Create index on similar columns for 3 different tables:

    Table with 1,000,000 row
    Create index on t(v_e) 2.90 sec
    Create index on t_nv(e) 2.40 sec
    Create index on t_m(v_e) (Table 3. with materialized column ) 3.31 sec

    Create the index on table 1’s virtual column v_e is a bit more expensive than its base column in table 2, since indexing virtual column(s) requires computing the value. However, in this case, it is still faster than creating an index on the same column with a STORED generated column, simply because the table with generated columns is so much bigger, a simple scan takes more time.

    A few more runs of CREATE INDEX on the table t, just to show the scale of the costs when adding indexes on a virtual column.

    Time to create index on 1,000,000 rows on table 1 (table t with virtual columns)
    Create index on column v_e 2.90 sec
    Create index on column e 2.47 sec
    Create index on column v_b(3) 3.26 sec
    Create index on column b(3) 2.67 sec
    Create index on column v_h_e 2.97 sec
    Create index on column v_a 3.06 sec
    Create index on column v_a_b(10) 4.19 sec

    As mentioned, creating an index on virtual columns are a bit more costly than creating an index on normal columns, since the computation needs to be performed on each row.

  3. Adding a new column

    ALTER TABLE ... ADD COLUMN would usually require a full table rebuild for normal or STORED generated columns. But if you add a virtual column, it is not required and thus will be almost instant.

    ALTER TABLE ADD COLUMN on table with 1million rows
    alter table t_nv add column col1 int; 1 min 20.50 sec
    alter table t_nv add column col2 int GENERATED ALWAYS AS (e) stored; 1 min 32.40 sec
    alter table t_nv add column col3 int GENERATED ALWAYS AS (e) virtual; 0.10 sec

    So if you add a virtual column and then materialize it via CREATE INDEX, it will only take a few seconds (2 to 3 sec for creating the index according to previous experiment). If you do that for a normal column or generated column, it will take 50x to 60x more time (mostly spent in rebuilding the table).

  4. Dropping a column

    Similarly, dropping a virtual column is far faster for the same reasons.

    ALTER TABLE DROP COLUMN on table with 1million rows
    alter table t_nv drop column col1; 47.02 sec
    alter table t_nv drop column col2; (A GENERATED column) 50.41 sec
    alter table t_nv add column col3; (A virtual column) 0.10 sec
  5. DMLs with a virtual index or “functional index”:
    1. INSERT
      Insert of 500,000 rows
      Table 1 with functional index on column v_e 6 min 57.31 sec
      Table 2 with index on column e 6 min 33.09 sec
      Table 3 with index on column v_e 9 min 5.24 sec

      As shown in this example, for a table with indexed virtual columns, ts insertion times are significantly less than table 3, which materializes the value in the clustered index (primary key).

    2. UPDATE

      The following UPDATE statements were then performed on the 3 tables, and results were:

      mysql> update t set e=e+1;

      Update time on table with 1,000,000 rows
      Update on table 1 with index on virtual column v_e 1 min 20.39 sec
      Update on table 2 with index on column e 52.26 sec
      Update on table 3 with index on materialized column v_e 1 min 2.52 sec

      As you can see, UPDATE statements on indexed virtual columns are more expensive. This demonstrates the additional MVCC costs associated with the operation (in addition to any operation associated with column e) because 1) The old value for v_e needs to be computed (for the UNDO log) and 2) The old and new values for v_e will need to be UNDO logged.

    3. DELETE
      Delete of 1,000,000 rows
      Delete all row with index on virtual column ‘v_e’ 21.52 sec
      Delete all row with index on ‘e’ 20.54 sec
      Delete on table 3 with index on materialized column v_e 32.09 sec

      The DELETE statements were faster on table with VIRTUAL generated columns than those for table with the STORED generated columns. The table with virtual columns is apparently much smaller than that with materialized column. So the deletion operation is much faster.

      The DELETE operation will also require a little extra MVCC work if there are indexes on virtual columns because 1) The old value for v_e needs to be computed (for the UNDO log) and 2) The old and new values for v_e will need to be UNDO logged.

      So the DELETE statement is a little bit more expensive than when using a regular column, but much faster than those with STORED generated columns.

  6. SELECT Queries:

    Of course, as expected, the table with STORED generated columns is much larger than the one with VIRTUAL generated columns. And this is clearly shown with a quick table scan (after the initial run to bring the data into the buffer pool)

    select count(*) from t
    Table 1. with virtual column (t) 0.59 sec
    Table 2. without virtual column (t_no_v) 0.60 sec
    Table 3. with materialized column 1.30 sec

    As shown above, a table scan on the table with STORED generated columns took 3x longer than the scan on the table with VIRTUAL generated columns.

    While the table with virtual columns and indexes remains small, it still takes advantage of having a materialized (secondary) index to facilitate efficient queries:

    Query on char_length(a) on table with 1,000,000 rows
    Table 1 with virtual column and index on ‘char_length(a)’ 0.00 sec
    Table 2 without index on ‘char_length(a)’ 0.66 sec
    Table 3 with stored column and index on ‘char_length(a)’ 0.00 sec

    Without the “functional index” on the ‘char_length(a)‘ value, table 2 requires a full table scan to get the results.

Summary

The virtual column and functional index work is now officially in 5.7! This is a great feature that allows users to ADD and DROP VIRTUAL generated columns, along with adding optional secondary indexes on those columns, all done using ONLINE operations. As shown in the simple performance study above, the data materialized in such a way keeps the base table small (as it does not have duplicate copies in the InnoDB clustered/primary index) and thus making more efficient use of persistent storage space and memory, while at the same time providing vastly improved query performance!

That’s it for now. As always, THANK YOU for using MySQL!

Car engine

What Makes the MySQL Audit Plugin API Special?

Why Should I Be Reading This?

To better understand how the MySQL Server functions, how to monitor the relevant server events, and find out what’s new in MySQL 5.7.8.

What’s Special About the Audit Plugin API?

Picking the right API for your new plugin is probably the most important design decision a plugin author will need to make. Each of the plugin types exposed by the MySQL server have interesting and unique events that plugins can consume. But in addition to that, some of them provide very important additional characteristics that make these APIs stand out and become much more convenient to use.

One API in particular—the Audit Plugin API—is full of very interesting traits:

  • It can support multiple active plugins receiving the same event(s).
  • It stores plugin references in the execution context so that it doesn’t have to repeatedly search for it in the global plugin list.
  • It supports grouping of events in a number of event classes or categories and allows plugins to subscribe to only those event classes that they are interested in.

Let’s go into more detail on why each of these are important.

Multiple Active Plugin Support

Take for example the Authentication Plugin API. Each user account uses one and only one authentication plugin. This makes a lot of sense because user accounts can only authenticate in one certain way. And there’s a lot of good authentication plugins out there. But what if I want to take some extra action when users connect?

Yes, in theory I can tweak the code of the authentication plugin(s) I’m using. But maintaining my custom changes on top of the ever changing upstream(s) can quickly become tedious, especially if I’m using multiple authentication methods within MySQL.

This is where the Audit API’s ability to support multiple active plugins all receiving the same event can be extremely handy. It allows me to write my own little plugin, then subscribe to the MYSQL_AUDIT_CONNECTION_CLASS and react to the events I need. For example, MYSQL_AUDIT_CONNECTION_CONNECT and MYSQL_AUDIT_CONNECTION_CHANGE_USER. And I can do all this without even modifying the existing plugins.

Caching the Plugin References in the Execution Context

Plugins are dynamically loadable and unloadable.  Loading and unloading is done through (eventually concurrent) SQL commands. And plugins loaded from one thread need to become visible to all of the other threads too. To implement all this the MySQL server keeps the plugins in a global list and employs some good old fashioned multi-threaded programing techniques to protect that list from concurrent access. Namely it uses a mutex called LOCK_plugin.

Now let’s look at what it takes to be able to safely call a plugin method. First of all we need to ensure that the plugin will not be unloaded for as long as we need it. Obviously it’s not practical to be holding LOCK_plugin for the entire duration that we’re using the plugin, as no other plugin operation can occur while we are holding the lock.

The server solves this by reference counting the loaded plugins. When we need to call a plugin we can lock LOCK_plugin, take a plugin reference (increasing the reference count as we do), then release LOCK_plugin and go on and use the plugin.

Consequently, when we are done with our plugin usage, we can lock LOCK_plugin, release the plugin reference (decreasing the reference count in the process), and then release LOCK_plugin.

Now imagine we need to do this millions of of time to execute a single query (e.g. call a function that calls a plugin from inside a subquery). The LOCK_plugin usage can cause a significant performance hit.

This is where caching the plugin references in the execution context comes in very handy. What it does is instead of releasing the reference right after usage it will store it into the session’s context, so that the next time we need the plugin we won’t have to take any mutexes or look at any global structures. We can just reuse the reference we’ve already taken. Of course there’s a cost for that. And it comes from the fact that one can’t UNINSTALL a plugin that has active references to it. But most of the time this is a very small cost to pay for much better performance.

Event Pre-Filtering

The Audit API generates an event for every query that’s executed and for every connection/disconnection. In the typical scenario the latter is much rarer than the former. Why would we need to bother calling all plugins that need to react on connect/disconnect for each query? Not only is there no real benefit, but there’s a cost to that (see the explanations in the previous section). This is where the event pre-filtering comes in handy. Check out the Audit Plugin data structure:

More specifically the class_mask property. This defines a bit-mask of all possible event classes the plugin is interested in receiving. The server aggregates this mask at INSTALL PLUGIN time so that it can consult it when it’s about to dispatch an event to the plugins, dispatching it solely to the plugins that are interested in receiving it and avoiding the dispatch altogether if there are no plugins interested in it.

Changes in 5.7.8

I hope you’ve seen the Query Rewrite API in the previous 5.7 DMRs (see Martin’s two part blog series for an introduction: part1, part2). It’s a useful API allowing one to install pre and post-parse plugins that can alter the query being executed. That API was being called several times during each query execution but without having some of the traits described above. Instead of re-inventing the wheel for it we’ve just transformed that separate plugin API into an audit API event class. And thus the Query Rewrite Plugins are now actually Audit plugins. This is described further in worklog 8505.

We’re also working on strengthening the good parts of the Audit API even further, so stay tuned!

That’s it for now. I hope that this has helped to shine a light on how useful the Audit Plugin API is! THANK YOU for using MySQL!

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 Marcin Babij and 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)