koji

#2383 koji-gc query drags database performance down
Closed: Fixed by tkopecek. Opened by kevin.

Closed: Fixed
Reopen Issue

We are seeing a koji-gc call drag database performance way down. I am not sure why this just started recently.

postgres 2419869 74.6 3.5 2582764 2171060 ? Ss Jul02 721:52 postgres: koji koji 10.3.169.104(42854) SELECT

These queries usually take 100% or more cpu (they sometimes have parallel workers).

The query is:

koji    | archiveinfo_buildroot_idx                       |               | AccessShareLock     | t       | koji     | SELECT
MAX(create_event)                                                                                                             
                                                                          +| 2020-07-02 23:42:25.537984+00 | 16:06:59.532154  |
 2419869                                                                                                                       
         |                                                 |               |                     |         |          |       
        FROM standard_buildroot                                                                                                
                                                                          +|                               |                  |
         |                                                 |               |                     |         |          |
        WHERE buildroot_id IN (
                                                                          +|                               |                  |
         |                                                 |               |                     |         |          |
          SELECT buildroot_id
                                                                          +|                               |                  |
         |                                                 |               |                     |         |          |
          FROM buildroot_listing
                                                                          +|                               |                  |
         |                                                 |               |                     |         |          |
          WHERE rpm_id IN ((21708700), (21708701), (21708702), (21708703), (21708704), (21708705), (21708706), (21708707), (217
08708), (21708709), (21708710), (21708711), (21708712), (21708713), (21708714), (21708715), (21708716), (21708717), (21708718),
 (21708719), (21708720), (21708721), (21708722), (21708723), (21708724), (21708725), (21708726), (21708727), (21708728), (21708
729), (21708730), (21708731), (21708732), (21708733), (21708734), (21708735), (21708736), (21708737), (21708738), (21708739), (
21708740), (21708741), (21708742), (21708743), (21708744), (21708745), (21708746), (21708747), (21708748), (21708749), (2170875
0), (21708751), (21708752), (21708753), (21708754), (21708755), (21708756), (21708757), (21708758), (21708759), (21708760), (21
708761), (21708762), (21708763), (21708764), (21708765), (21708766), (2170

After a day or so of running these queries start to slow down other things.

Happy to try and gather more info. Terminating the query or restarting postgresql brings everything back to normal.

I am not sure which koji-gc job this is...

Here's an explain on the query: 

 Finalize Aggregate  (cost=22985579.78..22985579.79 rows=1 width=4)                                                    ->  Gather  (cost=22985579.56..22985579.77 rows=2 width=4)                                                                Workers Planned: 2                                                                                                  ->  Partial Aggregate  (cost=22984579.56..22984579.57 rows=1 width=4)                                                     ->  Nested Loop Semi Join  (cost=0.58..22984529.17 rows=20158 width=4)                               
                     ->  Parallel Seq Scan on standard_buildroot  (cost=0.00..252340.22 rows=9073722 width=8)       
                     ->  Index Only Scan using buildroot_listing_buildroot_id_key on buildroot_listing  (cost=0.58..
21435.66 rows=11173 width=4)                                                                                        
                           Index Cond: ((buildroot_id = standard_buildroot.buildroot_id) AND (rpm_id = ANY ('{217087
00,21708701,21708702,21708703,21708704,21708705,21708706,21708707,21708708,21708709,21708710,21708711
...

Metadata Update from @kevin:
- Custom field Size adjusted to None

Metadata Update from @tkopecek:
- Issue set to the milestone: 1.23

every rpm_id adds a cycle (or parallel seq scan) from some reason

And this one is texlive again with 6000+ rpms in single query.

Ok, some experimenting has shown, that planner is getting confused with larger number of rpms.

In brew it means that for build with 561 rpms it uses correct index scan, while 562 switched to looped sequential scans with semi hash join. Runnin analyze hadn't helped.

As this is hardcoded query, we can prepend it with forcing to not use hash join.

PR #2402

Metadata Update from @jcupova:
- Issue tagged with: testing-ready

Metadata Update from @jcupova:
- Issue tagged with: testing-done

Commit ae4893d5 fixes this issue

Commit 45224e5b fixes this issue

This issue has been migrated to Fedora Forge:
https://forge.fedoraproject.org/koji/koji/issues/2383

Please continue any further discussion there.
Metadata
None

testing-done testing-ready

None
None
Low
Related Pull Requests
Static archive. pagure.io was sunset in 2026; this is a read-only snapshot.
Red Hat Logo

Fedora is sponsored by Red Hat. Learn more about the relationship between Red Hat and Fedora »

© Red Hat, Inc. and others.