Mariano Corral
2011-12-23 11:58:58 UTC
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
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