PHP 7 - Storing data between requests for this particular program

Question

I have N devices sending their data via HTTP (POST) requests all to the same URL (lets call it 'upload.php'). Every minute, around 100-150 POST requests are made to upload.php. Suppose for the sake of example that the devices spotted balloons and sent out this data:

[ {
  "timestamp" : 1507320877294,
  "color" : "red",
  "balloon" : {
    "x" : 42,
    "y" : 312,
    "z" : 287,
  }
} ]

..

[ {
  "timestamp" : 1607320877294,
  "color" : "yellow",
  "balloon" : {
     "x" : 42,
     "y" : 312,
     "z" : 287,
  }
} ]

I have a table in a database that looks like this and aggregates the data the following way:

+---------------------------------------------------------------------+
| spotter_sensor_id | hour_collected | red_balloons | yellow_balloons |
+---------------------------------------------------------------------+
| 992               | 17:00:00       | 5            | 5               |
+---------------------------------------------------------------------+

I don't want to make an update/insert query each time upload.php receives a post request. I'd rather temporarily store the data and then every 10 minutes or so make one query like:

INSERT INTO table
    (spotter_sensor_id, hour_collected, red_balloons, yellow_balloons)
VALUES
    ('992', '$hour_collected', '$red_balloons', '$yellow_balloons')
ON DUPLICATE KEY UPDATE
    red_balloons = $red_balloons + VALUES(red_balloons),
    yellow_balloons = $yellow_balloons + VALUES(yellow_balloons);

...that holds how many balloons were spotted for each sensor during those 10 minutes.

What are my options for storing intermediate data from upload.php to another script (lets call it 'send.php') using PHP 7? Preferably an option where data deadlocking or concurrency won't be an issue?

Note: my environment is Windows Server 2016 with PHP 7. I have looked at OPCache but I haven't found a DLL that works for PHP 7—an unofficial one I forked off GitHub has been very buggy.

Each execution of upload.php is unique so... Temp file, temp database table are obvious ones. I wonder if there is something else... — Nic3500, Oct 19 '17 at 16:16
Read up: https://stackoverflow.com/questions/4089361/how-can-i-store-data-in-ram-memory-using-php — Nic3500, Oct 19 '17 at 16:18
How about using an in-memory database? I've seen a few implementations with [Redis.io](https://redis.io/) — Gabriel Heming, Oct 19 '17 at 18:46
Why, what's your reasoning behind this - any sort of performance issues, or ...? — CBroe, Oct 19 '17 at 23:31
@CBroe I have a few dozen sensors sending data each second, and can't afford making a SQL query every time a sensor uploads its data. It's better aggregating it and sending the data off in one clump sql command every 5 minutes — Jebathon, Oct 23 '17 at 13:46

score 0 · Answer 1 · answered Nov 06 '17 at 13:18

You should be aware that 3 insert queries per seconds is very low for a RDBMS to handle (or else you just need to spend 10 bucks for an extra RAM module :)

If you really hit a bottleneck (because your hardware is massively undersized, or because in fact you have hundreds, not dozens, of inserts per seconds), then you may need to switch technology. A relational database may not be the right solution. A Redis cache was advised, you may also try a noSQL solution, or an in-memory table. But the simplest solution may be just consolidating the data at emitter side (your sensor).

PHP 7 - Storing data between requests for this particular program

1 Answers1