Forums

So I have a script that is scraping the currently playing song from radio station's website and writing details to MySQL. I have it scheduled every hour but it runs continually for that hour and usually writes in a range of every 3-6 minutes. At first, I opened a new connection to the database whenever I wrote and everything worked perfectly, but found myself being thrown into the tarpit often and figured this could be a large culprit of that. So I refactored my code to open one connection at the beginning and now if there is any more than 5 minutes between writes, I get the "_mysql_exceptions.OperationalError: (2006, 'MySQL server has gone away')" error. I assume this is because I am timing out for some reason, but I checked my wait_timeout value and it is still set at 6 hours. Any ideas on what is causing this? I plan on adding a try/except to catch when this happens and reconnect, but would still like to know what is going on. Thanks

Our MySQL servers are configured to kill idle connections after 5 minutes, as unfortunately quite a lot of people write code that opens but doesn't close connections so without the timeout the servers get overloaded. (Open connections use quite a lot of RAM.)

If you use SQLAlchemy, I think it can manage connections for you and will keep one live and re-start it when necessary transparently.

OTOH I don't think opening a MySQL connection is all that CPU-intensive. Perhaps there's another cause for the excessive CPU usage -- an inadvertent busy-wait somewhere or something?

Awesome thanks for the great response, I'll look into SQLAlchemy. I think a lot of my usage comes from the fact that its always running, but wanted to try to trim as much fat as I could.

No problem, just let us know if you have any problems with it!

Looks like the "recycle" argument might do the trick? http://docs.sqlalchemy.org/en/latest/core/pooling.html#setting-pool-recycle