Thanks for your response and your suggestions.
41 seconds.
4) commit.
Thanks for your time.
Post by Fernando Nunes2- How are your databases configured? Buffered or unbuffered
logging?
Database Name Owner In Dbspace Status
sysmaster informix dati U
sysutils informix dati U
eda root dati U*
U* -- Mode ANSI (unbuffered logging)
Hmmm... ANSI... That changes a couple of things...
3- Does your process execute many transactions (COMMITs)?
Yes, it does many commit.
On each commit the logical log buffer is flushed (writen to disk) and at
the same time copied to "HDR buffers". There are 12 of these. Not
configurable.
4- What are your instance parameters (DR*, LOGBUFFER...)?
# Data Replication Variables
DRINTERVAL -1
DRTIMEOUT 30
DRLOSTFOUND /opt/informix/etc/dr.lostfound
LOCKS 100000
BUFFERS 50000
PHYSBUFF 10000
LOGBUFF 5000
MULTIPROCESSOR 1
SINGLE_CPU_VP 0
VPCLASS CPU,num=10
VPCLASS AIO,num=2
NETTYPE soctcp,3,250,NET
5- Apart from HDR configurations, are the standard instance and
primary
instance configured te same way and running with the same resources?
Both machines are the same, and they are directly connected with
ethernet cable.
6- During the process do you see a lot of flags "G" in onstat -u?
No,
Userthreads
address flags sessid user tty wait tout locks
nreads nwrites
4d8ca018 ---P--D 1 root - 0 0 0 17
21
4d8ca618 ---P--F 0 root - 0 0 0 0
5
4d8cac18 ---P--F 0 root - 0 0 0 0
0
4d8cb218 ---P--F 0 root - 0 0 0 0
0
4d8cb818 ---P--F 0 root - 0 0 0 0
0
4d8cbe18 ---P--F 0 root - 0 0 0 0
0
4d8cc418 ---P--F 0 root - 0 0 0 0
0
4d8cca18 ---P--F 0 root - 0 0 0 0
0
4d8cd018 ---P--F 0 root - 0 0 0 0
0
4d8cd618 ---P--- 8 root - 0 0 0 0
0
4d8cdc18 ---P--B 9 root - 0 0 0 0
0
4d8ce218 --BPX-- 29 informix 0 0 0 1 92 1593
4d8ce818 ---P--D 22 root - 0 0 0 0
0
4d8cee18 ---P--D 13 root - 0 0 0 16
5
4d8cf418 Y--P--D 20 root - 4e2aa7a0 0 0 2107
3
4d8cfa18 ---P--D 21 root - 0 0 0 0
0
16 active, 128 total, 25 maximum concurrent
I trust you, but did you take several snapshots?
IN the case above I'd do "onstat -g ses 29 -r 1 | grep sqlexec"
And I suppose you got thi in the HDR enabled environment?
"G" flags means the session is waiting for a free logical log buffer
(there are 3 of those, not configurable).
This will happen when during the logical log buffer flush, there are no
free HDR buffers. Normally this can be caused by too frequent flushes or
by a slow secondary (that doesn't keep up with the HDR buffers "cleaning").
The "problem" with unbuffered or ANSI databases is that each commit will
trigger a flush, even if you're not on synchronous mode.
In your case you're in synchronous...
Almost everyone tends to give too much importance to bandwidth and
almost forget around round trip times. You clearly have a very good
bandwidth and I expect the round trip time to be very good (check
please).
The file transfer speed between the two machines is around 100MB/s
That's to be expected with 1GB ethernet, but again... that's not the
problem in most cases.
I'm used to see this every day (not yet saying that's what's
happening to you).
The typical situation are ETL lookup stages.
What is ETL ?
Extraction, Transformantion and Loading. An acronym used to referr to
batches that extract data from OLTP systems and load it in Data
warehouses after transforming it.
It was just an example... Many of this jobs process a large set of rows,
and for each sometime have to do a lookup in a reference table.
Lot's of incredible fast
queries that in the end seem to take forever. Why? Because it's like
playing ping-pong... the ball rarely is touching somewhere. But
it takes
time constantly traveling.
How can this apply to you? Well, in theory you may be facing a
lot of
message interchange between the two servers.
So I see a low utilization of the network due to a high exchange of
messages, rather than data?
It's a possibility...
One strange thing I noticed is that the amount of data concerned by
the test program are approximately 190 Mbytes, while network traffic
involves 1,80 Gbytes, why ?
What are you comparing? 190MB is what? The amount of data in the tables
affected? The ammount of data that is INSERTEd/UPDATEd/DELETEd?
Something interesting to compare would be the logical log space consumed
with the network traffic.
Things that would
1- unbuffered logging
Yes
2-many commits
Yes, (but in standard mode it takes only 26 minutes)
In standard mode you don't send part of a logical log buffer after each
commit...
Would it be possible to reduce the number of commits, just for testing
purposes?
3- small LOGBUFFER
Your logbuffer is large. But it's not being used (almost ure about it).
Check "onstat -l" and see how many pages per flush your doing....
4- HDR configured in synchronous mode
Yes, but I prefer it for security
I can understand that, but you'll pay some price. Question i, if the
"price" you're seeing is justifiable or if there is something we can do
to improve it.
5- slow secondary
The machines are identical.
Ok...
1- SET BUFFERED LOG (run in the session that executes the batch)
how can I do it ?
You could alter the code or do it in sysdbopen() procedure. But it will
not change anything because you're using ANSI mode
2- Make sure you're not using synchronous mode
I use it.
3- Increase your LOGBUFFER
Probably will not make any difference becaue of the constant flushing.
I'm not sure if I'm able to help you any further... did you open a PMR?
Or wait for more knowledgeable help here...The group is regularly
monitored by the MACH 11 architect... He knows more about it, than I'll
ever learn...
Regards.
Regards
On Tue, Aug 13, 2013 at 1:51 PM, Francesco Alfano
Hi,
I have a job that needs 26 minutes on a Informix server in
Standard
configuration.
When I put a server in Hdr configuration with another on a
dedicated 1Gb
ethernet line the same job requires 118 minutes.
Using iftop utility I have saw that the ethernet speed is
almost 150KB/s
( iftop -i eth1 -B ) on a 1Gb cable.
The server is a 2 dual processor with 24 cores and informix
takes almost
2% of cpu during job elaboration.
How can I speed up informix ?
_________________________________________________
Informix-list mailing list
http://www.iiug.org/mailman/__listinfo/informix-list
<http://www.iiug.org/mailman/listinfo/informix-list>
--
Fernando Nunes
Portugal
http://informix-technology.__blogspot.com
<http://informix-technology.blogspot.com>
My email works... but I don't check it frequently...
Thanks for your help.
--
Fernando Nunes
Portugal
http://informix-technology.blogspot.com
My email works... but I don't check it frequently...