Making Sphinx more bareable

[Grumpy]

I just finished tweaking Sphinx (and bunch of other stuff) for another member here and sphinx was being terrible with full indexing! So bad on the first time that it brought the site down for a good 15 minutes and maybe another half hour of crawling speeds on the first run. This person's db is bigger than the average, but something definitely not gigantic either. I write this little helper so others can benefit as well.

Problem:
The pull query creates a gigantic lock on several tables. And once the pull is complete, it creates a giant resource hog, maxing out the drive utilization writing all the indexing results.

(I also didn't realize the disk was almost full... so I was probably killing the journaling making the result worse.)

Solution:
Solution to making sphinx run well came in 4 parts.

Removing table locks
InnoDB to the rescue! Make sure you are running innodb on all referenced tables, not just the key ones. As myisam uses table locks, the pulling of the data can cause a lock that prevents all other actions by your forum.

Using Ranged Queries
Rather than pulling everything at once and killing your ram, disk, locks, etc., you can slice the query down to smaller size.
This is done by adding range metrics

sql_query_range = select min(pid), max(pid) from posts
sql_range_step = 1000

And adding limits through WHERE clause in the query.

 WHERE p.pid >= $start AND p.pid <= $end

The same can be done for tags and archive posts. Though your delta query is likely small enough that it will complete in 1 step.

Setting the range step to 1000 limits the query to do at most 1000 queries at a time. Between this time, other queries (not related to this) can be executed and reduce your queue load. If you look at the WHERE query, you'll see notice it's a fixed number variable rather than a LIMIT clause. So, even if there are zero results in this set, it will still count as one.

The amount of time spent between these queries are set through

sql_ranged_throttle = 200 #in milliseconds

It's worth noting that ranged queries should use primary keys as their limiters and not other key types as databases tend to be clumped by primary key. So fetching a 1000 consecutive rows will be lot easier than fetching 1000 rows spread out.

You can see the full example here: http://pastebin.com/Sm3Y8qEg
Please do not copy/paste that in to your configuration and expect it to work. It's just a sample devoid of any possible db prefixes, connection info.

For reference: Old thread here about ranged queries.

Limiting Indexer
The indexer itself also writes a ton. Plain lot of data. It can be as big or even a lot bigger than your entire database it's indexing. Even if you are using an SSD writing too much data too fast can cause your system to effectively stall even if it doesn't max out your IOPS. For example, even if your SSD can do 100k+ IOPS, it may still be able to only write 100MB/s. So, we limit the indexer's performance.

max_iops = 50
max_iosize = 1048576

By creating a chunk of single IO operation to be max of 1MB and set to maximum of 50 IO operation per second, we effectively create a limit of 50MB/s write limit. By doing so, your disk is still available to serve other needs at the same time.

Wildcard support. :sad:
Simply the lack there of.

#infix_fields    = post, title
#min_infix_len   = 3
#enable_star     = 1

In IPB ACP Sphinx settings, there is an option to support wildcards. While great to have, the amount of difference in performance this changes is extreme. If the database is big enough, they need to be disabled.

It's also worth noting that enable_star option is now considered deprecated. And if you are using a new sphinx version (2.2x or higher), it may not be an existing option entirely. It's just on all the time.

Reference:
http://sphinxsearch.com/docs/current.html#conf-infix-fields
http://sphinxsearch.com/docs/current.html#conf-min-infix-len

------------------------------

Small side note for others with really large number of forums.

You'll need to increase max_filter_values as each of your forum will be added to the query. This limiter is just anti-crazy request. So, even if set too high, it doesn't negatively affect you unless you happen to have a bad request flying out.

max_filter_values = 10000

This goes inside the searchd config.

------------------------------

Hope that helps!

[eGullet]

Thanks for posting this, it's how we've got our Sphinx set up: using the ranged queries is critical. Also, if you are running a particularly large board you would do well to actually run Sphinx on a separate server entirely: something from Amazon Cloud or Rackspace works very well and completely eliminates any danger of high disk IO killing your web server's performance.

[ASTRAPI]

On how big sql you are using this configuration?

Also on how many minutes did you set the sphinx to run on the server?

Thank you

[GreenLinks]

I tried similar changes last year but they do not make much change Grumpy.

What i suggest is adding enough ram to server and then keep Sphinx index completely in memory.

I strongly hope with IPB 4 Sphinx integration will receive major updates so we can have a more suitable search in our hand.

[Makoto]

I tried similar changes last year but they do not make much change Grumpy.

What i suggest is adding enough ram to server and then keep Sphinx index completely in memory.

I strongly hope with IPB 4 Sphinx integration will receive major updates so we can have a more suitable search in our hand.

Sphinx still has to write to disk when rotating indexes. Increasing Sphinx's maximum memory usage improves actual search performance, but won't mitigate the issues associated with rotating indexes on large forums.

I see this problem a lot, I generally always recommend people who want to use Sphinx convert from MyISAM to InnoDB to at least mitigate the heavy table locking issues while Sphinx updates.

[Grumpy]

I tried similar changes last year but they do not make much change Grumpy.

What was the issue that you were facing and trying to solve? Were you facing table locks? Disk saturation? CPU saturation? Memory saturation?
Changing to innodb helps with table locks.
Changing to ranged query helps with table locks, disk saturation & memory.
Setting throttle to ranged queries helps with locks, disk saturation & cpu.
Limiting indexer helps with disk saturation - should be biggest impact for disk.
Removing wildcard helps with disk, cpu & memory.

What i suggest is adding enough ram to server and then keep Sphinx index completely in memory.

I don't think that's really feasible for really large forums... My server's sphinx index total is ~40GB and growing fast (which is... still possible for high end). It's easier to expand ssd/hdd capacity than it is for ram.

[GreenLinks]

What was the issue that you were facing and trying to solve? Were you facing table locks? Disk saturation? CPU saturation? Memory saturation?
Changing to innodb helps with table locks.
Changing to ranged query helps with table locks, disk saturation & memory.
Setting throttle to ranged queries helps with locks, disk saturation & cpu.
Limiting indexer helps with disk saturation - should be biggest impact for disk.
Removing wildcard helps with disk, cpu & memory.
I don't think that's really feasible for really large forums... My server's sphinx index total is ~40GB and growing fast (which is... still possible for high end). It's easier to expand ssd/hdd capacity than it is for ram.

There was no issue , it was related to improving indexing and search results performance. Indexing is completed twice faster now ;) Mine sphinx index is around 36GB and i have plenty additional memory to add if needed. Memory is way cheaper then enterprise sad's these days :smile:

[Grumpy]

Memory is way cheaper then enterprise sad's these days :smile:

Not really...
http://www.newegg.com/Product/ProductList.aspx?Submit=ENE&DEPA=0&Order=BESTMATCH&Description=enterprise+ssd&N=-1&isNodeId=1
Samsung's is roughly $1/GB now vs $10/GB for ram. It's probably the most popular enterprise ssd now. Even if you go intel, it's roughly $4/GB.
It also isn't capped by as many factors like memory. If you had E3, you'd be capped at a mere 32GB.

Enterprise SSD is for suckers imo though. Better off just 2x consumer ssd.

There was no issue , it was related to improving indexing and search results performance. Indexing is completed twice faster now

The suggestions above purposefully slows down its performance to prevention of locking and crashing. Purely from indexing pov, not making these changes will increase performance unless you were hitting swap.

[Flitterkill]

Curious: What time do you have your cron jobs for the daily re-index scheduled for? No matter the size of the DB the running Delta indexing really shouldn't be a problem. Are your forums that popular 24/7 where the performance hit is that much of a problem? Genuinely interested - not trolling. I've got a forum with 100,000+ posts and have the full index cron at 4am. Might be a performance hit for a minute or two tops, maybe? Never had any locked tables - at least noticeable; even when people were active at that time. MyIsam as well.

[Makoto]

Small forums won't struggle much. 100,000 posts isn't really small, but it's still not a lot. When you have millions of posts on a heavily active forum is when it can really become a problem.

It also of course depends on how powerful your server is. A database server running on a SSD drive will suffer significantly less from I/O issues when the index is fully rotating.

I have around 800,000 posts and run a full rotation every 2-3 hours with no issues at all. But I also have a high performance server configuration to make that possible. SSD database/OS drive, MariaDB 10 with XtraDB over standard InnoDB, and so on.

A lot of people sadly are stuck running on shared spinning disks with relatively low end hardware on old unoptimized Apache/MySQL configurations.

[Flitterkill]

Agreed, easily. And the tips provided in the first post were also solid as well. I was just curious as if this was common, Sphinx performance problems on stock MySql/MyIsam roll outs. I've not really seen it myself and the first poster annotating in really small type that the drive was nearly full was almost certainly the primary problem in his instance.

Is there any particular reason for doing those full rotations every few hours instead of once a day?

[Grumpy]

I would suggest changes like above if you at hit roughly a million posts. Assuming running on SSD, <500k might not even really notice it.

Unless you get >50k posts per day, I doubt delta will cause much of a problem for anyone. I haven't seen any case first hand where the delta query caused the issue.

I've not really seen it myself and the first poster annotating in really small type that the drive was nearly full was almost certainly the primary problem in his instance.

It's not my first time setting up something like this. So, my reference to a specific case really doesn't matter. That's why I wrote it really small. And even on that case, I kept on tweaking even after realizing disk capacity issue based on what was reasonable/ideal for that server. The small statement really only refers to my first iteration of the tweak.

Is there any particular reason for doing those full rotations every few hours instead of once a day?

It allows greater accuracy.

[kihon]

We're the "member" who Grumpy did our Sphinx mentioned above. Seems to be running great! Search absolutely flat out flies! Thank you Grumps for your work.

[Grumpy]

Thank you Grumps for your work.

My pleasure.