15:07 <aakashjain> rjarry: thanks for the comment on https://github.com/buildbot/buildbot/issues/4327

15:07 <aakashjain> I am using postgres. Our instance has grown significantly. About ~10 TB

15:08 <aakashjain> with that it has also slowed down, and I am trying to figure out way to speed it up

15:21 <rjarry> aakashjain: 10T !!

15:21 <rjarry> you should really think about running my script, lol

15:21 <aakashjain> yup!

15:21 <rjarry> you can increase the cleanup day period

15:21 <rjarry> and do it progressively

15:22 <rjarry> -- Delete soucestamps older than %s days.

15:22 <rjarry> DELETE FROM sourcestamps

15:22 <rjarry> WHERE created_at < (SELECT max(created_at) - (%s * 24 * 60 * 60) FROM sourcestamps);

15:22 <rjarry> -- The starting point is not "now" but the most recent sourcestamp.

15:25 <aakashjain> rjarry: Yeah, that make sense

15:26 <aakashjain> Here is my db details: https://tinyurl.com/2vuptndj

15:29 <aakashjain> rjarry: how is %s used? would it be a parameter to the script?

15:29 <rjarry> you should replace it with the number of days until which you want to keep stuff

15:29 <rjarry> you can start with 365

15:29 <rjarry> 1 year$

15:34 <aakashjain> rjarry: yeah. I have a copy of the database, on that I tried just the first delete statement from your script (DELETE FROM sourcestamps WHERE created_at < (SELECT max(created_at) - (365 * 24 * 60 * 60) FROM sourcestamps);), and it didn't finish it ~3 hours. This is even though sourcestamps table is just few mbs. Not sure why it was taking so long (cpu usage was at 100% by postgres during that time). That's why I was wondering if I will

15:34 <aakashjain> be able to run the complete script in one go, when I wasn't able to run a single command.

15:39 <aakashjain> rjarry: could it really lead to inconsistency if I execute each of the DELETE, UPDATE statements one by one sequentially, instead of inside a BEGIN.. COMMIT block?

16:09 <rjarry> aakashja_: maybe you can try to tune the postgres server params

16:09 <rjarry> I managed to drastically improve performance by using hugepages

16:09 <rjarry> also, by improving overall cache sizes

16:10 <rjarry> how old is your database ?

17:09 <aakashja_> rjarry: ~4 years

17:09 <aakashja_> rjarry: which specific cache parameters?

17:44 <p12tic> is the sourcestamps table in https://tinyurl.com/2vuptndj before or after cleanup?

18:16 <rjarry> aakashja_: I guess you should start with (3 * 365 * 24 * 60 * 60)

18:16 <rjarry> 1/4th of the db

18:16 <rjarry> roughly

18:18 <rjarry> about postgresql params:

18:19 <rjarry> shared_buffers = 128MB

18:19 <rjarry> huge_pages = try

18:20 <rjarry> work_mem

18:20 <rjarry> read the manual for more info

18:20 <rjarry> https://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server

18:22 <rjarry> https://github.com/jfcoz/postgresqltuner looks interesting

18:56 <p12tic> note that for hugepages support it must be enabled in the kernel and potentially preallocated

19:26 <p12tic> tardyp: Could you please create a repository at buildbot/trac.buildbot.net or similar? I don't have permissions for that

19:27 <p12tic> I've created a html archive of the old content of trac.buildbot.net from a DB dump

19:27 <p12tic> Hopefully we can point trac.buildbot.net to the archive and avoid broken links which can sometimes be useful

19:28 <p12tic> the archive contains only the tickets, the wiki was already broken and pointing to our github half of the time

20:18 <aakashja_> rjarry: tardyp p12tic: would be there any inconsistency or other problem if I delete old data from some tables, but not from others (e.g.: deleting old data from builds, buildsets, steps etc, but not deleting from logs, logchunks tables)

20:19 <p12tic> we have cascade delete enabled, so deleting stuff from builds will delete all related data that refers to them

20:35 <aakashja_> p12tic: nice. So in this script https://gist.github.com/rjarry/0b746d8df3c21154a083e07a2be3e6b3#file-cleanupdb-sql-L65 (posted in https://github.com/buildbot/buildbot/issues/4327#issuecomment-565793743) after deleting builds (on line 65) can we skip deleting build_properties, steps, logs, logchunks etc.?

20:37 <p12tic> yes, at least in theory. if you dump the DB schema you should see that tables contain "ON DELETE CASCADE" on foreign keys

21:05 <aakashja_> p12tic: nice. In general if I delete old data from some tables, but not others (especially skip logs, logchunks), can it lead to any major issue?

21:08 <p12tic> depends on which table. for example logs and log chunks are safe because it will appear to buildbot that steps just didn't produce any logs