Re: 11.70.FC6 linux load from seems afwully slow.
Ahh, I hadn't noticed that. IO rates under VMWare are attrocious! If you
are not using the new hypervisor (vSphere? whatever) IO rates top out at
about 60MB/s. In our testing for a client we could only get a bit over
100MB/s with the older hypervisor or none at all.
Art
Art S. Kagel
Advanced DataTools (www.advancedatatools.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 my employer, Advanced DataTools, 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 Thu, Dec 20, 2012 at 2:57 PM, Fernando Nunes <domusonline@gmail.com>wrote:
> 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...
>
> _______________________________________________
> Informix-list mailing list
> Informix-list@iiug.org
> http://www.iiug.org/mailman/listinfo/informix-list
>
>
Hello Art, Fernando,
First of all thanks for the responses!!
ok some numbers, sorry it did got mixed up in cutting and pasting anways:
load with shm connection and FET_BUF_SIFE=32000 or empty
no difference. and yeah i have kaio iostat tells me that the writes done
are 32 K and that the disks are almost always 80 to 100 % busy.
time onspaces -c -d tessie -p /data2/infdev/tessie.000 -o 0 -s 15000000
Verifying physical disk space, please wait ...
Space successfully added.
** WARNING ** A level 0 archive of Root DBSpace will need to be
done.
real 1m4.566s
user 0m0.004s
sys 0m0.000s
iostat onspaces:
21.12.2012 12:11:38
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 0,00 0,00 531,00 0,00 240160,00 904,56 143,51 271,31 1,88 100,00
dbspace with 16K
21.12.2012 11:51:42
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 0,00 0,00 3993,60 0,00 121158,40 60,68 6,48 1,62 0,24 95,68
dbspace with 2K
21.12.2012 12:05:38
Device: rrqm/s wrqm/s r/s w/s rkB/s wkB/s avgrq-sz avgqu-sz await svctm %util
sda 0,00 0,60 0,20 2,20 0,80 11,20 10,00 0,01 3,67 0,67 0,16
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 0,00 0,00 1994,80 0,00 61408,00 61,57 0,95 0,48 0,44 87,20
2k:
Confirmed with onstat -Rr |grep dirty
1436536 dirty, 3600000 queued, 3600000 total, 4194304 hash buckets, 2048 buffer size
1284888 dirty, 3600000 queued, 3600000 total, 4194304 hash buckets,
1130840 dirty, 3600000 queued, 3600000 total, 4194304 hash buckets,
978344 dirty, 3600000 queued, 3600000 total, 4194304 hash buckets,
832952 dirty, 3600000 queued, 3600000 total, 4194304 hash buckets,
679240 dirty, 3600000 queued, 3600000 total, 4194304 hash buckets,
(1130840-978344) / 5 is 30.000 pages is 60 MB / sec
Bottom line is that 16k pagesize is doing twice as good as 2k despite the fact that it also has a 32 K buffer. Looking at onspaces it seems to have a 500 K buffer and does 4 times better as 2K.
My opinion is that this performance is no good and it is getting towards discussing 2 GB chunks yes/no, this is one of the old dogs in the engine needing a face lift. You may not choose to do so, then i guess it will get worse.
regarding load:
-->Don't forget that when you're "loading" you're reading ASCII and converting it. It will always be slower.
Yeah but i do not think a factor 6 is acceptable compared to writing to disk.
and it is worse when it uses the full capability of the i/o subsystem then
it is a factor 24!!!
See you
Superboer.
Hi Superboer,
Did you test a I/O performance with dd?
dd if=/dev/zero bs=2k count=1000000 of=/your/chunk
dd if=/dev/zero bs=16k count=125000 of=/your/chunk
Looking the numbers don't appear be the situation bellow.. so..
anyway... here is my comments.
You said : lru cleaning switched off ( 99% and 98 % am doing my own
checkpointing)
Just make prety sure you aren't going into LRU flush. (onstat -F) once
you get into them there is no way to exit...
if you try force a checkpoint with onmode -c after LRU flush was
started, the command freeze and start only after the lru flush finhish,
and with data incoming from the load, will take a long time.
I already get into this situation and needed write a script to check
every second for the dirty buffers and force a checkpoint when reach 50%
of the buffers because at the environment I was working , sometimes the
load are so fast they load 4 GB of buffers in couple seconds.
On 21/12/2012 16:27, superboer7@t-online.de wrote:
> Hello Art, Fernando,
>
>
> First of all thanks for the responses!!
>
> ok some numbers, sorry it did got mixed up in cutting and pasting anways:
>
> load with shm connection and FET_BUF_SIFE=32000 or empty
> no difference. and yeah i have kaio iostat tells me that the writes done
> are 32 K and that the disks are almost always 80 to 100 % busy.
>
>
> time onspaces -c -d tessie -p /data2/infdev/tessie.000 -o 0 -s 15000000
> Verifying physical disk space, please wait ...
> Space successfully added.
> ** WARNING ** A level 0 archive of Root DBSpace will need to be
>
> done.
>
> real 1m4.566s
> user 0m0.004s
> sys 0m0.000s
>
>
> iostat onspaces:
> 21.12.2012 12:11:38
> 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 0,00 0,00 531,00 0,00 240160,00 904,56 143,51 271,31 1,88 100,00
>
> dbspace with 16K
> 21.12.2012 11:51:42
> 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 0,00 0,00 3993,60 0,00 121158,40 60,68 6,48 1,62 0,24 95,68
>
>
> dbspace with 2K
> 21.12.2012 12:05:38
>
> Device: rrqm/s wrqm/s r/s w/s rkB/s wkB/s avgrq-sz avgqu-sz await svctm %util
> sda 0,00 0,60 0,20 2,20 0,80 11,20 10,00 0,01 3,67 0,67 0,16
> 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 0,00 0,00 1994,80 0,00 61408,00 61,57 0,95 0,48 0,44 87,20
>
>
> 2k:
>
> Confirmed with onstat -Rr |grep dirty
>
> 1436536 dirty, 3600000 queued, 3600000 total, 4194304 hash buckets, 2048 buffer size
> 1284888 dirty, 3600000 queued, 3600000 total, 4194304 hash buckets,
> 1130840 dirty, 3600000 queued, 3600000 total, 4194304 hash buckets,
> 978344 dirty, 3600000 queued, 3600000 total, 4194304 hash buckets,
> 832952 dirty, 3600000 queued, 3600000 total, 4194304 hash buckets,
> 679240 dirty, 3600000 queued, 3600000 total, 4194304 hash buckets,
>
>
> (1130840-978344) / 5 is 30.000 pages is 60 MB / sec
>
>
> Bottom line is that 16k pagesize is doing twice as good as 2k despite the fact that it also has a 32 K buffer. Looking at onspaces it seems to have a 500 K buffer and does 4 times better as 2K.
>
> My opinion is that this performance is no good and it is getting towards discussing 2 GB chunks yes/no, this is one of the old dogs in the engine needing a face lift. You may not choose to do so, then i guess it will get worse.
>
>
> regarding load:
>
> -->Don't forget that when you're "loading" you're reading ASCII and converting it. It will always be slower.
>
> Yeah but i do not think a factor 6 is acceptable compared to writing to disk.
> and it is worse when it uses the full capability of the i/o subsystem then
> it is a factor 24!!!
>
> See you
>
> Superboer.
>
> _______________________________________________
> Informix-list mailing list
> Informix-list@iiug.org
> http://www.iiug.org/mailman/listinfo/informix-list