Ian wrote:
dunleav1 wrote:
I already had logbufsz set to 4096.
I set mincommit to 1 and reran my test and the throughput was horrible.
This feels like a troll, now.
If you're serious about fixing the problem, then let's see some real
data, full snapshots (not excerpts).
Otherwise, please go away.
I'm not a troll.
I thought it would be ugly to post the whole snapshot but here you go.
get snapshot for all databases
Database Snapshot
Database name = B004
Database path = C:\DB2\NODE0000\SQL00002\
Input database alias =
Database status = Active
Catalog database partition number = 0
Catalog network node name =
Operating system running at database server= NT
Location of the database = Local
First database connect timestamp = 07/05/2006 09:26:16.534543
Last reset timestamp = 07/05/2006 09:52:37.897631
Last backup timestamp = 06/23/2006 03:00:01.000000
Snapshot timestamp = 07/05/2006 09:55:00.039582
Number of automatic storage paths = 0
High water mark for connections = 31
Application connects = 5
Secondary connects total = 0
Applications connected currently = 5
Appls. executing in db manager currently = 0
Agents associated with applications = 5
Maximum agents associated with applications= 31
Maximum coordinating agents = 31
Locks held currently = 0
Lock waits = 0
Time database waited on locks (ms) = Not Collected
Lock list memory in use (Bytes) = 3760
Deadlocks detected = 0
Lock escalations = 0
Exclusive lock escalations = 0
Agents currently waiting on locks = 0
Lock Timeouts = 0
Number of indoubt transactions = 0
Total Private Sort heap allocated = 0
Total Shared Sort heap allocated = 0
Shared Sort heap high water mark = 0
Total sorts = 1
Total sort time (ms) = 0
Sort overflows = 0
Active sorts = 0
Buffer pool data logical reads = 49456
Buffer pool data physical reads = 4
Buffer pool temporary data logical reads = 0
Buffer pool temporary data physical reads = 0
Asynchronous pool data page reads = 0
Buffer pool data writes = 4813
Asynchronous pool data page writes = 4643
Buffer pool index logical reads = 283454
Buffer pool index physical reads = 4
Buffer pool temporary index logical reads = 0
Buffer pool temporary index physical reads = 0
Asynchronous pool index page reads = 0
Buffer pool index writes = 899
Asynchronous pool index page writes = 892
Total buffer pool read time (milliseconds) = 25
Total buffer pool write time (milliseconds)= 414
Total elapsed asynchronous read time = 0
Total elapsed asynchronous write time = 363
Asynchronous data read requests = 0
Asynchronous index read requests = 0
No victim buffers available = 0
LSN Gap cleaner triggers = 0
Dirty page steal cleaner triggers = 0
Dirty page threshold cleaner triggers = 0
Time waited for prefetch (ms) = 0
Unread prefetch pages = 0
Direct reads = 812
Direct writes = 1280
Direct read requests = 366
Direct write requests = 5
Direct reads elapsed time (ms) = 50
Direct write elapsed time (ms) = 9
Database files closed = 0
Data pages copied to extended storage = 0
Index pages copied to extended storage = 0
Data pages copied from extended storage = 0
Index pages copied from extended storage = 0
Host execution elapsed time = Not Collected
Commit statements attempted = 28026
Rollback statements attempted = 0
Dynamic statements attempted = 29840
Static statements attempted = 28026
Failed statement operations = 0
Select SQL statements executed = 125
Update/Insert/Delete statements executed = 28988
DDL statements executed = 0
Inactive stmt history memory usage (bytes) = 0
Internal automatic rebinds = 0
Internal rows deleted = 0
Internal rows inserted = 0
Internal rows updated = 0
Internal commits = 1
Internal rollbacks = 0
Internal rollbacks due to deadlock = 0
Rows deleted = 0
Rows inserted = 28750
Rows updated = 238
Rows selected = 150
Rows read = 771
Binds/precompiles attempted = 0
Log space available to the database (Bytes)= 4175848011
Log space used by the database (Bytes) = 230551989
Maximum secondary log space used (Bytes) = 0
Maximum total log space used (Bytes) = 563699214
Secondary logs allocated currently = 0
Log pages read = 0
Log read time (sec.ns) = 0.000000004
Log pages written = 91478
Log write time (sec.ns) = 33.000000004
Number write log IOs = 91421
Number read log IOs = 0
Number partial page log IOs = 28056
Number log buffer full = 0
Log data found in buffer = 0
Appl id holding the oldest transaction = 7
Log to be redone for recovery (Bytes) = 323343002
Log accounted for by dirty pages (Bytes) = 323343002
File number of first active log = 4
File number of last active log = 3
File number of current active log = 5
File number of log being archived = Not applicable
Package cache lookups = 29109
Package cache inserts = 6
Package cache overflows = 0
Package cache high water mark (Bytes) = 859052
Application section lookups = 29840
Application section inserts = 6
Catalog cache lookups = 8
Catalog cache inserts = 2
Catalog cache overflows = 0
Catalog cache high water mark = 0
Workspace Information
Shared high water mark = 0
Corresponding shared overflows = 0
Total shared section inserts = 0
Total shared section lookups = 0
Private high water mark = 345658
Corresponding private overflows = 0
Total private section inserts = 6
Total private section lookups = 29107
Number of hash joins = 0
Number of hash loops = 0
Number of hash join overflows = 0
Number of small hash join overflows = 0
get snapshot for dbm
Database Manager Snapshot
Node name =
Node type = Enterprise Server
Edition with local and remote clients
Instance name = DB2
Number of database partitions in DB2 instance = 1
Database manager status = Active
Product name = DB2 v8.1.11.973
Service level = s060120 (WR21365)
Private Sort heap allocated = 0
Private Sort heap high water mark = 36049
Post threshold sorts = 0
Piped sorts requested = 1
Piped sorts accepted = 1
Start Database Manager timestamp = 07/05/2006
09:26:00.129033
Last reset timestamp = 07/05/2006
09:52:37.897631
Snapshot timestamp = 07/05/2006
09:55:09.818480
Remote connections to db manager = 4
Remote connections executing in db manager = 0
Local connections = 1
Local connections executing in db manager = 0
Active local databases = 1
High water mark for agents registered = 32
High water mark for agents waiting for a token = 0
Agents registered = 32
Agents waiting for a token = 0
Idle agents = 14
Committed private Memory (Bytes) = 136642560
Switch list for db partition number 0
Buffer Pool Activity Information (BUFFERPOOL) = ON 07/05/2006
09:51:29.282932
Lock Information (LOCK) = ON 07/05/2006
09:26:16.999227
Sorting Information (SORT) = ON 07/05/2006
09:52:03.069922
SQL Statement Information (STATEMENT) = OFF
Table Activity Information (TABLE) = OFF
Take Timestamp Information (TIMESTAMP) = ON 07/05/2006
09:26:00.129033
Unit of Work Information (UOW) = OFF
Agents assigned from pool = 209
Agents created from empty pool = 34
Agents stolen from another application = 0
High water mark for coordinating agents = 32
Max agents overflow = 0
Hash joins after heap threshold exceeded = 0
Total number of gateway connections = 0
Current number of gateway connections = 0
Gateway connections waiting for host reply = 0
Gateway connections waiting for client request = 0
Gateway connection pool agents stolen = 0
Memory usage for database manager:
Memory Pool Type = Database Monitor Heap
Current size (bytes) = 229376
High water mark (bytes) = 393216
Configured size (bytes) = 491520
Memory Pool Type = Other Memory
Current size (bytes) = 9961472
High water mark (bytes) = 14237696
Configured size (bytes) = 3991928832
Memory Pool Type = Backup/Restore/Util
Heap
Current size (bytes) = 16384
High water mark (bytes) = 16384
Configured size (bytes) = 368984064
Memory Pool Type = Package Cache Heap
Current size (bytes) = 950272
High water mark (bytes) = 950272
Configured size (bytes) = 3991928832
Memory Pool Type = Catalog Cache Heap
Current size (bytes) = 671744
High water mark (bytes) = 671744
Configured size (bytes) = 3991928832
Memory Pool Type = Buffer Pool Heap
Current size (bytes) = 514129920
High water mark (bytes) = 514129920
Configured size (bytes) = 3991928832
Memory Pool Type = Buffer Pool Heap
Current size (bytes) = 1028194304
High water mark (bytes) = 1028194304
Configured size (bytes) = 3991928832
Memory Pool Type = Buffer Pool Heap
Current size (bytes) = 103022592
High water mark (bytes) = 103022592
Configured size (bytes) = 3991928832
Memory Pool Type = Buffer Pool Heap
Current size (bytes) = 655360
High water mark (bytes) = 655360
Configured size (bytes) = 3991928832
Memory Pool Type = Buffer Pool Heap
Current size (bytes) = 393216
High water mark (bytes) = 393216
Configured size (bytes) = 3991928832
Memory Pool Type = Buffer Pool Heap
Current size (bytes) = 262144
High water mark (bytes) = 262144
Configured size (bytes) = 3991928832
Memory Pool Type = Buffer Pool Heap
Current size (bytes) = 196608
High water mark (bytes) = 196608
Configured size (bytes) = 3991928832
Memory Pool Type = Lock Manager Heap
Current size (bytes) = 278528
High water mark (bytes) = 278528
Configured size (bytes) = 294912
Memory Pool Type = Database Heap
Current size (bytes) = 5488640
High water mark (bytes) = 5488640
Configured size (bytes) = 35553280
get snapshot for all bufferpools
Bufferpool Snapshot
Bufferpool name = IBMDEFAULTBP
Database name = B004
Database path = C:\DB2\NODE0000\SQL00002\
Input database alias =
Snapshot timestamp = 07/05/2006 09:55:21.299562
Buffer pool data logical reads = 2695
Buffer pool data physical reads = 4
Buffer pool temporary data logical reads = 0
Buffer pool temporary data physical reads = 0
Buffer pool data writes = 388
Buffer pool index logical reads = 4191
Buffer pool index physical reads = 4
Buffer pool temporary index logical reads = 0
Buffer pool temporary index physical reads = 0
Total buffer pool read time (milliseconds) = 25
Total buffer pool write time (milliseconds)= 58
Asynchronous pool data page reads = 0
Asynchronous pool data page writes = 195
Buffer pool index writes = 14
Asynchronous pool index page reads = 0
Asynchronous pool index page writes = 6
Total elapsed asynchronous read time = 0
Total elapsed asynchronous write time = 0
Asynchronous data read requests = 0
Asynchronous index read requests = 0
No victim buffers available = 0
Direct reads = 914
Direct writes = 1536
Direct read requests = 417
Direct write requests = 6
Direct reads elapsed time (ms) = 51
Direct write elapsed time (ms) = 11
Database files closed = 0
Data pages copied to extended storage = 0
Index pages copied to extended storage = 0
Data pages copied from extended storage = 0
Index pages copied from extended storage = 0
Unread prefetch pages = 0
Vectored IOs = 0
Pages from vectored IOs = 0
Block IOs = 0
Pages from block IOs = 0
Physical page maps = 0
Node number = 0
Tablespaces using bufferpool = 5
Alter bufferpool information:
Pages left to remove = 0
Current size = 24402
Post-alter size = 24402
Bufferpool Snapshot
Bufferpool name = FLINT
Database name = B004
Database path = C:\DB2\NODE0000\SQL00002\
Input database alias =
Snapshot timestamp = 07/05/2006 09:55:21.299562
Buffer pool data logical reads = 54021
Buffer pool data physical reads = 0
Buffer pool temporary data logical reads = 0
Buffer pool temporary data physical reads = 0
Buffer pool data writes = 5401
Buffer pool index logical reads = 320956
Buffer pool index physical reads = 0
Buffer pool temporary index logical reads = 0
Buffer pool temporary index physical reads = 0
Total buffer pool read time (milliseconds) = 0
Total buffer pool write time (milliseconds)= 457
Asynchronous pool data page reads = 0
Asynchronous pool data page writes = 5401
Buffer pool index writes = 966
Asynchronous pool index page reads = 0
Asynchronous pool index page writes = 966
Total elapsed asynchronous read time = 0
Total elapsed asynchronous write time = 457
Asynchronous data read requests = 0
Asynchronous index read requests = 0
No victim buffers available = 0
Direct reads = 0
Direct writes = 0
Direct read requests = 0
Direct write requests = 0
Direct reads elapsed time (ms) = 0
Direct write elapsed time (ms) = 0
Database files closed = 0
Data pages copied to extended storage = 0
Index pages copied to extended storage = 0
Data pages copied from extended storage = 0
Index pages copied from extended storage = 0
Unread prefetch pages = 0
Vectored IOs = 0
Pages from vectored IOs = 0
Block IOs = 0
Pages from block IOs = 0
Physical page maps = 0
Node number = 0
Tablespaces using bufferpool = 1
Alter bufferpool information:
Pages left to remove = 0
Current size = 31250
Post-alter size = 31250
Bufferpool Snapshot
Bufferpool name = TEMP
Database name = B004
Database path = C:\DB2\NODE0000\SQL00002\
Input database alias =
Snapshot timestamp = 07/05/2006 09:55:21.299562
Buffer pool data logical reads = 0
Buffer pool data physical reads = 0
Buffer pool temporary data logical reads = 0
Buffer pool temporary data physical reads = 0
Buffer pool data writes = 0
Buffer pool index logical reads = 0
Buffer pool index physical reads = 0
Buffer pool temporary index logical reads = 0
Buffer pool temporary index physical reads = 0
Total buffer pool read time (milliseconds) = 0
Total buffer pool write time (milliseconds)= 0
Asynchronous pool data page reads = 0
Asynchronous pool data page writes = 0
Buffer pool index writes = 0
Asynchronous pool index page reads = 0
Asynchronous pool index page writes = 0
Total elapsed asynchronous read time = 0
Total elapsed asynchronous write time = 0
Asynchronous data read requests = 0
Asynchronous index read requests = 0
No victim buffers available = 0
Direct reads = 0
Direct writes = 0
Direct read requests = 0
Direct write requests = 0
Direct reads elapsed time (ms) = 0
Direct write elapsed time (ms) = 0
Database files closed = 0
Data pages copied to extended storage = 0
Index pages copied to extended storage = 0
Data pages copied from extended storage = 0
Index pages copied from extended storage = 0
Unread prefetch pages = 0
Vectored IOs = 0
Pages from vectored IOs = 0
Block IOs = 0
Pages from block IOs = 0
Physical page maps = 0
Node number = 0
Tablespaces using bufferpool = 1
Alter bufferpool information:
Pages left to remove = 0
Current size = 15625
Post-alter size = 15625
