Serendipity Forums

A guy I know wants to set up a few servers to host several s9y sites. The person he relies on to maintain the hardware, install php, mySQL, etc is setting up the databases as mySQl Cluster... I found info on it here... and here

Given my limited knowledge on mySQL, that page may as well be written in Greek because I do not understand a word of it.

As a test, I took a database backup from one of his existing s9y sites, and tried importing it into a newly created mysql cluster database and encountered errors relating to the entries table.

ERROR 1214 (HY000): The used table type doesn't support FULLTEXT indexes.

I then encountered subsequent errors because the table was not created.

On the reference pages above, I found:

FULLTEXT indexes. The NDB storage engine does not support FULLTEXT indexes, which are possible for MyISAM tables only.

I believe their intent is to build this thing up so it is very scalable since they really do not know how many sites they will ultimately run from this rack of servers (I think there are 6 servers).

Can anyone tell me what this error message means and what I need to do next?

Hi!

I haven'T done this myself yet, but I believe that a replicating Master/Slave system is most usable for this, so that you have X MySQL servers with their own database tables that synchronize each other.

This is a bit like a cluster, but more with "usual" installations and all possibilities of MyISAM tables...

Regards,
Garvin

But than you have to differenciate between write and read because you have one master, that gets all the writes and several slaves, that only do the read-statements.

Thanks guys. Is this what is referred to as MySQL replication? Does this approach require some modification to serendipity or our configuration files to achieve this master/slave replication?

Or just to put this another way, again what they are trying to accomplish within this hardware environment is load balancing and scalability across an unknown number of high volume serendipity sites. If the approach(s) I have mentioned so far as not the correct course of action, please feel free to suggest an alternative approach with respect to the configuration of apache, php, mysql, etc.

Yes, this is called replication, and yes, you have to do some modifications to s9y. First you should reduce writing, when easily possible (f.e. no visitor logging to db) because the masterserver is your bottleneck. Secondly you have to patch the db-abstraction layer to connect to 2 servers if needed and to send writing-queries to the master server and reading-queries to one of the slaves.

So I guess this diagram depicts it quite well....

Problem is, there is no way I could possibly implement this change on my own, nor do I know if such a change is warranted? Would anyone else benefit from this? Do you guys see this as beneficial to s9y as a project?

I guess the bigger question is whether or not this approach should even be considered. They are clearly trying to load balance mySQL.... should I be looking at something other than a change to s9y's code?

Braaiiiiiins....

I think we could implement this in a new database type, like mysql_replicated. It would check the first word of each query and decide whether it's a writing or reading query. Then it would choose an appropriate server to connect with and send it the query.

Problem is, we'd need to modify the admin configuration to allow multiple servers, and specify which are masters and which are slaves.

Additionally, implementing kleinerChemister's write reduction would involve modifying the entire codebase. Or removing log features. Whichever.

Hopefully, someone knows more about MySQL load balancing than I do. I'd be setting up a bunch of virtual servers and limiting their CPU usage. Then no s9y code needs to be changed at all.

Edit: maybe using a MySQL Proxy would help. It does all that hard work for us.

...rarrgh! (Returns to grave to await the next nightfall)

I don't think we have to remove logging as you can configure it to files or turn it off, as far as I remember.

When changeing the Admin for more servers, I would suggest to simply make it an array and always use the [0] as master, because you only have one master and multiple slaves.

For the db-type I would simply check, if the query starts with SELECT, or else route it to the master.

Some time ago, there was someone how wanted the same for an other database.

Thanks again. Modifying s9y code for this seems to be limited in value to others, so I think the best course of action will be to investigate MySQL Proxy as Judebert suggested... that has the potential to benefit more than just s9y in the event there are other scripts on the server using mySQL.