Advertisement:

Author Topic: Are SQL Clusters supported by SMF?  (Read 414 times)

Offline JadedDragoon

  • Newbie
  • *
  • Posts: 3
Are SQL Clusters supported by SMF?
« on: August 12, 2018, 03:55:32 AM »
Seems like a dumb question to ask, honestly. None the less, I find myself needing to know before I waste time on another BB that doesn't support common SQL configurations. I almost couldn't believe it when I was told phpBB doesn't support SQL clusters.

So, to be clear. I have Apache 2.4.18 with PHP 7.0.30 executed by FCGId (suExec'd) and a MariaDB 10.3.8-based Galera cluster. Is SMF 2.0.x expected to work on that configuration?

Offline Arantor

  • Resident Overthinker
  • SMF Friend
  • SMF Legend
  • *
  • Posts: 69,626
    • StoryBB/StoryBB on GitHub
Re: Are SQL Clusters supported by SMF?
« Reply #1 on: August 12, 2018, 05:28:37 AM »
It’s never been tested on clusters, and definitely not on such a recent version of MariaDB.

The reality is also that no SMF forum we’ve ever seen needed a DB cluster just for its forum. Not even one accepting more than 1 post per second every second for months on end.
Don’t try to tell me that some power can corrupt a person. You haven’t had enough to know what it’s like.

No good deed goes unpunished / No act of charity goes unresented.

Offline JadedDragoon

  • Newbie
  • *
  • Posts: 3
Re: Are SQL Clusters supported by SMF?
« Reply #2 on: August 12, 2018, 01:32:37 PM »
I should clarify that this is a master-master cluster (Galera).

The cluster is just what I already have. It's already set up and running. And I don't mind going off in a route that isn't well tested... this won't be for anything mission critical and I know enough about SQL databases and software design to know, if the cluster is doing its job and the software is well designed, there should be no difference between a cluster and a solo sql server.

Let me put it this way. Will SMF choke if I have an auto_increment_increment or auto_increment_offset of more than 1? Does it require auto-incremented values to be sequential? phpbb does.

Online albertlast

  • Development Contributor
  • Jr. Member
  • *
  • Posts: 370
Re: Are SQL Clusters supported by SMF?
« Reply #3 on: August 12, 2018, 02:49:16 PM »
The ai value is always getted by the information from mysql server and
not generated by smf self.

Offline Arantor

  • Resident Overthinker
  • SMF Friend
  • SMF Legend
  • *
  • Posts: 69,626
    • StoryBB/StoryBB on GitHub
Re: Are SQL Clusters supported by SMF?
« Reply #4 on: August 12, 2018, 02:54:46 PM »
SMF doesn’t require it to be sequential but it does in places rely on the fact that a higher id is a more recent item than a lower one. But in all cases SMF asks the DB for the inserted id.

However, I’d still point out that you characterise this as a common situation... it really isn’t. Clusters are way more rare than people seem to think, and even in my day job handling sites that routinely get tens of thousands of hits per minute we don’t build clusters for them...
Don’t try to tell me that some power can corrupt a person. You haven’t had enough to know what it’s like.

No good deed goes unpunished / No act of charity goes unresented.

Offline JadedDragoon

  • Newbie
  • *
  • Posts: 3
Re: Are SQL Clusters supported by SMF?
« Reply #5 on: August 12, 2018, 09:41:40 PM »
SMF doesn’t require it to be sequential but it does in places rely on the fact that a higher id is a more recent item than a lower one. But in all cases SMF asks the DB for the inserted id.

However, I’d still point out that you characterize this as a common situation... it really isn’t. Clusters are way more rare than people seem to think, and even in my day job handling sites that routinely get tens of thousands of hits per minute we don’t build clusters for them...

Thank you for your time and the answers to my questions. phpBB does (blindly) assume all auto-incremented values are sequential which, in my estimation, is a significant error whether clustering is involved or not. And when it discovers its assumption that the 5th row it inserted would have an id of 5 does not, in fact, have an id of 5 (because it has an id of 15)... it crashes to a screeching halt and spews cryptic errors about missing parenthesis in its SQL syntax. Please note how oddly specific I'm being. This has been an unresolved issue for them since 2008, at least.

As to your repeated points about performance: I understand your points clearly. But performance is in no way a consideration here. I'm not worried about performance. I have no expectation that this site will saturate the performance capabilities of even a single SQL server. Nor do I have any expectation of this site receiving tens of thousands of queries per minute, or hour, or year, or decade.  That is not why I have a cluster. There are other reasons for clustering, particularly master-master clustering, besides performance. And even if I had no reason to want a clustered SQL setup, it's what I already have. And I'm not tearing it down because just one application that uses it, a small forum for a personal project, wants to be a special snowflake and demand sequential auto-incremented values. Nor am I setting up a separate SQL server instance for a single small forum. Come to that I will simply use different forum software. Hell, I might use sqlite if I'm really forced to, but I'd really rather not.

I take your point about insertion order. Given the extremely low likelihood of multiple insertions (from multiple masters almost simultaneously) is in my use case, I may risk it. ::shrug::

Offline Arantor

  • Resident Overthinker
  • SMF Friend
  • SMF Legend
  • *
  • Posts: 69,626
    • StoryBB/StoryBB on GitHub
Re: Are SQL Clusters supported by SMF?
« Reply #6 on: August 13, 2018, 02:35:59 AM »
Honestly, good luck finding any of the forum packages that’s even been tested on a cluster...
Don’t try to tell me that some power can corrupt a person. You haven’t had enough to know what it’s like.

No good deed goes unpunished / No act of charity goes unresented.