Mark Leith

Improving InnoDB Transaction Reporting

Mark Leith — Wed, 18 Aug 2010 18:06:25 +0000

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 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 SQL (with all it’s aggregation goodness as well if you want to)!

Starting a new job!

Mark Leith — Wed, 05 May 2010 21:01:43 +0000

I’ve had a wild ride over the past ~4.5 years, starting with MySQL AB as a “Support Engineer”, and working through to “Senior Support Engineer”, and then “Regional Support Manager, Americas” with the MySQL Support Team - truly one of the best product support teams I’ve ever known in the IT industry, even if I am biased.

I’ve always had a passion for helping people, which is why I think I did “OK” in Support. However I’ve always also had a second passion which has been bubbling away for me too - building solutions for diagnosing database issues. I started in the database world in the Oracle market, working on monitoring and management tools. MySQL “poached” me from there whilst I was building a MySQL monitoring module for the cross database monitoring tool that we had, as well as working in a supporting/consulting role for our customers.

Given my background when I joined, I was an obvious person to be put in to an internal coordinator role to manage interaction between the team that develops MySQL Enterprise Monitor and Support back then (before MEM reached it’s first GA), so I also had the pleasure of working with that team pretty closely for my time in Support.

It satisfied my second passion for a long time. Now it’s time to take the leap back to that full time!

At the start of this week I started in my new role, as a “Software Development Manager” for the Enterprise Tools team, still within MySQL/Oracle, working full time on MySQL Enterprise Monitor and MySQL Proxy.

I’m pretty excited about the new role - I want to help make monitoring and management of MySQL easy, both by getting more of the “right” data from the server, and by representing it all in coherent ways for DBAs in our tools, so that they too can be more productive in making the right choices when managing their systems. I feel pretty well placed to do that now.

If you’re a customer that has used MEM and wants to give any feedback, or even just an interested user in the community - I’m all ears, either leave your comments here, or ping me by email (first_name.last_name@oracle.com) - I’d love to have some discussions with you about your needs, likes, or dislikes.

I thank every person in Support for my time with them, however I owe all of that time to one fairly anonymous guy in the “external” MySQL world, but a man mountain “internally” - Dean Ellis, the man who convinced me to join MySQL AB all those years ago, and has been a fantastic mentor and manager over the years to me. Mr Ellis - I salute you. Thanks for bringing me on the ride, I still don’t regret it, no matter how much you tried to make me!

Enterprise Tools team - Look Out, you have me to whine at you full time now!

A morning hack - Com_change_user

Mark Leith — Wed, 03 Mar 2010 11:06:56 +0000

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

An evening hack - Com_ping

Mark Leith — Wed, 03 Mar 2010 00:06:16 +0000

My boss vented about the lack of a Com_ping SHOW GLOBAL STATUS variable earlier, and I figured it would be dead easy to hack in.

A few minutes later:

Cerberus:mysql-next-mr mark$ bzr diff ./sql
=== modified file 'sql/mysqld.cc'
--- sql/mysqld.cc       revid:alik@sun.com-20100114090008-3rsdmlp1w2mqgrhg
+++ sql/mysqld.cc       2010-03-02 22:58:45 +0000
@@ -3174,6 +3174,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 +3351,12 @@
     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 9 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_ping                 => com_ping
       Com_stmt_close           => com_stmt_close
       Com_stmt_execute         => com_stmt_execute
       Com_stmt_fetch           => com_stmt_fetch
@@ -3368,7 +3370,7 @@
     of SQLCOM_ constants.
   */
   compile_time_assert(sizeof(com_status_vars)/sizeof(com_status_vars[0]) - 1 ==
-                     SQLCOM_END + 8);
+                     SQLCOM_END + 9);
 #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-02 22:56:05 +0000
@@ -443,6 +443,7 @@
   ulong ha_discover_count;
   ulong ha_savepoint_count;
   ulong ha_savepoint_rollback_count;
+  ulong com_ping;
 
   /* 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-02 23:23:41 +0000
@@ -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:

COM_PING has always historically been recorded under Com_admin_commands (which is tracked with com_other), however this also includes a number of other commands:

COM_TABLE_DUMP
COM_CHANGE_USER
COM_BINLOG_DUMP
COM_SHUTDOWN
COM_DEBUG

COM_CHANGE_USER can also be used frequently with connection pools, as can COM_PING. It would be nice to differentiate them when tracking down issues within such environments.

Here’s the bug that was opened:

http://bugs.mysql.com/bug.php?id=51667

And a quick test:

Cerberus:msb_5_5_5 mark$ mysql -u root -pmsandbox -h 127.0.0.1 -P 5550 -e "show global status like 'com_ping'"
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Com_ping      | 0     |
+---------------+-------+
Cerberus:msb_5_5_5 mark$ php ~/Dev/tests/com_ping.php
Connected!
Cerberus:msb_5_5_5 mark$ mysql -u root -pmsandbox -h 127.0.0.1 -P 5550 -e "show global status like 'com_ping'"
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Com_ping      | 1     |
+---------------+-------+
Cerberus:msb_5_5_5 mark$ php ~/Dev/tests/com_ping.php
Connected!
Cerberus:msb_5_5_5 mark$ mysql -u root -pmsandbox -h 127.0.0.1 -P 5550 -e "show global status like 'com_ping'"
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Com_ping      | 2     |
+---------------+-------+

The test script:

< ?php
 
$conn = mysql_connect('127.0.0.1:5550','root','msandbox');
mysql_select_db('test',$conn);
 
if (!mysql_ping ($conn)) {
   echo "Connection Dead!\n";
} else {
   echo "Connected!\n";
}
 
?>

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

PERFORMANCE_SCHEMA hits Prime Time!

Mark Leith — Thu, 14 Jan 2010 20:21:43 +0000

I’ve talked about PERFORMANCE_SCHEMA in the past - almost a year ago now.

Back then the feature was just coming together in to something useable, and was on the cusp of moving towards code review. It entered code review, and went around, and around for 9 months, whilst various refinements were made.

Never the less, Marc Alff persevered (much respect!), and yesterday pushed his final merge in to the mysql-next-mr bzr tree. PERFORMANCE_SCHEMA is now awaiting the next milestone release, and will be a part of the next GA release of MySQL!

We have the initial documentation ready:

http://dev.mysql.com/doc/performance-schema/en/index.html

This first round adds the infrastructure to take monitoring of the MySQL Server to the next level, initially adding in instrumentation for sync points (mutexes, rw locks, etc.) and file IO, in the SQL layer, and most of the default storage engines (all those controlled by MySQL/Sun).

Here’s a shout out to the other storage engine developers - we’d love you to start looking at instrumenting your own engines as well. Ask away on the internals@ list - I’m sure Marc will be more than willing to help.

And now that we have the above in - what’s next? It’s a good question! Here’s a list of the major outstanding worklogs:

WL#4674 PERFORMANCE_SCHEMA Setup For Actors
WL#4895 PERFORMANCE_SCHEMA Instrumenting Table IO
WL#4896 PERFORMANCE_SCHEMA Instrumenting Net IO
WL#4878 PERFORMANCE_SCHEMA Trace

Personally, I’m voting for WL#4895 next (well, I’d like to see InnoDB instrumentation too!), but you can vote for what ever you are most interested in via the votes in Worklog as well.

Thanks for all the hard work Marc!

Scientists say dolphins should be treated as ‘non-human persons’

Mark Leith — Mon, 04 Jan 2010 21:32:30 +0000

Being an employee of MySQL (past and present), every time I see something related to dolphins it catches my eye - and I just came across this:

Scientists say dolphins should be treated as ‘non-human persons’

The treatment of dolphins has long been an issue for me, I utterly despise some of the things I have seen, and read about. MySQL’s dolphin - Sakila - has always been seen as a symbol of freedom. This is marred by reality, hopefully this will change:

“The researchers argue that their work shows it is morally unacceptable to keep such intelligent animals in amusement parks or to kill them for food or by accident when fishing. Some 300,000 whales, dolphins and porpoises die in this way each year.”

Some other things in the article also ring true for me, in other ways.

“The studies show how dolphins have distinct personalities, a strong sense of self and can think about the future.”

“It has also become clear that they are “cultural” animals, meaning that new types of behaviour can quickly be picked up by one dolphin from another.”

“Other research has shown dolphins can solve difficult problems, while those living in the wild co-operate in ways that imply complex social structures and a high level of emotional sophistication.”

FWIW, I’ve donated to both http://www.orcaweb.org.uk/ and http://www.wdcs.org/ in the past, they are both wonderfully worthy causes.

Grouping by Arbitrary Time Ranges (Graphing What You Can See)

Mark Leith — Fri, 30 Oct 2009 17:17:56 +0000

First, the back story. One of the MEM developers asked me today about an interesting problem:

We have graphs in MEM that can be generated against an arbitrary time range in the UI - even for a year or more if the data is not purged. Currently MEM does not do any kind of rolling up of the data (in an RRD style), and pulls graph data from each agent/instance on a 1 minute interval. So if you wanted to pull, for instance, the last 3 months worth of data in to a graph - the server back end basically goes back to the database and requests all of the rows - all ~43,829 of them, oh, and that’s for each series - and then calculate deltas on the server side if need be (we store raw values), and stuffs the data in to a graphing library to draw the graph.

Further, graphs are only of a limited (but adjustable) size on the screen - I run MEM with a 900 pixel wide graph personally - so there’s only ~900 points (maybe a few less, with the axis etc. being shown) that can be filled with data.

Trying to fill 900 points with ~43,000 points is an exercise in futility. Damn those pixels. Why doesn’t everybody have a cinema in their ops centers? Filling up your memory with all that data on the app server side is also another issue.

So we’ve been looking at various ways of a) rolling this data in storage, and b) only selecting what can be shown on the screen if possible. The MEM dev hit me up about the latter today, and basically asked - “Given a time range, and a number of pixels, how we can we group a set of rows together to get an aggregated result that only returns the same number of rows as we have pixels?”

Math to the rescue!

Luckily we store the timestamps for the data we collect in a BIGINT column - we store a unix style timestamp with millisecond resolution times. If you’re not doing this, then you should convert your DATETIME / TIMESTAMP etc. to an integer (with UNIX_TIMESTAMP) first.

So I started off with the three known constants:

SET @start_time = 1254325343000; // About a month ago
SET @end_time = 1256908959000; // About now
SET @pixels = 900;

Now, to get the interval that you need to group, you have to first get the full integer range you are dealing with, and then divide it by the number of pixels:

SET @intervals = ((@end_time - @start_time) / @pixels);

Now that we know the interval size that we are going to GROUP together, how do we group it? Math to the rescue again - to generate the time slices you round the whole timestamp number to the nearest interval, and then convert it back again, basically:

FLOOR( (timestamp_col) /  @intervals) *  @intervals

To generate the range, I used a little CONCAT magic (and note that you add one before converting back for the end of the range), and then GROUP BY the output, here’s an example based on the MEM data set - which stores it’s timestamp in “end_time”:

SELECT instance_attribute_id, 
       SUM(value) sum_value,
       MIN(value) min_value,
       MAX(value) max_value,
       AVG(value) avg_value,
       COUNT(value) agg_points,
       CONCAT( ROUND( FLOOR( (end_time) /  @intervals) *  @intervals), ' - ', 
               ROUND( (FLOOR( (end_time) /  @intervals ) + 1) *  @intervals)) AS time_range
  FROM dc_ng_long_now 
 WHERE instance_attribute_id IN (73, 76, 77, 79)  
   AND end_time BETWEEN @start_time AND @end_time
 GROUP BY instance_attribute_id, time_range

The above is what could be used to generate the CPU Usage graph (the instance ID’s are each of the series) in the MEM UI, for a month of data. Some sample output:

+-----------------------+----------------+--------------+--------------+-------------------+------------+-------------------------------+
| instance_attribute_id | sum_value      | min_value    | max_value    | avg_value         | agg_points | time_range                    |
+-----------------------+----------------+--------------+--------------+-------------------+------------+-------------------------------+
|                    73 |   884176993340 | 442088260860 | 442088732480 | 442088496670.0000 |          2 | 1254322602524 - 1254325473209 | 
|                    73 | 21220811440010 | 442089201580 | 442111284920 | 442100238333.5417 |         48 | 1254325473209 - 1254328343893 | 
|                    73 | 21221895300670 | 442111747690 | 442133893930 | 442122818763.9583 |         48 | 1254328343893 - 1254331214578 | 
|                    73 | 21222981255310 | 442134365100 | 442156523250 | 442145442818.9583 |         48 | 1254331214578 - 1254334085262 | 
|                    73 | 20781887896940 | 442156993950 | 442178660650 | 442167827594.4681 |         47 | 1254334085262 - 1254336955947 | 
|                    73 | 21225129132600 | 442179133270 | 442201248030 | 442190190262.5000 |         48 | 1254336955947 - 1254339826631 | 
|                    73 | 21226213026150 | 442201720960 | 442223827720 | 442212771378.1250 |         48 | 1254339826631 - 1254342697316 | 
|                    73 | 21227293251850 | 442224296570 | 442246191720 | 442235276080.2083 |         48 | 1254342697316 - 1254345568000 | 
|                    73 | 21228371431870 | 442246663300 | 442268791290 | 442257738163.9583 |         48 | 1254345568000 - 1254348438684 | 
|                    73 | 21229455531730 | 442269262370 | 442291404770 | 442280323577.7083 |         48 | 1254348438684 - 1254351309369 | 
|                    73 | 20788226878750 | 442291875550 | 442313515300 | 442302699547.8723 |         47 | 1254351309369 - 1254354180053 | 
|                    73 | 21231576621020 | 442313990190 | 442333971060 | 442324512937.9167 |         48 | 1254354180053 - 1254357050738 | 
|                    73 | 21232474653460 | 442334346060 | 442352121880 | 442343221947.0833 |         48 | 1254357050738 - 1254359921422 | 
|                    73 | 21233360433480 | 442352500510 | 442371671120 | 442361675697.5000 |         48 | 1254359921422 - 1254362792107 | 
|                    73 | 21234392588940 | 442372144460 | 442394227610 | 442383178936.2500 |         48 | 1254362792107 - 1254365662791 | 
|                    73 | 21235472883050 | 442394698990 | 442416721710 | 442405685063.5417 |         48 | 1254365662791 - 1254368533476 | 
|                    73 | 21236557207050 | 442417196420 | 442439347780 | 442428275146.8750 |         48 | 1254368533476 - 1254371404160 | 
|                    73 | 20795177199970 | 442439809050 | 442461261420 | 442450578722.7660 |         47 | 1254371404160 - 1254374274844 | 
|                    73 | 21238693926960 | 442461733210 | 442483851610 | 442472790145.0000 |         48 | 1254374274844 - 1254377145529 | 
|                    73 | 21239778454900 | 442484323030 | 442506442450 | 442495384477.0833 |         48 | 1254377145529 - 1254380016213 |

We can see how many rows have actually been aggregated in to the interval too (scroll to the right) - so that we can decide if there were enough intervals during the first row to be a good enough average (sometimes the first and last intervals may not be, and should perhaps be discarded), and the the interval that each row is computed for, i.e “1254325473209 - 1254328343893″

And a little verification:

 
mysql> SELECT count(*) 
    ->   FROM dc_ng_long_now 
    ->  WHERE instance_attribute_id IN (73, 76, 77, 79) 
    ->    AND end_time BETWEEN @start_time AND @end_time;
+----------+
| count(*) |
+----------+
|   172240 | 
+----------+
1 row IN SET (0.12 sec)
 
mysql> SELECT count(*) FROM(
    -> SELECT instance_attribute_id, 
    ->        MIN(value),
    ->        MAX(value),
    ->        AVG(value),
    ->        CONCAT( ROUND(FLOOR( end_time /  @intervals) *  @intervals), ' - ', 
    ->                ROUND(( FLOOR( end_time /  @intervals ) + 1 ) *  @intervals)) AS time_range
    ->   FROM dc_ng_long_now 
    ->  WHERE instance_attribute_id IN (73, 76, 77, 79) 
    ->    AND end_time BETWEEN @start_time AND @end_time
    ->  GROUP BY instance_attribute_id, time_range
    -> ) s1;
+----------+
| count(*) |
+----------+
|     3604 | 
+----------+
1 row IN SET (1.01 sec)

Note, I’m getting 4 sets of 901 rows, in the above case I’m getting roughly a 48 to 1 compression ratio for a month of data - going from 172,240 rows to 3604!.

For MEM, there’s still work to do, but we’re on a roll now! Hopefully graph performance will be greatly improved in coming versions!

MySQL University Session - Customizing MySQL Enterprise Monitor

Mark Leith — Wed, 09 Sep 2009 14:28:24 +0000

Just a quick note to let the masses know that I will be hosting a MySQL University session tomorrow, based on the talk that I gave at the MySQL UC in April - Customizing MySQL Enterprise Monitor.

It will be at 14:00 UTC - so if you are at all interested in MEM, and want to know how to bend it towards your needs, then come along! I’ll see you there.

EDIT: OOOPS, it’s 13:00 UTC

SHOW RELAYLOG EVENTS

Mark Leith — Fri, 26 Jun 2009 11:56:28 +0000

I reported a bug about SHOW BINLOG EVENTS not working with relay logs a couple of years ago - Bug #28777.

It’s now been fixed in MySQL 5.4, by adding a new SHOW statement - SHOW RELAYLOG EVENTS.

The replication team are really hammering through things at the moment - Kudos!

Past Presentations Now Online

Mark Leith — Tue, 16 Jun 2009 10:04:15 +0000

I uploaded all of my past presentations to Slideshare recently, and realized that I hadn’t actually posted some of these on my blog in the past as well.

So I’ve created a new Presentations Page that has all of these together now.

It’s kind of funny to see the “MySQL for Oracle DBAs” presentation again - a lot has changed since 2006!

In any case, enjoy if you haven’t seen them - give them a look over if interested, and feel free to post comments or questions on the page!