Search code examples
phpandroidmysqldatabasebandwidth

Updating Database every X seconds

First off, I'm building the application in Android, and using PHP as my web-services and database communication, I'm using MySQL database.

Alright, here's the question: I'm building a Near Real-Time application and I need to update a row in my Database every 5 seconds, diffrent row for each user in the same table.. What is the best way to optimize the costs? I mean, It's working fine for 10 users.. but my guess is when I'll get 50-100 the DB will crash. And another important issue is the bandwidth, I really want to use as less as I can.

Any advice? If I'll put all the requests into a queue intead of updating the specific row, will it prevent it from crashing? I mean.. there still will be 50 connections to the DB in the same time..

Solution

  • Ok, so - bandwidth and database usage.

    The first is easy. You may want to try and determine what's your optimal values. Some things to keep in mind from the very start:

    • Keep your protocol light. Check JSON - it's human-readable and yet compact enough. Or you may want to implement a binary load.
    • You may want to try and throw some flow control in the mix:
      • Client-side heuristics: What's my current write latency? Under X milliseconds, keep updating each 5 seconds. Over X, 10 seconds. Over 5x X, 30 seconds.
      • Server-side Quality of Service (QoS): Processor load over X%? Push a message to the clients, tell them to update every 10 seconds instead of 5.

    About the database - it can be tricky, but nothing is impossible. Some thoughts:

    • Cache your data access; if you're asking for record N, and there was no update for it, no need to hit the database again - just read from a memory cache.
    • Use in-memory objects to read/write, commit to database once the processor load is low, a time limit is reached, or N operations where processed.