Synchronize database with external API

Question

I currently own a website where players can look-up their statistics of a game. When someone that is not in my database yet requests their statistics, I am calling the API with a PHP cURL request and storing them in my database.

Then I have a cronjob that runs every minute, takes 1/5th of the players in the database and updates every user in the database with the external API (20 users each request).

This worked fine when my mysql database was small (1000/2000 users) but the amount of users is increasing really fast and will become a lot more soon, so I need to change how I am updating the users.

My cronjob currently calls a php script that sends cURL requests and updates the database with the response.

Would it be better to update the players every hour and change the php cronjob to a python cronjob? I could call the API when the user requests their statistics but the external API is very slow sometimes or does not respond and this won't allow me to create graphs.

Looking forward to some thoughts about this.

Answer 1

runs every minute

First I'd make sure if that's required. Querying an API aggressively will sometimes throttle you or drop your queries. (Depends on who is running it).

Regarding PHP and cURL that should be fine. I've written a similar system that queried over 200K character stats that way, but I just did it hourly in blocks of like 500 characters I think it was.

20 users each request

Find the minimum amount of data you need and try to query the maximum amount of characters you can query and use that. If 20 is it then that seems very low. Ensure you're building the query optimally also for their system. Sometimes, depending on the API, rather than querying for individual characters or groups of characters it's much faster to query for all the characters sorted by id (with a last login time > than say a few months back) with a start (offset from the beginning) and limit. (If the API supports that feature). This tends to allow you to query large blocks of characters quickly.

Also if possible query for different data at different rates. There's usually a lot of API data that rarely changes.

Lastly look for or request a WebSocket based API. A lot of games with real-time data are setting them up. These usually function like a message queue where you can subscribe to certain stat changes.

(Also one last comment. Look for their API discussion forum. Often the person managing it will post there and just tell you the optimal way they wish people to use the API. Your use case is fairly common for a lot of sites).