3 ms·
Scaling the GitLab database
- koolba 9y ago> Sticky connections are supported by storing a pointer to the current PostgreSQL WAL position the moment a write is performed. This pointer is then stored in Redis for a short duration at the end of a request. Each user is given their own key so that the actions of one user won't lead to all other users being affected. In the next request we get the pointer and compare this with all the secondaries. If all secondaries have a WAL pointer that exceeds our pointer we know they are in sync and we can safely use a secondary for our read-only queries. If one or more secondaries are not yet in sync we will continue using the primary until they are in sync. If no write is performed for 30 seconds and all the secondaries are still not in sync we'll revert to using the secondaries in order to prevent somebody from ending up running queries on the primary forever. Interesting approach. Kind of halfway between async replication and a per client synchronous commit. Why wait for all of the slave databases to be in sync? If at least one is in sync, can you get all database interactions in the application to the valid subset? Snippet of code from the article: query = "SELECT NOT pg_is_in_recovery() OR " \ "pg_xlog_location_diff(pg_last_xlog_replay_location(), #{string}) >= 0 AS result" > Here WAL-POINTER is the WAL pointer as returned by the PostgreSQL function pg_current_xlog_insert_location(), which is executed on the primary. In the above code snippet the pointer is passed as an argument, which is then quoted/escaped and passed to the query. Why escape the input and use string interpolation instead of a parameter?
- YorickPeterse 9y agoSorry for the late reply! > Why wait for all of the slave databases to be in sync? If at least one is in sync, > can you get all database interactions in the application to the valid subset? If queries fail on the replica we'll cycle through the available replicas until we have one that works. If we didn't wait for all to be in sync it could in theory be possible we'd retry our query on a host that does not yet have the data, though this would be very unlikely. So basically we're being a bit paranoid here. > Why escape the input and use string interpolation instead of a parameter? Rails doesn't really provide an easy way of doing this unfortunately. That is, to run raw queries you'll use something like this: ActiveRecord::Base.connection.execute 'your query here' This particular API doesn't support passing any parameters, so you have to resort to manually escaping data and using string interpolation.