ASE Database forum - Reducing Tx Management by Single Log Record when ULC size = @maxpagesize

Author

Reducing Tx Management by Single Log Record when ULC size = @maxpagesize

Jesus M. Salvo Jr.

2005-10-27, 8:21 am

Based on the output from sp_sysmon, the docs and sp_sysmon output recommend
to reduce the "user log cache size" if it is greater than the logical database page size.

However, my "user log cache size" is = logical database page size.
So what else can one do to reduce the "Single Log Record" flush ??

ASE 12.5.3 ESD#1

1> select @@maxpagesize
2> go

-----------
4096

(1 row affected)

1> sp_configure 'user log cache size'
2> go
Parameter Name Default Memory Used Config Value Run Value Unit Type
------------------------------ ----------- ----------- ------------ ----------- -------------------- ----------
user log cache size 4096 0 4096 4096 bytes static

Transaction Management
----------------------

ULC Flushes to Xact Log per sec per xact count % of total
------------------------- ------------ ------------ ---------- ----------
by Full ULC 0.6 0.0 37 0.2 %
by End Transaction 37.0 0.6 2221 12.9 %
by Change of Database 0.0 0.0 1 0.0 %
by Single Log Record 151.5 2.7 9088 52.8 %
by Unpin 0.4 0.0 21 0.1 %
by Other 97.6 1.7 5856 34.0 %
------------------------- ------------ ------------ ----------
Total ULC Flushes 287.1 5.0 17224

ULC Log Records 497.0 8.7 29822 n/a
Max ULC Size During Sample n/a n/a 4096 n/a

ULC Semaphore Requests
Granted 1407.7 24.7 84462 100.0 %
Waited 0.0 0.0 2 0.0 %
------------------------- ------------ ------------ ----------
Total ULC Semaphore Req 1407.7 24.7 84464

Log Semaphore Requests
Granted 213.5 3.7 12810 99.9 %
Waited 0.2 0.0 13 0.1 %
------------------------- ------------ ------------ ----------
Total Log Semaphore Req 213.7 3.7 12823

Transaction Log Writes 21.3 0.4 1277 n/a
Transaction Log Alloc 12.7 0.2 763 n/a
Avg # Writes per Log Page n/a n/a 1.67366 n/a

Tuning Recommendations for Transaction Management
-------------------------------------------------
- Consider decreasing the 'user log cache size'
configuration parameter if it is greater than the
logical database page size.

Mark A. Parsons

2005-10-27, 8:21 am

You have to take the sp_sysmon tuning recommendations with a grain of salt.

You could run sp_sysmon during a period of heavy OLTP activity ... then
during a period of heavy DSS activity ... then during a period of
relative quiet ... and get different 'recommendations'.

The sp_sysmon recommendations are based on some very basic/simplistic
measurements.

In your situation sp_sysmon sees that the 'single log record' count is
(relatively) high. So sp_sysmon is stating that (during this particular
run of sp_sysmon) your ULC is larger than is necessary for most
transactions, so it recommends that you reduce the ULC size.

OK, but *how much* should you reduce your ULC by?

At this point sp_sysmon 'punts' and just gives you a canned suggestion
.... reduce ULC if it's bigger than the logical page size. (HINT:
sp_sysmon didn't actually do any comparisons of ULC vs logical page
size.) Also notice use of the word 'if' ... *IF* it (ULC) is greater
than the logical page size.

Since you're ULC *IS* the same size as your logical page size, this
recommendation really doesn't apply in this case.

You're welcome to try to reduce your ULC further (to 2048?) but you
should find that you get an error ... that ULC cannot be smaller than
your logical page size. In other words, the ULC has to be defined as at
least 1 whole (logical) page ... 4K in this case.

The point is that the sp_sysmon recommendations are there to help jog
your memory ... to give you some ideas that *might* apply (based on this
particular run of sp_sysmon). Ultimately it's up to the DBA to decide
if the recommendation is *really* applicable. In this case, the
sp_symon recommendation isn't *really* applicable.

Jesus M. Salvo Jr. wrote:

> Based on the output from sp_sysmon, the docs and sp_sysmon output recommend
> to reduce the "user log cache size" if it is greater than the logical database page size.
>
> However, my "user log cache size" is = logical database page size.
> So what else can one do to reduce the "Single Log Record" flush ??
>
> ASE 12.5.3 ESD#1
>
> 1> select @@maxpagesize
> 2> go
>
> -----------
> 4096
>
> (1 row affected)
>
> 1> sp_configure 'user log cache size'
> 2> go
> Parameter Name Default Memory Used Config Value Run Value Unit Type
> ------------------------------ ----------- ----------- ------------ ----------- -------------------- ----------
> user log cache size 4096 0 4096 4096 bytes static
>
>
>
>
>
> Transaction Management
> ----------------------
>
> ULC Flushes to Xact Log per sec per xact count % of total
> ------------------------- ------------ ------------ ---------- ----------
> by Full ULC 0.6 0.0 37 0.2 %
> by End Transaction 37.0 0.6 2221 12.9 %
> by Change of Database 0.0 0.0 1 0.0 %
> by Single Log Record 151.5 2.7 9088 52.8 %
> by Unpin 0.4 0.0 21 0.1 %
> by Other 97.6 1.7 5856 34.0 %
> ------------------------- ------------ ------------ ----------
> Total ULC Flushes 287.1 5.0 17224
>
> ULC Log Records 497.0 8.7 29822 n/a
> Max ULC Size During Sample n/a n/a 4096 n/a
>
> ULC Semaphore Requests
> Granted 1407.7 24.7 84462 100.0 %
> Waited 0.0 0.0 2 0.0 %
> ------------------------- ------------ ------------ ----------
> Total ULC Semaphore Req 1407.7 24.7 84464
>
> Log Semaphore Requests
> Granted 213.5 3.7 12810 99.9 %
> Waited 0.2 0.0 13 0.1 %
> ------------------------- ------------ ------------ ----------
> Total Log Semaphore Req 213.7 3.7 12823
>
> Transaction Log Writes 21.3 0.4 1277 n/a
> Transaction Log Alloc 12.7 0.2 763 n/a
> Avg # Writes per Log Page n/a n/a 1.67366 n/a
>
> Tuning Recommendations for Transaction Management
> -------------------------------------------------
> - Consider decreasing the 'user log cache size'
> configuration parameter if it is greater than the
> logical database page size.
>
>

Sponsored Links