Welcome to the Cumulus Support forum.

Latest Cumulus MX V3 release 3.28.6 (build 3283) - 21 March 2024

Cumulus MX V4 beta test release 4.0.0 (build 4017) - 17 March 2024

Legacy Cumulus 1 release v1.9.4 (build 1099) - 28 November 2014 (a patch is available for 1.9.4 build 1099 that extends the date range of drop-down menus to 2030)

Download the Software (Cumulus MX / Cumulus 1 and other related items) from the Wiki

MySQL Timeouts Continue

From build 3044 the development baton passed to Mark Crossley. Mark has been responsible for all the Builds since. He has made the code available on GitHub. It is Mark's hope that others will join in this development, but at the very least he welcomes your ideas for future developments (see Cumulus MX Development suggestions).

Moderator: mcrossley

stefanbagnato
Posts: 159
Joined: Sun 08 May 2016 9:11 pm
Weather Station: Vantage Pro 2
Operating System: Windows Server 2019
Location: Raleigh, NC

MySQL Timeouts Continue

Post by stefanbagnato »

I migrated CMX last night to a brand new server. The old server was on it's last days and I had been getting MySQL timeouts for months. I noticed this morning that I am seeing the timeouts again on the new server. Log shows "Error encountered during MySQL operation = The Command Timeout expired before the operation completed". I know for a fact that I have no resource issues on this new box. Other than just removing the alarm from being visible, is there something I can do to track this "timeout"?
User avatar
mcrossley
Posts: 12689
Joined: Thu 07 Jan 2010 9:44 pm
Weather Station: Davis VP2/WLL
Operating System: Bullseye Lite rPi
Location: Wilmslow, Cheshire, UK
Contact:

Re: MySQL Timeouts Continue

Post by mcrossley »

The error would imply either a network issue between CMX and the MySQL (I cannot remember if they are co-hosted?), or some issue in the MySQL service.

Have you checked the MySQL error log?
stefanbagnato
Posts: 159
Joined: Sun 08 May 2016 9:11 pm
Weather Station: Vantage Pro 2
Operating System: Windows Server 2019
Location: Raleigh, NC

Re: MySQL Timeouts Continue

Post by stefanbagnato »

Mark, sorry, should have noted everything is self hosted. The only "interesting" info that mysql tells me is that the client aborts the connection before closing the connection properly. Which I can't really make sense of. I mean I guess technically I could lower how often I am updating, but why would I want to do that haha? That's the point of self-hosting with high grade hardware, so I can push CMX as hard as it allows. It doesn't appear that there's any real "issue" per say other than the error being logged and the alert going off, both of which I can just hide. But I do feel as though it may be something that needs resolving at some point.
stefanbagnato
Posts: 159
Joined: Sun 08 May 2016 9:11 pm
Weather Station: Vantage Pro 2
Operating System: Windows Server 2019
Location: Raleigh, NC

Re: MySQL Timeouts Continue

Post by stefanbagnato »

I am looking into the Binlog cache disk use property. Don't know that this will be the magic fix, but if I can adjust that parameter, maybe it'll help? The top parameter, Aborted clients, is the mysql-side of the CMX error I am seeing.
Capture.PNG
You do not have the required permissions to view the files attached to this post.
User avatar
mcrossley
Posts: 12689
Joined: Thu 07 Jan 2010 9:44 pm
Weather Station: Davis VP2/WLL
Operating System: Bullseye Lite rPi
Location: Wilmslow, Cheshire, UK
Contact:

Re: MySQL Timeouts Continue

Post by mcrossley »

So CMX is saying it isn't getting a response from the server, and the server is saying CMX has aborted the connection!

How frequently are you making the updates? Are they overlapping?
stefanbagnato
Posts: 159
Joined: Sun 08 May 2016 9:11 pm
Weather Station: Vantage Pro 2
Operating System: Windows Server 2019
Location: Raleigh, NC

Re: MySQL Timeouts Continue

Post by stefanbagnato »

I know, right! Weird.

Can you clarify what you mean by making updates? Are you referring to my station log interval (1 min)? Or how often I'm writing to 'monthly' and 'realtime' (I believe 30s and 15s respectively, but I don't recall the setting in CMX off the top of my head and "trying" to listen to Webex I'm supposed to be paying attention to :lol: )
freddie
Posts: 2434
Joined: Wed 08 Jun 2011 11:19 am
Weather Station: Davis Vantage Pro 2 + Ecowitt
Operating System: GNU/Linux Ubuntu 22.04 LXC
Location: Alcaston, Shropshire, UK
Contact:

Re: MySQL Timeouts Continue

Post by freddie »

My realtime interval is 2 seconds, and I perform 4 sizeable database inserts at that interval to a database on my local network. If it's high end kit you have and your database is on your local network, then you either have a network issue or there is something amiss with MySQL.
Freddie
Image
stefanbagnato
Posts: 159
Joined: Sun 08 May 2016 9:11 pm
Weather Station: Vantage Pro 2
Operating System: Windows Server 2019
Location: Raleigh, NC

Re: MySQL Timeouts Continue

Post by stefanbagnato »

Nevermind on the monthly comment, not sure what I was talking about there... and yes, realtime is 15s
stefanbagnato
Posts: 159
Joined: Sun 08 May 2016 9:11 pm
Weather Station: Vantage Pro 2
Operating System: Windows Server 2019
Location: Raleigh, NC

Re: MySQL Timeouts Continue

Post by stefanbagnato »

freddie wrote: Thu 18 Aug 2022 6:04 pm My realtime interval is 2 seconds, and I perform 4 sizeable database inserts at that interval to a database on my local network. If it's high end kit you have and your database is on your local network, then you either have a network issue or there is something amiss with MySQL.
Hey Freddie, don't disagree. Yeah I mean it's nothing super magical, but it's a dedicated box so I agree with you, has to be a mysql config issue. That "fun" part is, what?? I (maybe wrongly) assumed the issue cropped up on the old server months back due to how bogged down it was (literally it took 60s to open the Start menu...), but this one is a 6-core i7 with 32gb of ram and almost nothing else running outside of CMX, MySQL, IIS, and an open source internal mail server. I don't know that there's any increase in logging I can do from the CMX side that I haven't already, so may just have to start messing with mysql to see what I can tweak
User avatar
mcrossley
Posts: 12689
Joined: Thu 07 Jan 2010 9:44 pm
Weather Station: Davis VP2/WLL
Operating System: Bullseye Lite rPi
Location: Wilmslow, Cheshire, UK
Contact:

Re: MySQL Timeouts Continue

Post by mcrossley »

I can't possibly see that 15 seconds should be a issue, I used 5 seconds on my old pi3 and that was also running CMX, two PHP based websites, and my pi-hole.
User avatar
mcrossley
Posts: 12689
Joined: Thu 07 Jan 2010 9:44 pm
Weather Station: Davis VP2/WLL
Operating System: Bullseye Lite rPi
Location: Wilmslow, Cheshire, UK
Contact:

Re: MySQL Timeouts Continue

Post by mcrossley »

Are you using the standard real-time MySQL or custom inserts?
stefanbagnato
Posts: 159
Joined: Sun 08 May 2016 9:11 pm
Weather Station: Vantage Pro 2
Operating System: Windows Server 2019
Location: Raleigh, NC

Re: MySQL Timeouts Continue

Post by stefanbagnato »

mcrossley wrote: Thu 18 Aug 2022 6:28 pm Are you using the standard real-time MySQL or custom inserts?
Standard.

Also, they stopped! It took me a bit of messing around as I couldn't edit my.ini manually or via command line (manually it "didn't work" - I had to have messed "something up", don't know don't care) (command line it just never seemed to take my commands). However, I figured out how to edit all of the parameters via MySQL Workbench.

Based off the screenshot I attached earlier, I honed in on the binlog_cache_size parameter which defaults to 32768. I doubled this to 65536, confirmed my.ini actually took the update, restarted the service, and just looking now maybe 30 mins later, the alarm stopped.

I have a couple of work-related items to take care of, so I am going to leave everything as is until this evening and then check back on the performance metrics ion mysql as well as review the cmx debug log. Wondering if that was the "issue" all along? Either way a new build was in order, and I wouldn't really be able to reproduce everything on the old box without swapping back over and I am not about to go through that. But this will be a good thing to monitor personally and also good for anybody else who might happen to be on the exact same config.
freddie
Posts: 2434
Joined: Wed 08 Jun 2011 11:19 am
Weather Station: Davis Vantage Pro 2 + Ecowitt
Operating System: GNU/Linux Ubuntu 22.04 LXC
Location: Alcaston, Shropshire, UK
Contact:

Re: MySQL Timeouts Continue

Post by freddie »

I'm not convinced, as my binlog_cache_size is set to the default value and I have a 2-second realtime interval using the standard realtime update plus three custom updates. My kit is nothing special (HP Proliant Microserver from about 2014), and I run the database in a container. Fair enough if the tweak works, but I'd be keeping an open mind and looking at other possibilities.
Freddie
Image
stefanbagnato
Posts: 159
Joined: Sun 08 May 2016 9:11 pm
Weather Station: Vantage Pro 2
Operating System: Windows Server 2019
Location: Raleigh, NC

Re: MySQL Timeouts Continue

Post by stefanbagnato »

freddie wrote: Thu 18 Aug 2022 8:53 pm I'm not convinced, as my binlog_cache_size is set to the default value and I have a 2-second realtime interval using the standard realtime update plus three custom updates. My kit is nothing special (HP Proliant Microserver from about 2014), and I run the database in a container. Fair enough if the tweak works, but I'd be keeping an open mind and looking at other possibilities.
Don't disagree. CMX log has been clean this evening. Slammed with work tomorrow but I'll dig through some of the additional parameters in mysql to see what I can find. May just need to read up on the logging functionality and implement some of that. Maybe I'm good to go, but maybe it comes back. Guess we'll see?

BTW, I am curious as to your 2s realtime? Why did you go with 2s? I went with 15s since that's the update time of the VP2 wind data. I assume you have something that updates more frequently?
freddie
Posts: 2434
Joined: Wed 08 Jun 2011 11:19 am
Weather Station: Davis Vantage Pro 2 + Ecowitt
Operating System: GNU/Linux Ubuntu 22.04 LXC
Location: Alcaston, Shropshire, UK
Contact:

Re: MySQL Timeouts Continue

Post by freddie »

stefanbagnato wrote: Fri 19 Aug 2022 1:53 amBTW, I am curious as to your 2s realtime? Why did you go with 2s? I went with 15s since that's the update time of the VP2 wind data. I assume you have something that updates more frequently?
The VP2 sensors actually send updates every 2.5 seconds. That's why I use 2s realtime interval. See https://www.davisinstruments.com/pages/ ... e-interval
Freddie
Image
Post Reply