The MySQL Team is Hiring

As mentioned in my recent MySQL Performance Blog interview, Oracle has a number of vacancies in the MySQL team. By my count, there are 21 current vacancies:

  • IRC2431754
    Software Developer 4
    MySQL Server Development (Windows)
  • IRC2433642
    Software Developer 4
    MySQL Server Development (Replication)
  • IRC2435607
    Internet Sales Representative II
    MySQL Corporate Sales
  • IRC2437590
    Software Developer 3
    MySQL Enterprise Tools
  • IRC2423566
    Technology Sales Representative III
    MySQL Sales
  • IRC2421573
    Technical Analyst 3-Support
    MySQL Support
  • IRC2409719
    Sales Consultant
    MySQL Global Business Unit
  • IRC2409720
    Sales Consultant
    MySQL Global Business Unit
  • IRC2303935
    Sales Consultant
    MySQL Global Business Unit
  • IRC2411711
    Telesales Business Development Representative II
    MySQL Sales
  • IRC2401744
    Internet Sales Representative II
    MySQL Sales
  • IRC2335551
    Business Analyst 4-Ops
    Professional Services
  • IRC2376534
    Technical Analyst 3-Support
    MySQL Support
  • IRC2378540
    Technical Analyst 3-Support
    MySQL Support
  • IRC2378080
    Internet Sales Representative II
    MySQL Sales Americas
  • IRC2379740
    Applications Sales Representative III
    MySQL Field Sales
  • IRC2359667
    Software Developer 4
    MySQL Server Development Team
  • IRC2351560
    Software Developer 3
    MySQL Engineering
  • IRC1720632
    Software Developer 4
    MySQL Server General Team
  • IRC2139563
    Technical Analyst 3-Support
    MySQL Support
  • IRC2131703
    Technical Analyst 3-Support
    MySQL Support

To apply, go to irecruitment.oracle.com and enter the IRC code in the search box.

Today's practical use-case for Performance Schema

Today’s blog post starts with a question:

“In my continuous integration environment, running tests currently takes 30 minutes. I
believe that a large percentage of time is spent in MySQL, and I want to know if putting MySQL
on tmpfs will cut that time down?”.

I have previously written about how to configure MySQL to be less durable in testing environments, but today I wanted to write about how we can really test the assumption that the question is based around. That is to say:

  • We know that total time is 30 minutes.
  • We assume MySQL is a large percentage of this.
  • We know that this optimization will potentially allow us to avoid IO.
  • We then want to know what amount of time MySQL spends waiting on IO during our test suite?

And the answer to this is performance schema.
Performance Schema instruments File IO with statistics such as bytes read/written, and wait time. So if we see that IO wait time was only a small percentage of the 30 minutes of test time, we automatically know if this optimization is worth investigating or not.
Here is a simple query that will work on 5.6 out of the box:

mysql> SELECT
 `a`.`EVENT_NAME`,
 `a`.`SUM_TIMER_WAIT`/1024/1024/1024 AS `total_latency_ms`,
 `b`.`SUM_NUMBER_OF_BYTES_READ` AS `total_bytes_read`,
 `b`.`SUM_NUMBER_OF_BYTES_WRITE` AS `total_bytes_written`
FROM
 `performance_schema`.`events_waits_summary_global_by_event_name` `a`
 INNER JOIN `performance_schema`.`file_summary_by_event_name` `b` USING (event_name)
WHERE
 `a`.`EVENT_NAME` like 'wait/io/file/%'
 AND `a`.`COUNT_STAR` > 0\G
*************************** 1. row ***************************
         EVENT_NAME: wait/io/file/sql/casetest
   total_latency_ms: 0.245716745034
   total_bytes_read: 0
total_bytes_written: 0
*************************** 2. row ***************************
         EVENT_NAME: wait/io/file/sql/ERRMSG
   total_latency_ms: 0.911862457171
   total_bytes_read: 58982
total_bytes_written: 0
*************************** 3. row ***************************
         EVENT_NAME: wait/io/file/sql/FRM
   total_latency_ms: 50.292402482592
   total_bytes_read: 137771
total_bytes_written: 0
*************************** 4. row ***************************
         EVENT_NAME: wait/io/file/sql/global_ddl_log
   total_latency_ms: 0.012274521403
   total_bytes_read: 0
total_bytes_written: 0
*************************** 5. row ***************************
         EVENT_NAME: wait/io/file/sql/pid
   total_latency_ms: 0.218277866021
   total_bytes_read: 0
total_bytes_written: 5
*************************** 6. row ***************************
         EVENT_NAME: wait/io/file/mysys/charset
   total_latency_ms: 0.450179565698
   total_bytes_read: 18316
total_bytes_written: 0
*************************** 7. row ***************************
         EVENT_NAME: wait/io/file/mysys/cnf
   total_latency_ms: 0.315982563421
   total_bytes_read: 56
total_bytes_written: 0
*************************** 8. row ***************************
         EVENT_NAME: wait/io/file/myisam/dfile
   total_latency_ms: 0.885083482601
   total_bytes_read: 2322
total_bytes_written: 0
*************************** 9. row ***************************
         EVENT_NAME: wait/io/file/myisam/kfile
   total_latency_ms: 3.881758853793
   total_bytes_read: 7058
total_bytes_written: 0
*************************** 10. row ***************************
         EVENT_NAME: wait/io/file/innodb/innodb_data_file
   total_latency_ms: 114.101030502468
   total_bytes_read: 10223616
total_bytes_written: 49152
*************************** 11. row ***************************
         EVENT_NAME: wait/io/file/innodb/innodb_log_file
   total_latency_ms: 2.865770795383
   total_bytes_read: 69632
total_bytes_written: 3072
11 rows in set (0.01 sec)

Taking it one step further and combining with ps_helper, I think there is a real opportunity to build this metrics collection into the test suite:

/* start by reseting all metrics */
ps_helper.truncate_all();
/* run test suite */
/* Collect test suite metrics from MySQL */
SELECT * FROM ps_helper.wait_classes_global_by_latency;

Here is an example for wait_classes_global_by_latency from the GitHub project:

mysql> select * from wait_classes_global_by_latency;
+-------------------+--------------+---------------+-------------+-------------+-------------+
| event_class       | total_events | total_latency | min_latency | avg_latency | max_latency |
+-------------------+--------------+---------------+-------------+-------------+-------------+
| wait/io/file      |       550470 | 46.01 s       | 19.44 ns    | 83.58 µs    | 4.21 s      |
| wait/io/socket    |       228833 | 2.71 s        | 0 ps        | 11.86 µs    | 29.93 ms    |
| wait/io/table     |        64063 | 1.89 s        | 99.79 ns    | 29.43 µs    | 68.07 ms    |
| wait/lock/table   |        76029 | 47.19 ms      | 65.45 ns    | 620.74 ns   | 969.88 µs   |
| wait/synch/mutex  |       635925 | 34.93 ms      | 19.44 ns    | 54.93 ns    | 107.70 µs   |
| wait/synch/rwlock |        61287 | 7.62 ms       | 21.38 ns    | 124.37 ns   | 34.65 µs    |
+-------------------+--------------+---------------+-------------+-------------+-------------+

Neat, huh?

MySQL 5.6.16 Community Release Notes

Thank you to the MySQL Community, on behalf of the MySQL team @ Oracle. Your bug reports, testcases and patches have helped create a better MySQL 5.6.16.
In particular:

  • Thanks to Honza Horak for suggesting we make tmpdir configurable at build-time, and for providing a patch. Bug #68338.
  • Thanks to Hartmut Holzgraefe for reporting a memory leak when using the InnoDB memcached API and replication. Bug #70757.
  • Thanks to Justin Swanhart for reporting that InnoDB reported an incorrect operating system error code when it failed to initialize. Bug #70867.
  • Thanks to Yoshinori Matsunobu who reported that MySQL 5.6’s persistent InnoDB statistics caused stalls due to latch contention. Bug #70768.
  • Thanks to Laurynas Biveinis for discovering that InnoDB could needlessly call os_thread_get_curr_id(). Bug #70417.
  • Thanks to Nizameddin Ordulu for identifying a case where InnoDB’s doublewrite buffer would not restore corrupted pages but could have. Bug #70087.
  • Thanks to Hartmut Holzgraefe for reporting that the README file for InnoDB memcached incorrectly specified the version of libevent statically linked. Bug #70034.
  • Thanks to Shahriyar Rzayev for reporting that the error message when reseting a slave with innodb_force_recovery set was cryptic. We have since improved the error message. Bug #69907.
  • Thanks to Keith Dechant for reporting that AUTO_INCREMENT values could not be reset when the INPLACE algorithm was used for ALTER TABLE. Bug #69882.
  • Thanks to Laurynas Biveinis for reporting an incorrect comment in the InnoDB source code. Bug #69847.
  • Thanks to Christian Rabe for reporting a problem when using an InnoDB tablespace stored on a raw device. Bug #69424.
  • Thanks to Justin Swanhart for reporting that an online ALTER TABLE operation on a partitioned table can consume a significant amount of memory. Bug #69325.
  • Thanks to Valeriy Kravchuk for reporting a specific workload where InnoDB did not scale well on multi-core machines. In response to Valeriy’s bug report, we added a new feature to use atomic reference counting to track page use instead of mutexes. Bug #68079.
  • Thanks to Stewart Smith for reporting that table renaming operations showed up under foreign key errors in SHOW ENGINE INNODB STATUS. Stewart also generously provided a patch. Bug #61746.
  • Thanks to Laurynas Biveinis for reporting that UNIV_SYNC_DEBUG was disabled erroneously as part of an earlier bug fix. We’ve since reenabled it. Bug #69617.
  • Thanks to Joffrey Michaie for reporting a situation where queries on tables partitioned by hash could return wrong results. Bug #70588.
  • Thanks to zhai weixiang for reporting an issue where binlog dump information could incorrectly be written to the error log. Bug #70685.
  • Thanks to Justin Swanhart for reporting that mysqlbinlog did not properly decode DECIMAL values. Bug #65812.
  • Thanks to Santosh Praneeth Banda for reporting that the semisynchronous replication plugin was called twice for a DDL statement. Bug #70410.
  • Thanks to Yang Dingning from NCNIPC, Graduate University of Chinese Academy of Sciences for reporting a security issue. Bug #61065.
  • Thanks to Bryan Turner for reporting a situation where tables with utf8_bin collation could return results in the wrong order. Bug #69005.
  • Thanks to Honza Horak for reporting code improvement suggestions as the result of a coverity analysis. Bug #70830.
  • Thanks to Ed Reeder for reporting that the option --local-service did not work on Windows. Bug #69637.
  • Thanks to David Coyle for reporting a limitation when using views with an ORDER BY in their definition. We have since worked around this limitation. Bug #69678.
  • Thanks to Davi Arnaut for reporting an issue with the performance_schema instrumentation interface. Bug #70628.
  • Thanks to Laurynas Biveinis for pointing out a situation where make_atomic_cas_body64 might be miscompiled. Thanks also to Davi Arnaut for his comments on the bug. Bug #63451.
  • Thanks to Davi Arnaut for reporting that the mysql_plugin client inadvertently attempts to remove files. Bug #69752.
  • Thanks to Igor Babaev and xiaobin lin who independently discovered a case when COUNT(DISTINCT) could return wrong results. xiaobin lin also provided a patch, which while we did not end up using it, we valued the contribution. Bug #68749, Bug #71028.
  • Thanks to Patrick Middleton for pointing out an issue when trying to compile MySQL without partitioning. While we didn’t end up using it, we thank Patrick for also providing a patch. Bug #71010.
  • Thanks to Anthony Tso for reporting a bug where wrong results could be returned. Bug #70608.
  • Thanks to Elena Stepanova for reporting a security issue. Bug #68751.
  • Thanks to Daniel van Eeden for letting us know that some scripts provided out of date information regarding where to file bug reports. Bug #68742.
  • Thanks to Don Coffin for reporting that some file removal operations were not instrumented by performance_schema. Bug #69782.
  • Thanks to Eric Bergen for reporting that mysqldump would not release metadata locks until after the dump operation had finished. Bug #71017.
  • Thanks to Michael Ezzell for reporting a security issue. Bug #68354.
  • Thanks to Giuseppe Maxia for raising awareness of the obsolete mysqlbug utility. We have since removed mention of it from mysql_install_db. Bug #29716.

Thank you again to all the community contributors listed above. In particular, the MySQL team would like to call out the names that appear more than once in this release:
Honza Horak (2), Hartmut Holzgraefe (2), Justin Swanhart (3), Davi Arnaut (2) and Laurynas Biveinis (4).
If I missed a name here, please let me know!
- Morgan