| Bug #101107 | Using VIEW with a prepared statement makes MySQL very slow | ||
|---|---|---|---|
| Submitted: | 9 Oct 2020 15:24 | Modified: | 5 Nov 2020 17:28 |
| Reporter: | Masahiro Tomita | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | MySQL Server: Prepared statements | Severity: | S3 (Non-critical) |
| Version: | 8.0.21 | OS: | Ubuntu |
| Assigned to: | CPU Architecture: | Any | |
[12 Oct 2020 12:14]
MySQL Verification Team
Hello Mr. Tomita, Thank you for your bug report. However, what you describe is, actually, expected behaviour. If you read our documentation on the statement reset, it actually clears lots of buffers and hence a new run must be slower. Also, if you read our documentation on the VIEWs, you will see that those are actually NOT replacements for the TABLEs. Unlike a direct work with tables, using views requires several additional operations. Views are here to make programming easier and not to make it as fast. Not a bug.
[13 Oct 2020 1:46]
Masahiro Tomita
This does not indicate that it is slow, but that it gradually slows down as it repeats. count sec qps 1000 0.116 8620.854 ... 10000 0.440 2272.771 ... 20000 0.688 1453.517 ... 30000 1.168 856.181 ... 40000 1.432 698.338 ... 50000 1.692 591.028
[13 Oct 2020 12:29]
MySQL Verification Team
Hi Mr. Tomita, This phenomenon has the same explanation as the one provided. More buffers get cleared and more temporary tables get created. Not a bug.
[5 Nov 2020 17:28]
Masahiro Tomita
This has been fixed in MySQL 8.0.22. Results at 8.0.22: % docker exec -it mysql80 ./a.out count sec qps 1000 0.108 9259.460 2000 0.104 9615.575 3000 0.104 9615.597 4000 0.104 9615.597 5000 0.100 10000.200 6000 0.104 9615.597 7000 0.108 9259.460 8000 0.100 10000.224 9000 0.100 10000.200 10000 0.108 9259.460 11000 0.100 10000.224 12000 0.108 9259.440 13000 0.100 10000.224 14000 0.112 8928.762 15000 0.104 9615.597 16000 0.104 9615.597 17000 0.104 9615.575 18000 0.100 10000.224 19000 0.100 10000.200 20000 0.104 9615.597 21000 0.100 10000.224 22000 0.100 10000.200 23000 0.104 9615.597 24000 0.100 10000.224 25000 0.112 8928.762 26000 0.096 10416.878 27000 0.132 7575.924 28000 0.100 10000.200 29000 0.104 9615.597 30000 0.100 10000.224 31000 0.108 9259.460 32000 0.100 10000.200 33000 0.104 9615.597 34000 0.100 10000.200 35000 0.104 9615.597 36000 0.112 8928.762 37000 0.096 10416.904 38000 0.104 9615.575 39000 0.104 9615.597 40000 0.096 10416.904 41000 0.104 9615.575 42000 0.100 10000.224 43000 0.100 10000.200 44000 0.104 9615.597 45000 0.100 10000.224 46000 0.104 9615.575 47000 0.104 9615.597 48000 0.096 10416.904 49000 0.108 9259.440 50000 0.104 9615.597
[6 Nov 2020 12:33]
MySQL Verification Team
Thank you for the feedback.
Description: Using VIEW with a prepared statement and using mysql_stmt_reset() makes MySQL very slow. If you use TABLE instead of VIEW or don't use mysql_stmt_reset(), it won't be slow. How to repeat: % cat test.c #include <stdlib.h> #include <stdio.h> #include <string.h> #include <time.h> #include <mysql/mysql.h> int main(int argc, char *argv[]) { MYSQL my; MYSQL_STMT *st; struct timespec ts; double t1, t2; mysql_init(&my); if (mysql_real_connect(&my, "localhost", "root", "", "test", 0, NULL, 0) == NULL) goto mysql_error; if ((st = mysql_stmt_init(&my)) == NULL) goto mysql_error; #ifdef WITHOUT_VIEW char *q = "select * from t where name='hogehoge'"; #else char *q = "select * from v where name='hogehoge'"; #endif if (mysql_stmt_prepare(st, q, strlen(q))) goto stmt_error; puts("count\t sec\t qps"); clock_gettime(CLOCK_REALTIME_COARSE, &ts); t1 = ts.tv_sec + (double)ts.tv_nsec / 1000000000; for (int i = 1; i <= 50000; i++) { if (i % 1000 == 0) { clock_gettime(CLOCK_REALTIME_COARSE, &ts); t2 = ts.tv_sec + (double)ts.tv_nsec / 1000000000; printf("%5d\t%6.3f\t%9.3f\n", i, t2-t1, 1000/(t2 - t1)); clock_gettime(CLOCK_REALTIME_COARSE, &ts); t1 = ts.tv_sec + (double)ts.tv_nsec / 1000000000; } if (mysql_stmt_execute(st)) goto stmt_error; if (mysql_stmt_store_result(st)) goto stmt_error; #ifndef WITHOUT_STMT_RESET if (mysql_stmt_reset(st)) goto stmt_error; #endif } return 0; mysql_error: puts(mysql_error(&my)); exit(1); stmt_error: puts(mysql_stmt_error(st)); exit(1); } % docker run --name mysql80 -d -e MYSQL_ALLOW_EMPTY_PASSWORD=1 mysql:8.0 % docker exec -it mysql80 mysql mysql> create database test; mysql> use test; mysql> create table t (id int auto_increment primary key, name varchar(255)); mysql> create view v (id, name) as select id, name from t; mysql> quit % docker exec mysql80 bash -c 'apt update && apt install -y gcc libmysqlclient-dev' % docker cp test.c mysql80:/ % docker exec mysql80 gcc test.c -lmysqlclient % docker exec -it mysql80 ./a.out count sec qps 1000 0.116 8620.854 2000 0.140 7142.998 3000 0.152 6579.073 4000 0.180 5555.664 5000 0.192 5208.439 6000 0.220 4545.543 7000 0.252 3968.331 8000 0.320 3125.061 9000 0.440 2272.771 10000 0.440 2272.771 11000 0.372 2688.224 12000 0.412 2427.233 13000 0.460 2173.956 14000 0.520 1923.114 15000 0.544 1838.271 16000 0.568 1760.598 17000 0.600 1666.699 18000 0.664 1506.053 19000 0.664 1506.053 20000 0.688 1453.517 21000 0.708 1412.457 22000 0.748 1336.925 23000 0.788 1269.060 24000 0.820 1219.536 25000 0.964 1037.364 26000 0.896 1116.093 27000 0.960 1041.687 28000 0.980 1020.428 29000 1.000 1000.020 30000 1.168 856.181 31000 1.080 925.944 32000 1.140 877.210 33000 1.196 836.137 34000 1.188 841.767 35000 1.288 776.413 36000 1.308 764.541 37000 1.416 706.228 38000 1.304 766.886 39000 1.384 722.558 40000 1.432 698.338 41000 1.552 644.342 42000 1.428 700.294 43000 1.464 683.073 44000 1.496 668.462 45000 1.540 649.363 46000 1.548 646.007 47000 1.612 620.359 48000 1.616 618.824 49000 1.660 602.421 50000 1.692 591.028 % docker exec mysql80 gcc test.c -lmysqlclient -DWITHOUT_VIEW % docker exec -it mysql80 ./a.out count sec qps 1000 0.084 11905.017 2000 0.088 11363.875 3000 0.088 11363.844 4000 0.084 11905.017 5000 0.084 11905.017 6000 0.092 10869.790 7000 0.084 11904.983 8000 0.088 11363.875 9000 0.088 11363.875 10000 0.092 10869.790 11000 0.084 11904.983 12000 0.096 10416.878 13000 0.116 8620.872 14000 0.088 11363.875 15000 0.104 9615.575 16000 0.128 7812.659 17000 0.088 11363.875 18000 0.088 11363.844 19000 0.096 10416.878 20000 0.092 10869.790 21000 0.092 10869.790 22000 0.100 10000.200 23000 0.092 10869.790 24000 0.100 10000.200 25000 0.100 10000.200 26000 0.084 11905.017 27000 0.088 11363.875 28000 0.088 11363.875 29000 0.092 10869.790 30000 0.088 11363.844 31000 0.092 10869.790 32000 0.096 10416.878 33000 0.108 9259.460 34000 0.092 10869.761 35000 0.100 10000.224 36000 0.092 10869.790 37000 0.100 10000.200 38000 0.096 10416.878 39000 0.100 10000.200 40000 0.096 10416.878 41000 0.112 8928.743 42000 0.108 9259.460 43000 0.092 10869.790 44000 0.088 11363.844 45000 0.088 11363.875 46000 0.096 10416.878 47000 0.092 10869.790 48000 0.092 10869.790 49000 0.100 10000.200 50000 0.100 10000.200 % docker exec mysql80 gcc test.c -lmysqlclient -DWITHOUT_STMT_RESET % docker exec -it mysql80 ./a.out count sec qps 1000 0.088 11363.875 2000 0.092 10869.790 3000 0.088 11363.875 4000 0.088 11363.875 5000 0.088 11363.844 6000 0.088 11363.875 7000 0.092 10869.790 8000 0.092 10869.790 9000 0.084 11904.983 10000 0.092 10869.790 11000 0.088 11363.875 12000 0.088 11363.875 13000 0.092 10869.790 14000 0.088 11363.844 15000 0.092 10869.790 16000 0.084 11905.017 17000 0.092 10869.790 18000 0.092 10869.761 19000 0.084 11905.017 20000 0.088 11363.875 21000 0.084 11904.983 22000 0.092 10869.790 23000 0.088 11363.875 24000 0.096 10416.878 25000 0.084 11905.017 26000 0.092 10869.761 27000 0.092 10869.790 28000 0.092 10869.790 29000 0.088 11363.875 30000 0.092 10869.790 31000 0.092 10869.790 32000 0.088 11363.844 33000 0.088 11363.875 34000 0.092 10869.790 35000 0.092 10869.790 36000 0.088 11363.875 37000 0.084 11904.983 38000 0.092 10869.790 39000 0.096 10416.878 40000 0.088 11363.875 41000 0.092 10869.790 42000 0.088 11363.844 43000 0.088 11363.875 44000 0.088 11363.875 45000 0.092 10869.790 46000 0.092 10869.790 47000 0.088 11363.844 48000 0.088 11363.875 49000 0.092 10869.790 50000 0.092 10869.790