Station registry returns 502 Bad Gateway #6
Comments
bit4gam
changed the title
|
It's not just you. It looks like all station registrations are failing with an error similar to: I'm not sure what's going on. @matthewwall wrote the registration software. I'll have to check with him. Thanks for calling it to my attention! |
|
I'll leave it to @matthewwall to fix, but it appears that MySQL is taking a lot of time (8-10 seconds per query) doing queries such as select max(last_seen) from stations where last_addr='51.38.58.37';The table mysql> show indexes from stations;
+----------+------------+----------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+----------+------------+----------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| stations | 0 | index_stations | 1 | station_url | A | 6959 | NULL | NULL | | BTREE | | | YES | NULL |
| stations | 0 | index_stations | 2 | latitude | A | 8047 | NULL | NULL | YES | BTREE | | | YES | NULL |
| stations | 0 | index_stations | 3 | longitude | A | 8583 | NULL | NULL | YES | BTREE | | | YES | NULL |
| stations | 0 | index_stations | 4 | station_type | A | 9196 | NULL | NULL | YES | BTREE | | | YES | NULL |
| stations | 0 | index_stations | 5 | station_model | A | 85837 | NULL | NULL | YES | BTREE | | | YES | NULL |
| stations | 0 | index_stations | 6 | weewx_info | A | 85837 | NULL | NULL | YES | BTREE | | | YES | NULL |
| stations | 0 | index_stations | 7 | python_info | A | 85837 | NULL | NULL | YES | BTREE | | | YES | NULL |
| stations | 0 | index_stations | 8 | platform_info | A | 128755 | NULL | NULL | YES | BTREE | | | YES | NULL |
| stations | 0 | index_stations | 9 | last_addr | A | 257511 | NULL | NULL | YES | BTREE | | | YES | NULL |
+----------+------------+----------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
9 rows in set (0.65 sec)The column I would propose having a two-column index of |
|
I changed the indexes around. First dropped the existing, over-constrained, index: ALTER TABLE stations DROP INDEX index_stations;Then created two separate indices: ALTER TABLE stations ADD UNIQUE INDEX index_url (station_url);
ALTER TABLE stations ADD INDEX index_addr (last_addr);Performance much improved. The query time for select max(last_seen) from stations where last_addr='51.38.58.37';dropped from 10 seconds to a few milliseconds. After clearing out the backlog of registrations by rebooting, the registration in the original post went through without problem. Still, I think the registration system needs a rewrite. It's overly complicated, and not very modular. Doing it in a language other than Perl wouldn't hurt either! :-) |
|
Now a lot of time is being spent on queries such as SELECT * FROM platform_history WHERE datetime=(select max(datetime) FROM platform_history);So, I added an index for ALTER TABLE platform_history ADD INDEX index_datetime (datetime);Parenthetically, a better way to construct this query would be: SELECT * FROM platform_history ORDER BY datetime DESC LIMIT 1;which uses only one query instead of two. |
the station registry fails with following error:
Sep 27 14:00:31 weewx[21] ERROR weewx.restx: StationRegistry: Failed to publish record 2022-09-27 13:56:00 CEST (1664279760): Failed upload after 3 triesmanually registering the station at registry url (http://weewx.com/register/register.cgi) returns following error:
The text was updated successfully, but these errors were encountered: