MySQL connection issues

MySQL connection issues

by Tony Yarusso -
Number of replies: 7

We recently started having intermittent errors connecting to the database, and I can't figure out why.  This started after some maintenance on the SAN backing our VMware environment, but I don't have any other evidence that the two events are related.  Both the Moodle server and the MySQL server are VMs in that environment.  This is what I've found in the logs so far:

[Thu Jan 02 01:58:08 2014] [error] PHP Warning:  mysqli::mysqli(): (HY000/2003): Can't connect to MySQL server on 'x.x.x.x' (110) in /var/www/moodle/lib/dml/mysqli_native_moodle_database.php on line 296
[Thu Jan 02 01:58:08 2014] [error] PHP Warning:  mysqli::close(): Couldn't fetch mysqli in /var/www/moodle/lib/dml/mysqli_native_moodle_database.php on line 336

All of my Googling on that error has turned up results with firewalls and other immediately post-installation issues, but this works generally, and only fails on some queries, so I'm not sure.  It seems like maybe it's some kind of resource limit on the MySQL side or a really terrible slow query, but I haven't found anything yet.  Any pointers?

Moodle release: 2.1.1+ (Build: 20110907)

Average of ratings: -
In reply to Tony Yarusso

Re: MySQL connection issues

by Howard Miller -
Picture of Core developers Picture of Documentation writers Picture of Particularly helpful Moodlers Picture of Peer reviewers Picture of Plugin developers

Are you running with full developer debugging on (it might show more detail)?

What's in your MySQL logs when this happens? 

I'm assuming that your MySQL server is on a different host to the web server? What's between them? 

Have you got any monitoring on either machines (e.g. munin) checking numbers of connections, load, etc.? If you do then what does it say? If you don't then you should wink

Average of ratings: -
In reply to Howard Miller

Re: MySQL connection issues

by Tony Yarusso -

Developer debugging: Not yet - have to talk to somebody else about that piece.

MySQL logs: Just enabled them this evening, and got this:  140102  2:15:01 [Warning] Aborted connection 2275 to db: 'whtwtrlrndb01' user: 'whtwtrlrn' host: 'x.x.x.y' (Got an error reading communication packets)

Hosts: Yes, separate hosts, with just the VMware virtual switch between them.  Same subnet.

Monitoring: Nagios is watching memory and load, which seem pretty normal.  I'll have to add MySQL-specific checks like number of connections tomorrow, although considering it's 2AM during a holiday break, I doubt those are super high.

Average of ratings: -
In reply to Tony Yarusso

Re: MySQL connection issues

by Howard Miller -
Picture of Core developers Picture of Documentation writers Picture of Particularly helpful Moodlers Picture of Peer reviewers Picture of Plugin developers

It's not really a job for Nagios as that really only looks out for failure conditions. I'm really talking about more generally seeing what's going on over time. Look at munin wink

Just repeating my point about developer debugging. Even if you just log the output (don't tick display errors).

What's your mysql max_allowed_packet size set to? I think Moodle recommends 4M minimum but I have seen discussed that imply it could need to be much larger.

After that you are probably looking at VMWare issues. I always getting a bad feeling when Moodle is run on VMs. I imagine that they two VM's are on the same physical hardware? What is the separation giving you other than extra complexity?

Average of ratings: -
In reply to Howard Miller

Re: MySQL connection issues

by Jon Witts -
Picture of Particularly helpful Moodlers Picture of Plugin developers Picture of Testers

We use nagiosgraph to map our monitoring from Nagios over time... works very nicely for us and plugs into our existing Nagios checks too...

Average of ratings: -
In reply to Howard Miller

Re: MySQL connection issues

by Tony Yarusso -

Nagios is also graphing, just like Munin would.

max_allowed_packet is set to 16M.

The two VMs are on the same hardware, and the reason for the separation is that the MySQL server is used by several different things, not just this Moodle installation.  (Although so far this Moodle site seems to be the only one having issues.)

Average of ratings: -
In reply to Tony Yarusso

Re: MySQL connection issues

by Ken Task -
Picture of Particularly helpful Moodlers

Howard has already mentioned max_packets so that is something to check.

VMWare could also be involved.   Another guest OS on the same VMWare server could 'spill over' (lack of a better term).   Any clues/hints there?Then there is this:

http://dev.mysql.com/doc/refman/5.0/en/communication-errors.html

which does mention this:

"Use of Ethernet protocol with Linux, both half and full duplex. Many Linux Ethernet drivers have this bug. You should test for this bug by transferring a huge file using FTP between the client and server machines. If a transfer goes in burst-pause-burst-pause mode, you are experiencing a Linux duplex syndrome. Switch the duplex mode for both your network card and hub/switch to either full duplex or to half duplex and test the results to determine the best setting."

'spirit of sharing', Ken

Average of ratings: Useful (2)
In reply to Tony Yarusso

Re: MySQL connection issues

by Tony Yarusso -

So, to follow up on this, we discovered it was something much simpler (and dumber).  Somebody had powered up an old clone of this VM, with the same IP address, so there were conflicting servers on the IP but connected to the same database, so it kind of worked but not really.  *sigh*

Average of ratings: -