Lost connection to MySQL under High load


#1

Hi,
I have recently seen lots of “lost connection to MySQL server” messages during queries.
I am using shinxql , thread_pool on a sigle server. I think the issue may be due to load spikes.
The error is fired during a mysqli->query call

Is there a way to log information to get more details about what is happening ?
Query log does not show the queries that fail so it may be a communication issue of the MySQL protocol.

It could be related to mysqli, manticore server or MySQL (unlikely as it should not be involved) but it is hard at this stage to identify the culprit.

Thanks
Roberto


#2

Anything interesting in the searchd log for the time when it happened?


#3

lost connection to MySQL server message might be crash that cause daemon restarts and drop all alive connections.

It might be better to look at searchd.log for any crash log messages and in case of these messages create ticket at Github


#4

Hi,

thanks for the replies, there were some crashes but when I tried to run the queries from the mysql client all seemed fine.

I believe the issue is with a problem affecting SSL sites related to the Comodo service (which should now be fixed) which caused a huge number of timeouts.

I will keep monitoring and see if the problem appears again now that the SSL part is working fine.

Thanks for now
Robert


#5

Update:

  • I do see a searchd crash (at 15:59), (I am creating a bug report now)
  • following the crash I see a lot of
    [02-May-2019 16:00:02 Europe/Rome] PHP Warning: mysqli::query(): MySQL server has gone away …
    [02-May-2019 16:00:02 Europe/Rome] PHP Warning: mysqli::query(): Error reading result set’s header in …

This makes sense as searchd is starting up after the crash and not yet handling connections.

  • There is no " MySQL server has gone away" just before the crash so this is not what is causing the crash
  • There are still lots of " MySQL server has gone away" afterwards when searchd is up and running

So I am not sure if the 2 problems are in any way related.

I tried to run the query that caused the crash from the mysql client and it runs fine.

I just would like to have a way to get some more meaningful messages/logs and debug it properly.

Also the problem appeared out of nowhere, no changes to config or else (load is high but not unusual), I thought it was related to a TLS problem we were having but that is now fixed and the searchd issue is still there.

Sorry I can’t provide much more info, any ideas are welcome

Thanks
Roberto


#6

I think drop of connection at your case related to crash of daemon you might check search.log and compare time between lines FATAL: CRASH DUMP and accepting connections as this period daemon restarts and reading index data. In case that period matches time then your application failed too that is due to daemon restart.

You might provide more details to investigate that crash or put couple of daemons after load balancer to always provide response to your app.