Bug #24305 Bulk INSERT statements shows up as a single INSERT in database activity
Submitted: 14 Nov 2006 16:27 Modified: 30 May 2007 14:02
Reporter: Anders Karlsson Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Enterprise Monitor: Server Severity:S3 (Non-critical)
Version:0.7.37 OS:Windows (Windows / Linux)
Assigned to: Mark Leith CPU Architecture:Any
Tags: mer 111

[14 Nov 2006 16:27] Anders Karlsson
Description:
Bulk INSERT statements, i.e. INSERT INTO ... VALUES(<values>),(<values>)...
shows up as a single INSERT in the "database activity" graph. My beta customers finds this really annoying, as it means that, in his application which does a lot of bulk inserts, the "database activity" chart shows little relevance to the actual database activity in his system.

How to repeat:
In the monitored MySQL Server, run a bunch of bulk INSERT statements. You will see the INSERT database activity grow with as many statements you issues, not with the number of rows.
[16 Nov 2006 2:03] Andy Bang
Email sent as follows:

Mark or Anders,

Is there *any* way for us to capture this activity?  Anders' comment suggests it's a server limitation.  Seems to me that our 2 options are:

1) Find some other Com_xxx or other status variable that captures this activity so we can include it in Merlin; or

2) Hand this over to the database server team for their consideration.

Comments?

Thanks,

Andy
[16 Nov 2006 14:47] Mark Leith
Indeed this is a limitation with the "Com_%" variables, and I don't think there is anything that we could do with the current graphs as we have them now to fix this. 

I *could* suggest an alternate way to do this however, basing another (new graph, in a new graphs window, when we get this functionality), based upon the "Handler_%" variables rather than the "Com_%" variables, as the Handler variables are based on rows rather than statements. 

Here's a quick illustration:

mysql> create table t1 (i int);
Query OK, 0 rows affected (0.05 sec)

mysql> FLUSH STATUS;
Query OK, 0 rows affected (0.00 sec)

mysql> show session status like 'handler_write';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Handler_write | 0     |
+---------------+-------+
1 row in set (0.00 sec)

mysql> show session status like 'handler_write';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Handler_write | 1     |
+---------------+-------+
1 row in set (0.00 sec)

mysql> show session status like 'handler_write';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Handler_write | 2     |
+---------------+-------+
1 row in set (0.00 sec)

mysql> show session status like 'handler_write';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Handler_write | 3     |
+---------------+-------+
1 row in set (0.00 sec)

/* note that just the SHOW statement is incrementing this value (as the temporary table is created in background, so it is important to use the LIKE to make the temporary table as small as possible */

mysql> INSERT INTO t1 VALUES (1);
Query OK, 1 row affected (0.00 sec)

mysql> show session status like 'handler_write';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Handler_write | 5     |
+---------------+-------+
1 row in set (0.00 sec)

/* now it incremented by 2 - one for the SHOW, one for the row inserted */

mysql> INSERT INTO t1 VALUES (1);
Query OK, 1 row affected (0.00 sec)

mysql> show session status like 'handler_write';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Handler_write | 7     |
+---------------+-------+
1 row in set (0.00 sec)

/* 2 again */

mysql> INSERT INTO t1 VALUES (1),(2),(3),(4),(5),(6),(7);
Query OK, 7 rows affected (0.00 sec)
Records: 7  Duplicates: 0  Warnings: 0

mysql> show session status like 'handler_write';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Handler_write | 15    |
+---------------+-------+
1 row in set (0.00 sec)

/* but now it incremented by 8 - the 7 rows inserted, and the 1 for doing the SHOW statement */

This still doesn't fix the "feature" of SHOW statements affecting these stats, but gives more indepth info in to numbers of rows inserted.

Cheers

Mark
[28 Mar 2007 19:39] Mark Leith
Reopening, assigning to myself, will create a new graph for row activity based on my last comments
[4 May 2007 19:13] Mark Leith
Committed within revision 5388 and 5389 to the advisors branch. Merging to 1.1.1.

Added 'Rows Inserted', 'Rows Updated' and 'Rows Deleted' to the 'Row Accesses' graph. Renamed this graph to 'Row Activity'.

Setting to QA Testing.
[22 May 2007 21:27] Bill Weber
Verified added 'Rows Inserted', 'Rows Updated' and 'Rows Deleted' to the 'Row Accesses' and renamed this graph to 'Row Activity' in build 1.1.1.5646 advisor jar.
[23 May 2007 17:39] Bill Weber
New issue: Row Activity graph is not available when viewing multiple servers (group).
[23 May 2007 19:39] Mark Leith
- Last commit caused a problem because 6 points/legends on the graph causes 18 within aggregated graphs - and we do not have 18 distinct colours available 
- Removed additions to the 'Row Accesses' graph 
- Moved the additions to an additional graph called 'Row Writes'
- We therefore have one graph for rows read in various ways, and one for INSERT/UPDATE/DELETE rows

Committed to trunk within r5709, committed to 1.1.1 branch within r5710.
[28 May 2007 21:35] Bill Weber
Verified that there is now a Row Writes graph in the 1.1.1.5725 advisor jar.
[30 May 2007 14:02] Peter Lavin
Added to the changelog.