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: | |
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
[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.