Serendipity Forums

I'm not sure this doesn't belong in development but I'll start it here and someone can tell me or not.
One customer requirement of several of our existing s9y implementations is full text searching. This requirement forced us to rely on the MySQL MyISAM default storage engine because currently, it is the only choice for handling full text indexing within MySQL and without modifying s9y. We are experiencing latency and connection failures under heavier loads, even with 4 mysql servers feeding the front end via mysqlproxy. The MyISAM storage engine lacks the high concurrency performance and scalability of other available MySQL storage engines.
I want to provide the full text searching with sphinx and move to the innoDB or possibly NDB Cluster storage engine. I'm looking for anyone currently using sphinx with s9y or other insight regarding the issue of scalability of s9y mysql back end DB.

Hi Keith!

This is in fact the best place to discuss this, yes.

I personally have not yet had such problems, but I also have not personally yet worked with s9y on a replicated/proxied mysql setup.

Hopefully someone with more knowledge can reply here. If there are any changes required to s9y, we could happily make that happen...

Best regards,
Garvin

Thanks Garvin. I assume you don't mean that nobody else has had issues with the database back-end not keeping up with the s9y front end, because our experience has been that with a single database, on the same or different server, (i.e. no mysqlproxy) we have had database latency issues under load relatively uniformly across several different hardware platforms. This is what drove the decision to scale out with a master-slave cluster. Originally, I had modified s9y to write only on the master cluster node with an insignificant code patch. We load tested the application to failure on that design but I don't have the numbers from those tests since they were run remotely by someone else. The decision to proxy was, so far, an unnecessary design change, driven by intent to run non s9y applications on the database cluster. It would be worthwhile running the same load test again with the current mysqlproxy design in place to compare with initial load test results assuming we have or remember those numbers. As far as I can tell, performance is the same with and without mysqlproxy. As far as s9y modifications, has anyone already done a database profile code patch of s9y? If not I will do so and then make it available here along with our profile analysis report once we complete it.

Sorry about the double post but I see you have some SQL profiling built in already, I'll try using that first and see what we get.

Hi!

I believe one usere here on this forum successfully used a reverse-proxy squid to actually cache s9y instances accross slave servers. This is a trade-off in terms of "dynamic" content serving, but if all "GET" responses come from the reverse proxy, this puts a lot of less tress on the DB backend because it is not initialized at all. Maybe that is an option additional to SQL proxying.

Often, the amount that s9y needs to serve a page are not so much depending on the load of the database, but simply due to some queries adding up to maybe half a second or so, and maybe that horizontally adds up to your latency issues?

Regards,
Garvin