Mysql – What are the sharding heuristics for MySQL – number of records per table & number of tables per instance


Designing an object store for ~10B objects and using mySQL for storing metadata. We know we need to shard and are looking for general heuristics to scale. Would appreciate pointers and hear about the back-of-the-envelope calculations you all used

Best Answer

These URLs will tell you about the data pointer option in MyISAM tables.

According to these URLs, a MyISAM table can theoretically be

256 TB (for myisam_data_pointer_size = 6 (default)
65536 TB (for myisam_data_pointer_size = 7 (maximum)

The largest InnoDB tablespace is 64TB.

The largest individual table can be 64TB if you enable innodb_file_per_table.

This URL will give you more numbers on largest BLOB, TEXT, VARCHAR, VARBINARY, LONGTEXT and BLOBs for InnoDB :

Click here for Maximum Number of Columns Per Table

As far as the maximum number of tables per database, that is OS dependent. If there is limit to the number of files in a folder in a given OS (let's call it OSFILEMAX), MAXTABLES is OSFILEMAX divided by 3 (.frm, .MYD, .MYI) for MyISAM tables, MAXTABLES is OSFILEMAX by 2 (.frm, .ibd) if using InnoDB with innodb_file_per_table enabled, and MAXTABLES is OSFILEMAX (.frm) if using InnoDB with innodb_file_per_table disabled.

I hope these URLs and other answers provided help you !!!