Implement a Sphinx driver for personal conversations

[Feld0]

For large communities, the ability to move tables to InnoDB and replace fulltext searches on them with Sphinx is quite honestly a lifesaver. MyISAM's table locks are a very real issue when you have over 3000 posts per day, and Sphinx performs great as a drop-in replacement for fulltext search.

But Sphinx searching support isn't universally implemented across the IPS suite, which makes it impossible to cleanly migrate some tables to InnoDB without breaking the search feature on them. If these tables happen to be part of actively used functionality on a large site, said functionality can run into some nasty performance issues.

Case in point: my message_posts and message_topics tables are huge, at ~335 000 and ~34 000 rows, respectively. We've spent a fair bit of money and put a lot of work into setting up an optimal environment for InnoDB, which is quite evident in the general responsiveness of our public-facing pages, but as soon as you open the personal messenger, things slow down noticeably. It's still serviceable, but it does present an issue when one part of the site is an order of magnitude less responsive than the rest of it. It's especially slow when submitting a reply to a convo, as any write operations in MyISAM initiate a full table lock for every user on the site. Those locks stack up when you deal with over 100 pageviews per minute during peak hours.

I realize that personal convos aren't a public-facing feature of forums, and optimizing the public-facing stuff first should take priority. But it's still a feature that is typically in very widespread use, and I don't see any reason to bar it from InnoDB's benefits.

Replacing MyISAM + fulltext indexes with a properly tuned InnoDB + Sphinx setup proved hugely beneficial for our 1.1 million posts, so I'd like to kindly request that Sphinx support be extended to personal conversations so those can take advantage of InnoDB as well, because the two tables associated with personal convos can easily grow very large on big communities.

If at all practical, I'd like to see a full Sphinx driver implemented for every IPS application in the future, to completely eliminate MyISAM/fulltext indexes as a dependency for any component of IP.Suite.

[AtariAge]

I second this wholeheartedly. My message_posts table has 649,642 rows, and message_topics has 303,655 rows. I use Sphinx and the Messenger functionality really needs some attention in this department.

..Al