RE: shareplex replication speed up

Zhu,
Other situations we found shareplex made worse on posting were:

• blind updates, the app was updating the entire row (including indexed columns) when actually a subset of the row (non-indexed columns) changed. This is poor practice for both source and target but was exasperated by shareplexes need to build bigger queue entries and the post the results. Are the key columns actually changing? We ended up fixing the app - but I believe you can put logic in side the post process to avoid this.
• tables without primary keys defined. We had one that shareplex handled posting updates/deletes by putting every column in the where clause. You can see that in the sql_trace file of the post process.

Hope that helps.

Bruce McCartney |DBIS |*403 615 3350 | bruce.mccartney_at_dbinfosystems.com  

> -----Original Message-----
> From: y chao [mailto:zhuchao_at_gmail.com]
> Sent: November 15, 2005 7:04 AM
> To: Bruce McCartney
> Cc: oracle-l_at_freelists.org
> Subject: Re: shareplex replication speed up
>
> Thanks for your sharing. We did stripe the volume using
> hardware. IO on queue filesystem is actually excellent , less
> than 3ms for average read/write.
>
> Seems we reached some kind of bottlenecks. I noticed each
> insert/delete statement caused average 3 disk IO/30 logical
> io(due to a lot of indexes to update), and each Disk read is
> around 8ms. Taking account into the other overhead like
> Logical accesss etc, each insert/delete takes around 24ms. So
> one second we can do around 50 DML on that table. Around 100
> on two core tables.
>
> The capture process is fine. Post queue delays. One dirty tip
> is to set to unsafe mode, but we can't always rely on such solution.
>
> We can talk more about using splex experience offline.
>
> By the way, index leaf node split is not much. I checked it
> in statspack.
> Thank you
> On 11/14/05, Bruce McCartney
> <bruce.mccartney_at_dbinfosystems.com> wrote:
> > Zhu,
> > We had similar issues with shareplex and were able to scale
> to much higher numbers by most of what you sugested. We also
> found that the filesystem I/O on the shareplex queue files
> were a bottleneck. Using hardware sriping there really helped
> - especially if they got large as the I/O routines of
> shareplex seemed to worsen the deeper the queue (i believe
> this has since been resolved - but you are on old software).
> > The other thing to look at is row migration (expansion of a
> row causing it to relocate). This operation causes
> additional overhead for the capture process mostly so may not
> apply if capture is keeping up but posting is lagging...
> >
> > Hope that helps
> >
> > Bruce McCartney
> > 403.615.3350
> >
> >
> > -----Original Message-----
> > From: "zhu chao"<zhuchao_at_gmail.com>
> > Sent: 11/14/05 8:22:15 AM
> > To: "Oracle-L"<oracle-l_at_freelists.org>
> > Subject: shareplex replication speed up
> >
> > hi, all,
> > We are using shareplex to replicate transaction
> data. We are still
> > using old oracle 8174 and shareplex 3.1.4.
> > We consistently see shareplex unable to keep up with primary
> > database so we have to slow down every batch job. We are
> only able to
> > do less than 120 transactions per second. Most
> transactions happen on
> > two core tables and there are a lot of indexes in these
> two tables.
> > Box is running on a 12 CPU box and with tons of memory. SAN
> > performance is ok (less than 10ms for read and 5 ms for write).
> > Currently we tried:
> > --increase sga as big as possible.
> > --rebuild index on the table to reduce index size to
> make it keep in
> > data buffer.
> > --set keep buffer pool for core tables.
> > -- we used quick IO for oracle redo log files..
> > -- we setup multiple post queue to make parallel post.
> > --the post queue process oracle sessions is mainly
> waiting on db file
> > sequential read.
> >
> > Any other ideas to make shareplex replicate faster?
> >
> > Regards
> > Zhu Chao
> > www.cnoug.org
> > --
> > http://www.freelists.org/webpage/oracle-l
> >
> >
> >
> >
> >
> >
>
>
> --
> Regards
> Zhu Chao
> www.cnoug.org
>

--
http://www.freelists.org/webpage/oracle-l