AUTOLOCATE
4 months ago
root> mkdir /opt/informix-connect-3.50.FC7 root> chown informix:informix /opt/informix-connect-3.50.FC7 root> ln -s /opt/informix-connect-3.50.FC7 /opt/informix-connect root> ls -ltr /opt total 4 drwxr-xr-x 2 informix informix 4096 Aug 1 20:56 informix-connect-3.50.FC7 lrwxrwxrwx 1 root root 30 Aug 1 20:56 informix-connect -> /opt/informix-connect-3.50.FC7
informix> touch /home/informix/chunks/DATADBS01.01 informix> chmod 660 /home/informix/chunks/DATADBS01.01 informix> onspaces -c -d datadbs01 -p /home/informix/chunks/DATADBS01.01 -o 0 -s 4194304 Verifying physical disk space, please wait ... Space successfully added. ** WARNING ** A level 0 archive of Root DBSpace will need to be done.Take a Level 0 backup like we're asked to and verify the dbspace was added by running onstat -d
informix> ontape -s -L 0 -d informix> onstat -d IBM Informix Dynamic Server Version 11.50.UC7IE -- On-Line -- Up 01:06:04 -- 144148 Kbytes Dbspaces address number flags fchunk nchunks pgsize flags owner name 4ae5b808 1 0x40001 1 1 2048 N B informix rootdbs 4ae5bc80 2 0x40001 2 1 2048 N B informix llogdbs01 4be72410 3 0x40001 3 1 2048 N B informix datadbs01 3 active, 2047 maximum Chunks address chunk/dbs offset size free bpages flags pathname 4ae5b968 1 1 0 1048576 518077 PO-B- /home/informix/chunks/ROOTDBS.01 4ae5bde0 2 2 0 1048576 11 PO-B- /home/informix/chunks/LLOGDBS01.01 4be72570 3 3 0 2097152 2097099 PO-B- /home/informix/chunks/DATADBS01.01 3 active, 32766 maximum NOTE: The values in the "size" and "free" columns for DBspace chunks are displayed in terms of "pgsize" of the DBspace to which they belong. Expanded chunk capacity mode: alwaysEasy peasy lemon squeezy.
informix> dbaccess - - > create database blog in datadbs01 with log; Database created.I have successfully created the blog database and am currently connected to it. When I told Informix to create blog in datadbs01 all of the housekeeping stuff for the blog database were put in datadbs01 and datadbs01 will be the default dbspace for any tables, indexes, etc. I create.
informix> vi create_tabs.sql create table blog ( id serial not null, name varchar(255) ) in datadbs01 extent size 256 next size 256 lock mode row; create table blog_post ( id serial not null, blog_id integer not null, title varchar(255) ) in datadbs01 extent size 1024 next size 1024 lock mode row; informix> dbaccess blog create_tabs.sql Database selected. Table created. Table created. Database closed.
informix> vi create_idx.sql create unique index blog_pk on blog (id) in datadbs01; alter table blog add constraint primary key (id) constraint blog_pk; create unique index blog_post_pk on blog_post (id) in datadbs01; alter table blog_post add constraint primary key (id) constraint blog_post_pk; create index blog_post_fk1 on blog_post (blog_id) in datadbs01; alter table blog_post add constraint foreign key (blog_id) references blog (id) constraint blog_post_fk1; informix> dbaccess blog create_idx.sql Database selected. Index created. Table altered. Index created. Table altered. Index created. Table altered. Database closed.I created 2 unique indexes with 2 primary keys on top of them and 1 non unique index with a foreign key on top of it. This way requires a little bit more typing, but it is how I like to do it and if you want to put the underlying referential constraint indexes in a non default dbspace and give these indexes a name then you should do it this way too.
informix> vi load.sql insert into blog (id, name) values (0, "Informix DBA"); insert into blog (id, name) values (0, "Informix technology"); select * from blog; informix> dbaccess blog load.sql Database selected. 1 row(s) inserted. 1 row(s) inserted. id 1 name Informix DBA id 2 name Informix technology 2 row(s) retrieved. Database closed. informix> vi load.unl 0|1|Creating Dbspaces, Databases, Tables and Indexes in Informix| 0|1|ZOMG, FYI - IM Informix Tech Support FTW| 0|1|Informix Backup and Restore - The Bare Minimum| 0|2|A bug can undermine your troubleshooting| 0|2|Informix Editions revisited| 0|2|New Informix editions: Bargain time?| informix> dbaccess blog - Database selected. > load from load.unl insert into blog_post; 6 row(s) loaded. > select * from blog_post; id 1 blog_id 1 title Creating Dbspaces, Databases, Tables and Indexes in Informix id 2 blog_id 1 title ZOMG, FYI - IM Informix Tech Support FTW id 3 blog_id 1 title Informix Backup and Restore - The Bare Minimum id 4 blog_id 2 title A bug can undermine your troubleshooting id 5 blog_id 2 title Informix Editions revisited id 6 blog_id 2 title New Informix editions: Bargain time? 6 row(s) retrieved.
informix> touch /home/informix/chunks/DATADBS01.02 informix> chmod 660 /home/informix/chunks/DATADBS01.02 informix> onspaces -a datadbs01 -p /home/informix/chunks/DATADBS01.02 -o 0 -s 4194304 Verifying physical disk space, please wait ... Chunk successfully added. informix> onstat -d IBM Informix Dynamic Server Version 11.50.UC7IE -- On-Line -- Up 00:26:37 -- 144148 Kbytes Dbspaces address number flags fchunk nchunks pgsize flags owner name 4ae5b808 1 0x60001 1 1 2048 N B informix rootdbs 4ae5bb88 2 0x40001 2 1 2048 N B informix llogdbs01 4bd65d28 3 0x60001 3 2 2048 N B informix datadbs01 3 active, 2047 maximum Chunks address chunk/dbs offset size free bpages flags pathname 4ae5b968 1 1 0 1048576 518259 PO-B- /home/informix/chunks/ROOTDBS.01 4ae5bce8 2 2 0 1048576 11 PO-B- /home/informix/chunks/LLOGDBS01.01 4bb51cc8 3 3 0 2097152 2095214 PO-B- /home/informix/chunks/DATADBS01.01 4bdb73e0 4 3 0 2097152 2097149 PO-B- /home/informix/chunks/DATADBS01.02 4 active, 32766 maximum NOTE: The values in the "size" and "free" columns for DBspace chunks are displayed in terms of "pgsize" of the DBspace to which they belong. Expanded chunk capacity mode: always
select
tabname::char(20) tab,
(pagreads + pagwrites) io,
pagreads,
pagwrites
from
sysptprof
where
dbsname = "ptpcc" and
tabname not matches ("sys*")
order by
(pagreads + pagwrites) desc;
...
tab io pagreads pagwrites
stock 833785 417350 416435
customer 226990 134711 92279
order_line 75287 42206 33081
stock_pk 45504 45504 0
oorder_uix1 36457 23932 12525
oorder 29367 26773 2594
order_line_pk 27214 11809 15405
oorder_pk 19565 16879 2686
customer_pk 17865 17865 0
new_order_pk 8496 6196 2300
district 4793 4745 48
customer_ix1 3894 3894 0
item 3773 3773 0
new_order 1979 773 1206
history 1522 12 1510
item_pk 815 815 0
warehouse 11 5 6
district_pk 7 7 0
warehouse_pk 1 1 0
Using this information I can place my tables and indexes in the appropriate dbspaces to spread the reads and writes across my disks evenly (giving the RAID10 set with 10 disks more I/Os to do and the RAID10 set with only 6 disks less I/Os to do) and hopefully improve my benchmark TPM.2010-05-07 16:35:07: benchmark - 100 terminals running for 900 seconds
2010-05-07 16:35:07: sql total min max avg num
2010-05-07 16:35:07: dbSelDistOrderId 366.158430 0.000592 3.143735 0.103085 3552
2010-05-07 16:35:07: dbSelDistPymt 1435.361208 0.000605 0.837176 0.037623 38151
2010-05-07 16:35:07: dbSelCustById 2255.096493 0.000784 1.295496 0.059110 38151
2010-05-07 16:35:07: dbSelNewestOrder 595.117024 0.000480 5.193018 0.172648 3447
2010-05-07 16:35:07: dbSelCustData 150.336596 0.000696 0.435675 0.038957 3859
2010-05-07 16:35:07: dbSelOrderLines 69.367385 0.000373 0.499633 0.020124 3447
2010-05-07 16:35:07: dbSelCustByOrder 1348.661282 0.000582 0.652879 0.038489 35040
2010-05-07 16:35:07: dbUpdOOrder 677.845172 0.000256 0.566081 0.019345 35040
2010-05-07 16:35:07: dbUpdDist 763.428072 0.000250 0.676018 0.019180 39803
2010-05-07 16:35:07: dbSelOrder 135.996560 0.000631 0.804294 0.039454 3447
2010-05-07 16:35:07: dbUpdDistPymt 4233.915816 0.000257 8.117577 0.110978 38151
2010-05-07 16:35:07: dbUpdStock 7224.883937 0.000263 0.797545 0.019243 375455
2010-05-07 16:35:07: dbSelCustCount 150.360743 0.000795 1.164771 0.069676 2158
2010-05-07 16:35:07: dbUpdWhsePymt 4263.935629 0.000254 8.083272 0.111765 38151
2010-05-07 16:35:07: dbUpdCustBalanceAndData 71.946746 0.000414 0.333886 0.018644 3859
2010-05-07 16:35:07: dbInsHistPymt 728.578052 0.000265 0.503783 0.019097 38151
2010-05-07 16:35:07: dbUpdCustBalance 666.788779 0.000283 0.656385 0.019444 34292
2010-05-07 16:35:07: dbSelCustByNameOrd 94.072595 0.000762 0.373746 0.046318 2031
2010-05-07 16:35:07: dbSelNewOrder 2017.488472 0.003504 5.246324 0.057577 35040
2010-05-07 16:35:07: dbSelCustWhse 5384.940138 0.000726 6.392744 0.135290 39803
2010-05-07 16:35:07: dbSelCustByIdOrd 82.164412 0.000758 0.852723 0.058026 1416
2010-05-07 16:35:07: dbUpdOrderLine 1070.116667 0.000348 1.189829 0.030540 35040
2010-05-07 16:35:07: dbSelStock 19846.689419 0.000703 1.215853 0.052860 375455
2010-05-07 16:35:07: dbSelOrderLineAmt 1375.268063 0.000681 0.623969 0.039249 35040
2010-05-07 16:35:07: dbSelWhsePymt 1448.440517 0.000599 0.677760 0.037966 38151
2010-05-07 16:35:07: dbInsOrderLine 7268.765215 0.000281 0.920966 0.019360 375455
2010-05-07 16:35:07: dbInsOOrder 764.685358 0.000281 0.520975 0.019212 39803
2010-05-07 16:35:07: dbDelNewOrder 770.737759 0.000263 7.087722 0.021996 35040
2010-05-07 16:35:07: dbSelDist 5383.367946 0.000620 6.788749 0.135250 39803
2010-05-07 16:35:07: dbInsNewOrder 767.917783 0.000262 0.398216 0.019293 39803
2010-05-07 16:35:07: dbUpdCustDelivery 1270.802428 0.000293 1.166629 0.036267 35040
2010-05-07 16:35:07: dbSelCountStock 118.598752 0.006119 1.274016 0.033389 3552
2010-05-07 16:35:07: dbSelItem 14574.856113 0.000633 0.837902 0.038819 375455
2010-05-07 16:35:07: totals 87376.689563 - - - 2240081
2010-05-07 16:35:07:
2010-05-07 16:35:07: time: 901.060969, total transactions: 88584, TPM: 5898.646354, SPM: 149162.891976
Maxing out buffers increases out TPM by almost 62% to 5899, not bad for a simple config change and engine bounce.2010-05-07 15:11:26: benchmark - 100 terminals running for 900 seconds
2010-05-07 15:31:28: sql total min max avg num
2010-05-07 15:31:28: dbSelDistOrderId 264.492183 0.000589 4.998652 0.120443 2196
2010-05-07 15:31:28: dbSelDistPymt 666.518798 0.000611 0.874549 0.028071 23744
2010-05-07 15:31:28: dbSelCustById 3865.562448 0.000819 2.452900 0.162802 23744
2010-05-07 15:31:28: dbSelNewestOrder 3540.358412 0.000530 13.473251 1.700460 2082
2010-05-07 15:31:28: dbSelCustData 69.895807 0.000703 0.853232 0.030310 2306
2010-05-07 15:31:28: dbSelOrderLines 33.498745 0.000373 0.484876 0.016090 2082
2010-05-07 15:31:28: dbSelOrder 69.810965 0.000608 0.529128 0.033531 2082
2010-05-07 15:31:28: dbSelCustByOrder 1081.806514 0.000592 1.636825 0.049017 22070
2010-05-07 15:31:28: dbUpdOOrder 1071.331497 0.000262 2.102251 0.048542 22070
2010-05-07 15:31:28: dbUpdDist 369.739051 0.000248 0.864587 0.015036 24591
2010-05-07 15:31:28: dbSelCountStock 5653.657284 0.006115 14.663261 2.574525 2196
2010-05-07 15:31:28: dbUpdDistPymt 3211.747694 0.000262 6.936206 0.135266 23744
2010-05-07 15:31:28: dbUpdStock 3607.889070 0.000283 1.362896 0.015618 231006
2010-05-07 15:31:28: dbSelCustCount 207.250488 0.017086 2.808686 0.166466 1245
2010-05-07 15:31:28: dbUpdWhsePymt 2955.763594 0.000259 5.403844 0.124485 23744
2010-05-07 15:31:28: dbUpdCustBalanceAndData 32.769989 0.000403 0.146913 0.014211 2306
2010-05-07 15:31:28: dbInsHistPymt 368.780551 0.000269 1.330070 0.015532 23744
2010-05-07 15:31:28: dbUpdCustBalance 345.092451 0.000297 1.336634 0.016097 21438
2010-05-07 15:31:28: dbSelCustByNameOrd 45.854596 0.000788 0.640742 0.038566 1189
2010-05-07 15:31:28: dbSelNewOrder 1986.016858 0.003527 6.152908 0.089987 22070
2010-05-07 15:31:28: dbSelCustWhse 5416.771947 0.000852 6.570103 0.220275 24591
2010-05-07 15:31:28: dbSelCustByIdOrd 108.330620 0.003462 1.446552 0.121311 893
2010-05-07 15:31:28: dbUpdOrderLine 1515.644348 0.000358 2.189919 0.068674 22070
2010-05-07 15:31:28: dbSelStock 26748.686979 0.000714 5.385313 0.115792 231006
2010-05-07 15:31:28: dbSelOrderLineAmt 688.818336 0.000692 1.372060 0.031211 22070
2010-05-07 15:31:28: dbSelWhsePymt 708.854190 0.000605 0.904277 0.029854 23744
2010-05-07 15:31:28: dbInsOrderLine 4126.250988 0.000295 2.563897 0.017862 231006
2010-05-07 15:31:28: dbInsOOrder 604.548543 0.000291 2.471327 0.024584 24591
2010-05-07 15:31:28: dbDelNewOrder 479.087660 0.000269 8.575327 0.021708 22070
2010-05-07 15:31:28: dbSelDist 3854.715855 0.000630 7.683246 0.156753 24591
2010-05-07 15:31:28: dbInsNewOrder 412.719221 0.000263 2.343799 0.016783 24591
2010-05-07 15:31:28: dbUpdCustDelivery 2324.551898 0.000327 2.426604 0.105326 22070
2010-05-07 15:31:28: dbSelItem 12098.294207 0.000647 1.985179 0.052372 231006
2010-05-07 15:31:28: totals 88535.111786 - - - 1383948
2010-05-07 15:31:28:
2010-05-07 15:31:28: time: 901.863525, total transactions: 54876, TPM: 3650.840630, SPM: 92072.556084
#!/usr/bin/python
import os
import sys
import getopt
import time
import informixdb
def usage(server):
print "%s -s <server> -d <database> -w <where clause> -n <num rows> -s <sleep seconds> [-v]" % sys.argv[0]
print " -s : DBSERVERNAME, default %s" % server
print " -d : database name, required"
print " -w : where clause, required"
print " -n : number of rows per transaction, optional, default 10"
print " -s : sleep seconds in between each transaction, optional, default 1"
print " -v : verbose output, optional, default off"
# wt4logbf executes onstat to identify the number of threads waiting for a log buffer
# if more than maxlogbfwt threads waiting on logbf are found we will sleep for sleepSeconds
#
# threads waiting for logbf is an indication that HDR is behind and if we do not throttle
# back out deletes engine performance will drop
def wt4logbf(maxlogbfwt, sleepSeconds):
# execute onstat -g ath and count the number of threads waiting on logbf
logbfwt = int(os.popen("onstat -g ath | grep logbf | wc -l").readlines()[0])
# sleep sleepSeconds and recheck number of waiting threads
# repeat until number of threads waiting for logbf is below maxlogbfwt
while logbfwt >= maxlogbfwt:
print "max logbf waits reached [%d >= %d] sleeping %d seconds" % (logbfwt, maxlogbfwt, sleepSeconds)
sys.stdout.flush()
time.sleep(sleepSeconds)
logbfwt = int(os.popen("onstat -g ath | grep logbf | wc -l").readlines()[0])
def main():
server = os.getenv("INFORMIXSERVER")
database = None
where = None
numDelPerTransaction = 10
sleepSeconds = 1
verbose = False
# parse command line arguments
try:
opts, args = getopt.getopt(sys.argv[1:], "S:d:w:n:s:v?")
except:
usage(server)
sys.exit(2)
for opt, val in opts:
if opt == "-S":
server = val
if opt == "-d":
database = val
if opt == "-w":
where = val
if opt == "-n":
numDelPerTransaction = int(val)
if opt == "-s":
sleepSeconds = int(val)
if opt == "-v":
verbose = True
if opt == "-?":
usage(server)
sys.exit()
# if the required arguments were not passed display the usage and exit
if (numDelPerTransaction < 1) or (sleepSeconds < 0) or (where is None):
usage(server)
sys.exit()
# sql to select the primary key fields (pkcol1 and pkcol2) from table1 that
# meet the user defined where clause
sqlSelect = """
select
pkcol1,
pkcol2
from
table1
where
%s
""" % (where, )
# sql to delete a row by the primary key of table1
sqlDelete = """
delete from
table1
where
pkcol1 = :1 and
pkcol2 = :2
"""
# connect to the database
try:
dbCon = informixdb.connect("%s@%s" % (database, server), autocommit = False)
except informixdb.DatabaseError, e:
print "unable to connect to %s@%s, %ld" % (database, server, e.sqlcode)
sys.exit(2)
# define select and delete cursors
try:
dbSelectCursor = dbCon.cursor(rowformat = informixdb.ROW_AS_OBJECT, hold=True)
dbDeleteCursor = dbCon.cursor()
except informixdb.DatabaseError, e:
print "unable to define cursors, %ld" % (e.sqlcode, )
sys.exit(2)
# set some session attributes
try:
dbSelectCursor.execute("set lock mode to wait")
dbSelectCursor.execute("set isolation dirty read")
except informixdb.DatabaseError, e:
print "unable to set session attributes, %ld" % (e.sqlcode, )
sys.exit(2)
try:
# select the primary key of all rows in table1 that meet our where clause
dbSelectCursor.execute(sqlSelect)
numRowsInTransaction = 0
totalRows = 0
startTime = time.time()
# for each row that meets our where clause, delete it
# committing the transaction and checking engine load at the user
# defined intervals
for dbRow in dbSelectCursor:
if verbose:
print "deleting row pkcol1 = %ld and pkcol2 = %ld" % (dbRow.pkcol1, dbRow.pkcol2)
# attempt to delete this row
try:
dbDeleteCursor.execute(sqlDelete, (dbRow.pkcol1, dbRow.pkcol2))
numRowsInTransaction = numRowsInTransaction + 1
totalRows = totalRows + 1
except informixdb.DatabaseError, e:
print "unable to delete row pkcol1 = %ld and pkcol2 = %ld, %ld" % (dbRow.pkcol1, dbRow.pkcol2, e.sqlcode)
# if we have met out rows to delete per transaction limit,
# commit the transaction, sleep and check engine load
if numRowsInTransaction == numDelPerTransaction:
dbCon.commit()
print "deleted %d rows [%f rows/second], sleeping %d seconds" % (totalRows, totalRows / (time.time() - startTime), sleepSeconds)
sys.stdout.flush()
numRowsInTransaction = 0
time.sleep(sleepSeconds)
wt4logbf(2, 30)
# commit the last transaction
dbCon.commit()
print "deleted %d rows" % totalRows
except informixdb.DatabaseError, e:
print "unable to execute %s, %ld" % (sqlSelect, e.sqlcode)
sys.exit(2)
if __name__ == "__main__":
main()
Send your suggestions, questions, complaints, general greetings and cash donations to andrew@informix-dba.com