In a problem! Need your piece of brain!

Required a solution to show faster search results.

[Farhan Kamal]

To all webmasters!

One of our clients owns a searchable (full text search) online library of articles and news reports, which are subscribed by users who use the search the service to find related articles for research.

Solutions such as stored procedures are used in order to make the search experience more efficient.

Currently the huge database of about 8 GB is hosted on a dedicated server hosted by (Rackspace). The server configuration is as follows:

1.6 Processor
1.5 GB Ram
2 x 18 scsii GB Hard Drives
Windows 2000 server
SQL database.

While testing the site, we noticed that whenever, 15 or 20 users search the site at the same time, the processor usage rises up to almost 100% which results a “connection time out’ for a couple of users. This high usage of the processor may sometimes cause a sever-hang as each search required a full-text search of 8 GB data.

Here’s a brief overview of the stats:

1 Hit
Processor: before hits usage: 6 %
after hits usage: 80%

5 Hits
Processor: before hits usage: 3 %
after hits usage: 100%

10 Hits
Processor: before hits usage: 4 %
after hits usage: 100%

20 Hits
Processor: before hits usage: 4.7%
after hits usage: 100%

What do you as webmaster suggest in this case? Please ask me for more clarification if required.

Help would be much appreciated
Thanks in advance.

[roddo]

Hi,

I'd say get a server with 2+ cpus and more RAM, 8GB is a hell of a lot of data to crunch and MSSQL Server is a power hungry beast.

[Farhan Kamal]

Okay.. so can you suggest an alternative to MSSQL? Will switching to UNIX servers (changing the database to MYSQL) be helpful?

[roddo]

Hi Farhan,

I think going a full text search on 8GB of data is going to hammer your existing server no matter what RDBMS you use. MySQL is faster than MSSQL but doesn't have all the features MSSQL has. You need to do some serious reading before making a decision to move as it will probably involve a of a lot of work.

[grahamstewart]

You could create a second database that links search terms to articles in the first database.

By 'search terms' I mean all uncommon words (e.g ignore things like and,but,a,the,for,of etc etc)

This might help spread the load and speed up searches. The harsher you are about search terms the smaller the search database and the faster it will be (i.e. you could be extreme and only allow nouns and pronouns)

[mattur]

As roddo says full-text search on a big db will be reource intensive, regardless of db you use.

I would start off by assessing what visitors search for, how they search, what the most popular searches are etc.

Do they need to use a full-text search or could a faster search be implmented? eg add metadata fields, then search using faster sql selects. Or maybe hard code the commonest searches.

The full-text search could then be called if the basic search returns no results, or could be provided as an advanced search option.

[Farhan Kamal]

Thanks for your very helpful replies :)

We have decided to stay on windown platform and and have limited the search to topics of articles only. This has reduced a lot of 'stress' and our server can breathe easily now.