NONparametrics

geekdom without assumption
NONparametrics

MySQL Data Footprints: How Big are Your Tables?

If you work on the architectural end of things (or if you’re just curious), it can be extremely helpful to see what kind of footprint your data tables actually have in real use. In testing, it can be difficult to visualize the big-picture impact of design decisions such as:

  • How much space should I allocate for a field? (eg, INT vs BIGINT vs TINYINT, TEXT vs VARCHAR(65,535))?
  • How much of my total storage will be taken up by table X ?
  • How much impact does each additional row in table X use?
  • What difference does the engine make in the storage size of a table?

To that end, I wrote a simple view that utilizes the information_schema tables to give you answers to some of these questions. Feel free to post improvements on this in the comments–it’s a pretty rough tool.

 

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `V_Table_Size` AS
SELECT `information_schema`.`TABLES`.`TABLE_NAME` AS `Table`,`information_schema`.`TABLES`.`ENGINE` AS `Engine`,
round(((`information_schema`.`TABLES`.`DATA_LENGTH` / 1024) / 1024),2) AS `Total Size`,
`information_schema`.`TABLES`.`TABLE_ROWS` AS `# of Rows`,
(`information_schema`.`TABLES`.`DATA_LENGTH` / `information_schema`.`TABLES`.`TABLE_ROWS`) AS `AVG Row` from `information_schema`.`TABLES`
where (`information_schema`.`TABLES`.`TABLE_SCHEMA` = _utf8’databaseNameHere’);

The output is essentially this:


+-------------------+---------+------------+-----------+------------+
| Table | Engine | Total Size | # of Rows | AVG Row |
+-------------------+---------+------------+-----------+------------+
| Session | InnoDB | 68.59 | 313966 | 229.0877 |
| AUDIT_LOG | ARCHIVE | 145.14 | 20500414 | 7.4240 |
| Zip | InnoDB | 0.65 | 29479 | 22.9713 |
...

I hope someone finds this helpful!

Related Tags: [ , ]

Leave a Reply