Long commit time

Jesus M. Salvo Jr.

unread,

Apr 21, 2004, 4:01:24 AM4/21/04

to

When ones does large amounts of delete on DOL tables ( about 100,000 ), and the commit takes long to complete,
what is it a sign of ? .... and what can be done about it ?

Jesus M. Salvo Jr.

unread,

Apr 22, 2004, 7:51:50 PM4/22/04

to

I thought that a commit simply creates a new row in the syslog table saying something like "committed"
so that all transactions before it and after the last commit or rollback are recorded as committed.

Iann

unread,

Apr 23, 2004, 6:25:07 AM4/23/04

to

There is definitely more to it than that. I haven't done a lot of
tuning work with DOL tables, but with all-pages locking a commit can
take absolutely forever when a large number of page splits takes place
in a transaction. All those pages have to be flushed to disk at the
commit, you know, data integrity and all that. I'm sure something very
similar happens with DOL although I can't describe the details for you.

--ian

Bret Halford

unread,

Apr 23, 2004, 9:20:45 AM4/23/04

to

Well, the data pages don't need to be flushed to disk at the commit,
but all the log records for the entire transaction do need to
be flushed. The "commit tran" command itself does only generate
one log record - but before it is written all the other log records
for that transaction must be written as well.

The datapages do not need to be flushed at this point, if ASE
is shut down abruptly, any committed changes can be recovered
from older images of the page plus the log records.

If you have more frequent checkpoints, more of the log records will
have been flused to disk earlier. It is possible that you may have
sp_configure "recovery interval" set high, which causes less frequent
checkpoints.

You can also bind syslogs to a smaller cache so that there can be fewer
unflushed log records in memory at any given time, which would speed
up commits.

-bret

Jesus M. Salvo Jr.

unread,

May 3, 2004, 2:22:19 AM5/3/04

to

Iann wrote:
> There is definitely more to it than that. I haven't done a lot of
> tuning work with DOL tables, but with all-pages locking a commit can
> take absolutely forever when a large number of page splits takes place
> in a transaction. All those pages have to be flushed to disk at the
> commit, you know, data integrity and all that. I'm sure something very
> similar happens with DOL although I can't describe the details for you.
>

I dont know what I was thinking back then when I asked that question .... must have been very tired.

Iann

unread,

May 3, 2004, 5:26:28 AM5/3/04

to

Possibly :) However, I can remember being very confused when I first
saw this sort of behaviour. At times, a large transaction would commit
almost instantly, and at other times a transaction affecting a similar
number of rows would take forever at the commit phase. Very easy to
imagine that something was configured wrong, or bottlenecking very
badly. Turned out I was inserting to a lot of different 100% full pages
and really piling up the volume of data recorded in the transaction log.

--ian

Jesus M. Salvo Jr. wrote: