1

Hope someone can help me debug this issue. There is a MySQL server 5.6.22 running on OS X 10.2. The load isn't too heavy. All databases are stored in InnoDB. From time to time it stop accepting connections from PHP applications and said applications return with a 2006 "gone away" error. Both PHP and MySQL are on the same machine. External connections are not allowed.

It works for a while after restart and then some arbitrary amount of hours later it starts denying some connections. If I keep it running like that some time later the ratio of denied vs accepted connections grows to the point of the server becoming unusable.

Connecting via the command line works. Nothing in the error logs (warning mode turned on). Restarting the server helps. "FLUSH TABLES" helps equally good. What's strange is if I issue "FLUSH TABLES tb_name" it suddenly starts denying all connections via PHP (not just some of them) until I issue "FLUSH TABLES" without a db name. I tried flushing every table sequentially by name but it doesn't help.

None of the suggestions from MySQL documentation or tinkering with various related variables helped.

This started happening when the OS was 10.9 and I think MySQL server got upgraded to a newer version at some point, although I am not 100% certain if that was the thing that introduced the unstable behavior.

I tried looking at global status, current connections et cetera - nothing out of the ordinary. I.e. the server doesn't get flooded with connections that don't get closed for example and I set a very short timeout for it anyway. It also doesn't have too many open files. The system is also mostly idling, lots of available CPU time and memory.

Any suggestions on where to look to get the root of the problem? It's not nice that the logs don't show anything and basically the only clue I have at the moment is that "FLUSH TABLES" works its magic, for whatever reason. If only I could break that command down to bits and execute them separately to see what part alleviates the problem...

Any suggestions on where to start looking?

Improve this question
8
  • There is a page about MySQL server has gone away. Maybe you have already tried the page advices. Do the PHP connections returns error immediatly or after some delay? What are the tables storage engine? Have you checked "show open tables"? Perhaps there are some ongoing transactions, you could check information_schema.innodb_locks. – Giovanni Feb 20 '15 at 12:58
  • Yes, I've seen that page of course... Tried every advice. PHP connections return error immediately, there's no wait at all. The tables use InnoDB everywhere. Show open tables shows... opened tables - nothing out of the ordinary, although I'm not sure whether they're supposed to be immediately closed after a data has been pulled in by PHP or not. If they're indeed supposed to be closed right after the connection then I have all of them permanently opened after the first connection was made. indoor_locks is empty, FLUSH QUERY CACHE makes no difference, I'll try RESET QUERY CACHE next time. – Захар Joe Feb 20 '15 at 13:06
  • Perhaps mysqld have hit the maximum number of open descriptors. Could you check ulimit -n from terminal and show variables like 'open_files_limit'? You can monitor also Opened table status with f.e. mysqladmin -r -i 1 ext | grep Opened_tables after `flush tables'. – Giovanni Feb 20 '15 at 13:53
  • I remember setting the file limit several months ago and it didn't have any effect. I'll try it again. open_files_limit is set to a very high value. mysqladmin command showed 10980 on the first line and zeros on all subsequent lines. Not sure what that means exactly. – Захар Joe Feb 20 '15 at 14:01
  • Also, doesn't ulimit specifically restricts the stats to the shell? What I'd probably be looking for is to change system-wide file limit. So sysctl -A | grep kern.maxfiles outputs a healthy 10240 file limit and mysql currently has about 800 files open (out of total 8000 files open currently). I've tried setting innodb_file_per_table ON and OFF - no effect. – Захар Joe Feb 20 '15 at 14:12
2

So the answer is that really you should only use MariaDB on OSX. Neither Percona or official MySQL distribution will handle the gone away error which arises due to a different mechanism of file locking. All detailed here: https://bugs.mysql.com/bug.php?id=79125

MySQL guys are reluctant to issue a fix but apparently MariaDB had this fixed for a long time. I use this in production and never had a problem since. And just in case, I didn't use ports or brew to install it, simply compiled from source after installing cmake. Hope this helps someone.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.