EMC flash drive as Temp database device

 

Here is an unusual data warehouse (columnar, not row based) server; I have several very large fact tables, some of them are over 800GB size.

Total DB size is way over 25TB, and we run very bizarre over 20 table joins, outer join/self join.

Mainly for financial risk reporting; the reporting tools are SAP business object, IBM Cognos and Micro Strategy dashboard, lots of cubes and ad-hoc queries. Most queries will do hash/merge joins, group by, order by; over 95% i/o will be at Temp DB devices.

For reporting server similar to this, in general, to support multiple running SQL in the system; the i/o from Temp database is became major bottleneck.

You can find good references about Cube from Data Warehouses Explained by Dremio.

DW graphical query plan output

 

If you have 5 concurrent reports similar to that, you will need close to 1000G temp database space.

 

Setup 3 EMC flash drive

From Red Hat Linux box, we have 3 EMC flash internal cards as fioa/fiob/fioc.

 

I/O stat monitoring

Finally, we can check i/o rates from 3 flash devices.

Rey Wang
Author: Rey Wang

Leave a Reply

Your email address will not be published. Required fields are marked *