-
-
Notifications
You must be signed in to change notification settings - Fork 107
New issue
Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.
By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.
Already on GitHub? Sign in to your account
SBFspot Upload Performance #283
Comments
You're running your MySQL/MariaDB on the same Rpi I suppose? I don't recommend this. |
Same problem here: raspberry PI3, uploaddeamon is causing 100% CPU time.
|
I'm looking to investigate some things about performance and uploading consumption. Is anybody willing to supply me a slow database for me to investigate on? |
Also looking at the database structure, an index on PVoutput will provide significant gains if the query optimizer can work out that very few values of PVoutput are null. |
I have this slow sqlite query too. The performance is steadily getting slower as the number of records in DayData and SpotData tables increase. They currently have around 180,000 records each.
|
Made it faster still, by using the date filter for the SpotData subquery.
|
Seriously, I don’t get it why you put all this SQL code inside the source instead of the view. Anyway nice job already. I’ll test it one of these days. |
Views don't get optimised very well, hence inlining was required. |
This statement is wrong. I did the test and there is no notable difference in speed. I digged up the reviewed queries from SBFspot 4 project which was put on hold (lack of time)
There are some new views created for generation. Consumption data is not necessarily available at the same time of generation. So a separate upload is preferable. --
-- vwPVODayData View
--
DROP VIEW IF EXISTS vwPVODayData;
CREATE VIEW vwPVODayData AS
SELECT TimeStamp,
Serial,
TotalYield,
Power
FROM DayData Dat
WHERE TimeStamp > strftime( '%s', 'now' ) -(
SELECT Value
FROM Config
WHERE [KEY] = 'Batch_DateLimit'
)
* 86400
AND
PvOutput IS NULL;
--
-- vwPVOSpotData View
--
DROP VIEW IF EXISTS vwPVOSpotData;
CREATE VIEW vwPVOSpotData AS
SELECT TimeStamp,
TimeStamp -( TimeStamp % 300 ) AS Nearest5min,
Serial,
Pdc1,
Pdc2,
Idc1,
Idc2,
Udc1,
Udc2,
Pac1,
Pac2,
Pac3,
Iac1,
Iac2,
Iac3,
Uac1,
Uac2,
Uac3,
Pdc1 + Pdc2 AS PdcTot,
Pac1 + Pac2 + Pac3 AS PacTot,
ROUND( Temperature, 1 ) AS Temperature
FROM SpotData
WHERE TimeStamp > strftime( '%s', 'now' ) -(
SELECT Value
FROM Config
WHERE [KEY] = 'Batch_DateLimit'
)
* 86400;
--
-- vwPVOSpotDataAvg View
--
DROP VIEW IF EXISTS vwPVOSpotDataAvg;
CREATE VIEW vwPVOSpotDataAvg AS
SELECT nearest5min,
serial,
avg( Pdc1 ) AS Pdc1,
avg( Pdc2 ) AS Pdc2,
avg( Idc1 ) AS Idc1,
avg( Idc2 ) AS Idc2,
avg( Udc1 ) AS Udc1,
avg( Udc2 ) AS Udc2,
avg( Pac1 ) AS Pac1,
avg( Pac2 ) AS Pac2,
avg( Pac3 ) AS Pac3,
avg( Iac1 ) AS Iac1,
avg( Iac2 ) AS Iac2,
avg( Iac3 ) AS Iac3,
avg( Uac1 ) AS Uac1,
avg( Uac2 ) AS Uac2,
avg( Uac3 ) AS Uac3,
avg( Temperature ) AS Temperature
FROM vwPVOSpotData
GROUP BY serial,
nearest5min;
--
-- vwPVOUploadGeneration View
--
DROP VIEW IF EXISTS vwPVOUploadGeneration;
CREATE VIEW vwPVOUploadGeneration AS
SELECT datetime( dd.TimeStamp, 'unixepoch', 'localtime' ) AS TimeStamp,
dd.Serial,
dd.TotalYield AS V1,
dd.Power AS V2,
NULL AS V3,
NULL AS V4,
spot.Temperature AS V5,
spot.Uac1 AS V6,
NULL AS V7,
NULL AS V8,
NULL AS V9,
NULL AS V10,
NULL AS V11,
NULL AS V12
FROM vwPVODayData AS dd
LEFT JOIN vwPVOSpotDataAvg AS spot
ON dd.Serial = spot.Serial
AND
dd.Timestamp = spot.Nearest5min
This is the modification in db_SQLite.cpp sql << "SELECT strftime('%Y%m%d,%H:%M',TimeStamp),V1,V2,V3,V4,V5,V6,V7,V8,V9,V10,V11,V12 FROM [vwPvoUploadGeneration] WHERE "
"Serial=" << Serial << " "
"ORDER BY TimeStamp "
"LIMIT " << statuslimit; Here is the logging (I needed a new timestamp function to have millisecond resolution)
|
I'm quite confident that my targeted sql is better than the sqlite's views query optimizer for this instance. |
I'm afraid I was not clear enough... |
Indeed, I misunderstood your earlier message. All understood now. thumbs up |
@sbf- Is your optimised query (0.1 seconds) committed already at this point? Previous comment ambiguous but issue still open so I think that means no? |
No, not yet |
Be careful that the optimized views don't work(as it is) with multiple inverters. |
Can you explain this a bit more? |
I tried the changes(views and code) and the data uploaded in pvoutput contains the output of only one of the inverters(I have two inverters configured with mis_enabled). The average column in pvoutput is always equal to 0. |
I would be looking for a MYSQL solution to this too. I can take a stab at it if the sqlite versions is baselined |
Tried using this latest version, since I'm having performance issues after 6 months of running (with 3 inverters). I'm also using a customised view to upload string data to PVOutput: CREATE VIEW vwPvoData AS
SELECT dd.Timestamp,
dd.Name,
dd.Type,
dd.Serial,
dd.TotalYield AS V1,
dd.Power AS V2,
cons.EnergyUsed AS V3,
cons.PowerUsed AS V4,
spot.Temperature AS V5,
spot.Uac1 AS V6,
spot.Udc1 AS V7, -- DC Voltage String 1
spot.Udc2 AS V8, -- DC Voltage String 2
spot.Pdc1 AS V9, -- DC Power String 1
spot.Pdc2 AS V10, -- DC Power String 2
NULL AS V11,
NULL AS V12,
dd.PVoutput
FROM vwDayData AS dd
LEFT JOIN vwAvgSpotData AS spot ON dd.Serial = spot.Serial AND dd.Timestamp = spot.Nearest5min
LEFT JOIN vwAvgConsumption AS cons ON dd.Timestamp = cons.Nearest5min
ORDER BY dd.Timestamp DESC; But since I'm using the new version v5-v10 do not get uploaded. The rest is working fine. |
With "this latest version" you mean the "dev" branch? |
Yes, meant the dev branch. |
It's a bit more complicated ;-) |
@sbf- Sorry, but can you clarify what the best approach is to apply this performance fix. Per my question in discussions/479 I am using SBFspot V3.8.2 (main) with a fresh install, but an old database with ~140K records. (previously ran the Update_340_SQLite.sql, so I think the schema is correct for 3.8.2). I applied the above fix (create the new views, modify db_SQLite.cpp, re-compile the app and the Daemon) but that did not help. I now understand that this requires me to use the V4 dev branch? I am happy to try the dev branch, but not clear if you are recommending it as ready for use? Does the basic functionality work correctly (single inverter, not measuring consumption, upload to pvoutput). I have a high level of technical knowledge, but don't want to start troubleshooting the C code :). |
No need to use dev branch. It should work with current production version 3.8.2 Maybe a silly question, but are you sure you replaced the daemon with the new version? You should see a much better performance. |
I did check timestamps and the daemon seemed to have a new date. Does the service somehow cache the daemon (I have done a stop & start)? Anyway I repeated the whole build process yesterday and it has run successfully today. Performance looks much better, and it does not take high CPU for long. I dont have any exact measure , but it appears on Maybe for a Pi Zero it would be worth reducing the interval - say every 10 minutes. Would you recommend a change to CommonServiceCode.cpp here?:
|
Pi Zero has a single core processor vs quad core for Pi 3, that's a big difference (apart from clock speed) Every 5min Every 10min Maybe you can try to lower the daemon priority (see StackExchange) I did the test myself on Pi Zero W (DB size 85Mb) |
Hi, I'm the creator of the branch mentioned by the OP. |
Yes, Version 3.9
Not a bad idea... I'll try to include it in the same release. |
Is this upload performance patch included in the v3.9.3 release? |
Hmm, looks like I didn't keep my promise. I'll add it to the 3.10 milestone (coming soon) |
Hi, I am also looking forward for this improvement. My Raspi 1B would be happy. |
Is there any update on this one? or something that can be done to improve the CPU time? My Pi Zero W is almost running at 100% CPU all the time just uploading data to PVOutput. |
In addition to the changes suggested above, which will hopefully make it into v3.10, I added a Python script to my setup that I run weekly to archive old data into a separate database. That reduces the data in the upload database to the data for the last month or so, and keeps my Pi0 CPU usage to about 3-4%. |
For what its worth ive been hunting down performance bottlenecks on my Odroid M1 (which is a 4 core 64bit ARM based SBC). SBF Uploader consumes 100% of one CPU core for about 15 seconds every 5mins. During this time it writes ~55MB of data to disk for each inverter. This is on my sqlite database of ~35MB. I will watch how this changes once 3.10 lands. Edit: I was impatient so I decided to give the Python script here a whirl: https://gist.github.com/warthog618/db30df2567ec605439f77a09702b0962 |
The SpotData table is storing the TimeStamp as an Int(4).
This is then converted to a proper timestamp in the views, using From_UnixTime.
The problem with this approach is that queries can't use the index, and have to scan the entire table. OK initially, but on my little pi (with 100,000 records) it's taking 5 seconds to run the query to find records to upload:
There is a clone of this repository at
https://github.com/warthog618/SBFspot/blob/master/SBFspot/CreateSQLiteDB.sql
where warthog618 attempts to address this problem by storing Timestamp as a datetime.
It can be turned back into a number in views if required.
The problem for me is the database is slowing down my whole PI, causing timeouts to other workloads.
Thanks,
Tony
The text was updated successfully, but these errors were encountered: