So now that we have set up the BasicText Search Blade, or BTS forshort, the next question is what performance tuning tools areavailable, andhow do we primarily troubleshoot problems?
BTS comes with 4 functions in addition to the bts_contains function.They are:
- bts_tracefile :This functions lets you designate the location and name of you tracefiles.
- bts_tracelevel : This enables tracing at the session level for BTS calls
- bts_index_compact: Think of this function as the Index Cleaner for the btsblade.
- bts_release: Allows you to skip using blademgr to find out what version of theblade you are using.
The biggest performance tuning tool is bts_index_compact. From anadministrative standpoint, we treat the BTS indices the same way wetreat btreeindices, with one exception, we have no automated thread which goes outandclean the BTS indices, for btree indices we have btscanner threads.bts_index_compact is the BTS version of what the btscannerthread does.So from a performance standpoint, you should frequently runbts_index_compactin order to decrease the size, both disk space and number of elements,and thusincrease performance. Please note that you can make thisprocessautomated by creating your index with delete= 'immediate' in youstatement.This will eliminate the need for compacting the index, but will makealldeletes run longer. A better option would be to set up a function usingtheadmin api to periodically run bts_index_compact on your indices, thuscreatinga poor mans btscanner for bts, so to speak.
Another performance tuning tip is to make sure that you have createdyour extspaces on fast disk. If you create an ext space on an NFS mount you arebasically asking for horrible performance. A little bit about extspaces may bein order. External spaces, or extspaces, are exactly as they sound,they arespaces external to the database proper. What this means is that youcannot usenormal onstat's to monitor an extspace.
Troubleshooting BTS Issues:
So now you have run into a problem, maybe something so simple as notremembering where your extspace is actually located, maybe you aregettingbigger problems, in either case, this is when you need to enable BTStracing.This process is actually fairly straight forward, as BTS has provided 2functions to handle this. First you will call bts_tracefile, to setyour tracefile location, then you should call bts_tracelevel which will dumpinformationto the file you specified.
Let's say that you had just forgotten the syntax and location of yourbts. Thequickest way would be to run the following:
execute function bts_tracefile('/tmp/bts.log');
execute function bts_tracelevel(10);
select * from customer where bts_contains(company,'all~');
After that just read the file bts.log
Below is a snippet from the trace file generated from the above query:
Taking a quick look at this you can see what your extspace info is.
database name = stores_demo
owner name = informix
ext space name = bts1
You also know now that your extspace is located in/work1/informix/11.10.FC1/bts_idx/stores_demo/informix
A quick word on extspaces. Extspaces are spaces that are external tothe database;as such there is no onstat that monitors them. If you forget what thename ofyour ext space is or forget its location, the above method isessentially youronly option by which to find them.One other important note about extspaces, if you want to back them up, there is only one support method, and is as follows:
onmode -c block
External backup of external space
External backup of the instance
onmode -c unblock
What the above means is that rather than going through the above to safely backup your BTS indices you should just plan on rebuilding your BTS indices in the case of a restore.
One other consideration when troubleshooting BTS issues, BTS does notallow formultiple bts_contains in the predicate portion of an SQL Statement.
select * from customer where bts_contains(company, "fred") orbts_contains(company, 'all')
would fail with the error:
(BTS22) - bts_error bts_contains requires an index on thesearch column
This concludes my series on the bts blade.