[Maria-discuss] mariadb still uses indexes with high average value group size.
Hi! I was playing around with indexes after we faced problems with mariadb choosing the wrong index because of high average value group size. So I created a table with 4 text columns and indexed the 4th column with a prefix length of 1. Following are the stats in brief -- The index has 35 unique values There are around 1040000 rows in total. this will give an average value group size of 29714 Cardinality is calculated as <no. of records in the index>/<average size of value groups>, thus it's value must be 35 BUT from show indexes, it's value is shown as 72 (innodb_stats_persistent_sample_pages = 9999999).
Hi, dE! On Jan 02, dE wrote:
Hi! I was playing around with indexes after we faced problems with mariadb choosing the wrong index because of high average value group size.
So I created a table with 4 text columns and indexed the 4th column with a prefix length of 1.
Following are the stats in brief --
The index has 35 unique values There are around 1040000 rows in total. this will give an average value group size of 29714 Cardinality is calculated as <no. of records in the index>/<average size of value groups>, thus it's value must be 35
BUT from show indexes, it's value is shown as 72 (innodb_stats_persistent_sample_pages = 9999999).
It's because InnoDB lies about the cardinality. It reports twice the actual value to the server. If MyISAM would report the cardinality as 36, InnoDB will say it's 72. You can enable the Engine Independent Table Statistics in MariaDB: https://mariadb.com/kb/en/mariadb/engine-independent-table-statistics/ This will show the correct value. Regards, Sergei Chief Architect MariaDB and security@mariadb.org -- Vote for my Percona Live 2016 talks: https://www.percona.com/live/data-performance-conference-2016/sessions/maria... https://www.percona.com/live/data-performance-conference-2016/sessions/maria...
Humm.. thanks for clarifying that! On 01/04/16 03:20, Sergei Golubchik wrote:
Hi, dE!
On Jan 02, dE wrote:
Hi! I was playing around with indexes after we faced problems with mariadb choosing the wrong index because of high average value group size.
So I created a table with 4 text columns and indexed the 4th column with a prefix length of 1.
Following are the stats in brief --
The index has 35 unique values There are around 1040000 rows in total. this will give an average value group size of 29714 Cardinality is calculated as <no. of records in the index>/<average size of value groups>, thus it's value must be 35
BUT from show indexes, it's value is shown as 72 (innodb_stats_persistent_sample_pages = 9999999). It's because InnoDB lies about the cardinality. It reports twice the actual value to the server. If MyISAM would report the cardinality as 36, InnoDB will say it's 72.
You can enable the Engine Independent Table Statistics in MariaDB: https://mariadb.com/kb/en/mariadb/engine-independent-table-statistics/ This will show the correct value.
Regards, Sergei Chief Architect MariaDB and security@mariadb.org
As a side note, the documentation for the engine independent table statistics isn't very complete : - Is there a reason not to activate it and what are the possible downsides of its activation - How much could it slow down an ANALYZE and possibly other operations? - There are no practical examples except on the histogram-based stats page - There are no other mention than a link to the KB page of the histogram-based stats on the KB page. Some of these slides could be re-used for the documentation I think : https://www.slideshare.net/mobile/SergeyPetrunya/mariadb-engine-independent-... ---- dE wrote ----
Humm.. thanks for clarifying that!
On 01/04/16 03:20, Sergei Golubchik wrote:
Hi, dE!
On Jan 02, dE wrote:
Hi! I was playing around with indexes after we faced problems with mariadb choosing the wrong index because of high average value group size.
So I created a table with 4 text columns and indexed the 4th column with a prefix length of 1.
Following are the stats in brief --
The index has 35 unique values There are around 1040000 rows in total. this will give an average value group size of 29714 Cardinality is calculated as <no. of records in the index>/<average size of value groups>, thus it's value must be 35
BUT from show indexes, it's value is shown as 72 (innodb_stats_persistent_sample_pages = 9999999). It's because InnoDB lies about the cardinality. It reports twice the actual value to the server. If MyISAM would report the cardinality as 36, InnoDB will say it's 72.
You can enable the Engine Independent Table Statistics in MariaDB: https://mariadb.com/kb/en/mariadb/engine-independent-table-statistics/ This will show the correct value.
Regards, Sergei Chief Architect MariaDB and security@mariadb.org
_______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
Well they've just provided the logic, you need to run that as per your situation. Experiences have not been shared. On 01/04/16 18:06, Jean Weisbuch wrote:
As a side note, the documentation for the engine independent table statistics isn't very complete : - Is there a reason not to activate it and what are the possible downsides of its activation - How much could it slow down an ANALYZE and possibly other operations? - There are no practical examples except on the histogram-based stats page - There are no other mention than a link to the KB page of the histogram-based stats on the KB page.
Some of these slides could be re-used for the documentation I think : https://www.slideshare.net/mobile/SergeyPetrunya/mariadb-engine-independent-...
---- dE wrote ----
Humm.. thanks for clarifying that!
On 01/04/16 03:20, Sergei Golubchik wrote:
Hi, dE!
On Jan 02, dE wrote:
Hi! I was playing around with indexes after we faced problems with mariadb choosing the wrong index because of high average value group size.
So I created a table with 4 text columns and indexed the 4th
column with > >> a prefix length of 1. > >> > >> Following are the stats in brief -- > >> > >> The index has 35 unique values > >> There are around 1040000 rows in total. > >> this will give an average value group size of 29714 > >> Cardinality is calculated as <no. of records in the index>/<average size > >> of value groups>, thus it's value must be 35 > >> > >> BUT from show indexes, it's value is shown as 72 > >> (innodb_stats_persistent_sample_pages = 9999999). > > It's because InnoDB lies about the cardinality. It reports twice the > > actual value to the server. If MyISAM would report the cardinality as > > 36, InnoDB will say it's 72. > > > > You can enable the Engine Independent Table Statistics in MariaDB: > > https://mariadb.com/kb/en/mariadb/engine-independent-table-statistics/ <https://mariadb.com/kb/en/mariadb/engine-independent-table-statistics/%0D>; > > This will show the correct value. > > > > Regards, > > Sergei > > Chief Architect MariaDB > > and security@mariadb.org <mailto:security@mariadb.org> > > > _______________________________________________ > Mailing list: https://launchpad.net/~maria-discuss <https://launchpad.net/%7Emaria-discuss%0D>; > Post to : maria-discuss@lists.launchpad.net <mailto:maria-discuss@lists.launchpad.net> > Unsubscribe : https://launchpad.net/~maria-discuss <https://launchpad.net/%7Emaria-discuss%0D>; > More help : https://help.launchpad.net/ListHelp
_______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
participants (3)
-
dE -
Jean Weisbuch -
Sergei Golubchik