899498 - buildbot running out of disk space

Status: RESOLVED FIXED

(Data & BI Services Team :: DB: MySQL, task)

Description

[Brandon Johnson [:cyborgshadow]]

buildbot in scl3 is running out of disk space. I had to purge binary logs before 7 days to free up disk space even though expire_logs_days is set to 10.

To note:
- Disk usage was 90% of 218 GB
- binary logs are still about 20 GB in size.
- a 5 GB MyISAM table (build_properties) has a 15 GB Index file (why so many crazy indices?)
- a 15 GB table has a 30 GB index (builds)
- outside of /var/lib/mysql, the only items bigger than 30 MB are:
     35M	/root/mysql_grants.sql
     95M	/usr/lib/locale/locale-archive
     125M	/var/cache/yum/x86_64/6Server/rhel-x86_64-server-6/671a6d4910490b4ae7e218b25080bac41fbd2bc044247385b9050686acfcf85a-primary.xml.gz.sqlite
     34M	/var/lib/rpm/Packages
     1.7G	/var/log/mysql/mysql.err

When sheeri's back we'll need to either add more disk space, permanently reduce expire_logs_days via puppet, or come up with another solution.

Comment 1

[Sheeri Cabral [:sheeri]]

Adding Hal in case he has any ideas about possibly archiving/purging data.

I checked, and the tables aren't fragmented (easy to check, MyISAM has exact stats and INFORMATION_SCHEMA matches the size on disk).

CREATE TABLE `build_properties` (
  `property_id` int(11) NOT NULL,
  `build_id` int(11) NOT NULL,
  KEY `ix_build_properties_build_id` (`build_id`),
  KEY `ix_build_properties_property_id` (`property_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

The "builds" table has indexes on all but 2 of the 11 fields. So the question stands as to whether or not they're needed. There are performance schema tables/tools in MySQL 5.6 that make answering this kind of question a lot more efficient. I think we should keep triaging until we're on 5.6 and then figure out if there are unused indexes we can drop.

Comment 2

[Hal Wine [:hwine] use NI!]

Unless this can't wait until catlee is back next week, he's the best person. If it needs to be handled more urgently, please needinfo me

Comment 3

[Chris AtLee [:catlee]]

we're going to try and address this in an upcoming downtime (cf bug 617017)

Comment 4

[Sheeri Cabral [:sheeri]]

Unfortunately, we will need to revamp the process so we are able to delete data without the costly outer joins. We deleted some data, but not enough. We will be working on this issue, though.

Comment 5

[Sheeri Cabral [:sheeri]]

We deleted enough data for the disk to clear up on both buildbot1 and 2 a few weeks ago. Bug 617017 is still tracking the regular cleanup, so closing this issue isn't losing any information.