Sure thing. Here’s a summary of my research so far. After some more testing and validation of my fixes, I plan on making the necessary changes in a PR.
Scenario 1: accessing the history of a single entity (clicking on an entity on the frontend)
We have a couple problem queries. Here is one:
SELECT states.state_id AS states_state_id, states.domain AS states_domain, states.entity_id AS states_entity_id, states.state AS states_state, states.attributes AS states_attributes, states.event_id AS states_event_id, states.last_changed ASstates_last_changed, states.last_updated AS states_last_updated, states.created AS states_created
FROM states
WHERE (states.domain IN ('thermostat', 'climate') OR states.last_changed = states.last_updated)
AND states.last_updated > '2017-06-25 22:02:07.718556'
AND states.entity_id IN ('sensor.living_room_temp_diff')
AND states.last_updated < '2017-06-26 22:02:07.718556'
ORDER BY states.entity_id, states.last_updated
We have two problems here. One is the states.domain IN ('thermostat', 'climate') OR states.last_changed = states.last_updated bit - it’s pointless because we’re already asking for a specific entity_id whose domain is always static, just causing the database to work harder.
Proposed Solution 1: Change the code to remove that domain filter when fetching history for a single entity.
The second problem is that we’re sorting by last_updated, which does not have a multicolumn index with entity_id. Creating this index:
CREATE INDEX ix_states_entity_id_last_updated ON states (entity_id, last_updated);
improves performance from 80 seconds to less than 2 - literally an order of magnitude!
Proposed Solution 2: Add this index to the db model
The second problem query in this scenario is a subquery used to grab the last state_id in a time range, which I will isolate here for clarity:
SELECT max(states.state_id) AS max_state_id
FROM states
WHERE states.created >= '2017-06-10 21:53:38.977455'
AND states.created < '2017-06-25 22:30:55.106688'
AND states.domain NOT IN ('zone', 'scene')
AND states.entity_id IN ('sensor.aeotec_zw100_multisensor_6_temperature_2_1')
GROUP BY states.entity_id
First of all, I want to validate that this second query is necessary at all, especially for how expensive it is. I am suspicious that we should be doing this second query rather than simply grabbing the highest state_id from the first query in code.
Proposed Solution 3: I need to do more research on the necessity of this query in the first place.
Assuming that we will keep the query, we have a problem similar to the first query: the domain filter (states.domain NOT IN ('zone', 'scene')) is completely unnecessary, because we’re already filtering by a single entity_id. In fact, removing this condition improves the performance of this query from 30 seconds to less than half a second. 60x improvement!
Proposed Solution 4: Remove the domain filter from this query in the code, when fetching a single entity.
Proposed Solution 5: Remove the GROUP BY clause in this query, because we’re already filtering by a single entity_id.
Scenario 2: Opening the History page
This one is a much worse problem, mainly because it’s just the above issues multiplied by how many entities you have. On my RPi3, the query to open this page churns for over 900 seconds before I get impatient and give up. I have no idea how long, if ever, it will actually take. So I dug in here as well.
Here’s the first problem query I am looking at. It’s a subquery used on the History page to grab the last state_id for each entity.
SELECT max(states.state_id) AS max_state_id
FROM states
WHERE states.created >= '2017-06-10 21:53:38.977455'
AND states.created < '2017-06-26 07:00:00'
AND states.domain NOT IN ('zone', 'scene')
AND states.domain NOT IN ('zone', 'scene')
AND states.domain NOT IN ('automation', 'group')
GROUP BY states.entity_id
First up is the same concern with the second query: why do we need this? I’ll investigate and determine if there is a more efficient way to do this from the time series query rather than having a second query just to get the final state_id for each entity.
Proposed Solution 6: I need to do more research on the necessity of this query in the first place.
As for the query, right off the bat we can see some weird duplicate domain restrictions. The first two are seemingly global logic for the history page, and the last one is because of the specific domains I have excluded in my Hass config.
Proposed Solution 7: Remove erroneous domain filter
Next up, we’ve got an index issue. We have no multicolumn index appropriate for this query. I have been experimenting with different indexes to solve this one with varying success. MySQL is having issues picking the right index sometimes, so I am investigating. Here is the probably ideal index:
CREATE INDEX ix_states_created_domain_entity_id ON states (created, domain, entity_id);
When I can get MySQL to use this index, the query changes from so long that I lose patience to 10 seconds. That’s not as fast as I would like, but it’s still multiple orders of magnitude faster than before.
Proposed Solution 8: Figure out the ideal index for this query and add it to the db model
So, that’s my research so far. Each time I fix a query, I find the next worse query and investigate that one, so I don’t think this is all of the fixes that are needed. I will continue my research and experimentation and report back when I know even more!
