IDS Forum

RE: create bts index long transaction

Posted By: Mark Ashworth
Date: Tuesday, 4 January 2011, at 6:48 p.m.

In Response To: RE: create bts index long transaction (Stuart McCann)

Another thing to check is the if SBSPACETEMP configuration on the server.

If neither the SBSPACETEMP onconfig variable is set nor a corresponding
temporary sbspace created, BTS may use large amount of log space while
creating the index. In this scenario, the index is built in the sbspace
specified by the IN (or spaces in FRAGMENT BY clause) and even though the
individual writes are not logged while building the index, there is a
significant amount of smartblob metadata changes that are unavoidably
logged. This is due to the nature of how CLucene creates or modifies the
index using sbpsaces and we see that it will use log space (and free space
in the sbspace where the index is created) much quicker. To avoid this
behaviour, after 11.50.xC4, when the SBSPACETEMP onconfig variable is set
and a corresponding sbspace is created with the -t flag, BTS will take
advantage of it and use that space to build the index. Because it is a
temporary sbspace, there no logging of metadata changes taking place. When
the index is fully formed it is essentially copied to its final sbspace
specified by the IN clause or FRAGMENT BY clause. The last "copy" is logged
so the secondaries receive the resulting index.

The next question I always get, is how big will the index be? That is a
harder question to answer since its full-text index. The index size can
vary depending on many variables in the data, not just the number of bytes.
The variables include the number of words, length of the words, the number
of distinct words and the frequency in the row.

I did a little test with some data set generators I use and I created a
table with a varchar(255) column and populated it with 30000 rows with words
that filled out each row to be longer than 220 chars (18 to 25 words per
row).

Some statistics for this index:

number of rows: 30000
total number of words: 652341
total number of unique words: 4085
total number of characters: 6048723 (single character locale)

This index size is about 12000 Kb (12 Mb)

Before: onstat -l
4ae98e18 5 U---C-L 29 1:45263 5000 169
3.38
4ae98e60 6 U-B---- 24 1:50263 5000 5000
100.00

After: onstat -l
4ae98e18 5 U-B---L 29 1:45263 5000 5000
100.00
4ae98e60 6 U---C-- 30 1:50263 5000 1990
39.80

With a 2K page size it used about 12-15% more log space (as a thumbnail
estimate) than the size of the created index. As for the temp sbspace size,
there should be about 2.1 times more space free than the size of the created
index (again this goes to how a CLucene merges its segments).

To contrast the index size but changing the ratio of unique words to total
number of words, I created an index with these characteristics:

number of rows: 30000
total number of words: 653012
total number of unique: 229673
total number of characters: 6050541

This index size is about 14200 Kb (14.2 Mb)

Here we see that the index is 2.2 Mb larger when this index only contained
1818 more characters.

I hope you find this helpful.

Happy New Year!

-- Mark

Mark Ashworth
IBM Informix Extensibility Architect
Check out my blog:
https://www.ibm.com/developerworks/mydeveloperworks/blogs/markashworth for
more discussions extensibility topics including BTS.

-----Original Message-----
From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Stuart
McCann
Sent: December 22, 2010 5:46 PM
To: ids@iiug.org
Subject: [Bulk] RE: create bts index long transaction [22293]

More than what you have now. Try 2 X current number of logs at the same size

you have now, perhaps more. If you have a DEV system add heaps and monitor
index build with onstat -x to see how many log files get used.

-----Original Message-----
From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of BEVIS
KENNEDY
Sent: Thursday, 23 December 2010 9:33 AM
To: ids@iiug.org
Subject: Re: create bts index long transaction [22292]

arggghhh! You are correct. I should have seen that.
Any recomendations for estimating required log size?

****************************************************************************
***
Forum Note: Use "Reply" to post a response in the discussion forum.

***************************************************************
This message is intended for the addressee named and may contain
confidential
information. If you are not the intended recipient, please delete it and
notify the sender. Views expressed in this message are those of the
individual
sender, and are not necessarily the views of the Land and Property
Management
Authority. This email message has been swept by MIMEsweeper for the presence

of computer viruses.
***************************************************************
Please consider the environment before printing this email.

****************************************************************************
***
Forum Note: Use "Reply" to post a response in the discussion forum.

Messages In This Thread

• RE: create bts index long transaction
  Stuart McCann -- Wednesday, 22 December 2010, at 5:45 p.m.
  • RE: create bts index long transaction
    Mark Ashworth -- Tuesday, 4 January 2011, at 6:48 p.m.

IDS Forum is maintained by Administrator with WebBBS 5.12.