Tag: MySQL 8.0

Feed: Planet MySQL. Author: MySQL Server Dev Team. MySQL 8.0.1では、utf8mb4の大文字小文字およびアクセント記号付きの文字を判別するas_cs collationに加えて、日本語用のCollation(文字照合順)を追加しました。 utf8mb4_ja_0900_as_csについて 日本語に関する文字照合およびソートのルールは複雑です。日本語ではひらがな、カタカナ、漢字、アルファベット(ラテン文字)を混在させて利用しています。さらに、全角と半角が存在する文字もあります。では、‘あ’, ‘ア’, ‘a’, ‘ｱ’はどのようにソートされるのでしょうか？ Unicode照合アルゴリズム(UCA / Unicode Collation Algorithm)の中で規定されているデフォルトUnicode照合基本テーブル(DUCET / Default Unicode Collation Element Table)にてUnicode文字列の比較アルゴリズムを定義しています。このDUCETは言語ごとにカスタマイズされた情報をXML形式で提供する共通ロケールデータリポジトリ(CLDR, Common Locale Data Repository)にて、日本語に関するソート順を定義した部分[reorder Latn Kana Hani]での日本語に関するソート順のルールによると、ラテン文字は全てのひらがなやカタカナよりも前に位置づけられているため‘a’が最初となります。では残りの‘あ’, ‘ア’, ‘ｱ’はどうなるでしょうか？ここでは以下のルールが適用されます: ‘&あ なぜ‘あ’, ‘ア’, ‘ｱ’は等価なのでしょうか？UCAではマルチレベルの比較アルゴリズムを使用して照合を個なっています。この比較アルゴリズムの4次レベルでは‘あ’が‘ア’よりも前に来るとされているにも関わらずです。ポイントはCLDRでは日本語の文字照合順の強度は3次レベルであると定義されています。従って4次レベルでの差は無視されることとなります。これはユーザーの期待した挙動ではないかもしれません。そこで我々は日本語を扱うMySQLユーザーの要望をもとにしたCollationをMySQL 8.0に追加することを検討しています。 日本工業規格の一つJIS X 0208 (http://www.jisc.go.jp/app/pager?id=94516)では、日本語の漢字6,355文字とそのソート順について定義しています。utf8mb4_ja_0900_as_csはこのJIS X 0208に準じた漢字のソートを行います。しかしこのJIS X 0208では含まれない漢字も多数存在します。これらの漢字についてutf8mb4_ja_0900_as_csはUCA(Unicode Collation Algorithm)で定義されたそれぞれの文字の重み(implicit weight) (http://www.unicode.org/reports/tr10/#Implicit_Weights)を利用します。 以下の文字を例にソート順を確認してみます: ‘王’, ‘人’, ‘兵’, ‘﨎’, ‘㐀’最初の3文字はJIS X 0208で定義されていますが、残りの2文字は定義されていません。 mysql> create table jpn(a varchar(10)) collate utf8mb4_ja_0900_as_cs; Query OK, 0 rows affected (0.90 sec) mysql> insert into jpn values ('王'), ('﨎'), ('人'), ('㐀'), ('兵'); Query OK, 5 rows affected (0.05 sec) Records: 5 Duplicates: 0 Warnings: 0 mysql> select ... Read More

Feed: Planet MySQL. Author: Mark Callaghan. I used Linux perf to get more details on system performance while running the point-query test with modern sysbench. This is for an in-memory workload and part of my series on low-concurrency CPU regressions for bug 86215.tl;drI have more explaining to do The increase in instructions/query explains the decrease in queries/second from MySQL 5.6 to 5.7 to 8.0. ConfigurationI tested MySQL using upstream 5.6.35, 5.7.17 and 8.0.1. For 8.0.1 I used the latin1 charset and latin1_swedish_ci collation. My servers are described here and are named i3 NUC and i5 NUC.My usage of sysbench, including the tests run, ... Read More

Feed: Planet MySQL. Author: MySQL Server Dev Team. In a world moving towards Unicode and UTF-8, a lot of applications still use some one-byte character set. And since one-byte characters usually accepts any byte in the range 0x00-0xFF it often works well to store and retrieve any data in such character strings, e.g. UTF-8 encoded data. At least as long as you don’t expect any semantics with regards to ordering and comparisons that will not cause any apparent problems. The MySQL latin1 character set is such a one-byte character set for Western Europe, and it is the default character set ... Read More

Feed: Planet MySQL. Author: Gabriela D'Ávila. Disclaimer: This post is aimed to you, the curious developer, sys-admin, technologist, whatever-title-you-use. DO NOT run the following lines on production. Not even in a stable environment, do this if you don’t care about the outcome of the current data. If you want to keep up with the newest MySQL developer milestones I have news for you: there is no upgrade available for milestone versions. The way to go is to remove old version and install new one, according to their website: Upgrades between milestone releases (or from a milestone release to a GA ... Read More

Feed: Planet MySQL. Author: MySQL Server Dev Team. Anybody who has run the MySQL MTR test suite must have seen a statement like “x tests were skipped, y by the test itself. ” at the end of test runs. Why are some tests being skipped? Are the skipped tests affecting test coverage? If you are interested to know, read on … Background The MTR test suite is run regularly on multiple platforms and with various values for MySQL system variables. Not all MTR tests can run on all environments making it necessary to skip tests in some cases. MTR supports ... Read More

Feed: Planet MySQL. Author: Severalnines. Back in the days when somebody said MySQL, there was only the MySQL. You could pick different versions (4.0, 4.1) but the vendor was the same. This changed around MySQL 5.0/5.1 when Percona decided to release their own flavour of MySQL - Percona Server. A bit later, MariaDB joined with MariaDB 5.1 and the fun (or confusion) increased. What version should I use? What is the difference between MySQL 5.1, Percona Server 5.1 and MariaDB 5.1? Which one is faster? Which one is more stable? Which one has superior functionality? With time, this got worse ... Read More

Feed: Planet MySQL. Author: Mark Callaghan. This continues my series on low-concurrency performance regressions, Here I share results for in-memory sysbench using my home Intel NUC servers. The results were first shared in bug 86215 and the numbers for the charts in this post are here.tl;drFor the i5 NUC most of the regression is from MySQL 5.6 to 5.7 For the i3 NUC MySQL 5.7.17 matches 5.6.35 in many tests and does much better on range scans. Alas I have yet to debug this. But MySQL 8.0.1 is much worse than 5.6.35. At first I only had results for the i3 ... Read More

Feed: Planet MySQL. Author: Morgan Tocker. As Rene wrote on the ProxySQL blog yesterday: Although MySQL Query Cache was meant to improve performance, it has serious scalability issues and it can easily become a severe bottleneck. This is indeed something we have observed in the MySQL team for a while. Before we get to the subject of today’s post, let me start with an introduction. Introduction to Query Cache The MySQL query cache is a query results cache. It compares incoming queries that start with SEL to a hash table, and if there is a match returns the results from ... Read More

Feed: Planet MySQL. Author: Jesper Krogh. This post was originally published on the MySQL Support Team Blog at https://blogs.oracle.com/mysqlsupport/entry/innodb_locks_analysis_why_is on 14 April 2017. Consider the scenario that you execute a query. You expect it to be fast – typically subsecond – but now it take not return until after 50 seconds (innodb_lock_wait_timeout seconds) and then it returns with an error: mysql> UPDATE world.City SET Population = Population + 999 WHERE ID = 130; ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction mysql> UPDATE world.City SET Population = Population + 999 WHERE ID = 130;ERROR 1205 (HY000): Lock wait ... Read More

Feed: Planet MySQL. Author: MySQL Server Dev Team. Some of the users may have noticed that .require files do not exist anymore in the MySQL test suite. A .require is file is used along with 'require' mysqltest command. In test cases, the command 'require' is used to ensure MTR performs certain sanity checks. 'require' command takes filename as the argument, generally a file with require extension and compares its content with the result of next query executed in the test case. A .require file is more like a result file for the next query executed in a test case. First ... Read More