1 of 1 people found this helpful
I cannot help you, but this type of problem is common. What I can do is telling you why this takes a lot of time.
I am in charge of a CRM-system based om Remedy ARS. We use version 9.1. sp1
15 million records in an Oracle db. Dual Win app servers. Dual win web-servers. Big-ip load balancing.
Average a few hundre bytes records. Total size of arsystem.dbf is 1.9 GB. That includes ar system base, but not temp, undo, users and so one tablespaces.
To reindex 1.9 GB (edit: TB that is) of data in a db running on flash disks, 22 GB SGA, 4 dedicated cpu's and 10 Gbit/s network, it takes about a week.
Our super fast db has logged 60 billions disk reads 35 billions buffer gets and about a billion direct writes. Most of the traffic is generated by the indexer. Online production with with 6-10.000 new records each day.
Here is a snapshot for one view accessing one table.
FTS select some rows, but fetches all 15 million rows each and evry select, then sorting, then indexing, then new select again and again, then a new table. Then a new day, a new week an in your case a new month, hurting online traffic and giving the user no search hits since index is not up to date. Bad for production, bad for business.
I reindexed my Outlook mailbox yesterday. 2 GB PST-file. Done in less than an hour on a regular win10 PC.
Remedy ARS uses about 200 hours for indexing merely 1.9 GB of data on a superfast production system using 100 billions of disk reads/buffer gets.
You say 9.1....which SP? I ask because there were some major breakthroughs in FTS indexing speed with SP2...don't know if you are using it.
Additionally....you say your production DB is 2GB?....not 2TB?
2 of 2 people found this helpful
I believe the best way to figure out the indexing speed would be to capture the arftindx.log file and analysed it for 1/2 hour or 1 hour time and then figure out how many records indexes are created successfully. This might vary based on the load on the system but atleast will give some rough estimation of the time it'll take for indexing.
Yes, its TB not GB.
Edited my first post and added sp1.
We are about to upgrade to SP4. So if sp2 has some major improvements in indexing, I will be very happy.
1 of 1 people found this helpful
Ok....a 2TB db makes more sense than a 2GB one ....so you are just 9.1.00 at the moment? You will be pleasantly surprised by the increase with SP2 and beyond. I'm curious if you would be willing to come back and provide a 'difference' report on the speed of a reindex after the upgrade.
A good advice. Thanks. The indexer logs a lot and analyzing will take some time. I will try15 minutes of logging and hope that is enough for a rough estimate.
I am reindexing for the second time for the moment. Had a problem with the stp-file. Easter holiday starts tomorrow at noon and last until next tuesday. Hopefully we are finished re indexing by then.
Well, I can do that, but only if the improvements are really major
I guess I can do a reindexing starting on a friday afternoon. Production starts monday morning at 7 am.
But first: Upgrading sometime this spring. We are really past due.
1 of 1 people found this helpful
A brief update:
The indexer has been running for 10 days now. We are about half way trough, using the size of the collection directory as a guideline. That is 240 hours. Most days where Easter holiday and no user traffic. Seems that when using real Norwegian words in the stp-list, the indexer collection directory got smaller but the indexing process itself got even slower than ever.
Tens of billions disk read and buffer gets. A disaster really.
The solution seems obvious: Stop using FTS.
To accomplish case insensitivity for text searches:
set nls_sort = 'BINARY_CI'; (CI = Case Insensitive)
set nls_comp = 'LINGUISTIC';
Can be done for session, database and/or instance.
Recreate all indexes to use binary_ci option. The optimizer will not use the old db table indexes when nls_sort is set to binary_ci, causing slow searches.
Create index with 'NLS_SORT = BINARY_CI' option
A preliminary test shows about the same speed for text searches as before.
Advantages: No FTS agent, no FTS setup in Remedy, no FTS license and most of all no indexer causing spikes in db all day long. (a problem even with a completed index rebuild done)
Upgrading to 9.1 SP4 will be performed anyway.
At 11.43 today we disabled the FTS agent and the FTS option.
We got tired of waiting and our customers agreed to have case sensitive searches until we reconfigure the database to use the Case Insensitive option.
The customers are happy with the performance benefit and so are we in operations.
The difference speaks for itself:
I would suggest thinking twice before making your Oracle DB case insensitive Tom Tellefsen. We did that last year with an ITSM 9.1.02 installation and the general DB performance was unacceptable. Make sure you do thorough performance testing on a case insensitive DB before taking it into production.
BTW: Thanks for posting that last performance graph - it really does highlight a dramatic change! :-)
Thanks for the advice.
The problem with Remedy text search is that they use the LIKE operator.
A typical search generates the following two sql's:
(SELECT AR_SQL_Alias$1.*, rownum as AR_RowNumber_Alias$1
(SELECT T229.C700030037, T229.C700001000, T229.C700001001, T229.C700030056, T229.C3, T229.C700030006, T229.C1
WHERE (T229.C700030028 LIKE 'apekatt' ) ORDER BY T229.C1 ASC) AR_SQL_Alias$1)
WHERE ((AR_RowNumber_Alias$1 > :2 ) AND (AR_RowNumber_Alias$1 < :3 ));
A limited table scan, but still very heavy.
Then another, even heavier one: Do not know why this count(*) is necessary. The other search gives the same count, so a new select(count) seems like a waste of time.
WHERE (T229.C700030028 LIKE 'apekatt');
The C700030028 is a clob and a full table scan generates lost of disk reads.
An alternative that has come up, is to make a special search form, use oracle text indexes and tailor our own searches using the CONTAINS operator.
Searches are fast, and can be even faster when limited by date range, type of customer requests and so one.
create index idx_sporsmal on T229("C700030028") indextype is ctxsys.context;
select T229.C700030028 from T229 WHERE CONTAINS("C700030028", 'apekatt')>0;
This select is case insensitive by default. We got both apekatt and Apekatt.
The index is quite large and it needs to be maintained for every insert to the table, but the search takes 4 seconds where Remedy takes 57 seconds.
15 million clobs in table. Average 4k size.
Further testing will be required before we make a final decision.
very interesting thread.
how did it ended? Was oracle insensitive set.
We had done it (AR Remedy 8.1), but unfortunately I wasn't aware of the performance before it.
When searching in SmartIT 1.6 global search performance is awful (no sure if there is any other underlying issue, it is a dev environment) when searching in midtier form (directly by CI Name* in asset form) the respond is fast (no global search involved there I guess)
Judging by some validation, there is no FTS implemented for asset forms. in our environment, therefore I assume the search is always going towards the database rather than FTS index.