Sphinx search performance optimization: multi-threaded search

queries in mysql, sphinx and many other database or search engines are typically single-threaded. that is when you issue a single query on your brand new r910 with 32 cpu cores and 16 disks, the maximum that is going to be used to process this query at any given point is 1 cpu core and 1 disk. in fact, only one or the other.

seriously, if query is cpu intensive, it is only going to be using 3% of the available cpu capacity (for the same 32-core machine). if disk io intensive – 6% of the available io capacity (for the 16-disk raid10 or raid0 for that matter).

let me put it another way. if your mysql or sphinx query takes 10s to run on a machine with a single cpu core and single disk, putting it on a machine with 32 such cores and 16 such disks will not make it any better.

but you knew this already. question is – can you do something about it?

in case of sphinx – indeed you can! and with very little effort. in fact, it does not require any changes to your application or database whatsoever, it is only a matter of small changes to the sphinx configuration.

the plan

first of all, let me explain what we are trying to achieve here.

sphinx has the ability to do distributed search out of the box – it was designed to scale out that way very early on. if your sphinx index does not fit to one machine, you would index different parts of it from different machines and then you would have an aggregator node that receives the request from application, issues search requests to all data nodes in parallel, merges results from all of the data nodes and returns results back to the application as if it was just one server serving the request in the first place.

well, guess what – you can actually utilize this feature to your advantage even if your data can easily fit into one machine and all you want is your queries to be many times faster. even more so, sphinx now supports this out of the box, so you don’t need to pretend you are querying remote nodes.

also, you get a bonus: once you configure server for distributed search, you can do indexing in parallel too!

word of caution: while this technique will improve most types of search queries, there are some that aren’t going to benefit greatly from parallel execution. the reason is that while search is done on data nodes (even if local ones) and in parallel, merging of results is done by the aggregator and therefore it is single-threaded. merging includes some cpu-intensive operations such as ranking, ordering or even count with group by and if data nodes return large amounts of data to post-process, aggregator may well become a bottle-neck due to its single-threaded nature.

this is however easy to check – look at your sphinx query log and look at the number of results matched per query – this should give you a clue.

execution

let us assume you have this one index on one server with the following basic configuration (many irrelevant details omitted):

source src1
{
	type = mysql
	sql_query = select id, text from table
}

index idx1
{
	type = plain
	source = src1
}

searchd
{
	dist_threads = 0 # default
}

and now we want it to utilize 3 cpu cores and/or disks on a local machine for this index of ours – idx1. here’s how we would change the configuration:

source src1
{
	type = mysql
	sql_query = select id, text from table
}

source src1p0 : src1
{
	sql_query = select id, text from table where id % 3 = 0;
}

source src1p1 : src1
{
	sql_query = select id, text from table where id % 3 = 1;
}

source src1p2 : src1
{
	sql_query = select id, text from table where id % 3 = 2;
}

index idx1_template
{
	type = plain
	source = src1
}

index idx1p0 : idx1_template
{
	source = src0
}

index idx1p1 : idx1_template
{
	source = src1
}

index idx1p2 : idx1_template
{
	source = src2
}

index idx1
{
	type = distributed
	local = idx1p0
	local = idx1p1
	local = idx1p2
}

searchd
{
	dist_threads = 3
}

and you’re done. of course, you need to reindex all of the indexes, but you can now do it in parallel – just run a separate indexer for every index idx1p0 through idx1p2.

btw, using div operator is not necessarily the best way to split data, you may want these to be ranges by using a helper table in mysql to define those ranges, mixing it with sql_query_range or something else, depending on how your data looks like.

finishing line

i always loved how sphinx scales out easily with as many machines you need and have been running it this way for many years now, however i think i don’t utilize this feature to make queries even faster on a one-machine show nearly as often as i should. well, it’s not like it is slow or anything, but queries are never too fast, are they?