Effect of Partitioning Stored Procedure, Partitioned and Replicated Tables
Nov 24, 2016
I am trying to improve the VoltDB performance in terms of TPS for TPCC queries. Now I am using some trial and error method to get higher performance. The things which I have tried are:
1) I have replicated the tables, warehouse, item and district.
2) I have partitioned all other tables on column 'district id' rather than 'warehouse id'.
3) I have also partitioned all stored procedures. (Actually when I used all queries as stored procedures without partitioning I got worse TPS than using AdHoc queries. But as soon as I partitioned the stored procedures as recommended by warnings while VoltDB server startup, I got almost double TPS)
Hence my question is , is there any thumb rule for partitioning and replicating the tables, which column is used for partitioning and when to use AdHoc queries, Stored Procedures and Partitioned Stored Procedure? so that for large number of tables I can make the firm decision regarding all these facts.
1) I have replicated the tables, warehouse, item and district.
2) I have partitioned all other tables on column 'district id' rather than 'warehouse id'.
3) I have also partitioned all stored procedures. (Actually when I used all queries as stored procedures without partitioning I got worse TPS than using AdHoc queries. But as soon as I partitioned the stored procedures as recommended by warnings while VoltDB server startup, I got almost double TPS)
Hence my question is , is there any thumb rule for partitioning and replicating the tables, which column is used for partitioning and when to use AdHoc queries, Stored Procedures and Partitioned Stored Procedure? so that for large number of tables I can make the firm decision regarding all these facts.