Joomla MySQL replication support

[Ofer Cohen]

Hey All,

I'm wondering if someone start to add support in Joomla for MySQL master-slave and master-master replication.

I think there are 2 options:

1. Use mysqlnd-ms php driver (replication and load balancing plugin for mysqlnd), which add support on the server configuration.
   The advantage here is we need only to configure it on server level and change the connection string at PHP level (or connection function). In addition, there is support for master-master and you can configure more than 2 servers easily.
   The disadvantage that mysqlnd_ms need to enable on the server.
2. Use native mysql php driver and balanced the writes to the master connection, and the reads to slave (or slaves).
   The advantage there is no need for special server configuration nor drivers.
   The disadvantage here that we should take care for load balancing and reliable of the connections.

IMHO, because replication is required by big websites in most cases, I'll go with option #1, because who need replication support will just have to install and configure the mysqlnd-ms php driver (PECL), and it's required less development on the Joomla side - add driver for mysqlnd_ms and add ability in the Joomla global configuration, including installation stage.

What do you think?

Thanks,

Ofer Cohen

[Pawel Panek]

Hello,

It's an old idea to split reads and writes on mysql. Drupal say they already have such db driver, can't find it's docs now. As you pointed out you need to decide where you want to do R/W splitting - server or application side.

For #1 the same idea goes with mysql-proxy and their r/w splitting lua script. But there's something wrong with server side solutions for R/W splitting - Limitations! The mysqlnd-ms has following limitations:

"The built-in read-write-split mechanism is very basic. Every query which starts with SELECT is considered a read request to be sent to a MySQL slave server. All other queries (such as SHOW statements) are considered as write requests that are sent to the MySQL master server. The build-in behavior can be overruled using SQL hints, or a user-defined callback function.

The read-write splitter is not aware of multi-statements. Multi-statements are considered as one statement. The decision of where to run the statement will be based on the beginning of the statement string. For example, if using mysqli_multi_query() to execute the multi-statement SELECT id FROM test ; INSERT INTO test(id) VALUES (1), the statement will be redirected to a slave server because it begins with SELECT. The INSERT statement, which is also part of the multi-statement, will not be redirected to a master server."

Is there any way to prevent 3rd party extensions from failing because of custom and complex mysql queries?

In this case the bast way is #2. Decision what query(ies) go to master and what go to slave should be done on application level. Application side driver can track mysql 'transactions' and issue all the queries to the same node in case we are dealing with something more complicated than just simple SELECT.

In my opinion the best server side r/w splitting mechanism for mysql is mysql-proxy with their rw split lua script  https://wikis.oracle.com/display/mysql/MySQL+Proxy+RW+Splitting. It's not perfect and I think it can't be used in hosting environment but you're free to write something better than that.

Regards,

Pawel Panek

[Júlio Pontes]

Pawel,

I've worked on a project that use a solution like this. I've created a solution like this.

[Ofer Cohen]

Julio, can you elaborate more about that?

Thanks, Ofer

On Tuesday, August 6, 2013, Júlio Pontes wrote:

Pawel,

I've worked on a project that use a solution like this. I've created a solution like this.

--
Framework source code: https://github.com/joomla/joomla-framework
Visit http://developer.joomla.org for more information about developing with Joomla!
---
You received this message because you are subscribed to the Google Groups "Joomla! Framework Development" group.
To unsubscribe from this group and stop receiving emails from it, send an email to joomla-dev-frame...@googlegroups.com.
Visit this group at http://groups.google.com/group/joomla-dev-framework.
 
 

--

Ofer Cohen

http://embad.com 
Facebook | Twitter | LinkedIn

[Ofer Cohen]

Patel, few comments about your comments.

Joomla MySQL driver doesn't support mysqli_multi_query, so it's not an issue for the framework. 

I think is enough to consider select query as read, and others as write.

In addition, we don't need to change any code of mysqli driver because we need to change the host to the mysqlns_nd config alias. I will publish article about that soon.

Another option beside the two we mentioned is PDO, but I didn't check it yet. More to come...

Ofer

On Monday, August 5, 2013, Pawel Panek wrote:

Hello,

It's an old idea to split reads and writes on mysql. Drupal say they already have such db driver, can't find it's docs now. As you pointed out you need to decide where you want to do R/W splitting - server or application si

--

Framework source code: https://github.com/joomla/joomla-framework
Visit http://developer.joomla.org for more information about developing with Joomla!
---
You received this message because you are subscribed to the Google Groups "Joomla! Framework Development" group.
To unsubscribe from this group and stop receiving emails from it, send an email to joomla-dev-frame...@googlegroups.com.
Visit this group at http://groups.google.com/group/joomla-dev-framework.