grinler Posted March 11, 2013 Share Posted March 11, 2013 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. Link to comment Share on other sites More sharing options...
Luis Manson Posted March 11, 2013 Share Posted March 11, 2013 thanks! Link to comment Share on other sites More sharing options...
maddog107_merged Posted March 14, 2013 Share Posted March 14, 2013 Thanks, optimizations are always appreciated. And thats really all they found? Are you using InnoDB? Link to comment Share on other sites More sharing options...
grinler Posted March 15, 2013 Author Share Posted March 15, 2013 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. Link to comment Share on other sites More sharing options...
Fast Lane! Posted April 29, 2013 Share Posted April 29, 2013 Bump... will this work with sphinx 0.99? Link to comment Share on other sites More sharing options...
grinler Posted April 29, 2013 Author Share Posted April 29, 2013 Yes, it does. Link to comment Share on other sites More sharing options...
Dmacleo Posted April 29, 2013 Share Posted April 29, 2013 iirc you were looking into real time indexes at one point, was it you and was there any success? Link to comment Share on other sites More sharing options...
grinler Posted April 29, 2013 Author Share Posted April 29, 2013 That wasn't me, unless you were talking to Fast Lane. Link to comment Share on other sites More sharing options...
Dmacleo Posted April 29, 2013 Share Posted April 29, 2013 That wasn't me, unless you were talking to Fast Lane. no I thought it may have been you, guess not :) Link to comment Share on other sites More sharing options...
Fast Lane! Posted May 6, 2013 Share Posted May 6, 2013 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! Link to comment Share on other sites More sharing options...
grinler Posted May 6, 2013 Author Share Posted May 6, 2013 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. Link to comment Share on other sites More sharing options...
bfarber Posted May 6, 2013 Share Posted May 6, 2013 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. Link to comment Share on other sites More sharing options...
grinler Posted May 6, 2013 Author Share Posted May 6, 2013 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. Link to comment Share on other sites More sharing options...
BostonBob Posted June 2, 2013 Share Posted June 2, 2013 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. Link to comment Share on other sites More sharing options...
BostonBob Posted June 3, 2013 Share Posted June 3, 2013 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? Link to comment Share on other sites More sharing options...
grinler Posted June 3, 2013 Author Share Posted June 3, 2013 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. Link to comment Share on other sites More sharing options...
BostonBob Posted June 4, 2013 Share Posted June 4, 2013 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 Link to comment Share on other sites More sharing options...
blair Posted June 7, 2013 Share Posted June 7, 2013 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). Link to comment Share on other sites More sharing options...
maddog107_merged Posted June 14, 2013 Share Posted June 14, 2013 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 Link to comment Share on other sites More sharing options...
blair Posted June 15, 2013 Share Posted June 15, 2013 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. Link to comment Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.