<div dir="ltr"><div>Hi Everyone,<br><br></div><div>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 (<a href="https://medium.com/@tardyp31/d0d41bba07e1" target="_blank">and designed for scalability</a>), however I am noticing the reverse.<br></div><div><br></div><div>My buildbot configuration is:<br></div><div>- buildbot 0.9.8<br></div><div>- single master<br></div><div>- postgresql db<br></div><div>- 200+ builders (queues)<br></div><div>- only 2 workers connected currently for testing<br></div><div>- decent hardware on master (8 gb ram, 8 cpu)<br><br><br></div><div>"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.<br><br>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:<br><br></div><div>1) Why I am seeing so many ROLLBACK in the postgresql logs? Any suggestions to fix/debug it?<br></div><div><br></div><div>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?<br></div><div><br></div><div>3) has anyone tested buildbot 0.9 for 200+ builders (for single-master)?<br><br></div><div><br>postgresql logs: <span style="color:rgb(68,68,68);font-family:Roboto,Helvetica,Arial,sans-serif;font-size:13px;font-style:normal;font-variant-ligatures:normal;font-variant-caps:normal;font-weight:normal;letter-spacing:normal;text-align:start;text-indent:0px;text-transform:none;white-space:normal;word-spacing:0px;text-decoration-style:initial;text-decoration-color:initial;display:inline;float:none"><a href="https://goo.gl/ZCd19o" target="_blank">https://goo.gl/ZCd19o</a><br></span></div><div><br><br>Relevant logs for ROLLBACK (#1):<blockquote><span style="font-family:monospace,monospace"><font size="2">2017-06-23 21:19:45.491 GMT 24705 0 localhost(56256) LOG: statement: <b>ROLLBACK</b><br>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 <br> FROM changes <br> WHERE changes.sourcestampid = 6<br>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 <br> FROM changes <br> WHERE changes.sourcestampid = 6<br>2017-06-23 21:19:45.496 GMT 24715 0 localhost(56266) LOG: statement: <b>ROLLBACK</b><br>2017-06-23 21:19:45.498 GMT 24697 0 localhost(56242) LOG: statement: <b>ROLLBACK</b><br>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 <br> FROM changes <br> WHERE changes.sourcestampid = 6</font><br><br></span></blockquote></div><div>Relevant logs for #2:<span style="font-family:monospace,monospace"></span><br><span style="font-family:monospace,monospace"></span><blockquote><span style="font-family:monospace,monospace"><span style="color:rgb(0,0,0)">2017-06-23 21:19:52.221 GMT 24738 localhost(56312) LOG: statement: SELECT <a href="http://tags.name">tags.name</a> <br> FROM tags JOIN builders_tags ON <a href="http://tags.id">tags.id</a> = builders_tags.tagid <br> WHERE builders_tags.builderid = 218<br>2017-06-23 21:19:52.223 GMT 24738 localhost(56312) LOG: statement: SELECT <a href="http://tags.name">tags.name</a> <br> FROM tags JOIN builders_tags ON <a href="http://tags.id">tags.id</a> = builders_tags.tagid <br> WHERE builders_tags.builderid = 219<br>2017-06-23 21:19:52.226 GMT 24738 localhost(56312) LOG: statement: SELECT <a href="http://tags.name">tags.name</a> <br> FROM tags JOIN builders_tags ON <a href="http://tags.id">tags.id</a> = builders_tags.tagid <br> WHERE builders_tags.builderid = 220<br>2017-06-23 21:19:52.235 GMT 24738 localhost(56312) LOG: statement: SELECT <a href="http://tags.name">tags.name</a> <br> FROM tags JOIN builders_tags ON <a href="http://tags.id">tags.id</a> = builders_tags.tagid <br> WHERE builders_tags.builderid = 221</span><br></span></blockquote><br></div><div>Thanks in Advance<br></div><div><br>-Aakash<br></div><div></div></div>