Hi Courtenay I don't have an obvious solution coming to mind, but let me just add some feedback on the points you've raised…
Index merging does exist - it's not particularly fast from what I understand, and I know it hasn't been reliable (but has apparently improved in recent Sphinx releases - not sure which version you're using?). The ts-datetime-delta gem merges its delta index into the core, if you're looking for code examples. If you were going to remove deltas, you could try a basic approach at sharding by updated_at - I'm just thinking this through as I type, so don't consider it a well-thought-out idea, but you can define multiple indices for a model, and so could have two or three indices with the same definitions, but with a WHERE clause breaking them up by certain timeframes. That way, you would only need to process one or two on a more regular basis - to some extent treating them like deltas. Granted, this would be a more manual set up, but if a good approach could be figured out, it may be a solid option. http://freelancing-god.github.com/ts/en/indexing.html#multiple http://freelancing-god.github.com/ts/en/indexing.html#conditions Patching Sphinx will not be a fun experience - my C++ skills are almost non-existent and I've only made one small patch myself (authentication of socket connections), but from what I understand Sphinx's codebase is not anything close to clean. And finally, on the topic of real-time indices - this isn't yet supported in Thinking Sphinx, and it's also worth noting that it's a separate index type, and thus doesn't have many of the features that the SQL indices do (morphologies, infix/prefixes, charset tables, etc). It also requires the SphinxQL protocol. I *am* rewriting TS to use SphinxQL - you can find that code in the edge branch - but it doesn't touch RT indices at this point. When I *do* get to that point, I'm considering having a core index that is a SQL index, to take advantage of Sphinx's full feature set, and then the delta is an RT index. That doesn't remove the need for regular full indexing, though… Here's hoping RT indices get all the nice features in the near future - but that's not yet the case at this point in time. I know there's no easy solutions that I've thought of, but perhaps this helps you to come up with something. -- Pat On 18/11/2011, at 7:18 AM, court3nay wrote: > Hey all, I'm bashing my head on this one and I can't believe that I'm > doing it right. > > Our setup is, we have a dedicated server with sphinx serving just 3 > indexes (faq, discussion, user). > Indexing takes about an hour for the full discussion index, so I'm > using delayed deltas. > > However, this means that the index task needs to do an UPDATE on the > discussion table. Which means I can't run it off a replicated > (readonly) slave - and so my main db (write) server gets taken down > when indexing because of the relative power of the sphinx box and the > overloaded state of the master db. > > Possible solutions: > > - add sql rate limiting to sphinx. Makes the index take four hours > instead. > - there seems to be such a thing as index merging. maybe use this > instead of reindexing? > - disable delta indexing and use the read-slave to index. Discussions > only get reindexed fully every hour or so, no delta. > - set up mysqlproxy and automatically send reads and writes to the > correct place. Requires that I learn Lua. Not entirely viable. > - patch Sphinx to allow different db connections. > - ??? How do other people solve this issue? > > However, we are running on an old version of Sphinx. What if we > upgrade to the latest with MVA and realtime indexes - we won't need > the deltas any more, right? > > > -c3 > > -- > You received this message because you are subscribed to the Google Groups > "Thinking Sphinx" group. > To post to this group, send email to [email protected]. > To unsubscribe from this group, send email to > [email protected]. > For more options, visit this group at > http://groups.google.com/group/thinking-sphinx?hl=en. > -- You received this message because you are subscribed to the Google Groups "Thinking Sphinx" group. To post to this group, send email to [email protected]. To unsubscribe from this group, send email to [email protected]. For more options, visit this group at http://groups.google.com/group/thinking-sphinx?hl=en.
