Very bad performance leading to "lost connection to mysql server" and weewx endless loop

[Luftfahrtverein Grünstadt]

Hi all,

after updating our raspberry pi (apt-get update & upgrade) I experience very bad performance of weewx resulting in an endless loop of retrying.

Here is an excerpt of the log:

Mar 18 08:10:18 raspberrypi weewx[467]: manager: Added record 2020-03-18 08:10:00 CET (1584515400) to database 'd0222d5f'
Mar 18 08:10:21 raspberrypi weewx[467]: manager: Added record 2020-03-18 08:10:00 CET (1584515400) to daily summary in 'd0222d5f'
Mar 18 08:11:00 raspberrypi weewx[467]: cheetahgenerator: Generated 8 files for report SeasonsReport in 39.21 seconds
^[[AMar 18 08:14:36 raspberrypi weewx[467]: imagegenerator: Generated 42 images for SeasonsReport in 215.72 seconds
Mar 18 08:14:36 raspberrypi weewx[467]: copygenerator: copied 5 files to /var/www/html/weewx
Mar 18 08:14:40 raspberrypi weewx[467]: GaugeGenerator: Generated 7 images for HTMLPages in 3.42 seconds
Mar 18 08:14:40 raspberrypi weewx[467]: translategenerator.pyc: Language is german
Mar 18 08:14:48 raspberrypi weewx[467]: historygenerator.pyc: Generated 6 tables in 7.08 seconds
Mar 18 08:15:02 raspberrypi weewx[467]: engine: Main loop exiting. Shutting engine down.
Mar 18 08:15:02 raspberrypi weewx[467]: engine: Shutting down StdReport thread
Mar 18 08:15:14 raspberrypi weewx[467]: cheetahgenerator: Generated 10 files for report HTMLPages in 33.72 seconds
Mar 18 08:15:14 raspberrypi weewx[467]: copygenerator: copied 3 files to /var/www/html/weewx/Bootstrap
Mar 18 08:15:14 raspberrypi weewx[467]: translategenerator.pyc: Language is german
Mar 18 08:15:22 raspberrypi weewx[467]: engine: Unable to shut down StdReport thread
Mar 18 08:15:22 raspberrypi weewx[467]: engine: Database connection exception: (2013, 'Lost connection to MySQL server during query')
Mar 18 08:15:22 raspberrypi weewx[467]:     ****  Waiting 2 minutes then retrying...

As you can see generating images / files took very very long (e.g. 215 seconds for seasonsreport). I successfully run weewx for 4 years now and It was never that slow.

A few weeks ago I moved from sqlite to mysql without any issues and it worked well, so I do not hink this has any relation to the problem.

In addition to the bad performance (i think it is a folllow-up problem) I got database connection exceptions (2013, lost connection during query), but adding records to the database work fine. Is there a way to set a higher connection timeout value?

I tried already rebuilding the daily values several times, setting the archive interval to 5 min (before it was 1 min) but nothing helps.

Any help is really appreciated! What do you need to know?

Thank you so much!

Sebastian

[Thomas Keffer]

I take it you are running MySQL on a Raspberry Pi? Could be that the SD card is failing.

--
You received this message because you are subscribed to the Google Groups "weewx-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to weewx-user+...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/weewx-user/570b85ea-14ce-4dd0-b5c7-354d72e7b7d1%40googlegroups.com.

[Luftfahrtverein Grünstadt]

Hi Thomas,

thank you for your answer.

No, the MySQL database is not on the pi, it is externally hosted.

To unsubscribe from this group and stop receiving emails from it, send an email to weewx...@googlegroups.com.

[Thomas Keffer]

Well, there is something wrong on the external server or, possibly, the connection between.

In general, installations that use MySQL are less reliable than those using SQLite. See the Wiki article SQLite vs MySQL.

-tk

To unsubscribe from this group and stop receiving emails from it, send an email to weewx-user+...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/weewx-user/fd6846ee-f9ba-458d-ba3a-252e630718b2%40googlegroups.com.

[Luftfahrtverein Grünstadt]

okay, since it is a paid service in a large data center I will give them a call.

Is there a way for getting more debug information, e.g. log the fired sql queries to fire them manually directly on the database?

To view this discussion on the web visit https://groups.google.com/d/msgid/weewx-user/fd6846ee-f9ba-458d-ba3a-252e630718b2%40googlegroups.com.

[Thomas Keffer]

If you are using a database connection to a remote server in the cloud, you can almost surely expect poor performance.

MySQL servers typically have logs for queries. Can't speak for where your data center put them, but they are usually in /var/log/mysql or something similar.

-tk

To unsubscribe from this group and stop receiving emails from it, send an email to weewx-user+...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/weewx-user/95da1267-40fd-44b0-bf62-6a796a8710b4%40googlegroups.com.

[Luftfahrtverein Grünstadt]

I got an answer from the data center which confirm that everything is running fine on their side.

Apparently something got wrong after updating the pi - does anyone know if there were recent updates on the pi, e.g. concerning a firewall or virus protection which could lead to the bad performance (also for the image generation)?