Re: external table parallel issue
- From: LS Cheng <exriscer@xxxxxxxxx>
- To: Ghassan Salem <salem.ghassan@xxxxxxxxx>
- Date: Mon, 17 May 2010 10:33:17 +0200
Hi
That is eactly the issue and I understand what is going, a bit tricky to
deal with may be instead of union all in the database I should try merge the
file into a single file.
However I thought during the parsing phase the optimizer can dynamic
sampling (I have optimizer_dynamic_sampling set to 4) and can know which
external tables has more data and which has cero rows and then distribute PX
slaves based on that but doesnt seem the case.
Thanks
On Mon, May 17, 2010 at 9:01 AM, Ghassan Salem <salem.ghassan@xxxxxxxxx>wrote:
> What may be happening here is that the system allocates each file to a
> different PX slave, so the PX granule is the file, and this does not get
> reconsidered once the execution starts. So the issue is that some of your PX
> slaves has very small files to read, while others has much bigger ones,
> that's why you get different load on each slave.
> Can do a test where the DPO is much higher than 64 (e.g. 128) and see what
> it gives?
>
> rgds
>
>
> On Mon, May 17, 2010 at 7:35 AM, LS Cheng <exriscer@xxxxxxxxx> wrote:
>
>> Hi
>>
>> The source comes from 20 (but many files not just 20) different places and
>> each leave their file in a different directory and several external tables
>> were created for each source file.
>>
>> The issue is that when the query is run against many external tables glued
>> together with union all some of PX slaves does no job at all, for example if
>> 16 slaves are spawned probably only a couple of slaves are performing 95% of
>> tasks and the others just idles, this can be observed from 10046 traces for
>> all PX processes. I am thinking the cause might be that not all external
>> tables has data, only 25% of all external tables and the rest are empty
>> files (but not always sometimes they do contain data) so the working slaves
>> get assigned the tables with data and the idles one got assigned tables with
>> empty files.
>>
>> This des not happen with single external table, all slaves works and
>> uniformly.
>>
>> This is reproduceble with select count(*)
>>
>> I have not tested with heap tables, I will see if I can carry mote tests
>> today.
>>
>>
>>
>> TIA
>>
>>
>>
>> On Mon, May 17, 2010 at 3:32 AM, Greg Rahn <greg@xxxxxxxxxxxxxxxxxx>wrote:
>>
>>> First off - why not just make a single external table that contains
>>> all the files of the other 64? E.g if you have 64 external tables
>>> with 1 file each, make a single, additional, external table with 64
>>> files. This would seem to eliminate the need for a 64 branch union
>>> (which seems like bad design to me).
>>> Second - does the issue (which I'm unclear on exactly what it is)
>>> reproduce if you use heap tables? E.g make N heap tables as select *
>>> from external_table and try your 64 branch union.
>>>
>>> If your concern is why some PX servers dont seem to be getting work
>>> when accessing the external table - does this reproduce with a simple
>>> select count(*) from the external table as well?
>>>
>>>
>>> On Sun, May 16, 2010 at 9:26 AM, LS Cheng <exriscer@xxxxxxxxx> wrote:
>>> > The size varies from a few MB to a couple hundreds of MB.
>>> >
>>> > With 1 external table the query is pretty fast, we have other process
>>> which
>>> > run against only a single external tables larger than the problematic
>>> one
>>> > and the timing is much better.
>>> >
>>> > The problem with this I am showing is that it is formed by many
>>> external
>>> > tables and to simply the query we created several union all views on
>>> top of
>>> > these external tables. I will test without views and see what happens.
>>>
>>> --
>>> Regards,
>>> Greg Rahn
>>> http://structureddata.org
>>>
>>
>>
>
Other related posts:
