What to avoid in php/mysql/ajax when relying on heavy reads/writes

Question

Hi, i was wondering what the main things were to avoid when creating an webapplication that relies on heavy reads and writes every second.

Think of online gaming where money is involved.

For example you have an javascript that constantly updates the browser(1), There is a cronjob running that updates the db(2), There is user input submitted via ajax or POST(3), and you have multiple users performing all these actions every second(4).

There is also money involved so all the data has to be read and written the correct way(5). And in case of crashes this the data has to be restored the right way so backups(6) are important.

So many things to keep in mind when creating such a complex application, what are the things you should definitely keep in mind.

I would like to know what your opinion on this is, Thank you!

Answer 1

Cache: Use Memcached and try to hit the database as least as possible.

Use a fast webserver or webservices, different servers or scripts running on different ports to perform different tasks, without much bloat.

Answer 2

I'm not sure if I'm answering this question on "what to avoid", but rather "what to think about"...

• Look into caching - opcode caching for the php pages and memory caching (APC, memcache) for reference data and other "static" (or close to static data).
• Look into concurrency issues - make sure you have covered the cases where two people are updating at the "same time" and how to back one out.
• Look into mysql replication.
• Try to avoid too much ajax. Sometimes we get carried away with the amount of data updating on the screen because ajax is so simple. i.e., sometimes you can change the business logic / requirements slightly (if this is possible) in order to make the techincal aspects much simplier.
• Have good use cases with a complex system like this and test often.

Answer 3

If bandwidth is an issue consider gzipping your output to the browsers. Or if CPU power is an issue and gzip is enabled, disable it but risk increased bandwidth usage.

Set your static files such as css and images and javascript etc to cache on the browser and set to expire after a long time. This means repeat users wont use up your bandwidth etc to request the same version of a file they already have.

Run benchmarks on querys that you use. Optimise where ever possible. Maybe splitting up long queries into smaller ones could have a performance boost. Use a profiler like xdebug to find out your bottlenecks.

Use persistent sql connections to save resources on new connections on every load.

Create a mirror database and when ever you insert into your main database, insert the same data into the second one. Dont read from the second database as it serves as a backup. Try having the second database on a different server if you can. If a second database is not feasable, cron job backups like you mentioned should work well.

Next thing... is ajax really nessasary in your application? Will it work without it? Dont add unessasary bloat to your site with extra requests within ajax.

I cant write more as i dont know the specifics of your site / app.

Answer 4

Those are very small read and writes, though. You're (hopefully) only joining two or three tables.

Additionally, you could do everything for the game itself in memory, simply storing everything to a user session or a global cache. You'd then want to write the financial details to the database every time they change, but updating a single player's bankroll should be a one-or-two table join, tops. You'd probably also want a logging/transactions table, to see at what time what amount of money was transferred to someone's account.

Anyways, I'd not worry hugely about performance on short queries; databases are surprisingly good at that.