Data Warehouse Performance Tuning
It's
been a while since I've worked in a data Warehouse environment, so I was
wondering if someone could help me with some ONCONFIG parameter settings. As
much as I can remember, they don't look correct. We are on a dedicated,
64-bit, 4-CPU HP server w/ 1 GB of RAM, of which 834M is SHM. Here are the
parameters I think I should be concerned with, at least at this point:
MULTIPROCESSOR 0 # 0 for single-processor, 1 for multi-processor
NUMCPUVPS 2 # Number of user (cpu) vps
BUFFERS 200000 # Maximum number of shared buffers
SHMVIRTSIZE 200000 # initial virtual shared memory segment size
DS_TOTAL_MEMORY 512 # Decision support memory (Kbytes)
Since we have 4 cpu's, MULTIPROCESSOR s/b 1.
For a DW environment, the BUFFERS setting should be much lower than
SHMVIRTSIZE. I think theer is a ratio, but I can't remember what it is.
DS_TOTAL_MEMORY s/b a lot higher than 512K. What should it be?
Thanks for your help.
Kevin Struckhoff
Data Warehouse Consultant
Yamaha Motor Inc.
Kevin_Struckhoff@yamaha-motor.com
The
Performance Guide recommends that, for pure DSS, you should set
DS_TOTAL_MEMORY to 90% of SHMTOTAL. The more OLTP work you do (as
opposed to DSS), the lower your DS_TOTAL_MEMORY.
You will have to experiment with BUFFERS. Index reads and UPDATE
STATISTICS use BUFFERS, but setting BUFFERS to a relatively low value
encourages light scans, which is desirable in DSS work.
HTH
Joe Vidrine
-----Original Message-----
From: KEVIN STRUC.... [mailto:kevinstruckhoff@yahoo.com]
Sent: Wednesday, May 21, 2003 2:02 PM
To: ids@iiug.org
Subject: Data Warehouse Performance Tuning [1200]
It's been a while since I've worked in a data Warehouse environment, so
I was wondering if someone could help me with some ONCONFIG parameter
settings. As much as I can remember, they don't look correct. We are on
a dedicated, 64-bit, 4-CPU HP server w/ 1 GB of RAM, of which 834M is
SHM. Here are the parameters I think I should be concerned with, at
least at this point:
MULTIPROCESSOR 0 # 0 for single-processor, 1 formulti-processor
NUMCPUVPS 2 # Number of user (cpu) vps
BUFFERS 200000 # Maximum number of shared buffers
SHMVIRTSIZE 200000 # initial virtual shared memory segmentsize
DS_TOTAL_MEMORY 512 # Decision support memory (Kbytes)
Since we have 4 cpu's, MULTIPROCESSOR s/b 1.
For a DW environment, the BUFFERS setting should be much lower than
SHMVIRTSIZE. I think theer is a ratio, but I can't remember what it is.
DS_TOTAL_MEMORY s/b a lot higher than 512K. What should it be?
Thanks for your help.
Kevin Struckhoff
Data Warehouse Consultant
Yamaha Motor Inc.
Kevin_Struckhoff@yamaha-motor.com