Sunday, October 16, 2016

MySQL Support Engineer's Chronicles, Issue #1

I have a habit to keep a log of everything I work on (or even just read about while working) every day. It's just a plain text file, with date labels, where most lines are URLs of issues and bugs I contributed to somehow, and my comments on what I did and why. But there are also code fragments, texts, copy-pasted outputs and links to interesting sites, articles and documentation.

Some of lines from this log I post on Facebook separately as I add them, these are mostly related to MySQL bugs and other things that annoy me. I know that some of my Facebook friends actually do not like this kind of posts at all... When I used LiveJornal actively, I've posted digests of my log from time to time (you can check what was there if you read Russian). But that digests contained everything, from funny videos of owls to tips for setting up Informix on Mac OS X, with few MySQL-related things in between. For this blog I decided to resurrect that digests, limit them to things related to MySQL and related forks and technologies, and try to make them regular. The idea is to post what I considered interesting and important at the end of every week. So, this is my first issue of what I am going to call "MySQL Support Engineer's Chronicles". Let's see how it is going to work long term...

This week started with more news related to MyRocks. First of all, it was announced that MyRocks will be included into MariaDB 10.2. New forum was also set up in Google Groups for the related discussions, myrocks-dev. I see only 3 topics there so far, but I still joined this group. I've also spent some time this week testing how well MyRocks scale for SELECTs like those in the famous Bug #68079. Good news is that (while InnoDB after that bug fix still seem to work faster) for that use case MyRocks now runs like 20% faster than last time I checked (end of April, 2016). Both InnoDB in 5.7.15 and MyRocks scale to 32+ threads on my old QuadCore box, that is, they run the same number of queries faster with more threads doing the job. I'll share more details on this test case later, when I get a chance to spend more time running tests with higher concurrency and checking for the bottlenecks.

Other good news were about MySQL & Friends Devroom at FOSDEM 2017. Call for papers is open! FOSDEM is my favorite conference and the only one I am going to participate in by all means every year (as I did in 2015 and 2016). I am going to submit several talks and, besides something with "gdb" in the title, I am thinking about a talk on some special bugs (like Bug #68079, Bug #28404, or Bug #82968) and their influence on MySQL users and further development (maybe even separate talk per each of these bugs), some new MariaDB 10.2 features (like recursive CTEs) or locking in MyRocks.

I've spent some time this week checking different ways to compress data in InnoDB tables (and I expect more work on this next week). Originally the idea was to show that MariaDB's compression really works. I am still thinking about writing a separate blog post showing step by step how it works and why it does not or why somebody can assume it does not (hints: check du and ls -ls commands, don't use just ls -l to make conclusions). For now just check this blog post by my colleague Jan Lindstrom and do not hesitate to try it on HDD with, say, ext4 filesystem. If you care enough to try different compression algorithms (you have to compile MariaDB from source for this), check also this great study by Percona.

Unfortunately these days I can not avoid Galera clusters and related issues, so I spent some time reading and trying to find some proper documentation on what may happen when poor user just decides to shut down all nodes in the cluster (for whatever reason) and then start the cluster again. SST may happen, and this is rarely good. I ended up checking this old post by my former colleague Przemysław Malkowski, some hints by Severalnines and Galera bugs (like Issue #283 and Issue #344). I was reading and thinking about step by step checks for some blog post, but my colleague Geoff Montee already summarized current state of documentation in this Galera Issue #431, "Document that the GCache is invalidated when a node restarts". Take extra care when you decide to shut down the last remaining Galera cluster node!

While checking for known bugs for one of the problems I discussed, I noted this MySQL bug, Bug #80580, "count(*) much slower on 5.7 than 5.6". Unfortunately there was no public test case, even though the bug was "Verified". I do not like to see bug reports like that. So, I've added my test case as a comment. Enjoy and keep thinking that MySQL 5.7 is the best of them all (besides already available 8.0, that added indexes to the tables in Performance Schema). It just has some performance problems with queries that do SELECT count(*) to InnoDB tables, also in case of fulltext indexes, see this recent Bug #83398 by Daniël van Eeden. But nobody cares much about few minor bugs for corner cases (and about those who process that bugs, but that's a different topic)...

By the way, Justin Swanhart had already added support to MySQL 8 to his ps_history schema/tool. Read his blog post for more details. Probably this is one of the first open source third party tools to support MySQL 8 explicitly. Correct me if I am wrong, but the only other one I know about is MySQL Sandbox by Giuseppe Maxia.

During some relatively non-busy time on Friday I decided to upgrade one of my Ubuntu VMs (the one running 15.04) to 16.04, and did that successfully (first to 15.10, then to 16.04). It's nice to know that Ubuntu allowed seamless in-place upgrades since 12.04. I had Percona Server installed from their .deb packages running there, historically, so I made sure it is also upgraded to the latest version for 16.04. In the process I hit a small problem with their repository that Percona tried to warn us all about in this blog post. Unfortunately, as you can see from comments (mine and other user's) suggested way to avoid the warning does not work as expected. I've got no reply from Percona about this.

Also on Friday Alibaba had finally released their AliSQL as open source on GitHub. I've cloned and built it immediately on my Ubuntu 14.04 netbook, and it works! It identifies itself as just MySQL 5.6.32, but read here about some of the great features now available. No bugs found so far, but I had no time to try hard.

So, that were some of things I read about, was excited about, complained about and worked on during this week as a support engineer in MariaDB. Do you consider this kind of digest useful?

4 comments:

  1. Thanks Valerii. Very useful post.

    ReplyDelete
  2. Do you think filesystems are able to handle holepunch-per-page?
    http://smalldatum.blogspot.com/2015/10/wanted-file-system-on-which-innodb.html

    ReplyDelete
    Replies
    1. I trust you that this approach may not work at all for tables of any reasonably big size. There is your http://bugs.mysql.com/bug.php?id=78277 in unclear status (I hope to see it clarified soon by Oracle engineers) etc.

      I was checking MariaDB 10.1.18 with tiny tables on ext4, and surely DROP is notably slower, twice or so. This was LZ4-compressed one:

      MariaDB [test]> \! ls -ls data/test/ttest*ibd
      561156 -rw-rw---- 1 openxs openxs 574619648 жов 17 11:14 data/test/ttest.ibd
      6495708 -rw-rw---- 1 openxs openxs 25375539200 жов 17 14:24 data/test/ttest_lz4.ibd
      MariaDB [test]> show table status like 'ttest_lz4'\G
      *************************** 1. row ***************************
      Name: ttest_lz4
      Engine: InnoDB
      Version: 10
      Row_format: Dynamic
      Rows: 182699880
      Avg_row_length: 136
      Data_length: 24970788864
      Max_data_length: 0
      Index_length: 0
      Data_free: 6291456
      Auto_increment: NULL
      Create_time: 2016-10-17 13:01:51
      Update_time: NULL
      Check_time: NULL
      Collation: latin1_swedish_ci
      Checksum: NULL
      Create_options: `page_compressed`=1
      Comment:
      1 row in set (0.05 sec)

      MariaDB [test]> drop table ttest_lz4;
      Query OK, 0 rows affected (15.18 sec)

      While this was just non-compressed one with a bit more data:

      MariaDB [test]> show table status like 'ttest_lz4'\G
      *************************** 1. row ***************************
      Name: ttest_lz4
      Engine: InnoDB
      Version: 10
      Row_format: Compact
      Rows: 208080623
      Avg_row_length: 128
      Data_length: 26809991168
      Max_data_length: 0
      Index_length: 0
      Data_free: 5242880
      Auto_increment: NULL
      Create_time: 2016-10-17 17:46:13
      Update_time: NULL
      Check_time: NULL
      Collation: latin1_swedish_ci
      Checksum: NULL
      Create_options:
      Comment:
      1 row in set (0.11 sec)

      MariaDB [test]> \! ls -ls data/test/ttest*ibd
      561156 -rw-rw---- 1 openxs openxs 574619648 жов 17 11:14 data/test/ttest.ibd
      28160004 -rw-rw---- 1 openxs openxs 28835840000 жов 17 19:19 data/test/ttest_lz4.ibd

      MariaDB [test]> show create table ttest_lz4\G
      *************************** 1. row ***************************
      Table: ttest_lz4
      Create Table: CREATE TABLE `ttest_lz4` (
      `c1` char(100) DEFAULT NULL
      ) ENGINE=InnoDB DEFAULT CHARSET=latin1
      1 row in set (0.08 sec)

      MariaDB [test]> drop table ttest_lz4;
      Query OK, 0 rows affected (8.32 sec)

      But I do not yet see so bad effects as you described previously, not for the tables several GB in size on disk. I'll keep checking. Theoretically I can try to reproduce your test case from that bug report even literally, just not now and not on this netbook :)

      Delete
  3. Or maybe I don't understand your comment about compression.

    ReplyDelete