Good suggestions, guys!
First, I modified the previously-posted test program to time the insertion rate
outside of the transaction (i.e. Kilnt's suggestion). I left the creation of
the table, however, outside of the timed insertion loop. In other words:
Create Table
Begin Timing
Begin Transaction
Loop Performing 10,000 Inserts
Commit Transaction
End Timing
Second, I took Mike & Dan's suggestion and put a transaction around the queries
as well. In other words:
Begin Timing
Begin Transaction
Loop Performing 10,000 Queries
Commit Transaction
End Timing
When I run this modified version on the same four file systems as before, I
get:
/tmp 27198.7, 29177.2, 25465.3 Insertions/Second
/tmp 301.234, 300.551, 304.311 Queries/Second
~ 26596.9, 27185.5, 26012 Insertions/Second
~ 306, 303.746, 302.499 Queries/Second
NFS(1) 33789.5, 33675.7, 30738.6 Insertions/Second
NFS(1) 304.678, 305.742, 303.51 Queries/Second
NFS(2) 30446.6, 30528.9, 29344.3 Insertions/Second
NFS(2) 300.903, 304.742, 303.782 Queries/Second
These results are pretty much directly in line with what one would expect given
the hypothesis that poor NFS locking performance was causing the slowdown. Key
points:
1) Timing outside the transaction for inserts did, as expected, lower the
amortized insertion rate in each case.
2) Adding a transaction around the queries did, as expected, increase the
amortized query rate in every case.
3) Adding a transaction around the queries, thus locking only once, as
expected, brought the query rate on NFS up to the query rate on local disk
(assuming the single, poor, NFS lock is amortized across a sufficient number
of queries).
So my conclusion from all of this is that I need to do one of two things. I
either need to group my queries together and bracket them with a transaction,
or I need to try the suggestion yesterday of disabling the locking.
-- William Hachfeld
