the station registry fails with following error: <code class="notran

I'll leave it to <a class="user-mention notranslate" data-hovercard-type="user" data-h

Now a lot of time is being spent on queries such as <div class="highlight highligh

Station registry returns 502 Bad Gateway about website HOT 4 CLOSED

bit4gam commented on June 22, 2024

Station registry returns 502 Bad Gateway

from website.

Comments (4)

tkeffer commented on June 22, 2024

It's not just you. It looks like all station registrations are failing with an error similar to:

2022/09/27 15:49:15 [error] 990#0: *1574 upstream timed out (110: Connection timed out) while reading response header from upstream, client: 67.40.252.225, server: weewx.com, request: "GET /register/register.cgi?station_url=https://meteo.fedibox.com/&description=Sapri&latitude=40.074&longitude=15.62&station_type=Ecowitt&station_model=WS2900&weewx_info=4.8.0&python_info=3.9.2&platform_info=Armbian HTTP/1.1", upstream: "fastcgi://unix:/var/run/fcgiwrap.socket", host: "weewx.com"

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!

from website.

tkeffer commented on June 22, 2024

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 stations uses a multi-column index, but rather than be selective about which columns to index, it does them all, in an unfortunate order:

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 last_addr is the 9th in the indexing sequence!

I would propose having a two-column index of station_url and last_addr, and dropping all the other indexes.

from website.

tkeffer commented on June 22, 2024

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! :-)

from website.

tkeffer commented on June 22, 2024

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 platform_history:

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.

from website.

Station registry returns 502 Bad Gateway about website HOT 4 CLOSED

Comments (4)

Related Issues (4)

Recommend Projects

React

Vue.js

Typescript

TensorFlow

Django

Laravel

D3

Recommend Topics

javascript

web

server

Machine learning

Visualization

Game

Recommend Org

Facebook

Microsoft

Google

Alibaba

D3

Tencent