Bug #73056 | Prepared Statements ABSURDLY SLOW due to inefficient query creation for logging | ||
---|---|---|---|
Submitted: | 19 Jun 2014 21:34 | Modified: | 11 Mar 2016 14:52 |
Reporter: | Seth Willits | Email Updates: | |
Status: | Closed | Impact on me: | |
Category: | MySQL Server: Prepared statements | Severity: | S5 (Performance) |
Version: | 5.7, 5.7.8 | OS: | Any |
Assigned to: | CPU Architecture: | Any | |
Tags: | logging, prepared, query, slow, statement |
[19 Jun 2014 21:34]
Seth Willits
[11 Aug 2014 10:02]
MySQL Verification Team
Hello Seth, Thank you for the report. I couldn't repeat this with dummy data on my testing box. Could you please provide complete repeatable test case(schema, scripts, etc), and conf file used during tests? Thanks, Umesh
[11 Aug 2014 17:35]
Seth Willits
I don't have any particular data and code lying around that I can share. The content of the data really doesn't matter. What matters are two things: 1) One or more of the binlog, general log, or slow log must be enabled. (See Prepared_statement::setup_set_params() for the path that will use insert_params_with_log). 2) The prepared statement's query must have a lot of parameters: eg, close to the 64K limit. Doing that will cause the full query to be generated via 64K inefficient String::replace() calls to fill in the parameters. Obviously the longer the parameter data the longer this replacement will take and the inefficiency will be magnified.
[27 Sep 2014 13:26]
MySQL Verification Team
related: https://bugs.mysql.com/bug.php?id=67676 Maybe that testcase can be used here ?
[24 Apr 2015 12:23]
MySQL Verification Team
Thank you Shane, Seth. Observed similar behavior with 5.7.8 with test case from #67676 Thanks, Umesh
[24 Apr 2015 12:27]
MySQL Verification Team
test results
Attachment: 73056.txt (text/plain), 16.70 KiB.
[7 May 2015 9:57]
Tatjana Nuernberg
There is some degree of improvement for the assembly of the log string (i.e. calculate total size required, allocate, fill in, rather than do it piecemeal). That said, as implicated by the profiling, bmove_upp() performed horribly on modern CPUs; it has however been fixed in Bug#16839824 changeset a/o 2013-06-14, so I'm a bit non-plussed about it showing up in a relatively recent profiling? Does this problem persist (to a similar extent) with a current server?
[7 May 2015 9:58]
Tatjana Nuernberg
Should read, "room for improvement," not "degree ..."
[7 May 2015 18:44]
Seth Willits
Unless I'm mistaken, 16839824 was fixed in 5.7.2 and Umesh confirmed this in 5.7.8, so the issue seems perfectly relevant still. Changing the algorithm itself would have a significant positive improvement on performance, so I don't see why it shouldn't happen. It's practically changing it from O(n^2) copies to O(n)... I'd call that a large degree of improvement.
[11 Mar 2016 14:52]
Paul DuBois
Noted in 5.8.0 changelog. Executed prepared statements are logged with ? parameter markers replaced by data values. Construction of the logged string was inefficient and has been improved.