Get benchmark database on its own hardware
Working on getting benchmark database onto its own hardware. Problem is that default postgresql.conf settings such as that in the Docker container will have the following:
shared_buffers = 128MB
work_mem = 4MB
maintenance_work_mem = 64MB
There are other settings worth considering, but these are most likely to cause us pain during the benchmark runs. Without a large enough shared_buffers
we are going to be punished by excessive I/O to handle the benchmark data, and the small work_mem
means our queries with any kind of ordering or sorting are probably going to fail to fit within that size constraint so we will once again be doing excessive I/O via temp tables on disk.
tables sorted by size captured during a benchmark run:
relation | total_size
----------------------------------------------------------+------------
public.oban_jobs | 909 MB
public.activities | 234 MB
public.objects | 218 MB
public.users | 36 MB
public.notifications | 23 MB
public.conversations | 2728 kB
public.oban_beats | 1024 kB
public.conversation_participation_recipient_ships | 904 kB
public.conversation_participations | 744 kB
public.instances | 56 kB
public.config | 56 kB
public.following_relationships | 56 kB
public.oauth_tokens | 48 kB
public.apps | 48 kB
public.lists | 40 kB
public.moderation_log | 40 kB
public.scheduled_activities | 32 kB
public.user_invite_tokens | 32 kB
public.registrations | 32 kB
public.filters | 32 kB
public.deliveries | 24 kB
public.push_subscriptions | 24 kB
public.schema_migrations | 24 kB
public.markers | 24 kB
public.oauth_authorizations | 16 kB
public.bookmarks | 16 kB
public.thread_mutes | 16 kB
public.user_relationships | 16 kB
public.markers_id_seq | 8192 bytes
public.user_relationships_id_seq | 8192 bytes
public.password_reset_tokens_id_seq | 8192 bytes
public.password_reset_tokens | 8192 bytes
public.lists_id_seq | 8192 bytes
public.user_invite_tokens_id_seq | 8192 bytes
public.filters_id_seq | 8192 bytes
public.push_subscriptions_id_seq | 8192 bytes
public.users_id_seq | 8192 bytes
public.activities_id_seq | 8192 bytes
public.objects_id_seq | 8192 bytes
public.apps_id_seq | 8192 bytes
public.oauth_authorizations_id_seq | 8192 bytes
public.oauth_tokens_id_seq | 8192 bytes
public.notifications_id_seq | 8192 bytes
public.instances_id_seq | 8192 bytes
public.thread_mutes_id_seq | 8192 bytes
public.scheduled_activities_id_seq | 8192 bytes
public.activity_expirations | 8192 bytes
public.conversations_id_seq | 8192 bytes
public.conversation_participations_id_seq | 8192 bytes
public.bookmarks_id_seq | 8192 bytes
public.config_id_seq | 8192 bytes
public.activity_expirations_id_seq | 8192 bytes
public.oban_jobs_id_seq | 8192 bytes
public.deliveries_id_seq | 8192 bytes
public.conversation_participation_recipient_ships_id_seq | 8192 bytes
public.moderation_log_id_seq | 8192 bytes
public.following_relationships_id_seq | 8192 bytes
(57 rows)
Sure, we could change these for the docker container we prop up for the job but there's still other stuff running on that server which can interfere with the test results: backup jobs, orchestration tool kicking in occasionally to make sure the server is configured as expected, tons of additional processes that will wake up. Not to mention that the results of our different CI servers cannot be compared directly for query performance.
So this is why we will be moving benchmarks to a dedicated database server where we can compare results with confidence.