Thread: Performance of jobs

samantha mahindrakar wrote:
> I am facing performance issues with running scheduled jobs. There are
> multiple jobs running on the database ...not necessarily acting on the
> same table. Either ways i checked if there was a locking issue, I did
> not find any conflicting issues. The program is run over night
> ......when i check the log files i saw that it runs slow when other
> loaders are running.Is there any way i can improve the performance?Can
> someone help me understand how to debug this bottle neck?

What is your hardware? OS? OS version? PostgreSQL version? Number of
disks, disk type, disk/RAID controller, RAID/LVM configuration, ...

--
Craig Ringer

samantha mahindrakar wrote:
> Windows 2003 Server SP2
> Intel Xeon 3.2 GHz
> 3.75 Gb RAM
> System Drive 33.8 Gb
> Data drive 956 Gb
> PostgreSQL 8.2.6
> PERC RAID 10 I believe SCSI disks

... all of which look fairly reasonable, though you didn't say how many
disks (which is *very* important for performance) or how fast they are.

The concurrent jobs will compete for disk bandwidth, CPU time, and
memory for cache. If a single job already loads your server quite
heavily then a second concurrent job will slow the original job down and
run slower its self. There's not much you can do about that except
schedule the jobs not to run concurrently.

You should probably start by using the system's performance monitoring
tools (whatever they are - I don't know much about that for Windows) to
determine how much RAM, CPU time, and disk I/O each job uses when run
alone. Time how long each job takes when run alone. Then run the jobs
concurrently, time them, and measure how much CPU time and (if possible)
disk I/O each job gets that way. See how much greater the total time
taken is when they're run concurrently and try to see if the jobs appear
to be CPU-limited, disk limited, etc.

If your controller has a battery backed cache, consider turning on write
caching and see if that helps. DO NOT turn write caching on without a
battery backed cache. If performance is important to you, you should
probably get a battery backed cache module anyway.

Also make sure that PostgreSQL has enough shared memory configured. It
might be struggling to fit the data it needs for the jobs in its shared
memory cache when you run them concurrently. See the manual for a more
detailed explanation.

You have not provided any useful information about exactly what you
think is wrong, what the problem jobs are, etc so it is not possible to
give more than very generic explanations. To be specific it'd probably
be necessary to know your data and queries. At minimum some measurements
of query times when run concurrently vs alone would be necessary.

--
Craig Ringer