Hi everyone,
Some more data I've collected, trying to best tune dbt-2 with
8.0beta4. Was hoping for some suggestions, explanations for what I'm
seeing, etc.
A review of hardware I've got:
4 x 1.5Ghz Itanium 2
16GB memory
84 15K RPM disks (6 controlers, 12 channels)
Physical Database table layout (using LVM2 for tables using more than 1 disk):
- warehouse 2 disks
- district 2 disks
- order_line 2 disks
- customer 4 disks
- stock 12 disks
- log 12 disks
- orders 2 disks
- new_order 2 disks
- history 1 disk
- item 1 disk
- index1 1 disk
- index2 1 disk
All these tests are using a 500 warehouse database.
Test 1: http://www.osdl.org/projects/dbt2dev/results/dev4-010/188/
Metric: 3316
DB parameter changes from default:
bgwriter_percent | 10
checkpoint_timeout | 300
checkpoint_segments | 800
checkpoint_timeout | 1800
default_statistics_target | 1000
max_connections | 140
stats_block_level | on
stats_command_string | on
stats_row_level | on
wal_buffers | 128
wal_sync_method | fsync
work_mem | 2048
Test 2: http://www.osdl.org/projects/dbt2dev/results/dev4-010/189/
Metric: 3261 -1.7% decrease Test 1
DB parameter changes from Test 1:
shared_buffers | 60000
Noted changes:
The block read for the customer table decreases significantly according
to the database.
Test 3: http://www.osdl.org/projects/dbt2dev/results/dev4-010/190/
Metric: 3261 0% change from Test 2
DB parameter changes from Test 2:
effective_cache_size | 220000
Noted changes:
No apparent changes according to the charts.
Test 4: http://www.osdl.org/projects/dbt2dev/results/dev4-010/191/
Metric: 3323 1.9 increase from Test 3
DB parameter changes from Test 3:
checkpoint_segments | 1024
effective_cache_size | 1000
Noted Changes:
The increased checkpoint_segments smothed out the throughput and
other i/o related stats.
Test 5: http://www.osdl.org/projects/dbt2dev/results/dev4-010/192/
Metric: 3149 -5% decrease from Test 4
DB parameter changes from Test 4:
shared_buffers | 80000
Noted changes:
The graphs are starting to jump around a bit. I figure 80,000
shared_buffers is too much.
Test 6: http://www.osdl.org/projects/dbt2dev/results/dev4-010/193/
Metric: 3277 4% increase from Test 5
DB parameter changes from Test 5:
random_page_cost | 2
shared_buffers | 60000
Noted changes:
Reducing the shared_buffers to the smoother performance found in Test 4
seemed to have disrupted by decreasing the random_page_cost to 2.
