High CPU usage after API data import

[Aussie Susan]

I'm running MT on a Raspberry Pi (RPI Model B, single core) and, while it works, I've noticed that the CPU usage shoots up to 100% for a minute or two after each time WeeWx writes its 'current' data via the API. (It does this every 5 minutes)
Looking at the 'api.php' script I can see where the data in written to the database ("INSERT INTO alldata...") but it seems to be followed immediately by an "ALTER TABLE alldata ORDER BY dateTime" statement.
I can see that the 'alldata' table has a index on the 'dateTime' field (still learning about MySQL but I have a very long experience with relational databases in general - I don;t think I have misinterpreted the output of the 'show index from alldata' statement).
I've not studied this further but I'm guessing that the 'ALTER TABLE' statement is where MySQL is spending its time as it (currently) has to sort 138687 records.
Is there any reason why the records in the table are being reordered after each single record insertion rather than leaving this up to the index defined on the same field? (Or am I showing my ignorance of the finer features of MySQL?)
Susan

[Jachym]

Hi,
I think it could be deleted. It was there because of how the api worked previously, but it should be ok without it now because some scripts changed (most importantly the ones that do the history import).

[Luc]

Hi Susan,
Weewx can do a lot of tasks when it is time to write the data to the weewx database.
Among others it can generate reports and graphs. Default the Standard skin is activated and use 100% cpu on the rpi for several minutes. When not needed, you have to comment out this in weewx.conf.
Do you run meteotemplate on the raspberry pi? When meteotemplate runs on an external server the mysql queries use cpu on the external computer, not rhe rpi.
Cheers, Luc

[Aussie Susan]

Well I commented out the 'ALTER TABLE...' code in api.php and the processing time dropped from several minutes (admittedly the first minute was spent fighting for the CPU with WeeWx itself) to so small I can't measure it using tools such as 'top'.
Based on my experience I would certainly recommend that this be taken from the code for the next release.

luc - the problem was not the work that WeeWx had to do - that as expected because of the tasks you mentioned. The problem was when WeeWx used the API to write a report to MT which then triggered a huge amount of CPU time from MySQL. My WeeWx installation uses SQlIte3 - and only MT uses MySQL.

Susan

[Jachym]

although when I ran the alter table command in PHPMyAdmin, the time taken (1.5M records) was less than a second