Collector is slow on storing samples to backend database

[Mitsuru Kanabuchi]

It takes about 18 minutes in storing 20,000 samples (floating ips in our case) to the backend database.
Stable/havana, with MySQL backend DB is used.

The polling interval is 10 minutes by default, so next polling cycle will start before storing completes.
Therefore, problems such that queue is choked with messages (samples) and/or resources for collector (cpu, memory) increase, might occur.

The followings can be considered to be reasons of the problem.
- Collector stores samples one at a time (i.e., no bulk insert).
- Schema level/code level problems may exist, as pointed out in the following article.
  http://lists.openstack.org/pipermail/openstack-dev/2013-December/023134.html

[Mitsuru Kanabuchi (kanabuchi)]

I think this is not a duplicate of Bug #1291054: ceilometer service is working on a single thread.

#1291054 seems to target to enable multiple workers for notification agent and collector. The fix will have great effect in speeding up storing samples to backend database.

However, #1291054 seems to solve our problem only partly. There are cases that single central agent needs to handle a large amount of samples. For example, 20,000 samples of floating ips, acquired from one polling to neutron, are made into one message and put into the queue. Only one collector can get the message and store 20,000 samples. Therefore, we believe that we need to consider the DB store speed of a single collector.

Ideas for solutions are:
1. implement some bulk insert feature into central agent.
2. avoid forming large number of samples into single message - divide message to contain smaller number of samples for each, so that multiple workers can take effect.

[OpenStack Infra (hudson-openstack)]

Fix proposed to branch: master
Review: https://review.openstack.org/94155

[Mitsuru Kanabuchi (kanabuchi)]

In order to make effective use of the Multiple Worker [1],
we made modifications to split a message and to send it.

[1]:https://bugs.launchpad.net/ceilometer/+bug/1291054

[gordon chung (chungg)]

hi Mitsuru, we've made changes to collecto sql backend so the collector should write quite a bit faster now. you can also enable multiple collector workers now

[Mitsuru Kanabuchi (kanabuchi)]

Hi Gordon, thank you for great works!
We have a plan to re-evaluate the collector's performance after Juno-1 released.
We'll post the result of that measurement.

[Rikimaru Honjo (honjo-rikimaru-c6)]

Hi Gordon,

I'm Rikimaru Honjo. I assumed this task from Mitsuru. Please keeping in touch with me about this topic.

we have re-evaluated the collector's performance using Juno-1.

Due to the change in our test environment, this time we used glance image and image.size, each 10400 samples (20800 total). The result shows that Juno-1 is more than 2 times faster than Icehouse.
Using Icehouse: 50 minutes to write the 20800 samples to DB.
Using Juno-1: 18.5 minutes.
Please note that absolute times are irrelevant, since we used very old machine.
Thank you to your effort in refining the model.

[gordon chung (chungg)]

hi Rikimaru Honjo.

that is great news. more refinements are coming (less related to write speed but some improvement is expected)

[gordon chung (chungg)]

the SQL backend can still be improved with batch inserts

[OpenStack Infra (hudson-openstack)]

Change abandoned by gordon chung (<email address hidden>) on branch: master
Review: https://review.openstack.org/94155
Reason: inactive, please reopen if important

[gordon chung (chungg)]

will close this... as we transition to new model (gnocchi), we can re-open.