| Bug #109439 | Significant performance overhead on MySQL router 8.0.29 and after | ||
|---|---|---|---|
| Submitted: | 20 Dec 2022 15:28 | Modified: | 31 Jan 2023 16:52 |
| Reporter: | Telemisis Support | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Router | Severity: | S2 (Serious) |
| Version: | 8.0.29, 8.0.30, 8.0.31 | OS: | Windows |
| Assigned to: | CPU Architecture: | x86 | |
[20 Dec 2022 17:11]
MySQL Verification Team
Hi, I'm not reproducing this. Have you tried both InnoDB Cluster and Router to be 8.0.31 ? Thanks
[20 Dec 2022 17:13]
Telemisis Support
Hi, Yes any version of MySQL Router after 8.0.28 has the same issue. Have you got enough test data? regards, James
[20 Dec 2022 17:23]
Telemisis Support
These are the results using the same prepared statement, direct and via MySQL Router 8.0.31 Direct C:\code\prepared_statement_test> node .\prepared_statement.js DB Connect: 15.892ms DB Prepare and Exec: 223.568ms DB Direct Exec: 245.223ms DB Exec Pre-prepared: 184.402ms MySQL Router 8.0.31 C:\code\prepared_statement_test> node .\prepared_statement.js DB Connect: 15.81ms DB Prepare and Exec: 9.639s DB Direct Exec: 235.57ms DB Exec Pre-prepared: 9.774s
[20 Dec 2022 18:24]
MySQL Verification Team
Hi, Two questions 1. are you using SSL (if yes, can you try without)? 2. can you reproduce this without text/blob field? thanks
[20 Dec 2022 18:59]
Telemisis Support
Hello, Disabling SSL by setting the following in the MySQL Router Config makes no difference. client_ssl_mode=DISABLED server_ssl_mode=DISABLED
[20 Dec 2022 19:07]
Telemisis Support
Same issue with varchar(255) and 100,000 rows CREATE TABLE router_test.NewTable_1 ( ID BIGINT auto_increment NOT NULL, `text` varchar(255) NULL, CONSTRAINT NewTable_1_pk PRIMARY KEY (ID) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci; Direct connection of prepared statement C:\code\prepared_statement_test> node .\prepared_statement.js DB Connect: 15.527ms DB Prepare and Exec: 127.767ms Router connection of prepared statement C:\code\prepared_statement_test> node .\prepared_statement.js DB Connect: 16.089ms DB Prepare and Exec: 3.082s
[20 Dec 2022 19:12]
MySQL Verification Team
Hi, Thanks for confirmation and testing. I'll update you after more analysis.
[21 Dec 2022 9:07]
MySQL Verification Team
Hi, Having issues reproducing. Can you please share your full config files. thanks
[21 Dec 2022 10:11]
Telemisis Support
Test config, code, and data
Attachment: prepared_statement_test.zip (application/x-zip-compressed, text), 491.50 KiB.
[22 Dec 2022 13:50]
MySQL Verification Team
I'm not sure if this is the issue with nodejs connector or mysql router. I reproduced the issue with provided data. Thank you for the reproducible test case
[22 Dec 2022 14:05]
Telemisis Support
From the tests I have run it seems to definately be an issue in MySQL Router and behaves the same with any client library. We first found the issue with a C++ application using Connector/C. I'm using node.js here just for ease of reproducing and raising the issue.
[3 Jan 2023 9:23]
Telemisis Support
Are there instructions on compiling MySQL router under windows / visual studio?
[5 Jan 2023 12:40]
Telemisis Support
I've compiled router 8.0.28 and 8.0.29 and run them under the visual studio profiler. There is a significant overhead in performance for all connections on the newer version; not only prepared statements (updated bug report title reflects this). Looking through the code there appears to be a big restructure for "WL#12771 reuse connection". I tested to see if the issue is only on initial connection by sending a second query down and existing connection but unfortunately the issue is still present.
[13 Jan 2023 10:04]
Telemisis Support
Updating affected versions to 8.0.29, 8.0.30, 8.0.31
[27 Jan 2023 13:49]
Jan Kneschke
Posted by developer: Thanks for reporting the test-sample. It helped fixing several performance related issues: - Bug#34977233 - Bug#35006489 - Bug#35011055 - Bug#35019415 - Bug#35019430 With 8.0.32, a microbenchmark showed: [ RUN ] Spec/Benchmark.classic_protocol/many_short_rows name | query | fetch | throughput ------------------ no-ssl | ---------- | ---------- | ----------- DIRECT_DISABLED | 44.16 us | 18.26 ms | 25.53 MB/s DISABLED__DISABLED | 31.96 us | 76.76 ms | 6.07 MB/s DISABLED__REQUIRED | 38.99 us | 87.55 ms | 5.33 MB/s --------------------- ssl | ---------- | ---------- | ----------- DIRECT_PREFERRED | 49.73 us | 25.48 ms | 18.30 MB/s PASSTHROUGH__AS_CLIENT | 47.57 us | 25.83 ms | 18.05 MB/s PREFERRED__DISABLED | 34.63 us | 326.05 ms | 1.43 MB/s PREFERRED__AS_CLIENT | 34.33 us | 327.14 ms | 1.43 MB/s PREFERRED__PREFERRED | 39.36 us | 325.22 ms | 1.43 MB/s [ OK ] Spec/Benchmark.classic_protocol/many_short_rows (1665 ms) With 8.0.33: [ RUN ] Spec/Benchmark.classic_protocol/many_short_rows name | query | fetch | against 8.0.32 ------------------ no-ssl | ---------- | ---------- | ----------- DIRECT_DISABLED | 45.53 us | 18.32 ms | DISABLED__DISABLED | 39.86 us | 38.43 ms | -50% DISABLED__REQUIRED | 35.95 us | 72.68 ms | -17% --------------------- ssl | ---------- | ---------- | ----------- DIRECT_PREFERRED | 46.05 us | 25.61 ms | PASSTHROUGH__AS_CLIENT | 47.52 us | 25.82 ms | PREFERRED__DISABLED | 44.70 us | 39.73 ms | -88% PREFERRED__AS_CLIENT | 43.67 us | 73.99 ms | -78% PREFERRED__PREFERRED | 43.80 us | 74.01 ms | -78% [ OK ] Spec/Benchmark.classic_protocol/many_short_rows (1035 ms)
[27 Jan 2023 14:36]
Telemisis Support
Excellent work; those benchmarks look promising. Is this code available to test?
[31 Jan 2023 16:52]
Edward Gilmore
Posted by developer: Added the following note to the MySQL Router 8.0.33 release notes: Multiple performance improvements were made in connection management, memory management, and statement processing.
Description: Prepared statement execution is severly delayed when calling a prepared statement through MySQL Router >= 8.0.29 There is no issue if either: -Connection is made to database directly OR -MySQL Router <= 8.0.28 is used instead How to repeat: 1. Set up an innoDB Cluster (8.0.31) 2. Set up MySQL Router 8.0.29 3. Generate some data in this case ~20MB of text CREATE DATABASE `router_test` CREATE TABLE `newtable` ( `ID` bigint NOT NULL AUTO_INCREMENT, `text` text, PRIMARY KEY (`ID`) ) ENGINE=InnoDB AUTO_INCREMENT=300959 DEFAULT CHARSET=latin1 STATS_PERSISTENT=1 STATS_AUTO_RECALC=0; 4. Compare the performance of a prepared statement against a direct connection or a verson of MySQL Router <= 8.0.28 const mysql = require('mysql2'); console.time("DB Connect"); const connection = mysql.createConnection({ user: 'root', //port: 7446, database: 'router_test' }); console.timeEnd("DB Connect"); var test_query = 'select ID, text from newtable'; console.time("DB Prepare and Exec"); connection.prepare(test_query, (err, statement) => { if(err) console.log(err); statement.execute([], (err, rows, columns) => { if(err) console.log(err); console.timeEnd("DB Prepare and Exec"); console.time("DB Direct Exec"); connection.query(test_query,[],function(err, results) { if(err) console.log(err); console.timeEnd("DB Direct Exec"); console.time("DB Exec Pre-prepared"); statement.execute([], (err, rows, columns) => { if(err) console.log(err); console.timeEnd("DB Exec Pre-prepared"); statement.close(); process.exit(); }); } ); }); }); Results: Via 8.0.29 C:\code\prepared_statement_test> node .\prepared_statement.js DB Connect: 15.422ms DB Prepare and Exec: 6.340s DB Direct Exec: 208.092ms DB Exec Pre-prepared: 6.393s Direct C:\code\prepared_statement_test> node .\prepared_statement.js DB Connect: 15.64ms DB Prepare and Exec: 206.244ms DB Direct Exec: 204.01ms DB Exec Pre-prepared: 185.878ms Via 8.0.28 C:\code\prepared_statement_test> node .\prepared_statement.js DB Connect: 15.876ms DB Prepare and Exec: 206.611ms DB Direct Exec: 217.797ms DB Exec Pre-prepared: 184.999ms