 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
SQL Server Forum / Other Technologies / Full-Text Search / July 2004Need help with full-text performance for large database
We are having significant performance problems with full-text indexing on one column of a large database. System configuration: Windows 2000 Advanced Server with all service packs as of 6/10/04. We are using SQL 7 with all service packs as of 6/10/04. The CPU is a 2 GHz Athlon. The system has 1.5 G of RAM. We are using two 7200 RPM IDE disks in a Raid 0 configuration. All of the SQL databases and FT catalogs are on these disks. Database: One column is indexed. The items in the column tend to be about 60 characters long with approximately 10 words. The indexed table has a change-date-time column. The table currently has about 2,600,000 rows, but about 200,000 rows are added each day. We expect the table to grow to about 6,000,000 rows and then stabilize with about 200k rows added & deleted daily. Searches are being directed to the database through our application which fields Internet search requests from tens of thousands of clients around the world. Fortunately, clients are not making continuous requests, so we average about 20 searches per minute (we would probably have a higher search rate if we could respond faster). Another program running on the same computer as the SQL server is adding records to the database pretty much non-stop during the day. On average, about 200k new rows are added per day. This program is also doing a moderate amount of I/O to a series of sequential files that hold the messages being added. A typical search will find between 10 and 500 matches whose results must be returned to the client. The application that is fielding the search requests and sending the replies is running on a separate computer. It is multi-threaded and has no problems keeping up with the incoming requests and outgoing results -- the bootleneck is the actual search. We are struggling to keep up with the searches. The searches commonly involve a number of words with the AND operator and sometimes also OR. While some simple searches are completed in a few seconds, the typical time for a search is in the range of 6 seconds. Some searches timeout after 40 seconds which is what we have set as the command timeout on the SQL query. The application that handles the requests is multi-threaded and can present multiple searches to the SQL server simultaneously. We often see 5 threads presenting simultaneous search requests. An incremental indexing run last night took 7 hours to handle the new 200k records added yesterday. We have found that if we attempt to do the incremental index during the busy part of the day, it totally clobbers searches, and we end up with many searches timing out after 40 seconds. So we are doing one incremental index per day which starts at 3 AM. Would change tracking be better or worse than incremental indexing? The bottleneck seems to be disk I/O on the SQL server. The CPU is averaging about 20% with short bursts to 100%. What can we do? We could upgrade the CPU to a 3 GHz, but with the average CPU utilization at 20% it doesn't look like that would help. We are running Raid 0 on our two disks, so it is not clear what to do to boost their performance significantly. Perhaps SQL 7 full-text indexing just isn't up to this job and we should look for some better indexing software. I am open to any suggestions. Thank you. Phil Sherrod Phil, First of all, thanks for the detailed hardware and software configurations as this type of info is always helpful ! While SQL Server 7.0 is certainly up to handling this amount of data volumn (2.6+ million rows) and growing at 200K rate per day, I'm sad to say that SQL Server 7.0's Full-text Search (FTS) component is not really up to the performance levels (FT Indexing and FT Searching) that you are expecting. You are most likely hitting one or both of these bugs: 230036 BUG: Heavy Full Text Query Activity Results in Unexpected Timeout Errors http://support.microsoft.com/default.aspx?scid=KB;en-us;Q230036 230103 BUG: Cannot Have More than Eight Full Text Joins and Operations http://support.microsoft.com/default.aspx?scid=KB;en-us;Q230103 I'm sure you have considered upgrading to SQL Server 2000 for other reasons, and I'd highly recommend that you consider upgrading to SQL Server 2000 in order to get access to it's new FTS features and performance improvements. Specificly, "Change Tracking" with "Upgrade Index in Background" that is not available with SQL Server 7.0. Upgrading your hardware with SQL Server 7.0 installed will not help SQL Server 7.0 FTS performance. I'd also recommend that you evaluate & test SQL Server 2000 FTS with your table sized up to 5 million rows prior implementing it in production to ensure that SQL Server 2000 FTS can meet your future FT Search requriments. Regards, John > We are having significant performance problems with full-text indexing on one > column of a large database. [quoted text clipped - 66 lines] > > Phil Sherrod > First of all, thanks for the detailed hardware and software configurations > as this type of info is always helpful ! > While SQL Server 7.0 is certainly up to handling this amount of data volume > (2.6+ million rows) and growing at 200K rate per day, I'm sad to say that > SQL Server 7.0's Full-text Search (FTS) component is not really up to the > performance levels (FT Indexing and FT Searching) that you are expecting. John, Thank you for your reply and the information. I am embarrased to report that I was incorrect when I said we are using SQL 7. In fact, we are using SQL 2000. I just checked the About box on our SQL server and it reports: "Version 8.00.760". I am the primary programmer developing the applications that use the SQL server, but I did not install it. I apologize for the mistake, I should have checked more carefully before posting my original message. Now that we have established that we are running SQL version 8, do you have any recommendations about what we should do to improve our search performance? Thank you again for your reply. Phil Sherrod > First of all, thanks for the detailed hardware and software configurations > as this type of info is always helpful ! > While SQL Server 7.0 is certainly up to handling this amount of data volume > (2.6+ million rows) and growing at 200K rate per day, I'm sad to say that > SQL Server 7.0's Full-text Search (FTS) component is not really up to the > performance levels (FT Indexing and FT Searching) that you are expecting. John, Thank you for your reply and the information. I am embarrassed to report that I was incorrect when I said we are using SQL 7. In fact, we are using SQL 2000. I just checked the About box on our SQL server and it reports: "Version 8.00.760". I am the primary programmer developing the applications that use the SQL server, but I did not install it. I apologize for the mistake, I should have checked more carefully before posting my original message. Now that we have established that we are running SQL version 8, do you have any recommendations about what we should do to improve our search performance? Thank you again for your reply. Phil Sherrod You're welcome, Phil, Ok... So, you're using SQL Server 2000 SP3. At 2.6 million to 6 million rows, SQL Server 2000 FT Indexing and FT Search requires a bit of tuning. I'd recommend that you first review the SQL 2000 BOL title "Full-text Search Recommendations" and then move on to the "Full-Text Search Deployment" white paper (I was a 'contributor' ;-) at http://support.microsoft.com/default.aspx?scid=/support/sql/content/2000papers/f ts_white%20paper.asp. For faster FT Search you should use the new "Top_N_Rank" parameter with either CONTAINSTABLE or FREETEXTABLE as doc'ed in KB article 240833 (Q240833) "FIX: Full-Text Search Performance Improved via Support for TOP" at: http://support.microsoft.com//default.aspx?scid=kb;EN-US;240833. Additionally, you should set the MSSearch resource usage level to 5 via sp_fulltext_service 'resource_usage', 5 -- as this will allow more concurrent searches. If you have not already implemented "Change Tracking" with "Update Index in Background", I'd recommend this as well before your table grows too large. Note, if the FT Catalog is already populated enabling CT with UIiB, it will automatically start an Incremental Population to sync up the FT Catalog. If your FT Catalog is not on its own disk drive, you can move it to another disk array via the procedures in KB article 240867 (Q240867) "INF: How to Move, Copy, and Backup Full-Text Catalog Folders and Files" at: http://support.microsoft.com/default.aspx?scid=kb;EN-US;240867. Note, ensure that this drive is not a RAID5 and is separate from the pagefile.sys file drives. You should also limit SQL Server's memory to a fixed amount to ensure that at least 512Mb of your 1.5Gb of RAM is always available to the MSSearch service. Depending upon how much and how often you 200K rows are updated/inserted, you may need to turn off UIiB and use a scheduled Incremental Population unless the updates/inserts are done in small batches. Relative to your FTS queries, you should switch to using "Top_N_Rank" parameter with either CONTAINSTABLE or FREETEXTABLE and only use one FTS predicate (CONTAINS* or FREETEXT*) per query as each predicate is a "round-trip" to the FT Catalog. Hope this helps, John > > First of all, thanks for the detailed hardware and software configurations > > as this type of info is always helpful ! [quoted text clipped - 20 lines] > > Phil Sherrod John, Than you for your additional suggestions. I have studied them and discussed them with others in my company. Following your suggestion, I changed the CONTAINS clause to CONTAINSTABLE with the Top_N_Rank option. I believe this has had a positive impact on the search times. I am now observing the majority of the searches completing within 4 seconds with a few taking 15 seconds and a rare one taking 30 seconds. I haven't seen any searches hit the 40 second timeout. However, they always run slower when an incremental update is going, so we'll have to reserve final judgment until we do the update in the morning. Regarding the incremental indexing update, it ran 8 hours last night, and it appears to be getting slower each day as the database grows. We are up to just over 3 million rows now. I read another message in this group by someone who reported that an incremental update on a large database took 24 hours when only 30 rows were altered. What the heck does an incremental update do? It must be doing more than updating the index for the rows that were changed. What are the pros and cons of switching to change tracking? I will try to be succinct, but here are a few questions that I would appreciate your guidance on: 1. When I created the primary table, I specified that the primary key as NON-clustered because it sounded like that would work better with a table undergoing 200+ K additions and deletions per day. Was that a good idea or a mistake? 2. The table that is updated the most has two numeric columns, a date-time column, two varchar columns and one Text column that typically has only 200 characters but which can grow to 20,000 characters. It there any horrendous penalty for having a Text type column like this? 3. We are considering purchasing an Adaptec or Promise caching RAID controller with either 128 or 256 MB of onboard caching RAM. Have you had an experience using this type of controller, and do you think it would provide a significant performance boost from the simple RAID controller we are currently using? One of our primary concerns at the moment is the speed of inserting new records into the database. When the database was small (a couple of hundred thousand rows), we were able to insert about 11,000 rows per minute (5.5 milliseconds per insertion). But now that the database has grown to 3 million rows, the insertion rate has dropped to 1,000 rows per minute (60 milliseconds per insertion) or about 17 rows/second. That is getting painfully slow, and it is getting worse each day. This insertion time is just the SQL insertion time because it does not include the time to do the incremental index build. Is this dramatic drop in performance as the database grows something that you would expect? I have a hard time believing that the SQL server running on a RAID 0 disk array can't insert more than 17 rows per second. Thank you for your help and guidance.  SignaturePhil Sherrod (phil.sherrod 'at' sandh.com) http://www.dtreg.com (decision tree modeling) http://www.nlreg.com (nonlinear regression) You get the fastest response from a SQL FTS catalog if you don't query it:) On some client sites I have worked on we have been able to get radical improvement in search speed and scalability by caching the frequently searched on items. What we do is pregenerate the search results pages as static html pages, and when a user searches on one of the cached search term, they are redirected to the static html pages using an ISAPI extension. We regenerate the static search results pages each day. If many of your users are searching on the same search terms you get radical search performance increases. I posted the code for this in this newsgroup some time ago. Search on redirect.txt SignatureLooking for a SQL Server replication book? http://www.nwsu.com/0974973602.html > John, > [quoted text clipped - 52 lines] > > Thank you for your help and guidance. On 15-Jun-2004, "=?Utf-8?B?SGlsYXJ5IENvdHRlcg==?=" <hilaryk@att.net> wrote: > You get the fastest response from a SQL FTS catalog if you don't query > it:) I can confirm that observation. > What we do is pregenerate the search results pages as static html pages, > and when a user searches on one of the cached search term, they are [quoted text clipped - 3 lines] > If many of your users are searching on the same search terms you get > radical search performance increases. In our situation, we have 3.2 million indexed rows, and it is growing at 200,000+ rows/day. The searches vary wildly, and there is no way to identify frequent searches. Is the Microsoft SQL FTS actually being used in large production environments? I'm talking about environments that have 5+ million indexed rows and get 50,000+ searches per day. If so, what hardware configuration is used? Is there better search software that we should be considering? Our system is a 2 GHz Athlon with 1.5 GB of RAM and two 7200 RPM disks in a RAID 0 array. The CPU usage is usually less than 20%, so we don't think we would benefit from upgrading it. The disk activity is very heavy. We are considering installing a caching RAID controller, but we don't know if that will significantly improve the performance. Our nightly "incremental" index updates are currently taking 8 hours, and they get slower each day as the database grows. SignaturePhil Sherrod (phil.sherrod 'at' sandh.com) http://www.dtreg.com (decision tree modeling) http://www.nlreg.com (nonlinear regression) Nexis Lexis uses SQL FTS, but not as their main search engine. I don't have any details about what they are using it for, hardware, or number of queries. Their used to be a case study for this on microsoft.com. I can't find it now. Going to a seperate controller is a good idea. If you have money invest first in the disk subsystem, placing the catalog on its own raid and controller. There is a sweet spot for indexing and searching with a quad or 8 way. SignatureLooking for a SQL Server replication book? http://www.nwsu.com/0974973602.html > On 15-Jun-2004, "=?Utf-8?B?SGlsYXJ5IENvdHRlcg==?=" <hilaryk@att.net> wrote: > [quoted text clipped - 29 lines] > updates are currently taking 8 hours, and they get slower each day as the > database grows. John, I thought of one more question: Do you know if there are significant performance improvements in full-text indexing in the Yukon SQL server that is being field tested? SignaturePhil Sherrod (phil.sherrod 'at' sandh.com) http://www.dtreg.com (decision tree modeling) http://www.nlreg.com (nonlinear regression) Phil, Sorry for the late reply... I've started a new job and it's keeping me somewhat busy... :-) Yes, Yukon or now officially known as SQL Server 2005 Full Text Search will be significantly faster in FT Indexing (order of magnitude improvement) and on the FT Search query side, between 30 & 50% improvement in query times relative to SQL Server 2000. Regards, John > John, > > I thought of one more question: > > Do you know if there are significant performance improvements in full-text > indexing in the Yukon SQL server that is being field tested? > Sorry for the late reply... I've started a new job and it's keeping me > somewhat busy... :-) I appreciate the help you have provided. I hope your new job goes well. > Yes, Yukon or now officially known as SQL Server 2005 Full Text Search will > be significantly faster in FT Indexing (order of magnitude improvement) and > on the FT Search query side, between 30 & 50% improvement in query times > relative to SQL Server 2000. That is good to hear. We have improved our situation by installing a new Adaptec caching RAID controller and 10,000 RPM disk drives. This has greatly improved the performance of the full-text indexing. I am looking forward to Yukon. After we installed the new disk system, we reinitialized our database and started over from scratch. We engaged Change Tracking with Background Updates. That worked well for a while, but now we are having a problem, and I urgently need some assistance. I posted this as a separate message, but I would appreciate it if you would consider our situation and see if you can help: We're having a problem with Change Tracking sitting in the "Change tracking" state but not doing anything. We are indexing a single column in a table that has about 6.5 million rows. Change tracking is engaged along with "Update index in background". We have an application that inserts several hundred thousand rows over a period of several hours. During that time, the status of the index catalog is "Paused". When the insertions end, the status changes to "Change tracking". Once our insertion application ends, we can see several hundred thousand row entries in the sysfulltextnotify table. Over a period of an hour or so, the rows in the sysfulltextnotify table are removed by change tracking processing. The status of the catalog index during this period remains "Change tracking", but the item count does not change. I believe this is normal operation. In the past, we have seen change tracking begin to insert items into the index catalog as soon as the sysfulltextnotify table is emptied. The problem we're having is that recently it has stopped adding entries to the catalog even though no insertions are being done and the sysfulltextnotify table has been emptied. I watched it empty the sysfulltextnotify table several hours ago, and I have been refreshing the catalog properties hoping to see the item count increase. But it is just sitting there -- what did it do with the entries it got out of sysfulltextnotify several hours ago? There is a steady stream of searches being done against the index; the gap between searches is never more than about a minute. The searches are running properly. What can cause change tracking to successfully engage and empty the sysfulltextnotify table but then not insert those records into the catalog? Thank you, Phil Sherrod You're welcome, Phil, Yep, so far so good! Yes, SQL Server 2000 FTS performance is mostly disk i/o bound as you have discovered. Relative to your app that "inserts several hundred thousand rows over a period of several hours" with Change tracking and Update Index in background enabled and the intentionally un-documented system table sysfulltextnotify, what you are seeing is the normal operational behavior. Also, the item count does not change is normal as well at least until a master merge is done as is normal at the end of a Full or Incremental Population or at Midnight, the latter is controllable via a Registry key. Relative to the "stopped adding entries to the catalog even though no insertions are being done and the sysfulltextnotify table has been emptied", are there any "Microsoft Search" or MssCi source event errors or warnings in the server's Application Event log? The MSSearch service *might* be paused or too many rows have been passed to it via the sysfulltextnotify table for it to handle so, it goes into a wait state or paused state. Rows that are posted to the un-documented sysfulltextnotify table *should* be posted to the MSSearch controlled FT Catalog within one second, but I've tested this with one insert and waitfor statements to select the row via a contains query and the wait could be between 5 to 7 seconds, even for a one row insert! When to use or not use "Update Index in Background" is somewhat documented in the SQL 2000 BOL title "Maintaining Full-Text Indexes" and with several hound thousands of updates/inserts, I'd recommend that you turn off "Update Index in Background" and do your inserts/updates and then run an Full or Incremental population or as this BOL title states: "Use a full rebuild when a large percentage of records change or are added at once. If a large percentage of records change over an extended period of time, consider using change tracking with scheduled or background update index. Use an incremental rebuild when a large number, but not a large percentage, of documents change at one time. If a large number of records change over an extended period of time, consider using change tracking with scheduled or background update index." Hope this helps! Regards, John > > Sorry for the late reply... I've started a new job and it's keeping me > > somewhat busy... :-) [quoted text clipped - 52 lines] > > Phil Sherrod John, Thank you for your continued help; we are really struggling here, and we have limited resources to figure out what the SQL server is doing. To say our experience has been frustrating and disappointing would be a major understatement. Last night the SQL server stopped responding to Enterprise Manger. Curiously, searches were working fine, and our applications that insert new rows were running without problems. But all attempts to connect to the SQL server using EM failed (EM hung). I tried connecting EM from several computers -- all without success. So after waiting a number of hours in hopes that the condition would clear, I tried stopping the SQL server and restarting it; that did not solve the problem. So, in desperation, I stopped the SQL server and did an orderly shutdown and restart of Windows 2000. On startup, the FT catalog reported that it was "Recovering". This lasted several hours during which time searches were blocked. When the recovery phase finished, the catalog which previously had roughly 7 million items, now had 109 rows. Yes -- a total of 109 rows. All searches came up with no hits. So we were back to ground zero. Fortunately (I guess) the table being indexed was not damaged; it currently has about 9.5 million rows. So early this morning I started a full population. The full population has gotten up to 1.8 million rows; meanwhile our customers around the world are out of luck. I would have to say in honesty that I just don't think SQL server with FTI is ready for heavy, non-stop production use. Unfortunately, it would be difficult for us to revert to our older home-grown (JET based) FTI system which was limited to about 2 million rows but which ran flawlessly for 5 years. > Relative to your app that "inserts several hundred thousand rows over a > period of several hours" with Change tracking and Update Index in background [quoted text clipped - 3 lines] > done as is normal at the end of a Full or Incremental Population or at > Midnight, the latter is controllable via a Registry key. I waited 16 hours after the sysfulltextnotify table had been emptied, and there was no change to the item count in the FT catalog. Midnight had been crossed. What is the registry key you mentioned? > The MSSearch service *might* be paused > or too many rows have been passed to it via the sysfulltextnotify table for > it to handle so, it goes into a wait state or paused state. Is there some limit on the number of rows that change tracking can handle ("too many rows")?? > Rows that are posted to the un-documented sysfulltextnotify table *should* > be posted to the MSSearch controlled FT Catalog within one second, but I've [quoted text clipped - 5 lines] > off "Update Index in Background" and do your inserts/updates and then run an > Full or Incremental population or as this BOL title states: We turned on change tracking at the suggestion of you or someone else in this group who said that Microsoft recommended change tracking with background updates because the performance of Full/Incremental population was so slow. How long is it going to take to do an incremental update of a catalog with 10 million items? Do you really recommend incremental updates over change tracking for this situation? > "Use a full rebuild when a large percentage of records change or are added > at once. If a large percentage of records change over an extended period of [quoted text clipped - 5 lines] > extended period of time, consider using change tracking with scheduled or > background update index." The table has about 10 million rows and about 300,000 are being inserted and deleted per day. So that is a daily change percentage of about 3% (or 6% if you count the deleted rows separately from additions). What is your suggestion for that situation? Are you available for private consultation? Thank you, Phil Sherrod You're welcome, Phil, I'll help all I can as I use to do SQL server down support as well while I was at MSFT too! When SQL Server stops responding, the best thing to do is to review the current SQL Server errorlog using Notepad and look for any databases marked suspect or going though a long recovery (for some reason). If this is/was the case, you should never use the EM to try and connect as it will always hang due to it pooling all databases and when it gets to the any databases marked suspect or going though a long recovery, it too will hang. Instead you should use either the QA or osql to connect to master and then do a select * from sysdatabases and review the status column. As for the FT catalog reported that it was "Recovering", most likely there will also be some error or warning in the Application Event log with the source event of "Microsoft Search" or MssCi and you should filter your Application event log based upon each of these events and correlate any errors with the start of the SQL Server hang. Most likely the FT Catalog went corrupt (there will be a MssCi event for this and a stack trace in the event details) and if so, the MSSearch service will on its own try and recover the data. Because of the large size of the FT Catalog, I'd recommend what you are already doing - run a full population. Note, while the results may not be correct while the Full Population is running and completes, your users can still run FTS queries! As this was to be one of the many subjects I was going to cover in my book on Microsoft Search technologies, along with the above troubleshooting and diagnosis techniques, there are existing 3rd party FTS products now on the market that you might want to review until SQL Server 2005 (Yukon) RTM's in 2005. Feel free to email me directly, if you want more details. Regards, John > John, > [quoted text clipped - 83 lines] > > Phil Sherrod John, Thank you for your help. > As this was to be one of the many subjects I was going to cover in my book > on Microsoft Search technologies, along with the above troubleshooting and > diagnosis techniques, If this book is available, we would like to buy a copy. We started a full population of the catalog. It appeared to run successfully for about 20 hours and added about 9.5 million items to the catalog. Unfortunately, it then logged the following error to the application event log: Event Type: Error Event Source: Microsoft Search Event Category: Gatherer Event ID: 3014 Date: 7/1/2004 Time: 7:26:30 AM User: N/A Computer: IDX Description: An exception occurred. ID: 8. This is an internal error. Reproduce the error with the debugger attached and enable exceptions, then contact PSS. One of the components loaded in your system is bad. You may be able to avoid the problem by recreating the catalog. We checked the Gatherer log file and found many entries similar to this starting after the above error: 7/1/2004 4:55:40 AM MSSQL75://SQLServer/3c69fb99/417A0CF120000000 Add Error fetching URL, (80041812 - Content Index has been shutdown. ) What should we do? SignaturePhil Sherrod (phil.sherrod 'at' sandh.com) http://www.dtreg.com (decision tree modeling) http://www.nlreg.com (nonlinear regression) Phil, This is just a follow-up from our emails on this, so that others who encounter the below Event ID 3014, Event Source, Microsoft Search and Event Category: Gatherer "An exception occurred. ID: 8. This is an internal error" and the actually more informative error msg from the Gatherlog file "80041812 - Content Index has been shutdown.", know what you did to resolve it... Simply, stop and restart (or reboot server) the "Microsoft Search" (mssearch.exe) service! While it's still not apparent what caused this issue, or whether or not it will re-occur given your hardware configuration, remains to be seen. However, the current configuration of FT Indexing 9.5 million rows with both the database files and FT Catalog files on the same disk drive *might* be a contributing factor. If possible, I'd still recommend that you contact Microsoft PSS SQL Server support and have them attach a debugger to the mssearch service to determine (if possible) the exact cause of the above errors! Regards, John PS: As for my book on MS Search technologies, it's on hold for the time being while I go back to work and earn a living and wait for Yukon to be released in mid-2005. > John, > [quoted text clipped - 29 lines] > > 7/1/2004 4:55:40 AM MSSQL75://SQLServer/3c69fb99/417A0CF120000000 Add Error > fetching URL, (80041812 - Content Index has been shutdown. ) > > What should we do? |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2009 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.