Project

General

Profile

Actions

action #93925

closed

coordination #80142: [saga][epic] Scale out: Redundant/load-balancing deployments of openQA, easy containers, containers on kubernetes

coordination #92854: [epic] limit overload of openQA webUI by heavy requests

Optimize SQL on /tests/overview

Added by tinita almost 3 years ago. Updated over 2 years ago.

Status:
Resolved
Priority:
Low
Assignee:
Category:
Feature requests
Target version:
Start date:
2021-06-12
Due date:
% Done:

0%

Estimated time:

Description

For /tests/overview pages with a lot of jobs, retrieving data from our database is quite inefficient.
For every job several SQL statements are fired.
Some of them can be combined into one statement.
Also in several statements, too much columns are selected, where we need only a few. That includes costly DateTime columns.

Identify statements which can be combined, combine them and measure performance.


Related issues 1 (0 open1 closed)

Related to openQA Project - action #94111: Optimize /api/v1/jobsResolvedtinita2021-06-16

Actions
Actions #1

Updated by okurz almost 3 years ago

  • Priority changed from Normal to Low

Let's see that as something that can help and is certainly related but can't prevent an "overload" so reducing prio to "Low". But besides that no further impact, keep on the good work :)

Actions #2

Updated by kraih almost 3 years ago

Might also be possible to replace DateTime with a less heavy module like DateTime::Tiny in some cases.

Actions #3

Updated by mkittler almost 3 years ago

Also take note of #92854#note-13 regarding the DateTime overhead.

Actions #4

Updated by tinita almost 3 years ago

I made the following config changes on o3:

# /etc/apache2/global.conf
CustomLog /space/logs/apache2/access_log combined_with_time
# /etc/apache2/vhosts.d/openqa.conf
CustomLog /space/logs/apache2/openqa.access_log combined_with_time
# /etc/apache2/mod_log_config.conf
LogFormat "%h %l %u %t \"%r\" %>s %b \
\"%{Referer}i\" \"%{User-Agent}i\" %{ms}T"              combined_with_time

Added the files to /etc/logrotate.d/apache2

This way we can compare request times before and after a deployment.

Actions #5

Updated by tinita almost 3 years ago

I did the same changes on osd (except access_log was already enabled), though I had to touch different files.

I replaced combined with combined_with_time in /etc/apache2/global.conf and added it to /etc/apache2/mod_log_config.conf

I added combined_with_time to /etc/sysconfig/apache2 and reloaded apache, which generated /etc/apache2/sysconfig.d/global.conf.

Now we can see the time in /var/log/apache2/access_log

Actions #6

Updated by tinita almost 3 years ago

  • Status changed from In Progress to Feedback
Actions #7

Updated by tinita almost 3 years ago

Actions #8

Updated by tinita almost 3 years ago

  • Status changed from Feedback to In Progress

I will gather some statistics from the logs in the next days and report them here.
So far on o3 the average Tumbleweed build page took 46% of the time before the deployment.

Actions #9

Updated by tinita over 2 years ago

  • Status changed from In Progress to Resolved

Unfortunately I didn't look to closely on the load average when doing my test queries before the rollout.

I did the test queries today when the load was not too high (between 10-15) on osd.

# Yast
% perl -nwE'if (m{ /tests/overview\?distri=sle&version=15-SP3&build=187.1&groupid=129 } and m{(\d+)$}) { $count++; $sum += $1 } END { say sprintf "count: %d | sum: %d ms | avg: %.2f ms", $count, $sum, $sum/$count }' access_log-20210617
count: 17 | sum: 127184 ms | avg: 7481.41 ms
% perl -nwE'if (m{ /tests/overview\?distri=sle&version=15-SP3&build=187.1&groupid=129 } and m{(\d+)$}) { $count++; $sum += $1 } END { say sprintf "count: %d | sum: %d ms | avg: %.2f ms", $count, $sum, $sum/$count }' access_log-today
count: 46 | sum: 142907 ms | avg: 3106.67 ms
% perl -wE'say 3106.67/7481.41'
0.415251937803168

# HA
% perl -nwE'if (m{ /tests/overview\?distri=sle&version=15-SP3&build=178.1&groupid=143 } and m{(\d+)$}) { $count++; $sum += $1 } END { say sprintf "count: %d | sum: %d ms | avg: %.2f ms", $count, $sum, $sum/$count }' access_log-20210617                                                      count: 20 | sum: 147886 ms | avg: 7394.30 ms
% perl -nwE'if (m{ /tests/overview\?distri=sle&version=15-SP3&build=178.1&groupid=143 } and m{(\d+)$}) { $count++; $sum += $1 } END { say sprintf "count: %d | sum: %d ms | avg: %.2f ms", $count, $sum, $sum/$count }' access_log-today
count: 40 | sum: 153192 ms | avg: 3829.80 ms
% perl -wE'say 3829.80/7394.30'
0.517939493934517

So for those relatively big groups the average load time decreased to 42% (Yast) / 52% (HA).

Actions #10

Updated by okurz over 2 years ago

A significant and important improvement. Thanks!

Actions #11

Updated by okurz over 2 years ago

  • Related to action #94354: Optimize /dashboard_build_results and /group_overview/* pages added
Actions #12

Updated by okurz over 2 years ago

  • Related to deleted (action #94354: Optimize /dashboard_build_results and /group_overview/* pages)
Actions

Also available in: Atom PDF