Re: Slow database creation and loading

On Jun 26, 10:23 pm, johneevo <johne...@gmail.com> wrote: > On Jun 21, 9:35 am, "Art S. Kagel" <art.ka...@gmail.com> wrote: > Hi Art, > > In case you didn't see my reply to Superboer I was able to speed up > the import > by only creating the tables, running HPL then setting PDQ and then > creating the > indexes. THis cut the time from 10:25 to 8:40. I would like to see > about getting > this even faster. > > Below are the stats that you requested. They were all taken one after > another in the > order posted while the import was running See my comments below: > Thanks again for your help with this. > > > Once you get that all worked out, post the following if it's still not > > good: > > > onstat -p (including the time since stats were zero'd).> > I'm not sure if I included the time since stats wer zero'd (I ran > onstat -p). I'm not really sure> what you meant or were looking for with that statement. Stats are automatically zero'd at startup, so since the engine had been bounce about 1.8 hours ago, I'll use that. Otherwise if you run onstat -z it zeros the stats again, if you'd done that sometime after startup then that's the time I needed - how long since onstat -z was run. > Informix Dynamic Server Version 7.31.UD7 -- On-Line -- Up 01:47:33 > -- 221180 > 8 Kbytes > > Profile > dskreads pagreads bufreads %cached dskwrits pagwrits bufwrits %cached > 1570999 8775316 28181244 94.43 1135204 2834114 3322217 65.83 > > isamtot open start read write rewrite delete > commit rollbk > 53795341 130822 148858 12409836 35421742 3680 13513 > 5476 139 > > gp_read gp_write gp_rewrt gp_del gp_alloc gp_free gp_curs > 0 0 0 0 0 0 0 > > ovlock ovuserthread ovbuff usercpu syscpu numckpts flushes > 0 0 0 4259.90 417.96 372 762 > > bufwaits lokwaits lockreqs deadlks dltouts ckpwaits compress > seqscans > 848420 0 15624669 0 0 640 1869 11852 > > ixda-RA idx-RA da-RA RA-pgsused lchwaits > 42 1 253189 253220 42055 Metrics: Pagreads: 8775316 Bufwrits: 3322217 Bufwaits: 848420 BUFFERS: 400000 Time since reset: 1.8 ixda-RA: 42 idx-RA: 1 da-RA: 253189 RA-pgsused: 253220 BR = (848420 / (3322217 + 8775316)) * 100.00 = 7.0100 BTR = (((3322217 + 8775316) / 400000) / 1.8) = 16.8021 RAU = (253220/(42+1+253189)) * 100.00 = 99.9900 BR is just a little high, but considering you're in the middle of a load, not worrysome. BTR is about double what I want to see - but better than the first time I ran it, so we'll look further into that. You may be able to take advantage of even more buffers. RAU is ideal. > > onstat -D No obvious bottlenecks showing here. > > onstat -P> > Informix Dynamic Server Version 7.31.UD7 -- On-Line -- Up 01:53:04 > -- 221180 > 8 Kbytes > partnum total btree data other resident dirty > 0 199025 198583 6 436 0 985 The 'other' value for partnum '0' is large, that would indicate that you probably have enough buffers for what you are doing. (You'll want to review the BTR and this output again during/after normal load to be sure, but it's not hurting the load.) Also I don't see any particular buffer cache hogs looking per partnum (table/index/fragment). > Totals: 400000 397625 1920 455 0 1216 > > Percentages: > Data 0.48 > Btree 99.41 But, this is a VERY bad sign during normal load. It may be that you are just only seeing index pages because of the HPloading, but 7.31 can hit you with the buffer aging bug, so watch this. Normally one expects to see about 2/3 data pages to 1/3 index pages for OLTP and mixed operations, perhaps 50/50 for a more DSS server, and 90/10 for a DW style server. > Other 0.11 > > onstat -g iov> > Informix Dynamic Server Version 7.31.UD7 -- On-Line -- Up 01:53:48 > -- 221180 > 8 Kbytes > > AIO I/O vps: > class/vp s io/s totalops dskread dskwrite dskcopy wakeups io/wup > errors No problems here > > onstat -F> > Informix Dynamic Server Version 7.31.UD7 -- On-Line -- Up 01:54:08 > -- 221180 > 8 Kbytes > > Fg Writes LRU Writes Chunk Writes > 0 43169 1091716 For an OLTP engine I like to see about 60% LRU writes to keep things humming, at least prior to Cheetah. Cheetah's checkpoints were rewritten so that this ratio may no longer hold. > > onstat -R> > Informix Dynamic Server Version 7.31.UD7 -- On-Line -- Up 01:54:29 > -- 221180 > 8 Kbytes > > 99 buffer LRU queue pairs priority levels > # f/m pair total % of length LOW MED_LOW MED_HIGH HIGH > 0 f 4041 98.0% 3961 0 3902 56 3 > 1 m 2.0% 80 0 80 0 0 All of the HIGH buckets are low so the onstat -P output is probably NOT showing the effects of the buffers aging bug. Just an artifact of hploading. > 7944 dirty, 399993 queued, 400000 total, 524288 hash buckets, 4096 > buffer size > start clean at 10% (of pair total) dirty, or 404 buffs dirty, stop at > 5% If you want to get closer to 60% LRU writes lower LRU_MIN/MAX_DIRTY from 10/5 to 5/1. Likely this is not hurting the load either. > 0 priority downgrades, 0 priority upgrades > > > onstat -g iof> > Informix Dynamic Server Version 7.31.UD7 -- On-Line -- Up 01:55:00 > -- 221180 > 8 Kbytes > > AIO global files: > gfd pathname totalops dskread dskwrite io/s > 3 infrootlv1 108738 9564 99174 15.8 This is worrysome. Too much IO in the root chunks! Are your logs in there? See if you can spread that out. > 20 infidxlv1 298669 274618 24051 43.3 > 21 infidxlv2 164296 152513 11783 23.8 > 22 infidxlv3 325403 305521 19882 47.2 These three chunks are being hit 3X as hard as the rest. Must be where the table you're loading lives. One thing that might help would be to spread the load to more chunks or different structures. > 23 infdatalv1 3 3 0 0.0 > 24 infidxlv4 3 2 1 0.0 > 25 infidxlv5 3 2 1 0.0 > Nothing glaring except the IO load on the three chunks which are probably on a single disk structure. Art S. Kagel

a couple more things: can you post the timings like set pdqpriority 100; select current from systables where tabname = systables;create index...... select current from systables where tabname = systables; i sure hope you do not create a clustered index.... if you do, then don ' t also if you have foreighn key constraints you may have a problem. do no if this is adressed in your version or in a later one. The problem is that fk constraints are/were not processed in paralel. a fellow called johnp filed that fea req /bug a looooonnng time ago against V6. If you create that fk and use pdq have a look at onstat -g ses <sid> for the number of threads. Superboer.