Re: Performance problems -- need guidance on scaling
From: Phil Sherrod (phil.sherrod_at_REMOVETHISsandh.com)
Date: 11/04/04
- Next message: Hilary Cotter: "Re: DIFFICULT PROBLEM! SSL for SQL 2000 Server. MS Fix bulletin does not help at all."
- Previous message: Hilary Cotter: "Re: Words Found Using Inflectional Search"
- In reply to: John Kane: "Re: Performance problems -- need guidance on scaling"
- Next in thread: John Kane: "Re: Performance problems -- need guidance on scaling"
- Reply: John Kane: "Re: Performance problems -- need guidance on scaling"
- Messages sorted by: [ date ] [ thread ]
Date: Thu, 4 Nov 2004 22:16:48 GMT
John,
Thank you for your message, and thank you for the help you provided
previously. I hope you got your MVP rating.
I never updated you on the solution to the reliability problems that we
finally got from Microsoft. When we designed our system, we were concerned
about overflowing a 32-bit integer in a few years, so we used a double
precision floating point value as the key; it had only integer values in it.
Apparently, there is a bug in FTS that causes it to crash occasionally if
you use a floating point key. We dropped back to a 32-bit integer key, and
that solved the crashes.
On 4-Nov-2004, "John Kane" <jt-kane@comcast.net> wrote:
> I see that you've implemented many of the ideas & references we discussed
> previously under the "Re: Need help with full-text performance for large
> database" thread in this newsgroup and more specifically, we discussed
> that
> FTI was mostly I/O bound, now that you're no longer i/o bound but are now
> bound by the CPU.
Yes, that is correct. The switch to the Adaptec RAID controller with the
10k RPM disks tremendously increased our I/O capacity. So we're now CPU
bound.
I have read that putting the catalog and SQL table on separate RAID
controllers improves performance, but the old Promise controller that we
have is SO slow compared to the Adaptec that we opted to use it only for our
application files. We may consider adding a second Adaptec RAID controller
with two more SATA drives if you still think that splitting the table and
catalog would provide significant performance improvement.
> While I'm not sure if your server will support, multiple
> CPU's, but that's the next step
Our current server will support only a single CPU. We are considering a new
one with dual Xeon 3.2 GHz CPUs and 4 G of 800 MB FSB memory. Pretty much
all of the servers we've found with more than 2 CPU capability use the
slower 3.0 G CPU's with 333 or 300 MB FSB. I would appreciate your comments
about your feeling about whether this will be adequate.
> and then use sp_configure to set SQL Server to use the other CPU to avoid
> the MSSearch service from affecting your normal SQL Server processing. You
> should set the sp_fulltext_service resource_usage level to 5 to allow for
> more concurrent SQL FTS queries.
Please explain the sp_fulltext_service resource_usage level. What is the
default value? How exactly do we increase it?
Currently, we are sending 4 simultaneous searches to the server. Do you
think this is a good number?
> Could you post examples of your more complex searches that are taking 20
> to
> 30 seconds to complete? Are they using multiple CONTAINS or CONTAINSTABLE
> statements &/or depend upon additional where clause restrictions?
There is only a single CONTAINSTABLE clause in any of our searches.
Here is an example of a simple search that typically takes 2 to 10 seconds
depending on the words specified:
("Basic" AND "Instinct" AND "Sharon" AND "Stone") AND (JPG OR GIF OR PNG OR
MPG OR AVI OR NZB)
A complex query would combine up to 50 searches similar to the one shown
above combined with OR, like this:
(query1) OR (query2) or ... or (query50)
Where query1, etc, is ("Basic" AND "Instinct" AND "Sharon" AND "Stone") AND
(JPG OR GIF OR PNG OR MPG OR AVI OR NZB) or something similar to it. In
other words, each basic query will have AND and OR operators and up to 50 of
them enclosed in parentheses will be combined using OR.
Note that there may be 5 million or more entries in the catalog for JPG, 2
million for MPG, etc., but the combination will probably only match a few
messages if any.
> Finally, I'd also recommend (if you have not already done so) that you try
> to get a copy of SQL Server 2005 (codename Yukon) beta2 that can be
> downloaded from MSDN, if you are a subscriber to MSDN as Yukon contains
> many, many FTS performance improvements!
We have a Universal MSDN subscription, so that's not a problem. Do you
think the beta2 version is reliable enough for production use?
What's involved in transferring a table and catalog with 15 M rows to Yukon?
It would take a long time to build the index from scratch. Can Yukon import
SQL tables and FTS catalogs from SQL 8?
-- Phil Sherrod (phil.sherrod 'at' sandh.com) http://www.dtreg.com (decision tree modeling) http://www.nlreg.com (nonlinear regression) http://www.NewsRover.com (Usenet newsreader) http://www.LogRover.com (Web statistics analysis)
- Next message: Hilary Cotter: "Re: DIFFICULT PROBLEM! SSL for SQL 2000 Server. MS Fix bulletin does not help at all."
- Previous message: Hilary Cotter: "Re: Words Found Using Inflectional Search"
- In reply to: John Kane: "Re: Performance problems -- need guidance on scaling"
- Next in thread: John Kane: "Re: Performance problems -- need guidance on scaling"
- Reply: John Kane: "Re: Performance problems -- need guidance on scaling"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
