Optimise the AURWeb

The server is constantly busy with quite some mariadb load:

• Are there more things we can cache?
• We are getting scraped a lot now by LLM's, what do they scape, package pages?
• Can we cache the RSS feeds in nginx? Archweb does this for example
• Find the top requests of AURWeb (we should have some prometheus stats from aurweb
• Lower requests / sec, it is currently quite high (20 req/s), surely a normal page load is like 1-3 requests?
• Do we have a proper robots.txt setup for scrapers like LLM's? Crawl-Delay?
• Make more use of redis in aurweb
• Cache the recent packages in the homepage
• Can we figure out the top requests from prometheus? https://monitoring.archlinux.org/d/gmvZGXjGk/aur-statistics?orgId=1

changed the description

It seems like we don't have any robots.txt right now:

$ curl --silent -I https://aur.archlinux.org/robots.txt
HTTP/2 404 
server: nginx

Robots.txt makes a lot of sense as bots are indexing all of cgit:

[02/Aug/2024:08:19:25 +0000] "GET /cgit/aur.git/snapshot/aur-81a4d7f371be32598a98c52a8b4a8f5103be14d1.tar.gz HTTP/1.1" 200 5686 "-" "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_10_1) AppleWebKit/600.2.5 (KHTML, like Gecko) Safari/600.2.5 (Amazonbot/0.1; +https://developer.amazon.com/support/amazonbot)" 
 go.zybss.com - [02/Aug/2024:08:19:40 +0000] "GET /cgit/aur.git/tree/?h=act-sky130-git&id=81a4d7f371be32598a98c52a8b4a8f5103be14d1 HTTP/1.1" 200 1607 "-" "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_10_1) AppleWebKit/600.2.5 (KHTML, like Gecko) Safari/600.2.5 (Amazonbot/0.1; +https://developer.amazon.com/support/amazonbot)" "52.70.240.171" 0.036
 go.zybss.com - [02/Aug/2024:08:19:43 +0000] "GET /cgit/aur.git/diff/.SRCINFO?h=act-sky130-git&id=ce0b026adf35e1b61fdbb80e7ec76e7d1bac22db&id2=81a4d7f371be32598a98c52a8b4a8f5103be14d1 HTTP/1.1" 200 2474 "-" "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_10_1) AppleWebKit/600.2.5 (KHTML, like Gecko) Safari/600.2.5 (Amazonbot/0.1; +https://developer.amazon.com/support/amazonbot)" "52.70.240.171" 0.016
go.zybss.com - [02/Aug/2024:08:19:50 +0000] "GET /cgit/aur.git/commit/LICENSE?h=act-sky130-git&id=81a4d7f371be32598a98c52a8b4a8f5103be14d1 HTTP/1.1" 200 2018 "-" "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_10_1) AppleWebKit/600.2.5 (KHTML, like Gecko) Safari/600.2.5 (Amazonbot/0.1; +https://developer.amazon.com/support/amazonbot)" "52.70.240.171" 2.402
 go.zybss.com - [02/Aug/2024:08:19:57 +0000] "GET /cgit/aur.git/diff/LICENSE?h=act-sky130-git&id=81a4d7f371be32598a98c52a8b4a8f5103be14d1&id2=e8b231692617f2dcddf7fc47204ad62883958802 HTTP/1.1" 200 1757 "-" "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_10_1) AppleWebKit/600.2.5 (KHTML, like Gecko) Safari/600.2.5 (Amazonbot/0.1; +https://developer.amazon.com/support/amazonbot)" "52.70.240.171" 0.019

mentioned in merge request !854 (merged)

We did now also cache the /rss endpoint: aurweb!812 (merged)

The top requests:

[root@aur aur.archlinux.org]# cat access.log | awk '{print $7}' | sort -n | uniq -c | sort -nr | head -30
 228022 /rpc
  25619 /rpc/v5/info
  22591 /static/css/archnavbar/aurlogo.png
  22550 /static/images/favicon.ico
  20749 /static/css/archweb.css
  20445 /static/css/aurweb.css
  19202 /static/js/typeahead.js
  18577 /packages.gz
  17251 /static/js/copy.js
  16575 /static/js/comment-edit.js
  15865 /
  12592 /packages-meta-ext-v1.json.gz
   9849 /google-chrome.git/git-upload-pack
   7818 /rss/
   7041 /brave-bin.git/git-upload-pack
   5280 /google-chrome.git/info/refs?service=git-upload-pack
   4905 /yay.git/git-upload-pack
   4861 /metrics
   4788 /static/images/rss.svg
   4654 /cgit.js
   4652 /visual-studio-code-bin.git/git-upload-pack
   4646 /static/js/typeahead-home.js

And a bunch of /rpc requests.

Of all those requests, /rpc goes to our go service. /static and /*.gz should go only to nginx.

• /rss - now cached, but not in nginx so hits the backend
• / - not cached, requires investigation
• /*/git-upload-pack should be git pulls

Although the total amount of hits is not neccesarily a good metric, see the following list sorted by avg response time (shot the access.log on aurweb through goaccess):

 Hits       h%  Vis.     v% Tx. Amount Avg. T.S. Cum. T.S. Max. T.S. Mtd  Proto    Data
 ------ ------ ----- ------ ---------- --------- --------- --------- ---- -------- ----
   7818  0.37%  2425  1.98%  44.41 GiB   4.88  s  10.61 hr  17.80 mn GET  HTTP/2   /packages-meta-ext-v1.json.gz
   9299  0.44%  8095  6.61%   3.77 GiB 687.19 ms   1.78 hr   1.39 mn GET  HTTP/2   /packages.gz
   3858  0.18%  2964  2.42%   1.58 GiB 583.87 ms  37.54 mn   1.14 mn GET  HTTP/1.1 /packages.gz
   1554  0.07%     3  0.00%  79.01 MiB 505.03 ms  13.08 mn   3.92  s GET  HTTP/1.1 /packages/librewolf-bin
   1553  0.07%     3  0.00%  47.18 MiB 408.95 ms  10.58 mn   1.98  s GET  HTTP/1.1 /packages/abcde
   1554  0.07%     4  0.00%  44.38 MiB 310.93 ms   8.05 mn   3.07  s GET  HTTP/1.1 /packages/pokerth
   1551  0.07%     2  0.00%  37.97 MiB 290.62 ms   7.51 mn   2.65  s GET  HTTP/1.1 /packages/buku-git
   1551  0.07%     1  0.00%  30.67 MiB 238.39 ms   6.16 mn   3.00  s GET  HTTP/1.1 /packages/libmp3splt
   3021  0.14%  1118  0.91% 132.72 MiB 198.21 ms   9.98 mn   1.00 mn POST HTTP/2   /yay.git/git-upload-pack
   1551  0.07%     1  0.00%  29.51 MiB 171.35 ms   4.43 mn   1.59  s GET  HTTP/1.1 /packages/buku_run-git
    641  0.03%   268  0.22%  27.14 MiB 138.93 ms   1.48 mn  22.72  s POST HTTP/2   /hyprland-git.git/git-upload-pack
   1551  0.07%     1  0.00%  37.11 MiB 138.84 ms   3.59 mn   1.80  s GET  HTTP/1.1 /packages/autojump
   1551  0.07%     1  0.00%  29.53 MiB 130.33 ms   3.37 mn   1.79  s GET  HTTP/1.1 /packages/mandelbrot-gtk-git
   1553  0.07%     3  0.00%  40.97 MiB 127.29 ms   3.29 mn   2.92  s GET  HTTP/1.1 /packages/mbrola
   2759  0.13%  1276  1.04% 143.98 MiB 124.20 ms   5.71 mn  43.84  s POST HTTP/2   /brave-bin.git/git-upload-pack
   1553  0.07%     3  0.00%  28.86 MiB 122.89 ms   3.18 mn   1.35  s GET  HTTP/1.1 /packages/python-pmw
   1552  0.07%     1  0.00%  31.05 MiB 122.74 ms   3.17 mn   2.88  s GET  HTTP/1.1 /packages/sengi-appimage
   1551  0.07%     1  0.00%  30.31 MiB 118.76 ms   3.07 mn   2.45  s GET  HTTP/1.1 /packages/ncurses-life
   4705  0.22%  2180  1.78% 143.41 MiB 114.15 ms   8.95 mn  38.13  s POST HTTP/2   /google-chrome.git/git-upload-pack
  10163  0.48%  4759  3.88% 374.92 MiB 110.18 ms  18.66 mn   1.06 mn POST HTTP/2   /visual-studio-code-bin.git/git-upload-pack
   1551  0.07%     1  0.00%  30.08 MiB 106.41 ms   2.75 mn   1.84  s GET  HTTP/1.1 /packages/mbrola-voices-de5

And this is sorted by cumulative time:

Hits       h%  Vis.     v% Tx. Amount Avg. T.S. Cum. T.S. Max. T.S. Mtd  Proto    Data
 ------ ------ ----- ------ ---------- --------- --------- --------- ---- -------- ----
   7818  0.37%  2425  1.98%  44.41 GiB   4.88  s  10.61 hr  17.80 mn GET  HTTP/2   /packages-meta-ext-v1.json.gz
   9299  0.44%  8095  6.61%   3.77 GiB 687.19 ms   1.78 hr   1.39 mn GET  HTTP/2   /packages.gz
   3858  0.18%  2964  2.42%   1.58 GiB 583.87 ms  37.54 mn   1.14 mn GET  HTTP/1.1 /packages.gz
  10163  0.48%  4759  3.88% 374.92 MiB 110.18 ms  18.66 mn   1.06 mn POST HTTP/2   /visual-studio-code-bin.git/git-upload-pack
   1554  0.07%     3  0.00%  79.01 MiB 505.03 ms  13.08 mn   3.92  s GET  HTTP/1.1 /packages/librewolf-bin
   7404  0.35%  3685  3.01%  59.88 MiB  91.52 ms  11.29 mn  11.00  s GET  HTTP/2   /
   1553  0.07%     3  0.00%  47.18 MiB 408.95 ms  10.58 mn   1.98  s GET  HTTP/1.1 /packages/abcde
   3021  0.14%  1118  0.91% 132.72 MiB 198.21 ms   9.98 mn   1.00 mn POST HTTP/2   /yay.git/git-upload-pack
 138093  6.58% 10445  8.53% 578.98 MiB   4.33 ms   9.96 mn  15.57  s POST HTTP/1.1 /rpc
   4705  0.22%  2180  1.78% 143.41 MiB 114.15 ms   8.95 mn  38.13  s POST HTTP/2   /google-chrome.git/git-upload-pack
   1554  0.07%     4  0.00%  44.38 MiB 310.93 ms   8.05 mn   3.07  s GET  HTTP/1.1 /packages/pokerth
   1551  0.07%     2  0.00%  37.97 MiB 290.62 ms   7.51 mn   2.65  s GET  HTTP/1.1 /packages/buku-git
   1551  0.07%     1  0.00%  30.67 MiB 238.39 ms   6.16 mn   3.00  s GET  HTTP/1.1 /packages/libmp3splt
   2759  0.13%  1276  1.04% 143.98 MiB 124.20 ms   5.71 mn  43.84  s POST HTTP/2   /brave-bin.git/git-upload-pack
   1551  0.07%     1  0.00%  29.51 MiB 171.35 ms   4.43 mn   1.59  s GET  HTTP/1.1 /packages/buku_run-git

So in general I think these results have to be interpreted carefully, but i.e. this tells us that generally the RPC is able to answer queries pretty fast and maybe some more general optimizsation on the /packages/ endpoint would be good.

Generally I think it would be good to get the opentelemetry stuff from sven running so we actually gain insights on what needs optimization

Thanks for the information, this is super useful. We noticed today that the AUR does ~ 600-700 queries/second https://monitoring.archlinux.org/d/MQWgroiiz/mysql-overview?orgId=1&refresh=1m&from=now-24h&to=now

So getting that down would be helpful, I believe we can optimise some queries which due to the use of an ORM follow the following pattern:

for id in (SELECT ID from PackageMaintainers where Pkgbase == $ID):
   SELECT * from Users where ID = id

I've seen this pattern for co-maintainers, provides.

We should be able to query everything in one go, I don't have exact numbers of how many queries an average package page. I wonder if there is something like django-debug-toolbar for fastapi.

See queries logged here https://gist.github.com/jelly/8adc16ad81b9973bd9aff61cf06c7ca4

Some SQL query optimising can be done aurweb#511 (closed) and one MR has been made to do less queries when not logged in aurweb!816 (merged)

We've deployed aurweb!816 (merged)

The amount of queries has gone down a bit, overall the change is good but the impact on CPU usage by mariadb isn't very high.

Maybe interesting to also investigate this metric:

Why is rollback the second highest command?

Thanks for looking into this, let's get there bit by bit!

The rollback stuff might be due to how SQLAlchemys connection pooling works according to this post: https://stackoverflow.com/questions/7559570/make-sqlalchemy-commit-instead-of-rollback-after-a-select-query

I would also suggest to run some kind of query log for a while and analyze there WHERE and ORDER BY clauses of the most used queries and maybe add some indices to the tables. This can have a huge impact on performance both in terms of load of the mariadb process and in response time of the query and as such also the request in question.