Ronald Bradford
MySQL Expert

MySQL Expert Ronald Bradford shares valuable input in MySQL Performance Tuning, MySQL Scalability and general MySQL Help from his two decades of working with MySQL, Oracle, Ingres and development technologies.

«
»

InnoDB I_S.tables.table_rows out by a factor of 100x

I’ve always believed that the MySQL Information_schema.tables.table_rows figure for Innodb tables to be while approximate, approximately accurate.

Today I found that the figures varied on one table from 10x to 100x wrong.

Before performing an ALTER I always verify sizes for reference.

+---------+------------+--------+------------+------------+----------------+------------+------------+-----------+------------+
| table_s | table_name | engine | row_format | table_rows | avg_row_length | total_mb   | data_mb    | index_mb  | today      |
+---------+------------+--------+------------+------------+----------------+------------+------------+-----------+------------+
| XXXXX   | s_a        | InnoDB | Compact    |     208993 |           7475 |  1491.5312 |  1490.0156 |    1.5156 | 2009-09-09 |

mysql> alter table s_a modify col VARCHAR(255);
Query OK, 23471 rows affected (4 min 26.23 sec)

After

+---------+------------+--------+------------+------------+----------------+------------+------------+-----------+------------+
| XXXXX   | s_a        | InnoDB | Compact    |    2407063 |            629 |  1447.0312 |  1445.5156 |    1.5156 | 2009-09-09 |

I was so caught out by this. The table reported 200k rows, but the alter returned 23k, that’s like 10x out.
I ran my query again, and the second time I got.

+---------+------------+--------+------------+------------+----------------+------------+------------+-----------+------------+
| XXXXX   | s_a        | InnoDB | Compact    |      21813 |          69487 |  1447.0312 |  1445.5156 |    1.5156 | 2009-09-09 |

This is closer to what I’d expected, 22k verses 23k.

I have to say, while I have always treated the data and index size as accurate, I now how little confidence in the table_rows any more.

Tags: , , ,

This entry was posted on Wednesday, September 9th, 2009 at 5:21 pm and is filed under Databases, MySQL, Open Source, Professional, Uncategorized. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

3 Responses to “InnoDB I_S.tables.table_rows out by a factor of 100x”

  1. erin says:
    September 9, 2009 at 6:18 pm

    Me too! Radically changes for me on two tables that have not been updated/inserted/deleted in any way for months kept showing up in the information_schema. I had to stop using it. Now I doubt all the stats for tables in the information_schema.

  2. Sarah Sproehnle says:
    September 9, 2009 at 8:28 pm

    Ronald,

    This is documented (http://dev.mysql.com/doc/refman/5.1/en/tables-table.html). Although it is a rough approximation, it can be useful when I don’t need an exact count. As you surely know, “SELECT count(*) FROM innodb_table” can be slow.

    -Sarah

  3. Marc Delisle says:
    September 10, 2009 at 10:16 am

    Hi Ronald,
    I suggest you add “InnoDB” in your article’s title.

Leave a Reply