Bug #11605 | yyparse() is 93% slower in 5.0 than in 4.1 on test-wisconsin | ||
---|---|---|---|
Submitted: | 28 Jun 2005 9:51 | Modified: | 4 Mar 2008 22:28 |
Reporter: | Marko Mäkelä | Email Updates: | |
Status: | Closed | Impact on me: | |
Category: | MySQL Server: Parser | Severity: | S2 (Serious) |
Version: | 5.0.8 | OS: | Linux (GNU/Linux) |
Assigned to: | Marc ALFF | CPU Architecture: | Any |
[28 Jun 2005 9:51]
Marko Mäkelä
[28 Jun 2005 13:14]
Heikki Tuuri
Marko, please paste the profiler report to this bug report, and describe the computer where this was measured. Regards, Heikki
[28 Jun 2005 13:32]
Marko Mäkelä
This is the top of "opreport" output from 5.0 with ./test-wisconsin --create-options='engine=innodb row_format=redundant': CPU: P4 / Xeon with 2 hyper-threads, speed 1988.44 MHz (estimated) Counted GLOBAL_POWER_EVENTS events (time during which processor is not stopped) with a unit mask of 0x01 (mandatory) count 100000 samples % symbol name 285731 14.4120 yyparse(void*) 73886 3.7267 log_group_write_buf 67311 3.3951 cmp_rec_rec_with_match 64872 3.2721 rec_get_offsets_func 49275 2.4854 my_long10_to_str_8bit 48827 2.4628 rec_init_offsets 43577 2.1980 yylex(void*, void*) This is from 4.1.12, with ./test-wisconsin --create-options=engine=innodb: CPU: P4 / Xeon with 2 hyper-threads, speed 1988.44 MHz (estimated) Counted GLOBAL_POWER_EVENTS events (time during which processor is not stopped) with a unit mask of 0x01 (mandatory) count 100000 samples % symbol name 148118 8.3887 yyparse(void*) 69247 3.9218 cmp_rec_rec_with_match 68947 3.9049 log_group_write_buf 64020 3.6258 rec_get_nth_field 49630 2.8108 cmp_dtuple_rec_with_match 49101 2.7809 my_long10_to_str_8bit 44199 2.5032 yylex(void*, void*)
[30 Jun 2005 6:34]
Aleksey Kishkin
Hi! (in order to reproduce it) what settings of mysqld did you use? and which exactly sql-bench did you use (from bk (sql bench has it's own bk tree) or from mysql distribution?)
[30 Jun 2005 9:08]
Vadim Tkachenko
I used default mysqld params (i.e. I ran it as ./mysqld --user=root) and I used sql-bench from mysql-5.0/sql-bench dir
[27 Sep 2005 15:29]
Aleksey Kishkin
checked on slackware 10, but got 8% difference (not 12)
[13 Oct 2006 23:04]
Marc ALFF
For what it's worth, the following are trends in the parser complexity. All are mesured with the current code as of 2006-10-13, with bison 2.2: Release 4.0: ============ 95 Shift/Reduce, 4 reduce/reduce /* YYLAST -- Last index in YYTABLE. */ #define YYLAST 26073 /* YYNTOKENS -- Number of terminals. */ #define YYNTOKENS 412 /* YYNNTS -- Number of nonterminals. */ #define YYNNTS 371 /* YYNRULES -- Number of rules. */ #define YYNRULES 1294 /* YYNRULES -- Number of states. */ #define YYNSTATES 2418 Release 4.1: ============ 366 Shift/Reduce /* YYLAST -- Last index in YYTABLE. */ #define YYLAST 39430 /* YYNTOKENS -- Number of terminals. */ #define YYNTOKENS 495 /* YYNNTS -- Number of nonterminals. */ #define YYNNTS 497 /* YYNRULES -- Number of rules. */ #define YYNRULES 1678 /* YYNRULES -- Number of states. */ #define YYNSTATES 3113 Release 5.0: ============ 248 Shift/Reduce /* YYLAST -- Last index in YYTABLE. */ #define YYLAST 42780 /* YYNTOKENS -- Number of terminals. */ #define YYNTOKENS 570 /* YYNNTS -- Number of nonterminals. */ #define YYNNTS 654 /* YYNRULES -- Number of rules. */ #define YYNRULES 2000 /* YYNRULES -- Number of states. */ #define YYNSTATES 3638 Release 5.1: ============ 282 Shift/Reduce /* YYLAST -- Last index in YYTABLE. */ #define YYLAST 48668 /* YYNTOKENS -- Number of terminals. */ #define YYNTOKENS 611 /* YYNNTS -- Number of nonterminals. */ #define YYNNTS 812 /* YYNRULES -- Number of rules. */ #define YYNRULES 2344 /* YYNRULES -- Number of states. */ #define YYNSTATES 4193 The size of YYTABLE has increased a lot due to the enhancements (views, SP, ...) implemented, which had some impact on the parser and possibly performances
[20 Jul 2007 14:12]
Mark Callaghan
I just noticed this (see bug http://bugs.mysql.com/bug.php?id=29921). For my test, MYSQLparse in MySQL 5.0.37 uses 3X more CPU in MySQL 4.0.26). This contributes to making replay of mysqldump output 50% slower in MySQL 5.0.
[20 Jul 2007 23:26]
Mark Callaghan
What are the plans to fix this or is this the price of implementing something for the enterprise (views, triggers, stored procedures, spatial, ...). I have pulled all of the above from the parser in MySQL5 to see if the old performance can be brought back.
[4 Aug 2007 20:11]
Marko Mäkelä
Bug #29921 has been filed as a follow-up to this.
[11 Sep 2007 1:00]
Marc ALFF
The performance of the parser have been greatly improved in 5.0.50, see bug#29921 for details. Changing this report to 'Open', as it needs to be verified again, with MySQL 5.0.50 or later
[25 Sep 2007 13:35]
MySQL Verification Team
Marko could you please re-verify according Marc last request. Thanks in advance.
[25 Sep 2007 19:39]
Marko Mäkelä
I am not sure if I can compile the 4.1 tree any more. I was unable to compile the 4.0 tree on GCC 4.2 some weeks ago, even after some tweaking. The bug can be verified or refuted easily; see the initial bug report and my comment from 28 Jun 2005. The test case is included in the MySQL source tree, in sql-bench/test-wisconsin.
[4 Mar 2008 21:51]
Marc ALFF
See related blog: http://mysqlha.blogspot.com/2008/02/is-mysql-getting-faster.html
[4 Mar 2008 22:28]
Marc ALFF
Several performance related issues have been fixed in the parser: - Bug#30237 (Performance regression in boolean expressions) - Bug#30333 (Performance, expressions lists in the parser) - Bug#30625 (Performance, reduce depth for expressions) The performance degradation reported has been addressed, so this bug is now closed. Feel free to re-open this bug report, with new performance measures, if further improvement is needed.