Implementing a massive search application

Question

We have an email service that hosts close to 10000 domains such that we store the headers of messages in a SQL Server database.

I need to implement an application that will search the message body for keywords. The messages are stored as files on a NAS storage system.

As a proof of concept, I had implemented a SQL server based search system were I would parse the message and store all the words in a database table along with the memberid and the messageid. The database was on a separate server to the headers database.

The problem with that system was that I ended up with a table with 600 million rows after processing messages on just one domain. Obviously this is not a very scalable solution.

Since the headers are stored in a SQL Server table, I am going to need to join the messageIDs from the search application to the header table to display the messages that contain the searched for keywords.

Any suggestions on a better architecture? Any better alternative to using SQL server? We receive over 20 million messages a day. We are a small company with limited resources with respect to servers, maintenance etc.

Thanks

Answer 1

lucene.net will help you a lot, but no matter how you approach this, it's going to be a lot of work.

Answer 2

Consider not using SQL for this. It isn't helping.

GREP and other flat-file techniques for searching the text of the headers is MUCH faster and much simpler.

Answer 3

You can also check out the java lucene stuff which might be useful to you. Both Katta which is a distributed lucene index and Solr which can use rsync for index syncing might be useful. While I don't consider either to be very elegant it is often better to use something that is already built and known to work before embarking on actual development. Without knowing more details its hard to make a more specific recommendation.

Answer 4

have a look at Hadoop. It's complete "map-reduce" framework for working with huge datasets inspired by Google. It think (but I could be wrong) Rackspace is using it for email search for their clients.

Answer 5

i wonder if BigTable (http://en.wikipedia.org/wiki/BigTable) does searching.

Answer 6

Look into the SQL Server full text search services/functionality. I haven't used it myself, but I once read that Stack Overflow uses it.

Answer 7

three solutions:

1. Use an already-existant text search engine (lucene is the most mentioned, there are several more)
2. Store the whole message in the SQL database, and use included full text search (most DBs have it these days).
3. Don't create a new record for each word occurrence, just add a new value to a big field in the word record. Even better if you don't use SQL for this table, use a key-value store where the key is the word and the value is the list of occurrences. Check some Inverted Index bibliography for inspiration

but to be honest, i think the only reasonable approach is #1

Answer 8

If you can break up your 600 million rows, look into database sharding. Any query across all rows is going to be slow. At very least you could break up by language. If they're all English, well, find some way to split the data that makes sense based on common searches. I'm just guessing here but maybe domains could be grouped by TLD (.com, .net, .org, etc).

For fulltext search, compare SQL Server vs Lucene.NET vs cLucene vs MySQL vs PostgreSQL. Note full-text search will be faster if you don't need to rank the results. If a database is still slow look into performance tuning and if that fails look into a Linux-based db.

http://incubator.apache.org/lucene.net/

http://sourceforge.net/projects/clucene/