MySQL binary logs enabled by default

[Andrii Petrenko]

Default setting for MySQL enabled binary logging. Binary logging is not necessary for Galera replication and just waste disk space. in case of small disk on the controller and high activity of the cloud/zabbix can cause MySQL failure.

Details:

initial state:

71G /var/lib/mysql

Cleaned up by command

mysql> purge binary logs to 'mysql-bin.000078';
Query OK, 0 rows affected (10.18 sec)

After clean up:
38G /var/lib/mysql

Solving:
comments or remove from mysql config options:

log_bin
expire_logs_days
max_binlog_size

[Davanum Srinivas (DIMS) (dims-v)]

Andrii,

fyi - https://github.com/openstack/fuel-library/commit/9021076349fa8e6641338d9b3e8771688b9c43e0

https://bugs.launchpad.net/fuel/+bug/1420825

[Davanum Srinivas (DIMS) (dims-v)]

Andrii,

If you need something else, please propose a fix to deployment/puppet/galera/templates/wsrep.cnf.erb

Thanks,
Dims

[Andrii Petrenko (aplsms)]

for a galera replication we do not need binary logs, but we need binary logs in case customer will perform MySQL backup. I propose limit rotation period for 1 day, and set option for disable binary logging. Patch attached. Please take a look.

[Davanum Srinivas (DIMS) (dims-v)]

Andrii, I've submitted the patch upstream on your behalf - https://review.openstack.org/#/c/267246/

[Dmitry Mescheryakov (dmitrymex)]

Setting priority to high since the issue causes full OpenStack outage until manually fixed.

[Bogdan Dobrelya (bogdando)]

According to the Sergii Golovatiuk's comment:
Binary logs are used for IST. If we simple disable them galera won't be able to do IST anymore

[OpenStack Infra (hudson-openstack)]

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

[OpenStack Infra (hudson-openstack)]

Change abandoned by Davanum Srinivas (dims) (<email address hidden>) on branch: master
Review: https://review.openstack.org/267246
Reason: Since Andrii wrote the original patch in bug and has a new review up now in https://review.openstack.org/#/c/267695/ i'll abandon this.

[Davanum Srinivas (DIMS) (dims-v)]

Based on new comment from Sergii

Sergii Golovatiuk
11:01 AM
↩
Patch Set 2: -Code-Review
I checked the code of galera and mysql-wsrep patch. Both projects have not used binary-logs since 2012. Galera uses RBR events to do replication. Thanks to Kostya Kalin for helping to figure that out!

[Natalia Kivak (nkivak)]

hi, what is ETA to have this defect fixed in 8.0?

[Davanum Srinivas (DIMS) (dims-v)]

Natalia,

Please follow along on the review:
https://review.openstack.org/#/c/267695/

[OpenStack Infra (hudson-openstack)]

Reviewed: https://review.openstack.org/267695
Committed: https://git.openstack.org/cgit/openstack/fuel-library/commit/?id=9660e49bdf8229418450c9a1151ab217d357a797
Submitter: Jenkins
Branch: master

commit 9660e49bdf8229418450c9a1151ab217d357a797
Author: Andrii Petrenko <email address hidden>
Date: Thu Jan 14 08:30:54 2016 -0800

    Tweak MySQL logging configuration

    For a galera replication we do not need binary logs, but we need binary logs in
    case customer needs to perform MySQL backup. In this review we propose to limit
    rotation period for 1 day, and set option to disable binary logging by default.

    Change-Id: I001c87081a3b334eb8844abca60e468370ea5f24
    Closes-Bug: #1533805

[Alexey Stupnikov (astupnikov)]

I have looked at proposed patch and could say that it looks like an easy fix for fuel in stable/6.1 and stable/7.0 branches (although it has to be tested with different MySQL versions). On the other hand, it will not fix problems with existing MOS environments, because patched puppet manifest are used only during node's deployment.

[OpenStack Infra (hudson-openstack)]

Fix proposed to branch: stable/7.0
Review: https://review.openstack.org/269651

[OpenStack Infra (hudson-openstack)]

Fix proposed to branch: stable/7.0
Review: https://review.openstack.org/269657

[OpenStack Infra (hudson-openstack)]

Change abandoned by Alexey Stupnikov (<email address hidden>) on branch: stable/7.0
Review: https://review.openstack.org/269651

[OpenStack Infra (hudson-openstack)]

Fix proposed to branch: stable/6.1
Review: https://review.openstack.org/269666

[OpenStack Infra (hudson-openstack)]

Fix proposed to branch: stable/7.0
Review: https://review.openstack.org/269748

[OpenStack Infra (hudson-openstack)]

Change abandoned by Alexey Stupnikov (<email address hidden>) on branch: stable/7.0
Review: https://review.openstack.org/269657
Reason: Abandoned because of incorrect change-id. New review is https://review.openstack.org/#/c/269748/

[OpenStack Infra (hudson-openstack)]

Fix proposed to branch: stable/6.1
Review: https://review.openstack.org/269751

[OpenStack Infra (hudson-openstack)]

Change abandoned by Alexey Stupnikov (<email address hidden>) on branch: stable/6.1
Review: https://review.openstack.org/269666
Reason: Abandoned because of incorrect change-id. New review is https://review.openstack.org/#/c/269748/

[OpenStack Infra (hudson-openstack)]

Fix proposed to branch: stable/8.0
Review: https://review.openstack.org/269776

[Anton Matveev (amatveev)]

sla2 for MOS 7.0

[Alexey Stupnikov (astupnikov)]

Fuel-CI tests are failing for stable/6.1 branch patch. Filled bugs #1536178, #1536179, #1536180 and assigned them to fuel-ci team.

[Alexey Stupnikov (astupnikov)]

Didn't insert link to created bugs in the previous message, check bug #1536178, bug #1536179, bug #1536180

[Alexey Stupnikov (astupnikov)]

Retrigger didn't helped for 6.1 branch. Opened up new bugs: bug #1536216 and bug #1536222

[Dmitry Mescheryakov (dmitrymex)]

The fix for 8.0 is not yet merged: https://review.openstack.org/#/c/269776/

[OpenStack Infra (hudson-openstack)]

Reviewed: https://review.openstack.org/269748
Committed: https://git.openstack.org/cgit/openstack/fuel-library/commit/?id=8ce0804f21ec6f36dd2f3efc0ffac92adef5d034
Submitter: Jenkins
Branch: stable/7.0

commit 8ce0804f21ec6f36dd2f3efc0ffac92adef5d034
Author: Andrii Petrenko <email address hidden>
Date: Thu Jan 14 08:30:54 2016 -0800

    Tweak MySQL logging configuration

    For a galera replication we do not need binary logs, but we need binary logs in
    case customer needs to perform MySQL backup. In this review we propose to limit
    rotation period for 1 day, and set option to disable binary logging by default.

    Closes-Bug: #1533805

    (cherry picked from commit 9660e49bdf8229418450c9a1151ab217d357a797)

    Conflicts:
     tests/noop/spec/hosts/database/database_spec.rb

    Change-Id: I001c87081a3b334eb8844abca60e468370ea5f24

[OpenStack Infra (hudson-openstack)]

Reviewed: https://review.openstack.org/269751
Committed: https://git.openstack.org/cgit/openstack/fuel-library/commit/?id=da6950c86884e8ec2c923ca36e52cbc0dbbeeef5
Submitter: Jenkins
Branch: stable/6.1

commit da6950c86884e8ec2c923ca36e52cbc0dbbeeef5
Author: Andrii Petrenko <email address hidden>
Date: Thu Jan 14 08:30:54 2016 -0800

    Tweak MySQL logging configuration

    For a galera replication we do not need binary logs, but we need binary logs in
    case customer needs to perform MySQL backup. In this review we propose to limit
    rotation period for 1 day, and set option to disable binary logging by default.

    Closes-Bug: #1533805
    (cherry picked from commit 9660e49bdf8229418450c9a1151ab217d357a797)

    Conflicts:
     deployment/puppet/galera/manifests/init.pp
     tests/noop/spec/hosts/database/database_spec.rb

    Change-Id: I001c87081a3b334eb8844abca60e468370ea5f24

[OpenStack Infra (hudson-openstack)]

Reviewed: https://review.openstack.org/269776
Committed: https://git.openstack.org/cgit/openstack/fuel-library/commit/?id=3b905666d578f9ad9b23385d8ac94ae391fd854c
Submitter: Jenkins
Branch: stable/8.0

commit 3b905666d578f9ad9b23385d8ac94ae391fd854c
Author: Andrii Petrenko <email address hidden>
Date: Thu Jan 14 08:30:54 2016 -0800

    Tweak MySQL logging configuration

    For a galera replication we do not need binary logs, but we need binary logs in
    case customer needs to perform MySQL backup. In this review we propose to limit
    rotation period for 1 day, and set option to disable binary logging by default.

    Change-Id: I001c87081a3b334eb8844abca60e468370ea5f24
    Closes-Bug: #1533805
    (cherry picked from commit 9660e49bdf8229418450c9a1151ab217d357a797)

[Alexey Stupnikov (astupnikov)]

Fix verified for MOS 7.0, package fuel-library7.0-7.0.0-7263.1.git8ce0804.noarch.rpm.

[Aleksei Stepanov (penguinolog)]

Fix verified on MOS 8.0: fuel-8.0-509-2016-02-01_07-56-25.iso

[Dmitry (dtsapikov)]

Verified on release 6.1 + mu5

[Kyrylo Romanenko (kromanenko)]

I can't find any patchset for 9.0
https://review.openstack.org/#/q/Id4e50ef8e0b764a53a1aa5f819e562017616f7dd,n,z

[Kyrylo Romanenko (kromanenko)]

Set to incomplete because could not find where is fix for 9.0

[Alex Schultz (alex-schultz)]

So we don't have binary logs enabled in mitaka (see 9.0):
https://github.com/openstack/fuel-library/blob/stable/mitaka/deployment/puppet/osnailyfacter/manifests/database/database.pp#L30

Also this was the fix at the time:

https://review.openstack.org/#/c/267695/

but we rewrote the database task so see the previous link.

[Kyrylo Romanenko (kromanenko)]

Thanks for links!

cat /etc/fuel_build_id:
 495
cat /etc/fuel_build_number:
 495
cat /etc/fuel_release:
 9.0
cat /etc/fuel_openstack_version:
 mitaka-9.0
rpm -qa | egrep 'fuel|astute|network-checker|nailgun|packetary|shotgun':
 fuel-release-9.0.0-1.mos6349.noarch
 fuel-misc-9.0.0-1.mos8460.noarch
 python-packetary-9.0.0-1.mos140.noarch
 fuel-bootstrap-cli-9.0.0-1.mos285.noarch
 fuel-migrate-9.0.0-1.mos8460.noarch
 rubygem-astute-9.0.0-1.mos750.noarch
 fuel-mirror-9.0.0-1.mos140.noarch
 shotgun-9.0.0-1.mos90.noarch
 fuel-openstack-metadata-9.0.0-1.mos8743.noarch
 fuel-notify-9.0.0-1.mos8460.noarch
 nailgun-mcagents-9.0.0-1.mos750.noarch
 python-fuelclient-9.0.0-1.mos325.noarch
 fuel-9.0.0-1.mos6349.noarch
 fuel-utils-9.0.0-1.mos8460.noarch
 fuel-setup-9.0.0-1.mos6349.noarch
 fuel-provisioning-scripts-9.0.0-1.mos8743.noarch
 fuel-library9.0-9.0.0-1.mos8460.noarch
 network-checker-9.0.0-1.mos74.x86_64
 fuel-agent-9.0.0-1.mos285.noarch
 fuel-ui-9.0.0-1.mos2717.noarch
 fuel-ostf-9.0.0-1.mos936.noarch
 fuelmenu-9.0.0-1.mos274.noarch
 fuel-nailgun-9.0.0-1.mos8743.noarch