| Bug #41133 | InnoDB cardinality statistics are uneven | ||
|---|---|---|---|
| Submitted: | 30 Nov 2008 13:15 | Modified: | 18 Sep 2009 14:12 |
| Reporter: | Baron Schwartz (Basic Quality Contributor) | Email Updates: | |
| Status: | Duplicate | Impact on me: | |
| Category: | MySQL Server: InnoDB storage engine | Severity: | S3 (Non-critical) |
| Version: | 4.1, 5.0, 5,1, 6.0 bzr | OS: | Any |
| Assigned to: | Assigned Account | CPU Architecture: | Any |
| Tags: | qc | ||
[1 Dec 2008 8:57]
Sveta Smirnova
Thank you for the report. Verified as described.
[1 Dec 2008 8:59]
Sveta Smirnova
reduced test case
Attachment: bug41133.test (application/octet-stream, text), 208.01 KiB.
[1 Dec 2008 18:38]
Mikhail Izioumtchenko
Vasil is our optimizer statistics specialist.
[18 Sep 2009 7:51]
MySQL Verification Team
would fix for bug #43660 (innodb_use_legacy_cardinality_algorithm=0) would fix this too ?
[18 Sep 2009 14:10]
Vasil Dimov
Shane, Good catch! This is indeed the same bug - the pattern (2 of 3) looks pretty much like "Bug #43660 SHOW INDEXES/ANALYZE does NOT update cardinality for indexes of InnoDB table" I confirm the problem goes away when innodb-use-legacy-cardinality-algorithm is set to 0: (using Baron's steps to reproduce) hag:/tmp/sakila-db$ wc -l sizes-* 1099 sizes-innodb-use-legacy-cardinality-algorithm=1-default 1072 sizes-innodb-use-legacy-cardinality-algorithm=0 2171 total hag:/tmp/sakila-db$ sort sizes-innodb-use-legacy-cardinality-algorithm\=1-default | uniq -c 100 1022 100 1024 100 1058 100 1059 100 1131 200 951 200 952 199 953 hag:/tmp/sakila-db$ sort sizes-innodb-use-legacy-cardinality-algorithm\=0 | uniq -c 1 1017 8 1018 14 1019 12 1021 33 1022 42 1024 32 1025 23 1026 23 1028 5 1029 1 1030 1 1032 1 1051 2 1052 7 1054 19 1055 35 1057 37 1058 43 1059 16 1061 24 1062 11 1063 3 1065 1 1069 1 1123 2 1124 2 1125 13 1127 20 1128 33 1129 39 1131 29 1132 25 1134 12 1135 6 1136 5 1138 2 1139 2 1140 1 663 1 665 1 699 1 709 1 732 1 733 3 735 2 736 2 738 6 739 1 740 1 742 3 769 2 771 4 772 5 773 3 804 2 805 1 806 8 808 6 809 6 810 6 812 2 813 1 816 1 817 1 839 4 841 11 842 7 843 9 845 12 846 6 848 1 849 2 850 3 875 3 876 5 878 7 879 12 881 5 882 1 883 2 885 3 908 7 911 5 912 12 914 16 915 15 916 16 918 13 919 3 920 3 922 1 923 2 925 3 945 4 947 13 948 24 949 21 951 28 952 29 953 16 955 6 956 2 958 2 959 2 980 2 981 9 982 17 984 13 985 16 986 9 988 9 989 3 991 1 993 hag:/tmp/sakila-db$ Thank you!
[18 Sep 2009 14:12]
Vasil Dimov
Setting as duplicate of Bug#43660 SHOW INDEXES/ANALYZE does NOT update cardinality for indexes of InnoDB table
Description: The InnoDB cardinality statistics have a strange pattern that probably indicates a random number generator isn't being truly random or something like that. This pattern (3 of the values are shown about 2x as often as the other 5) is probably a symptom of a bug in InnoDB that should be fixed. How to repeat: Using the sakila sample database, run this for a while: while true; do mysql sakila -N -e 'show index from film' | head -n 2 | tail -n 1 | awk '{print $7}'; done > sizes CTRL-C when you think you have enough. Then, $ sort sizes | uniq -c 157 1022 156 1024 156 1058 156 1059 156 1131 313 951 312 952 312 953