Optimized sphinx.conf that uses ranged queries

[grinler]

For those who are interested, I have made available a sphinx.conf that uses ranged queries. If you are using MyIsam mysql tables and find that you are getting table locks when rebuilding Sphinx every night, this config should help alleviate these issues.

After I upgraded to version 3.4.2 from 3.1.4, my server was getting table locks doing the nightly rebuild. I hired Percona, a well respected MySQL support company, to resolve this issue and they suggested ranged queries in the Sphinx rebuild process. This has reduced the table locks on my server, though the DB server will run slowly while performing the index. I offered this file to Matt and Brandon to include in the base distribution, but never heard back, so I thought I would offer it to the community directly.

The new sphinx.conf file can be found here:

http://www.bleepingcomputer.com/misc/sphinx.conf

Please note that when using this new conf, make sure the table names match up with what you are using. Some sites may be using a $INFO['sql_tbl_prefix'] in their conf_global.php and will have to adjust the config accordingly.

Hope you find it useful.

[Luis Manson]

thanks!

[maddog107_merged]

Thanks, optimizations are always appreciated. And thats really all they found? Are you using InnoDB?

[grinler]

Nope..they felt it was unnecessary at this time to switch to innodb. The sphinx optimization was more than adequate to get indexing working properly.

[Fast Lane!]

Bump... will this work with sphinx 0.99?

[grinler]

Yes, it does.

[Dmacleo]

iirc you were looking into real time indexes at one point, was it you and was there any success?

[grinler]

That wasn't me, unless you were talking to Fast Lane.

[Dmacleo]

That wasn't me, unless you were talking to Fast Lane.

no I thought it may have been you, guess not :)

[Fast Lane!]

For those who are interested, I have made available a sphinx.conf that uses ranged queries. If you are using MyIsam mysql tables and find that you are getting table locks when rebuilding Sphinx every night, this config should help alleviate these issues.

After I upgraded to version 3.4.2 from 3.1.4, my server was getting table locks doing the nightly rebuild. I hired Percona, a well respected MySQL support company, to resolve this issue and they suggested ranged queries in the Sphinx rebuild process. This has reduced the table locks on my server, though the DB server will run slowly while performing the index. I offered this file to Matt and Brandon to include in the base distribution, but never heard back, so I thought I would offer it to the community directly.

The new sphinx.conf file can be found here:

http://www.bleepingcomputer.com/misc/sphinx.conf

Please note that when using this new conf, make sure the table names match up with what you are using. Some sites may be using a $INFO['sql_tbl_prefix'] in their conf_global.php and will have to adjust the config accordingly.

Hope you find it useful.

I wanted to thank you SOOOOO much. This saves me 30 minutes of downtime per day!

[grinler]

Your very welcome. I sent this off to Brandon and Matt and never received a response back. As there does not seem to be any disadvantages to using the ranged queries, I hope they would consider using this in their default sphinx.conf.

Seems like a win win to me.

[bfarber]

I don't recall getting this previously, though if it was a PM I get a lot of those and often lose track.

We are planning to look into real-time index updates in a future release of the software.

[grinler]

Hi Brandon,

I just looked and it seems I sent it to only Matt. Could have sworn I included you, but I guess I didn't. Apologies for the confusion.

Real time indexing would be terrific. Would eliminate both the need for ranged queries (table locks) and obviously the benefits of the of having an almost real-time index.

[BostonBob]

I went ahead and used your configuration, but I'm still having nightly crashes with my table locking up.

So I modified the sql_range_step from 1000 to 500. And I also modified the sql_ranged_throttle = 1000.

Has anyone attempted to fiddle with these settings with good results? Is there any other settings that might prevent nightly crashing with Sphinx reindexes its search database?

Our forums board is huge with over 105,000 members and 1.8M posts. I'm trying my best to prevent a table lockup.

[BostonBob]

The settings continued to shut down the server with sql_range of 500 and 1000. I also increased the sql_ranged_throttle to 3000 to try and solve it -- but to no avail.

Is there a way to throttle this script to prevent the shutting down of the site?

[grinler]

If the ranged queries are not helping, you may want to try and offload the sphinx indexing to a replication server if you have one.

[BostonBob]

If there any other suggestions you might have?

I saw a thread on Sphinx about other settings -- but I'm not sure about them.

http://sphinxsearch.com/forum/view.html?id=9706

Apparently someone with a large invision forum, added some other parameters

max_iops = 20

max_iosize = 1M

Has anyone messed with these settings?

Thanks,

Worthy

[blair]

Is there a way to throttle this script to prevent the shutting down of the site?

InnoDB tables instead of MyISAM. Solid state drive(s).

[maddog107_merged]

InnoDB tables instead of MyISAM. Solid state drive(s).

I still get about 1-2 mins of outtage (according to Webmetrics) when I do a full reindex using SSD's (4x512gb Crucial M4's) and of course I am using InnoDB. I haven't tried ranged yet though.

3.2 Million posts

43k members

[blair]

I can use my forum during a full reindex and not even know it's happening. >2M posts, 13GB posts table. Must be a difference in hardware, or my.cnf settings.

Ranged queries still seem like a good idea.