Open Stack

Excerpts from Qiming Teng's message of 2014-09-24 20:37:39 -0700:
> Hi,
> 
> Some weeks ago, I checked my then latest devstack install and I learned
> this: event support in Ceilometer is only available for sqlalchemy
> backend; mongodb backend was still under development.  I have been using
> MySQL during the past weeks and now I think I'm trapped by a performance
> problem of MySQL.
> 
> One or two Nova servers were launched and remain idle for about 10 days.
> Now I'm seeing a lot of data accumulated in db and I wanted to cleanse
> it manually.  Here is what I got:
> 
> mysql> select count(*) from metadata_text;
> +----------+
> | count(*) |
> +----------+
> | 25249913 |
> +----------+
> 1 row in set (3.83 sec)
> 
> mysql> delete from metadata_text limit 1000;
> Query OK, 1000 rows affected (0.02 sec)
> 
> mysql> delete from metadata_text limit 10000;
> Query OK, 10000 rows affected (0.39 sec)
> 
> mysql> delete from metadata_text limit 100000;
> Query OK, 100000 rows affected (2.31 sec)
> 
> mysql> delete from metadata_text limit 1000000;
> Query OK, 1000000 rows affected (25.32 sec)
> 
> mysql> delete from metadata_text limit 2000000;
> Query OK, 2000000 rows affected (1 min 16.17 sec)
> 
> mysql> delete from metadata_text limit 4000000;
> Query OK, 4000000 rows affected (7 min 40.40 sec)
> 
> There were 25M records in one table.  The deletion time is reaching an
> unacceptable level (7 minutes for 4M records) and it was not increasing
> in a linear way.  Maybe DB experts can show me how to optimize this?

Wow, you definitely do not want to be doing transactions like that on a
regular basis. It is just murder on performance and can be deadly for
things like replication. If you plan to do the whole table, just use
truncate table.

This is not unique to Ceilometer, and is in fact as old as databases
really.

There is a fantastic tool for doing it as efficiently as possible
though:

http://www.percona.com/doc/percona-toolkit/2.1/pt-archiver.html

It will try to order by the physical blocks in the table, and do small
chunks continuously to minimize the impact. You can also have it sleep
for an amount of time based on how long the last delete took, so that
you are responsive to server load and impact.

The approach pt-archiver uses should be built in to any purge commands,
but many of the purge commands I've encountered in OpenStack just throw
a massive delete at the DB and hope for the best. I have not looked at
Ceilometer's.

Please note this is mostly an operational question, and not a development
question, so I think this thread might want to move over to the openstack@
mailing list.