|
Home > Archive > MS SQL Server OLAP > September 2005 > MSAS 2000 Read Time (64 bit) ; DB: DB2
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
MSAS 2000 Read Time (64 bit) ; DB: DB2
|
|
| Prasad 2005-09-16, 8:24 pm |
| Hi Dave,
I am running 3 cube partitions in parallel using Parallel Partition Utility.
The Database is DB2 UDB Version 8.2. The MSAS Read time is a concern at this
point. I am not doing any aggregation on the cubes. When I see the generated
logs, MSAS is reading 10,000 rows per second which is 60K per minute. Taking
all 3 partitions into consideration it is reading about 180K rows per minute.
When we execute the same 3 queries in parallel on the DB2 database and spit
out the results to a text file, every thing is done in a matter of 5 to 6
minutes. I am trying to find out the bottle neck and speed up the Read time.
I mentioned our hardware and Software settings along with the Analysis
Services settings. Please let me know.
Software:
• 64 bit Microsoft Analysis Services 2000 Enterprise Edition
• 64 bit Microsoft Windows Server 2003 Enterprise Edition
Hardware:
• Intel Itanium processor Family
• 1.60 GHz, 8 Way
• 60 GB RAM
• Gigabit Ethernet
• SCSI Disk Device
MSAS Settings:
Performance settings:
• Maximum number of threads: 16
• Large Level defined as: 10,000
Memory settings:
• Minimum allocated memory: 16384 MB
• Memory conservation threshold: 32768 MB
Processing settings:
• Read-ahead buffer size: 32 MB
• Process buffer size: 256 MB
Thanks,
Prasad.
| |
| Darren Gosbell 2005-09-17, 3:24 am |
| Could it be the provider you are using to connect to DB2? If you are=20
using the default ODBC driver from MS, it is not all that fast. I have=20
seen dramatic increases in performance against DB2 from switching to=20
using a native OLEDB provider.
Have you tried using the text files you dumped out to as source for AS.=20
You might not want to do this as a long term solution, but it would=20
remove the DB2 provider from the equation temporarily so that you could=20
see if it is the issue.
The amount of aggregations will not affect the reading speed as they are=20
done in a separate step after the data has been read.
--=20
Regards
Darren Gosbell [MCSD]
< dgosbell_at_yahoo_do
t_com>
Blog: http://www.geekswithblogs.net/darrengosbell
In article <25DD0DB2-3DCA-426A-BF0C- 954CFF4A8D31@microso
ft.com>,=20
Prasad@discussions.microsoft.com says...
> Hi Dave,
>=20
> I am running 3 cube partitions in parallel using Parallel Partition Utili=
ty.=20
> The Database is DB2 UDB Version 8.2. The MSAS Read time is a concern at t=
his=20
> point. I am not doing any aggregation on the cubes. When I see the genera=
ted=20
> logs, MSAS is reading 10,000 rows per second which is 60K per minute. Tak=
ing=20
> all 3 partitions into consideration it is reading about 180K rows per min=
ute.
>=20
> When we execute the same 3 queries in parallel on the DB2 database and sp=
it=20
> out the results to a text file, every thing is done in a matter of 5 to 6=
=20
> minutes. I am trying to find out the bottle neck and speed up the Read ti=
me.=20
> I mentioned our hardware and Software settings along with the Analysis=20
> Services settings. Please let me know.
>=20
> Software:
> =E2=A4=A2=0964 bit Microsoft Analysis Services 2000 Enterprise Edition
> =E2=A4=A2=0964 bit Microsoft Windows Server 2003 Enterprise Edition
>=20
> Hardware:=20
> =E2=A4=A2=09Intel Itanium processor Family
> =E2=A4=A2=091.60 GHz, 8 Way
> =E2=A4=A2=0960 GB RAM
> =E2=A4=A2=09Gigabit Ethernet
> =E2=A4=A2=09SCSI Disk Device
>=20
> MSAS Settings:
>=20
>=20
> Performance settings:
> =E2=A4=A2=09Maximum number of threads: 16
> =E2=A4=A2=09Large Level defined as: 10,000
>=20
> Memory settings:
> =E2=A4=A2=09Minimum allocated memory: 16384 MB
> =E2=A4=A2=09Memory conservation threshold: 32768 MB
>=20
> Processing settings:
> =E2=A4=A2=09Read-ahead buffer size: 32 MB
> =E2=A4=A2=09Process buffer size: 256 MB
>=20
> Thanks,
>=20
> Prasad.
>=20
>=20
>=20
| |
|
| Yes, try different drivers and connection configuration.
in the past I have seen some options like row batch size in the DB2 drivers.
not all drivers support the fast load retrieval interface.
"Darren Gosbell" < dgosbell_at_yahoo_do
t_com> wrote in message
news:MPG. 1d966850c2f544ae9896
a3@news.microsoft.com...
Could it be the provider you are using to connect to DB2? If you are
using the default ODBC driver from MS, it is not all that fast. I have
seen dramatic increases in performance against DB2 from switching to
using a native OLEDB provider.
Have you tried using the text files you dumped out to as source for AS.
You might not want to do this as a long term solution, but it would
remove the DB2 provider from the equation temporarily so that you could
see if it is the issue.
The amount of aggregations will not affect the reading speed as they are
done in a separate step after the data has been read.
--
Regards
Darren Gosbell [MCSD]
< dgosbell_at_yahoo_do
t_com>
Blog: http://www.geekswithblogs.net/darrengosbell
In article <25DD0DB2-3DCA-426A-BF0C- 954CFF4A8D31@microso
ft.com>,
Prasad@discussions.microsoft.com says...
> Hi Dave,
>
> I am running 3 cube partitions in parallel using Parallel Partition
> Utility.
> The Database is DB2 UDB Version 8.2. The MSAS Read time is a concern at
> this
> point. I am not doing any aggregation on the cubes. When I see the
> generated
> logs, MSAS is reading 10,000 rows per second which is 60K per minute.
> Taking
> all 3 partitions into consideration it is reading about 180K rows per
> minute.
>
> When we execute the same 3 queries in parallel on the DB2 database and
> spit
> out the results to a text file, every thing is done in a matter of 5 to 6
> minutes. I am trying to find out the bottle neck and speed up the Read
> time.
> I mentioned our hardware and Software settings along with the Analysis
> Services settings. Please let me know.
>
> Software:
> ⤢ 64 bit Microsoft Analysis Services 2000 Enterprise Edition
> ⤢ 64 bit Microsoft Windows Server 2003 Enterprise Edition
>
> Hardware:
> ⤢ Intel Itanium processor Family
> ⤢ 1.60 GHz, 8 Way
> ⤢ 60 GB RAM
> ⤢ Gigabit Ethernet
> ⤢ SCSI Disk Device
>
> MSAS Settings:
>
>
> Performance settings:
> ⤢ Maximum number of threads: 16
> ⤢ Large Level defined as: 10,000
>
> Memory settings:
> ⤢ Minimum allocated memory: 16384 MB
> ⤢ Memory conservation threshold: 32768 MB
>
> Processing settings:
> ⤢ Read-ahead buffer size: 32 MB
> ⤢ Process buffer size: 256 MB
>
> Thanks,
>
> Prasad.
>
>
>
| |
| Dave Wickert [MSFT] 2005-09-19, 8:24 pm |
| Now that is a *nice* machine.
Since this is 64-bit, it means that you are using the native DB2 OLEDB
provider. I would check with them to ensure that you have the latest version
of the software.
To debug this, I think the first thing that I would do is to get the SQL
statements that we are issuing (from the system-wide processing log file,
which if you haven't already done, stop reading this email and do
immediately, then read the rest . . .
(stopping while you turn on the system-wide processing log file)
OK. Now go to that file and look at the SQL statement. Pull it out and
execute it by-hand using the normal DB2 utilities. Sorry, I'm not up on the
latest and greatest from IBM, but I am sure you know it. Execute several
statement in parallel yourself interactively. What throughput are you
getting?
A known problem is if you are table partitioning on the IBM DB2-side and
partitioning on the AS-side. The way that AS passes the data slice to DB2 is
to use ODBC cannonical format for parameters. The SQL looks something like:
SELECT <col list> FROM <table> WHERE <slice field> = ?
And then the ? is replaced with the data slice for the partition. The WHERE
clause is automatically added by AS when you have multi-partitioning. The
issue on the DB2-side is that the DB2 optimizer cannot use table
partitioning across a parameter passed like that. It must be passed as a
constant. Thus on the AS-side, you set an advanced filter for the partition
to be <slice-field> = <value> and then set a registry setting to disable AS
from adding the default WHERE clause for partitioning. The registry setting
is documented here:
http://msdn.microsoft.com/library/d...i
ngs.asp
See MAP_NOT_USE_SLICE_FO
R_QUERY
Hope this helps.
--
Dave Wickert [MSFT]
dwickert@online.microsoft.com
Program Manager
BI Systems Team
SQL BI Product Unit (Analysis Services)
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Prasad" <Prasad@discussions.microsoft.com> wrote in message
news:25DD0DB2-3DCA-426A-BF0C- 954CFF4A8D31@microso
ft.com...
> Hi Dave,
>
> I am running 3 cube partitions in parallel using Parallel Partition
> Utility.
> The Database is DB2 UDB Version 8.2. The MSAS Read time is a concern at
> this
> point. I am not doing any aggregation on the cubes. When I see the
> generated
> logs, MSAS is reading 10,000 rows per second which is 60K per minute.
> Taking
> all 3 partitions into consideration it is reading about 180K rows per
> minute.
>
> When we execute the same 3 queries in parallel on the DB2 database and
> spit
> out the results to a text file, every thing is done in a matter of 5 to 6
> minutes. I am trying to find out the bottle neck and speed up the Read
> time.
> I mentioned our hardware and Software settings along with the Analysis
> Services settings. Please let me know.
>
> Software:
> . 64 bit Microsoft Analysis Services 2000 Enterprise Edition
> . 64 bit Microsoft Windows Server 2003 Enterprise Edition
>
> Hardware:
> . Intel Itanium processor Family
> . 1.60 GHz, 8 Way
> . 60 GB RAM
> . Gigabit Ethernet
> . SCSI Disk Device
>
> MSAS Settings:
>
>
> Performance settings:
> . Maximum number of threads: 16
> . Large Level defined as: 10,000
>
> Memory settings:
> . Minimum allocated memory: 16384 MB
> . Memory conservation threshold: 32768 MB
>
> Processing settings:
> . Read-ahead buffer size: 32 MB
> . Process buffer size: 256 MB
>
> Thanks,
>
> Prasad.
>
>
| |
| Prasad 2005-09-20, 11:23 am |
| Hi Dave,
Thanks for the reply. Yes, the management approved the hardware that we
requested. Now, we are trying to prove that we can port all our existing
cubes and also build bigger scalable cubes with MSAS 2000 and 64 bit server.
I already have the advanced filter set to each of the partition slices. Also
the DB2 registry setting is done for MAP_NOT_USE_SLICE_FO
R_QUERY as per the
article.
I want to correct some of my MSAS Read time stats that I mentioned in my
note earlier. MSAS is reading about 50 k rows per minute. I am running 3 in
parallel with the utility. Therefore I am assuming that it is reading 50k *3
close to 150 K rows per minute from DB2.
Each partition has roughly 8 million rows. The MSAS Read time for 3
partitions in parallel is 15 to 16 minutes.
I took the exact same 3 queries, executed them in parallel on DB2 directly
and also piped the results to a text file. It took little less than 5 minutes
to complete all 3 of them. Pretty amazing!
We need to get the MSAS read time around the same ball park as DB2 to build
all the necessary cubes in our load week end. (Essentially 48 hours) I am
not planning to have any aggregations at all in any of our cubes because of
the number of dimensions.
We are using IBM OLEDB Provider for DB2 shipped with MSAS. I am not sure how
to check the version of the driver. Also, I did not see any parameters like
batch size etc that I can change with the driver. Please let me know if there
are better drivers that I can use to speed up the MSAS read times from the
DB2 database.
Also, I am not sure how are your other clients of this nature are handling
the read times with database in DB2?
Thanks in advance for your help. Please let me know if you would like me to
test any other things.
Prasad.
"Dave Wickert [MSFT]" wrote:
> Now that is a *nice* machine.
>
> Since this is 64-bit, it means that you are using the native DB2 OLEDB
> provider. I would check with them to ensure that you have the latest version
> of the software.
>
> To debug this, I think the first thing that I would do is to get the SQL
> statements that we are issuing (from the system-wide processing log file,
> which if you haven't already done, stop reading this email and do
> immediately, then read the rest . . .
>
> (stopping while you turn on the system-wide processing log file)
>
> OK. Now go to that file and look at the SQL statement. Pull it out and
> execute it by-hand using the normal DB2 utilities. Sorry, I'm not up on the
> latest and greatest from IBM, but I am sure you know it. Execute several
> statement in parallel yourself interactively. What throughput are you
> getting?
>
> A known problem is if you are table partitioning on the IBM DB2-side and
> partitioning on the AS-side. The way that AS passes the data slice to DB2 is
> to use ODBC cannonical format for parameters. The SQL looks something like:
>
> SELECT <col list> FROM <table> WHERE <slice field> = ?
>
> And then the ? is replaced with the data slice for the partition. The WHERE
> clause is automatically added by AS when you have multi-partitioning. The
> issue on the DB2-side is that the DB2 optimizer cannot use table
> partitioning across a parameter passed like that. It must be passed as a
> constant. Thus on the AS-side, you set an advanced filter for the partition
> to be <slice-field> = <value> and then set a registry setting to disable AS
> from adding the default WHERE clause for partitioning. The registry setting
> is documented here:
>
> http://msdn.microsoft.com/library/d...i
ngs.asp
>
> See MAP_NOT_USE_SLICE_FO
R_QUERY
>
> Hope this helps.
> --
> Dave Wickert [MSFT]
> dwickert@online.microsoft.com
> Program Manager
> BI Systems Team
> SQL BI Product Unit (Analysis Services)
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
>
> "Prasad" <Prasad@discussions.microsoft.com> wrote in message
> news:25DD0DB2-3DCA-426A-BF0C- 954CFF4A8D31@microso
ft.com...
>
>
>
| |
| Dave Wickert [MSFT] 2005-09-21, 7:23 am |
| zero aggs?
--
Dave Wickert [MSFT]
dwickert@online.microsoft.com
Program Manager
BI Systems Team
SQL BI Product Unit (Analysis Services)
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Prasad" <Prasad@discussions.microsoft.com> wrote in message
news:CEC180BE-A645-455D-9459- 1C9F740FD98D@microso
ft.com...[color=darkred]
> Hi Dave,
>
> Thanks for the reply. Yes, the management approved the hardware that we
> requested. Now, we are trying to prove that we can port all our existing
> cubes and also build bigger scalable cubes with MSAS 2000 and 64 bit
> server.
>
> I already have the advanced filter set to each of the partition slices.
> Also
> the DB2 registry setting is done for MAP_NOT_USE_SLICE_FO
R_QUERY as per
> the
> article.
>
> I want to correct some of my MSAS Read time stats that I mentioned in my
> note earlier. MSAS is reading about 50 k rows per minute. I am running 3
> in
> parallel with the utility. Therefore I am assuming that it is reading 50k
> *3
> close to 150 K rows per minute from DB2.
>
> Each partition has roughly 8 million rows. The MSAS Read time for 3
> partitions in parallel is 15 to 16 minutes.
>
> I took the exact same 3 queries, executed them in parallel on DB2 directly
> and also piped the results to a text file. It took little less than 5
> minutes
> to complete all 3 of them. Pretty amazing!
>
> We need to get the MSAS read time around the same ball park as DB2 to
> build
> all the necessary cubes in our load week end. (Essentially 48 hours) I am
> not planning to have any aggregations at all in any of our cubes because
> of
> the number of dimensions.
>
> We are using IBM OLEDB Provider for DB2 shipped with MSAS. I am not sure
> how
> to check the version of the driver. Also, I did not see any parameters
> like
> batch size etc that I can change with the driver. Please let me know if
> there
> are better drivers that I can use to speed up the MSAS read times from the
> DB2 database.
>
> Also, I am not sure how are your other clients of this nature are handling
> the read times with database in DB2?
>
> Thanks in advance for your help. Please let me know if you would like me
> to
> test any other things.
>
> Prasad.
>
> "Dave Wickert [MSFT]" wrote:
>
| |
| Prasad 2005-09-21, 9:24 am |
| Sorry. I mean to say very less percentage of aggregation : 5% (MSAS estimated
around 20 aggs).
Please note that i had most of the dimensions to "Top Level" only except 3
otr 4.
Thanks,
"Dave Wickert [MSFT]" wrote:
> zero aggs?
>
> --
> Dave Wickert [MSFT]
> dwickert@online.microsoft.com
> Program Manager
> BI Systems Team
> SQL BI Product Unit (Analysis Services)
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
>
> "Prasad" <Prasad@discussions.microsoft.com> wrote in message
> news:CEC180BE-A645-455D-9459- 1C9F740FD98D@microso
ft.com...
>
>
>
| |
| Prasad 2005-09-23, 1:23 pm |
| Hi Dave,
Can you please comment on the performance and give recommendations?
Regarding aggs in my prior note, Sorry. I mean to say very less percentage
of aggregation : 5% (MSAS estimated around 20 aggs).
Please note that i had most of the dimensions to "Top Level" only except 3
or 4.
Thanks,
"Dave Wickert [MSFT]" wrote:
> zero aggs?
>
> --
> Dave Wickert [MSFT]
> dwickert@online.microsoft.com
> Program Manager
> BI Systems Team
> SQL BI Product Unit (Analysis Services)
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
>
> "Prasad" <Prasad@discussions.microsoft.com> wrote in message
> news:CEC180BE-A645-455D-9459- 1C9F740FD98D@microso
ft.com...
>
>
>
|
|
|
|
|