My question, in short, is this: Does COMMIT read log pages?
I think ASE just writes dirty log pages, and it shouldn't go
back to read old log pages belonging to the transaction
We ask this question in order to configure the correct size for the
named cache syslogs is bound to. For big or long transactions, reading
old log pages would require a larger cache in order to keep those
pages in cache.
Some observations, though, seem to negate my thought. We have syslogs
bound to a small named cache with a wash area proportionally large.
During long and heavy transactions, log pages should cross soon the
wash marker, forcing ASE to write them to disk.This should ensure that
log pages are written to disk at a regular pace. When the program
executes COMMIT, few log pages should remain dirty; moreoless, the
pages before the wash marker. But it's common that, when commiting a
huge transaction involving hundreths of log pages, sysprocesses shows
a large number of physical_io, and the COMMIT takes minutes. We wonder
if ASE, in addition to writes, is reading old log pages (which were
flushed out of cache since the cache is small).
This is ASE 12.5.3 64-bit on HP-UX. Our database has a Replication
Agent. We know that our small log cache can place a performance problem
for the RepAgent, because it may read pages flushed out of cache if
RepAgent is not reading the log fast enough. I mean, during peak
periods a high number of new log pages are pushed into the log cache,
flushing old pages out; log pages stay in cache only a few seconds, so
RepAgent had better reading them in these few seconds, while in
memory, or it will have to read them from disk.
In Historical Server disk statistics, the log devices show sometimes a
high number of device reads. But not always; they also show "good"
periods with many writes and very few reads.
But there are other explanations for those reads: the RepAgent, a
large deferred update or a rollback (we have few rollbacks).