Hi,
I have a couple of questions about dbt2 performance.
1.
I tested dbt2+postgresql 8.4.2 on my server, but the NOTPM is around
only 320~390 with 10 connections and 30 warehouses. Increasing the
number of connections did not improve the throughput? The NOPTM number
does not seem very high to me. Should I try more configurations to see
if it can be improved? Are there any numbers I can compare with (NOPTM
and response time)?
2. Moreover, the disk utilization was high and the "await" time
from iostat is around 500 ms. Could disk I/O limit the overall
throughput? The server has 2 SATA disks, one for system and postgresql
and the other is dedicated to logging (pg_xlog). As far as I
understand, modern database systems should be CPU-bound rather than
I/O-bound, is it because I did not perform adequate performance tuning?
3. From "vmstat", the cpus spent around 72% of time idle, 25%
waiting for I/O, and only 2~3% left doing real work. I was surprised
that the cpu utilization was so low. Is that normal or could it be due
to misconfiguration? In my opinion, even if disk I/O may have been
stressed, 70% of idle time was still too high.
Below are some specs/configurations that I used. Any suggestion is welcome. Thanks!
server spec:
4 cores (2*Dual-Core AMD Opteron, 800MHz), 12GB ram
2 SATA disks, one for system and postgresql and the other is dedicated to logging (pg_xlog)
postgres configuration:
30 warehouses
256MB shared_buffer
768MB effective_cache_size
checkpoint_timeout 1hr (All my tests are within 10 minutes interval, so checkpointing should not interfere the performance)
I turned off fsync to see whether the performance could be improved.
Yu-Ju
