Slow Bulk Loading problem

Hi, I have a raw fragmented table with 10 indexes, I want to load many files of approx 50 million row each file with external tables, the table now contain 163 Million row, but I remark that it begin loading very fast 8000 row/sec or more and after hours, the loding become too slow it step by 100 row/sec or 500 row/sec sometimes, how could I analyse this behaviour I have to load 1 billion row in this table and I want to kill the process and repeat from bigening each time, should I load a small numerous files of 5 million maybe each??? Help plz If I kill the process and restart the database, the initial load would be vers fast and after that it start to go very slow, help plz.

It sounds like your buffer pool might be getting very dirty and the loads are having to do foreground writes to find an empty page. You might try to do a checkpoint in between each file that you load. Chunk writes are used to flush pages to disk during the checkpoint and they are much more efficient than LRU writes and foreground writes. [1]Sent from Yahoo Mail for iPad On Oct 14, 2015, 4:24:28 AM, CHALLENGER212 ABDERRAFI wrote: Hi, I have a raw fragmented table with 10 indexes, I want to load many files of approx 50 million row each file with external tables, the table now contain 163 Million row, but I remark that it begin loading very fast 8000 row/sec or more and after hours, the loding become too slow it step by 100 row/sec or 500 row/sec sometimes, how could I analyse this behaviour I have to load 1 billion row in this table and I want to kill the process and repeat from bigening each time, should I load a small numerous files of 5 million maybe each??? Help plz If I kill the process and restart the database, the initial load would be vers fast and after that it start to go very slow, help plz. ********************************************************************** ********* Forum Note: Use "Reply" to post a response in the discussion forum. References 1. https://yho.com/footer0

Hi, My Lrus_min_dirty et Max_dirty are as fllow 2% for Max and 1% for Min, because I have long checkpoints, for the foregrouds write here is the onstat -F : Fg Writes LRU Writes Chunk Writes 0 45125361 1574526 address flusher state data # LRU Chunk Wakeups Idle Tim 143e148c8 0 I 0 143756 332 193123 62268682.968 143e15160 1 I 0 141502 288 190220 61630692.493 143e159f8 2 I 0 142449 270 190588 61019413.312 143e16290 3 I 0 140818 272 189964 62015791.764 143e16b28 4 I 0 142012 261 190583 61407264.147 143e173c0 5 I 0 142238 232 190813 61167814.545 143e17c58 6 I 0 141896 217 190286 61095963.670 143e184f0 7 I 0 141722 196 189819 60874018.650

How are you copying the data from the external tables to the raw table? Are you using INSERT INTO ... SELECT FROM? Are you using a single SELECT UNIONing the data from the many files or are you using many INSERT INTO .... SELECT FROM statements? How many files and how many CPU VPs do you have? How many physical cores on the machine? Are the external tables in DELUXE mode or EXPRESS mode? How is the raw table partitioned? ROUND ROBIN? By expression? INTERVAL or LIST? Does the data in each file correspond to a single partition/fragment or would data from a single file be distributed across multiple partitions? Have you tried using my dbcopy utility to move the data from the external tables with a separate copy of dbcopy for each file? Dbcopy's algorithm is designed, in part, to prevent the kind of slowdowns from very large data sets that you are experiencing. 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 Wed, Oct 14, 2015 at 5:24 AM, CHALLENGER212 ABDERRAFI < abderrafi212@gmail.com> wrote: > Hi, I have a raw fragmented table with 10 indexes, I want to load many > files > of approx 50 million row each file with external tables, the table now > contain > 163 Million row, but I remark that it begin loading very fast 8000 row/sec > or > more and after hours, the loding become too slow it step by 100 row/sec or > 500 > row/sec sometimes, how could I analyse this behaviour I have to load 1 > billion > row in this table and I want to kill the process and repeat from bigening > each > time, should I load a small numerous files of 5 million maybe each??? Help > plz > If I kill the process and restart the database, the initial load would be > vers > fast and after that it start to go very slow, help plz. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001a1142d85e5f222d05220e8fc1

I'm usiing INSERT INTO ... SELECT FROM to load data on the raw table also I have one processes in wich I'm using Load from insert into to load other datas on this table, so I have 2 loads in process on ewith external table and an other with load from CPU VPs??? I did not understand physical cores on the machine: 4 CPUS with 4 cores each one I put the external table on Express but it switch to deluxe when i launch the load raw table partitioned is by expression, based on date: between date 1 and date 2 The data in each file correspond to a single partition: Yes, the files are by year Using dbcopy utility : I did not understand My problems is why it start so fast and then go too slow ???? is the other load from file ... can impact the load from used by extrenal table, shouldlaunch one load processes or should I split the files into small files??

I still suspect that IO is somehow involved - simply because that is what I've generally seen to be the issue when things run well for a while and then slow down. Do you have triggers on the table being loaded? If so, are the triggers scanning the table being loaded? If that is the case, then the trigger stored procedure might need to have update statistics run on the stored procedure once the table has grown. Also, you might want to run onstat -z and then run onstat -F once the load process starts slowing down. It could be that the LRU writes starting at that point and are taking a lot of resources from the CPUVP - just a guess. On Wednesday, October 14, 2015 6:30 AM, CHALLENGER212 ABDERRAFI <abderrafi212@gmail.com> wrote: I'm usiing INSERT INTO ... SELECT FROM to load data on the raw table also I have one processes in wich I'm using Load from insert into to load other datas on this table, so I have 2 loads in process on ewith external table and an other with load from CPU VPs??? I did not understand physical cores on the machine: 4 CPUS with 4 cores each one I put the external table on Express but it switch to deluxe when i launch the load raw table partitioned is by expression, based on date: between date 1 and date 2 The data in each file correspond to a single partition: Yes, the files are by year Using dbcopy utility : I did not understand My problems is why it start so fast and then go too slow ???? is the other load from file ... can impact the load from used by extrenal table, shouldlaunch one load processes or should I split the files into small files?? ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.

OK, see my responses in-line below: 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 Wed, Oct 14, 2015 at 7:29 AM, CHALLENGER212 ABDERRAFI < abderrafi212@gmail.com> wrote: > I'm usiing INSERT INTO ... SELECT FROM to load data on the raw table also I > have one processes in wich I'm using Load from insert into to load other > datas > on this table, so I have 2 loads in process on ewith external table and an > other with load from > So only two loads. You are loading "other data" in one session and copying from one external table at a time? Or is it a single external table defined with many files? If the latter, one thing you can try is having one external table for each input file and load from the many external tables in parallel using separate multiple sessions. > CPU VPs??? I did not understand > The server instance. Informix uses multiple server processes known and VPs or virtual processors. The ones of these VPs that process SQL requests from clients are known as the CPU VPs. How is the instance configured? How many CPU VPs? Run onstat -g glo and post the output. With only a single CPU VP (virtual processor task) the engine can only process a single session at a time, so having multiple CPU VPs is critical to server throughput when there are multiple sessions active. > physical cores on the machine: 4 CPUS with 4 cores each one > So, 16 cores. You should be running at least 16 CPU VPs (but might have as many as 64 running). > I put the external table on Express but it switch to deluxe when i launch > the > load > Why? Express is faster. > raw table partitioned is by expression, based on date: between date 1 and > date > 2 > The data in each file correspond to a single partition: Yes, the files are > by > year > OK, so you should definitely be loading the data using multiple sessions one per file! > > Using dbcopy utility : I did not understand > I have published an open source package or utilities for Informix users known as utils2_ak. That package contains, among others, a utility named dbcopy. Dbcopy is the fastest way to move data from one table to another and you are using external tables for the load, so using it is appropriate (as long as the source table doesn't have any LVARCHAR columns defined (because of a long standing bug in the Informix libraries one of the methods that dbcopy uses to make the copy process faster fails with a spurious error -831 if the source table has LVARCHAR type columns). > My problems is why it start so fast and then go too slow ???? is the other > There can be several reasons why the copy slows down over time. Here are a few: - The target table's EXTENT SIZE and NEXT SIZE are too small causing the copy to frequently have to stop to allocate a new extent. - The storage chunks the target table resides on is RAID5 or RAID6. - Your logical logs are too small - though with a RAW mode target table this is an unlikely candidate here. - Your logical log buffers are too small - this one can still affect you due to the extent issue. - You have LOTS of indexes which have to be rebalanced and pages split as the table grows. Try disabling or dropping the indexes before the load and re-enable or recreate them after the load. Make sure you are using parallel sorting and PDQPRIORITY when the indexes rebuild so the index rebuilds go as fast as possible. - Any single very long SELECT statement will tend to slow down over time. That is why when I build dbcopy I built it to select and insert the data differently from the way INSERT INTO ... SELECT FROM works. load from file ... can impact the load from used by extrenal table, should> launch one load processes or should I split the files into small files?? > You should have as many sessions loading data as there are partitions in the table with one for each file loading to a single partition. So, as I indicated above, there should be many external tables, one for each file. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001a1142d85ea57eb105220f367a

Oh, you can download the latest release of my utils2_ak package (free) from my website: www.askdbmgt.com/my-utilities.html. Download the package. The file is a shell archive. Just execute it with ksh, sh, or bash and it will extract itself into the current directory. There are build instructions in the BUILDING and README.1st files. However, on Linux it will build out of the box by just running "make". 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 Wed, Oct 14, 2015 at 7:29 AM, CHALLENGER212 ABDERRAFI < abderrafi212@gmail.com> wrote: > I'm usiing INSERT INTO ... SELECT FROM to load data on the raw table also I > have one processes in wich I'm using Load from insert into to load other > datas > on this table, so I have 2 loads in process on ewith external table and an > other with load from > CPU VPs??? I did not understand > physical cores on the machine: 4 CPUS with 4 cores each one > I put the external table on Express but it switch to deluxe when i launch > the > load > raw table partitioned is by expression, based on date: between date 1 and > date > 2 > The data in each file correspond to a single partition: Yes, the files are > by > year > > Using dbcopy utility : I did not understand > My problems is why it start so fast and then go too slow ???? is the other > load from file ... can impact the load from used by extrenal table, should> launch one load processes or should I split the files into small files?? > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --089e0149c0c6a5186d05220f426f

Hi this is the result of onstat -g glo MT global info: sessions threads vps lngspins time 3 34 9 0 93014 sched calls thread switches yield 0 yield n yield forever total: 448381298 447128835 2435982 2300468 228252272 per sec: 0 0 0 0 0 Virtual processor summary: class vps usercpu syscpu total cpu 1 4487.97 24271.55 28759.52 aio 1 0.23 0.58 0.82 lio 1 0.00 0.00 0.00 pio 1 0.00 0.00 0.00 adm 1 0.00 0.00 0.00 soc 2 0.00 0.00 0.00 msc 1 0.00 0.00 0.00 fifo 1 0.02 0.00 0.02 total 9 4488.22 24272.13 28760.35 Individual virtual processors: vp pid class usercpu syscpu total Thread Eff 1 25820 cpu 4487.97 24271.55 28759.52 105546607.87 0% 2 23860 adm 0.00 0.00 0.00 0.00 0% 3 6616 lio 0.00 0.00 0.00 0.57 0% 4 21476 pio 0.00 0.00 0.00 0.66 0% 5 18488 aio 0.23 0.58 0.82 2215156.39 0% 6 14996 msc 0.00 0.00 0.00 42048.42 0% 7 25040 fifo 0.02 0.00 0.02 0.61 2% 8 8184 soc 0.00 0.00 0.00 NA NA 9 24184 soc 0.00 0.00 0.00 NA NA tot 4488.22 24272.13 28760.35

OK, so that's a problem. You only have a single CPU VP which is single threading your sessions' work. You can modify the ONCONFIG file to change the VPCLASS parameter for the "cpu" type to increase the "num=1"to "num=16" and restart the instance. Alternatively, you can add CPU VPs on the fly using "onmode -p +15 cpu". Either way. Then see if that helps. But again, you'll need more than one session doing the copies to get best overall throughput. 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 Wed, Oct 14, 2015 at 9:51 AM, CHALLENGER212 ABDERRAFI < abderrafi212@gmail.com> wrote: > Hi this is the result of onstat -g glo > > MT global info: > sessions threads vps lngspins time > 3 34 9 0 93014 > > sched calls thread switches yield 0 yield n yield forever > total: 448381298 447128835 2435982 2300468 228252272 > per sec: 0 0 0 0 0 > > Virtual processor summary: > class vps usercpu syscpu total > cpu 1 4487.97 24271.55 28759.52 > aio 1 0.23 0.58 0.82 > lio 1 0.00 0.00 0.00 > pio 1 0.00 0.00 0.00 > adm 1 0.00 0.00 0.00 > soc 2 0.00 0.00 0.00 > msc 1 0.00 0.00 0.00 > fifo 1 0.02 0.00 0.02 > total 9 4488.22 24272.13 28760.35 > > Individual virtual processors: > vp pid class usercpu syscpu total Thread Eff > 1 25820 cpu 4487.97 24271.55 28759.52 105546607.87 0% > 2 23860 adm 0.00 0.00 0.00 0.00 0% > 3 6616 lio 0.00 0.00 0.00 0.57 0% > 4 21476 pio 0.00 0.00 0.00 0.66 0% > 5 18488 aio 0.23 0.58 0.82 2215156.39 0% > 6 14996 msc 0.00 0.00 0.00 42048.42 0% > 7 25040 fifo 0.02 0.00 0.02 0.61 2% > 8 8184 soc 0.00 0.00 0.00 NA NA > 9 24184 soc 0.00 0.00 0.00 NA NA > > tot 4488.22 24272.13 28760.35 > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --089e0149c0c6f0b8de052210ec0b

Hi, I have add 15 cpus by opnmode command but it seems tha same situation, too slow, my two load processes is slow knowing that it starts so fast more than 8000 rows/s and now it load by only 800 rows /s or less

As I said, adding more CPU VPs will not speed up any single process/session, and in general with IO waits a single CPU VP can handle one or two sessions efficiently, but I was suggesting that now that you have more VPs you try running MANY load tasks one for each input file with each file connected to a separate external table. See my other notes about how to speed up each individual process. 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 Wed, Oct 14, 2015 at 10:35 AM, CHALLENGER212 ABDERRAFI < abderrafi212@gmail.com> wrote: > Hi, I have add 15 cpus by opnmode command but it seems tha same situation, > too > slow, my two load processes is slow knowing that it starts so fast more > than > 8000 rows/s and now it load by only 800 rows /s or less > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001a11349ceedb58f10522118a56

Are you able to try the load without the 10 indexes on the table? Mike Walker Advanced DataTools Corporation -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of CHALLENGER212 ABDERRAFI Sent: Wednesday, October 14, 2015 8:36 AM To: ids@iiug.org Subject: Re: Slow Bulk Loading problem [35883] Hi, I have add 15 cpus by opnmode command but it seems tha same situation, too slow, my two load processes is slow knowing that it starts so fast more than 8000 rows/s and now it load by only 800 rows /s or less **************************************************************************** *** Forum Note: Use "Reply" to post a response in the discussion forum.

Hi, this is the result of onstat -g iof IBM Informix Dynamic Server Version 12.10.FC4 -- On-Line -- Up 1 days 02:34:42 -- 4860096 Kbytes AIO global files: gfd pathname bytes read page reads bytes write page writes io/s 3 **M:\\\\IFMXDATA\\\\ARCHIVAGE_CCP\\\\ROOTLOGDBS\\\\rootdbs_dat.000 26832896 6551 42045440 10265 0.0 op type count avg. time seeks 0 N/A reads 0 N/A writes 0 N/A kaio_reads 5026 121.5852 kaio_writes 9333 12.5019 4 **M:\\\\IFMXDATA\\\\ARCHIVAGE_CCP\\\\ROOTLOGDBS\\\\datatemp_ARCHIVAGE_CCP.000 12288 3 16384 4 0.0 op type count avg. time seeks 0 N/A reads 0 N/A writes 0 N/A kaio_reads 3 119.0422 kaio_writes 2 0.1060 5 **M:\\\\IFMXDATA\\\\ARCHIVAGE_CCP\\\\ROOTLOGDBS\\\\datatemp_ARCHIVAGE_CCP.001 4096 1 8192 2 11.2 op type count avg. time seeks 0 N/A reads 0 N/A writes 0 N/A kaio_reads 1 0.0379 kaio_writes 2 0.1144 6 **M:\\\\IFMXDATA\\\\ARCHIVAGE_CCP\\\\ROOTLOGDBS\\\\datalog_ARCHIVAGE_CCP.000 167936 41 31842304 7774 0.1 op type count avg. time seeks 0 N/A reads 0 N/A writes 0 N/A kaio_reads 10 81.8401 kaio_writes 5194 7.0370 7 **M:\\\\IFMXDATA\\\\ARCHIVAGE_CCP\\\\ROOTLOGDBS\\\\datalog_ARCHIVAGE_CCP.001 4096 1 0 0 7.5 op type count avg. time seeks 0 N/A reads 0 N/A writes 0 N/A kaio_reads 1 0.1338 kaio_writes 0 N/A 40 **M:\\\\IFMXDATA\\\\ARCHIVAGE_CCP\\\\DATAS\\\\DATACHUNK_OF.000 699043840 170665 36884480 9005 0.0 op type count avg. time seeks 0 N/A reads 0 N/A writes 0 N/A kaio_reads 72904 77.5283 kaio_writes 7297 12.8232 41 **M:\\\\IFMXDATA\\\\ARCHIVAGE_CCP\\\\DATAS\\\\DATACHUNK_OF.001 717266944 175114 0 0 0.0 op type count avg. time seeks 0 N/A reads 0 N/A writes 0 N/A kaio_reads 65172 80.3576 kaio_writes 0 N/A 42 **M:\\\\IFMXDATA\\\\ARCHIVAGE_CCP\\\\DATAS\\\\DATACHUNK_OF.002 478650368 116858 0 0 0.0 op type count avg. time seeks 0 N/A reads 0 N/A writes 0 N/A kaio_reads 43177 81.2606 kaio_writes 0 N/A 43 **M:\\\\IFMXDATA\\\\ARCHIVAGE_CCP\\\\DATAS\\\\DATACHUNK_OF.003 973074432 237567 0 0 0.0 op type count avg. time seeks 0 N/A reads 0 N/A writes 0 N/A kaio_reads 84575 78.0467 kaio_writes 0 N/A 44 **M:\\\\IFMXDATA\\\\ARCHIVAGE_CCP\\\\DATAS\\\\DATACHUNK_OF.004 262873088 64178 0 0 0.0 op type count avg. time seeks 0 N/A reads 0 N/A writes 0 N/A kaio_reads 25463 79.2424 kaio_writes 0 N/A 45 **M:\\\\IFMXDATA\\\\ARCHIVAGE_CCP\\\\DATAS\\\\DATACHUNK_OF.005 105947136 25866 0 0 0.0 op type count avg. time seeks 0 N/A reads 0 N/A writes 0 N/A kaio_reads 8893 81.0555 kaio_writes 0 N/A 46 **M:\\\\IFMXDATA\\\\ARCHIVAGE_CCP\\\\DATAS\\\\DATACHUNK_OF.006 590946304 144274 0 0 0.0 op type count avg. time seeks 0 N/A reads 0 N/A writes 0 N/A kaio_reads 48969 83.5233 kaio_writes 0 N/A 47 **M:\\\\IFMXDATA\\\\ARCHIVAGE_CCP\\\\DATAS\\\\DATACHUNK_OF.007 744337408 181723 0 0 0.0 op type count avg. time seeks 0 N/A reads 0 N/A writes 0 N/A kaio_reads 64085 81.8259 kaio_writes 0 N/A 48 **M:\\\\IFMXDATA\\\\ARCHIVAGE_CCP\\\\DATAS\\\\DATACHUNK_OF.008 458547200 111950 0 0 0.0 op type count avg. time seeks 0 N/A reads 0 N/A writes 0 N/A kaio_reads 42601 83.9845 kaio_writes 0 N/A

I can drop the indexes if I have the guerantee to rcreate them fast on ver 900 Millions rows

OK, so there's a major problem. Service times for your disks should, ideally, be less than 10ms (0.010s) for reads and less than 15ms (i0.015s) for writes. Your are getting 80 to 92 SECONDS per IO for your read service times and 7 to 10 SECOND service times for writes! Your IOs should be 1000 to 10,000 time faster! I'm not kidding! This may be the result of the Windows compression, or a problem with the underlying disk(s). Are the drives older than 4 years? If so, they may be experiencing massive IO errors that are being corrected internally without reporting to the OS and may soon fail. I just don't know at this point. 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 Wed, Oct 14, 2015 at 10:56 AM, CHALLENGER212 ABDERRAFI < abderrafi212@gmail.com> wrote: > Hi, this is the result of onstat -g iof > > IBM Informix Dynamic Server Version 12.10.FC4 -- On-Line -- Up 1 days > 02:34:42 > -- 4860096 Kbytes> > AIO global files: > gfd pathname bytes read page reads bytes write page writes io/s > 3 **M:\\\\IFMXDATA\\\\ARCHIVAGE_CCP\\\\ROOTLOGDBS\\\\rootdbs_dat.000 26832896 6551 > 42045440 10265 0.0 > > op type count avg. time > > seeks 0 N/A > > reads 0 N/A > > writes 0 N/A > > kaio_reads 5026 121.5852 > > kaio_writes 9333 12.5019 > > 4 **M:\\\\IFMXDATA\\\\ARCHIVAGE_CCP\\\\ROOTLOGDBS\\\\datatemp_ARCHIVAGE_CCP.000 12288 3 > 16384 4 0.0 > > op type count avg. time > > seeks 0 N/A > > reads 0 N/A > > writes 0 N/A > > kaio_reads 3 119.0422 > > kaio_writes 2 0.1060 > > 5 **M:\\\\IFMXDATA\\\\ARCHIVAGE_CCP\\\\ROOTLOGDBS\\\\datatemp_ARCHIVAGE_CCP.001 4096 1 > 8192 2 11.2 > > op type count avg. time > > seeks 0 N/A > > reads 0 N/A > > writes 0 N/A > > kaio_reads 1 0.0379 > > kaio_writes 2 0.1144 > > 6 **M:\\\\IFMXDATA\\\\ARCHIVAGE_CCP\\\\ROOTLOGDBS\\\\datalog_ARCHIVAGE_CCP.000 167936 > 41 > 31842304 7774 0.1 > > op type count avg. time > > seeks 0 N/A > > reads 0 N/A > > writes 0 N/A > > kaio_reads 10 81.8401 > > kaio_writes 5194 7.0370 > > 7 **M:\\\\IFMXDATA\\\\ARCHIVAGE_CCP\\\\ROOTLOGDBS\\\\datalog_ARCHIVAGE_CCP.001 4096 1 > 0 0 > 7.5 > > op type count avg. time > > seeks 0 N/A > > reads 0 N/A > > writes 0 N/A > > kaio_reads 1 0.1338 > > kaio_writes 0 N/A > > 40 **M:\\\\IFMXDATA\\\\ARCHIVAGE_CCP\\\\DATAS\\\\DATACHUNK_OF.000 699043840 170665 > 36884480 9005 0.0 > > op type count avg. time > > seeks 0 N/A > > reads 0 N/A > > writes 0 N/A > > kaio_reads 72904 77.5283 > > kaio_writes 7297 12.8232 > > 41 **M:\\\\IFMXDATA\\\\ARCHIVAGE_CCP\\\\DATAS\\\\DATACHUNK_OF.001 717266944 175114 0 0 > 0.0 > > op type count avg. time > > seeks 0 N/A > > reads 0 N/A > > writes 0 N/A > > kaio_reads 65172 80.3576 > > kaio_writes 0 N/A > > 42 **M:\\\\IFMXDATA\\\\ARCHIVAGE_CCP\\\\DATAS\\\\DATACHUNK_OF.002 478650368 116858 0 0 > 0.0 > > op type count avg. time > > seeks 0 N/A > > reads 0 N/A > > writes 0 N/A > > kaio_reads 43177 81.2606 > > kaio_writes 0 N/A > > 43 **M:\\\\IFMXDATA\\\\ARCHIVAGE_CCP\\\\DATAS\\\\DATACHUNK_OF.003 973074432 237567 0 0 > 0.0 > > op type count avg. time > > seeks 0 N/A > > reads 0 N/A > > writes 0 N/A > > kaio_reads 84575 78.0467 > > kaio_writes 0 N/A > > 44 **M:\\\\IFMXDATA\\\\ARCHIVAGE_CCP\\\\DATAS\\\\DATACHUNK_OF.004 262873088 64178 0 0 > 0.0 > > op type count avg. time > > seeks 0 N/A > > reads 0 N/A > > writes 0 N/A > > kaio_reads 25463 79.2424 > > kaio_writes 0 N/A > > 45 **M:\\\\IFMXDATA\\\\ARCHIVAGE_CCP\\\\DATAS\\\\DATACHUNK_OF.005 105947136 25866 0 0 > 0.0 > > op type count avg. time > > seeks 0 N/A > > reads 0 N/A > > writes 0 N/A > > kaio_reads 8893 81.0555 > > kaio_writes 0 N/A > > 46 **M:\\\\IFMXDATA\\\\ARCHIVAGE_CCP\\\\DATAS\\\\DATACHUNK_OF.006 590946304 144274 0 0 > 0.0 > > op type count avg. time > > seeks 0 N/A > > reads 0 N/A > > writes 0 N/A > > kaio_reads 48969 83.5233 > > kaio_writes 0 N/A > > 47 **M:\\\\IFMXDATA\\\\ARCHIVAGE_CCP\\\\DATAS\\\\DATACHUNK_OF.007 744337408 181723 0 0 > 0.0 > > op type count avg. time > > seeks 0 N/A > > reads 0 N/A > > writes 0 N/A > > kaio_reads 64085 81.8259 > > kaio_writes 0 N/A > > 48 **M:\\\\IFMXDATA\\\\ARCHIVAGE_CCP\\\\DATAS\\\\DATACHUNK_OF.008 458547200 111950 0 0 > 0.0 > > op type count avg. time > > seeks 0 N/A > > reads 0 N/A > > writes 0 N/A > > kaio_reads 42601 83.9845 > > kaio_writes 0 N/A > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001a1142d85e9997fa052212048f

When you rebuild the indexes set: PSORT_NPROCS=32 PDQPRIORITY=100 in your environment ( since this is windows you'll have to set it using setnet32) to get maximum parallelism for the index builds and minimum time. But your IO speed problems will nlimit index build speed. 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 Wed, Oct 14, 2015 at 11:02 AM, CHALLENGER212 ABDERRAFI < abderrafi212@gmail.com> wrote: > I can drop the indexes if I have the guerantee to rcreate them fast on ver > 900 > Millions rows > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --089e0149c0c67963940522121022

Is this a virtual system? On Wednesday, October 14, 2015 10:14 AM, Art Kagel <art.kagel@gmail.com> wrote: OK, so there's a major problem. Service times for your disks should, ideally, be less than 10ms (0.010s) for reads and less than 15ms (i0.015s) for writes. Your are getting 80 to 92 SECONDS per IO for your read service times and 7 to 10 SECOND service times for writes! Your IOs should be 1000 to 10,000 time faster! I'm not kidding! This may be the result of the Windows compression, or a problem with the underlying disk(s). Are the drives older than 4 years? If so, they may be experiencing massive IO errors that are being corrected internally without reporting to the OS and may soon fail. I just don't know at this point. 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 Wed, Oct 14, 2015 at 10:56 AM, CHALLENGER212 ABDERRAFI < abderrafi212@gmail.com> wrote: > Hi, this is the result of onstat -g iof > > IBM Informix Dynamic Server Version 12.10.FC4 -- On-Line -- Up 1 days > 02:34:42 > -- 4860096 Kbytes> > AIO global files: > gfd pathname bytes read page reads bytes write page writes io/s > 3 **M:\\\\IFMXDATA\\\\ARCHIVAGE_CCP\\\\ROOTLOGDBS\\\\rootdbs_dat.000 26832896 6551 > 42045440 10265 0.0 > > op type count avg. time > > seeks 0 N/A > > reads 0 N/A > > writes 0 N/A > > kaio_reads 5026 121.5852 > > kaio_writes 9333 12.5019 > > 4 **M:\\\\IFMXDATA\\\\ARCHIVAGE_CCP\\\\ROOTLOGDBS\\\\datatemp_ARCHIVAGE_CCP.000 12288 3 > 16384 4 0.0 > > op type count avg. time > > seeks 0 N/A > > reads 0 N/A > > writes 0 N/A > > kaio_reads 3 119.0422 > > kaio_writes 2 0.1060 > > 5 **M:\\\\IFMXDATA\\\\ARCHIVAGE_CCP\\\\ROOTLOGDBS\\\\datatemp_ARCHIVAGE_CCP.001 4096 1 > 8192 2 11.2 > > op type count avg. time > > seeks 0 N/A > > reads 0 N/A > > writes 0 N/A > > kaio_reads 1 0.0379 > > kaio_writes 2 0.1144 > > 6 **M:\\\\IFMXDATA\\\\ARCHIVAGE_CCP\\\\ROOTLOGDBS\\\\datalog_ARCHIVAGE_CCP.000 167936 > 41 > 31842304 7774 0.1 > > op type count avg. time > > seeks 0 N/A > > reads 0 N/A > > writes 0 N/A > > kaio_reads 10 81.8401 > > kaio_writes 5194 7.0370 > > 7 **M:\\\\IFMXDATA\\\\ARCHIVAGE_CCP\\\\ROOTLOGDBS\\\\datalog_ARCHIVAGE_CCP.001 4096 1 > 0 0 > 7.5 > > op type count avg. time > > seeks 0 N/A > > reads 0 N/A > > writes 0 N/A > > kaio_reads 1 0.1338 > > kaio_writes 0 N/A > > 40 **M:\\\\IFMXDATA\\\\ARCHIVAGE_CCP\\\\DATAS\\\\DATACHUNK_OF.000 699043840 170665 > 36884480 9005 0.0 > > op type count avg. time > > seeks 0 N/A > > reads 0 N/A > > writes 0 N/A > > kaio_reads 72904 77.5283 > > kaio_writes 7297 12.8232 > > 41 **M:\\\\IFMXDATA\\\\ARCHIVAGE_CCP\\\\DATAS\\\\DATACHUNK_OF.001 717266944 175114 0 0 > 0.0 > > op type count avg. time > > seeks 0 N/A > > reads 0 N/A > > writes 0 N/A > > kaio_reads 65172 80.3576 > > kaio_writes 0 N/A > > 42 **M:\\\\IFMXDATA\\\\ARCHIVAGE_CCP\\\\DATAS\\\\DATACHUNK_OF.002 478650368 116858 0 0 > 0.0 > > op type count avg. time > > seeks 0 N/A > > reads 0 N/A > > writes 0 N/A > > kaio_reads 43177 81.2606 > > kaio_writes 0 N/A > > 43 **M:\\\\IFMXDATA\\\\ARCHIVAGE_CCP\\\\DATAS\\\\DATACHUNK_OF.003 973074432 237567 0 0 > 0.0 > > op type count avg. time > > seeks 0 N/A > > reads 0 N/A > > writes 0 N/A > > kaio_reads 84575 78.0467 > > kaio_writes 0 N/A > > 44 **M:\\\\IFMXDATA\\\\ARCHIVAGE_CCP\\\\DATAS\\\\DATACHUNK_OF.004 262873088 64178 0 0 > 0.0 > > op type count avg. time > > seeks 0 N/A > > reads 0 N/A > > writes 0 N/A > > kaio_reads 25463 79.2424 > > kaio_writes 0 N/A > > 45 **M:\\\\IFMXDATA\\\\ARCHIVAGE_CCP\\\\DATAS\\\\DATACHUNK_OF.005 105947136 25866 0 0 > 0.0 > > op type count avg. time > > seeks 0 N/A > > reads 0 N/A > > writes 0 N/A > > kaio_reads 8893 81.0555 > > kaio_writes 0 N/A > > 46 **M:\\\\IFMXDATA\\\\ARCHIVAGE_CCP\\\\DATAS\\\\DATACHUNK_OF.006 590946304 144274 0 0 > 0.0 > > op type count avg. time > > seeks 0 N/A > > reads 0 N/A > > writes 0 N/A > > kaio_reads 48969 83.5233 > > kaio_writes 0 N/A > > 47 **M:\\\\IFMXDATA\\\\ARCHIVAGE_CCP\\\\DATAS\\\\DATACHUNK_OF.007 744337408 181723 0 0 > 0.0 > > op type count avg. time > > seeks 0 N/A > > reads 0 N/A > > writes 0 N/A > > kaio_reads 64085 81.8259 > > kaio_writes 0 N/A > > 48 **M:\\\\IFMXDATA\\\\ARCHIVAGE_CCP\\\\DATAS\\\\DATACHUNK_OF.008 458547200 111950 0 0 > 0.0 > > op type count avg. time > > seeks 0 N/A > > reads 0 N/A > > writes 0 N/A > > kaio_reads 42601 83.9845 > > kaio_writes 0 N/A > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001a1142d85e9997fa052212048f ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.

You need to zap the onstats (onstat -z) and then run the onstat to gather diagnostic information once the performance is sluggish. What is the machine, version, etc. On Wednesday, October 14, 2015 1:07 PM, CHALLENGER212 ABDERRAFI <abderrafi212@gmail.com> wrote: Hi, I have add 15 cpus by opnmode command but it seems tha same situation, too slow, my two load processes is slow knowing that it starts so fast more than 8000 rows/s and now it load by only 800 rows /s or less ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.