create bts index long transaction
Creating a BTS (full-text) index on a CLOB column on IDS 11.50.FC7 aborted with a long-transaction error, while an equivalent index on a VARCHAR column worked. Stuart McCann identified the index build spanning past LTXHWM and suggested adding logical logs (monitoring with onstat -x). Mark Ashworth explained that without SBSPACETEMP set (plus a temp sbspace created with -t), BTS logs lots of smartblob metadata; using a temp sbspace avoids this, and he gave rough sizing figures. After adding 2 GB of logs the poster still hit the error; further suggestions were altering the table to raw during the build or disabling smart blob logging for the sbspace. No confirmed resolution is recorded.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
I am trying to setup and use bts on our dev sds primary psdev1. I know I could
turn logging off but that would require taking the secondary offline and the
solution I find must work when this is moved to production. There are 29341
rows in dev; about 3 times that number in prod.
I have been able to successfully create and use an index on a varchar(255)
column but when I index a clob column I'm getting a 'Aborting Long
Transaction' error.
btssbspace is a 5GB smart blob space and is pretty much unused; I tried this a
few times and the most the btsspace usage got up to was 22%.
Any ideas on how I can do this without turning logging off?
Linux psdb04sat 2.6.16.60-0.42.9-smp #1 SMP Thu Jan 28 23:39:37 UTC 2010
x86_64 x86_64 x86_64 GNU/Linux
IBM Informix Dynamic Server Version 11.50.FC7W1XA
LTXEHWM=80,LTXHWM=70
create table 'informix'.ucjis_request_log (
request_id SERIAL8 not null,
received_time DATETIME YEAR TO SECOND,
user_agency CHAR(6),
user_id CHAR(8),
user_full_name CHAR(60),
requesting_ori CHAR(9),
requesting_host_name CHAR(8),
requesting_host_ip_addresss CHAR(15),
requesting_host_message_id VARCHAR(255),
service CHAR(5),
service_operation CHAR(20),
request_message clob,
request_message_summary VARCHAR(255)
)
put request_message in (dev_sblob)
extent size 2048 next size 20480
lock mode row;
create index 'informix'.idx_reqrecv on 'informix'.ucjis_request_log
(
received_time
);
create index 'informix'.ix_auditlogs_ucjisreqmsgsum on
'informix'.ucjis_request_log
(
request_message_summary bts_varchar_ops
)
in btssbspace
USING BTS;
alter table 'informix'.ucjis_request_log add constraint primary key
(request_id)
constraint pk_ucjis_request;
This is the create index that fails:
CREATE INDEX ix_auditlogs_ucjisreqmsg ON ucjis_request_log
(request_message bts_clob_ops)
USING bts IN btssbspace;
Sounds like your index build is spanning more than 80% of your logical log
files.
How many logical log files do you have and how large are they?
If you increase the number of logs you should avoid the long transaction.
Stuart McCann
-----Original Message-----
From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of BEVIS
KENNEDY
Sent: Thursday, 23 December 2010 8:30 AM
To: ids@iiug.org
Subject: create bts index long transaction [22289]
I am trying to setup and use bts on our dev sds primary psdev1. I know I could
turn logging off but that would require taking the secondary offline and the
solution I find must work when this is moved to production. There are 29341
rows in dev; about 3 times that number in prod.
I have been able to successfully create and use an index on a varchar(255)
column but when I index a clob column I'm getting a 'Aborting Long
Transaction' error.
btssbspace is a 5GB smart blob space and is pretty much unused; I tried this a
few times and the most the btsspace usage got up to was 22%.
Any ideas on how I can do this without turning logging off?
Linux psdb04sat 2.6.16.60-0.42.9-smp #1 SMP Thu Jan 28 23:39:37 UTC 2010
x86_64 x86_64 x86_64 GNU/Linux
IBM Informix Dynamic Server Version 11.50.FC7W1XA
LTXEHWM=80,LTXHWM=70
create table 'informix'.ucjis_request_log (
request_id SERIAL8 not null,
received_time DATETIME YEAR TO SECOND,
user_agency CHAR(6),
user_id CHAR(8),
user_full_name CHAR(60),
requesting_ori CHAR(9),
requesting_host_name CHAR(8),
requesting_host_ip_addresss CHAR(15),
requesting_host_message_id VARCHAR(255),
service CHAR(5),
service_operation CHAR(20),
request_message clob,
request_message_summary VARCHAR(255)
)
put request_message in (dev_sblob)
extent size 2048 next size 20480
lock mode row;
create index 'informix'.idx_reqrecv on 'informix'.ucjis_request_log
(
received_time
);
create index 'informix'.ix_auditlogs_ucjisreqmsgsum on
'informix'.ucjis_request_log
(
request_message_summary bts_varchar_ops
)
in btssbspace
USING BTS;
alter table 'informix'.ucjis_request_log add constraint primary key
(request_id)
constraint pk_ucjis_request;
This is the create index that fails:
CREATE INDEX ix_auditlogs_ucjisreqmsg ON ucjis_request_log
(request_message bts_clob_ops)
USING bts IN btssbspace;
*******************************************************************************
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.
arggghhh! You are correct. I should have seen that. Any recomendations for estimating required log size?
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.
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.
I added 2 Gig of log space giving me a total of 6.4 Gig; and still I get a long transaction. Is there some kind of rule of thumb for calculating how much log space it takes to create a bts index? And will this index be kept up to date as table data changes?
Have you tried altering the table to raw for the duration of the index build? Thanx, Dan -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of BEVIS KENNEDY Sent: Monday, January 10, 2011 4:25 PM To: ids@iiug.org Subject: Re: RE: create bts index long transaction [22384] I added 2 Gig of log space giving me a total of 6.4 Gig; and still I get a long transaction. Is there some kind of rule of thumb for calculating how much log space it takes to create a bts index? And will this index be kept up to date as table data changes? ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.
You could also turn off smart blob logging for the sbspace where the index is being created.. -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Dan Mueller Sent: Tuesday, 11 January 2011 11:06 PM To: ids@iiug.org Subject: RE: create bts index long transaction [22387] Have you tried altering the table to raw for the duration of the index build? Thanx, Dan -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of BEVIS KENNEDY Sent: Monday, January 10, 2011 4:25 PM To: ids@iiug.org Subject: Re: RE: create bts index long transaction [22384] I added 2 Gig of log space giving me a total of 6.4 Gig; and still I get a long transaction. Is there some kind of rule of thumb for calculating how much log space it takes to create a bts index? And will this index be kept up to date as table data changes? ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum. ******************************************************************************* 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.