Does COMMIT read old log pages?

[Mariano Corral]

Dear group,

my question, in short, is this: Does COMMIT read log pages?
I think ASE doesn't do that; that is, for a COMMIT it only flushes
URL
to log cache and dirty log pages in cache to disk. This means
writing,
not reading.

But, for years, we have seen cases where ASE apparently read thousands
of log pages during commit. We clearly saw it twice. There was a
large transaction commiting and we followed it by looking at
sysprocesses. It shown the connection in COMMIT TRANSACTION during
two hours and physical_io reached one million. syslogs is bound to
a named cache having room for only 150,000 pages; consequently, it
couldn't be that those one million pages were dirty. In other words,
those I/O couldn't be writes. They had to be reads and everything
points to ASE reading most of the database's transaction log generated
since transaction begin, of which only a small part was generated
by our transaction. Unfortunately for us, that transaction ran in
parallel with a process generating huge amounts of log in thousands
of small transactions.

This is ASE 12.5.4 64-bit on HP-UX. Our database has a Replication
Agent. We know that our not-so-large log cache can be 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 may stay in cache only a few
seconds, so RepAgent had better reading them in those few seconds,
while in memory, or it will have to read them from disk.

Maybe we may reconsider our logiosize or the configuration of the
named cache syslogs is bound to. But they would not require any
change if ASE doesn't read old log pages during a COMMIT. ¿Does
anyone know why is this happening? ¿Anything we can do about it?

Thanks in advance,
Mariano Corral

[Antony]

RepAgent will only read up to the last committed transaction. If you have a large open transaction, those log records (and log records for any parallel transactions) will not have been read by RepAgent. When you commit that large transaction, RepAgent will then read all the (now committed) log records. So it's not the COMMIT that is doing all the reads - it's RepAgent.