Database Error

[AlexJ]

I am seeing below consistent problems... trying to track down, which query is triggering.. any suggestions? I have a feeling, it's coming from download. 

2022-07-13T06:44:02.365652Z 8488613 [Note] Aborted connection 8488613 to db: 'xxxx' user: 'abc' host: 'localhost' (Got timeout reading communication packets)
2022-07-13T09:59:02.386169Z 8499671 [Note] Aborted connection 8499671 to db: 'xxxx' user: 'abc' host: 'localhost' (Got timeout reading communication packets)
2022-07-13T16:16:01.951564Z 8530536 [Note] Aborted connection 8530536 to db: 'xxxx' user: 'abc' host: 'localhost' (Got timeout reading communication packets)
2022-07-13T16:59:07.843974Z 8535076 [Note] Aborted connection 8535076 to db: 'xxxx' user: 'abc' host: 'localhost' (Got timeout reading communication packets)
2022-07-13T17:59:41.600576Z 8542128 [Note] Aborted connection 8542128 to db: 'xxxx' user: 'abc' host: 'localhost' (Got timeout reading communication packets)
2022-07-13T18:54:44.054694Z 8547447 [Note] Aborted connection 8547447 to db: 'xxxx' user: 'abc' host: 'localhost' (Got timeout reading communication packets)

Any help is appreciated.

[Miss_B]

This seems to be server/host related. What is the wait_timeout value set to?

You can read more about this here:

https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_wait_timeout

 

[Randy Calvert]

It's likely the mysql server on the host is too busy and the response timed out.  

[AlexJ]

2 hours ago, Miss_B said:

This seems to be server/host related. What is the wait_timeout value set to?

You can read more about this here:

https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_wait_timeout

 

That's set to 30 sec. 

1 hour ago, Randy Calvert said:

It's likely the mysql server on the host is too busy and the response timed out.  

I don't think so. 

-------- Performance Metrics -----------------------------------------------------------------------
[--] Up for: 69d 3h 48m 43s (347M q [58.114 qps], 8M conn, TX: 5865G, RX: 542G)
[--] Reads / Writes: 96% / 4%
[--] Binary logging is disabled
[--] Physical Memory     : 62.6G
[--] Max MySQL memory    : 25.0G
[--] Other process memory: 0B
[--] Total buffers: 14.6G global + 71.2M per thread (150 max threads)
[--] P_S Max memory usage: 72B
[--] Galera GCache Max memory usage: 0B
[OK] Maximum reached memory usage: 21.1G (33.71% of installed RAM)
[OK] Maximum possible memory usage: 25.0G (39.93% of installed RAM)
[OK] Overall possible memory usage with other process is compatible with memory available
[OK] Slow queries: 0% (365/347M)
[OK] Highest usage of available connections: 62% (94/150)
[OK] Aborted connections: 0.26%  (22371/8770303)
[!!] Query cache may be disabled by default due to mutex contention.
[!!] Query cache efficiency: 0.2% (653K cached / 299M selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (175K temp sorts / 64M sorts)
[!!] Joins performed without indexes: 5410235
[!!] Temporary tables created on disk: 27% (6M on disk / 23M total)
[OK] Thread cache hit rate: 99% (94 created / 8M connections)
[OK] Table cache hit rate: 99% (837M hits / 839M requests)
[OK] table_definition_cache(4000) is upper than number of tables(1348)
[OK] Open file limit used: 5% (295/5K)
[OK] Table locks acquired immediately: 99% (17M immediate / 17M locks)

 

Spike is because I was running some longer queries. But overall not much longer running queries. 

Also no delays in acquiring locks:

Also plenty of CPU - so I don't see any resource constrain:

[Randy Calvert]

Try increasing the wait timeout to 60 seconds.  It's typically in /etc/my.cnf.  If the variable does not exist, add it.  

wait_timeout=60

Edited July 15, 2022 by Randy Calvert

[AlexJ]

20 hours ago, Randy Calvert said:

Try increasing the wait timeout to 60 seconds.  It's typically in /etc/my.cnf.  If the variable does not exist, add it.  

wait_timeout=60

Question is why to increase? To me no user is going to wait for 60 sec, staring at website... I would like to first understand, why query is taking 60 sec. 

I expect website to display in less then 10 sec at max and as fast as possible depending on latency, user location, etc. 

Slow query log is not helpful in finding slow queries cos it doesn't pin point issues for the logs which I shared. Anywhere else, I should look for?

[Randy Calvert]

16 minutes ago, AlexJ said:

Question is why to increase? To me no user is going to wait for 60 sec, staring at website... I would like to first understand, why query is taking 60 sec. 

That variable has nothing to do with an end user's wait time.  Per the mySQL documentation, it is:

Quote

The number of seconds the server waits for activity on a noninteractive connection before closing it.

It's how long mySQL will hold a connection waiting for more requests from the same server.  (In your case, it's ALWAYS going to be the same since it's localhost.). Keeping the connection open longer means your server does not have to keep opening new connections to mySQL and instead can just use an existing one for longer.  

Remember...  your server is serving multiple end users at once from the same machine.  PHP can use the same connection to mySQL to get data for multiple users at once.  

Edited July 16, 2022 by Randy Calvert

[AlexJ]

7 hours ago, Randy Calvert said:

It's how long mySQL will hold a connection waiting for more requests from the same server.  (In your case, it's ALWAYS going to be the same since it's localhost.). Keeping the connection open longer means your server does not have to keep opening new connections to mySQL and instead can just use an existing one for longer.

Thanks. I still have question around it.. please help clarify and I appreciate the feedback. 

wait_timeout closed idle state non interactive connection. So basically let's say, connection is idle after 10 sec, it can be closed. 

My above error seems like active connection is 'forcefully' closed? How extended wait_timeout will help? Just asking because in past, I had it at 90 and I still had similar errors. 

 

 

[Randy Calvert]

Your server is trying to create a new connection to itself because none of the existing open connections are valid. 

2022-07-13T06:44:02.365652Z 8488613 [Note] Aborted connection 8488613 to db: 'xxxx' user: 'abc' host: 'localhost' (Got timeout reading communication packets)

Instead of trying to force your server to make a brand new connection to MySQL (opening another door to itself)… increasing the timeout value helps it already have an available already open door to use.  

By the way… hit Google for that phrase “Got timeout reading communication packets”. It’s not just us saying this. 😉 

[AlexJ]

On 7/17/2022 at 12:19 AM, Randy Calvert said:

By the way… hit Google for that phrase “Got timeout reading communication packets”. It’s not just us saying this. 😉 

I had tried Google approach first and didn't work and hence created topic 😞 

Sadly, didn't help after making it back to 60 sec. So far moved from 90 sec to 15 sec and back to 60 sec. 

Error still remains. Any other tips, to pin point it? Thank you.

Edited July 19, 2022 by AlexJ

[Randy Calvert]

According to:

https://itecnote.com/tecnote/mysql-how-to-diagnose-cause-of-warning-aborted-connection-got-timeout-reading-communication-packets/

Their situation with that error was a result of too many connections as a result of orphaned connections as a result of a mail server.  Have you looked at the network connections (such as netstat) to see if it's an issue there?

Actually...  just found this:

https://super-unix.com/database/mariadb-aborted-connection-got-timeout-reading-communication-packets/

The reason for that messages is the client's inactivity. If no actions from the client for wait_timeoutseconds then connection will be closed with a message.

That is the normal behaviour and that message was hidden by default log_warnings = 1 before mariadb 10.2.24. All newer releases has log_warnings = 2 and logfile now is filled by "(Got timeout reading communication packets)".

Just change an option to log_warnings = 1 to rid of annoying messages.

Are you actually having problems with connections or just seeing something in the logs that looks bad?

[AlexJ]

Anh - that 2nd URL makes sense.

I don't have issues with TCP - since for email, I use AWS API. Plus all my TCP connections are mostly available through Cloudflare. To restrict DDoS events, I just had banned multiple data-center ASN plus it helps with spam protection as well, since majority of spammers, use Proxy. 

mysql> SHOW VARIABLES LIKE '%log_warn%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_warnings  | 2     |
+---------------+-------+
1 row in set (0.00 sec)

Time to set to 1 and monitor. 

Thanks for the help. 

Edited July 19, 2022 by AlexJ