Problem to create an index on a large table
Creating an index on a large table under IDS 7.31 on SLES 11 failed with "212: Cannot add index / 27: File too large" once the sort work file in PSORT_DBTEMP approached 2 GB. OS ulimits were unlimited and a dd test proved >2 GB files were fine, and simply listing several directories in PSORT_DBTEMP didn't help (only one big file was created). The fix: set PSORT_NPROCS=4 (with multiple PSORT_DBTEMP directories suggested), after which the server created four sort files that grew evenly and the index built successfully.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Hi all,
SLES 11 SP1
Linux 2.6.32.59-0.7-default #1 SMP 2012-07-13 15:50:56 +0200 x86_64
x86_64 x86_64 GNU/Linux
IBM Informix Dynamic Server Version 7.31.UD10X1
I want to create an index on a large table.
I put the sortfile on disk (not in the tmpdbs of the DB)
with the command: export PSORT_DBTEMP=/online
The 'create index' statement creates an file on disk:
srt1208968_001
After some time i get the error message:
212: Cannot add index.
27: File too large
The size of the file srt1208968_001 gets near or greater (I don't know
it exactly , because it's deleted when the error message appears) 2GB
when the error message appears.
I am aware of the 2 GB limit of IDS 7.
Why does IDS not create multiple files less than 2 GB?
What can/must i do to avoid the error message?
Thanks in advance for your help.
Heinz
--
WESTFLEISCH eG * Hauptsitz: Brockhoffstr. 11, 48143 Münster
Amtsgericht Münster: Gen.-Reg. 307
Aufsichtsratsvorsitzender: Josef Lehmenkühler
Vorstand: Dirk Niederstucke (Vorsitzender); Peter Piekenbrock; Gerhard
Meierzuherde; Dr. Helfried Giesen, Geschäftsführer (Sprecher); Carsten
Schruck, Geschäftsführer
Hinweise: Es können nur Mails bis 30 MB empfangen werden.
PowerPoint-Dateien müssen in eine ZIP-Datei gepackt werden.
--------------------------------------------------
check your OS file size limit (ulimit -a), should be set to 'unlimited' or to some very high value. Mark
Hallo Mark,
thanks for your quick response.
ulimit -a shows unlimited.
I believe the problem is, that IDS tries to write a file greater than 2
GB, but has intern a limit of 2GB.
OS permits files greater than 2 GB.
Heinz
>>> "MARK JALKIEWICZ" <mark.jalkiewicz@verizon.net> 08.01.2013 14:59
>>>
check your OS file size limit (ulimit -a), should be set to 'unlimited'
or to
some very high value.
Mark
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.
Hi all,
SLES 11 SP1
Linux 2.6.32.59-0.7-default #1 SMP 2012-07-13 15:50:56 +0200 x86_64
x86_64 x86_64 GNU/Linux
IBM Informix Dynamic Server Version 7.31.UD10X1
I want to create an index on a large table.
I put the sortfile on disk (not in the tmpdbs of the DB)
with the command: export PSORT_DBTEMP=/online
The 'create index' statement creates an file on disk:
srt1208968_001
After some time i get the error message:
212: Cannot add index.
27: File too large
The size of the file srt1208968_001 gets near or greater (I don't know
it exactly , because it's deleted when the error message appears) 2GB
when the error message appears.
I am aware of the 2 GB limit of IDS 7.
Why does IDS not create multiple files less than 2 GB?
What can/must i do to avoid the error message?
Thanks in advance for your help.
Heinz
--
WESTFLEISCH eG * Hauptsitz: Brockhoffstr. 11, 48143 Münster
Amtsgericht Münster: Gen.-Reg. 307
Aufsichtsratsvorsitzender: Josef Lehmenkühler
Vorstand: Dirk Niederstucke (Vorsitzender); Peter Piekenbrock; Gerhard
Meierzuherde; Dr. Helfried Giesen, Geschäftsführer (Sprecher); Carsten
Schruck, Geschäftsführer
Hinweise: Es können nur Mails bis 30 MB empfangen werden.
PowerPoint-Dateien müssen in eine ZIP-Datei gepackt werden.
--------------------------------------------------
this is odd because the error returned is the OS value for file to large (27). I would tend to believe that if Informix was limiting the file size an error more specific to Informix would be returned and not the OS value. Did you check ulimits for user "informix" and root ? Mark
I'm guessing that there either the filesystem doesn't support large files (see its mount options for the "nolargefiles" attribute) or there is a ulimit on user informix which is a little smaller than 2G. I know for a fact that 7.31 can properly create multiple sort-work files for larger sorts under PSORT_DBTEMP. Art Art S. Kagel Advanced DataTools (www.advancedatatools.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 my employer, Advanced DataTools, 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 Tue, Jan 8, 2013 at 8:38 AM, MARK JALKIEWICZ <mark.jalkiewicz@verizon.net > wrote: > this is odd because the error returned is the OS value for file to large > (27). > I would tend to believe that if Informix was limiting the file size an > error > more specific to Informix would be returned and not the OS value. > > Did you check ulimits for user "informix" and root ? > > Mark > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --bcaec517cbe04d63ec04d2c830e3
you can rule out the ulimit/2GB thing by running a simple dd command, something like dd if=/dev/zero of=sometest_file bs=2048 count=11000000 Cheers Paul > this is odd because the error returned is the OS value for file to large > (27). > I would tend to believe that if Informix was limiting the file size an > error > more specific to Informix would be returned and not the OS value. > > Did you check ulimits for user "informix" and root ? > > Mark > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > -- Paul Watson Tel: +1 913-674-0360 Mob: +1 913-387-7529 Web: www.oninit.com www.advancedatatools.com Failure is not as frightening as regret. If you want to improve, be content to be thought foolish and stupid. What this country needs are more unemployed politicians
Hi, the ulimit thing is no problem with user root and informix. dd if=/dev/zero of=sometest_file bs=2048 count=1100000 -rw-r--r-- 1 informix users 2252800000 Jan 8 16:06 sometest_file Heinz >>> "Paul Watson" <paul@oninit.com> 08.01.2013 16:00 >>> you can rule out the ulimit/2GB thing by running a simple dd command, something like dd if=/dev/zero of=sometest_file bs=2048 count=11000000 Cheers Paul > this is odd because the error returned is the OS value for file to large > (27). > I would tend to believe that if Informix was limiting the file size an > error > more specific to Informix would be returned and not the OS value. > > Did you check ulimits for user "informix" and root ? > > Mark > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > -- Paul Watson Tel: +1 913-674-0360 Mob: +1 913-387-7529 Web: www.oninit.com www.advancedatatools.com Failure is not as frightening as regret. If you want to improve, be content to be thought foolish and stupid. What this country needs are more unemployed politicians ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum. -- WESTFLEISCH eG * Hauptsitz: Brockhoffstr. 11, 48143 Münster Amtsgericht Münster: Gen.-Reg. 307 Aufsichtsratsvorsitzender: Josef Lehmenkühler Vorstand: Dirk Niederstucke (Vorsitzender); Peter Piekenbrock; Gerhard Meierzuherde; Dr. Helfried Giesen, Geschäftsführer (Sprecher); Carsten Schruck, Geschäftsführer Hinweise: Es können nur Mails bis 30 MB empfangen werden. PowerPoint-Dateien müssen in eine ZIP-Datei gepackt werden. --------------------------------------------------
Try using more than 1 directory in PSORT_DBTEMP, say 4 different directories instead. Regards, David. On 08 January 2013 at 14:59 Art Kagel <art.kagel@gmail.com> wrote: > I'm guessing that there either the filesystem doesn't support large files > (see its mount options for the "nolargefiles" attribute) or there is a > ulimit on user informix which is a little smaller than 2G. I know for a > fact that 7.31 can properly create multiple sort-work files for larger > sorts under PSORT_DBTEMP. > > Art > > Art S. Kagel > Advanced DataTools (www.advancedatatools.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 my employer, Advanced DataTools, 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 Tue, Jan 8, 2013 at 8:38 AM, MARK JALKIEWICZ <mark.jalkiewicz@verizon.net > > wrote: > > > this is odd because the error returned is the OS value for file to large > > (27). > > I would tend to believe that if Informix was limiting the file size an > > error > > more specific to Informix would be returned and not the OS value. > > > > Did you check ulimits for user "informix" and root ? > > > > Mark > > > > > > > > > ******************************************************************************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > --bcaec517cbe04d63ec04d2c830e3 > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. >
Hallo Art, thanks for your reply. No problem to create greater than 2 GB files as user informix with the command: cd /online dd if=/dev/zero of=sometest_file bs=2048 count=1100000 testids1:/online # l /online drwxr-xr-x 4 informix informix 4096 Jan 8 16:05 ./ drwxr-xr-x 30 root root 4096 Jan 8 11:38 ../ -rw-r--r-- 1 informix users 2252800000 Jan 8 16:06 sometest_file Heinz >>> "Art Kagel" <art.kagel@gmail.com> 08.01.2013 15:59 >>> I'm guessing that there either the filesystem doesn't support large files (see its mount options for the "nolargefiles" attribute) or there is a ulimit on user informix which is a little smaller than 2G. I know for a fact that 7.31 can properly create multiple sort-work files for larger sorts under PSORT_DBTEMP. Art Art S. Kagel Advanced DataTools (www.advancedatatools.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 my employer, Advanced DataTools, 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 Tue, Jan 8, 2013 at 8:38 AM, MARK JALKIEWICZ <mark.jalkiewicz@verizon.net > wrote: > this is odd because the error returned is the OS value for file to large > (27). > I would tend to believe that if Informix was limiting the file size an > error > more specific to Informix would be returned and not the OS value. > > Did you check ulimits for user "informix" and root ? > > Mark > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --bcaec517cbe04d63ec04d2c830e3 ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum. -- WESTFLEISCH eG * Hauptsitz: Brockhoffstr. 11, 48143 Münster Amtsgericht Münster: Gen.-Reg. 307 Aufsichtsratsvorsitzender: Josef Lehmenkühler Vorstand: Dirk Niederstucke (Vorsitzender); Peter Piekenbrock; Gerhard Meierzuherde; Dr. Helfried Giesen, Geschäftsführer (Sprecher); Carsten Schruck, Geschäftsführer Hinweise: Es können nur Mails bis 30 MB empfangen werden. PowerPoint-Dateien müssen in eine ZIP-Datei gepackt werden. --------------------------------------------------
Hallo David, thanks for your reply. I already have tried it with 3 different directories. No success. Only one great file in one of the directories was created and then the error message appeared. Heinz >>> "david@smooth1.co.uk" <david@smooth1.co.uk> 08.01.2013 22:34 >>> Try using more than 1 directory in PSORT_DBTEMP, say 4 different directories instead. Regards, David. On 08 January 2013 at 14:59 Art Kagel <art.kagel@gmail.com> wrote: > I'm guessing that there either the filesystem doesn't support large files > (see its mount options for the "nolargefiles" attribute) or there is a > ulimit on user informix which is a little smaller than 2G. I know for a > fact that 7.31 can properly create multiple sort-work files for larger > sorts under PSORT_DBTEMP. > > Art > > Art S. Kagel > Advanced DataTools (www.advancedatatools.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 my employer, Advanced DataTools, 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 Tue, Jan 8, 2013 at 8:38 AM, MARK JALKIEWICZ <mark.jalkiewicz@verizon.net > > wrote: > > > this is odd because the error returned is the OS value for file to large > > (27). > > I would tend to believe that if Informix was limiting the file size an > > error > > more specific to Informix would be returned and not the OS value. > > > > Did you check ulimits for user "informix" and root ? > > > > Mark > > > > > > > > > ******************************************************************************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > --bcaec517cbe04d63ec04d2c830e3 > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum. -- WESTFLEISCH eG * Hauptsitz: Brockhoffstr. 11, 48143 Münster Amtsgericht Münster: Gen.-Reg. 307 Aufsichtsratsvorsitzender: Josef Lehmenkühler Vorstand: Dirk Niederstucke (Vorsitzender); Peter Piekenbrock; Gerhard Meierzuherde; Dr. Helfried Giesen, Geschäftsführer (Sprecher); Carsten Schruck, Geschäftsführer Hinweise: Es können nur Mails bis 30 MB empfangen werden. PowerPoint-Dateien müssen in eine ZIP-Datei gepackt werden. --------------------------------------------------
Try using PSORT_NPROCS=4 and PSORT_DBTEMP=/dir1:/dir2:/dir3:/dir4
On 8 Jan 2013, at 11:32, Weitkamp Heinz <Heinz.Weitkamp@westfleisch.de> wrote:
> Hi all,
>
> SLES 11 SP1
> Linux 2.6.32.59-0.7-default #1 SMP 2012-07-13 15:50:56 +0200 x86_64
> x86_64 x86_64 GNU/Linux
>
> IBM Informix Dynamic Server Version 7.31.UD10X1
>
> I want to create an index on a large table.
> I put the sortfile on disk (not in the tmpdbs of the DB)
> with the command: export PSORT_DBTEMP=/online
>
> The 'create index' statement creates an file on disk:
> srt1208968_001
>
> After some time i get the error message:
>
> 212: Cannot add index.>
> 27: File too large
>
> The size of the file srt1208968_001 gets near or greater (I don't know
> it exactly , because it's deleted when the error message appears) 2GB
> when the error message appears.
>
> I am aware of the 2 GB limit of IDS 7.
> Why does IDS not create multiple files less than 2 GB?
>
> What can/must i do to avoid the error message?
>
> Thanks in advance for your help.
>
> Heinz
>
> --
> WESTFLEISCH eG * Hauptsitz: Brockhoffstr. 11, 48143 Münster
> Amtsgericht Münster: Gen.-Reg. 307
>
> Aufsichtsratsvorsitzender: Josef Lehmenkühler
> Vorstand: Dirk Niederstucke (Vorsitzender); Peter Piekenbrock; Gerhard
> Meierzuherde; Dr. Helfried Giesen, Geschäftsführer (Sprecher); Carsten
> Schruck, Geschäftsführer
>
> Hinweise: Es können nur Mails bis 30 MB empfangen werden.
> PowerPoint-Dateien müssen in eine ZIP-Datei gepackt werden.
> --------------------------------------------------
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
Hallo,
that's the goal. It works.
With PSORT_NPROCS=4 and PSORT_DBTEMP=/dir1
4 files srt....._001 to srt....._004 are created at once in /dir1 and
they grow evenly.
Thank you very much.
In my view: very strange behavior
Heinz
>>> "Spokey Wheeler Gmail" <spokey.wheeler@gmail.com> 09.01.2013 12:05
>>>
Try using PSORT_NPROCS=4 and PSORT_DBTEMP=/dir1:/dir2:/dir3:/dir4
On 8 Jan 2013, at 11:32, Weitkamp Heinz <Heinz.Weitkamp@westfleisch.de>
wrote:
> Hi all,
>
> SLES 11 SP1
> Linux 2.6.32.59-0.7-default #1 SMP 2012-07-13 15:50:56 +0200 x86_64
> x86_64 x86_64 GNU/Linux
>
> IBM Informix Dynamic Server Version 7.31.UD10X1
>
> I want to create an index on a large table.
> I put the sortfile on disk (not in the tmpdbs of the DB)
> with the command: export PSORT_DBTEMP=/online
>
> The 'create index' statement creates an file on disk:
> srt1208968_001
>
> After some time i get the error message:
>
> 212: Cannot add index.>
> 27: File too large
>
> The size of the file srt1208968_001 gets near or greater (I don't
know
> it exactly , because it's deleted when the error message appears) 2GB
> when the error message appears.
>
> I am aware of the 2 GB limit of IDS 7.
> Why does IDS not create multiple files less than 2 GB?
>
> What can/must i do to avoid the error message?
>
> Thanks in advance for your help.
>
> Heinz
>
> --
> WESTFLEISCH eG * Hauptsitz: Brockhoffstr. 11, 48143 Münster
> Amtsgericht Münster: Gen.-Reg. 307
>
> Aufsichtsratsvorsitzender: Josef Lehmenkühler
> Vorstand: Dirk Niederstucke (Vorsitzender); Peter Piekenbrock;
Gerhard
> Meierzuherde; Dr. Helfried Giesen, Geschäftsführer (Sprecher);
Carsten
> Schruck, Geschäftsführer
>
> Hinweise: Es können nur Mails bis 30 MB empfangen werden.
> PowerPoint-Dateien müssen in eine ZIP-Datei gepackt werden.
> --------------------------------------------------
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.
--
WESTFLEISCH eG * Hauptsitz: Brockhoffstr. 11, 48143 Münster
Amtsgericht Münster: Gen.-Reg. 307
Aufsichtsratsvorsitzender: Josef Lehmenkühler
Vorstand: Dirk Niederstucke (Vorsitzender); Peter Piekenbrock; Gerhard
Meierzuherde; Dr. Helfried Giesen, Geschäftsführer (Sprecher); Carsten
Schruck, Geschäftsführer
Hinweise: Es können nur Mails bis 30 MB empfangen werden.
PowerPoint-Dateien müssen in eine ZIP-Datei gepackt werden.
--------------------------------------------------