Database Software

Hi,

I am trying to use schedular jobs to replicate inserts to a table onto a remote database.

I have one job (generator) that runs every second and inserts certain number of records into the table. This is used to simulate the actual production load.

I also have multiple replicator jobs which replicates the inserts on to the remote database. The replicator jobs also runs every second. Each replicator job handles a subset of the new inserted rows. The selection algorithm ensures that each replicator job have a even distribution of work.

The reason for having multiple replicators is that the network between the local and the remote site is a high latency network and each replicator can handle only upto 20 inserts per second. The Idea is to have a large number of replicators running in parallel to handle replication for a high insert rate (1500/sec).

Initially this theory worked fine till a rate of 160 inserts per sec (8 replicators). When I tried to increase the number of replicators to handle higher load the execution of the generator job started getting delayed. The actual execution is less than a second but there is difference between scheduled start time and actual start time of the job.

After some investigation I found that the job_queue_processes is set to 10. After increasing this to 20 this problem was resolved.

The problem again resurfaced at 320 inserts/sec (16 replicators). I have increased the job_queue_processes to 40 but still the problem persists.

There are no other jobs running. The value of max_slave_processes for scheduler is not set.

My guess is generator is getting blocked on some shared resources when the number of replicator jobs becomes large. How do I find out what is the reason for this delay? And how can we resolve this?

I would appreciate if someone can help on this.

Regards,

Dinabandhu