How to implement background/asynchronous write-behind caching in PHP?

Question

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.

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).

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

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?

Answer 1

Just update your local cache (hopefully memcached) and then push the write requests through beanstalkd.

Answer 2

I would suspect a problem with your SQL inserts - it really shouldn't take that long. Would prepared queries help? Does your mysql server need some more memory dedicated to the keyspace? I think some more questions need asked.

Answer 3

How are you doing the inserts, are you doing one insert per record

mysql_query('INSERT INTO tableName VALUES(...)');
mysql_query('INSERT INTO tableName VALUES(...)');
mysql_query('INSERT INTO tableName VALUES(...)');
mysql_query('INSERT INTO tableName VALUES(...)');
mysql_query('INSERT INTO tableName VALUES(...)');

or are you using a single query

mysql_query('INSERT INTO tableName VALUES(...),(...),(...),(...)');

The later of the two options is substantially faster, and from experience the first option will cause it to take much longer as PHP must wait for the first query to finish before moving to the second and so on.

Answer 4

Look at the statistics for your database while you do the inserts. I'm guessing that one of your updates locks the table and therefor all your statements are queued up and you experience this delay. Another thing to look into is your index creation/updating because the more indices you have on a table, the slower all UPDATE and INSERT statements get.

Another thing is that I think you use MYISAM (table engine) which locks the entire table on UPDATE.I suggest you use INNODB instead. INNODB is slower on SELECT-queries, but faster on INSERT and UPDATE because it only locks the row it's working on and not the entire table.

Answer 5

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.

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.

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 tuning it instead, keeping the currently algorithm.

Answer 6

One more solution that you could use (instead of tuning mysql :) ) is to use some JMS server and STOMP connection driver for PHP for write data to database server in a asynchronous manner. ActiveMQ have built-in support for STOMP protocol. And there is StompConnect project which is STOMP proxy for any JMS compilant server (OpenMQ, JBossMQ etc).

Answer 7

Hi all

mysql_query('INSERT INTO tableName VALUES(...),(...),(...),(...)')

Above given query statement is better. But we have another solution to improve the performance of insert statement.Follow the following steps.. 1. You just create a csv(comma separeted delimeter file)or simple txt file and write all the data that you want to insert using file writting machanism (like FileOutputStream class in java). 2. use this command

LOAD DATA INFILE 'data.txt' INTO TABLE table2 FIELDS TERMINATED BY '\t';

3 if you are not clear about this command then follow the link given below

http://dev.mysql.com/doc/refman/5.1/en/load-data.html