In the sys_rpt, we see "db file extend" as one of the waits consuming some (though not significant) time.
In Oracle, we can pre-allocate space to the datafile (and its a recommended practice to gain better performance). Can we do something similar in EDB so that the server process does not have to wait to extend the file?
No we have no pre-allocation of space for data files like in oracle. But it should not be consumed. I hope the disks are faster. What are the "COUNT", "WAIT TIME" and "% WAIT", you are getting?
Thanks for your reply.
You mentioned "But it should not be consumed", can you kindly elaborate, what did you mean by that?
The values are:
WAIT NAME COUNT WAIT TIME % WAIT
db file read 59 0.158183 95.11
query plan 17546959 0.003489 2.10
db file extend 242 0.002933 1.76
other lwlock acquire 24954 0.001458 0.88
wal flush 2697422 0.000094 0.06
wal write 1342851 0.000091 0.05
wal file sync 1342917 0.000071 0.04
I understand that the total wait time is less than a second in a report of 20min, which is very negligible, but that 95.11% is somewhat perplexing. We are doing a load test and see that EDB is consuming 50% more cpu as compared to Oracle for the same (infact a little less) workload.
Instead of pre-allocating db file size in oracle, EDB has a dynamic db file extend mechanism which will automatically increase the db file size.Whenever a table exceeds 1 GB. It is divided into gigabyte-sized segments. The first segment's file name is the same as the filenode(fn); subsequent segments are named fn.1, fn.2, etc. This arrangement avoids problems on platforms that have file size limitations.
Are you facing more than 90% wait time every time or it was just a rare occurrence?
Oracle also has dynamic extend mechanism (more correctly referred to as autoextend) but basically that increases latency because the transaction has to wait while extension is going on and hence its generally preferred to pre-allocate.
I'll check if its everytime. But should we really bother given that the total time in absolute terms was less than a second?
The "wait time" is the stats time between the snapshots you have taken, and the %wait. In this case, 95% of time has been taken for "db file read" and the actual time is 0.158183 ms. i.e., for getting the stats it has consumed 95% of total time of the 7 rows in the query output.
So nothing to bother about the "db file extend" and "db file read" too.