These are chat archives for ManageIQ/manageiq/performance

6th
Dec 2016
Keenan Brock
@kbrock
Dec 06 2016 14:05
Curious that there are 2 almost identical indexes on the metrics tables
In my current database they are 300-500MB each. So removing one of these indexes will be a couple of gigs (there are 12)
"index_metric_rollups_01_on_resource_and_ts" btree (resource_id, resource_type, capture_interval_name, "timestamp")
"index_metric_rollups_01_on_ts_and_capture_interval_name" btree ("timestamp", capture_interval_name, resource_id, resource_type)
Jason Frey
@Fryguy
Dec 06 2016 14:06
The order is different
Keenan Brock
@kbrock
Dec 06 2016 14:06
yes
yea
having a timestamp at the beginning of a btree is curious
Jason Frey
@Fryguy
Dec 06 2016 14:06
One night be used for is left to right props, but then it wouldn't need all 4 columns
Keenan Brock
@kbrock
Dec 06 2016 14:06
not sure if we can use it
think since the timestamp does not match, not sure if the others are used
Jason Frey
@Fryguy
Dec 06 2016 14:07
Why wouldn't timestamp match?
Also why can't a timestamp be in a btree?
Keenan Brock
@kbrock
Dec 06 2016 14:07
it can match on a > or <
but a timestamp is never ==
or maybe it is
Jason Frey
@Fryguy
Dec 06 2016 14:08
Treatable had to match directly or nothing would work :)
Keenan Brock
@kbrock
Dec 06 2016 14:09
ok. thanks
treatable?
Jason Frey
@Fryguy
Dec 06 2016 14:09
Damn autocorrect
Keenan Brock
@kbrock
Dec 06 2016 14:10
lol
Jason Frey
@Fryguy
Dec 06 2016 14:10
Timestamp
Keenan Brock
@kbrock
Dec 06 2016 14:10
lol
Keenan Brock
@kbrock
Dec 06 2016 14:31
well, in truth, it is all the indexes on the event_stream table that is of interest (not as easy of a yes/no call)
but
object size
event_streams 14 GB
index_event_streams_on_event_type 1.4 GB
index_event_streams_on_chain_id_and_ems_id 863 MB
index_event_streams_on_host_id 773 MB
index_event_streams_on_ems_cluster_id 773 MB
index_event_streams_on_ems_id 773 MB
index_event_streams_on_timestamp 773 MB
index_event_streams_on_vm_or_template_id 773 MB
index_event_streams_on_dest_host_id 773 MB
index_event_streams_on_dest_vm_or_template_id 773 MB
So an index on a string is ~10% the table size, and an index on a field is 5% of the table size
9 indexes really adds up. especially for our largest table
Jason Frey
@Fryguy
Dec 06 2016 15:39
I'm curious on the history of those two indexes
I'd have to look through history
Keenan Brock
@kbrock
Dec 06 2016 15:43
@Fryguy think that index may have been introduced at the time where we were aiming for uniqueness as far left as possible
I'm not sure how much truth there is to that theory, but that is probably why the index is the way it is
Jason Frey
@Fryguy
Dec 06 2016 15:47
oh sorry...I thought you were back on the metrics index
Keenan Brock
@kbrock
Dec 06 2016 15:47
yes
the metrics index
the 2 indexes are identical, but the time date stamp is far left
Jason Frey
@Fryguy
Dec 06 2016 15:47
ah ok
yeah
we should look at history of that
for event streams, the individual indexes on the _id columns were for lookup by that CI
so host.events should use _on_host_id and _on_dest_host_id
Keenan Brock
@kbrock
Dec 06 2016 15:49
yea, event stream makes more sense, but that is a lot of data there. 7GB in indexes alone