On 25/09/14 15:37, Qiming Teng wrote:
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?
Writes of bigger datasets will take non linear time when (possibly
default?) configs are outgrown. For instance (assumimg metadata_text is
an innodb table, take a look at:
- innodb_log_buffer_size
- innodb_log_file_size (warning: read the manual carefully before
changing this)
- innodb_buffer_pool_size
Also index maintenance can get to be a limiting factor, I'm not sure if
mysql will use the sort buffer to help with this, but maybe try increase
- sort_buffer_size
(just for the session doing the delete) and see if it helps.
There are many (way too many) other parameters to tweak, but the above
ones are probably the best to start with.
Cheers
Mark
_______________________________________________
OpenStack-dev mailing list
OpenStack-dev@lists.openstack.org
http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev
