Xenial - Can't connect to local MySQL server through socket /var/run/mysqld/mysqld.sock

[Ryan Beisner]

Xenial-Mitaka full deploy tests (non-ha) are failing as of Jul 4 (\o/), but were passing on Jul 2 before the commit below [1].

percona-cluster (mysql) services are not started and running when shared_db_changed tries to connect and configure.

2016-07-04 22:45:58 INFO shared-db-relation-changed _mysql_exceptions.OperationalError: (2002, "Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)")

2016-07-04 22:45:58 ERROR juju.worker.uniter.operation runhook.go:107 hook "shared-db-relation-changed" failed: exit status 1

# Reproducer bundle (xenial-mitaka target)
http://bazaar.launchpad.net/~ost-maintainers/openstack-charm-testing/trunk/view/head:/bundles/sparse/next.yaml#L164

# Juju stat, full trace & other info
See attached.

# Possibly began with:
[1] https://github.com/openstack/charm-percona-cluster/commit/789719bbac42fc91eca787bcfb98d9f10207265e

[David Ames (thedac)]

This is a configuration problem.

First, we should be using innodb-buffer-pool-size rather than dataset-size.

Second, the max connections settings has memory implications. It takes a little more than 2MB [1] for each connection. So setting max-connections to 20000 requires > 5G of memory just for mysql.

The error we see is InnoDB failing to allocate memory:

2016-07-07 22:08:43 28260 [Note] InnoDB: Initializing buffer pool, size = xM InnoDB: mmap(x bytes) failed; errno 12

The solution is balancing the amount of memory on a system/instance and the two settings innodb-buffer-pool-size and max-connections.

[1] http://www.mysqlcalculator.com/

[David Ames (thedac)]

Beyond the memory configuration issue, percona cluster was quietly failing to bootstrap. The problem was brought to light but not caused by a recent commit which added a service stop of mysql before the bootstrap.

Systemd systems try to run: systemctl bootstrap-pxc mysql
Which returns: Unknown operation bootstrap-pxc.

Fix in progress to both handle systemd and be more verbose when a bootstrap fails.

[OpenStack Infra (hudson-openstack)]

Fix proposed to branch: master
Review: https://review.openstack.org/339823

[Ryan Beisner (1chb1n)]

A test gap exists which is why this was not detected earlier. The corresponding tracking bug: https://bugs.launchpad.net/charms/+source/percona-cluster/+bug/1546577

[OpenStack Infra (hudson-openstack)]

Fix proposed to branch: stable/16.04
Review: https://review.openstack.org/340613

[OpenStack Infra (hudson-openstack)]

Reviewed: https://review.openstack.org/339823
Committed: https://git.openstack.org/cgit/openstack/charm-percona-cluster/commit/?id=dd9f4e7b71d293e52245040a9c27e7e085f76316
Submitter: Jenkins
Branch: master

commit dd9f4e7b71d293e52245040a9c27e7e085f76316
Author: David Ames <email address hidden>
Date: Fri Jul 8 11:27:35 2016 -0700

    Fix boostrap pxc on systemd

    Percona-cluster was quietly failing to bootstrap. The problem was brought to
    light but not caused by a recent commit which added a service stop of mysql
    before the bootstrap.

    Systemd systems try to run: systemctl bootstrap-pxc mysql
    Which returns: Unknown operation bootstrap-pxc.

    This fix both handles systemd bootstrapping and is more verbose when a bootstrap fails.

    Change-Id: Ifdaa3114fd2de315d2da8d2bd6a807e503d26dce
    Partial-Bug: 1600001

[OpenStack Infra (hudson-openstack)]

Change abandoned by David Ames (<email address hidden>) on branch: stable/16.04
Review: https://review.openstack.org/340613
Reason: Fix to this bug is in master and will be release in 16.07. Abandoning this PR.

[OpenStack Infra (hudson-openstack)]

Fix proposed to branch: master
Review: https://review.openstack.org/417131

[OpenStack Infra (hudson-openstack)]

Reviewed: https://review.openstack.org/417131
Committed: https://git.openstack.org/cgit/openstack/charm-percona-cluster/commit/?id=e47c4ad13517996963867e6d6a4765a0a8aa29f4
Submitter: Jenkins
Branch: master

commit e47c4ad13517996963867e6d6a4765a0a8aa29f4
Author: David Ames <email address hidden>
Date: Thu Jan 5 10:08:55 2017 -0800

    Default performance schema to off

    The 5.6 version of mysql defauls to performance_schema=on at server
    startup. This allocates all the memory that would be required to
    handle max-connections plus several other memory settings.

    We occasionally saw connection exhaustion in HA clouds with
    max-connections near 2000. The common practice became to set
    max-connections unrealistically high near 10k or 20k. In the move to
    5.6 on Xenial this became a problem as the memory requirements for
    values that high are unrealistic.

    This change adds performance schema as a configuration option and
    defaults to off giving 5.5 like behavior. It also considerably
    updates documentation and highlights the need to carefully consider
    memory related configuration settings for production environments.

    Make the amulet test python2 and python3 capable.

    Change-Id: Id01095ac2f73fa041b25b2602496a4c04a13e3ef
    Partial-Bug: #1654086
    Closes-Bug: #1600001