使用Zend_Db读取/写入拆分 [英] Read/Write splits using Zend_Db

问题描述

我有一个成长中的PHP应用程序.该数据库曾经位于单个主服务器上，但是我们打算通过相当标准的主/从复制来更改数据库，以提高性能和HA.

I have a PHP application that has grown in size. The database used to be on a single master but we intend to change that with a fairly standard master/slave replication for performance and HA.

由于此应用程序读取量很大，因此我希望将读取委派给从属副本，并将写入操作交给主副本.

Since this app is read-heavy I would like to have the reads delegated to the slave replicas and writes going to the master.

该应用程序基于Zend Framework 1.1.10，并使用Zend_Db.

The app is based on Zend Framework 1.1.10 and uses Zend_Db.

在不过度重构代码的情况下，使该应用程序拆分读取和写入数据库的最佳策略是什么? (我意识到这里可能涉及一些重构).

What would be my best strategy for getting this app to split reads and writes to the DB without refactoring the code too much? (I realize there would probably be some refactoring involved here).

P.S:

我看过 MySQL代理，看来它可以通过坐姿透明地拆分读写在数据库服务器和应用程序之间，但是我不确定在生产环境中使用它的性能问题.有人有经验吗?

I have looked at MySQL Proxy and it seems it can transparently split reads and writes by sitting in between the DB server and the app, but I'm not sure about the performance issues using this in a production environment. Does anyone have experience with this?

推荐答案

正如您所说的，MySQlProxy可以作为解决方案，但我个人从未在生产中对其进行过测试.

As you said MySQlProxy can be a solution, but I personnaly never tested it out in production.

我在代码中使用2个Db连接来拆分写入和读取请求. 80％的常规任务是通过读取连接完成的.您可以使用 Zend_Application_Resource_Multidb 来处理该问题(对我来说，我很早以前就完成了这一部分，我只是在注册表中存储了第二个Db连接).

I use 2 Db connections in my code to split-out write and read requests. 80% of the usual tasks are done with the read connection. You could use the Zend_Application_Resource_Multidb to handle that (For me I've done this part long before and I simply store a second Db connection in the registry).

• 首先仅在以下位置限制您的用户权限 读取操作并创建另一个数据库 具有写权限的用户.
• 然后跟踪其中的每个写请求 您的代码(更新"，插入"， 删除"是一个不错的开始)，然后尝试 专门拨打所有这些电话 助手.
• 运行您的应用程序并观察其崩溃，然后解决问题:-)

• First limit your user rights only on read operation and create another db user with write authorization.
• then track every write request in your code ("update", "insert", "delete" is a good start) and try to make all these calls with a dedicated helper.
• run your app and watch it crash, then fix problems :-)

一开始就认为这个问题比较容易.例如:

It's easier when you think this problem in the beginning. For example:

• 我通常有一个Zend_Db_Table工厂，带有一个读"或写"参数，并给我一个正确的Zend_Db_Table的单例(一个双单例，它可以有一个读取实例和一个写入实例).然后，我只需要确保在使用写访问查询/操作时使用正确的初始化的Zend_Db_Table.请注意，将Zend_Db_Table用作单例时，内存使用情况要好得多.
• 我尝试在TransactionHandler中获取所有写操作.我在那里可以检查是否仅使用与正确连接链接的对象.然后在控制器上管理事务，但我从不尝试在数据库层中管理事务，所有启动/提交/回滚的想法都在控制器(或其他概念层，而不是DAO层)上完成.

最后一点，交易很重要.如果您要管理事务，则必须使用已启用写功能的连接发出在事务内部的读取请求.由于在事务处理之前完成的所有读取都应被视为已过时，并且如果您的数据库后端正在执行隐式锁，则必须发出读取请求才能获取该锁.如果您的数据库后端未执行隐式读取，那么您还必须在事务中执行行锁. 这意味着您不应该依赖SELECT关键字在只读连接上推送该请求.

This last point, transactions, is important. If you want to manage transaction it's important to make the READ requests INSIDE the transaction, with the WRITE-enabled connection. As all reads done before the transaction should be considered as outdated, and if your database backend is doing implicits locks you'll have to make the read request to get the locks. If your database backend is not doing implicit reads then you'll have to perform the row locks in the transaction as well. And that mean you should'nt rely on the SELECT keyword to push that request on the read-only connection.

如果您的应用程序中使用了很好的数据库层，则更改并不难.如果您对数据库/DAO层进行了混乱处理，那么...可能会更困难.

If you have a nice db layer usage in your application the change is not really hard to make. If you made chaotic things with your database/DAO layer then... it may be harder.

这篇关于使用Zend_Db读取/写入拆分的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！