NO MORE EXTENTS; System is so slow
Informix product and version: Informix Dynamic Server Version 7.30.uc2
Operating System: SCO_SV 3.2 5.0.5
=20
onstat -d
=20
Informix Dynamic Server Version 7.30.UC2 -- On-Line -- Up 3 days
00:25:42 -- 20480 Kbytes
=20Dbspaces
address number flags fchunk nchunks flags owner name
8244613c 1 1 1 1 N informix rootdbs
82446c4c 2 2001 2 1 N T informix tempdbs
82446d08 3 1 3 9 N informix drawback
3 active, 2047 maximum
=20
Chunks
address chk/dbs offset size free bpages flags pathname
824461f8 1 1 0 250000 163533 PO-
/dev/informix/online_root
824463b4 2 2 250000 737136 737033 PO-
/dev/informix/online_root
82446490 3 3 0 987136 3 PO-
/dev/informix/online_data1
8244656c 4 3 0 987136 5 PO-
/dev/informix/online_data2
82446648 5 3 0 987136 3 PO-
/dev/informix/online_data3
82446724 6 3 0 987136 5 PO-
/dev/informix/online_data4
82446800 7 3 0 987136 3 PO-
/dev/informix/online_data5
824468dc 8 3 0 987136 5 PO-
/dev/informix/online_data6
824469b8 9 3 0 987136 54933 PO-
/dev/informix/online_data7
82446a94 10 3 0 987136 987133 PO-
/dev/informix/online_data8
82446b70 11 3 0 987136 987133 PO-
/dev/informix/online_data9
11 active, 2047 maximum
=20
onstat -t
=20
Informix Dynamic Server Version 7.30.UC2 -- On-Line -- Up 3 days
00:26:00 -- 20480 Kbytes
=20Tblspaces
n address flgs ucnt tblnum physaddr npages nused npdata nrows
nextns resident
6 824471c0 0 1 100001 10000e 250 216 0 0 5
0 =20
28 82447500 0 1 200001 200004 100 96 0 0 2
0 =20
29 82447cd0 0 1 300001 300004 350 320 0 0 7
0 =20
30 82478828 0 12 300002 300005 32 28 12 123 4
0 =20
31 82479418 0 6 300003 300006 80 79 34 1151 9
0 =20
32 82854300 0 6 300004 300007 48 42 14 216 6
0 =20
33 82478fb8 0 6 300005 300008 24 19 5 147 3
0 =20
62 82916d90 0 1 300022 300025 464 463 380 11754 8
0 =20
63 829174f0 0 1 300023 300026 1416 1365 1019 31559 52
0 =20
114 82972014 0 1 3000d8 3007b3 70896 70870 60996 853943 66
0 =20
130 829161d4 0 1 3000f1 3007cc 2261816 2261816 1916813
19168062 222 0 =20
132 82860880 2 6 3000f3 3007ce 726888 726374 435647 4547969
144 0 =20
12 active, 149 total
=20
=20
Table Name exports
Owner lorna
Row Size 182
Number of Rows 19168062
Number of Columns 18
Date Created 01/15/2001
=20
=20
=20
tabname exports
owner lorna
partnum 3145969
tabid 311
rowsize 182
ncols 18
nindexes 4
nrows 19168363
created 01/15/2001
version 20382155
tabtype T
locklevel P
npused 1916841
fextsize 16
nextsize 16
flags 0
=20
=20
onstat -p
=20
Informix Dynamic Server Version 7.30.UC2 -- On-Line -- Up 3 days
00:26:05 -- 20480 Kbytes
=20Profile
dskreads pagreads bufreads Êched dskwrits pagwrits bufwrits Êched
3090872 7319469 35264666 91.24 200202 309443 3791820 94.72 =20
=20
isamtot open start read write rewrite delete commit
rollbk
13077583 76375 174777 10718038 51603 3682 697480 166
0
=20
gp_read gp_write gp_rewrt gp_del gp_alloc gp_free gp_curs=20
0 0 0 0 0 0 0 =20
=20
ovlock ovuserthread ovbuff usercpu syscpu numckpts flushes=20
0 0 2600 1784.33 561.88 109 1726 =20
=20
bufwaits lokwaits lockreqs deadlks dltouts ckpwaits compress seqscans
149900 0 7623501 0 0 21 1135 313 =20
=20
ixda-RA idx-RA da-RA RA-pgsused lchwaits
1353402 4340 6535 996777 5679 =20
=20
Message Log File: /var/informix/online.log
09:54:28 Checkpoint Completed: duration was 4 seconds.
09:59:35 Checkpoint Completed: duration was 4 seconds.
10:04:42 Checkpoint Completed: duration was 4 seconds.
10:10:22 Checkpoint Completed: duration was 37 seconds.
10:15:29 Checkpoint Completed: duration was 4 seconds.
10:20:36 Checkpoint Completed: duration was 4 seconds.
10:25:43 Checkpoint Completed: duration was 4 seconds.
10:30:50 Checkpoint Completed: duration was 4 seconds.
10:36:12 Checkpoint Completed: duration was 19 seconds.
10:41:17 Checkpoint Completed: duration was 2 seconds.
10:46:22 Checkpoint Completed: duration was 2 seconds.
10:51:27 Checkpoint Completed: duration was 2 seconds.
10:56:34 Checkpoint Completed: duration was 4 seconds.
11:01:38 Checkpoint Completed: duration was 0 seconds.
11:06:41 Checkpoint Completed: duration was 0 seconds.
11:11:44 Checkpoint Completed: duration was 0 seconds.
11:16:51 Checkpoint Completed: duration was 4 seconds.
11:21:54 Checkpoint Completed: duration was 0 seconds.
11:27:01 Checkpoint Completed: duration was 4 seconds.
11:32:08 Checkpoint Completed: duration was 4 seconds.
=20
=20
=20
Last week, I was running an application that would load data into my
table. The program stopped and got a SQL statement error # -271 ( Could
not insert new row into the table) and System error # -136 (ISAM error:
no more extents).
I deleted some old data into these big table and ran the application
again, and it went okay without any errors or problems.
=20
On my onstat -t, my table (exports table) have 19168062 rows and have
222 as shown on nextns. Is this why my system is so slow? I have been
putting the database OFFLINE and then back ONLINE ( at least twice last
week), and then the system will go fast again. (I have put the database
OFFLINE and then back ONLINE, last Thursday, July 17, and after doing
some table unloads and deletes from SQL, the system is going slow again.
Today, (Monday, July 21), my users are starting to complain that the
system is too slow and then the system crash. Does the table extents
problem could lead to the system crashing? How could I resolved my
table extents problem? Can I just drop the table and load back the
data?
=20
Thanks,=20
=20
Lorna=20
=20
=20
=20
=20
=20
=20
=20
=20
=20
What is the output of 'oncheck -pe'?
When you created the 'exports' table, did you specify an extent size?
Example:
create table exports
(
blah blah,
blah2 blah,.
.
.
) extent size XXXXXX next size YYYYYY;
XXXXXX is the starting extent size in kilobytes based on how much data you
expect your table to hold. YYYYYY is the size of the next extent on kilobytes
that the Informix engine will allocate for the table if the existing extents
are all full. If an extent size is not specified when the table is created, it
defaults to something like 16KB (8 pages). As the table grows, the engine will
allocate larger and larger extents, but it is still possible to run out of
extents for the table. I believe each table can only have a maximum of 253
extents in Informix IDS 7.30.
I think your best course of action would be to unload your table somehow to
save your data, then drop the table and recreate it with extent sizes and then
reload your data. You may have to unload it in sections because the total size
of the output unload file would more than likely be larger than 2 Gig, which
is the largest file size SCO's HTFS filesystem can support, I think. Since
your table is so large, I'd pick 'extent size 300000 next size 300000' since
those values are somewhat even multiples of your chunk sizes.
As for the slowness, are you periodically performing an 'update statistics'
command? The simplest form of this would be (in a shell script):
#!/bin/sh
isqlrf databasename <<!
update statistics;!
You might want to read up on 'update statistics' in the Informix manuals to
learn how you can fine-tune what it does for your particular application.
- Brian
This is
a multipart message in MIME format.
--=_related 0054D20286256D6D_=
Content-Type: multipart/alternative; boundary="=_alternative
0054D20286256D6D_="
--=_alternative 0054D20286256D6D_=
Content-Type: text/plain; charset="us-ascii"
I would use the dbexport command in Informix to save off the table and
data - I believe it automatically breaks up the data files into manageable
sizes. In addition, if you have views, etc. dependent upon the file you
are reorganizing you will lose them when you drop the table. When
possible, if you can export the entire database, you can correct the table
that has too many extents, then bring everything back in. The syntax is:
dbexport -c -q databasename -ss > logfile 2>&1
where logfile is the name of the outfile you want to create to review
after your export to ensure that there were no errors.
The flags have the following meaning:
-c This flag makes dbexport complete unless a fatal error
occurs; it will not stop for any errors except:
a. can't open tape device
b. bad writes
c. invalid command parameters
d. can't open database or no system permissions
-q Suppresses display of errors, warnings, and SQL data
definition statements
-ss Stands for server-specific (specifies initial and next
extent sizes, dbspace sizes, etc.)
The command will create a new directory named database_name.exp. Within
this directory will be a database_name.sql file, and as many *.dat files
as there are tables in your database. These *.dat files are
pipe-delimited text files that will be used to reload the tables during a
"dbimport". The syntax for the import is:
dbimport -c -q -i /home/informix databasename > logfile 2>&1
Important: You cannot run EITHER dbexport or dbimport unless you have
exclusive access to the database.
However, if your database is very large and you don't have room (or time)
to export and import the whole thing, just make sure what the dependencies
are so that you can make sure to back them up and restore them as well.
Then you can use the dbload and dbunload so that you can commit every
10000 records or so to avoid a long transaction. Look in the Migration
Guide for details on how all these work!
"BRIAN SMITH" <bsmith@lyrix.com>
Sent by: forum.subscriber@iiug.org
07/24/2003 08:02 AM
To: ids@iiug.org
cc:
Subject: Re: NO MORE EXTENTS; System is so slow [1589]
What is the output of 'oncheck -pe'?
When you created the 'exports' table, did you specify an extent size?
Example:
create table exports
(
blah blah,
blah2 blah,.
.
.
) extent size XXXXXX next size YYYYYY;
XXXXXX is the starting extent size in kilobytes based on how much data you
expect your table to hold. YYYYYY is the size of the next extent on
kilobytes that the Informix engine will allocate for the table if the
existing extents are all full. If an extent size is not specified when the
table is created, it defaults to something like 16KB (8 pages). As the
table grows, the engine will allocate larger and larger extents, but it is
still possible to run out of extents for the table. I believe each table
can only have a maximum of 253 extents in Informix IDS 7.30.
I think your best course of action would be to unload your table somehow
to save your data, then drop the table and recreate it with extent sizes
and then reload your data. You may have to unload it in sections because
the total size of the output unload file would more than likely be larger
than 2 Gig, which is the largest file size SCO's HTFS filesystem can
support, I think. Since your table is so large, I'd pick 'extent size
300000 next size 300000' since those values are somewhat even multiples of
your chunk sizes.
As for the slowness, are you periodically performing an 'update
statistics' command? The simplest form of this would be (in a shell
script):
#!/bin/sh
isqlrf databasename <<!
update statistics;!
You might want to read up on 'update statistics' in the Informix manuals
to learn how you can fine-tune what it does for your particular
application.
- Brian
--=_alternative 0054D20286256D6D_=
Content-Type: text/html; charset="us-ascii"
<br><font size=2 face="sans-serif">I would use the dbexport command in
Informix to save off the table and data - I believe it automatically breaks up
the data files into manageable sizes. In addition, if you have views,
etc. dependent upon the file you are reorganizing you will lose them when you
drop the table. When possible, if you can export the entire database,
you can correct the table that has too many extents, then bring everything
back in. The syntax is:</font>
<br>
<br><font size=2 face="sans-serif"><b>dbexport -c -q databasename -ss >
logfile 2>&1</b></font>
<br>
<br><font size=2 face="sans-serif">where logfile is the name of the
outfile you want to create to review after your export to ensure that there
were no errors.</font>
<br>
<br><font size=2 face="sans-serif">The flags have the following meaning:</font>
<br>
<br><font size=2 face="sans-serif">-c
This flag makes dbexport complete unless a fatal error
occurs; it will not stop for any errors except:</font>
<br><font size=2 face="sans-serif">
a. can't open tape device</font>
<br><font size=2 face="sans-serif">
b. bad writes</font>
<br><font size=2 face="sans-serif">
c. invalid command parameters</font>
<br><font size=2 face="sans-serif">
d. can't open database or no system
permissions</font>
<br>
<br><font size=2 face="sans-serif">-q
Suppresses display of errors, warnings, and SQL data
definition statements</font>
<br>
<br><font size=2 face="sans-serif">-ss
Stands for server-specific (specifies initial and next
extent sizes, dbspace sizes, etc.)</font>
<br>
<br><font size=2 face="sans-serif">The command will create a new directory
named database_name.exp. Within this directory will be a
database_name.sql file, and as many *.dat files as there are tables in your
database. These *.dat files are pipe-delimited text files that will be
used to reload the tables during a "dbimport". The syntax for
the import is:</font>
<br>
<br><font size=2 face="sans-serif"><b>dbimport -c -q -i /home/informix
databasename > logfile 2>&1</b></font>
<br>
<br><font size=2 face="sans-serif">Important: You cannot run EITHER
dbexport or dbimport unless you have exclusive access to the database.</font>
<br>
<br><font size=2 face="sans-serif">However, if your dat
Related threads
- IDS 10 table-level restore
- Informix Development Webinar December 11, 2007
- ontape -p/r with changed ROOTPATH
- Migrate from HP PA-RISC to HP ITANIUM by ontape