[library] DB grew large within 1 week, filled root filesystem

[Andrew Woodward]

After 1 week, the MySQL db grew to almost 30gb, filling the root fileystem

root@node-3:/var/lib/mysql# du -sh *
260K cinder
0 debian-5.5.flag
129M galera.cache
80K glance
4.0K GRA_25_9143377.log
4.0K GRA_6_9282638.log
4.0K grastate.dat
132K heat
29G ibdata1
5.0M ib_logfile0
5.0M ib_logfile1
224K keystone
1.1M mysql
508K neutron
1.5M nova
212K performance_schema
4.0K test
root@node-3:/var/lib/mysql# du -sh .
29G .
root@node-3:/var/lib/mysql#

There are two inherent problems, one is that we place the DB on the Small root partition, but send all of the remaining space to /var/lib/glance

The second is that we are using a single ibdata1 file
http://stackoverflow.com/questions/3456159/how-to-shrink-purge-ibdata1-file-in-mysql

[OpenStack Infra (hudson-openstack)]

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

[OpenStack Infra (hudson-openstack)]

Reviewed: https://review.openstack.org/73563
Committed: https://git.openstack.org/cgit/stackforge/fuel-library/commit/?id=b7504a2672cc73f4cc1430d9eedfeb4e7f5576d1
Submitter: Jenkins
Branch: master

commit b7504a2672cc73f4cc1430d9eedfeb4e7f5576d1
Author: Vladimir Kuklin <email address hidden>
Date: Fri Feb 14 14:04:17 2014 +0400

    Mysql performance and disk optimization

    1. Use per table innodb option
    2. Use Barracuda file format

    Change-Id: I42eaa51aa80853f609f34d8028eed32f1ec8a2dc
    Partial-bug: #1278964

[Vladimir Kuklin (vkuklin)]

Partially fixed for 4.1. Further optimizations post-poned to 5.0.

[Vladimir Kuklin (vkuklin)]

Do we still face the issue? What are the current details on DB growth?

[Andrew Woodward (xarses)]

We still don't have a good disk allocation for this. 50gb is too small for any reasonable sized install. We either need to track this here or on https://bugs.launchpad.net/bugs/1306792

[Sergii Golovatiuk (sgolovatiuk)]

There were a couple of improvements in Fuel 5.0. The most important one is

commit 0a3cdce60389ea0a6adc8f97afc7e2e9bfd25210
Change-Id: I511f5849206455d65ca11ecbb8e8c1c3f639fbcb

This change introduces memcache as a storage for Keystone tokens. It's the most disk consuming table. After this implementation we tested how quick MySQL fills in disk space. The result was really good even if you have a lot of operations with openstack.

[Andrew Woodward (xarses)]

No, this is not resolved. We still do not have a sufficient default disk allocation for DB on controller nodes.

We still need:
1) A seperate lvm for /var/lib (so it can be resized with out being taken offline)
2) Better sizing for the volume in which the DB lies

When there is tons of space on the node (and ceph isn't used) it is all given to glance for images. The DB needs a better share of the disk than ~45Gb in this configuration.

If the defalt allocation remained, but /var/lib is still moved to a lvm, we can at least online resize the volume so it's not as painful to deal with wen you run out of space.

[Sergii Golovatiuk (sgolovatiuk)]

We are in process of adding MySQL 5.6 to master. I fixed spec for CentOS and control for Ubuntu. Now, it's possible to create separate partition /var/lib/mysql in Disk Manager.

[Vladimir Kozhukalov (kozhukalov)]

Andrew, resize2fs can expand the size of mounted file system for ext3 and ext4, so you can resize root filesystem while it is mounted. It is not the issue here.

But I'd rather agree that /var (not /var/lib) needs to be a separate file system.
Currently root file system is disk size * 0.2 and >15G and <50G
My suggestion is to make root file system equal to 15G (fixed size) and /var size equal to disk size * 0.2 - 15G and >15G and <30G. Other suggestions?

[OpenStack Infra (hudson-openstack)]

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

[OpenStack Infra (hudson-openstack)]

Change abandoned by Vladimir Kozhukalov (<email address hidden>) on branch: master
Review: https://review.openstack.org/110778
Reason: That is not supposed to be a proper fix because we install os on a plain partition in case of ubuntu.

[Dmitry Borodaenko (angdraug)]

Proposed change was abandoned, resetting status to Triaged.

[Mike Scherbakov (mihgen)]

Dmitry, Vladimir K., since we have IBP, can't we accept https://review.openstack.org/#/c/110778/ as a fix?

[Vladimir Kozhukalov (kozhukalov)]

This original issue was partly fixed by this https://review.openstack.org/73563. And the topic was converted into "Our current partitioning scheme is very sub-optimal". This sub-optimal default scheme will be fixed in 7.0.

[Aleksandr Didenko (adidenko)]

We're going to apply a work-around while waiting for a feature.

[OpenStack Infra (hudson-openstack)]

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

[Vladimir Sharshov (vsharshov)]

Return to the command, because Alexander Kislitsky is on vacation. Also Vladimir Kozhukalov set -1 for review. Please check BVT and review for more details.

[Fedor Zhadaev (fzhadaev)]

We need to create separate image for /var if we want to use separate volume for it. I work on it.

[Fedor Zhadaev (fzhadaev)]

Important question. Do we really need the separate volume for /var when we already have separate volume for /var/lib/mysql?

[OpenStack Infra (hudson-openstack)]

Reviewed: https://review.openstack.org/204091
Committed: https://git.openstack.org/cgit/stackforge/fuel-web/commit/?id=b7ab2e992f90ced87c013ab529a8fa62f224d8dd
Submitter: Jenkins
Branch: master

commit b7ab2e992f90ced87c013ab529a8fa62f224d8dd
Author: Alexander Kislitsky <email address hidden>
Date: Tue Jul 21 16:50:38 2015 +0300

    Mount point /var/log added for controller

    As workaround before volume manager refactoring we add
    /var/log mount point for role controller.

    Change-Id: I65c1c2f627a0124ed1f2d09d82df397012fe87f3
    Closes-Bug: #1278964
    Closes-Bug: #1394864

[Peter Zhurba (pzhurba)]

[root@fuel-slab2 ~]# head /etc/fuel/version.yaml
VERSION:
  feature_groups:
    - mirantis
  production: "docker"
  release: "7.0"
  openstack_version: "2015.1.0-7.0"
  api: "1.0"
  build_number: "288"
  build_id: "288"
  nailgun_sha: "93477f9b42c5a5e0506248659f40bebc9ac23943"
[root@fuel-slab2 ~]# for i in node-{1,2,4}; do ssh $i "df -h /var/lib/mysql"; done
Warning: Permanently added 'node-1' (RSA) to the list of known hosts.
Filesystem Size Used Avail Use% Mounted on
/dev/mapper/mysql-root 20G 3.0G 16G 16% /var/lib/mysql
Warning: Permanently added 'node-2' (RSA) to the list of known hosts.
Filesystem Size Used Avail Use% Mounted on
/dev/mapper/mysql-root 20G 3.0G 16G 16% /var/lib/mysql
Warning: Permanently added 'node-4' (RSA) to the list of known hosts.
Filesystem Size Used Avail Use% Mounted on
/dev/mapper/mysql-root 20G 3.3G 16G 18% /var/lib/mysql
[root@fuel-slab2 ~]#

[Peter Zhurba (pzhurba)]

mysql volumes are separated

[Peter Zhurba (pzhurba)]

root@node-1:~# mysql -e "SHOW VARIABLES LIKE 'innodb_file_format'"
+--------------------+-----------+
| Variable_name | Value |
+--------------------+-----------+
| innodb_file_format | Barracuda |
+--------------------+-----------+
root@node-1:~#
root@node-1:~# mysql -e "SELECT count(*) FROM information_schema.TABLES where engine='InnoDB' ;"
+----------+
| count(*) |
+----------+
| 363 |
+----------+
root@node-1:~# mysql -e "SELECT count(*) FROM information_schema.TABLES;"
+----------+
| count(*) |
+----------+
| 497 |
+----------+
root@node-1:~#

Most part of the tables use innodb

[OpenStack Infra (hudson-openstack)]

Related fix proposed to branch: master
Review: https://review.openstack.org/223465

[OpenStack Infra (hudson-openstack)]

Reviewed: https://review.openstack.org/223465
Committed: https://git.openstack.org/cgit/stackforge/fuel-docs/commit/?id=8ec103a9468e3e9d773ed8c2c24bbc7295ba23ab
Submitter: Jenkins
Branch: master

commit 8ec103a9468e3e9d773ed8c2c24bbc7295ba23ab
Author: evkonstantinov <email address hidden>
Date: Tue Sep 15 10:50:20 2015 +0300

    Add DB growth resolved issue to relnotes

    Change-Id: I6fe933ca0084c4df9b75131279f1a8aae840ef9c
    Related-Bug:#1278964