Fragmentation Fundamental Question
On IDS 11.5.FC8 (16 CPU VPs, MAX_PDQPRIORITY 100), a 30M-row table fragmented round-robin into 3 fragments was scanned with only one thread; SET EXPLAIN showed a Serial sequential scan over all fragments. Posters asked what PDQPRIORITY was actually used for the query (he said 100 in the SQL) and about the client/server environment settings. The poster suspected PDQ was being overridden (e.g. by sysdbopen) and planned to open a PMR; he later found a much larger table did get 3 scan threads, wondering if the table was simply too small to justify parallel scanning. No definitive resolution is recorded in the thread.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
This seems like a ridiculously simple situation but not seeing the expected behavior with respect to number of scan threads. I expect 3, but only getting 1. IDS 11.5.FC8; cpuvps 16; MAX_PDQPRIORITY 100; Fragments 3 30M row table fragmented by RR. Only getting 1 scan thread on a full SELECT of the table. Initially I thought the table was small enough that we didn't care about reading it in parallel. Not trying to get elimination (since it's RR), but just reading the table in parallel. Original table was 10M so I've loaded it a few times to see if size is the difference. Hoping it's a bug so I can get off 11.5, but doubt it. Haven't played with directives yet but I suppose that's next. Thoughts? Query Plan: QUERY: (OPTIMIZATION TIMESTAMP: 07-10-2015 13:50:43) ------ "sqexplain.out" 78 lines, 1944 characters 1) informix.this_table: SEQUENTIAL SCAN (Serial, fragments: ALL) Query statistics: ----------------- Table map : ---------------------------- Internal name Table name ---------------------------- t1 this_table type table rows_prod est_rows rows_scan time est_cost ------------------------------------------------------------------- scan t1 32711304 32711304 32711304 00:39.97 1236255
Mark, What is your pdqpriority set to for the query? You gave us the onconfig setting but not what you used to run the query. -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of MARK SCRANTON Sent: Friday, July 10, 2015 4:30 PM To: ids@iiug.org Subject: Fragmentation Fundamental Question [35430] This seems like a ridiculously simple situation but not seeing the expected behavior with respect to number of scan threads. I expect 3, but only getting 1. IDS 11.5.FC8; cpuvps 16; MAX_PDQPRIORITY 100; Fragments 3 30M row table fragmented by RR. Only getting 1 scan thread on a full SELECT of the table. Initially I thought the table was small enough that we didn't care about reading it in parallel. Not trying to get elimination (since it's RR), but just reading the table in parallel. Original table was 10M so I've loaded it a few times to see if size is the difference. Hoping it's a bug so I can get off 11.5, but doubt it. Haven't played with directives yet but I suppose that's next. Thoughts? Query Plan: QUERY: (OPTIMIZATION TIMESTAMP: 07-10-2015 13:50:43) ------ "sqexplain.out" 78 lines, 1944 characters 1) informix.this_table: SEQUENTIAL SCAN (Serial, fragments: ALL) Query statistics: ----------------- Table map : ---------------------------- Internal name Table name ---------------------------- t1 this_table type table rows_prod est_rows rows_scan time est_cost ------------------------------------------------------------------- scan t1 32711304 32711304 32711304 00:39.97 1236255 ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.
And the local env Cheers Paul > -----Original Message----- > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Link, > David A > Sent: Friday, July 10, 2015 4:45 PM > To: ids@iiug.org > Subject: RE: Fragmentation Fundamental Question [35432] > > Mark, > > What is your pdqpriority set to for the query? You gave us the onconfig > setting but not what you used to run the query. > > -----Original Message----- > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of > MARK > SCRANTON > Sent: Friday, July 10, 2015 4:30 PM > To: ids@iiug.org > Subject: Fragmentation Fundamental Question [35430] > > This seems like a ridiculously simple situation but not seeing the expected > behavior with respect to number of scan threads. I expect 3, but only getting > 1. > > IDS 11.5.FC8; cpuvps 16; MAX_PDQPRIORITY 100; Fragments 3 > > 30M row table fragmented by RR. > > Only getting 1 scan thread on a full SELECT of the table. Initially I thought > the table was small enough that we didn't care about reading it in parallel. > Not trying to get elimination (since it's RR), but just reading the table in > parallel. Original table was 10M so I've loaded it a few times to see if size > is the difference. Hoping it's a bug so I can get off 11.5, but doubt it. > Haven't played with directives yet but I suppose that's next. > > Thoughts? > > Query Plan: > > QUERY: (OPTIMIZATION TIMESTAMP: 07-10-2015 13:50:43) > ------ > "sqexplain.out" 78 lines, 1944 characters > 1) informix.this_table: SEQUENTIAL SCAN (Serial, fragments: ALL) > > Query statistics: > ----------------- > > Table map : > ---------------------------- > Internal name Table name > ---------------------------- > t1 this_table > > type table rows_prod est_rows rows_scan time est_cost > ------------------------------------------------------------------- > scan t1 32711304 32711304 32711304 00:39.97 1236255 > > > ********************************************************** > ********************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > > ********************************************************** > ********************* > Forum Note: Use "Reply" to post a response in the discussion forum. --- This email has been checked for viruses by Avast antivirus software. http://www.avast.com
David - It's set to 100 in the sql. Thanks - Mark
Server start-up environment: Variable Value [values-list] DBDATE mdy4/ DBDELIMITER | [|] [|] DBMONEY $. DBPRINT cat > informix.out [cat > informix.out] [lpr] DBTEMP /prod/dbtemp [/prod/dbtemp] [/tmp] INFORMIXDIR /home/informix/prod [/home/informix/prod] [/usr/informix] INFORMIXTERM terminfo [terminfo] [terminfo] LANG C LC_COLLATE C LC_CTYPE C LC_MONETARY C LC_NUMERIC C LC_TIME C LKNOTIFY yes LOCKDOWN no NODEFDAC no SERVER_LOCALE en_US.819 SHELL /usr/bin/ksh TERM vt100 [vt100] [dumb] TERMCAP /home/informix/prod/etc/termcap [/home/informix/prod/etc/termcap] [/etc/termcap]
It appears this might be a bug if not being unset by sysdbopen or similar. I'm going to open a PMR and hope for a reason to upgrade from 11.5. ;) Thanks - Mark
Did the same proof against a much larger table on AM getting 3 scan threads. Working on original test details. Even at 30M rows the table was very small. Wondering if that is why only 1 scan thread was allocated? Thanks - Mark
What does the set explain look like? From: "MARK SCRANTON" <mark@markscranton.com> To: ids@iiug.org Date: 07/13/2015 03:49 PM Subject: Re: RE: Fragmentation Fundamental Question [35441] Sent by: ids-bounces@iiug.org Did the same proof against a much larger table on AM getting 3 scan threads. Working on original test details. Even at 30M rows the table was very small. Wondering if that is why only 1 scan thread was allocated? Thanks - Mark ***************************************************************************= **** Forum Note: Use "Reply" to post a response in the discussion forum.