Could not do a physical ..in a query group by
A user ran a GROUP BY over a 600M-row table (to find duplicates before building a unique index) on an unlogged 11.70 instance; after ~2 hours it failed with "Could not do a physical order...". Respondents asked for the underlying ISAM error code and noted this message usually means a lock conflict (locks still occur even in nologged databases), suggesting SET LOCK MODE TO WAIT n, dirty read isolation, disabling BATCHEDREAD_TABLE/INDEX, and checking sort/temp space since /tmp was used instead of temp dbspaces. The poster said space was adequate but never supplied the ISAM error, and no resolution is recorded.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Hi,
I have a query group by on a large table Over 600 Millions rows), the query
run over 2 hours and then it generates an error
Could not do a physical order to ....
My database is an Nolog informix 11.70.
on the onstat -u, I have 8 processes run by informix for this query
plz help
due to a lock conflict issue? if so, set isolation to dirty read ?
On Fri, Oct 16, 2015 at 11:42 AM, CHALLENGER212 ABDERRAFI <
abderrafi212@gmail.com> wrote:
> Hi,
> I have a query group by on a large table Over 600 Millions rows), the query
> run over 2 hours and then it generates an error
> Could not do a physical order to ....
> My database is an Nolog informix 11.70.
> on the onstat -u, I have 8 processes run by informix for this query
>
> plz help
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--001a113ab2822b884105223ab9dc
What temp dbspaces have you got configured ? How many of the 600 M rows are
you selecting ?
What is the exact error you are getting (error number) ?
On 16 October 2015 at 16:42, CHALLENGER212 ABDERRAFI <abderrafi212@gmail.com
> wrote:
> Hi,
> I have a query group by on a large table Over 600 Millions rows), the query
> run over 2 hours and then it generates an error
> Could not do a physical order to ....
> My database is an Nolog informix 11.70.
> on the onstat -u, I have 8 processes run by informix for this query
>
> plz help
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--001a11c2668281686005223abe02
I use tmp directory instead of tempdbs, I'm selecting all rows because I want to get duplicate value on 3 columns in order to delete then and ccreate a uniqu index on thats columns.
No, the set isolation does not work its already a Nolog database
Can you share the ISAM error? We return two error codes. The SQLCODE which is more generic and and a more detailed one called the ISAM error. (in fact the same ISAM error may cause different SQL codes). Usually that sqlcode you mentioned is associated with the lock error which is "strange" in a non-logged database, But "strange" doesn't mean impossible. Is the table being changed (INSERT/UPDATE/DELETE) concurrently? Are you using BATCHEDREAD_TABLE or BATCHEDREAD_INDEX? If yes, try to disable both and retry. Does this happen every time? Regards. On Fri, Oct 16, 2015 at 5:00 PM, CHALLENGER212 ABDERRAFI < abderrafi212@gmail.com> wrote: > No, the set isolation does not work its already a Nolog database > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently... --001a114214faad461d05223af450
OK, how big is your /tmp directory and how wide are the columns you are selecting ? On 16 October 2015 at 16:55, CHALLENGER212 ABDERRAFI <abderrafi212@gmail.com > wrote: > I use tmp directory instead of tempdbs, I'm selecting all rows because I > want > to get duplicate value on 3 columns in order to delete then and ccreate a > uniqu index on thats columns. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --f46d043bdbe6d5c3d105223b0847
You may need to set the lock mode to wait for a bit of time. SET LOCK MODE TO
WAIT ## where ## is the number of seconds. We really need to see the ISAM
error code.
Even if the database is not logged, locks are still required to access a given
row. And if you did not set LOCK MODE and the row is locked for even a
milli-second when you are trying to access it, then you will get this type of
error.
On Friday, October 16, 2015 10:42 AM, CHALLENGER212 ABDERRAFI
<abderrafi212@gmail.com> wrote:
Hi,
I have a query group by on a large table Over 600 Millions rows), the query
run over 2 hours and then it generates an error
Could not do a physical order to ....
My database is an Nolog informix 11.70.
on the onstat -u, I have 8 processes run by informix for this query
plz help
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.
You will need approx 0.5 Gb per character in the row, twice (if I remember the sort mechanism). So if you are returning 24 characters in the row you need 24 Gb of free space in the /tmp directory (probably) !! On 16 October 2015 at 17:10, Keith Simmons <smiley73@gmail.com> wrote: > OK, how big is your /tmp directory and how wide are the columns you are > selecting ? > > On 16 October 2015 at 16:55, CHALLENGER212 ABDERRAFI < > abderrafi212@gmail.com > > wrote: > > > I use tmp directory instead of tempdbs, I'm selecting all rows because I > > want > > to get duplicate value on 3 columns in order to delete then and ccreate a > > uniqu index on thats columns. > > > > > > > > > > ******************************************************************************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > --f46d043bdbe6d5c3d105223b0847 > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --047d7ba984b86bf80505223b2795
I have enough space in my tmp directory and for the colums its 3 simple columns, (date,decimal(12,0) and decimal(6,0)
Even in an unlogged database there will be some occassional locks. If it
was some other internal error, the ISAM error code will have identified
that. At any rate, your 8 processes should all be setting: SET LOCK MODE
TO WAIT 10; after connecting to the server and before issuing the query.
That way they will wait past transitory locks instead of erroring out.
Art
Art S. Kagel, President and Principal Consultant
ASK Database Management
www.askdbmgt.com
Blog: http://informix-myview.blogspot.com/
Disclaimer: Please keep in mind that my own opinions are my own opinions
and do not reflect on the IIUG, nor any other organization with which I am
associated either explicitly, implicitly, or by inference. Neither do
those opinions reflect those of other individuals affiliated with any
entity with which I am affiliated nor those of the entities themselves.
On Fri, Oct 16, 2015 at 11:42 AM, CHALLENGER212 ABDERRAFI <
abderrafi212@gmail.com> wrote:
> Hi,
> I have a query group by on a large table Over 600 Millions rows), the query
> run over 2 hours and then it generates an error
> Could not do a physical order to ....
> My database is an Nolog informix 11.70.
> on the onstat -u, I have 8 processes run by informix for this query
>
> plz help
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--001a1140421a03ed8305223b9010