 |
IDS Forum
Re: NO MORE EXTENTS; System is so slow
Posted By: Stephanie_Peltier@txnp.uscourts.gov
Date:
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 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!</font>
<br>
<br><font size=2 face="sans-serif"><br>
</font><img src=cid:_1_0240000060780054D20286256D6D>
<br>
<br>
<br>
<table width=100%>
<tr valign=top>
<td>
<td><font size=1 face="sans-serif"><b>"BRIAN SMITH" <bsmith@lyrix.com></b></font>
<br><font size=1 face="sans-serif">Sent by: forum.subscriber@iiug.org</font>
<p><font size=1 face="sans-serif">07/24/2003 08:02 AM</font>
<br>
<td><font size=1 face="Arial"> </font>
<br><font size=1 face="sans-serif"> To: ids@iiug.org</font>
<br><font size=1 face="sans-serif"> cc: </font>
<br><font size=1 face="sans-serif"> Subject: Re: NO MORE EXTENTS; System is so slow [1589]</font></table>
<br>
<br>
<br><font size=2 face="Courier New">What is the output of 'oncheck -pe'?<br>
<br>
When you created the 'exports' table, did you specify an extent size?<br>
Example:<br>
create table exports<br>
(<br>
blah blah,<br>
blah2 blah,<br>
.<br>
.<br>
.<br>
) extent size XXXXXX next size YYYYYY;<br>
<br>
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.<br>
<br>
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.<br>
<br>
As for the slowness, are you periodically performing an 'update statistics' command? The simplest form of this would be (in a shell script):<br>
<br>
#!/bin/sh<br>
isqlrf databasename <<!<br>
update statistics;<br>
!<br>
<br>
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.<br>
<br>
- Brian<br>
<br>
<br>
</font>
<br>
<br>
--=_alternative 0054D20286256D6D_=--
--=_related 0054D20286256D6D_=
Content-Type: image/gif
Content-ID: <_1_0240000060780054D20286256D6D>
Content-Transfer-Encoding: base64
R0lGODlh/wGPAOcAAAAAAP////j48AAAgAAAQCAggCAgQCAAgMDYwAAgQAAggCAAQKDI8MDAwAAA
wCAgwEAggEBAgP7/+v/d3S4W3d0c7AcANAAAAEBAgAD/AAAAAIAAAAAAAAD///8AgACAAAAA/wAJ
CQkJ/v/+//r/3d36/93dCMgAAEsC/v/SBAAAAPoBAAAAAAAAzAAAyAAEfv90x4wAyAABAAAAS0IO
Aoy4EgC8sBIAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAzasAJAsEAAAAAAAAAAD//wsEAAAAAAAAAAAAAAApCwQA
AAAAAAAAAAAAAAAAAAAA////////AAAAAAAAAAAAAAAAAAAAAACiCwQAAAAAAAAAAAAAAPr/BgIC
AAAAPkcAAPi34eI6uYxz4oMSALVkJYXmAAAAyVNgAGtqJYUIANioWgBrbSWGAAAAIAAAAQABAAAA
+gEAAOAC+gEAABDH2KhaAGttJYbYqFoAa20lhgAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAD6
AQAAZMYAAAAAAAD6AQAArBkAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAD6AQAA8Bj6AQAA5FEAAAAA
AAD6AQAAULoAAAAAAAD6AQAAIBr6AQAAEMf6AQAAUA36AQAAuLr6AQAAPAz6AQAACBj6AQAA6G76
AQAAaGD6AQAA9Iv6AQAAbH36AQAAlKn6AQAAeJr6AQAAGLn6AQAAsLgAAAAAAAAAAAAAAAAAAAAA
AAD6AQAAwBv6AQAArAwAAAAAAAAAAAAAAAAAAAAAAAD6AQAAkMT6AQAAkBr6AQAAYAMAAAAAAAD6
AQAAPAQAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAD6AQAANL0AAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAD6ASwAAAAA/wGPAEAI/wADCBxI
sKDBgwgTKlzIsKHDhxAjSpxIsaLFixgzatzIsaPHjxgBFBQZgORDkx9RlkxIUiXIlzBjypxJs6bN
mzglAti5c2BPgTx9ljRJ1KfIlkJXAv05kiXTlkiXFs1JtarVq1izasWJ0iVBr0lTKjUINmPZplvT
ql3Ltq1bhTyZLlX69KjUrnalfpUL9CDUn1Pp9h1MtO7guVCNBn7LuLHjx5AZqjw7GS7ahWfDRt7M
ubPnz1zlgo27WPPXvYVF5wWsOGxm0LBjy56tVnTD0he73o74+vBl2sCDCx+OsLLew6yRP3VNNu7p
40ORt57bHLfvoYELjw1KvLv37zV1U//f+7u0eM1R0Y9dD769+/fwSZ9/Pv+uYpfy7WPnzh2+//8A
BmecTOfxFd1zB+pWn3gDrmdggBBGKKFlxeUVXWrMOeWcaYT5dd901aWGIVkTlmjiiSimqOKKKQqA
EAEwwlhAjDQSQJCLAeCIYwAG0GjAjzveGGQANdYopEAJENCjkkXGuCSMAu24ZAIzGinAji5mmWOO
V26po45e3sjimGRadaUACBBJwAAFsKmlAD0OIOeMB60p5wBXElBAlQlweSaPd+7p559i4ohAly7G
OMABBCxggIuHdrmlkncOQEACSfbI5aA4/tRAlEPt2FNPAgRV6qMuysefdnSNauCrf1H/Z9hJ+Bl1
4K22jYcdZuO5ShqucKmaHmCx9neXsbbuSmyyyEqGn4jMHjWZrxuSh1hi+/1VbUN/yninjVmeKa6k
RLb5wAGVWlqpnnQOZOe6oMJpZwELtItolgZYWmWU4goEo5xMDuQiA4FaSsC9Q271YJlrLVzRtgxH
LPHEFFds8cUYZ+zRctJt1zF9SQ0bIseCeWxybxqnrPLKLLfsMmcNdkiiySBf5lV2IWcocskI3vxx
ei8HLfTQRBe9csxOYfbrzx/nLDPPKBst9dRUV221ylHtjPPTO7PHXtYznxx2z9epJ53PXl+t9tps
t+32TQ127SCCHDp9HYZRv6333nz3/+33W/nNPNXgOpXHdNrFGs7gqE//7fjjkEcu+eSUV/4RoQSg
C7Ci6VpqgMCSCoDuAZp/q1CS79LYeaNbAvpv55WSbrC7bXoOasEDLBDulaSSRy5QnwPw+a663loc
rc2ltHTxxgqrrbTLo0bshstDnLy1FTokLFWBQ6drXc9TO1q1S1dvPUNB2ll67IHyS/ucB09a6aOt
8/iumwh92fqfAtyP56CgExi6aienAeqLX6E7CJbCFCQtgYov4OPPsW6Wn2X1qnwagtizLhii+1CQ
eiAkX39gNRIRYat7yRqZCT2IreKxcFoldGEEodesY33IhtnKG6gytyiAbepI7iogm//AVZD+EdBS
+yPS+no4J9LZKEqRulG+NEe/+gUgTeUS4p1Gx64ncsqBlgujGMdIxjKa8YxoTKMai/ZBEElLKKop
CslqOKAREm9EK9HhGvfoFuPghnBc6xjQxka3QHpNj3xM5GwQqchGOvKRkCxTHafnRuM1L44s+dpq
EkS4OVIyj8cZ4XIYGclI+tFugISa4CiEysNpbZWu1FkpZ0nLWtrylrgMGtIKySuQxaovmLxjJR2W
y2LmZJemoczxBNlKsRnSmNCMpjSnSc1qSpJmdqSeJUeZR7Sh8pN2GREdP/kb4mEzmNbcoxyZGUub
ceiUaNlarcI2SLM1ZWu8TKc+98n/z37605oLmluGMpnJzFgobf9MaEg8NMGmERJxBEWoRBVK0Ypa
9KIYxegk8bIdbkYtoAWCaEDhOLZ1ZrSUo2nmPLXH0BwSJnpli9tDaUjMk97SOjbNqU53ytOe6vR3
CAQjQj61v91ZEYEJCZcCxwVUUBmkX6BzIKEKEkUrJvBeYfphlw51VJ9erYHsgx2MfpRELDJJi+2K
qpT8J1YvBmBGclLAETtXOyURxEeWOkABZGewN+XoJ4baSacAkKW4FLamXjVav/6FLiUxwEt6CtQT
sbQmzQmgdgqA0biipKc17ct9alqAkzYVrnfp9WD6EwgCXueAATxWSzOqUvzEJVgo/x4ITBciLJBY
lUJztsp42IuWb31bllkx7obbRG4Ml9tNUdqKYyTE3iWv5ReO6ue5qCGPq7Inqze6EVodlKF2RPhc
3jpEUgyAKxM7+6/azShhqcPRuzJbPzjli4nuU2oSg3pZz8ppeIeq6kDi1DkuPjHAg11aqnYyvFIR
Njpp6h0Ew5c184qXgzlE2cIojGHl4EpbHSYhyaq73MBZELgO2y6wMuzCYHHXxDRVMYrDV17qXjgi
XbpUwWY0vBy9Fqrzzayi9pS5dpk1sqaz4lgDKLAtVYq+QXVqAB+r3jZpNl5dxQpiddmRLSOvbCz7
k16/9bkrRYqpOTKAaMdcQEX59/9bu2Mrk4rcJkYVIMrvszKnWkcACBDwvf5inwF+jKjErjFRS5It
V53apQb0iMhL9pPAnlQA+gbpsarbXAG+BNUBy4hRuB0IFjH9JDtd+YtNNbSqV83qVrv61bCOtaxn
TWtYwxQijOwNCmda62nC8yQwQSbPek1NZMmzwoZMjtzuxs5UtpDYtRyWNp857ZLdenwSPFm1of3I
Xw8ObDJLjrXfucxxJ5vb+yQlutctGXa7+93wjsmtcR3siLLyofE2o7CVCZKD5lMjGs633rztSnCr
kmZke2l3ww1uZ79SkOoWeMQ2CrRqg9ec1zZxM80NtTlKr50SJ1pIExSyh4t72T7/c7bTAKlyhWMT
zCGPucxnTvOa27xvwsZhBr2sw4jfXIw5D3o5y33vn0d76CXUdTy1ZvCH/3aiRq9czvFdt5ef8+Ts
hHrUKYfTZBZ9oCZ3+rK3Tvaym/3saE+7xqZOdYpEr+dmMaGX1V4xCykd5m5Hut7t/XW80x1r4S65
hw9uwZUGnmtNB3MqDXc2Wfr97/EhqTOtjkGHfk05+KynN+vpm7cnHY+Ql5B1Wz75rhm+5fK0W8JT
CnZtWz70sI+97GdP+6hrsKUTfN51P45DEc699n9Dyga97nd+K7M+wJd6IQ9aoG3V8DTztrvPk1+1
0zetuCRqHtFZT33Jcb9mzJZ8/0wZX8fum//86E+/+me/a3kbG/cC3Y9ykVu+Zk1//cNhu1hAyVwH
pXz54Nd5wYV/J0JwT/dtheN1IYUX0qY4FmdSBIgixZVtrUKBLEV+jjd00BN/5DZsEVgirKdsTtdu
8Nc4qmd3JTU3f/SBgMeCO/V8LhiDMjiDNFiDNniDOJiDn9FUhaYQ5HJVP+QlRJVUQZg/WYVlRBg6
ZhZVTeYlYKQji4YlUoVUWLRoOugYAlAvBrQ6kYaE/lIvPRQjTwVkSLY6BdBjAmEActVZsAM/dUY/
8pVp8zNZnxdHJLFggAVcV7gW5NImdaZedAUlAoNF/QM7gMZk/hInnFMpdTZb5f8yV204J5uWZzQS
AbFjL2dCKkrFFKXiEw62JRIGQ33HXQO4EdYzfLk3Pao4gXIHIt5DYqUYi9U1b+Fhf6L4PSv0ikkn
PaA3XA+xWfdDRC5yREqyO/LFRGwyAEKGVJMiZJaSJJClOYyiKSWxJI8miW3mJJeCIwogJw7gVvaT
jGmVicKzKk8VKtv1YDPWS7dxf86SQsamGtLFXNH1dPC4gcQ0YS7WjrLofveoj/94QbOSXRb4YQYJ
EcBIQJOFI3syP1f0QzwkJ2oSI33CPzlCjKDlL6eWQFGyV42oO6GWKPcDjfn1hGl4h4MVAJ+SJl0i
WNSTYN1TPQ11PdaiKh5Uddf/U3EHKZAT9hoxxkHQ9Sw2eV2riIqc5HszdJNbJoLZ1Ssz5l16aBjm
Y44tllRgclm1wyitgylIZlcFYQBZGT9IRic/GFl+yGkLVCiS4l9EplaT5i2MSCN7diUGoI4lJhIO
hocL1ltHOW0DyZecFC20CItLF2Ljk5POhZjOMWI02V0g1GEzyXtBWX+8QUE2llstJj40xjxQWZQI
6UCRtT5vNkDC+D5scleL0lhNyIZDpFVjKGo5wlXsAmdFuEN+GFa0KWlOmERXyZt+xZmZqRe2iJjC
VZkEZZN/GZhU2X/mSElBkY+iaJnKApDEOZ2Z+VET2FBFuYHy54vPqWIyBp6//0dZgKiQT4IwA9E5
AvNmpUkkBeNWUwhAAZSMc6KEP1gQorUm3TgnfXWEG3OL3flbG1ZBLWSHODmU71SgQrmLsyhKDDgt
FraORJkYD5Ji4sl8iVmVCAqcUcmA88egfHlJv5dfanKb8CkkZ5Yk6eKHVfItk7glDSmOqrWWNVJm
RSUAVKKMSHRFhNI/MJKf8CkAOpqMZdZpteGOEoOkVel2g5kyaylZqMVUU2VEZVg6daaeOAKGbghZ
lbUoV4aeWdRZcykv37KQb0WfO6qbe9gex9hDbYkmxriePKRnUhYA6kJABfE6kCiN9KJaR5WFQlSk
Q1KIQqQ5Q2YwXrRZa8qm5VJiqOBYVBqZjQkTqaYTJEtCV7GVAMY4JDBSOnAoZTkGiQBjAJoqJKm2
qMMhVaGmlkBEaP2CRTd6Xg9ZRK8KWpslpZMaQFKKqrzaq776q8AKGQEBADs=
--=_related 0054D20286256D6D_=--
Messages In This Thread
IDS Forum is maintained by Administrator with WebBBS 5.12.
|
|
