sysdistrib with dostats

Hi, Sometimes, I have the following error when I run dostats in parallel (all tables, aprox. 3000 tables) 211: Cannot read system catalog (sysdistrib). 144: ISAM error: key value locked How can I set a lock mode in dostats ? Thanks, Roger

dostats ... -w <secs> ... The default is "-w 10". Personally I wouldn't try to update stats on all 3000 tables in parallel using 3000 copies of dostats. I would use drive_dostats to run 10 or 20 or some other reasonable number of tables in parallel. Best to choose a number based on the number of CPU VPs you have. If nothing is happening on the server at the time, run one or two sessions per CPU VP. If user activity needs to proceed with minimal delays then estimate the number of parallel user sessions and use the remaining number of CPU VPs. drive_dostats <nprocs> <database> [-a] [dostats options] The -a specifies to process tables in size order starting with the <nprocs> smallest tables. The default is to process tables from largest to smallest. Whether you use -a or not depends on whether your priority is to get the largest number of tables processed fastest or to get the biggest (and presumably the most troublesome) tables done as fast as possible with the smaller tables processed after. Alternatively, you could run two copies of drive_dostats using the -i@listfile and -x@listfile to specify which tables each copy is to process. So one session can do large tables named in the listfile and the other all the tables not named in the listfile. Art Art S. Kagel, President and Principal Consultant ASK Database Management www.askdbmgt.com Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Tue, Nov 17, 2015 at 12:49 PM, ROGER VILCA <rvilca@luzdelsur.com.pe> wrote: > Hi, > > Sometimes, I have the following error when I run dostats in parallel (all > tables, aprox. 3000 tables) > > 211: Cannot read system catalog (sysdistrib).> > 144: ISAM error: key value locked> > How can I set a lock mode in dostats ? > Thanks, > Roger > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001a11425bd28517c40524c08f03

Thanks Art. Currently I run 15 dostats in parallel for all the 3000 tables. Regards, Roger

Cool. So, you can try setting -w "-1" which means wait forever. How many CPU VPs and processor cores do you have? If you have fewer VPs or cores than 15 a session could grab a lock, perform an IO, and go to sleep waiting for the IO and after that for a VP to free up so the lock will live much longer than it should. Art Art S. Kagel, President and Principal Consultant ASK Database Management www.askdbmgt.com Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Tue, Nov 17, 2015 at 3:11 PM, ROGER VILCA <rvilca@luzdelsur.com.pe> wrote: > Thanks Art. > > Currently I run 15 dostats in parallel for all the 3000 tables. > > Regards, > Roger > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001a11425bd24976b80524c2310e

I have 8 cores. I think a core could serve more than one VP Roger

Yes a core can host approximately one CPU VP per 500MHZ of core speed. So on a 3GHZ processor you should be able to host up to 6 CPU VPs per core. HOWEVER, for the purpose of minimizing lock contention between sessions doing the same job on the same data (ie like multiple dostats runs) I would keep it to one process per core - two at most after testing. Let dostats use the remaining CPU VPs for parallel sorting by enabling PDQPRIORITY with the -Q option. If you are running 8 dostats instances, set -Q 10 or -Q 12 at most and also set PSORT_NPROCS=<#CPU VPS> in the environments of all of the dostats processes. Update statistics doesn't hold locks while it is sorting, so it's safe to be more aggressive about using many sort threads. Anyway, bottom line is always YMMV so test it under several configurations until you settle on the one that best balances total runtime and impact on your systems. Art Art S. Kagel, President and Principal Consultant ASK Database Management www.askdbmgt.com Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Tue, Nov 17, 2015 at 3:24 PM, ROGER VILCA <rvilca@luzdelsur.com.pe> wrote: > I have 8 cores. I think a core could serve more than one VP > > Roger > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001a113fe84e4a3cfd0524c2680a

Thanks Art.