Re: [openstack-dev] [Ceilometer] time consuming of listing resource

2014-02-18 Thread Mitsuru Kanabuchi

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 jaypi...@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 jaypi...@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 30 items, so this operation may consume about 30 seconds;
   2.The operationfor m in meter.resource.meters will circulate the
   meters of this resource . a resource of server may have more than 10
   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 

Re: [openstack-dev] [Ceilometer] time consuming of listing resource

2013-12-28 Thread Haomai Wang
I think the better way is save meters as a field in resource table.

You can look at MongoDB model and may get some ideas.

Beside above, sql backend can introduce Memcache to improve performance.
IMO, the best way may be redesign the sql model to match workload.



On Sat, Dec 28, 2013 at 6:51 PM, 刘胜 liusheng1...@126.com 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 30 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 10
 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?

 Best Regards
 liusheng


 ___
 OpenStack-dev mailing list
 OpenStack-dev@lists.openstack.org
 http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev




-- 

Best regards,

Haomai Wang, UnitedStack Inc.
___
OpenStack-dev mailing list
OpenStack-dev@lists.openstack.org
http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev


Re: [openstack-dev] [Ceilometer] time consuming of listing resource

2013-12-28 Thread Jay Pipes

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 30 items, so this operation may consume about 30 seconds;
2.The operationfor m in meter.resource.meters will circulate the
meters of this resource . a resource of server may have more than 10
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