Showing posts with label subscribing. Show all posts
Showing posts with label subscribing. Show all posts

Wednesday, March 28, 2012

Replication system disk performance issue after 1 month

Summary: Started replication April 1 of 4M xact / day publishing system to subscribing system.

Performance was good. Latency was ~ 5-7 seconds.

May 10 we noticed that the DB was behind (latency was 12 hours).

All performance counters seem good with the exception of the disk.

. Performance spikes are 8 minutes apart and last from 30 - 60 seconds.

. During this period, Disk % Busy (1 - Disk % Idle) is 100%

The publisher DB publishes about 50-52 xacts/sec.

Rate of distribution (distribution DB to Subscriber DB) is ~ 47 xacts / second, so latency is increasing (currently at 33 hours). Previously my Subscriber system's "capacity" was 150 xacts / sec.

I know this because several weeks ago, the network went down, we were 24 hours behind.

When the network came back up the replication subscriber system was able to catchup at around 150 xacts / sec, or 3X the production system rate.

What has changed between then and now? Not much. We did install Tivoli Service Manager (IBM's backup system) a couple of weeks ago. It seems to run fine on a nightly basis, but I don't see any periodic heavy Disk I/O from that. Just to be sure, I've had them shut the TSM services down just to be sure.

We've also eliminated all extraneous processes other than those I need for performance monitoring (there was a RTVscan, virus scan process).

I've eliminated Autogrowth's as an issue as I've bumped the growth so that they are very infrequent (several days at this point. When we resolve the problem, I'll dial this down to something more reasonable.

My disk configuration is not ideal I realize (single Raid-5 disk with 3 partitions), however, this has not changed in the 6 weeks.

Thanks for any help on this!

Jack Griffith

Configuration:

Subscribing System:

SQL Server: 2000, SP4 - 8.0.2039

CPU - 2.8GHZ Xeon, Quad Dual-core

Memory - 3.5GB RAM

Disk: 3 partitions on a single RAID-5 disk with 1118 GB of space:

C: 39GB System and Programs

D: 97GB Log space

E: 982 GB Data space

Replication configuration:

- nosynch, continuous Transactional Replication

- Distribution db is on Subscription system

- distribution - Publication of approx. 50 transactions / second

Subscriber DB configuration:

DB size: 64458 MB

Logging: Simple (at this point)

distribution

DB size: 3111 MB

Logging: Simple (at this point)

Is the whole topology (pub,dist and sub) SQL 2000 SP2? Do you know if logreader agent or distribution agent is the bottleneck? Did you add any new indexes or triggers at the subscriber db?

Replication system disk performance issue after 1 month

Summary: Started replication April 1 of 4M xact / day publishing system to subscribing system.

Performance was good. Latency was ~ 5-7 seconds.

May 10 we noticed that the DB was behind (latency was 12 hours).

All performance counters seem good with the exception of the disk.

. Performance spikes are 8 minutes apart and last from 30 - 60 seconds.

. During this period, Disk % Busy (1 - Disk % Idle) is 100%

The publisher DB publishes about 50-52 xacts/sec.

Rate of distribution (distribution DB to Subscriber DB) is ~ 47 xacts / second, so latency is increasing (currently at 33 hours). Previously my Subscriber system's "capacity" was 150 xacts / sec.

I know this because several weeks ago, the network went down, we were 24 hours behind.

When the network came back up the replication subscriber system was able to catchup at around 150 xacts / sec, or 3X the production system rate.

What has changed between then and now? Not much. We did install Tivoli Service Manager (IBM's backup system) a couple of weeks ago. It seems to run fine on a nightly basis, but I don't see any periodic heavy Disk I/O from that. Just to be sure, I've had them shut the TSM services down just to be sure.

We've also eliminated all extraneous processes other than those I need for performance monitoring (there was a RTVscan, virus scan process).

I've eliminated Autogrowth's as an issue as I've bumped the growth so that they are very infrequent (several days at this point. When we resolve the problem, I'll dial this down to something more reasonable.

My disk configuration is not ideal I realize (single Raid-5 disk with 3 partitions), however, this has not changed in the 6 weeks.

Thanks for any help on this!

Jack Griffith

Configuration:

Subscribing System:

SQL Server: 2000, SP4 - 8.0.2039

CPU - 2.8GHZ Xeon, Quad Dual-core

Memory - 3.5GB RAM

Disk: 3 partitions on a single RAID-5 disk with 1118 GB of space:

C: 39GB System and Programs

D: 97GB Log space

E: 982 GB Data space

Replication configuration:

- nosynch, continuous Transactional Replication

- Distribution db is on Subscription system

- distribution - Publication of approx. 50 transactions / second

Subscriber DB configuration:

DB size: 64458 MB

Logging: Simple (at this point)

distribution

DB size: 3111 MB

Logging: Simple (at this point)

Is the whole topology (pub,dist and sub) SQL 2000 SP2? Do you know if logreader agent or distribution agent is the bottleneck? Did you add any new indexes or triggers at the subscriber db?sql

Monday, March 12, 2012

Replication Over FTP, Initial Snapshot Timeout

Hello,
I am trying to replicate over FTP (merge replication) and I'm having
trouble with subscribing.
After 60-70 minutes I get a timeout error and the initial snapshot fails
to work.
The exact error is:
"Category:OS
Source:
Number: 12002
Message: The operation timed out"
Then it says that it failed to propagate the file it was working on.
The entire snapshot directory is less than 500 meg. I tried the
compressed snapshot first (which makes it about 130 meg) and that gets
the same timeout error.
Is there a way to increase that timeout somewhere? I don't quite
understand why it's timing out anyway, it should have plenty of time to
transfer that size of a file. Both machines are on high speed connections.
Thanks,
Jeff
right click on your pull subscription agent. Select agent properties, then
select run steps, and give focus to run agent. click on the commands button
and press the end key. Then press space, and type -QueryTimeout 600.
Then restart your pull subscription agent.
You may want to right click on replicaiton monitor and select refresh rate
and settings and set your inactivity threshold to something high.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Jeff Hedlund" <jeff.hedlund_NOSPAM_@._NOSPAM_elsym.com> wrote in message
news:oIA3d.1761$DY.272@.chiapp18.algx.net...
> Hello,
> I am trying to replicate over FTP (merge replication) and I'm having
> trouble with subscribing.
> After 60-70 minutes I get a timeout error and the initial snapshot fails
> to work.
> The exact error is:
> "Category:OS
> Source:
> Number: 12002
> Message: The operation timed out"
> Then it says that it failed to propagate the file it was working on.
> The entire snapshot directory is less than 500 meg. I tried the
> compressed snapshot first (which makes it about 130 meg) and that gets
> the same timeout error.
> Is there a way to increase that timeout somewhere? I don't quite
> understand why it's timing out anyway, it should have plenty of time to
> transfer that size of a file. Both machines are on high speed
connections.
> Thanks,
> Jeff
|||Hilary Cotter wrote:
> right click on your pull subscription agent. Select agent properties, then
> select run steps, and give focus to run agent. click on the commands button
> and press the end key. Then press space, and type -QueryTimeout 600.
Ok, I had previously tried this with no success but ...

> Then restart your pull subscription agent.
> You may want to right click on replicaiton monitor and select refresh rate
> and settings and set your inactivity threshold to something high.
This appeared to do the trick. I noticed that after 10 minutes (the
default timeout) is when my transfer would stop taking place.
I successfully transferred a few large bcp files (133 meg and 220 meg),
but ran into another problem when it applied one of those files before
continuing to download. So by the time it finished applying one of those
files, it tried to download the next file and the connection was no
longer active.
So I decided to go back to the compressed snapshot and try again since
it looked like my timeout issue disappeared with the inactivity
threshold setting.
Now I'm not getting a timeout, but for some reason the compressed
snapshot (cab) fails to complete it's download (it's about 120 meg).
First time I got to ~58 meg. Second time it got to ~9 meg. Third time
it got to ~105 meg.
I don't see any problem with the network between the two machines
(though they are separated by the Atlantic Ocean), and only moments
before it had no problem downloading the two large bcp files.
Is there some other setting I can look for? Is there something else it
does while downloading a compressed snapshot?
Thanks,
Jeff
|||Jeff Hedlund wrote:
> Now I'm not getting a timeout, but for some reason the compressed
> snapshot (cab) fails to complete it's download (it's about 120 meg).
> First time I got to ~58 meg. Second time it got to ~9 meg. Third time
> it got to ~105 meg.
> I don't see any problem with the network between the two machines
> (though they are separated by the Atlantic Ocean), and only moments
> before it had no problem downloading the two large bcp files.
Looking at the FTP server logs, I see errors about 120 second inactivity
timeouts. So maybe I am having some network congestion/problems.
Jeff
|||I would try to zip up the snapshot files and then have the subscriber pull
them through ftp. Once they arrive at the subscriber, expand them, create a
pull subscription and then when you get to the specify snapshot location
select specify directory and browse to the location of the snapshot files.
The path will have to be of the form repldata\unc\pubname_subname\date
(IIRC).
"Jeff Hedlund" <jeff.hedlund_NOSPAM_@._NOSPAM_elsym.com> wrote in message
news:hGG3d.1861$DY.760@.chiapp18.algx.net...
> Jeff Hedlund wrote:
> Looking at the FTP server logs, I see errors about 120 second inactivity
> timeouts. So maybe I am having some network congestion/problems.
> Jeff
|||I can use this procedure to manually apply the snapshot But using PUSH
subscriptions? what will be the procedure?
Thanks.
"Hilary Cotter" wrote:

> I would try to zip up the snapshot files and then have the subscriber pull
> them through ftp. Once they arrive at the subscriber, expand them, create a
> pull subscription and then when you get to the specify snapshot location
> select specify directory and browse to the location of the snapshot files.
> The path will have to be of the form repldata\unc\pubname_subname\date
> (IIRC).
>
> "Jeff Hedlund" <jeff.hedlund_NOSPAM_@._NOSPAM_elsym.com> wrote in message
> news:hGG3d.1861$DY.760@.chiapp18.algx.net...
>
>
|||the original poster was using FTP. If you are doing a push you can still
compress your snapshot. If your snapshot is very large you should think
about doing no sync subscriptions, and manually get the data over to the
subscriber.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Adrian320" <Adrian320@.discussions.microsoft.com> wrote in message
news:641144BB-058C-4202-B790-ABCE3BC2D18B@.microsoft.com...[vbcol=seagreen]
> I can use this procedure to manually apply the snapshot But using PUSH
> subscriptions? what will be the procedure?
> Thanks.
> "Hilary Cotter" wrote:
pull[vbcol=seagreen]
create a[vbcol=seagreen]
files.[vbcol=seagreen]
time[vbcol=seagreen]
(though[vbcol=seagreen]
had[vbcol=seagreen]
inactivity[vbcol=seagreen]
|||Ok, so when you do it with FTP you can specify the location, ok.
One more question I have the .bcp files on the subscriber computer. But I
don't know how to run a script to restore that table ('article') to the
subscriber database to then enable the nosync subscription.
Is there a link that you could point me to learn about this? Or can I use
the Restore Database command?
Thanks.
"Hilary Cotter" wrote:

> the original poster was using FTP. If you are doing a push you can still
> compress your snapshot. If your snapshot is very large you should think
> about doing no sync subscriptions, and manually get the data over to the
> subscriber.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "Adrian320" <Adrian320@.discussions.microsoft.com> wrote in message
> news:641144BB-058C-4202-B790-ABCE3BC2D18B@.microsoft.com...
> pull
> create a
> files.
> time
> (though
> had
> inactivity
>
>