I have a problem with performance of queries on federation and I am trying to find a bottleneck.
No data are stored on the federation server, which is managed by DB2 ESE LUW v9.5 with WebSphere Federation Server. Two databases are queried through the federation server. They are both stored in a DB2 instance on a separate server and managed by DB2 ESE LUW v9.5.
A query, which performs equi-join of data retrieved from both database, take 4 and half minutes. The amount of data transferred from one database is 2.5 million raws of bigint and int i.e. 30MB, and from another database is 11.5 million raws of bigint and int i.e. 131MB. The network speed is 100Mb/s on each site, which is utilized by maximum 15% during query execution. CPU of the federation is utilized by 70-80%. Join is performed by hash join and then count on the result.
Can anyone suggest where and how I should look for performance bottleneck? Can anyone recommend good articles on performance issues of IBM websphere federation server?
If necessary I can provide more information about the problem
This topic has been locked.
3 replies Latest Post - 2010-05-18T10:00:55Z by k_rus
Pinned topic bad query performance on federation server
Answered question This question has been answered.
Unanswered question This question has not been answered yet.
Updated on 2010-05-18T10:00:55Z at 2010-05-18T10:00:55Z by k_rus
RichieEscarez 270000WWFW2 PostsACCEPTED ANSWER
Re: bad query performance on federation server2010-05-12T18:20:49Z in response to k_rusHi,
Perhaps some of the articles link in the "Tuning" section of the IBM InfoSphere Federation Server roadmap might help:
IBM InfoSphere Federation Server
Re: bad query performance on federation server2010-05-13T12:56:22Z in response to RichieEscarezHi Richie,
Thank you for the link. I have already went through through some parts of this chapter.
In my case query optimizer made a good job and maximum operations are push down to the sources.
Parallelism cannot be utilized since there is only one partition and one CPU.
I was not able to enable asynchronous processing, and I don't know why.
Theoretically, the query execution can be speed up with current resources. For example, a strategy, in which all necessary data will be transferred to the federation with maximum network speed, stored in the main memory and then processed by join and aggregate operations, should be faster than current strategy. I have not found if it is possible to enable such strategy.
Re: bad query performance on federation server2010-05-18T10:00:55Z in response to k_rusI was experimenting with different queries on the federation. It looks like that the bottleneck is caused by significant wrapper overhead.
I made a number of queries to transfer data from a source database to a client computer through the federation. The CPU load on the federation was about 100%, while the network was utilized by 3-4% in one direction. Thus it seems that the relational wrappers (DB2 and Oracle) do a lot of processing, which significantly consumes CPU.
Does anyone know about wrappers' overhead? Is there a way to reduce the overhead? Can anyone provide a link to deep description of relational wrappers v.9.5?