IDS11 on Linux - insert into temp takes very long

Hi, Environment details - Informix 11.50.FC4 OS Red hat linux - 2.6.18-92.el5. One query with two tables joined runs fast and gives result within 1-2 minutes. But when I use same query to insert result into temp table "select... from .. insert into temp t1 with no log" it takes around 8-9 hours. Records - Table 1 : 32503 Table 2 : 16827089 I doubt about temp dbspace but temp dbspaces are defined in config and environment. I do not see much change in tempdbspace read/write with onstat -D. Also no files created in /tmp directory. What can be issue? and where to check? is it know issue with informix11? Please help. sorry for the irrelevent subject in earlier mail. Onstat -d output -------------------------------------------------------------------------------- ------------- IBM Informix Dynamic Server Version 11.50.FC4 -- On-Line -- Up 4 days 10:49:03 -- 7838356 KbytesDbspaces address number flags fchunk nchunks pgsize flags owner name 12b93d028 1 0x40001 1 1 2048 N B informix ibs_ia1_rootdbs 12f336418 2 0x40001 2 1 2048 N B informix ibs_ia1_plogdbs 12f3365b0 3 0x40001 3 3 2048 N B informix ibs_ia1_logdbs1 12f336748 4 0x40001 6 3 2048 N B informix ibs_ia1_logdbs2 12f3368e0 5 0x42001 9 1 2048 N TB informix ibs_ia1_tmpdbs1 12f336a78 6 0x42001 10 1 2048 N TB informix ibs_ia1_tmpdbs2 12f336c10 7 0x42001 11 1 2048 N TB informix ibs_ia1_tmpdbs3 12f336da8 8 0x42001 12 1 2048 N TB informix ibs_ia1_tmpdbs4 12f335cc0 9 0x48001 13 1 2048 N SB informix ibs_ia1_ersbs 12f335e58 10 0x40001 14 1 2048 N B informix ibs_ia1_erdbs 12f337028 11 0x40001 15 1 2048 N B informix ibs_ia1_sadmdbs 12f3371c0 12 0x40001 16 7 2048 N B informix ibs_ia1_mxdbs1 12f337358 13 0x40001 22 6 2048 N B informix ibs_ia1_mxdbs2 12f3374f0 14 0x40001 28 4 2048 N B informix ibs_ia1_mxdbs3 12f337688 15 0x40001 32 1 2048 N B informix ibs_ia1_mxinvhdr_idx1 12f337820 16 0x40001 33 1 2048 N B informix ibs_ia1_mxinvhdr_idx2 12f3379b8 17 0x40001 34 1 2048 N B informix ibs_ia1_mxinvdtl_idx1 12f337b50 18 0x40001 35 1 2048 N B informix ibs_ia1_mxinvdtl_idx2 12f337ce8 19 0x40001 36 1 2048 N B informix ibs_ia1_mxinvdtl_dbs1 12f337e80 20 0x40001 37 1 2048 N B informix ibs_ia1_mxinvdtl_dbs2 12f339028 21 0x40001 38 1 2048 N B informix ibs_ia1_mxinvdtl_dbs3 12f3391c0 22 0x40001 39 1 2048 N B informix ibs_ia1_mxinvdtl_dbs4 12f339358 23 0x40001 40 1 2048 N B informix ibs_ia1_mxinvdtl_dbs5 12f3394f0 24 0x40001 41 1 2048 N B informix ibs_ia1_mxinvdtl_dbs6 12f339688 25 0x40001 42 2 2048 N B informix ibs_ia1_mxinvhdr_dbs1 12f339820 26 0x40001 44 2 2048 N B informix ibs_ia1_mxinvhdr_dbs2 12f3399b8 27 0x40001 46 2 2048 N B informix ibs_ia1_mxinvhdr_dbs3 12f339b50 28 0x40001 48 2 2048 N B informix ibs_ia1_mxinvhdr_dbs4 12f339ce8 29 0x40001 50 2 2048 N B informix ibs_ia1_mxinvhdr_dbs5 12f339e80 30 0x40001 52 2 2048 N B informix ibs_ia1_mxinvhdr_dbs6 30 active, 2047 maximum Chunks address chunk/dbs offset size free bpages flags pathname 12b93d1c0 1 1 0 512000 504783 PO-B- /usr/informix_ia1_ibs/infdev/ibs_ia1_rootdbs.1 12f33b028 2 2 0 2048000 47947 PO-B- /usr/informix_ia1_ibs/infdev/ibs_ia1_plogdbs.1 12f33b218 3 3 0 1024000 23947 PO-B- /usr/informix_ia1_ibs/infdev/ibs_ia1_logdbs1.1 12f33b408 4 3 0 1024000 23997 PO-B- /usr/informix_ia1_ibs/infdev/ibs_ia1_logdbs1.2 12f33b5f8 5 3 0 1024000 23997 PO-B- /usr/informix_ia1_ibs/infdev/ibs_ia1_logdbs1.3 12f33b7e8 6 4 0 1024000 23947 PO-B- /usr/informix_ia1_ibs/infdev/ibs_ia1_logdbs2.1 12f33b9d8 7 4 0 1024000 23997 PO-B- /usr/informix_ia1_ibs/infdev/ibs_ia1_logdbs2.2 12f33bbc8 8 4 0 1024000 23997 PO-B- /usr/informix_ia1_ibs/infdev/ibs_ia1_logdbs2.3 12f33bdb8 9 5 0 1024000 1023907 PO-B- /usr/informix_ia1_ibs/infdev/ibs_ia1_tmpdbs1.1 12f33d028 10 6 0 1024000 1023923 PO-B- /usr/informix_ia1_ibs/infdev/ibs_ia1_tmpdbs2.1 12f33d218 11 7 0 1024000 1023947 PO-B- /usr/informix_ia1_ibs/infdev/ibs_ia1_tmpdbs3.1 12f33d408 12 8 0 1024000 1023947 PO-B- /usr/informix_ia1_ibs/infdev/ibs_ia1_tmpdbs4.1 12f33d5f8 13 9 0 1024000 955003 955004 POSB- /usr/informix_ia1_ibs/infdev/ibs_ia1_ersbs.1 Metadata 68943 51302 68943 12f33d7e8 14 10 0 1024000 1023907 PO-B- /usr/informix_ia1_ibs/infdev/ibs_ia1_erdbs.1 12f33d9d8 15 11 0 1024000 1021301 PO-B- /usr/informix_ia1_ibs/infdev/ibs_ia1_sadmdbs.1 12f33dbc8 16 12 0 5120000 17037 PO-B- /usr/informix_ia1_ibs/infdev/ibs_ia1_mxdbs1.1 12f33ddb8 17 12 0 5120000 0 PO-B- /usr/informix_ia1_ibs/infdev/ibs_ia1_mxdbs1.2 12f33f028 18 12 0 5120000 69720 PO-B- /usr/informix_ia1_ibs/infdev/ibs_ia1_mxdbs1.3 12f33f218 19 12 0 5120000 661308 PO-B- /usr/informix_ia1_ibs/infdev/ibs_ia1_mxdbs1.4 12f33f408 20 12 0 5120000 5119847 PO-B- /usr/informix_ia1_ibs/infdev/ibs_ia1_mxdbs1.5 12f33f5f8 21 12 0 5120000 0 PO-B- /usr/informix_ia1_ibs/infdev/ibs_ia1_mxdbs1.6 12f33f7e8 22 13 0 5120000 54623 PO-B- /usr/informix_ia1_ibs/infdev/ibs_ia1_mxdbs2.1 12f33f9d8 23 13 0 5120000 0 PO-B- /usr/informix_ia1_ibs/infdev/ibs_ia1_mxdbs2.2 12f33fbc8 24 13 0 5120000 5192 PO-B- /usr/informix_ia1_ibs/infdev/ibs_ia1_mxdbs2.3 12f33fdb8 25 13 0 5120000 192696 PO-B- /usr/informix_ia1_ibs/infdev/ibs_ia1_mxdbs2.4 12f342028 26 13 0 5120000 2619997 PO-B- /usr/informix_ia1_ibs/infdev/ibs_ia1_mxdbs2.5 12f342218 27 13 0 5120000 5119997 PO-B- /usr/informix_ia1_ibs/infdev/ibs_ia1_mxdbs2.6 12f342408 28 14 0 5120000 333206 PO-B- /usr/informix_ia1_ibs/infdev/ibs_ia1_mxdbs3.1 12f3425f8 29 14 0 5120000 0 PO-B- /usr/informix_ia1_ibs/infdev/ibs_ia1_mxdbs3.2 12f3427e8 30 14 0 5120000 1684659 PO-B- /usr/informix_ia1_ibs/infdev/ibs_ia1_mxdbs3.3 12f3429d8 31 14 0 5120000 2619997 PO-B- /usr/informix_ia1_ibs/infdev/ibs_ia1_mxdbs3.4 12f342bc8 32 15 0 1024000 799426 PO-B- /usr/informix_ia1_ibs/infdev/ibs_ia1_mxinvhdr_idx1.1 12f342db8 33 16 0 1024000 861518 PO-B- /usr/informix_ia1_ibs/infdev/ibs_ia1_mxinvhdr_idx2.1 12f344028 34 17 0 1024000 795128 PO-B- /usr/informix_ia1_ibs/infdev/ibs_ia1_mxinvdtl_idx1.1 12f344218 35 18 0 1024000 836397 PO-B- /usr/informix_ia1_ibs/infdev/ibs_ia1_mxinvdtl_idx2.1 12f344408 36 19 0 5120000 3839947 PO-B- /usr/informix_ia1_ibs/infdev/ibs_ia1_mxinvdtl_dbs1.1 12f3445f8 37 20 0 5120000 3839947 PO-B- /usr/informix_ia1_ibs/infdev/ibs_ia1_mxinvdtl_dbs2.1 12f3447e8 38 21 0 5120000 3839947 PO-B- /usr/informix_ia1_ibs/infdev/ibs_ia1_mxinvdtl_dbs3.1 12f3449d8 39 22 0 5120000 3839947 PO-B- /usr/informix_ia1_ibs/infdev/ibs_ia1_mxinvdtl_dbs4.1 12f344bc8 40 23 0 5120000 3839947 PO-B- /usr/informix_ia1_ibs/infdev/ibs_ia1_mxinvdtl_dbs5.1 12f344db8 41 24 0 5120000 3839947 PO-B- /usr/informix_ia1_ibs/infdev/ibs_ia1_mxinvdtl_dbs6.1 12f346028 42 25 0 5120000 1791947 PO-B- /usr/informix_ia1_ibs/infdev/ibs_ia1_mxinvhdr_dbs1.1 12f346218 43 25 0 5120000 5119997 PO-B- /usr/informix_ia1_ibs/infdev/ibs_ia1_mxinvhdr_dbs1.2 12f346408 44 26 0 5120000 1791947 PO-B- /usr/informix_ia1_ibs/infdev/ibs_ia1_mxinvhdr_dbs2.1 12f3465f8 45 26 0 5120000 5119997 PO-B- /usr/informix_ia1_ibs/infdev/ibs_ia1_mxinvhdr_dbs2.2 12f3467e8 46 27 0 5120000 1791947 PO-B- /usr/informix_ia1_ibs/infdev/ibs_ia1

Hi Nandkishor.. I had some pretty back delays with temps, logging, and other such operations. I originally thought it was disk I/O or something related, what I found out is that I didn't have correct Kernel tunings for my Linux box. I run IDS 10.00.FC9 on RedHat Linux AS 4.7. Here is what I had to add to my /etc/sysctl.conf file.... this really helped me out. File: /etc/syctl.conf # Kernel sysctl configuration file for Red Hat Linux # # For binary values, 0 is disabled, 1 is enabled. See sysctl(8) and # sysctl.conf(5) for more details. <skip to bottom of file> #### This section added by Informix kernel.shmmax = 4398046511104 kernel.shmmin = 1 kernel.shmseg = 128 kernel.shmall = 4194304 kernel.shmmni = 4096 kernel.semmsl = 250 kernel.semmns = 32000 kernel.semopm = 32 kernel.sem = 250 409600 32 4096 I hope that helpful. Sincerely, Jonathan B. Smaby Pomona College phone: (909) 621-8506 email: jonathan.smaby@pomona.edu ------------------------------------------------------------- This message has been scanned by Postini anti-virus software.

Folks, please don't email me directly asking me to explain what Linux Kernel tunings are all about. I've been pinged asking to give a dissertation on Linux Kernel. No-can-do, I don't have the time to train you. If you don't understand Linux kernel tunings, please talk with your Linux system admin. If you are wearing both hats, like me, please refer to the IBM Informix machine notes resource. It can explain better than I have time. http://www-01.ibm.com/software/data/informix/pubs/library/notes/machnote/machnot e.html If that doesn't help, talk to Art @ Oninit, LLC, they are very sharp and they can consult for you with any Informix issue. Thanks! Jonathan B. Smaby Pomona College phone: (909) 621-8506 email: jonathan.smaby@pomona.edu -- "Just because nobody complains doesn't mean all parachutes are perfect." - Benny Hill (1924 - 1992) ------------------------------------------------------------- This message has been scanned by Postini anti-virus software.

Hello, mr Singare: On our OLTP systems, some troubles with slow queryes were solved when using ON clauses: I suggest you to make two queryes with UNION, instead of using ON clauses, and watch the explain stats again, maybe that raises your speed, ok? Ex: remove the section b.bill_doc_ty in ('R', 'O') and on the first query just put the first condition b.bill_doc_ty = 'R' and then make the same query again, UNION a second one with b.bill_doc_ty = 'O' Best regards! Alexandre Marini Tecnologia da Informação - DBA SEFAZ-MS / SGI-UIMP / Sistemas IBM-Informix Nandkishor Singare escreveu: > Hi, > > Environment details - > > Informix 11.50.FC4 > OS Red hat linux - 2.6.18-92.el5. > > One query with two tables joined runs fast and gives result within 1-2 > minutes. > But when I use same query to insert result into temp table "select... from > ... insert into temp t1 with no log" it takes around 8-9 hours. > Records - > Table 1 : 32503 > Table 2 : 16827089 > > I doubt about temp dbspace but temp dbspaces are defined in config and > environment. > I do not see much change in tempdbspace read/write with onstat -D. > Also no files created in /tmp directory. > > What can be issue? and where to check? is it know issue with informix11? > Please help. > > sorry for the irrelevent subject in earlier mail. > > Onstat -d output > > -------------------------------------------------------------------------------- ------------- > > IBM Informix Dynamic Server Version 11.50.FC4 -- On-Line -- Up 4 days > 10:49:03 -- 7838356 Kbytes> Dbspaces > address number flags fchunk nchunks pgsize flags > owner name > 12b93d028 1 0x40001 1 1 2048 N B > informix ibs_ia1_rootdbs > 12f336418 2 0x40001 2 1 2048 N B > informix ibs_ia1_plogdbs > 12f3365b0 3 0x40001 3 3 2048 N B > informix ibs_ia1_logdbs1 > 12f336748 4 0x40001 6 3 2048 N B > informix ibs_ia1_logdbs2 > 12f3368e0 5 0x42001 9 1 2048 N TB > informix ibs_ia1_tmpdbs1 > 12f336a78 6 0x42001 10 1 2048 N TB > informix ibs_ia1_tmpdbs2 > 12f336c10 7 0x42001 11 1 2048 N TB > informix ibs_ia1_tmpdbs3 > 12f336da8 8 0x42001 12 1 2048 N TB > informix ibs_ia1_tmpdbs4 > 12f335cc0 9 0x48001 13 1 2048 N SB > informix ibs_ia1_ersbs > 12f335e58 10 0x40001 14 1 2048 N B > informix ibs_ia1_erdbs > 12f337028 11 0x40001 15 1 2048 N B > informix ibs_ia1_sadmdbs > 12f3371c0 12 0x40001 16 7 2048 N B > informix ibs_ia1_mxdbs1 > 12f337358 13 0x40001 22 6 2048 N B > informix ibs_ia1_mxdbs2 > 12f3374f0 14 0x40001 28 4 2048 N B > informix ibs_ia1_mxdbs3 > 12f337688 15 0x40001 32 1 2048 N B > informix ibs_ia1_mxinvhdr_idx1 > 12f337820 16 0x40001 33 1 2048 N B > informix ibs_ia1_mxinvhdr_idx2 > 12f3379b8 17 0x40001 34 1 2048 N B > informix ibs_ia1_mxinvdtl_idx1 > 12f337b50 18 0x40001 35 1 2048 N B > informix ibs_ia1_mxinvdtl_idx2 > 12f337ce8 19 0x40001 36 1 2048 N B > informix ibs_ia1_mxinvdtl_dbs1 > 12f337e80 20 0x40001 37 1 2048 N B > informix ibs_ia1_mxinvdtl_dbs2 > 12f339028 21 0x40001 38 1 2048 N B > informix ibs_ia1_mxinvdtl_dbs3 > 12f3391c0 22 0x40001 39 1 2048 N B > informix ibs_ia1_mxinvdtl_dbs4 > 12f339358 23 0x40001 40 1 2048 N B > informix ibs_ia1_mxinvdtl_dbs5 > 12f3394f0 24 0x40001 41 1 2048 N B > informix ibs_ia1_mxinvdtl_dbs6 > 12f339688 25 0x40001 42 2 2048 N B > informix ibs_ia1_mxinvhdr_dbs1 > 12f339820 26 0x40001 44 2 2048 N B > informix ibs_ia1_mxinvhdr_dbs2 > 12f3399b8 27 0x40001 46 2 2048 N B > informix ibs_ia1_mxinvhdr_dbs3 > 12f339b50 28 0x40001 48 2 2048 N B > informix ibs_ia1_mxinvhdr_dbs4 > 12f339ce8 29 0x40001 50 2 2048 N B > informix ibs_ia1_mxinvhdr_dbs5 > 12f339e80 30 0x40001 52 2 2048 N B > informix ibs_ia1_mxinvhdr_dbs6 > 30 active, 2047 maximum > Chunks > address chunk/dbs offset size free bpages > flags pathname > 12b93d1c0 1 1 0 512000 504783 > PO-B- /usr/informix_ia1_ibs/infdev/ibs_ia1_rootdbs.1 > 12f33b028 2 2 0 2048000 47947 > PO-B- /usr/informix_ia1_ibs/infdev/ibs_ia1_plogdbs.1 > 12f33b218 3 3 0 1024000 23947 > PO-B- /usr/informix_ia1_ibs/infdev/ibs_ia1_logdbs1.1 > 12f33b408 4 3 0 1024000 23997 > PO-B- /usr/informix_ia1_ibs/infdev/ibs_ia1_logdbs1.2 > 12f33b5f8 5 3 0 1024000 23997 > PO-B- /usr/informix_ia1_ibs/infdev/ibs_ia1_logdbs1.3 > 12f33b7e8 6 4 0 1024000 23947 > PO-B- /usr/informix_ia1_ibs/infdev/ibs_ia1_logdbs2.1 > 12f33b9d8 7 4 0 1024000 23997 > PO-B- /usr/informix_ia1_ibs/infdev/ibs_ia1_logdbs2.2 > 12f33bbc8 8 4 0 1024000 23997 > PO-B- /usr/informix_ia1_ibs/infdev/ibs_ia1_logdbs2.3 > 12f33bdb8 9 5 0 1024000 1023907 > PO-B- /usr/informix_ia1_ibs/infdev/ibs_ia1_tmpdbs1.1 > 12f33d028 10 6 0 1024000 1023923 > PO-B- /usr/informix_ia1_ibs/infdev/ibs_ia1_tmpdbs2.1 > 12f33d218 11 7 0 1024000 1023947 > PO-B- /usr/informix_ia1_ibs/infdev/ibs_ia1_tmpdbs3.1 > 12f33d408 12 8 0 1024000 1023947 > PO-B- /usr/informix_ia1_ibs/infdev/ibs_ia1_tmpdbs4.1 > 12f33d5f8 13 9 0 1024000 955003 955004 > POSB- /usr/informix_ia1_ibs/infdev/ibs_ia1_ersbs.1 > > Metadata 68943 51302 68943 > 12f33d7e8 14 10 0 1024000 1023907 > PO-B- /usr/informix_ia1_ibs/infdev/ibs_ia1_erdbs.1 > 12f33d9d8 15 11 0 1024000 1021301 > PO-B- /usr/informix_ia1_ibs/infdev/ibs_ia1_sadmdbs.1 > 12f33dbc8 16 12 0 5120000 17037 > PO-B- /usr/informix_ia1_ibs/infdev/ibs_ia1_mxdbs1.1 > 12f33ddb8 17 12 0 5120000 0 > PO-B- /usr/informix_ia1_ibs/infdev/ibs_ia1_mxdbs1.2 > 12f33f028 18 12 0 5120000 69720 > PO-B- /usr/informix_ia1_ibs/infdev/ibs_ia1_mxdbs1.3 > 12f33f218 19 12 0 5120000 661308 > PO-B- /usr/informix_ia1_ibs/infdev/ibs_ia1_mxdbs1.4 > 12f33f408 20 12 0 5120000 5119847 > PO-B- /usr/informix_ia1_ibs/infdev/ibs_ia1_mxdbs1.5 > 12f33f5f8 21 12 0 5120000 0 > PO-B- /usr/informix_ia1_ibs/infdev/ibs_ia1_mxdbs1.6 > 12f33f7e8 22 13 0 5120000 54623 > PO-B- /usr/informix_ia1_ibs/infdev/ibs_ia1_mxdbs2.1 > 12f33f9d8 23 13 0 5120000 0 > PO-B- /usr/informix_ia1_ibs/infdev/ibs_ia1_mxdbs2.2 > 12f33fbc8 24 13 0 5120000 5192 > PO-B- /usr/informix_ia1_ibs/infdev/ibs_ia1_mxdbs2.3 > 12f33fdb8 25 13 0 5120000 192696 > PO-B- /usr/informix_ia1_ibs/infdev/ibs_ia1_mxdbs2.4 > 12f342028 26 13 0 5120000 2619997 > PO-B- /usr/informix_ia1_ibs/infdev/ibs_ia1_mxdbs2.5 > 12f342218 27 13 0 5120000 5119997 > PO-B- /usr/informix_ia1_ibs/infdev/ibs_ia1_mxdbs2.6 > 12f342408 28 14 0 5120000 333206 > PO-B- /usr/informix_ia1_ibs/infdev/ibs_ia1_mxdbs3.1 > 12f3425f8 29 14 0 5120000 0 > PO-B- /usr/informix_ia1_ibs/infdev/ibs_ia1_mxdbs3.2 > 12f3427e8 30 14 0 5120000 1684659 > PO-B- /usr/informix_ia1_ibs/infdev/ibs_ia1_mxdbs3.3 > 12f3429d8 31 14 0 5120000 2619997 > PO-B- /usr/informix_ia1_ibs/infdev/ibs_ia1_mxdbs3.4 > 12f342bc8 32 15 0 1024000 799426 > PO-B- /usr/informix_ia1_ibs/infdev/ibs_ia1_mxinvhdr_idx1.1 > 12f342db8 33 16 0 1024000 861518 > PO-B- /usr/informix_ia1_ibs/infdev/ibs_ia1_mxinvhdr_idx2.1 > 12f344028 34 17 0 1024000 795128 > PO-B- /usr/informix_ia1_ibs/infdev/ibs_ia1_mxinvdtl_idx1.1 > 12f344218 35 18 0 1024000 836397 > PO-B- /usr/informix_ia1_ibs/infdev/ibs_ia1_mxinvdtl_idx2.1 > 12f344408 36 19 0 5120000 3839947 > PO-B