InnoDB全文索引:N-gram Parser

InnoDB默认的全文索引parser非常合适于Latin,因为Latin是通过空格来分词的。但对于像中文,日文和韩文来说,没有这样的分隔符。一个词可以由多个字来组成,所以我们需要用不同的方式来处理。在MySQL 5.7.6中我们能使用一个新的全文索引插件来处理它们:n-gram parser.

什么是N-gram?

在全文索引中,n-gram就是一段文字里面连续的n个字的序列。例如,用n-gram来对”信息系统”来进行分词,得到的结果如下:

 如何在InnoDB中使用N-gram Parser?

N-gram parser是默认加载到MySQL中并可以直接使用的。我们只需要在DDL中创建全文索引时使用WITH PARSER ngram。比如,下面的SQL语句在MySQL 5.7.6及更高版本上可以运行。

我们引入了一个新的全局变量叫ngram_token_size。由它来决定n-gram中n的大小,也就是词的大小。它的默认值是2,这个时候,我们使用的是bigram。它的合法的取值范围是1到10。现在,我们很自然会想到一个问题:实际应用中应该如何设置ngram_token_size值的大小呢?当然,我们推荐使用2。但是你也可以通过如下这个简单的规则来可以选择任何合法的值:设置到你希望能查询到的最小的词的大小。如果你想查询到单个字,那么我们需要设置为1。 ngram_token_size的值设置的越小,全文索引占用的空间也越小。一般来说,查询正好等于ngram_token_size的词,速度会更快,但是查询比它更长的词或短语,则会变慢。

N-gram分词处理

N-gram parser和系统默认的全文索引parser有如下不同点:

  1. 词大小检查:因为有了ngram_token_size,所以innodb_ft_min_token_sizeinnodb_ft_max_token_size将不适用于n-gram。
  2. 无用词(stopword)处理:通常,对于一个新的词,我们会查找stopwords表,看是否有匹配的词。如果有,这个词就不会加入到全文索引中。但是在n-gram中,我们会查找stopwords表,看是否包含里面的词。这样处理的原因是,在中日韩的文本中,有很多没有意义的字符,词语和标点符号。比如,如果我们把‘的’加入到stopwords表中,那么对于句子‘信息的系统’,在默认情况下我们分词结果为‘信息’,‘系统’。其中‘息的’和‘的系’被过滤掉了。

我们可以通过查询INFORMATION_SCHEMA.INNODB_FT_INDEX_CACHEINFORMATION_SCHEMA.INNODB_FT_TABLE_TABLE来查询哪些词在全文索引里面。这是一个非常有用的调试工具。如果我们发现一个包含某个词的文档,没有如我们所期望的那样出现在查询结果中,那么这个词可能是因为某些原因不在全文索引里面。比如,它含有stopword,或者它的大小小于ngram_token_size等等。这个时候我们就可以通过查询这两个表来确认。下面是一个简单的例子:

 N-gram查询处理

文本查询(Text Searches)

  • 在自然语言模式(NATURAL LANGUAGE MODE)下,文本的查询被转换为n-gram分词查询的并集。例如,(‘信息系统’)转换为(‘信息 息系 系统’)。下面一个例子:
  • 在布尔模式(BOOLEAN MODE),文本查询被转化为n-gram分词的短语查询。例如,(‘信息系统’)转换为(“‘信息 息系 系统'”)。

通配符查询(Wildcard Searches)

  • 如果前缀的长度比ngram_token_size小,那么查询结果将返回在全文索引中所有以这个词作为前缀的n-gram的词。
  • 如果前缀的长度大于等于ngam_token_size,那么这个查询则转换为一个短语(phrase search),通配符则被忽略。例如,(‘信息*’)转换为(‘”信息”‘),(‘信息系*’)转换为(‘”信息 息系”‘)。

短语查询(Phrase Searches)

  • 短语查询则被转换为n-gram分词的短语查询。比如,(‘信息系统’)转换为(‘”信息 息系 系统”‘)。

如果您想了解更多关于InnoDB全文索引的详细内容,可以参考用户手册中InnoDB全文索引的部分,还有Jimmy在Dr. Dobb上的精彩文章。如果您想了解更多关于n-gram的详细内容,则可以参考用户手册中n-gram parser的部分。

我们很高兴在MySQL 5.7全文索引中增强对中日韩文的支持,这也是我们工作中很重要的部分,希望这个功能对大家有帮助。如果您有任何问题,可以在本blog中进行评论,提交一个服务需求,或者提交一个bug报告

最后,感谢您使用MySQL!

MySQL Statement Digests

Decoupling Statement Digests From Performance Schema

MySQL Statement Digests are a feature originally introduced as part of the MySQL Performance Schema in MySQL 5.6 to aggregate statement statistics based on the normalized statements executed within the server (for additional info, see here).

Statement Digests were previously only available as a part of the MySQL Performance Schema. In other words, they were only available when Performance Schema was enabled in a running MySQL Server. Now, from the MySQL 5.7.4 DMR on, this Statement Digest feature has been made available irrespective of the Performance Schema.

Why This Is Important

This change allows other MySQL Server Components and Plugins to make use of Statement Digests without being dependent on the Performance Schema configuration. One example of this is the new MySQL Query Rewrite plugin (available in the MySQL 5.7.6 DMR).

Some Additional Notes

There is no change in Performance Schema itself and it still works as it did before with regards to statement aggregation and summary information. All we’ve done is move the Statement Digest functionality to the SQL layer so that it can be used for features unrelated to Performance Schema.

The following MySQL Server options and status variables remain valid and unchanged within the Performance Schema context:

We also added a new max_digest_length MySQL Server option in order to provide greater flexibility in the behavior of Statement Digests. For more details on this new option, please see my previous post on the topic.

If you would like some additional details on this work, you can see the Worklog entry here. If you have any questions, please feel free to post them here on the blog post or in a support ticket.

As always, THANK YOU for using MySQL!

MySQL Statement Digests: Configurable Max Lengths

MySQL Statement Digests are a feature originally introduced as part of the MySQL Performance Schema in MySQL 5.6 to aggregate statement statistics based on the normalized statements executed within the server (for additional info, see here).

Digest calculations are done based on the tokens found in the statement text. The length to which these tokens were considered for the digest calculation was previously fixed at 1024 bytes. Which meant that while reading the tokens, once 1024 bytes were read from statement’s token stream, only that many tokens were considered when generating the Statement Digest. Due to this, statements which only differed after the first 1024 bytes would generate the same digest and thus be aggregated together.

To improve upon this behavior, this limit has been made configurable in MySQL 5.7.6 with the introduction of a new server variable called max_digest_length (range 0 – 1048576). The default value of this new server variable is 1024:

For more information, please refer to the MySQL Documentation here.

We hope that you find this new feature useful! If you have any questions please feel free to post them here on the blog post or in a support ticket. If you feel that you have encountered any related bugs, please let us know via a comment here, a bug report, or a support ticket.

As always, THANK YOU for using MySQL!

MySQL Performance Schema: Instrumentation Exceptions

The setup_actors table in MySQL Performance Schema can be used to specify what users and hosts one wants to have instrumentation enabled for. By default, connections from all users and hosts are instrumented:

You can then use standard SQL against this setup_actors table in order to specify what users and hosts you want to have instrumentation enabled for.

But what about the case where you want to enable instrumentation for everyone except the ‘mayank’ user?

In the MySQL 5.7.6 DMR, a new ENABLED column has been added to the setup_actors table. So now the default configuration looks like this:

This new column now allows us to easily specify that we want to enable instrumentation for all users except ‘mayank':

The same goes for hosts as well. For example, you may want to enable instrumentation for all hosts except ‘localhost':

So with the above configuration, instrumentation is enabled for all user/host combinations except for the ‘mayank’ user (from any host) and any user from ‘localhost’.

We hope that you find this new feature useful! If you have any questions please feel free to post them here on the blog post or in a support ticket. If you feel that you have encountered any related bugs, please let us know via a comment here, a bug report, or a support ticket.

As always, THANK YOU for using MySQL!

InnoDB Full-Text: MeCab Parser

In addition to our general CJK support, as detailed in this blog post, we’ve also added a MeCab parser. MeCab is a Japanese morphological analyzer, and we now have a full-text plugin parser based on it!

How Would I Use It?

  1. Set the mecab_rc_file option — mecab_rc_file is a read-only system variable pertaining to the MeCab parser. The mecabrc file that it points to is a configuration file required by MeCab, and it should at least have one entry for dicdir=/path/to/ipadic , which tells MeCab where to load the dictionary from.Once MySQL is installed, we have a default bundled mecabrc file in /path/to/mysql/install/lib/mecab/etc/mecabrc , and we have three dictionaries within the
    /path/to/mysql/install/lib/mecab/dic directory: ipadic_euc-jp, ipadic_sjis, and ipadic_utf-8. We’ll need to modify the mecabrc file to specify which one of these three dictionaries we want to use.

    Note: If you have your own dictionary, you can instead use that as well. There are also many additional options that can be specified within the mecabrc file. For more information about that configuration file, please see the documentation here.

    For our testing purposes here, let’s load ipadic_utf-8 using these steps (in my case, MySQL 5.7.6 is installed in /usr/local/mysql):

    1. Add an entry in the mecabrc file like this:
      dicdir=/usr/local/mysql/lib/mecab/dic/ipadic_utf-8
    2. Add an entry in the [mysqld] section of /etc/my.cnf like this:
      loose-mecab-rc-file=/usr/local/mysql/lib/mecab/etc/mecabrc
  2. Set innodb_ft_min_token_size — The recommended value is 1 or 2 with the MeCab parser (the default value is 3). We will use 1 for the following examples.
  3. Install the MeCab Plugin:
  4. Create a Full-Text Index with Mecab:

More on MeCab Tokenization

Let’s look at an example that demonstrates how the word tokenization is done:

More on Full-Text Searches with Mecab
Text Searches

  • In NATURAL LANGUAGE MODE, the text searched for is converted to a union of search tokens. For example, '日本の首都' is converted to '日本 の 首都'. Here’s a working example:
  • In BOOLEAN MODE searches, the text searched for is converted to a phrase search. For example, '日本の首都' is converted to '"日本 の 首都"'. Here’s a working example:

Wildcard Searches

  • We don’t tokenize the text of a wildcard search. For example, for '日本の首都*' we will search the prefix of '日本の首都', and may not produce any matches. Here’s two working examples:

Phrase Searches

  • A phrase search is tokenized by mecab. For example, "日本の首都" is converted to "日本 の 首都". Here’s a working example:

MeCab Limitations

It only supports three specific character sets: eucjpms (ujis), cp932 (sjis), and utf8 (utf8mb4). If there is a mismatch between what MeCab is using and what the InnoDB table is using—for example the MeCab character set is ujis, but the the fulltext index is utf8/utf8mb4—then you will get a character set mismatch error when attempting the search.

If you would like to learn more general details about InnoDB full-text search, please see the InnoDB Full-Text Index section of the user manual and Jimmy’s excellent Dr. Dobb’s article. For more details about the MeCab parser specifically, please see the MeCab parser section in the user manual.

We hope that you find this new feature useful! We’re very happy to have improved CJK support throughout MySQL 5.7, and this is a big part of that. If you have any questions please feel free to post them here on the blog post or in a support ticket. If you feel that you have encountered any related bugs, please let us know via a comment here, a bug report, or a support ticket.

As always, THANK YOU for using MySQL!

InnoDB Full-Text : N-gram Parser

The default InnoDB full-text parser is ideal for latin based languages where whitespace is the token or word separator, but for languages like Chinese, Japanese, and Korean (CJK)—where there is no fixed separators for individual words, and each word can be compromised of multiple characters—we need a different way to handle the word tokens. I’m now very happy to say that in MySQL 5.7.6 we’ve made use of the new pluggable full-text parser support in order to provide you with an n-gram parser that can be used with CJK!

What Exactly Is N-gram?

In full-text search, an n-gram is a contiguous sequence of n characters from a given sequence of text. For example, with n-gram we tokenize the text “abcd” as follows:

How Can You Use the N-gram Parser with InnoDB?

The new n-gram parser is loaded and enabled by default. To use it, you would simply specify the WITH PARSER ngram clause in your relevant DDL statements. For example, any of the following statements are valid in MySQL 5.7.6 and later:

We also have a new global server variable called ngram_token_size (a token being roughly equivalent to a word, made up of n characters). The default value is 2 (bigram), and the valid range is from 1 to 10. The next natural question then becomes: what token size value should I choose? 2, or bigram, is recommended for general use with CJK, but you can choose any valid value based on this simple rule: set the token size to the largest token you expect to search for. If we only ever want to search for single characters, then we should set ngram_token_size to 1. The smaller the ngram_token_size the smaller the index, and the faster the full-text searches using that index will generally be. The downside, however, is that you’re then limiting the token size and thus what you can search for. For example, ‘Happy Birthday’ in English translates to ‘生日高興’ in traditional Chinese, where ‘Happy’ == ‘高興’ and ‘Birthday’ == ‘生日’. As you can see, each respective word or token is comprised of two individual characters, so in order to search for each token we need to have ngram_token_size set to 2 (the default) or higher.

More on N-gram Tokenization

The n-gram parser differs from the default full-text parser in the following ways:

  1. Token sizes — innodb_ft_min_token_size and innodb_ft_max_token_size do not apply. You instead specify token handling with ngram_token_size.
  2. Stopword handling — we handle stopwords a little differently. Usually if a tokenized word itself (an exact match) is in the stopword table, then it will not be added to the full-text index. With the n-gram parser, however, we check whether the tokenized word contains any word in the stop word table and if so then we do not index that token. The reason for this difference in behavior is that we have so many frequent and meaningless characters, words, and  punctuation with CJK. If we use the contains method to match stopwords, then we can eliminate more useless tokens.
  3. White space — white space is always a hard-coded stopword. As an example, this means that ‘my sql’ is always tokenized to ‘my’, ‘y ‘, ‘ s’, ‘sq’, and ‘ql’, and ‘y ‘ and ‘ s’ will not be indexed.

We can query INFORMATION_SCHEMA.INNODB_FT_INDEX_CACHE and INFORMATION_SCHEMA.INNODB_FT_TABLE_TABLE to see exactly what tokens have been indexed within a specific full-text index. This is a very useful tool for debugging purposes. For example, if a word doesn’t show up in a full-text search results as expected, then the word may simply not be indexed for one reason or another (stop words, token sizes, etc.) and you can query these tables to be find out for sure. Here’s a simple example:

More on Search Result Processing with N-gram

Text Searches

  • In NATURAL LANGUAGE MODE, the text searched for is converted to a union of n-gram values. For example, 'sql' is converted to 'sq ql' (with a default token size of 2 or bigram). Here’s an example:
  • In BOOLEAN MODE searches, the text searched for is converted to an n-gram phrase search. For example, 'sql' is converted to '"sq ql"':

Wildcard Searches

  • If the prefix is shorter than ngram_token_size, then the query returns all rows that contain n-gram tokens starting with that prefix. For example:
  • If the prefix length is equal to or greater than ngram_token_size, then the wildcard search is converted to a phrase search and the wildcard is ignored. For example, 'sq*' is converted to '"sq"', and 'sql*' is equivalent to '"sq ql"':

Phrase Searches

  • A phrase search is converted to a phrase search of n-gram tokens. For example, "sql" is converted to "sq ql". For some examples:

If you would like to learn more general details about InnoDB full-text search, please see the InnoDB Full-Text Index section of the user manual and Jimmy’s excellent Dr. Dobb’s article. For more details about the N-gram parser specifically, please see the N-gram parser section in the user manual.

We hope that you find this new feature useful! We’re very happy to have improved CJK support throughout MySQL 5.7, and this is a big part of that. If you have any questions please feel free to post them here on the blog post or in a support ticket. If you feel that you have encountered any related bugs, please let us know via a comment here, a bug report, or a support ticket.

As always, THANK YOU for using MySQL!

Initialize Your MySQL 5.7 Instances with Ease

MySQL 5.7.6 brings in a simplification that solves the very first problem that I encountered back in the days when I first started using MySQL 5.0. Namely…

How do I create a new database instance?

I know it sounds like a very basic question. But as it turned out, the answer was not that simple. I tried mysqld --help. Nothing there. And then, after reading the manual and trying out the complex command line (including redirection) based steps a couple of times, I just resorted to employing the mysql-test-run.pl test suite driver to create the initial system tables and data for me.

Obviously this has disadvantages too. mysql-test-run.pl is a test tool, so it creates the database in an extra-permissive mode. It has a “test” database/schema that everybody can write to. It has users I don’t want. It recently started having an “mtr” database too that I didn’t want either. That’s tolerable when you’re developing the server, but certainly not when you’re setting up a database server for production use.

Another potential option was running mysql_install_db… except that I was running on MS Windows, and it is not natively supported on Windows.

Then my colleagues—who were also fed up with having to create and maintain their own custom versions of mysql_install_db for Windows—started insisting that it’s high time we do something about this.

This is exactly why it’s great to be a MySQL server developer. You can actually scratch your own itches. :)

So we started looking into it, and thanks to the wonderful system of Development Milestone releases (DMRs) we had an ideal medium with which to communicate our plans to everyone. We first tried making mysql_install_db easier to use. We got rid of the Perl scripting and re-wrote it in C. It started looking better.

But after having fully analyzed exactly what mysql_install_db was trying to do, it became obvious that there’s no way this would work reliably cross platform without another large command line tool.

It just was not the right approach.

What do you do in such cases?

You cut out the middle man.

mysql_install_db‘s primary job was to invoke the mysqld (MySQL server) binary in a special mode and pipe in a bunch of SQL scripts to it.

What if mysqld could instead do the job all on its own? Without the need for all of these extra SQL files? So you don’t have to carry them around and worry if they match the binary? Why not have mysqld bootstrap itself and eliminate all of this complexity?

This is how the new --initialize MySQL server option was born.

A single option to initialize your database instance! Using nothing but your database server itself! Always consistent and cross platform!

Bye bye scripts/mysql_system*.sql!

Since it’s the same server working with the same data directory, what about cases where there’s already existing data within the data directory? You don’t want that overwritten! So if the data directory exists and there’s even a single item in it, then the --initialize procedure will stop right there with a helpful and descriptive error message. If the data directory does not yet exist, then it will simply create the data directory for you. This work keeps things nice and simple, offering one step setup and initialization.

I was aware that different people like their servers initialized differently. But continuing to support all of these various options that were using all of these various languages and that had all of these secondary path and file dependencies, was just far too complicated—it had a large development cost for us and it provided a poor user experience.

Less is More!

So I’ve decided to go for the lowest common denominator, and to make sure that the aptly named --init-file Server option works well with the new --initialize Server option, so that people who want custom installation methods can add custom install related commands and processes they need that way.

Having that part sorted out, I then set out to ensure that the --initialize step creates the absolute minimum of system tables—the mysql database/schema and the mysql command line tool help files—and a single user account: root@localhost.

Since I primarily work on security related features, the password for that root@localhost account had to be secure. Thus I’ve also made mysqld generate a default password that’s random enough to meet the default policy criteria for the password validation plugin, and finally it also marks it as expired (it’s temporary, and you should change it ASAP).

So far so good, but where do I put the generated temporary password so that you can see it when doing the installation?

I could have written it to the $HOME/.mysql_secret file (just like mysql_install_db was doing).

But I realized that this password should be easy for people to see and use.

People don’t like extra steps: read the manual, go open an obscure hidden file, and so on. Scripts need that. Not people.

Real people just want the password on their screen. One they can copy, paste, and login.

This is why I decided to scrap the file generation and just print the password to STDERR. This way it’s right in front of you. You can simply copy it, run mysql -u root -p, and then paste the temporary password. Note: you also now set the password using a more obvious and straightforward syntax: mysql> set password='mypass';

The concept was now ready.

But then I confronted real life and all the scripts and related files that were then in need of adjustments when using the new method.

Scripts don’t need no random passwords!

All they need is to be able to reliably log in and do stuff on their own. And the only thing preventing them from doing that was the generated random temporary password.

This is why I then also added the --initialize-insecure option to turn the random password generation off and instead leave the root@localhost account with an empty, non-expired password.

And yes, I’ve picked the name on purpose. :)

Now it was time to do some clean up chores.

Obviously mysql_install_db was now going to ride off into the sunset. Not just yet though of course, as we need to give users time to adjust their scripts and habits. So we’ve now simply marked it as deprecated in 5.7.

Then we also realized that the primary use case for the --bootstrap Server option was to support mysql_install_db. So we went ahead and marked this as deprecated too.

Cleaning up is important! My wife would be so proud of me for saying that. :)

There it is.

A better way to bootstrap your MySQL database server!

We look forward to your feedback on this new work! You can leave a comment here on the blog post or in a support ticket. If you feel that you encountered any related bugs, please do let us know via a bug report.

As always, THANK YOU for using MySQL!

MySQL 5.7: InnoDB Intrinsic Tables

Introduction

The MySQL Optimizer sometimes needs a temporary data-store during query processing, for storing intermediate results. Before MySQL 5.7, this need was serviced exclusively using a combination of the HEAP/MEMORY storage engine (for smaller tables) and the MyISAM storage engine (for larger tables). You can find more information on when disk based temporary tables (MyISAM or InnoDB) are used instead of MEMORY tables here.

The InnoDB storage engine has been the default engine used for user tables since MySQL 5.6, but MyISAM was still used for internal disk based temporary tables (for some related notes, see this excellent blog post by Jaime Crespo). This legacy behavior lead to many headaches, both for our users and for our developers. For example, see Stewart Smith’s excellent write up on some of the related problems. In order to address the related issues in MySQL 5.7, we’ve had to do a lot of work within both InnoDB and the Optimizer to lay the groundwork for what we’ll talk about next. You can read about all of the related 5.7 work leading up to this point here.

Optimizer Switches to InnoDB Instrinsic Tables

InnoDB is our MVCC ACID compliant storage engine, and as part of our larger effort to decouple the MyISAM storage engine from MySQL itself so that it becomes an optional engine, starting with MySQL 5.7.6 InnoDB is now the default storage engine used for internal disk based temporary tables. The engine used can be controlled using the new internal_tmp_disk_storage_engine server option.

In our efforts to make this change in MySQL 5.7.6, we have made changes to InnoDB so that it performs as good or better (often far better) than MyISAM when used for internal disk based temporary tables. To fulfill this use-case, we created a new type of table within InnoDB called intrinsic tables, which have relaxed MVCC and ACID semantics. These tables are a special type of temporary InnoDB table that do not perform any UNDO or REDO logging (REDO logging is disabled for all temporary InnoDB tables). As the name suggests, these tables are meant for internal use only and thus can only be used by an internal MySQL module such as the Optimizer. In other words, end-users will not be able to explicitly create these new types of tables (they are also not visible in the innodb_temp_table_info table). We will, however, leverage this new type of table for future end-user focused work. So stay tuned!

A Deeper Dive Inside InnoDB Intrinsic Tables

Let’s now take a deeper dive into this new type of table to better understand some of its key features which allowed us to finally supplant the internal usage of MyISAM (and all of its related issues) for internal disk based temporary tables :

  • Instead of logging information about them within SYS_TABLES and the InnoDB Dictionary cache, all references to them are maintained in thread (or SESSION level) local storage. Given their limited usage and visibility, it is the perfect place to host them.
  • They are created within the shared temporary tablespace, which ensures that they are automatically removed when MySQL is shutdown or restarted (they are normally simply dropped automatically as part of the internal query processing, but this covers non-normal shutdowns such as a system crash or loss of power).
  • These tables don’t need to support ROLLBACK and thus the internal hidden DB_ROLL_PTR column that is normally appended to a table for this purpose is skipped. The only hidden columns for these tables are then DB_ROW_ID and DB_TRX_ID. For these tables, DB_TRX_ID is also not currently used, so the transaction number is simply a dummy counter that facilitates the creation of the read-only view while processing the writes to them.
  • Given that there is no support for ROLLBACK, atomicity can be limited to the row level (somewhat similar to MyISAM). This is really not an issue under the controlled environment used for internal temporary tables, and it also helps to provide a smooth transition for this usage, as we can then replace MyISAM usage here without changing the Optimizer semantics around how they handle the normal difference in atomicity levels between the two engines.
  • They follow a shared nothing architecture (as dictated by the semantics) and so are super fast and very light weight. We optimize the writes by, for example, caching the last inserted position in the default clustered index (based on DB_ROW_ID).
  • There are some additional new features added to InnoDB that are initially meant only for intrinsic tables. For example: delete_all_rows, and enable/disable index.
  • Localized DB_ROW_ID and DB_TRX_ID values are maintained at the table level, which saves a costly round trip to the global InnoDB generator for managing these values. (There are no common objects like UNDO logs generated by these tables so localized counters are fine). We also avoid unnecessary global management of the internal table_id and index_id values.
  • UPDATEs are executed as a series of atomic DELETE+INSERT operations. This avoids unnecessary overhead with regards to space management.
  • Searches are further optimized by caching the last search position and restoring the cursor directly at the block and record where it left off previously. This cursor is then simply invalidated if there is a change to the table structure while a read is happening as part of the larger UPDATE operation.
  • These tables do not use the double write buffer or the adaptive hash index (AHI) features, as they would simply be adding overhead without providing a performance benefit in the case of internal temporary tables.

 

The Performance Results

Not only has this work improved our internal semantics, making for a more robust and
reliable MySQL, but it has also improved the performance! Using the internal_tmp_disk_storage_engine option, we can easily benchmark the performance of MySQL 5.7.6 when using MyISAM or InnoDB for internal disk based temporary tables.

Let’s first look at some simple benchmarks using the 2 most popular suites for benchmarking MySQL, namely sysbench (OLTP workloads) and DBT-3 (OLAP workloads).

Sysbench

InnoDBvsMyISAM

sysbench executed with 1 million seed size, read-only workload.

MySQL consistently performed better when using InnoDB for internal disk based temporary tables.

DBT-3

InnoDBvsMyISAM_DBT3

DBT3 with 10G seed size.

Again, these tests consistently show that MySQL performs as good or better when InnoDB is used for internal disk based temporary tables.

Conclusion

This work plays a large part in our bigger effort to make the InnoDB storage engine the native storage engine for MySQL. Stay tuned for further improvements to MySQL, as we make MySQL an MVCC and ACID compliant server from end-to-end (while allowing users to continue to use MyISAM and other non-transactional engines, but only at their explicit request).

We look forward to your feedback on this new work! You can leave a comment here on the blog post or in a support ticket. If you feel that you encountered any related bugs, please do let us know via a bug report.

As always, THANK YOU for using MySQL!

The MySQL 5.7.6 Milestone Release is Available

The MySQL Development team is happy to announce our 5.7.6 development milestone release (DMR), now available for download at dev.mysql.com! The source code is available at GitHub. You can find the full list of changes and bug fixes in the 5.7.6 release notes. Here are the highlights. Enjoy!

InnoDB

Refactoring needed for general tablespace (CREATE TABLESPACE) support (WL#8109) : This work by Kevin Lewis refactors and cleans up InnoDB code in preparation for supporting general tablespaces in WL#6205.

Implement CREATE TABLESPACE for general use (WL#6205) : This work by Kevin Lewis adds the ability for InnoDB to create a general tablespace using the CREATE TABLESPACE syntax. This work gives the user the freedom to choose their own mapping between tables and tablespaces, i.e. which tablespaces to create and what tables they should contain. This provides the means for doing things such as grouping all tables for a given user or customer within a single tablespace, and thus having all of their data within a single file on the filesystem.

Separate partitioning interface from handler to Partition_handler (WL#4807) : This work by Mattias Jonsson separates the partitioning specific parts of the handler class into an own partitioning interface. This is refactoring done to modularize and decouple the partitioning engine from the rest of the server code base.

Add native partitioning support to InnoDB (WL#6035) : This work by Mattias Jonsson adds native partitioning support to InnoDB. Until now, partitioning support for InnoDB has relied on the ha_partition handler, which created a new handler for each partition, which in turn wasted a lot of resources when using many partitions. By supporting native partitioning within InnoDB one can use far fewer overall resources (see also: bug#62536, bug#37252, and bug#70641). InnoDB native partitioning also paves the way for better overall partitioning. That would include things such as parallel query processing, improved partition pruning (due to more accurate index statistics), foreign key support, global secondary indexes, and full-text searches on partitioned tables.

Add InnoDB events to Performance Schema’s Event Stage table (WL#5889) : This work by Vasil Dimov introduces InnoDB stages in the performance_schema.events_stages_* tables (named ‘stage/innodb/%’). The purpose is to enable progress reporting for long running transactions. Some tasks inside InnoDB may take a significant amount of time (more than a few minutes) to complete, and the users need a way to check on the progress of such a task. For example, if somebody has already waited 3 hours for an ALTER TABLE command to complete, they will want to know if it will likely complete in another 10 minutes, or in another 7 hours.

Improvements around flushing for proper performance (WL#7868) : This work by Yasufumi Kinoshita improves the adaptive flushing algorithm and related mechanisms in order to achieve more consistent or smooth throughput. Please see the WorkLog entry for all of the details.

Make InnoDB fill factor settable (WL#6747) : This work by Yasufumi Kinoshita makes the merge_threshold settable per index. Until now it has been fixed at 50%. If the  amount of data within the page falls below the merge_threshold, e.g. when deleting a row or shortening it via an update, InnoDB will attempt to merge it with a neighbor page. This offers a way to decrease InnoDB’s overall disk footprint, at the expense of some additional work due to the additional page merges.

Make InnoDB the default engine used for internal disk based temporary tables (WL#6737) : This work by Krunal Bauskar makes InnoDB the default engine used for internal disk based temporary tables created by the Optimizer during query processing and execution (e.g. when it needs to produce intermediate results). Until now MyISAM has been the default.

Enhance Check Table for InnoDB Spatial indexes (WL#7740) : This work by Allen Lai extends CHECK TABLE so that it is able to determine whether any spatial indexes are valid. It now checks for a valid R-tree structure and ensures that the entry count matches up with the clustered index.

InnoDB FULLTEXT SEARCH: CJK support (WL#6607) : This work by Shaohua Wang implements CJK (Chinese, Japanese, and Korean) support for InnoDB FULLTEXT search. This work provides a default fulltext parser for CJK (N-GRAM support) and an alternative plugin which provides MeCab support for Japanese. Users can also implement their own pluggable fulltext parser for CJK using the pluggable parser support.

Support for 32k and 64k pages (WL#5757) : This work by Shaohua Wang adds support for 32k and 64k page sizes (the default remains 16k). Bigger page sizes will help to improve compression ratios (the bigger the page size, the more redundant bits), thus offering much improved data compression in conjunction with the new transparent page compression. Furthermore, bigger page sizes allow more “on page” or “inline” storage of BLOBs and large VARCHAR/TEXT fields, thus improving I/O performance when such fields are used.

High Priority Transactions in InnoDB (WL#6835) : This work by Sunny Bains implements high priority transactions within InnoDB, i.e. transactions that shall never be chosen to be aborted in a deadlock scenario. The motivation is to better support MySQL Group Replication, where a transaction cannot be aborted in one replica and committed in another.

Runtime

Introduce a separate error code range for 5.7 (WL#8206) : This work by Jon Olav Hauglid implements a separate range of numbers for new server error messages introduced in 5.7. These codes are currently numbered consecutively, starting at 1000. We also have a second range of error codes used by the client, starting at 2000. The problem is that the first range of codes is currently up to 1974 which means that the two ranges would soon overlap, making it hard to differentiate between server and client errors. The new 5.7 server error codes will now instead start at 3000.

Add MDL for tablespaces (WL#7957) : This work by Sivert Sørumgård defines and implements meta data locking (MDL) for general tablespaces. This is specifically needed for the new general tablespaces in InnoDB, and for the already existing NDB general tablespaces.

Move the plugin and servers tables from MyISAM to transactional storage (WL#7160) : This work by Dmitry Shulga makes InnoDB the default storage engine used for the plugin and server system tables (MyISAM remains optional).

Refactor low-level thread handling (WL#8190) : This work by Jon Olav Hauglid improves the low-level code for handling OS threads. For example, by harmonizing the APIs for Windows and non-Windows platforms (thread create & join), and removes the use of macros.

Non-intrusive refactoring of the Security_context class code (WL#8048) : This work by Praveenkumar Hulakund refactors the Security_context code to ensure proper data encapsulation. This work removes the following issues found in previous versions: 1. some of the security context members were public and the code accessing and modifying these members was spread across many files within the MySQL source code 2. access to these members were not guarded, and thus threads could potentially read stale data when accessing them from a particular thread.

Multi-Source Replication

Multi-source replication  (WL#1697) : This work by Ritheesh Vedire implements Multi-Source Replication. It allows a slave to replicate from multiple sources/masters, but without any additional conflict detection and resolution. One use case is to commission a “fan in slave” for data aggregation or backups. This version takes into consideration the great feedback we got from the community when we first released this via MySQL Labs some time ago.

Replication – Multi Threaded Slaves (MTS)

Optimize MTS scheduling by increasing the parallelization window on master (WL#7165) : This work by Andrei Elkin improves the performance of Multi-Threaded Slaves (MTS) by using a more precise algorithm to determine which transactions are non-conflicting. This allows more transactions to execute in parallel when using --slave-parallel-type=LOGICAL_CLOCK, thus greatly improving the overall replication performance.

Replication – GTID

Support setting gtid_mode=ON online (WL#7083) : This work by Sven Sandberg provides a way to turn on GTIDs online, so that reads and writes are allowed during the procedure, and servers do not need to synchronize. Prior to this work, the user had to stop all updates, synchronize all servers, and then restart all of them simultaneously. This previously meant that enabling GTIDs implied several minutes of planned downtime. See Bug#69059. Now GTIDs can be enabled as an online operation.

Always generate Gtid_log_event and Previous_gtids_log_event (WL#7592) : This work by Sven Sandberg ensures that similar events are generated regardless of the current value for GTID_MODE. Without ensuring this consistent behavior, there were cases where you could potentially lose GTID_EXECUTED and GTID_PURGED values (see the details in the WorkLog entry). (This was originally part of WL#7083).

Session Tracker: Add GTIDs context to the OK packet (WL#6128) : This work by Luis Soares adds a tracker to the response packet of the MySQL protocol. This is done to be able to pass the connector information about the session state collected which can then be used to implement session consistency. This work will be collecting, packing, and sending the data provided by WL#6972 below.

Collect GTIDs to include in the protocol’s OK packet (WL#6972) : This work by Luis Soares implements a mechanism to collect the necessary set of GTIDs to be sent over the wire in the response packet.

Replication – Refactoring

Group Replication: Server Interfaces (WL#8007 and WL#8202) : This work by Tiago Jorge and Nuno Carvalho create stable interfaces useful for MySQL Group Replication and other server plugins. Server plugins can now be informed of server events in the form of server callbacks.

Moving binlog event decoding into a separate module (WL#7440) : This work by Neha Kumari separates out the deserialization of events in a MySQL replication stream into the a separate module. The goal of this refactoring is to break the dependencies between the server core and the replication framework.

Performance Schema

PERFORMANCE_SCHEMA, STATUS VARIABLES (WL#6629) : This work by Chris Powers exposes the GLOBAL and SESSION level STATUS and VARIABLES from within Performance Schema. The corresponding SHOW commands will be preserved for backward compatibility. This work represents an important step in our effort to improve the monitoring framework and to improve MySQL observability.

PERFORMANCE SCHEMA, configurable statement text size (WL#7270) : This work by Mayank Prasad makes the two entities related to Query Text Length—SQL text and digest—configurable. The default for both remains at 1024 bytes. This is a much requested feature from users who commonly have SQL queries larger than 1024 bytes.

PERFORMANCE SCHEMA, SCALABLE MEMORY ALLOCATION (WL#7794) : This work by Marc Alff changes the way performance schema does memory allocations. Previously, the Performance Schema has allocated all of the memory it needs up front when the server starts. This work relaxes the memory constraints to increase ease-of-use (less configuration) and to decrease the overall memory footprint. We are now automatically scaling the memory consumption to match the actual server load.

PERFORMANCE SCHEMA, SETUP_ACTORS ENABLED COLUMN (WL#7800) : This work by Mayank Prasad improves ease-of-use by allowing you to enable all user/host combinations except “this specific one” (exclusion). To allow this, a new column named ENABLED has been added to the setup_actors table to provide users with the ability to specific exclusion as well as inclusion rules.

PERFORMANCE SCHEMA, REDUCE MEMORY USAGE FOR TABLE IO / TABLE LOCK (WL#7698) : This work by Mayank Prasad refactors Performance Schema’s internal buffers so that the memory overhead for the table instrumentation scales better when the number of tables in the database is large.

Security

Limit the scope of the FILE privilege in the default installation – secure deployment (WL#6782) : This work by Harin Vadodaria restricts the scope of the FILE privilege to a secure default value for --secure-file-priv. The server will also warn users of insecure configurations for --secure-file-priv at start-up time.

SSL cert and key generation for MySQL Community – secure deployment (WL#7706) : This work by Harin Vadodaria provides a “best effort” SSL setup process during installation. If successful, the MySQL server configuration file is also modified to reference the newly generated SSL certs and keys.

Temporary disablement of users (WL#6054) : This work by Marek Szymczak makes it possible to enable (unlock) or disable (lock) a user account (login permission) by extending the CREATE USER and ALTER USER commands.

Extend mysql_real_escape_string() to be aware of the string type it’s escaping for (WL#8077) : This work by Marek Szymczak adds an additional argument of a “quote type” to the function mysql_real_escape_string_quote(). This provides the means to explicitly distinguish between single-quoted and double-quoted strings. This breaks the ABI, so it will not be backported to 5.6.

Fold mysql_install_db into the server binary (WL#7307) : This work by Georgi Kodinov simplifies the server bootstrap process and removes the need for the mysql_install_db script. Simple specifying --initialize on the server command line is now enough to create a database directory if it’s absent, and then initialize it.

ENSURE 5.7 SUPPORTS SMOOTH LIVE UPGRADE FROM 5.6 (WL#8350) : This work by Robert Golebiowski ensures a smooth upgrade fom 5.6 to 5.7 without having to specify --skip-grant-tables as part of an intermediate step.

GIS

Spatial Relation Check Functions Now Use Boost.Geometry Extensions  (WL#7224) : This work by David Zhao replaces the old geometry relation check algorithms with new and improved ones. Boost.Geometry 1.56.0 now contains all of the needed type combinations for all used geometry relation check functions.

Geometry Collection Support (WL#7420) : This work by David Zhao implements support for the geometry_collection type in all GIS functionalty—including spatial relationship checking, geometry set operations, and spatial analysis functions—using Boost.Geometry’s functionality for the six basic types (point, linestring, polygon, multipoint, multilinestring, multipolygon).

More user friendly GIS functions (WL#8034) : This work by David Zhao adds some new GIS functions which, while not being defined by the OGC, make MySQL’s GIS much more user friendly. The added functions are: ST_Distance_Sphere(), ST_MakeEnvelope(), ST_IsValid(),  ST_Validate(), ST_Simplify().

GIS MBR spatial operations enhancement (WL#7541) : This work by David Zhao implements various improvements in the GIS Minimum Bounding Rectangle (MBR) support, see the worklog entry for the details.

Consistent naming scheme for GIS functions – Deprecation (WL#8055) : This work by Erik Frøseth cleans up the GIS function namespace 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.

Parser

True bottom-up server parser: refactoring of the INSERT/REPLACE statement (WL#7201) : This work by Gleb Shchepa refactors the parser grammar rules for INSERT and REPLACE statements. This work makes the parser more maintainable and extendable.

True bottom-up server parser: refactoring of the DELETE statement (WL#7202) : This work by Gleb Shchepa refactors parser grammar rules for DELETE and TRUNCATE statements. This work makes the parser more maintainable and extendable.

True bottom-up server parser: refactoring of the UPDATE statement (WL#8062) : This work by Gleb Shchepa refactors parser grammar rules for the UPDATE statement. This work makes the parser more maintainable and extendable.

Optimizer

Generated columns (WL#411) : This work by Benny Wang adds support for Generated Columns (GC). The values of such columns, unlike a regular field’s value, isn’t set by the user but instead computed by the server when the row is created or updated, using the expression specified by the user as part of the table definition.

Query Rewrite Plugins (WL#7589) : This work by Martin Hansson implements a Query Rewrite Plugin, as well as a framework (APIs) for people to write their own custom query rewrite plugins. A query rewrite plugin specifies how certain queries that arrive at the server should be rewritten before they are processed and executed by the server. One usage is to enforce/prevent a certain query plan by adding hints to a query. Our query rewrite plugin is a superb tool for handling such problematic queries when users cannot rewrite the query within the application itself, e.g. because it originates from an external tool (like an Object Relational Mapping tool such as Hibernate) or from some other 3rd party application.

Process subqueries in FROM clause in the same way as view (WL#5275) : This work by Roy Lyseng unifies handling of derived tables and views as much as possible. Until now, subqueries in the FROM clause (derived tables) were unconditionally materialized, while views created from the same query expressions were sometimes materialized and sometimes merged into the outer query. This behavior, beside being inconsistent, can lead to a serious performance penalty. This work fixes Bug#59203, Bug#67631, and Bug#12755.

Make switching of index due to small limit cost-based (WL#6986) : This work by Chaithra Gopalareddy makes the decision in make_join_select() of whether to switch to a new index in order to support "ORDER BY ... LIMIT N" cost-based. This work fixes Bug#73837.

API for estimates on how much of table and index data that is in a memory buffer (WL#7168) : This work by Olav Sandstå extends the handler interface with a new API for providing estimates for where table and index data is currently available. It also contains a default implementation that will be used if the storage engine does not provide this information.

Create JOIN object after query preparation (WL#7870) : This work by Roy Lyseng makes it possible to prepare a query without having a JOIN object available. The JOIN object is hereafter created just before a query is optimized, and can thus be initialized directly from a prepared SELECT_LEX object. See also: Bug#70553.

Deprecation and Removal

Deprecate the ENCRYPT, DES_ENCRYPT and DES_DECRYPT functions (WL#8126) : This work by Harin Vadodaria deprecates the encryption and decryption functions based on the Data Encryption Standard (DES) in favor of Advanced Encryption Standard (AES) based functions, because AES provides much better security.

Deprecate PASSWORD() and extend ALTER USER syntax to manage authentication attributes (WL#6409) : This work by Bharathy Satish deprecates SET PASSWORD 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 and remove the sync_frm sysvar (WL#8216) : This work by Jon Olav Hauglid deprecates the sync_frm system variable in 5.7 (removed in 5.8).

Deprecate GLOBAL @@collation_database, @@character_set_database; assignment of SESSION counterparts (WL#3811) : This work by Jon Olav Hauglid deprecates the GLOBAL collation_database and character_set_database variables in 5.7 (removed in 5.8) and deprecates the SESSION collation_database and character_set_database variables in 5.7 (making them read-only in 5.8). See also: Bug#35357, Bug#27208, and Bug#27687.

Deprecate the conversion of pre MySQL 5.1 encoded database names (WL#8186) : This work by Jon Olav Hauglid deprecates the conversion of pre MySQL 5.1 encoded database/schema names in 5.7 (removed in 5.8). See also: ALTER DATABASE … UPGRADE DATA DIRECTORY NAME.

Remove the innodb_file_io_threads variable in 5.7 (WL#7149) : This work by Sunny Bains removes the innodb_file_io_threads system variable, which was deprecated in 5.5.

That’s it for now. Thank you for using MySQL!

Performance Impact of InnoDB Transaction Isolation Modes in MySQL 5.7

During the process of reviewing our server defaults for MySQL 5.7, we thought that it might be better to change the default transaction isolation level from REPEATABLE-READ to READ-COMMITTED (the default for PostgreSQL, Oracle, and SQL Server). After some benchmarking, however, it seems that we should stick with REPEATABLE-READ as the default for now.

It’s very easy to modify the default isolation level, however, and it can even be done at the SESSION level. For the most optimal performance you can change the transaction isolation level dynamically in your SESSION according the situation:

  • For short running queries and transactions, use the default level of REPEATABLE-READ.
  • For long running queries and transactions, use the level of READ-COMMITTED.

You can find the full details of our recent benchmarking efforts on this topic here: http://dimitrik.free.fr/blog/archives/2015/02/mysql-performance-impact-of-innodb-transaction-isolation-modes-in-mysql-57.html

As always, THANK YOU for using MySQL!