Fwd: FW: Why is rootdbs busy?

Based on your system load and number of buffers defined you might be suffering from statistics lookups. Try enabling Data Distribution Cache. My database system tables are in a separate DBspace and I reduced the I/O by 50% using the DDC. Mileage may vary, I have 30,000 tables in our Baan database. regards jim namenek >From: "Namenek, James H." <namenej@diebold.com> >To: namenej@hotmail.com >Subject: FW: Why is rootdbs busy? >Date: Thu, 31 Aug 2000 08:20:51 -0400 > >forward... > >regards >Jim Namenek > >phone: 330-490-5929 >fax: 330-490-5735 >email: namenej@diebold.com > > > >-----Original Message----- >From: Art S. Kagel [mailto:kagel@bloomberg.net] >Sent: Wednesday, August 30, 2000 3:43 PM >To: informix-list@iiug.org >Subject: Re: Why is rootdbs busy? > > >I'm going to differ with Madison and recommend that you increase BOTH >DD_HASHMAX to say 20 and DD_HASHSIZE to a larger prime number say 53. >You could BTW estimate your working set of tables add in say 5 time the >number of databases to hold system catalog tables and adjust the DD_ >values so their product is slightly larger than that. This will prevent >and thrashing of the DD cache and insure that there will always be an >existing entry for every active table everytime it is referenced once the >server reaches steady state. > >Art S. Kagel > >Tony Flaherty wrote: > > > > I'm not familiar with this aspect of IDS configuration :o( running the > > onstat -g dic gives me the following output;-> > > > onstat -g dic> > > > Informix Dynamic Server Version 7.30.UC7 -- On-Line -- Up 17 days > > 01:57:18 -- 264912 Kbytes > > > > Dictionary Cache: Number of lists: 31, Maximum list size: 10 > > > > list# size refcnt dirty? heapptr table name > > -------------------------------------------------------- > > 0 10 13 no cb736e80 mars@live1:informix.processor > > 7 no cb4e9020 mitre2@live1:informix.m_onotes > > 10 no cb43fbe0 mfsl@live1:informix.glhist > > 0 no cb53d740 acti@live1:informix.glhist > > 0 no cb69a420 mfsl@live1:informix.stpara > > 0 no cb77de40 eire@live1:informix.glhist > > 0 no cb634340 mars@live1:fch.credit_dtl > > 0 no cca09200 mars@live1:informix.v_faults > > 0 no cb6283c0 acti@live1:informix.stpara > > 0 no cb778f80 acti@live1:informix.stfifo > > > > 1 10 9 no cb532a20 >mars@live1:informix.call_log_detail > > 3 no cb567420 mfsl@live1:informix.dlstat > > . > > . > > . > > . > > . > > 30 10 3 no cb5bba40 mfsl@live1:informix.pllink > > 0 no cb83a820 mars@live1:informix.eng_ts_hdr > > 0 no cb573820 acti@live1:informix.pllink > > 0 no cb580460 mfsl@live1:informix.stacc > > 0 no cd1f2400 icon@live1:informix.prod_trans > > 0 no cb67f020 acti@live1:informix.wopara > > 0 no cb538f60 acti@live1:informix.stacc > > 0 no cb6d4820 acti@live1:informix.poinvbat > > 0 no cb60d0a0 acti@live1:informix.dlreps > > 0 no cb885bc0 acti@live1:informix.almas > > > > Total number of dictionary entries: 316 > > > > This looks like all of the chains are full. Whilst I don't have the > > problems described by the original poster, I'd appreciate some advice on > > whether I should be looking at tuning this and if so how. > > > > Grepping the output for "sys" shows 56 entries taken up by system >tables. > > I've got fifteen databases in this instance, presumably I can tell from >this > > how many sytem tables should be cached? Or am I completely missing the > > point here? > > > > TIA. > > Tony Flaherty > > Snr A/P, DBA, Unix Admin. give me a broom and I'll sweep the floor as >well! > > MFS Ltd. > > > > Madison Pruet wrote in message <39AC785E.3F46BDB4@home.com>... > > >While I approve of the DD_HASHSIZE, I'm not so sure about HASHMAX. >That > > limits > > >the dictionary hash chains to only four entries. While that might be >OK, > > it can > > >also indicate a pontential for thrashing. Basically if there are four > > entries > > >and a table is no longer referenced, then it's dictionary information >will > > be > > >removed from memory. That means that if you have several tables that > > happen to > > >collide to a single hash bucket, then you might be seeing some >re-reading > > of the > > >systables/syscolumns/sysindexes/etc... when the table is re-referenced. > > With > > >the chain being limited to only four, this might be occuring. > > > > > >N.B. The chains can go beyond four, but only if the table is still >being > > >referenced. As soon as the reference count drops then the table > > information > > >would be removed from memory. > > > > > > > > > > > >"B. Dana" wrote: > > > > > >> The databases are defined in rootdbs, tables defined in lots of >chunks. > > >> I'm not sure how to read the output of onstat -g dic. It appears that >we > > >> have set DD_HASHSIZE to 501 and DD_HASHMAX to 4. Very few > > >> of the entries currently have a size of 4. The system is relatively >idle > > >> at this time. > > >> > > >> Not using data replication if that is what HDR is. > > >> > > >> Madison Pruet wrote: > > >> > > >> > Where are your databases defined? If the database is defined in >the > > root > > >> > dbs and the tables are defined elsewhere, then you might be hitting >the > > >> > systables quit a bit. What does onstat -g dic look like? There is >a > > max > > >> > length of each of the dictionary chains. If all of the hash chains >are > > >> > full, then you might be opening quite a few of the systables >constantly > > to > > >> > get the dictionary information into the cache. > > >> > > > >> > Are you using HDR??? > > >> > > > >> > "B. Dana" wrote: > > >> > > > >> > > According to onstat -g iof, rootdbs appears to be the busiest >chunk > > in > > >> > > the system with 10 > > >> > > times more dskwrite than any other chunk. The performance guide > > states > > >> > > that "When > > >> > > you do not specify dbspaces in the DBSPACETEMP parameter or the > > client > > >> > > DBSPACETEMP environment variable, Dynamic Server places all >temporary > > >> > > tables > > >> > > in the root dbspace." There are four temp dbspaces and they are > > defined > > >> > > in the onconfig > > >> > > file. I have seen activity on all of those spaces with onstat -g >iof > > >> > > and using the OS monitor > > >> > > command while creating indexes. If the client sets DBSPACETEMP to >a > > >> > > nonexistent > > >> > > dbspace or does not se