Invision Community 4: SEO, prepare for v5 and dormant account notifications Matt November 11, 2024Nov 11
Posted March 11, 201312 yr 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.
March 14, 201312 yr Thanks, optimizations are always appreciated. And thats really all they found? Are you using InnoDB?
March 15, 201312 yr Author 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.
April 29, 201311 yr iirc you were looking into real time indexes at one point, was it you and was there any success?
April 29, 201311 yr That wasn't me, unless you were talking to Fast Lane. no I thought it may have been you, guess not :)
May 6, 201311 yr 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!
May 6, 201311 yr Author 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.
May 6, 201311 yr 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.
May 6, 201311 yr Author 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.
June 2, 201311 yr 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.
June 3, 201311 yr 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?
June 3, 201311 yr Author 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.
June 4, 201311 yr 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
June 7, 201311 yr 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).
June 14, 201311 yr 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
June 15, 201311 yr 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.
Archived
This topic is now archived and is closed to further replies.