event write performance is terrible in sql backend

[gordon chung]

when generating a generic list of events, the write performance is terrible for the sql backend compared to mongodb.

ie. ~4700 events, a single thread, a single event type, each event has 4 different traits of different types:

completion time (mongodb): ~2.17s
completion time(mysql/mariadb): ~120.57s

so... yeah.

[gordon chung (chungg)]

current schema:

        - EventType
          - event definition
          - { id: event type id
              desc: description of event
              }
        - Event
          - event data
          - { id: event id
              message_id: message id
              generated = timestamp of event
              event_type_id = event type -> eventtype.id
              }
        - Trait
          - trait value
          - { event_id: event -> event.id
              trait_type_id: trait type -> traittype.id
              t_string: string value
              t_float: float value
              t_int: integer value
              t_datetime: timestamp value
              }
        - TraitType
          - trait definition
          - { id: trait id
              desc: description of trait
              data_type: data type (integer that maps to datatype)
              }

step 1 proposal:

divide trait table into 4 tables, one for each trait_type -- similar to how we handle metadata in metering data. in current design we're always guaranteed that 3 of 6 columns are always empty in each row. this adds unnecessary complexity as we have to store more data (empty data), and then during retrieval, we again have overhead to figure out which column the value is stored in.

the Trait TraitType table will be dropped in proposal and replaced with 4 Trait_* tables (one for each possible trait type).

ie.

        - Trait_String
          - trait value
          - { event_id: event -> event.id
              key: trait type -> traittype.id
              value: string value
              }
        - Trait_float
          - trait value
          - { event_id: event -> event.id
              key: trait type -> traittype.id
              value: float value
              }
        - Trait_int
          - trait value
          - { event_id: event -> event.id
              key: trait type -> traittype.id
              value: int value
              }
        - Trait_datetime
          - trait value
          - { event_id: event -> event.id
              key: trait type -> traittype.id
              value: string value
              }

[gordon chung (chungg)]

i'm not sure why this didn't update but the corresponding patch is here: https://review.openstack.org/#/c/130869/

[OpenStack Infra (hudson-openstack)]

Reviewed: https://review.openstack.org/130869
Committed: https://git.openstack.org/cgit/openstack/ceilometer/commit/?id=b41d8243df7b711df98d60fbc7f7e7ab3602de16
Submitter: Jenkins
Branch: master

commit b41d8243df7b711df98d60fbc7f7e7ab3602de16
Author: gordon chung <email address hidden>
Date: Tue Oct 21 16:23:34 2014 -0400

    modify events sql schema to reduce empty columns

    in current design, three columns in Trait table are always empty.
    this adds size to each row. since we are already checking for type
    of value, we should just drop the items into their own tables.

    this patch:
    - moves trait table to four smaller tables similar to metadata
    - does not map traits to dict-like attribute in events model as
      query performance is horrible.
    - bulk inserts traits

    this patch aims to improve write performance.

    Closes-Bug: #1381736
    Change-Id: I2bd4c16b57fe2c1ad67646ccd4417078f0f0f734