MySQL Forums
Forum List  »  Performance

events_statements_summary_by_digest.DIGEST_TEXT includes parse_gcol_expr for slow INSERTs
Posted by: Lee Marshall
Date: October 09, 2020 05:17PM

I periodically monitor how my application is using the database with a query on events_statements_summary_by_digest taking the top 20 SUM_TIMER_WAIT.

Lately, I'm seeing two distinct entries for inserts into my most active, largest table, both in the top 5 operations. The slow queries have some extra text in DIGEST_TEXT that I'm not familiar with.

slow:
average wait: 71 sec!
INSERT INTO EVENTS ( `deviceID` , DESCRIPTION , VALUE , `tstamp` , `controllerID` , `SR2time` , `value_retrieved` , `junk` ) VALUES (...) (unknown) `parse_gcol_expr` (?)

fast:
average wait: 8 msec
runs 1000x more often. Thank goodness it's not the other way around!
INSERT INTO EVENTS ( `deviceID` , DESCRIPTION , VALUE , `tstamp` , `controllerID` , `SR2time` , `value_retrieved` , `junk` ) VALUES (...)

The actual insert statements look identical modulo the actual values supplied. After I remove sensitive information, they are identical!

insert into events (deviceID, description, value, tstamp, controllerID, SR2time, value_retrieved, junk) values (sensitive-int, 'sensitive', 'sensitive', 'sensitive-date', sensitive-int, 'sensitive-date', '0000-00-00 00:00:00', 0);


I've got no generated columns. I do have these interesting columns:
`last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`junk` tinyint(1) DEFAULT '0',
`source` tinyint NOT NULL DEFAULT (0),

MySQL Workbench shows (but won't let me copy/paste!) `source` as "DEFAULT_GENERATED" but not `junk`. So I suppose we've accidentally given it an expression rather than a value. It's a new column, perhaps newer than this problem.
last_update also shows up as "DEFAULT_GENERATED".

Questions:
1) What's this business about "(unknown) `parse_gcol_expr` (?)"?
--I've done some searches for "parse_gcol_expr" and come up with slim pickings in the direction of generated columns
2) What tree should I be barking up?

Options: ReplyQuote


Subject
Views
Written By
Posted
events_statements_summary_by_digest.DIGEST_TEXT includes parse_gcol_expr for slow INSERTs
183
October 09, 2020 05:17PM


Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.