Oninit cdi / Archive
Talk to an expert →

Re: 11.70.FC6 linux load from seems afwully slow.

Posted in 2012

Topics: Performance & Tuning, Storage & Space Management, Server Administration, Logging & Checkpoints, Migration, Import/Export & Data Conversion, Platform-Specific Issues, Internationalization & Character Sets

Fernando Nunes — — source: Usenet: comp.databases.informix

Have you setup FET_BUF_SIZE? Do you see kaio threads? Don't forget that when you're "loading" you're reading ASCII and converting it. It will always be slower. A way to speed it up is to use external tables. This will be the fastest way and will solve your loading times problem. FET_BUF_SIZE should improve, but it should not make a dramatic difference. It would also be interesting to check your environment I/O capabilities. It would not be the first time that we would see VMWare saturated with a low I/O rate. It will help to create bigger page dbspaces (4KB at least) Regards. On Thu, Dec 20, 2012 at 7:11 PM, Art Kagel <art.kagel@gmail.com> wrote: > Make sure that the tables' initial extrnt is big enough to hold the entire > dataset being loaded. If you didn'tinclue the -ss flag to dbexport the > table starts out with a 16k default. extent. > > Art > On Dec 20, 2012 1:20 PM, <superboer7@t-online.de> wrote: > >> Hello ALl, >> >> I am trying to optimize a load from ( actually a dbimport) >> Initially all unlogged and after each test dbspaces are dropped and >> recreated >> they are on a ext2 filesystem. Direct IO switched on. >> >> Machine is on debian linux vmware 4 cpus 16 GB memory. >> >> Configured are 3.000.000 buffers lru cleaning switched off ( 99% and 98 % >> am doing my own checkpointing) when i run a load from insert into >> table_where_rowsize_1924_bytes. Columns in the table are char, int, >> decimal , date types nothing really special. >> the database dirtys pages at a rate of 5000 pages per second that is 10 >> MB a second. top says one oninit is eating aprox 95%CPU dbimport (or >> dbaccess) >> is eating 70 % of cpu.(have tryed it with a german locale and the >> default, no difference) >> >> This does not seem right. >> >> When i instead copy a table from dbspace a to b it dirtys 100.000 pages >> or more a second. >> >> When it is time to write the data to disk the i/o subsystem is doing >> 64 MB a second at 2000 request (iostat says so) and the io subsystem is >> 100 % busy. In this case writing the data to disk seems to be 6 times >> faster then >> loading it into the buffer cache. >> >> >> Last but not least writing seems to be done using 32 KB request eq the >> old 16 pages as MAXIO size. When i add a dbspace using onspaces it does a >> lot bigger requests and is capable of writing 250 MB a second. >> >> I guess one should take a real close look to this since disks are getting >> faster >> and this is killing performance. so if i can ask for a feature request: >> get rid of MAXIO eq 16 pages, make it bigger so we can have a better >> troughput. >> >> >> Thanks >> >> Superboer. >> _______________________________________________ >> Informix-list mailing list >> Informix-list@iiug.org >> http://www.iiug.org/mailman/listinfo/informix-list >> > > _______________________________________________ > Informix-list mailing list > Informix-list@iiug.org > http://www.iiug.org/mailman/listinfo/informix-list > > -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently...

↪ replying to Fernando Nunes
superboer7@t-online.de — — source: Usenet: comp.databases.informix

Hello Fernando, all results including HPL: loading 1.4 GB table 2k page copy to memory using load 140 secs, writing to disk at 64 MB/sec 22 secs total 162 secs 16 page copy to memory using load 140 secs, writing to disk at 150 MB/sec 9 secs total 149 secs both had dbaccess eating 80 % cpu and one oninit eating 80 % CPU HPL untuned did 36 MB /sec writing loading total 39 secs only one onpload eating 100 % cpu HPL overtuned with: CONVERTTHREADS 8 # Number of conversion threads per device CONVERTVPS 8 # Max number of vps for converters (total) # Buffer Configuration STRMBUFFSIZE 16384 # Buffer size for server stream buffer (kbytes) STRMBUFFERS 8 # Number of server stream buffers per device AIOBUFSIZE 16384 # Buffer size for tape/file I/O (kbytes) AIOBUFFERS 8 # Number of buff on a 4 cpu box did: Device: rrqm/s wrqm/s r/s w/s rkB/s wkB/s avgrq-sz avgqu-sz await svctm %util sda 0,00 0,00 0,00 0,00 0,00 0,00 0,00 0,00 0,00 0,00 0,00 sdb 0,00 0,00 0,00 0,00 0,00 0,00 0,00 0,00 0,00 0,00 0,00 sdc 0,00 5404,00 0,00 731,60 0,00 151232,00 413,43 5,55 7,58 0,58 42,72 so 150 MB writing is 9 secs in this case the all the 4 cpus were 100 % busy. As far as i can tell here the io size is a lot bigger then 32K. See you Superboer.

Glossary