Join IIUG
 for   
 

Informix News
18 Nov 13 - ZDNet - Top 20 mobile skills in demand... Read
09 Sep 13 - telecompaper - Shaspa and Tatung have shown a new smart home platform at Ifa in Berlin. Powered by the IBM Informix software... Read
06 Sep 13 - IBM data magazine - Mission Accomplished - Miami, Florida will be the backdrop for the 2014 IIUG Informix Conference... Read
01 Feb 13 - IBM Data Magazine - Are your database backups safe? Lester Knutsen (IBM Champion) writes about database back up safety using "archecker"... Read
14 Nov 12 - IBM - IBM's Big Data For Smart Grid Goes Live In Texas... Read
3 Oct 12 - The Financial - IBM and TransWorks Collaborate to Help Louisiana-Pacific Corporation Achieve Supply Chain Efficiency... Read
28 Aug 12 - techCLOUD9 - Splunk kicks up a SaaS Storm... Read
10 Aug 12 - businessCLOUD9 - Is this the other half of Cloud monitoring?... Read
3 Aug 12 - IBM data management - Supercharging the data warehouse while keeping costs down IBM Informix Warehouse Accelerator (IWA) delivers superior performance for in-memory analytics processing... Read
2 Aug 12 - channelbiz - Oninit Group launches Pay Per Pulse cloud-based service... Read
28 May 12 - Bloor - David Norfolk on the recent Informix benchmark "pretty impressive results"... Read
23 May 12 - DBTA - Informix Genero: A Way to Modernize Informix 4GL Applications... Read
9 Apr 12 - Mastering Data Management - Upping the Informix Ante: Advanced Data Tools... Read
22 Mar 12 - developerWorks - Optimizing Informix database access... Read
14 Mar 12 - BernieSpang.com - International Informix User Group set to meet in San Diego... Read
1 Mar 12 - IBM Data Management - IIUG Heads West for 2012 - Get ready for sun and sand in San Diego... Read
1 Mar 12 - IBM Data Management - Running Informix on Solid-State Drives.Speed Up Database Access... Read
26 Feb 12 - BernieSpan.com - Better results, lower cost for a broad set of new IBM clients and partners... Read
24 Feb 12 - developerWorks - Informix Warehouse Accelerator: Continuous Acceleration during Data Refresh... Read
6 Feb 12 - PRLOG - Informix port delivers unlimited database scalability for popular SaaS application ... Read
2 Feb 12 - developerWorks - Loading data with the IBM Informix TimeSeries Plug-in for Data Studio... Read
1 Feb 12 - developerWorks - 100 Tech Tips, #47: Log-in to Fix Central... Read
13 Jan 12 - MC Press online - Informix Dynamic Server Entices New Users with Free Production Edition ... Read
11 Jan 12 - Computerworld - Ecologic Analytics and Landis+Gyr -- Suitors Decide to Tie the Knot... Read
9 Jan 12 - planetIDS.com - DNS impact on Informix / Impacto do DNS no Informix... Read
8 Sep 11 - TMCnet.com - IBM Offers Database Solution to Enable Smart Meter Data Capture... Read
1 Aug 11 - IBM Data Management Magazine - IIUG user view: Happy 10th anniversary to IBM and Informix... Read
8 Jul 11 - Database Trends and Applications - Managing Time Series Data with Informix... Read
31 May 11 - Smart Grid - The meter data management pitfall utilities are overlooking... Read
27 May 11 - IBM Data Management Magazine - IIUG user view: Big data, big time ( Series data, warehouse acceleration, and 4GLs )... Read
16 May 11 - Business Wire - HiT Software Announces DBMoto for Enterprise Integration, Adds Informix. Log-based Change Data Capture... Read
21 Mar 11 - Yahoo! Finance - IBM and Cable&Wireless Worldwide Announce UK Smart Energy Cloud... Read
14 Mar 11 - MarketWatch - Fuzzy Logix and IBM Unveil In-Database Analytics for IBM Informix... Read
11 Mar 11 - InvestorPlace - It's Time to Give IBM Props: How many tech stocks are up 53% since the dot-com boom?... Read
9 Mar 11 - DBTA - Database Administration and the Goal of Diminishing Downtime... Read
2 Feb 11 - DBTAs - Informix 11.7 Flexible Grid Provides a Different Way of Looking at Database Servers... Read
27 Jan 11 - exactsolutions - Exact to Add Informix Support to Database Replay, SQL Monitoring Solutions... Read
25 Jan 11 - PR Newswire - Bank of China in the UK Works With IBM to Become a Smarter, Greener Bank... Read
12 Oct 10 - Database Trends and Applications - Informix 11.7: The Beginning of the Next Decade of IBM Informix... Read
20 Sep 10 - planetIDS.com - ITG analyst paper: Cost/Benefit case for IBM Informix as compared to Microsoft SQL Server... Read
20 Jul 10 - IBM Announcements - IBM Informix Choice Edition V11.50 helps deploy low-cost scalable and reliable solutions for Apple Macintosh and Microsoft Windows... Read
20 Jul 10 - IBM Announcements - Software withdrawal: Elite Support for Informix Ultimate-C Edition... Read
24 May 10 - eWeek Europe - IBM Supplies Database Tech For EU Smart Grid... Read
23 May 10 - SiliconIndia - IBM's smart metering system allows wise use of energy... Read
21 May 10 - CNET - IBM to help people monitor energy use... Read
20 May 10 - ebiz - IBM Teams With Hildebrand To Bring Smart Metering To Homes Across Britain... Read
19 May 10 - The New Blog Times - Misurare il consumo energetico: DEHEMS è pronto... Read
19 May 10 - ZDNet - IBM software in your home? Pact enables five-city smart meter pilot in Europe... Read
17 March 10 - ZDNet (blog) David Morgenstern - TCO: New research finds Macs in the enterprise easier, cheaper to manage than... Read
17 March 2010 - Virtualization Review - ...key components of Big Blue's platform to the commercial cloud such as its WebSphere suite of application ser vers and its DB2 and Informix databases... Read
10 February 2010 - The Wall Street Journal - International Business Machines is expanding an initiative to win over students and professors on its products. How do they lure the college crowd?... Read

End of Support Dates
IDS 11.10 - 30 Sep 2012
Subscribe to notifications

IIUG on Facebook IIUG on Twitter

[ View Thread ] [ Post Response ] [ Return to Index ] [ Read Prev Msg ] [ Read Next Msg ]

IDS Forum

VERY slow BTS index build

Posted By: MICHAEL HOFFMAN
Date: Friday, 5 September 2014, at 6:13 p.m.

Hi All,
First off, thank you all for your assistance so far in helping me get BTS blade setup.

I am trying to build a BTS index across 950K rows defined as char(25).

create index cp_lnm_bts_idx on char_person (cp_lname bts_char_ops) using

bts

We ran into issues with running out of Temp space (we expanded the SBSPACETEMP to 60GB and still ran into the issue) or running into Long Transactions.

I found a blog writeup by Mark Ashworth ("Use of temporary sbspace in BTS" 12/2010)
(http://webcache.googleusercontent.com/search?q=cache:FDYPM8cCceQJ:https://www.ibm.com/developerworks/community/blogs/markashworth/entry/use_of_temporary_snspace_in_bts1+&cd=1&hl=en&ct=clnk&gl=us)

Once I recreated the bts_temp space as a temporary sbspace, the build completed, but it took **over 20 HOURS**!!!

I bounced the engine after adding more BTS VPs (1 -> 3), Buffers (50K -> 400K), and splitting the SB temp into 3 separate 20GB spaces.

The index build is running again, however it seems to be dragging along. So far, running for 6 hours. The buffers seem to be adding/modifying very slowly, so I'm expecting this rebuild to be no faster than the previous.

One immediate issue: setting SBSPACETEMP to bts_temp:bts_temp1:bts_temp2 causes a warning when the index build begins ("space not found"). Is ":" not the appropriate delimiter? It's what we use for DBSPACETEMP. I tried ',', but that didn't work either -- no error, but all work was logged in the default sbspace.

I had to reset SBSPACETEMP to only bts_temp, and the system seems to be working with only that temp space. :-(

Here are some snapshots: (a couple of the snapshots were run twice after 10 or 15 seconds to show changes) (relevant settings from onstat -c at end)

Last Name (SQL OUTPUT)
==========================
start-time
2014-09-05 10:37:08.000

date: Fri Sep 5 15:26:10 MDT 2014

onstat -g ses 27
------------------
IBM Informix Dynamic Server Version 11.50.FC6 -- On-Line -- Up 04:58:08 -- 1278976 Kbytes

session effective #RSAM total used dynamic
id user user tty pid hostname threads memory memory explain
27 devdba - 1 3406 devdata2 1 700416 568784 off

tid name rstcb flags curstk status
57 sqlexec 145366e00 --BP--- 23550 running-

Memory pools count 2
name class addr totalsize freesize #allocfrag #freefrag
27 V 1463c2040 696320 130824 638 132
27*O0 V 146410040 4096 808 1 1

name free used name free used
overhead 0 6576 mtmisc 0 72
scb 0 144 opentable 0 11832
filetable 0 2192 log 0 16536
temprec 0 21664 partn 0 72
keys 0 1192 ralloc 0 441448
gentcb 0 1584 ostcb 0 2816
sqscb 0 24040 sql 0 72
rdahead 0 1120 hashfiletab 0 552
osenv 0 3504 sqtcb 0 10952
fragman 0 11640 GenPg 0 872
sapi 0 200 SAPI callback 0 720
udr 0 872 sqlj 0 72
vii 0 7736

sqscb info
scb sqscb optofc pdqpriority optcompind directives
1463b90c0 1463c3028 0 100 0 1

Sess SQL Current Iso Lock SQL ISAM F.E.
Id Stmt type Database Lvl Mode ERR ERR Vers Explain
27 CREATE INDEX char CR Not Wait 0 0 9.24 Off

Current SQL statement :
create index cp_lnm_bts_idx on char_person (cp_lname bts_char_ops) using

bts

onstat -d
------------------
IBM Informix Dynamic Server Version 11.50.FC6 -- On-Line -- Up 04:50:03 -- 1
278976 Kbytes

Dbspaces
address number flags fchunk nchunks pgsize flags owner
name
14603ae28 4 0x1 7 1 2048 N informi
x chardb
1461b61c0 26 0x68001 61 2 2048 N SB informi
x bts_sbpace
1461b6358 27 0x4a001 62 1 2048 N UB informi
x bts_temp
1461b64f0 28 0x4a001 63 1 2048 N UB informi
x bts_temp1
1461b6688 29 0x4a001 65 1 2048 N UB informi
x bts_temp2

Chunks
address chunk/dbs offset size free bpages flags
pathname
1461b7218 7 4 50 1000000 362208 PO---
/dev/md/rdsk/d114
1461bddb8 61 26 0 10000000 7993025 7999947 POSB-
/dev/md/rdsk/d136

Metadata 2000000 1680069 2000000
1461bf028 62 27 0 10000000 9306697 9326885 POSB-
/dev/md/rdsk/d137

Metadata 673062 500842 673062
1461bf218 63 28 0 10000000 9326885 9326885 POSB-
/dev/md/rdsk/d138

Metadata 673062 500842 673062
1461bf408 64 26 0 10000000 9264826 9326931 POSB-
/dev/md/rdsk/d135

Metadata 673066 673066 673066
1461bf5f8 65 29 0 10000000 9326885 9326885 POSB-
/dev/md/rdsk/d133

Metadata 673062 500842 673062

onstat -D
-------------
IBM Informix Dynamic Server Version 11.50.FC6 -- On-Line -- Up 04:52:07 -- 1
278976 Kbytes

Dbspaces
address number flags fchunk nchunks pgsize flags owner
name
14603ae28 4 0x1 7 1 2048 N informi
x chardb
1461b61c0 26 0x68001 61 2 2048 N SB informi
x bts_sbpace
1461b6358 27 0x4a001 62 1 2048 N UB informi
x bts_temp
1461b64f0 28 0x4a001 63 1 2048 N UB informi
x bts_temp1
1461b6688 29 0x4a001 65 1 2048 N UB informi
x bts_temp2

Chunks
address chunk/dbs offset page Rd page Wr pathname
1461b7218 7 4 50 223876 4 /dev/md/rdsk/d114
1461bddb8 61 26 0 65535 7 /dev/md/rdsk/d136
1461bf028 62 27 0 51 52472 /dev/md/rdsk/d137
1461bf218 63 28 0 51 60 /dev/md/rdsk/d138
1461bf408 64 26 0 1 0 /dev/md/rdsk/d135
1461bf5f8 65 29 0 51 60 /dev/md/rdsk/d133

IBM Informix Dynamic Server Version 11.50.FC6 -- On-Line -- Up 05:04:42 -- 1278976 Kbytes
1461b7218 7 4 50 227782 4 /dev/md/rdsk/d114
1461bddb8 61 26 0 65535 7 /dev/md/rdsk/d136
1461bf028 62 27 0 51 52472 /dev/md/rdsk/d137
1461bf218 63 28 0 51 60 /dev/md/rdsk/d138
1461bf408 64 26 0 1 0 /dev/md/rdsk/d135
1461bf5f8 65 29 0 51 60 /dev/md/rdsk/d133

onstat -b
-------------------
IBM Informix Dynamic Server Version 11.50.FC6 -- On-Line -- Up 04:54:13 -- 1278976 Kbytes

Buffers
address userthread flgs pagenum memaddr nslots pgflgs xflgs owner waitlist

Buffer pool page size: 2048
1138817e8 0 80807 62:4734256 1407e0000 2 81 0 0 0
45977 modified, 400000 total, 524288 hash buckets, 2048 buffer size

IBM Informix Dynamic Server Version 11.50.FC6 -- On-Line -- Up 05:03:53 -- 1278976 Kbytes

Buffers
address userthread flgs pagenum memaddr nslots pgflgs xflgs owner waitlist

Buffer pool page size: 2048
47677 modified, 400000 total, 524288 hash buckets, 2048 buffer size

onstat -P
------------
IBM Informix Dynamic Server Version 11.50.FC6 -- On-Line -- Up 04:54:42 -- 1278976 Kbytes

Buffer pool page size: 2048
partnum total btree data other dirty
0 63965 0 29609 34356 29584
1048577 77 0 75 2 8
[snip]
27262981 53176 0 53162 14 0
27262982 1 0 1 0 0
27262983 12326 0 12322 4 0
28311553 6 0 5 1 0
28311554 2 0 1 1 1
28311555 2 0 1 1 0
28311556 20 0 13 7 14
28311557 44947 0 44910 37 16402
[snip]
Totals: 400000 309 365077 34614 46071

Percentages:
Data 91.27
Btree 0.08
Other 8.65

onstat -p
-----------
IBM Informix Dynamic Server Version 11.50.FC6 -- On-Line -- Up 04:55:33 -- 1278976 Kbytes

Profile
dskreads pagreads bufreads %cached dskwrits pagwrits bufwrits %cached
291374 291467 412247733 99.93 52849 53094 173829735 99.97
isamtot open start read write rewrite delete commit rollbk
560480642 16169942 1842 560632 748 59 519 109 0
gp_read gp_write gp_rewrt gp_del gp_alloc gp_free gp_curs
227801080 100794 79180097 100714 3 0 13
ovlock ovuserthread ovbuff usercpu syscpu numckpts flushes
0 0 0 17688.58 23.60 4 3
bufwaits lokwaits lockreqs deadlks dltouts ckpwaits compress seqscans
2430 0 277172445 0 0 1 43 381
ixda-RA idx-RA da-RA RA-pgsused lchwaits
96 15 225454 225509 66

onstat -g mgm
---------------
IBM Informix Dynamic Server Version 11.50.FC6 -- On-Line -- Up 04:56:21 -- 1278976 Kbytes

Memory Grant Manager (MGM)
--------------------------

MAX_PDQPRIORITY: 100
DS_MAX_QUERIES: 6
DS_MAX_SCANS: 1048576
DS_NONPDQ_QUERY_MEM: 128 KB
DS_TOTAL_MEMORY: 768 KB

Queries: Active Ready Maximum

1 0 6

Memory: Total Free Quantum
(KB) 768 0 128

Scans: Total Free Quantum

1048576 1048575 1

Load Control: (Memory) (Scans) (Priority) (Max Queries) (Reinit)

Gate 1 Gate 2 Gate 3 Gate 4 Gate 5
(Queue Length) 0 0 0 0 0

Active Queries:
---------------
Session Query Priority Thread Memory Scans Gate

27 146427c08 100 1463cad40 0/96 0/1 -

Ready Queries: None

Free Resource Average # Minimum #
-------------- --------------- ---------
Memory 0.0 +- 0.0 0
Scans 1048575.0 +- 0.0 1048575

Queries Average # Maximum # Total #
-------------- --------------- --------- -------
Active 1.0 +- 0.0 1 1
Ready 0.0 +- 0.0 0 0

Resource/Lock Cycle Prevention count: 0

onstat -g act
---------------
IBM Informix Dynamic Server Version 11.50.FC6 -- On-Line -- Up 04:57:18 -- 1278976 Kbytes

Running threads:
tid tcb rstcb prty status vp-class name
9 145ec1830 0 1 running 1cpu* tlitcppoll
10 145f0f638 0 1 running 6cpu* tlitcppoll
11 145e6a2a8 0 1 running 14shm* sm_poll
57 1463cad40 145366e00 1 running 3bts* sqlexec

onstat -g cpu
--------------
24 kaio 7cpu* 09/05 15:18:54 6.7544 78285 IO Idle
42 kaio 6cpu* 09/05 15:45:16 7.3771 38429 IO Idle
43 kaio 1cpu* 09/05 15:18:54 3.2953 29975 IO Idle
57 sqlexec 3bts* 09/05 15:45:16 18484.4641 20946 running
59 kaio 3bts* 09/05 10:37:09 0.0826 322 IO Idle

onstat -g iov
--------------
IBM Informix Dynamic Server Version 11.50.FC6 -- On-Line -- Up 05:10:31 -- 1278976 Kbytes

AIO I/O vps:
class/vp/id s io/s totalops dskread dskwrite dskcopy wakeups io/wup errors tempops
fifo 12 0 i 0.0 0 0 0 0 1 0.0 0 0
kio -1 0 i 2.1 39194 39083 111 0 78284 0.5 0 0
kio -1 1 i 1.6 29774 26281 3493 0 38440 0.8 0 0
kio -1 2 i 0.8 15017 14978 39 0 29973 0.5 0 0
kio -1 3 i 0.0 160 160 0 0 320 0.5 0 0
msc 11 0 i 0.0 6 0 0 0 7 0.9 0 6
aio 10 0 i 0.0 257 17 2 0 258 1.0 0 0
aio 13 1 i 0.0 0 0 0 0 1 0.0 0 0
pio 9 0 i 0.0 0 0 0 0 1 0.0 0 0
lio 8 0 i 0.0 0 0 0 0 1 0.0 0 0

onstat -g glo
-------------
IBM Informix Dynamic Server Version 11.50.FC6 -- On-Line -- Up 05:14:11 -- 127
8976 Kbytes

MT global info:
sessions threads vps lngspins
1 34 14 0

sched calls thread switches yield 0 yield n yield forever
total: 899787 730236 83892 245297 219342
per sec: 0 0 0 0 0

Virtual processor summary:
class vps usercpu syscpu total
cpu 3 25.00 14.11 39.11
aio 2 0.38 0.87 1.25
shm 1 0.14 0.26 0.40
lio 1 0.16 0.27 0.43
pio 1 0.18 0.35 0.53
adm 1 1.10 1.66 2.76
msc 1 0.00 0.00 0.00
fifo 1 0.19 0.35 0.54
bts 3 18780.10 6.70 18786.80
total 14 18807.25 24.57 18831.82

Individual virtual processors:
vp pid class usercpu syscpu total Thread Eff
1 3391 cpu 5.73 4.27 10.00 10.86 92%
2 3392 adm 1.10 1.66 2.76 0.00 0%
3 3393 bts 18779.43 6.12 18785.55 18797.89 99%
4 3394 bts 0.34 0.28 0.62 0.00 0%
5 3395 bts 0.33 0.30 0.63 0.00 0%
6 3396 cpu 11.99 6.25 18.24 19.59 93%
7 3397 cpu 7.28 3.59 10.87 15.15 71%
8 3398 lio 0.16 0.27 0.43 0.43 100%
9 3399 pio 0.18 0.35 0.53 0.53 100%
10 3400 aio 0.18 0.50 0.68 1.83 37%
11 3401 msc 0.00 0.00 0.00 0.01 0%
12 3402 fifo 0.19 0.35 0.54 0.54 100%
13 3403 aio 0.20 0.37 0.57 0.57 100%
14 3404 shm 0.14 0.26 0.40 NA NA

tot 18807.25 24.57 18831.82

onstat -F
----------
IBM Informix Dynamic Server Version 11.50.FC6 -- On-Line -- Up 05:06:38 -- 1278976 Kbytes

Fg Writes LRU Writes Chunk Writes
0 0 52710

onstat -c
-----------
IBM Informix Dynamic Server Version 11.50.FC6 -- On-Line -- Up 04:58:59 -- 1
278976 Kbytes

PHYSFILE 1000000
PLOG_OVERFLOW_PATH /soft/prodinformix/foghorn/tmp
PHYSBUFF 128
LOGFILES 54
LOGSIZE 25000
DYNAMIC_LOGS 2
LOGBUFF 64
LTXHWM 70
LTXEHWM 80
DBSPACETEMP dbtemp:dbtemp2
#SBSPACETEMP bts_temp:bts_temp1:bts_temp2
SBSPACETEMP bts_temp
SBSPACENAME bts_sbpace
SYSSBSPACENAME
ONDBSPACEDOWN 1
MULTIPROCESSOR 1
VPCLASS cpu,num=3,noage
VP_MEMORY_CACHE_KB 0
SINGLE_CPU_VP 0
VPCLASS aio,num=2,noage
CLEANERS 8
AUTO_AIOVPS 1
DIRECT_IO 0
VPCLASS bts,num=3,noyield
LOCKS 512000
DEF_TABLE_LOCKMODE ROW
RESIDENT 0
SHMBASE 0x10a000000
#SHMBASE 0x10A000000L
SHMVIRTSIZE 256000
SHMADD 64000
EXTSHMADD 4096
SHMTOTAL 0
SHMVIRT_ALLOCSEG 0.000000
SHMNOACCESS formix-1150fc5.tar
CKPTINTVL 10800
AUTO_CKPTS 1
RTO_SERVER_RESTART 0
BLOCKTIMEOUT 3600
TXTIMEOUT 300
DEADLOCK_TIMEOUT 60
HETERO_COMMIT 0
STMT_CACHE 1
STMT_CACHE_HITS 1
STMT_CACHE_SIZE 1024
STMT_CACHE_NOLIMIT 0
STMT_CACHE_NUMPOOL 1
FILLFACTOR 90
MAX_FILL_DATA_PAGES 0
BTSCANNER num=1,threshold=5000,rangesize=-1,alice=6,compression=default
ONLIDX_MAXMEM 5120
MAX_PDQPRIORITY 100
DS_MAX_QUERIES
DS_TOTAL_MEMORY
DS_MAX_SCANS 1048576
DS_NONPDQ_QUERY_MEM 128
DATASKIP OFF
RA_PAGES 64
RA_THRESHOLD 16
BUFFERPOOL size=2K,buffers=400000,lrus=10,lru_min_dirty=80.000000,lru_max_dirty=90.000000
AUTO_LRU_TUNING 1

Messages In This Thread

[ View Thread ] [ Post Response ] [ Return to Index ] [ Read Prev Msg ] [ Read Next Msg ]

IDS Forum is maintained by Administrator with WebBBS 5.12.