如何在PHP中实现后台/异步后写式缓存? [英] How to implement background/asynchronous write-behind caching in PHP?

问题描述

我有一个特殊的PHP页面，出于各种原因，该页面需要将大约200个字段保存到数据库中.这些是200个单独的插入和/或更新语句.现在显而易见的事情是减少该数量，但是，正如我说的那样，出于我不会打扰的原因，我不能这样做.

I have a particular PHP page that, for various reasons, needs to save ~200 fields to a database. These are 200 separate insert and/or update statements. Now the obvious thing to do is reduce this number but, like I said, for reasons I won't bother going into I can't do this.

我没想到这个问题.选择在MySQL中似乎表现合理，但插入/更新却没有(执行此更新大约需要15-20秒，这自然是不可接受的).我编写的Java/Oracle系统可以愉快地同时进行数千次插入/更新(在两种情况下都运行本地数据库； MySQL 5 vs OracleXE).

I wasn't expecting this problem. Selects seem reasonably performant in MySQL but inserts/updates aren't (it takes about 15-20 seconds to do this update, which is naturally unacceptable). I've written Java/Oracle systems that can happily do thousands of inserts/updates in the same time (in both cases running local databases; MySQL 5 vs OracleXE).

现在，使用Java或.Net之类的东西，我可以很容易地执行以下操作之一:

Now in something like Java or .Net I could quite easily do one of the following:

1. 将数据写到内存中 后写缓存(即 知道如何持久化到数据库 并可以异步执行)；
2. 将数据写入内存缓存 并使用PaaS(Persistence作为 服务)模型，即 缓存将保留字段；或
3. 只需启动后台进程 可以保留数据.

1. Write the data to an in-memory write-behind cache (ie it would know how to persist to the database and could do so asynchronously);
2. Write the data to an in-memory cache and use the PaaS (Persistence as a Service) model ie a listener to the cache would persist the fields; or
3. Simply start a background process that could persist the data.

最小的解决方案是拥有一个我可以简单更新的缓存，它将在自己的时间内分别进行数据库更新(即在更新内存中的缓存后立即返回).这可以是全局高速缓存或会话高速缓存(尽管全局共享高速缓存确实以其他方式具有吸引力).

The minimal solution is to have a cache that I can simply update, which will separately go and upate the database in its own time (ie it'll return immediately after update the in-memory cache). This can either be a global cache or a session cache (although a global shared cache does appeal in other ways).

还有其他解决此类问题的方法吗?

Any other solutions to this kind of problem?

推荐答案

您应该能够相对较快地完成200次插入，但这将取决于许多因素.如果您使用的是事务引擎，并且每个事务都在自己的事务中进行，则不要-这样会产生太多的I/O.

You should be able to do 200 inserts relatively quickly, but it will depend on lots of factors. If you are using a transactional engine and doing each one in its own transaction, don't - that creates way too much I/O.

如果您使用的是非事务引擎，那会有些棘手.使用单个多行插入可能会更好，因为MySQL的刷新策略意味着它不需要在每一行之后刷新其更改.

If you are using a non-transactional engine, it's a bit trickier. Using a single multi-row insert is likely to be better as the flushing policy of MySQL means that it won't need to flush its changes after each row.

您真的希望能够在生产规格开发箱上重现此错误并准确分析其发生的原因.应该不难停止.

You really want to be able to reproduce this on your production-spec development box and analyse exactly why it's happening. It should not be difficult to stop.

当然，另一种可能性是，由于表的大小过大或索引数量过多，因此插入速度很慢-在这种情况下，您应该适当地扩展数据库服务器.通常，在表的索引不适合RAM的表中插入很多行(或者没有正确配置RAM以用于缓存那些索引的表)会很麻烦.

Of course, another possibility is that your inserts are slow because of extreme sized tables or large numbers of indexes - in which case you should scale your database server appropriately. Inserting lots of rows into a table whose indexes don't fit into RAM (or doesn't have RAM correctly configured to be used for caching those indexes) generally gets pretty smelly.

当有一种方法可以轻松地使应用程序保持当前算法不变时，不要尝试寻找使您的应用程序复杂化的方法.

BUT don't try to look for a way of complicating your application when there is a way of easily turning it instead, keeping the current algorithm.

这篇关于如何在PHP中实现后台/异步后写式缓存?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！