RSS with DELAY_APPLY is dog slow!

[Nick]

Hello,

Does anyone have experience with RSS and the DELAY_APPLY feature?

I am running tests, with the end goal to find the best HDR or RSS
setup that will minimize the performance impact on my primary db
server. That is, I want the best possible performing primary for my
OLTP environment. I don't care if the secondary lags behind a little.
The secondary is at a remote location with a dedicated T3 between the
sites for this purpose.

But I am finding that RSS with DELAY_APPLY=2H (or any value that I
have tried), the primary server updates/inserts are severely
impacted. Why would this be the case? I thought the goal of RSS was
to just shoot the log records off, and let them be applied at will on
the secondary, with the primary not caring if/when they are received.

My tests are also showing that HDR yields faster primary performance
than RSS, which further confounds me.

Here is my simple test: drop test table, create test table, revoke
privs, lock table exclusively, load from file insert into... (1mil
rows), then index.

This takes on average (I have run it multiple times to get good
averages):
2.0min - no replication
2.5min - HDR replication
3.0min - RSS replication with DELAY_APPLY=0
14.0min - RSS replication with DELAY_APPLY=2H

Here is my setup:
IDS 11.50.FC7 on Sun Solaris 10, Intel processor 64bit
LOGBUFF 64
DRAUTO 0
DRINTERVAL 30
DRTIMEOUT 30
DRIDXAUTO 0
LOG_INDEX_BUILDS 1
UPDATABLE_SECONDARY 0
TEMPTAB_NOLOG 0
DELAY_APPLY 2H # This is the only value that I have tweaked in
this test
STOP_APPLY 0
LOG_STAGING_DIR /u08/ifxdb2t/logs
RSS_FLOW_CONTROL -1

Any help or observation would be appreciated.

Thanks,
Nick

[jrenaut]

That is odd, you already have RSS_FLOW_CONTROL turned off, so it
shouldn't be that. I guess I'd be curious to see the thread states on
the RSS with delay apply to see if that identifies any bottle neck
(just monitor onstat -g ath output over the run looking at the sqlexec
thread doing the test and the rss_ threads and smx threads and see
when they aren't running wait that reports them as waiting on). When
I get some time, I'll see if I can do a similar test and see if I
duplicate your results, and if so, see if I can spot anything in the
delay apply test.

Jacques Renaut
IBM Informix Advanced Support
APD Team

[Art Kagel]

Check out HDR with DRINTERVAL=0.  Performance on the primary should be just as good as with it set to 30 but the secondary will be almost insync.

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.

_______________________________________________
Informix-list mailing list
Inform...@iiug.org
http://www.iiug.org/mailman/listinfo/informix-list

[Jeff Filippi]

Hi Nick,

FYI, RSS_FLOW_CONTROL did not come out until 11.50FC8 unless you received it
in a patch for 11.50FC7.
How far away is your RS server from your primary?

Jeff

[jrenaut]

On Nov 10, 4:21 pm, "Jeff Filippi" <i...@itdataconsulting.com> wrote:
> Hi Nick,
>
> FYI, RSS_FLOW_CONTROL did not come out until 11.50FC8 unless you received it
> in a patch for 11.50FC7.
> How far away is your RS server from your primary?
>
> Jeff
>
> -----Original Message-----
> From: informix-list-boun...@iiug.org [mailto:informix-list-boun...@iiug.org]
>
> On Behalf Of Nick
> Sent: Thursday, November 10, 2011 2:53 PM

> To: informix-l...@iiug.org

> Informix-l...@iiug.orghttp://www.iiug.org/mailman/listinfo/informix-list

Yeah we'd definitely want to know the exact version you are using to
verify if RSS_FLOW_CONTROL is in your version or not. If you are in a
version that doesn't have it, then for a couple releases (I don't
remember exactly but between something like 11.50.xC4 and 11.50.xC8)
the RSS secondaries were more tightly coupled to the primary and so if
they fell behind, it did impact the primary. So starting with
11.50.xC8 and with RSS_FLOW_CONTROL you can decide if you want to
couple the rss secondary to the primary or not.

Jacques Renaut

[Nick]

Jeff - thanks, I found RSS_FLOW_CONTROL in the online docs and copied
it into my onconfig. You might be on to something if it is being
ignored in xc7 (there is nothing in the log at all recognizing
RSS_FLOW_CONTROL).

Art - my next test is with DRINTERVAL=0 to see how that impacts each
scenario. I have to wait until tonight to bounce my test environment
as it's in use by developers today. And I might increase LOGBUFFER to
128 to see if that changes anything.

Jacque - I looked at the ath output, but was not sure what I was
looking for. I'll try again with your input...

[Fernando Nunes]

dbaccess sysmaster <<EOF

SELECT * FROM syscfgtab WHERE cf_name = 'RSS_FLOW_CONTROL';
EOF

Regards

_______________________________________________
Informix-list mailing list
Inform...@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...

[Nick]

ok - I think you guys helped find it...

> dbaccess sysmaster <<EOF
>
> SELECT * FROM syscfgtab WHERE cf_name = 'RSS_FLOW_CONTROL';
> EOF

No rows found.

Which looks like RSS_FLOW_CONTROL was not implemented in xC7.

And watching onstat -g ath I notice the following (when testing RSS
with DELAY_APPLY=2h):

The top and bottom lines here are the relevant lines that change
during my test (this is on the primary). The smxrcv thread starts
counting down from 11 to 1, at which time the one sqlexec thread wakes
up and goes into IO Wait state for about 1-2 seconds. It then goes
back to "sleeping sec: 1", as below, and stays there until the smxrcv
thread counts down again from "sleeping sec: 11" down to 1.

879 209117d78 2072e1610 3 sleeping secs:
7 1cpu smxrcv ifxdb2t
886 20aaf7db0 2072e26c0 3 cond wait smx
pipe1 3cpu smxsnd ifxdb2t
1695 20ae62d20 2072dd350 3 sleeping secs:
1 3cpu sqlexec

It's almost like the sqlexec thread is working only 1-2 seconds out of
every 11. Compared to the sqlexec thread bouncing around constantly
with HDR.

And then I found this in the release notes - bug fixes in 11.50.xC8:

IC70316 RSS FAILS TO RE-CONNECT TO PRI WITH PRIMARY SMXSND THREAD
IN COND WAIT SMX PIPE1

Which looks suspiciously like it matches here...

Thanks for all your help, I think I need to upgrade!

Nick