[devel at bb.net] Buildbot 0.9 performance issue

[Aakash Jain]

Hi Everyone,

I am working on migrating our buildbot instance from 0.8.12 to 0.9.8. I did
not have performance issue with 0.8.12. However with buildbot 0.9 (with the
same configuration), I am seeing a lot of performance issues, mostly
related to database. I was expecting a performance improvement since
buildbot 0.9 moved from pickles to database (and designed for scalability
<https://medium.com/@tardyp31/d0d41bba07e1>), however I am noticing the
reverse.

My buildbot configuration is:
- buildbot 0.9.8
- single master
- postgresql db
- 200+ builders (queues)
- only 2 workers connected currently for testing
- decent hardware on master (8 gb ram, 8 cpu)


"buildbot start" is taking ~8 minutes (the start time increased gradually
over last few weeks). Also loading various buildbot webpages are slow (many
times webpage loads, but the build data load after ~30s). During this time
there isn't too much cpu/memory/IO load on master.

I enabled postgresql logs and I see extremely large number of SELECT
statements in the logs. I also see a large number of "ROLLBACK" in
postgresql logs. I have few questions:

1) Why I am seeing so many ROLLBACK in the postgresql logs? Any suggestions
to fix/debug it?

2) From postgresql logs, I notice that buildbot is running database queries
in a loop fetching data for individual builder/build/ (resulting in large
number of queries). Wouldn't it be more efficient to use a single database
query to fetch data for all the builders and then filter it in python code?

3) has anyone tested buildbot 0.9 for 200+ builders (for single-master)?


postgresql logs: https://goo.gl/ZCd19o


Relevant logs for ROLLBACK (#1):

2017-06-23 21:19:45.491 GMT 24705 0 localhost(56256) LOG:  statement:
*ROLLBACK*
2017-06-23 21:19:45.494 GMT 24697 0 localhost(56242) LOG:  statement:
SELECT changes.changeid, changes.author, changes.comments, changes.branch,
changes.revision, changes.revlink, changes.when_timestamp,
changes.category, changes.repository, changes.codebase, changes.project,
changes.sourcestampid, changes.parent_changeids
    FROM changes
    WHERE changes.sourcestampid = 6
2017-06-23 21:19:45.496 GMT 24717 0 localhost(56270) LOG:  statement:
SELECT changes.changeid, changes.author, changes.comments, changes.branch,
changes.revision, changes.revlink, changes.when_timestamp,
changes.category, changes.repository, changes.codebase, changes.project,
changes.sourcestampid, changes.parent_changeids
    FROM changes
    WHERE changes.sourcestampid = 6
2017-06-23 21:19:45.496 GMT 24715 0 localhost(56266) LOG:  statement:
*ROLLBACK*
2017-06-23 21:19:45.498 GMT 24697 0 localhost(56242) LOG:  statement:
*ROLLBACK*
2017-06-23 21:19:45.499 GMT 24698 0 localhost(56244) LOG:  statement:
SELECT changes.changeid, changes.author, changes.comments, changes.branch,
changes.revision, changes.revlink, changes.when_timestamp,
changes.category, changes.repository, changes.codebase, changes.project,
changes.sourcestampid, changes.parent_changeids
    FROM changes
    WHERE changes.sourcestampid = 6

Relevant logs for #2:

2017-06-23 21:19:52.221 GMT 24738 localhost(56312) LOG:  statement: SELECT
tags.name
    FROM tags JOIN builders_tags ON tags.id = builders_tags.tagid
    WHERE builders_tags.builderid = 218
2017-06-23 21:19:52.223 GMT 24738 localhost(56312) LOG:  statement: SELECT
tags.name
    FROM tags JOIN builders_tags ON tags.id = builders_tags.tagid
    WHERE builders_tags.builderid = 219
2017-06-23 21:19:52.226 GMT 24738 localhost(56312) LOG:  statement: SELECT
tags.name
    FROM tags JOIN builders_tags ON tags.id = builders_tags.tagid
    WHERE builders_tags.builderid = 220
2017-06-23 21:19:52.235 GMT 24738 localhost(56312) LOG:  statement: SELECT
tags.name
    FROM tags JOIN builders_tags ON tags.id = builders_tags.tagid
    WHERE builders_tags.builderid = 221


Thanks in Advance

-Aakash
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.buildbot.net/pipermail/devel/attachments/20170627/8e8a2b38/attachment.html>