Tuesday, July 24, 2007

Mulitple DB Writers use'm or not?

There was this comment on some blog that suggested that given that now we're in 2007 and most operating systems that matter support async i/o. There shouldn't be a need for multiple db writers.

Though I agree in principal with that. However, I have observed in my testing using an OLTP workload on a Sun V40z RHEL4 64bit Performance on Oracle that it did help performance when using multiple dbwriters as many as cores on the system. I thought I'd pass this info on for any one that may be wondering.

3 comments:

aditya said...

We have a Sun T2000 with 8 cores in front of a Netapp 3020 and running Oracle 10g over NFS. The T2000 has 8 cores but can do 4 parallel threads in hw per core, so Oracle shows it has 32 "CPUs" -- and Oracle seems to pick 4 db writers per default, probably 1 per 8 CPUs as this performance guide seems to suggest. In this case do you think we would benefit from additional db writers? if so, how many makes sense to try?

- Sanjay Gulabani said...

aditya

thanks for your comment. Can I assume you're using Solaris?

- Sanjay Gulabani said...

Aditya

Given the number of cores etc you specify, I am assuming this is Solaris operating system. The performance data point on multiple dbwriters I have is for Linux. However, I have noticed that if you use more dbwriter than necessary, there isn't performance penalty of any kind. The extra processes just sit there and do nothing. If you want to tune it is best done with some experimentation. You can also check with some utility such as top etc if any of your db writers are idling during your peak workload. If they are, you likely won't benefit from adding more. Hope that helps.