Performance Tuning
Hi everyone,
I'm a new DBA in our company and I'm assigned to do some
tuning on one of our servers and I was hoping if you could help me.
Thanks in advance,
Lyzander Marantal
Environment:
SUN 1000E machine w/ 4 CPUs 512 MB RAM
Informix 7.12 UC3
Solaris 2.51
Number of users is approx. 130
Number of chunks = 60
Number of Logical Logs = 96 (1000 pages each)
ONCONFIG PARAMATERS
ROOTNAME rootdbsROOTPATH /informix/informix7.12/chunks/rootP
DBSERVERNAME imossfeMIRRORPATH /informix/informix7.12/chunks/rootM
PHYSDBS phylog
ROOTOFFSET 0
ROOTSIZE 35000MIRROR 1
MIRROROFFSET 0
PHYSFILE 20000
LOGFILES 96
LOGSIZE 1000
SERVERNUM 0
DEADLOCK_TIMEOUT 60
RESIDENT 0
LOCKS 64000
BUFFERS 1000
ONDBSPACEDOWN 0LBU_PRESERVE 0
OPCACHEMAX 0
PHYSBUFF 32
LOGBUFF 32LOGSMAX 96
CLEANERS 8
SHMBASE 0xa000000
CKPTINTVL 300
LRUS 8
LRU_MAX_DIRTY 60
LRU_MIN_DIRTY 50
SINGLE_CPU_VP 0
LTXHWM 50
LTXEHWM 60
TXTIMEOUT 300
NUMCPUVPS 2
DRAUTO 0
SHMVIRTSIZE 64000
USEOSTIME 0
NOAGE 0
AFF_SPROC 0
AFF_NPROCS 0
RA_PAGES 8
RA_THRESHOLD 4
NUMAIOVPS 64
DBSERVERALIASES imossfe_net
MULTIPROCESSOR 0
FILLFACTOR 90
STACKSIZE 32
DBSPACETEMP tmpspace1,tmpspace2STAGEBLOB
DRNODE 1
DRNAME
DRINTERVAL 30
DRTIMEOUT 30
DRLOSTFOUND /usr/informix/etc/dr.lostfound
OFF_RECVRY_THREADS 10
ON_RECVRY_THREADS 1
DUMPSHMEM 1
DUMPGCORE 1
DUMPCORE 1
DUMPCNT 1DUMPDIR /dba/dump_informix
DATASKIP offPDQPRIORITY 0
DS_MAX_QUERIES 4
DS_TOTAL_MEMORY 512
DS_MAX_SCANS 1048576
SHMADD 0x800000
SHMTOTAL 0x0
OPTCOMPIND 0
MAX_PDQPRIORITY 100
Profile
dskreads pagreads bufreads %cached dskwrits pagwrits bufwrits %cached
6589236 7746127 226129288 97.09 221851 424882 861439 74.25
isamtot open start read write rewrite delete commit rollbk
112002383 585536 2814436 99177046 118021 19101 100953 18590 17
ovlock ovuserthread ovbuff usercpu syscpu numckpts flushes
0 0 0 43012.30 7944.45 182 536
bufwaits lokwaits lockreqs deadlks dltouts ckpwaits compress seqscans
1428683 1103 672259072 0 0 190 18530 172077
ixda-RA idx-RA da-RA RA-pgsused lchwaits
2746074 797 2516807 5242910 2362751
Physical Logging
Buffer bufused bufsize numpages numwrits pages/io
P-1 6 16 80106 5154 15.54
phybegin physize phypos phyused %used
200035 10000 7417 166 1.66
Logical Logging
Buffer bufused bufsize numrecs numpages numwrits recs/pages pages/io
L-1 0 16 734755 29584 4767 24.8 6.2
-----------== Posted via Deja News, The Discussion Network ==----------
http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own
Well, the first thing to do is to determine what your performance goals are,
then do a comprehensive audit on the current system (os, database and
network) under load. The system or db parametes you change will depend
heavily on where any performance goals are defecient. When you do start to
make changes, make ONE at a time and measure the change over a period of
time.
Not an quick and dirty answer, but performance tuning is an ongoing process
rather than a quick fix.
On the other side, your 74.25% cached seems a bit low. In general, better
performance is percieved by users when you %cached numbers are in the high
90s. One other thing you can check by looking at the logs is how long
checkpoints are taking to complete, you want to keep that to a reasonably
low number as they block the system.
Frank
zandy@skyinet.net wrote in message <79t5ud$gv4$1@nnrp1.dejanews.com>...
>Hi everyone,
>
>I'm a new DBA in our company and I'm assigned to do some
>tuning on one of our servers and I was hoping if you could help me.
>
>Thanks in advance,
>Lyzander Marantal
>
>Environment:
>
>SUN 1000E machine w/ 4 CPUs 512 MB RAM
>Informix 7.12 UC3
>Solaris 2.51
>Number of users is approx. 130
>Number of chunks = 60
>Number of Logical Logs = 96 (1000 pages each)
>
>ONCONFIG PARAMATERS
>
>ROOTNAME rootdbs>ROOTPATH /informix/informix7.12/chunks/rootP
>DBSERVERNAME imossfe>MIRRORPATH /informix/informix7.12/chunks/rootM
>PHYSDBS phylog
>ROOTOFFSET 0
>ROOTSIZE 35000>MIRROR 1
>MIRROROFFSET 0
>PHYSFILE 20000
>LOGFILES 96
>LOGSIZE 1000
>SERVERNUM 0
>DEADLOCK_TIMEOUT 60
>RESIDENT 0
>LOCKS 64000
>BUFFERS 1000
>ONDBSPACEDOWN 0>LBU_PRESERVE 0
>OPCACHEMAX 0
>PHYSBUFF 32
>LOGBUFF 32>LOGSMAX 96
>CLEANERS 8
>SHMBASE 0xa000000
>CKPTINTVL 300
>LRUS 8
>LRU_MAX_DIRTY 60
>LRU_MIN_DIRTY 50
>SINGLE_CPU_VP 0
>LTXHWM 50
>LTXEHWM 60
>TXTIMEOUT 300
>NUMCPUVPS 2
>DRAUTO 0
>SHMVIRTSIZE 64000
>USEOSTIME 0
>NOAGE 0
>AFF_SPROC 0
>AFF_NPROCS 0
>RA_PAGES 8
>RA_THRESHOLD 4
>NUMAIOVPS 64
>DBSERVERALIASES imossfe_net
>MULTIPROCESSOR 0
>FILLFACTOR 90
>STACKSIZE 32
>DBSPACETEMP tmpspace1,tmpspace2>STAGEBLOB
>DRNODE 1
>DRNAME
>DRINTERVAL 30
>DRTIMEOUT 30
>DRLOSTFOUND /usr/informix/etc/dr.lostfound
>OFF_RECVRY_THREADS 10
>ON_RECVRY_THREADS 1
>DUMPSHMEM 1
>DUMPGCORE 1
>DUMPCORE 1
>DUMPCNT 1>DUMPDIR /dba/dump_informix
>DATASKIP off>PDQPRIORITY 0
>DS_MAX_QUERIES 4
>DS_TOTAL_MEMORY 512
>DS_MAX_SCANS 1048576
>SHMADD 0x800000
>SHMTOTAL 0x0
>OPTCOMPIND 0
>MAX_PDQPRIORITY 100>
>Profile
>dskreads pagreads bufreads %cached dskwrits pagwrits bufwrits %cached
>6589236 7746127 226129288 97.09 221851 424882 861439 74.25
>
>isamtot open start read write rewrite delete commit
rollbk
>112002383 585536 2814436 99177046 118021 19101 100953 18590 17
>
>ovlock ovuserthread ovbuff usercpu syscpu numckpts flushes
>0 0 0 43012.30 7944.45 182 536
>
>bufwaits lokwaits lockreqs deadlks dltouts ckpwaits compress seqscans
>1428683 1103 672259072 0 0 190 18530 172077
>
>ixda-RA idx-RA da-RA RA-pgsused lchwaits
>2746074 797 2516807 5242910 2362751
>
>Physical Logging
>Buffer bufused bufsize numpages numwrits pages/io
> P-1 6 16 80106 5154 15.54
> phybegin physize phypos phyused %used
> 200035 10000 7417 166 1.66
>
>Logical Logging
>Buffer bufused bufsize numrecs numpages numwrits recs/pages pages/io
> L-1 0 16 734755 29584 4767 24.8 6.2
>
>-----------== Posted via Deja News, The Discussion Network ==----------
>http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own
zandy@skyinet.net wrote:
>
snip
> BUFFERS 1000
You have 512 MBytes of Memory.
Why don't give Online more ?
snip
> RA_PAGES 8
> RA_THRESHOLD 4
Why don't Read 32 Pages instead of 8 ?
> NUMAIOVPS 64
OOps - What's that ?
>
snip
>
> bufwaits lokwaits lockreqs deadlks dltouts ckpwaits compress seqscans
> 1428683 1103 672259072 0 0 190 18530 172077
>
> ixda-RA idx-RA da-RA RA-pgsused lchwaits
> 2746074 797 2516807 5242910 2362751
>
snip
Markus
zandy@skyinet.net wrote:
[snip]
I just picked some "obvious" things and you should know (as others
pointed to) that tuning can't be done in this quick and dirty way.
> BUFFERS 1000
BUFFERS is dirty low!If vmstat shows free memory increase BUFFERS. If the machine runs as
database server in a client-server environment a good starting-point
for buffers is 25% of real-memory. (25% of 512MB = 65536)
> PHYSBUFF 32Your ouput of onstat -l shows a very high percentage of
physical-logbuffer-usage (15.54 of 16):
Physical Logging
Buffer bufused bufsize numpages numwrits pages/io
P-1 6 16 80106 5154 15.54
Increase PHYSBUFF by doubling until your
onstat -l shows lower usage of physical logbuffer!> NUMCPUVPS 2Try using all CPU's for Informix: NUMCPUVPS 4
> SHMVIRTSIZE 64000Have a look at virtual Shared Memory Segments (class V) if there is a
bunch of almost free segments, try increasing SHMVIRTSIZE until the
engine almost uses one segment.
> NOAGE 0Change this one to 1 anyway.
> AFF_SPROC 0
> AFF_NPROCS 0Enable AFFINITY by setting AFF_NPROCS = NUMCPUVPS.
> RA_PAGES 8
> RA_THRESHOLD 4Your 'onstat -p' output shows that RA-Pages are used by nearly 100%.
You may increase both RA_PAGES and RA_THRESHOLD by doubling until
onstat -p shows a usage of 75% - 80%.> NUMAIOVPS 64Solaris 2.5.1 supports asynchonous kernel io to raw-devices. Do you
have all your chunks build as cooked-files? If so, you may "wan't" to
recreate your whole instance using raw-devices, which is can be an
awfull job on a system in use. :-(
If your are using raw-devices for all chunks you can set NUMAIOVPS to
1.
> MULTIPROCESSOR 0If you want to use the AFFINITY feature you will have to change this
to 1.
> SHMADD 0x800000The server will not be able to add such a big segment ;-))
change that to 8192 or 16384 (decimal!)
A general advice is to change only one parameter at a time and look
for the results, before changing others. Start with the
memory-parameters (BUFFERS, SHMVIRTSIZE, SHMADD).
And have a look on your IO-Statistics( iostat -x) for distributing
io-loads.
hth
martin
In article <79t5ud$gv4$1@nnrp1.dejanews.com>,
zandy@skyinet.net wrote:
> Hi everyone,
>
> I'm a new DBA in our company and I'm assigned to do some
> tuning on one of our servers and I was hoping if you could help me.
>
> Thanks in advance,
> Lyzander Marantal
The other responses to this posting have all given excellent advice and
suggestions. I think you should really consider and implement the changes
presented. However, probably the most important thing to remember about
performance tuning is ...
"PERFORMANCE IS PERCEIVED".
I think that all DBAs, Administrators, and especially pointy-haired managers
should have that statement tatooed on the inside of their eyelids. If your
end-users do not perceive a performance problem, then there is NOT a
performance problem. To many of us get into that perfectionist mode where we
go to great lengths and stress levels to eak out that .009% performance
improvement or attempt to find that silver bullet that will make the system
scream at mach 3. I've got news for you. There is no silver bullet, and that
.009% improvement that your benchmarks showed you could get will not even be
noticed by your end-users. At some point you reach that plateau where
nothing you can do will improve database/hardware performance to a noticeable
extent.
This does not mean you can kick back and watch Gilligan re-runs all day, you
still have plenty to do. Work on improving your "early warning systems",
automate those daily monkey banging tasks, show your developers how to test
their own code against the database BEFORE you have to fix it, get in the
pointy-haired managers face and make him understand that switching to Oracle
on NT is not the cure for cancer, etc., etc.
As changes to the application are implemented and as upgrades occur, you will
find yourself presented with new tuning challenges. But always base your
activities on the inputs from your end-users. You would not have a job if it
wasn't for them.
You may now scrap the soap box for kindling.
Bob
---------
>
> Environment:
>
> SUN 1000E machine w/ 4 CPUs 512 MB RAM
> Informix 7.12 UC3
> Solaris 2.51
> Number of users is approx. 130
> Number of chunks = 60
> Number of Logical Logs = 96 (1000 pages each)
>
> ONCONFIG PARAMATERS
>
> ROOTNAME rootdbs> ROOTPATH /informix/informix7.12/chunks/rootP
> DBSERVERNAME imossfe> MIRRORPATH /informix/informix7.12/chunks/rootM
> PHYSDBS phylog
> ROOTOFFSET 0
> ROOTSIZE 35000> MIRROR 1
> MIRROROFFSET 0
> PHYSFILE 20000
> LOGFILES 96
> LOGSIZE 1000
> SERVERNUM 0
> DEADLOCK_TIMEOUT 60
> RESIDENT 0
> LOCKS 64000
> BUFFERS 1000
> ONDBSPACEDOWN 0> LBU_PRESERVE 0
> OPCACHEMAX 0
> PHYSBUFF 32
> LOGBUFF 32> LOGSMAX 96
> CLEANERS 8
> SHMBASE 0xa000000
> CKPTINTVL 300
> LRUS 8
> LRU_MAX_DIRTY 60
> LRU_MIN_DIRTY 50
> SINGLE_CPU_VP 0
> LTXHWM 50
> LTXEHWM 60
> TXTIMEOUT 300
> NUMCPUVPS 2
> DRAUTO 0
> SHMVIRTSIZE 64000
> USEOSTIME 0
> NOAGE 0
> AFF_SPROC 0
> AFF_NPROCS 0
> RA_PAGES 8
> RA_THRESHOLD 4
> NUMAIOVPS 64
> DBSERVERALIASES imossfe_net
> MULTIPROCESSOR 0
> FILLFACTOR 90
> STACKSIZE 32
> DBSPACETEMP tmpspace1,tmpspace2> STAGEBLOB
> DRNODE 1
> DRNAME
> DRINTERVAL 30
> DRTIMEOUT 30
> DRLOSTFOUND /usr/informix/etc/dr.lostfound
> OFF_RECVRY_THREADS 10
> ON_RECVRY_THREADS 1
> DUMPSHMEM 1
> DUMPGCORE 1
> DUMPCORE 1
> DUMPCNT 1> DUMPDIR /dba/dump_informix
> DATASKIP off> PDQPRIORITY 0
> DS_MAX_QUERIES 4
> DS_TOTAL_MEMORY 512
> DS_MAX_SCANS 1048576
> SHMADD 0x800000
> SHMTOTAL 0x0
> OPTCOMPIND 0
> MAX_PDQPRIORITY 100>
> Profile
> dskreads pagreads bufreads %cached dskwrits pagwrits bufwrits %cached
> 6589236 7746127 226129288 97.09 221851 424882 861439 74.25
>
> isamtot open start read write rewrite delete commit rollbk
> 112002383 585536 2814436 99177046 118021 19101 100953 18590 17
>
> ovlock ovuserthread ovbuff usercpu syscpu numckpts flushes
> 0 0 0 43012.30 7944.45 182 536
>
> bufwaits lokwaits lockreqs deadlks dltouts ckpwaits compress seqscans
> 1428683 1103 672259072 0 0 190 18530 172077
>
> ixda-RA idx-RA da-RA RA-pgsused lchwaits
> 2746074 797 2516807 5242910 2362751
>
> Physical Logging
> Buffer bufused bufsize numpages numwrits pages/io
> P-1 6 16 80106 5154 15.54
> phybegin physize phypos phyused %used
> 200035 10000 7417 166 1.66
>
> Logical Logging
> Buffer bufused bufsize numrecs numpages numwrits recs/pages pages/io
> L-1 0 16 734755 29584 4767 24.8 6.2
>
> -----------== Posted via Deja News, The Discussion Network ==----------
> http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own
>
-----------== Posted via Deja News, The Discussion Network ==----------
http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own