MySQL Persistence minimumSince gives no result but high cpu load

Hello,

To calculate my daily / monthly / yearly power consumption I use the item.minimumsince function.

Unfortunately I have more and more problems all the more days of the year, as the period of the sql querys gets bigger.

To investigate, I wrote the following test rule and set the MySQL persistence Logging to Trace:

import java.util.TimeZone
import java.util.Calendar

rule "Sample Rule"
when
Item Ruletester received update 
then

var Calendar current_time = java::util::Calendar::getInstance(TimeZone::getTimeZone("GMT"))
var int	dayoy = current_time.get(java::util::Calendar::DAY_OF_YEAR)

var wertvoreinjahr = Z_Haus_Verbrauch_Gesamt.minimumSince(now.minusDays(dayoy),"mysql").state.toString()
logWarn("Stromzaehler","MinimumSince: " + wertvoreinjahr)

end 

If i run the rule, i get the following log entry:

19-Oct-2018 08:48:33.551 [DEBUG] [persistence.mysql.internal.MysqlPersistenceService] - mySQL: query:SELECT Time, Value FROM Item594 WHERE TIME>'2017-12-31 08:48:33' ORDER BY Time ASC

that’s over 30 minutes ago. Since then, the CPU load is at 100% and the Webinterface is no longer available.

However, when I run this query directly in MySQL, I get the results very quickly:

mysql> SELECT Time, Value FROM Item594 WHERE TIME>‘2017-12-31 08:48:33’
362035 rows in set (2.65 sec)

Are 362035 records too much for OpenHAB running on a RaspberryPi 3?

Can I somehow finish execution without rebooting openhab?

Thank You!

Seems OH handles the functionality of finding the minimum value internally, probably because it must support all the different database types. The query itself could have been done much easier and less resource intense if we had the full SQL syntax available.

Maybe it would be better for you to create a virtual item that’s updated by an external script (python/node/php whatever) which queries the db?

An external script would probably be the best solution to the problem, a “Select Min (Value)” as an SQL query. I just came across .historicState, which is actually perfect for calculating my annual electricity consumption. historicState executes the following query:
SELECT Time, Value FROM Item594 WHERE TIME < '2017-12-31 10:15:11' ORDER BY Time DESC LIMIT 0.1

Does it? I would have assumed that OH would have required the Persistence bindings to implement minimumSince but it wouldn’t implement it itself. That seems like a better approach as is illustrated here, different approaches will work better for different databases.

I ask because if I’m right this might be something that can be fixed with a simple PR. If not then perhaps this is something that should be brought up and a larger change made across the Persistence bindings. It makes no sense to me to not implement them to use the most efficient approach which will potentially be different for each DB.

I only suspect since the query mentioned from the trace seems to fetch all the rows in the query and not limit the query result to 1 row

I found the code that makes the query:

It does indeed appear that you are right and the API is set up to get everything between the two dates and I assume calculates the min/max/avg in generic OH code. There is nothing in the MySQL code to do it that I can tell.

That’s unfortunate and I think it would take a huge effort to change that. It’s not how I would have set it up but no one asked me. :wink:

It definitely looks like an external script would be the better approach.