Prev: Are these table features mutual exclusive ?
Next: Installed client V9.5 on Linux --> file access error
From: k_rus on 26 May 2010 02:14
Hi,

How much CPU intensive is WebSphere Federation Server 9.5?

We are using WebSphere Federation Server on top of DB2 v9.5 ESE LUW on
Windows Server 2003 to federate three databases: one is managed by
Oracle 10g, two others are managed by one DB2 Server. We noticed
problems with performance.
During the investigation I run simple query to transfer data from a
DB2 database through the federation server to a client desktop. The
transferred data are 2,5 million rows of an Integer indexed column
from one table, i.e., 10 MB. It takes 50 seconds to execute the
transferring query on the federation server with single CPU. The
bottleneck is the CPU on the federation server, which is loaded by
100%.
Later we added second CPU to the federation.The execution time of the
query was reduced down to 30 seconds.
Is it normal that an execution of a query, which transfers 10 MB of
data through federation, takes 30 seconds? Is there a way to improve
performance?

Best regards,
Ruslan
From: Serge Rielau on 26 May 2010 07:27
Not sure if thsi can be tracked via usenet.. have youconsidered opening
a PMR?
Some general thoughts: If the "remote" DB is local it could be you see
high CPU because of the bufferpool of both DB2 Fed and the source DB.
So that would be a good thing.

But can you get teh optimizer plan for the federated query?
Maybe it's just a bad plan?

Cheers
Serge


--
Serge Rielau
SQL Architect DB2 for LUW
IBM Toronto Lab

From: k_rus on 27 May 2010 05:44
Hi Serge,

Thank you for your reply.

> Not sure if thsi can be tracked via usenet.. have youconsidered opening
> a PMR?
We are trying to understand if such overhead is normal or not, first.

> Some general thoughts: If the "remote" DB is local it could be you see
> high CPU because of the bufferpool of both DB2 Fed and the source DB.
> So that would be a good thing.
DB2 server and federation server are running in separate virtual
servers. They are connected through TCP/IP. So I guess it should not
be a case.

> But can you get teh optimizer plan for the federated query?
> Maybe it's just a bad plan?
The query is very simple: select col from nickname
The selection is shipped to the source and result is returned back to
client. I attached explain plan to the end of the message.

Best regards,
Ruslan
--
Ruslan Fomkin, PhD
Research assistant: research and development in databases
Department of Medical Epidemiology and Biostatistics (MEB)
Karolinska Institutet
-----
DB2 Universal Database Version 9.5, 5622-044 (c) Copyright IBM Corp.
1991, 2007
Licensed Material - Program Property of IBM
IBM DB2 Universal Database SQL and XQUERY Explain Tool

******************** DYNAMIC ***************************************
==================== STATEMENT
==========================================
Isolation Level = Cursor Stability
Blocking = Block Unambiguous Cursors
Query Optimization Class = 5
Partition Parallel = No
Intra-Partition Parallel = No
SQL Path = "SYSIBM", "SYSFUN", "SYSPROC",
"SYSIBMADM",
"ADMINISTRATOR"
Statement:

select lopnr
from nbhw.dead_clean

Section Code Page = 1208
Estimated Cost = 234419,046875
Estimated Cardinality = 2582676,000000

( 2) Ship Federated Subquery #1
| #Columns = 1
( 1) Return Data to Application
| #Columns = 1

Federated Substatement #1:

( 2) Server: NBHW (DB2/UDB 9.5)
SQL Statement:
SELECT A0."LOPNR"
FROM "NBHW" ."DEAD_CLEAN" A0
FOR READ ONLY
Nicknames Referenced:
1: NBHW.DEAD_CLEAN ID = 65533,32804
Base = NBHW.DEAD_CLEAN
#Output Columns = 1

End of section

Optimizer Plan:

Rows
Operator
(ID)
Cost

2,58268e+006
RETURN
( 1)
234419
|
2,58268e+006
SHIP
( 2)
234419
|
2,58268e+006
Index:
<65533,32804,1>
From: Serge Rielau on 27 May 2010 08:25
I don't think it's normal or expected....

--
Serge Rielau
SQL Architect DB2 for LUW
IBM Toronto Lab

From: k_rus on 28 May 2010 09:11
I also don't think that it is acceptable. We are in touch with a
consultant from IBM US and I am not able to convince him that it is
not normal and should be investigated.

I made more experiments with sizes of the transferred data.
Queries, which selects more columns from the same table, perform the
same as with one column, i.e., 30 seconds for 2,5 million rows.
A query, which selects a column of the same type (integer), from a
bigger tables, performs worse. E.g., transferring of 7,8 million rows
takes 1 min 30 sec. I.e., linear dependency between execution time and
selected number of rows.

Best regards,
Ruslan
 |  Next  |  Last
Pages: 1 2
Prev: Are these table features mutual exclusive ?
Next: Installed client V9.5 on Linux --> file access error