Performance Monitoring/Tuning

As the users of our advertisement system are moaning about the bad performance, i'm trying to find out why the system's perfomance isn't as good as it was in the beginning. Even after dropping and reimporting the whole database the performance was only slightly better. I'm running informix 7.30 UC6 on hp-ux 10.20, the rootdbs is located on the system's root-hd, the aps-dbspaces are lying in an AutoRaid (Raid5) striped over 8 discs - therefor fragmentation of the big tables won't better the performance (or am i wrong)?. Does a change of LRU_MAX_DIRTY/LRU_MIN_DIRTY (actually 50/60) improve performance? The server isn't only db-server but also application server, how many of the (4) CPUs should by affinated? I somewhere read, the number of LRUs should equal the number of CPUs of the db-server - is this right? How can I generally monitor system's performance/throughput? Furthermore the workload of the lun the dbspace are located on often averages some 80 - 90 p.c. - may be this a reason for the bad performance? thanx for help thomas ------------------------------------------------- Thomas Stainer - tstainer@styria.com

Thomas Stainer wrote: > > As the users of our advertisement system are moaning about the bad > performance, i'm trying to find out why the system's perfomance isn't as > good as it was in the beginning. Even after dropping and reimporting the > whole database the performance was only slightly better. . . . and I'm presuming that you reran the required UPDATE STATISTICS? What ONCONFIG changes were made initially?? > I'm running > informix 7.30 UC6 on hp-ux 10.20, the rootdbs is located on the system's > root-hd, the aps-dbspaces are lying in an AutoRaid (Raid5) striped over > 8 discs - therefor fragmentation of the big tables won't better the > performance (or am i wrong)?. Depends, I guess. Is performance bad because the disks are busy or because the query plan is bad? > Does a change of LRU_MAX_DIRTY/LRU_MIN_DIRTY (actually 50/60) improve > performance? When it comes to writing dirty pages to disk. How long are your checkpoints?? > The server isn't only db-server but also application server, how many of > the (4) CPUs should by affinated? Didn't know that you could do that on HPUX . . . . > I somewhere read, the number of LRUs should equal the number of CPUs of > the db-server - is this right? I believe that may be the 'official' Informix documentation version, but it really depends on the number of BUFFERS that you have. If buffer contention exists, then you might need to increase the number of LRUs. > How can I generally monitor system's performance/throughput? I use glance and various onstat commands. > Furthermore the workload of the lun the dbspace are located on often > averages some 80 - 90 p.c. - may be this a reason for the bad > performance? > Depends on what the performance issues are. I've seen disks at 80-90% activity with decent response time. > thanx for help > If you would, could you please be a bit more specific concerning what the 'performance issues' are? -- John Carlson Informix DBA WHSmith USA #include std_disclaimer.h /* These are my opinions, not my company's opinion */

i rerun UPDATE STATISTICS every night - what about UPDATE STATISTICS MEDIUM or HIGH? initially my checkpoints took 0 - 2 seconds, meanwhile checkpoint duration is between 10 and 30 secs. as far as the query plan is concerned i'm not shure it is really good. "Carlson@WHSmith" wrote: > Thomas Stainer wrote: > > > > As the users of our advertisement system are moaning about the bad > > performance, i'm trying to find out why the system's perfomance isn't as > > good as it was in the beginning. Even after dropping and reimporting the > > whole database the performance was only slightly better. > > . . . and I'm presuming that you reran the required UPDATE STATISTICS? > What ONCONFIG changes were made initially?? > > > I'm running > > informix 7.30 UC6 on hp-ux 10.20, the rootdbs is located on the system's > > root-hd, the aps-dbspaces are lying in an AutoRaid (Raid5) striped over > > 8 discs - therefor fragmentation of the big tables won't better the > > performance (or am i wrong)?. > > Depends, I guess. Is performance bad because the disks are busy or > because the query plan is bad? > > > Does a change of LRU_MAX_DIRTY/LRU_MIN_DIRTY (actually 50/60) improve > > performance? > > When it comes to writing dirty pages to disk. How long are your > checkpoints?? > > > The server isn't only db-server but also application server, how many of > > the (4) CPUs should by affinated? > > Didn't know that you could do that on HPUX . . . . > > > I somewhere read, the number of LRUs should equal the number of CPUs of > > the db-server - is this right? > > I believe that may be the 'official' Informix documentation version, but > it really depends on the number of BUFFERS that you have. If buffer > contention exists, then you might need to increase the number of LRUs. > > > How can I generally monitor system's performance/throughput? > > I use glance and various onstat commands. > > > Furthermore the workload of the lun the dbspace are located on often > > averages some 80 - 90 p.c. - may be this a reason for the bad > > performance? > > > > Depends on what the performance issues are. I've seen disks at 80-90% > activity with decent response time. > > > thanx for help > > > > If you would, could you please be a bit more specific concerning what > the 'performance issues' are? > > -- > John Carlson > Informix DBA > WHSmith USA > > #include std_disclaimer.h /* These are my opinions, not my company's > opinion */ -- ------------------------------------------------- Thomas Stainer - tstainer@styria.com open-it informationsberatungsges.m.b.h. & co kg Schönaugasse 64 TEL: +43-316-875-3045 8010 Graz, Austria FAX: +43-316-875-3034

As someone else already replied to most of your questions, I won't go over all of them. I do have one comment though... In article <39490D25.41B5842A@styria.com>, Thomas Stainer <tstainer@styria.com> wrote: > As the users of our advertisement system are moaning about the bad > performance, i'm trying to find out why the system's perfomance isn't as > good as it was in the beginning. Even after dropping and reimporting the > whole database the performance was only slightly better. I'm running > informix 7.30 UC6 on hp-ux 10.20, the rootdbs is located on the system's > root-hd, the aps-dbspaces are lying in an AutoRaid (Raid5) striped over > 8 discs - therefor fragmentation of the big tables won't better the > performance (or am i wrong)?. Actually, fragmenting still could increase performance dramatically. First of all, you can use a fragmentation by expression strategy that could significantly reduce the number of reads necessary to find the records you're looking for. (Without knowing the data, I can't tell you how to fragment it.) Second, just by having multiple fragments, it allows the database to start as many threads as you have dbspaces to concurrently scan the table. This can also increase speed quite a bit. Just using RAID doesn't make any use of Informix's internal performance increasing abilities; you might want to take a look at fragmenting the data. > Does a change of LRU_MAX_DIRTY/LRU_MIN_DIRTY (actually 50/60) improve > performance? > The server isn't only db-server but also application server, how many of > the (4) CPUs should by affinated? > I somewhere read, the number of LRUs should equal the number of CPUs of > the db-server - is this right? > How can I generally monitor system's performance/throughput? > Furthermore the workload of the lun the dbspace are located on often > averages some 80 - 90 p.c. - may be this a reason for the bad > performance? > > thanx for help > > thomas > ------------------------------------------------- > Thomas Stainer - tstainer@styria.com > > -- # unrm / ksh: unrm: not found # man cpio Sent via Deja.com http://www.deja.com/ Before you buy.

when you run onstat -p and look at the output , what are the numbers for read cache, and write cache? are they below 90? if so, there probably is something you can do to increase the cache performance. by adding more buffers, you might increase the read cache numbers. if the write cache is low, you might want to increase the number of lru's and add additional cleaners , one for each lru queue. it depends on whether you are frequently writing to the physical log for some reason, on whether your write cache is low. look at the checkpoint duration. if it is more than a few seconds, it could be you could add more lru's, and cleaners ( 1 for each lru). you can monitor your disk speed with onstat -g iof, and there are other onstat -g commands to monitor other queus etc. what is the read ahead used total, compared to the three columns to the left of it? the totals of the ixda-RA idx-RA da-RA should be close to RA-pgsused Thomas Stainer wrote: > As the users of our advertisement system are moaning about the bad > performance, i'm trying to find out why the system's perfomance isn't as > good as it was in the beginning. Even after dropping and reimporting the > whole database the performance was only slightly better. I'm running > informix 7.30 UC6 on hp-ux 10.20, the rootdbs is located on the system's > root-hd, the aps-dbspaces are lying in an AutoRaid (Raid5) striped over > 8 discs - therefor fragmentation of the big tables won't better the > performance (or am i wrong)?. > Does a change of LRU_MAX_DIRTY/LRU_MIN_DIRTY (actually 50/60) improve > performance? > The server isn't only db-server but also application server, how many of > the (4) CPUs should by affinated? > I somewhere read, the number of LRUs should equal the number of CPUs of > the db-server - is this right? > How can I generally monitor system's performance/throughput? > Furthermore the workload of the lun the dbspace are located on often > averages some 80 - 90 p.c. - may be this a reason for the bad > performance? > > thanx for help > > thomas > ------------------------------------------------- > Thomas Stainer - tstainer@styria.com

How can I generally monitor system's performance/throughput? Refer to the Zero Impact Service Level Monitor (ZISLM)) at www.sqlpower.com. It will measure end-user response time as well as database server response time. If average end-user response time begins to trend up N% per day, it will be immediately noticed by the DBA/support staff when the ZISLM is monitoring production database servers. When average end-user response time begins to trend up N%, usually it will not be initially noticed by end-users. However if the trend continues for a few weeks, it (increasing end-user response time) will at some point be noticed by the end-user community. The ZISLM allows DBA/support staff to proactively investigate and resolve database performance issues before the end-user contacts the DBA/support desk/project leader/IT management regarding poor system performance. Thomas Stainer <tstainer@styria.com> wrote in message news:39490D25.41B5842A@styria.com... > As the users of our advertisement system are moaning about the bad > performance, i'm trying to find out why the system's perfomance isn't as > good as it was in the beginning. Even after dropping and reimporting the > whole database the performance was only slightly better. I'm running > informix 7.30 UC6 on hp-ux 10.20, the rootdbs is located on the system's > root-hd, the aps-dbspaces are lying in an AutoRaid (Raid5) striped over > 8 discs - therefor fragmentation of the big tables won't better the > performance (or am i wrong)?. > Does a change of LRU_MAX_DIRTY/LRU_MIN_DIRTY (actually 50/60) improve > performance? > The server isn't only db-server but also application server, how many of > the (4) CPUs should by affinated? > I somewhere read, the number of LRUs should equal the number of CPUs of > the db-server - is this right? > How can I generally monitor system's performance/throughput? > Furthermore the workload of the lun the dbspace are located on often > averages some 80 - 90 p.c. - may be this a reason for the bad > performance? > > thanx for help > > thomas > ------------------------------------------------- > Thomas Stainer - tstainer@styria.com > >

How can I generally monitor system's performance/throughput? Refer to the Zero Impact Service Level Monitor (ZISLM)) at www.sqlpower.com. It will measure end-user response time as well as database server response time. If average end-user response time begins to trend up N% per day, it will be immediately noticed by the DBA/support staff when the ZISLM is monitoring production database servers. When average end-user response time begins to trend up N%, usually it will not be initially noticed by end-users. However if the trend continues for a few weeks, it (increasing end-user response time) will at some point be noticed by the end-user community. The ZISLM allows DBA/support staff to proactively investigate and resolve database performance issues before the end-user contacts the DBA/support desk/project leader/IT management regarding poor system performance. Thomas Stainer <tstainer@styria.com> wrote in message news:39490D25.41B5842A@styria.com... > As the users of our advertisement system are moaning about the bad > performance, i'm trying to find out why the system's perfomance isn't as > good as it was in the beginning. Even after dropping and reimporting the > whole database the performance was only slightly better. I'm running > informix 7.30 UC6 on hp-ux 10.20, the rootdbs is located on the system's > root-hd, the aps-dbspaces are lying in an AutoRaid (Raid5) striped over > 8 discs - therefor fragmentation of the big tables won't better the > performance (or am i wrong)?. > Does a change of LRU_MAX_DIRTY/LRU_MIN_DIRTY (actually 50/60) improve > performance? > The server isn't only db-server but also application server, how many of > the (4) CPUs should by affinated? > I somewhere read, the number of LRUs should equal the number of CPUs of > the db-server - is this right? > How can I generally monitor system's performance/throughput? > Furthermore the workload of the lun the dbspace are located on often > averages some 80 - 90 p.c. - may be this a reason for the bad > performance? > > thanx for help > > thomas > ------------------------------------------------- > Thomas Stainer - tstainer@styria.com > >