PlanetMysql.ru — информация о СУБД MySQL

I've been going through our bugs database to compile a list of some noteworthy patch contributions that have been included in the MySQL 5.5 release. Of course any contribution is appreciated, no matter how small! And the list is probably not complete — please let me know if I'm missing any. I omitted a number of smaller patches that fixed compile issues and I only considered contributions that were tracked in our bug database and were tagged as "Contribution".

Note that these are new patches that have not been part of any other MySQL release — of course, all contributions from previous releases are included in 5.5 as well. We also received a few patches for InnoDB (particularly by Mark Callaghan and his team mates at Google/Facebook), which were incorporated in the InnoDB plugin in MySQL 5.1 (and hence got included in the InnoDB version of MySQL 5.5, too).

Here they are, in no particular order:

• BUG#13175: SHA2 function (Patch contributed by Bill Karwin)
• BUG#14104: FLUSH LOGS now takes an optional log_type value so that FLUSH log_type LOGS can be used to flush only a specified log type. (Patch contributed by Eric Bergen)
• BUG#27249: Aliases for wildcards (as in SELECT t.* AS 'alias' FROM t) are no longer accepted and result in an error. Previously, such aliases were ignored silently. (Patch contributed by Martin Friebe)
• BUG#40368: mysqld_safe did not honor underscores in the same way as dashes for server options (Patch contributed by Erik Ljungstrom)
• BUG#45767: Removal of Field::pack_key, Field::unpack_key, Field::pack_cmp storage engine functions (Patch contributed by Zardosht Kasheff)
• BUG#50057: SHOW PROFILE CPU port for Windows (Patch contributed by Alex Budkovski)
• BUG#5724: "mysqladmin password" prompts for a password when none is provided on the command line (Patch contributed by Harrison Fisk)
• BUG#26780: A new mysql client option "--auto-vertical-output", which causes the client to test whether a result table is too wide for the current window (where available) and emit vertical results in that case. (Patch contributed by Eric Bergen)

Everybody knows that parsing the output of SHOW ENGINE INNODB STATUS is hard, especially when you want to track the information historically, or want to aggregate any of the more dynamic sections such as the TRANSACTIONS one.

Within the InnoDB plugin the INFORMATION_SCHEMA.INNODB_TRX table was added, which allowed you to at least get some of the information on each transaction, but not the full breadth of of information that SHOW ENGINE INNODB STATUS provided.

“This is nice..” I thought “..but why not go the whole hog..?”.. And so I set about doing that, and opened up Bug#53336. In a very short time, I was in a review process with the InnoDB team, the patch was cleaned up, and (after a little hiccup) everything has been pushed for the next milestone.

Here’s the docs notes that I wrote for it today (with a little more annotation):

This patch adds the following columns:

mysql> DESC innodb_trx;
+----------------------------+---------------------+------+-----+---------------------+-------+
| FIELD                      | Type                | NULL | KEY | DEFAULT             | Extra |
+----------------------------+---------------------+------+-----+---------------------+-------+
...
| trx_operation_state        | varchar(64)         | YES  |     | NULL                |       |
| trx_tables_in_use          | bigint(21) UNSIGNED | NO   |     | 0                   |       |
| trx_tables_locked          | bigint(21) UNSIGNED | NO   |     | 0                   |       |
| trx_lock_structs           | bigint(21) UNSIGNED | NO   |     | 0                   |       |
| trx_lock_memory_bytes      | bigint(21) UNSIGNED | NO   |     | 0                   |       |
| trx_rows_locked            | bigint(21) UNSIGNED | NO   |     | 0                   |       |
| trx_rows_modified          | bigint(21) UNSIGNED | NO   |     | 0                   |       |
| trx_concurrency_tickets    | bigint(21) UNSIGNED | NO   |     | 0                   |       |
| trx_isolation_level        | varchar(16)         | NO   |     |                     |       |
| trx_unique_checks          | int(1)              | NO   |     | 0                   |       |
| trx_foreign_key_checks     | int(1)              | NO   |     | 0                   |       |
| trx_last_foreign_key_error | varchar(256)        | YES  |     | NULL                |       |
| trx_adaptive_hash_latched  | int(1)              | NO   |     | 0                   |       |
| trx_adaptive_hash_timeout  | bigint(21) UNSIGNED | NO   |     | 0                   |       |
+----------------------------+---------------------+------+-----+---------------------+-------+
22 rows IN SET (0.05 sec)

Most of these are self explanatory, a lot of them duplicate the information within SHOW ENGINE INNODB STATUS (so you now no longer need to parse the output of that to get complete transaction information).

Below are some of my notes. For comparison, here’s an example of a transaction from the SHOW ENGINE INNODB STATUS statement:

---TRANSACTION 517, ACTIVE 1 sec, OS thread id 2958520320 inserting
mysql TABLES IN USE 2, locked 2
189 LOCK struct(s), heap size 27968, 54389 row LOCK(s), undo log entries 2406
MySQL thread id 2, query id 36 localhost root Sending DATA
INSERT IGNORE INTO t1 (SELECT (i * rand())*100, 1 FROM t1)

trx_operation_state - corresponds to “inserting”, InnoDB’s internal transaction state
trx_tables_in_use - corresponds to “mysql tables in use 2″, the number of table locks requested by MySQL via external_lock()
trx_tables_locked - corresponds to “locked 2″, the number of actual table locks taken via external_lock()
trx_lock_structs - corresponds to “189 lock struct(s)”, the size of the lock struct list
trx_lock_memory_bytes - corresponds to “heap size 27968″, the number of bytes allocated to locks structs
trx_rows_locked - corresponds to “54389 row lock(s)”, an estimation of the number of rows locked (delete marked rows may make it imprecise)
trx_rows_modified - corresponds to “undo log entries 2406″, the number of rows modified in the transaction (inserted, updated, deleted)

Not seen in the transaction output above:

trx_concurrency_tickets - corresponds to “thread declared inside InnoDB 89″ for a transaction in SHOW ENGINE INNODB STATUS, the number of concurrency tickets remaining for the transaction when innodb_thread_concurrency != 0
trx_isolation_level - the transactions isolation level
trx_unique_checks - whether the transaction has “SET UNIQUE_CHECKS = 0″
trx_foreign_key_checks - whether the transaction has “SET FOREIGN_KEY_CHECKS = 0″
trx_last_foreign_key_error - if the last statement in the transaction resulted in an FK error, the error text is printed here
trx_adaptive_hash_latched - corresponds to “holds adaptive hash latch” being printed for a transaction in SHOW ENGINE INNODB STATUS
trx_adaptive_hash_timeout - when innodb_adapative_hash_index is enabled (default), statements that try to get the adapative hash latch spin 10000 (BTR_SEA_TIMEOUT) times, re-trying getting the adaptive hash latch, before giving up. lower numbers here for a lot of transactions may indicate contention on the adaptive hash latch

In summary - no more having to parse SHOW ENGINE INNODB STATUS output for transaction information, now you can just do it with a SQL (with all it’s aggregation goodness as well if you want to)!

So after I published my patch last night, another of my colleagues - the esteemed Shane Bester - pointed out that there is a related bug - Bug#28405 - which requests that Com_change_user is also split out from Com_admin_commands.

So I extended my patch this morning, to kill two birds with one stone:

=== modified file 'sql/mysqld.cc'
--- sql/mysqld.cc       revid:alik@sun.com-20100114090008-3rsdmlp1w2mqgrhg
+++ sql/mysqld.cc       2010-03-03 09:57:40 +0000
@@ -3131,6 +3131,7 @@
   {"call_procedure",       (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_CALL]), SHOW_LONG_STATUS},
   {"change_db",            (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_CHANGE_DB]), SHOW_LONG_STATUS},
   {"change_master",        (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_CHANGE_MASTER]), SHOW_LONG_STATUS},
+  {"change_user",          (char*) offsetof(STATUS_VAR, com_change_user), SHOW_LONG_STATUS},
   {"check",                (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_CHECK]), SHOW_LONG_STATUS},
   {"checksum",             (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_CHECKSUM]), SHOW_LONG_STATUS},
   {"commit",               (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_COMMIT]), SHOW_LONG_STATUS},
@@ -3174,6 +3175,7 @@
   {"load",                 (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_LOAD]), SHOW_LONG_STATUS},
   {"lock_tables",          (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_LOCK_TABLES]), SHOW_LONG_STATUS},
   {"optimize",             (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_OPTIMIZE]), SHOW_LONG_STATUS},
+  {"ping",                 (char*) offsetof(STATUS_VAR, com_ping), SHOW_LONG_STATUS},
   {"preload_keys",         (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_PRELOAD_KEYS]), SHOW_LONG_STATUS},
   {"prepare_sql",          (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_PREPARE]), SHOW_LONG_STATUS},
   {"purge",                (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_PURGE]), SHOW_LONG_STATUS},
@@ -3350,11 +3352,13 @@
     We have few debug-only commands in com_status_vars, only visible in debug
     builds. for simplicity we enable the assert only in debug builds
 
-    There are 8 Com_ variables which don't have corresponding SQLCOM_ values:
+    There are 10 Com_ variables which don't have corresponding SQLCOM_ values:
     (TODO strictly speaking they shouldn't be here, should not have Com_ prefix
     that is. Perhaps Stmt_ ? Comstmt_ ? Prepstmt_ ?)
 
       Com_admin_commands       => com_other
+      Com_change_user          => com_change_user
+      Com_ping                 => com_ping
       Com_stmt_close           => com_stmt_close
       Com_stmt_execute         => com_stmt_execute
       Com_stmt_fetch           => com_stmt_fetch
@@ -3368,7 +3372,7 @@
     of SQLCOM_ constants.
   */
   compile_time_assert(sizeof(com_status_vars)/sizeof(com_status_vars[0]) - 1 ==
-                     SQLCOM_END + 8);
+                     SQLCOM_END + 10);
 #endif
 
   if (get_options(&remaining_argc, &remaining_argv))
 
=== modified file 'sql/sql_class.h'
--- sql/sql_class.h     revid:alik@sun.com-20100114090008-3rsdmlp1w2mqgrhg
+++ sql/sql_class.h     2010-03-03 09:56:18 +0000
@@ -443,6 +443,8 @@
   ulong ha_discover_count;
   ulong ha_savepoint_count;
   ulong ha_savepoint_rollback_count;
+  ulong com_ping;
+  ulong com_change_user;
 
   /* KEY_CACHE parts. These are copies of the original */
   ulong key_blocks_changed;
 
=== modified file 'sql/sql_parse.cc'
--- sql/sql_parse.cc    revid:alik@sun.com-20100114090008-3rsdmlp1w2mqgrhg
+++ sql/sql_parse.cc    2010-03-03 09:56:19 +0000
@@ -979,7 +979,7 @@
 #endif
   case COM_CHANGE_USER:
   {
-    status_var_increment(thd->status_var.com_other);
+    status_var_increment(thd->status_var.com_change_user);
     char *user= (char*) packet, *packet_end= packet + packet_length;
     /* Safe because there is always a trailing \0 at the end of the packet */
     char *passwd= strend(user)+1;
@@ -1409,7 +1409,7 @@
     break;
   }
   case COM_PING:
-    status_var_increment(thd->status_var.com_other);
+    status_var_increment(thd->status_var.com_ping);
     my_ok(thd);                                // Tell client we are alive
     break;
   case COM_PROCESS_INFO:

Again, a quick test script:

< ?php
 
$conn = mysqli_connect('127.0.0.1','root','msandbox', 'test', 5550);
 
if (!mysqli_change_user($conn, 'root', 'msandbox', 'mysql')) {
   echo "Change user failed!\n";
} else {
   echo "User changed!\n";
}
 
?>

And test:

Cerberus:msb_5_5_5 mark$ mysql -u root -pmsandbox -h 127.0.0.1 -P5550 -e "show global status like 'com_change_user'"
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| Com_change_user | 0     |
+-----------------+-------+
Cerberus:msb_5_5_5 mark$ php ~/Dev/tests/com_change_user.php
User changed!
Cerberus:msb_5_5_5 mark$ mysql -u root -pmsandbox -h 127.0.0.1 -P5550 -e "show global status like 'com_change_user'"
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| Com_change_user | 1     |
+-----------------+-------+
Cerberus:msb_5_5_5 mark$ php ~/Dev/tests/com_change_user.php
User changed!
Cerberus:msb_5_5_5 mark$ mysql -u root -pmsandbox -h 127.0.0.1 -P5550 -e "show global status like 'com_change_user'"
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| Com_change_user | 2     |
+-----------------+-------+

Yet Another Disclaimer: Not sure if/when/where this might get in! It is a change in behavior with regards to Com_admin_commands

Kristian Nielsen presented “Beyond MySQL GA: patches, storage engines, forks, and pre-releases”.
This included a history of current products:

Google Patches (5.0 & 5.1) included improvements in :

• statistics/monitoring
• lock contention
• binlog
• malloc()
• filesorts
• innodb I/O and wait statistics
• SHOW …STATISTICS statements
• smp scalability
• I/O scalability
• semisync replication
• many more

Percona Patches (5.0) focus on

• statistics/monitoring
• performance/scalability
• buffer pool content/mutexes
• microslow patch

These have been ported to 5.1 and mainly integrated into XtraDB.

EBay Patches (5.0) have included:

• variable length memory storage engine
• pool of threads
• Virtual columns

XtraDB storage engine (5.1) includes

• Percona patches
• Google patches
• Innodb patches
• Has XtraBackup for backup

Other engines/patches discussed included:

• PBXT storage engine – community contribution
• FederatedX – replacement to Federated
• Sphinx storage engine
• Pinba storage engine – Collects PHP statistics
• Others OQGraph/Spider
• Galera – Synchronous replication
• Drizzle

Alternative packaging options for MySQL 5.0 and MySQL 5.1 including Our Delta, Percona and MariaDB.

FOSDEM 2010 MySQL Developer Room Schedule
FOSDEM 2010 Website
Brussels, Belgium
February 7, 2010

Today, the InnoDB team announced the latest release of the InnoDB Plugin, release 1.0.4. Some of the performance gains in this release are quite remarkable!

As noted in the announcement, this release contains contributions from Sun Microsystems, Google and Percona, Inc., for which we are very appreciative. This page briefly describes each of the contributions and the way we treated them. The purpose of this post is to describe the general approach the InnoDB team takes toward third party contributions.

In principle, we appreciate third party contributions. However, we simply don’t have the resources to seriously evaluate every change that someone proposes, but when we do undertake to evaluate a patch, we have some clear criteria in mind:

• The patch has to be technically sound, reliable, and effective
• The change should fit with the architecture, and our overall plans and philosophy for InnoDB
• The contribution must be available to us under a suitable license

Let’s consider, in general terms, what these criteria mean in practice.

We have to expend a fair bit of effort to carefully evaluate and possibly modify a patch before we can include it in the release. Some of the third party contributions we’ve seen have not been portable, or have been developed just for Linux. It can take time to find an approach that enables a platform to take advantage of a new feature, even if the platform has the required capabililties. Some of the patches we’ve evaluated have contained actual bugs that would impact reliability, cause deadlocks or have other negative implications. InnoDB is a clean and elegant piece of code, yet some of its internal algorithms and behaviors are subtle and complex. Therefore, changes in the “guts” of InnoDB (or any storage engine) must be done carefully and thoroughly tested. Some patches that have been offered make a difference, but only when compared to an inappropriate “baseline”. At any given point, we would look to include a patch only if it makes a significant improvement over the “best” version or configuration of InnoDB available at the time. We like to test each patch in isolation, to assess its individual value. This requires some rigorous performance testing, with multiple workloads.

From time to time, third parties have made suggestions for changes that may seem attractive at first, but don’t make sense longer term. In general, we may have a more comprehensive approach to a problem or requirement that we would like to implement, rather than incorporate a patch that would introduce a feature that would ultimately be made obsolete. We prefer to have fewer “knobs” and tuning complexity, so we’re more inclined to implement heuristic, self-tuning capabilities than we are to add new configuration parameters. Lastly, we take care to protect the ability to upgrade and downgrade user databases with the file format management features in the InnoDB Plugin. If a patch requires an on-disk change, we will defer its incorporation until the time comes to implement a new file format.

For us to be able to make continued investment in InnoDB, we must be able to license the software commercially. OEMs and ISVs who incorporate MySQL with InnoDB in their products may not wish to release their products in open source form. Therefore, for each contribution we are to accept, we must have clear legal rights to the change.

Beyond all that, of course, we take care to carefully document each new feature, both in terms of form and function. We try hard to explain the implications of a feature, providing information about what it does, and when and where to use a feature, as well as how to do so. And, we generally speaking are committed to upward compatibility and support of a feature once it is introduced.

It’s pretty clear that the integrity of InnoDB, with its broad adoption and importance everywhere it is used, is paramount to you and to us. You can trust the InnoDB team to protect InnoDB now and in the future, while being open to suggestions and contributions. Let us know if you think we’re doing a good job!