PostgreSQL generates lots of WAL data for an idle Geo instance
In the last 24 hours on my mostly idle GitLab Geo instance, I count 937 WAL files of 16 MB, which comes out to be 15 GB of WAL data. That's an insane amount of data for an idle instance. We also see Geo customers are running into out of disk space issues when their secondary goes offline.
@_stark said in https://gitlab.com/gitlab-org/gitlab-ee/issues/4434#note_52676137 that it might be because our projects tables are wide and frequently updated, but I'm not sure if that's the cause.
Is there a way to peek at WAL data?
I found https://github.com/laurenz/pgreplay and enabled log_statement = 'all' on my PostgreSQL server to see if we can gather more information. So far I see lots of SELECT statements and very little UPDATE or INSERT traffic, but I do see these regular updates every 30 seconds or so:
2018-02-01_19:59:17.96121 LOG: statement: BEGIN
2018-02-01_19:59:17.96133 LOG: statement: SET LOCAL random_page_cost TO 1
2018-02-01_19:59:17.96156 LOG: statement: SELECT
2018-02-01_19:59:17.96160 projects.namespace_id,
2018-02-01_19:59:17.96163 ci_builds.status,
2018-02-01_19:59:17.96166 projects.shared_runners_enabled,
2018-02-01_19:59:17.96169 (COALESCE(namespaces.shared_runners_minutes_limit, application_settings.shared_runners_minutes, 0) = 0 OR
2018-02-01_19:59:17.96172 COALESCE(namespace_statistics.shared_runners_seconds, 0) < COALESCE(namespaces.shared_runners_minutes_limit, application_settings.shared_runners_minutes, 0) * 60) as has_minutes,
2018-02-01_19:59:17.96175 COUNT(*) AS count
2018-02-01_19:59:17.96180 FROM ci_builds
2018-02-01_19:59:17.96183 JOIN projects
2018-02-01_19:59:17.96185 ON projects.id = ci_builds.project_id
2018-02-01_19:59:17.96187 JOIN namespaces
2018-02-01_19:59:17.96189 ON namespaces.id = projects.namespace_id
2018-02-01_19:59:17.96191 LEFT JOIN namespace_statistics
2018-02-01_19:59:17.96193 ON namespace_statistics.namespace_id = namespaces.id
2018-02-01_19:59:17.96195 JOIN application_settings
2018-02-01_19:59:17.96197 ON application_settings.id = 1
2018-02-01_19:59:17.96199 WHERE ci_builds.type = 'Ci::Build'
2018-02-01_19:59:17.96201 AND ci_builds.status IN ('created', 'pending')
2018-02-01_19:59:17.96203 AND projects.pending_delete = 'f'
2018-02-01_19:59:17.96206 GROUP BY
2018-02-01_19:59:17.96208 projects.namespace_id,
2018-02-01_19:59:17.96210 ci_builds.status,
2018-02-01_19:59:17.96212 projects.shared_runners_enabled,
2018-02-01_19:59:17.96214 namespaces.shared_runners_minutes_limit,
2018-02-01_19:59:17.96216 namespace_statistics.shared_runners_seconds,
2018-02-01_19:59:17.96219 application_settings.shared_runners_minutes
2018-02-01_19:59:17.96221
2018-02-01_19:59:17.97091 LOG: statement: COMMIT/cc: @abrandl, @yorickpeterse