Playing around with my benchmark script and found the following results
on one of my servers. The test details can be found below.=20
Question:=20
-> In test 5 logging is disabled but I "log file parallel write" becomes
primary wait, the only action that really improved was the DELETE ALL
FROM TABLE. How do you "tune/reduce" this wait event? Does this relate
to CPU speed at this point?
Observation:
-> dbwr_io_slaves introduced quite a bit of more wait from "io done" but
did not effect overall run time of the benchmark.=20
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D
Ran 4 tests
Ran as is.
change db_writer_processes from 1 - 6, much faster
tried dbwr_io_slaves=3D6 - pretty much same results as #2
same as 2 plus _disable_logging=3Dtrue
The benchmark includes the followings tests and these represent the
general times for 2-4 with the exception of the DELETE TABLE which was
much fast with _disable_logging =3D true in test #4.
INSERT APPEND INTO BENCHMARK : 00:00:41.47
GATHER TABLES STATS : 00:00:57.13
CREATE TABLE LOGGING : 00:00:10.60
UPDATE COLUMN : 00:03:37.02
DELETE ALL FROM TABLE : 00:01:46.30
DROP TABLE : 00:03:26.47
CREATE TABLE NOLOGGING : 00:00:10.14
INSERT/COMMIT LOOP : 00:00:04.65
BULK UPDATE : 00:05:20.53
TRUNCATE TABLE : 00:00:06.14
ADD COLUMN NUMERIC DEFAULT : 00:02:55.60
ADD COLUMN CHARACTER DEFAULT : 00:05:52.33
DROP NUMERIC COLUMN : 00:04:20.83
DROP CHARACTER COLUMN : 00:05:35.75
HARD PARSE : 00:00:21.02
The interesting thing is to look at the changes in wait times from
Statspack.
TEST 1
Avg
Total Wait wait
Waits
Event Waits Timeouts Time (s) (ms)
/txn
---------------------------- ------------ ---------- ---------- ------
--------
log file switch (checkpoint 3,936 3,905 3,941 1001
0.4
buffer busy waits 670 494 1,372 2047
0.1
log file parallel write 13,593 0 305 22
1.3
local write wait 39 36 37 943
0.0
control file parallel write 1,360 0 29 22
0.1
TEST 2
Avg
Total Wait wait
Waits
Event Waits Timeouts Time (s) (ms)
/txn
---------------------------- ------------ ---------- ---------- ------
--------
log file switch (checkpoint 998 980 995 997
0.1
buffer busy waits 316 211 442 1399
0.0
log file parallel write 13,245 0 299 23
1.3
db file sequential read 15,574 0 24 2
1.5
control file parallel write 863 0 23 26
0.1
TEST 3
Avg
Total Wait wait
Waits
Event Waits Timeouts Time (s) (ms)
/txn
---------------------------- ------------ ---------- ---------- ------
--------
io done 537,831 46 2,211 4
53.3
log file switch (checkpoint 1,178 1,150 1,169 992
0.1
buffer busy waits 262 181 440 1680
0.0
log file parallel write 13,503 0 256 19
1.3
control file parallel write 850 0 21 24
0.1
TEST 4
Avg
Total Wait wait
Waits
Event Waits Timeouts Time (s) (ms)
/txn
---------------------------- ------------ ---------- ---------- ------
--------
log file parallel write 373,088 41 1,417 4
37.0
log file switch (checkpoint 896 881 893 997
0.1
buffer busy waits 143 90 270 1889
0.0
log buffer space 2,418 0 54 22
0.2
control file parallel write 882 0 23 26
0.1
db file sequential read 16,102 0 22 1
1.6
