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 4019) - 03 April 2024
Legacy Cumulus 1 release 1.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
Latest Cumulus MX V3 release 3.28.6 (build 3283) - 21 March 2024
Cumulus MX V4 beta test release 4.0.0 (build 4019) - 03 April 2024
Legacy Cumulus 1 release 1.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
Moderator: mcrossley
-
- 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
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"?
- mcrossley
- Posts: 12765
- 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
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?
Have you checked the MySQL error log?
-
- 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
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.
-
- 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
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.
You do not have the required permissions to view the files attached to this post.
- mcrossley
- Posts: 12765
- 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
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?
How frequently are you making the updates? Are they overlapping?
-
- 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
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 )
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 )
-
- Posts: 2476
- 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
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.
-
- 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
Nevermind on the monthly comment, not sure what I was talking about there... and yes, realtime is 15s
-
- 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
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 tweakfreddie 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.
- mcrossley
- Posts: 12765
- 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
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.
- mcrossley
- Posts: 12765
- 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
Are you using the standard real-time MySQL or custom inserts?
-
- 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
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.
-
- Posts: 2476
- 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
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.
-
- 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
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?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.
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?
-
- Posts: 2476
- 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
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-intervalstefanbagnato 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?