wiki:db/tests/MySql/WideTables
Last modified 6 years ago Last modified on 07/22/2013 05:07:31 AM

Wide Tables

We are expecting the Object table to have nearly 5,000 columns. We need to test what are the limits in mysql, e.g. theoretically it should handle 4K columns (http://dev.mysql.com/doc/refman/5.6/en/column-count-limit.html). that means we will have to split vertically the table, or pack columns into blobs. We need to test if there a visible performance degradation for user queries when we deal with table with thousands of columns

It is likely we will want to pack some of these 5000 columns into blobs. Try some combinations, e.g.

  • 5000 columns in one blob, plus 500 non-blob columns
  • 1000 columns per blob (5 blobs), plus 500 non-blob columns
  • 100 columns per blob (50 blobs), plus 500 non-blob columns

Try

  • keeping blobs together with non-blob columns in the same table
  • keeping all blobs together in a different dedicated table, with objectId that can be used to join back to the table with non-blob columns.
  • have all non-blob columns but split across multiple tables.

and measure speed of some queries that operate on this data.

Try few different table sizes (1 GB, 10 GB, 100 GB)

IN2P3 benchmarks

db/tests/MySql/WideTablesAtIn2p3