Unpredictable Query Performance !
Sometimes I face performance disparity for all my queries under similar circumstances.
Currently i have up to 12 projections and I am measuring the execution time of 180 queries and I was able to reach to a baseline where all queries take between 1 - 3 seconds expect very few taking longer. However I am trying to reach to an explanation why at certain times most of the queries take less than a second while few others take between 1 - 2 seconds; does it has to do with Vertica loading the database into memory? If yes, then how i can instruct Vertica to do so?
As an exmaple, I did the following procedure on a clean db:
- Create single table, encoding RLE for group by dimensions...
- Load one month data into my table using COPY AUTO; raw data size is 8GB and compressed data size is 4GB
- Update certain columns
- Purge()
- Create all my 12 projections, encoding RLE for group by dimensions
- Refresh() and Analyze_statistics();
All the sudden, all queries started to take a remarkable less time, however, once i rebooted the system, am back to my performance baseline...
I tried to reproduce few times without luck.... [UPDATE] Actually I was able to reproduce the behavior once instructed the database designer to advise on optimized design, and once the new design is refreshed, queries response time went below 1s, yet once rebooted, am back to orig. baseline!!
I am using 3 memory-optimized AWS instances...
Please advise if you have a scientific explanation for such behavior...
Shadi
Currently i have up to 12 projections and I am measuring the execution time of 180 queries and I was able to reach to a baseline where all queries take between 1 - 3 seconds expect very few taking longer. However I am trying to reach to an explanation why at certain times most of the queries take less than a second while few others take between 1 - 2 seconds; does it has to do with Vertica loading the database into memory? If yes, then how i can instruct Vertica to do so?
As an exmaple, I did the following procedure on a clean db:
- Create single table, encoding RLE for group by dimensions...
- Load one month data into my table using COPY AUTO; raw data size is 8GB and compressed data size is 4GB
- Update certain columns
- Purge()
- Create all my 12 projections, encoding RLE for group by dimensions
- Refresh() and Analyze_statistics();
All the sudden, all queries started to take a remarkable less time, however, once i rebooted the system, am back to my performance baseline...
I tried to reproduce few times without luck.... [UPDATE] Actually I was able to reproduce the behavior once instructed the database designer to advise on optimized design, and once the new design is refreshed, queries response time went below 1s, yet once rebooted, am back to orig. baseline!!
I am using 3 memory-optimized AWS instances...
Please advise if you have a scientific explanation for such behavior...
Shadi
0