I have an interesting problem I'd like to find a solution to.
I have a MySQL server running on Ubuntu (16.04). I also have a number of servers that store system information like CPU temperature, network traffic data & CPU loads in a database on the MySQL server. To accomplish this I have a couple of Python programs running on each server that harvest the data and push it to the database.
On those servers I've also got a number of scripts that periodically query the database for historical data that they then graph and present on a webpage.
The way I've set this up right now results in all the servers tending to query the database at about the same time. This causes a high load on the MySQL server followed by a long period of virtually no load.
What options are there for me (preferably client-side Python) that can help me spread the load on the SQL server more evenly?
This is generally known as a stampeding or thundering herd problem. All of a sudden a ton of clients want something, and then activity goes back to nothing. There are different ways of coping with that. If you have some intermediary between the client and the server, e.g. a load balancer, you can use that to spread the load around and perhaps even use it to spin up new server instances as needed.
In the case of a direct client-MySQL connection that typically isn't an option. Perhaps you can switch to read-only replicated slaves, which can more easily absorb the impact (i.e. scale horizontally). Or you get a bigger server which has a better peak-load performance. Of course, if 99% of the time there's no load whatsoever, these solutions aren't very cost-effective.
The cheap solution is to avoid all clients stampeding towards the server at the same time. Either offset each client individually (e.g. schedule their cron job for 0 * * * *, 5 * * * *, 10 * * * * etc.), or simply delay each client by a random amount each time (sleep(randint(0, 360)) in Python, sleep $((RANDOM % 360)) && ... in bash).
Client-side (Python) load-balancing a MySQL server