Open Stack

Hi Liusheng,

We are having the same performance issues and interested in
the following bug ticket.

  https://bugs.launchpad.net/ceilometer/+bug/1264434

You said,

> As you said.both the schema level and the code level,the SQL driver in
> Ceilometer should be optimized. thanks for your advicese.I will search
> around about this.

Could you please share the current status of this work.
Do you have any specific time line for patch release?

Regards,


On Mon, 06 Jan 2014 11:12:26 -0500
Jay Pipes <jaypipes at gmail.com> wrote:

> On Mon, 2014-01-06 at 21:06 +0800, 刘胜 wrote:
> > Hi jay,Thank you for the comments, I have simply tested the
> > performance of ceilometer with mysql driver.,while,the DB table may
> > become huge in few days.Unfortunately,the result is not satisfied .
> > As you said.both the schema level and the code level,the SQL driver in
> > Ceilometer should be optimized. thanks for your advicese.I will search
> > around about this.
> 
> Hi there :) Please do let me know what performance improvements you see
> by following the steps I listed below.
> 
> All the best,
> -jay
> 
> > 
> > 在 2013-12-29 00:16:47，"Jay Pipes" <jaypipes at gmail.com> 写道：
> > >On 12/28/2013 05:51 AM, 刘胜 wrote:
> > >> Hi all：
> > >> I have reported a bug about time consuming of “resource-list” in
> > >> ceilometer CLI：
> > >> https://bugs.launchpad.net/ceilometer/+bug/1264434
> > >>
> > >> In order to Identify the causes of this phenomenon, I have pdb the codes
> > >> in my invironment(configured  mysql as db driver):
> > >> the most import part of process of listing resource is implemented in
> > >> following codes:
> > >>
> > >> code of get_resources() in /ceilometer/storage/impl_sqlalchemy.py：
> > >> …………
> > >>   for meter, first_ts, last_ts in query.all():
> > >>              yield api_models.Resource(
> > >>                  resource_id=meter.resource_id,
> > >>                  project_id=meter.project_id,
> > >>                  first_sample_timestamp=first_ts,
> > >>                  last_sample_timestamp=last_ts,
> > >>                  source=meter.sources[0].id,
> > >>                  user_id=meter.user_id,
> > >>                  metadata=meter.resource_metadata,
> > >>                  meter=[
> > >>                      api_models.ResourceMeter(
> > >>                          counter_name=m.counter_name,
> > >>                          counter_type=m.counter_type,
> > >>                          counter_unit=m.counter_unit,
> > >>                      )
> > >> for m in meter.resource.meters
> > >>                  ],
> > >>              )
> > >> The method  generate iterator of object of api_models.Resource for
> > >> ceilometer API to show.
> > >> 1.The operation “query.all()” will query the DB table “meter” with the
> > >> expression generated forward,in my invironment the DB table “meter” have
> > >> more than 300000 items, so this operation may consume about 30 seconds;
> > >> 2.The operation"for m in meter.resource.meters" will circulate the
> > >> meters of this resource . a resource of server may have more than 100000
> > >> meter iterms in my invironment.  So the time of whole process is too
> > >> long. I think the meter of Resource object can be reduced and I have
> > >> tested this modification, it is OK for listing resource,and reduce the
> > >> most time consumption
> > >>
> > >> I have noticed that there are many methods of db operation may time
> > >> consumption.
> > >>
> > >> ps: I have configured the ceilometer pulling interval from 600s to 60s
> > >> in /etc/ceilometer/pipeline.yaml, but the invironment has just run 10 days!
> > >>
> > >> I'm a beginner of ceilometer,and want to fix this bug,but I haven't
> > >> found a suitable way
> > >> may be someone can help me with this?
> > >
> > >Yep. The performance of the SQL driver in Ceilometer out-of-the-box with 
> > >that particular line is unusable in our experience. We have our Chef 
> > >cookbook literally patch Ceilometer's source code and comment out that 
> > >particular line because it makes performance of Ceilometer unusable.
> > >
> > >I hate to say it, but the SQL driver in Ceilometer really needs an 
> > >overhaul, both at the schema level and the code level:
> > >
> > >On the schema level:
> > >
> > >* The indexes, especially on sourceassoc, are wrong:
> > >  ** The order of the columns in the multi-column indexes like idx_sr, 
> > >idx_sm, idx_su, idx_sp is incorrect. Columns used in predicates should 
> > >*precede* columns (like source_id) that are used in joins. The way the 
> > >indexes are structured now makes them unusable by the query optimizer 
> > >for 99% of queries on the sourceassoc table, which means any queries on 
> > >sourceassoc trigger a full table scan of the hundreds of millions of 
> > >records in the table. Things are made worse by the fact that INSERT 
> > >operations are slowed for each index on a table, and the fact that none 
> > >of these indexes are used just means we're wasting cycles on each INSERT 
> > >for no reason.
> > >  ** The indexes are across the entire VARCHAR(255) field width. This 
> > >isn't necessary (and I would argue that the base field type should be 
> > >smaller). Index width can be reduced (and performance increased) by 
> > >limiting the indexable width to 32 (or smaller).
> > >
> > >The solution to the main indexing issues is to do the following:
> > >
> > >DROP INDEX idx_sr ON sourceassoc;
> > >CREATE INDEX idx_sr ON sourceassoc (resource_id(32), source_id(32));
> > >DROP INDEX idx_sp ON sourceassoc;
> > >CREATE INDEX idx_sp ON sourceassoc (project_id(32), source_id(32));
> > >DROP INDEX idx_su ON sourceassoc;
> > >CREATE INDEX idx_su ON sourceassoc (user_id(32), source_id(32));
> > >DROP INDEX idx_sm ON sourceassoc;
> > >CREATE INDEX idx_sm ON sourceassoc (meter_id, source_id(32));
> > >
> > >Keep in mind if you have (hundreds of) millions of records in the 
> > >sourceassoc table, the above will take a long time to run. It will take 
> > >hours, but you'll be happy you did it. You'll see the database 
> > >performance increase dramatically.
> > >
> > >* The columns that refer to IDs of various kinds should not be UTF8. 
> > >Changing these columns to a latin1 or even binary charset would cut the 
> > >space requirements for the data and index storage by 65%. This means you 
> > >can fit around 3x as many records in the same data and index pages. The 
> > >more records you fit into an index page, the faster seeks and scans will be.
> > >
> > >* sourceassoc has no primary key.
> > >
> > >* The meter table has the following:
> > >
> > >   KEY ix_meter_id (id)
> > >
> > >   which is entirely redundant (id is the primary key) and does nothing 
> > >but slow down insert operations for every record in the meter table.
> > >
> > >* The meter table mixes frequently searched and aggregated fields (like 
> > >timestamp, counter_type, project_id) with infrequently accessed fields 
> > >(like resource_metadata, which is a VARCHAR(5000)). This leads to poorer 
> > >performance of aggregate queries on the meter table that use the 
> > >clustered index (primary key) in aggregation (for an example, see the 
> > >particular line of code that we comment out of Ceilometer above). A 
> > >better performing schema would consolidate slim, frequently accessed 
> > >fields into the main meter table and move infrequently accessed or 
> > >searched fields into a meter_extra table. This would mean many more 
> > >records of the main meter table can fit into a single InnoDB data page 
> > >(the clustered index), which means faster seeks and scans for 99% of 
> > >queries on that table.
> > >
> > >On the code level there are a variety of inefficient queries that are 
> > >generated, and there are a number of places where using something like a 
> > >memcache caching layer for common lookup queries could help reduce load 
> > >on the DB server.
> > >
> > >I'm hoping to push some patches in the early part of 2014 that address 
> > >performance and scalability issues in the SQL driver for Ceilometer.
> > >
> > >Best,
> > >-jay
> > >
> > >_______________________________________________
> > >OpenStack-dev mailing list
> > >OpenStack-dev at lists.openstack.org
> > >http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev
> > _______________________________________________
> > OpenStack-dev mailing list
> > OpenStack-dev at lists.openstack.org
> > http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev
> 
> _______________________________________________
> OpenStack-dev mailing list
> OpenStack-dev at lists.openstack.org
> http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev

--------------------
  Mitsuru Kanabuchi
    NTT Software Corporation
    E-Mail : kanabuchi.mitsuru at po.ntts.co.jp