[MDEV-20400] Implement a way to query an InnoDB table's max row size Created: 2019-08-21 Updated: 2023-11-30 |
|
| Status: | Confirmed |
| Project: | MariaDB Server |
| Component/s: | Storage Engine - InnoDB |
| Fix Version/s: | None |
| Type: | New Feature | Priority: | Critical |
| Reporter: | Geoff Montee (Inactive) | Assignee: | Sergei Golubchik |
| Resolution: | Unresolved | Votes: | 7 |
| Labels: | None | ||
| Issue Links: |
|
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Description |
|
Users see this error quite frequently:
I think the reason users run into this issue a lot is because:
Once a table that exceeds the maximum row size is created, it can be quite difficult to determine which tables can hit this issue. The easiest way to currently check if a table hits the limit is to set innodb_strict_mode=ON, and then to create another table with the exact same definition. For example:
However, if you want to check all tables on the system, then this method is impractical for systems that have a lot of tables. I think we should have some way to more easily determine which tables on a server have an "unsafe" maximum row size that can exceed the row format's maximum row size. Some potential ways to implement this are:
"CHECK TABLE" does currently return a warning in this case sometimes, but I can't tell if that behavior is intentional, or if it is a bug that was introduced by the fix for For example:
Users could use this to determine if a table is at risk of surpassing InnoDB's maximum row size by looking at the row for the table, and then checking if this row's "MAX_ROW_SIZE" value is greater than 1/2 of innodb_page_size. https://mariadb.com/kb/en/library/information-schema-innodb_sys_tables-table/
Users could use this to determine if a table is at risk of surpassing InnoDB's maximum row size by looking at the row for the table's PRIMARY index, and then checking if this row's "MAX_RECORD_SIZE" value is greater than 1/2 of innodb_page_size. https://mariadb.com/kb/en/library/information-schema-innodb_sys_indexes-table/
Users could use this to determine if a table is at risk of surpassing InnoDB's maximum row size by looking at the rows for all columns in the table, and then checking if the sum of the rows' "INLINE_LEN" values is greater than 1/2 of innodb_page_size. https://mariadb.com/kb/en/library/information-schema-innodb_sys_columns-table/ |
| Comments |
| Comment by Marko Mäkelä [ 2019-08-22 ] | |||||||||||||||||||||||||||||||||||||||||||
|
InnoDB currently ignores any clauses in CHECK TABLE, except for QUICK. I think that it would be reasonable for all forms of CHECK TABLE to check the maximum row size, based on the value of innodb_strict_mode. Side note: As far as I know, CHECK TABLE currently always returns a success code, and any errors are reported in the result-set. I would not want to extend the output of any INFORMATION_SCHEMA.INNODB_SYS_ views. After all, I would like to remove these views along with the InnoDB internal data dictionary, related to MDEV-11655. | |||||||||||||||||||||||||||||||||||||||||||
| Comment by Marko Mäkelä [ 2019-11-14 ] | |||||||||||||||||||||||||||||||||||||||||||
|
For InnoDB, I would expose the following:
I do not think that such an interface is applicable to MyRocks (which uses append-only compressed files) or ‘virtual’ storage engines, such as Spider. I believe that the minimum and maximum sizes would make sense for any storage engine that uses B-trees. MyISAM and Aria use those for indexes, even though the data itself is heap-organized (unlike InnoDB’s index-organized tables, which store the data with the PRIMARY KEY in the clustered index). Also, the choice of ROW_FORMAT should affect the record sizes in MyISAM and Aria. | |||||||||||||||||||||||||||||||||||||||||||
| Comment by Michael Widenius [ 2020-02-26 ] | |||||||||||||||||||||||||||||||||||||||||||
|
The main reason this ticket was created was because of The real solution to users who has this problem is to either increase InnoDB block size or change long VARCHAR columns to TEXT | |||||||||||||||||||||||||||||||||||||||||||
| Comment by Hartmut Holzgraefe [ 2020-02-26 ] | |||||||||||||||||||||||||||||||||||||||||||
|
"The main reason this ticket was created was because of No, it was created in the aftermath of all the problems the | |||||||||||||||||||||||||||||||||||||||||||
| Comment by Geoff Montee (Inactive) [ 2020-02-26 ] | |||||||||||||||||||||||||||||||||||||||||||
Right. This task was created because of the fix for juan.vera did create a script this week that can be used to do this until we can implement something in the server. See here:
I would think that using a script like this is probably a good temporary workaround.
Actually, long VARCHAR columns do not need to be converted to TEXT. This is a common misunderstanding. With InnoDB's DYNAMIC row format, long VARCHAR columns are usually fine already, because that row format can store any VARCHAR columns that are 256 bytes or longer completely on overflow pages. Therefore, if the user is using InnoDB's DYNAMIC row format, then the problem is usually caused by short VARCHAR columns, and a counter-intuitive but effective solution to the problem is actually to increase the length of these short VARCHAR columns. See here:
| |||||||||||||||||||||||||||||||||||||||||||
| Comment by Marko Mäkelä [ 2020-04-21 ] | |||||||||||||||||||||||||||||||||||||||||||
|
For the record, sanja suggested that INFORMATION_SCHEMA.INNODB_% views be used for this. I strongly disagree with that due to the following:
In my opinion, if this is to be implemented, it needs to be a proper storage engine interface that can be invoked after handler::open(). Side note: INFORMATION_SCHEMA.INNODB_SYS_% was reverted from MySQL 5.5 due to opposition by the senior MySQL architect Rune Humborstad. Unfortunately, it was added in MySQL 5.6 and extended in 5.7. | |||||||||||||||||||||||||||||||||||||||||||
| Comment by Sergei Golubchik [ 2020-05-20 ] | |||||||||||||||||||||||||||||||||||||||||||
|
2. Ideally, the engine should see only names in the my_charset_filename, but I doubt we're there yet | |||||||||||||||||||||||||||||||||||||||||||
| Comment by Marko Mäkelä [ 2020-08-05 ] | |||||||||||||||||||||||||||||||||||||||||||
|
I see that fill_schema_schemata(), which implements information_schema.schemata, is making use of LOOKUP_FIELD_VALUES. That might address my last point. But, given my long-term goal to remove the persistent InnoDB data dictionary, it would seem more appropriate to provide the information via class handler, which is not adequately capturing the underlying data format of InnoDB:
In InnoDB, B-tree leaf and non-leaf pages impose different length limitations in different indexes. I can imagine cases where the clustered index record size is more limiting than any secondary index, but it could also be the opposite. The above API is limited to the table level only, and it does not even consider the ROW_FORMAT of the table. I vaguely remember that those functions could be called without opening a table handle first. I think that this problem would be best addressed by adequately refactoring the handler API. For the InnoDB changes, I hope that the fix of | |||||||||||||||||||||||||||||||||||||||||||
| Comment by Marko Mäkelä [ 2020-09-23 ] | |||||||||||||||||||||||||||||||||||||||||||
|
I think that we could try to create a new view INFORMATION_SCHEMA.INNODB_TABLES that would expose this information and generally be a replacement for INNODB_SYS_TABLES. The names of the schema, table, partition and subpartition should be returned in a consistent fashion with non-InnoDB views in the INFORMATION_SCHEMA, and an attempt to support condition pushdown via LOOKUP_FIELD_VALUES should be made. This new table could also export other attributes, such as the ON_SSD attribute that was added in | |||||||||||||||||||||||||||||||||||||||||||
| Comment by Vladislav Vaintroub [ 2021-05-06 ] | |||||||||||||||||||||||||||||||||||||||||||
|
Maybe this does not need to be solved in the exact fashion like GeoffMontee asks for it, He wants to know how much bytes is a row with a given table definition. If I make an assumption that output is deterministic, and only depends on DDL, then someone could and also write a perl function that takes a table definition text, parses it, does some math, and spits out the number, based on the same logic that is in C++. this function can be written in Perl, and combined with command client in a true Unix manner, via pipes mysql -e "show create table test.foobar" | perl do_some_math.pl , I do not see that every bug should automatically turn into an additional field in the information schema table, for a workaround that only few people need (only few people have zillions of tables so that the "slow" method can't be used) Perhaps, with some effort, we can introduce an SQL function that looks up I_S.columns and spits out the number, and say in 10.6 it would be added to sys schema. But I'm almost sure that we do not need an additional column to slow down already slow I_S queries. | |||||||||||||||||||||||||||||||||||||||||||
| Comment by Marko Mäkelä [ 2021-05-07 ] | |||||||||||||||||||||||||||||||||||||||||||
|
wlad, in a B-tree based storage engine like InnoDB, there are multiple different size constraints, not a single "row size". In addition to the combinations that I mentioned on 2019-11-14:
there is one more constraint, caused by the fact that an InnoDB undo log record must fit in a single page: An attempt to UPDATE too many indexed columns may fail, because the undo log record must store the secondary index keys. This is most prominent when many prefix-indexed BLOB or TEXT columns are being updated in ROW_FORMAT=DYNAMIC or ROW_FORMAT=COMPRESSED. | |||||||||||||||||||||||||||||||||||||||||||
| Comment by Marko Mäkelä [ 2021-05-26 ] | |||||||||||||||||||||||||||||||||||||||||||
|
I think that the maximum number of index fields in a secondary index record is 16+16+1, for the maximum number of PRIMARY KEY columns (16), the maximum number of INDEX columns (16, if these are different from the PRIMARY KEY columns), and 1 child page number pointer (4 bytes). In the clustered index, other than the PRIMARY KEY columns may be stored off-page (as BLOBs), provided that they are stored as variable-size and the values are long enough. The rules are rather complex, and they depend on the ROW_FORMAT. The maximum size (innodb_page_size minus some overhead, divided by 2) would typically be exceeded by a secondary index non-leaf page record, or by a clustered index record. I think that we must return both the maximum size and the name of the index that has the longest-maximum-size record. | |||||||||||||||||||||||||||||||||||||||||||
| Comment by Marko Mäkelä [ 2022-04-12 ] | |||||||||||||||||||||||||||||||||||||||||||
|
serg, I think that an interface needs to be created that matches the storage model of InnoDB. |