4 ms·

I really hate to pile on, but after reading through this whole thread and the whole post-mortem, there are a few basic things that are troubling besides the wid

I really hate to pile on, but after reading through this whole thread and the whole post-mortem, there are a few basic things that are troubling besides the widely-acknowledged backup methodology. I don't see issues directly related to addressing these things. 1. notifications go through regular email. Email should be only one channel used to dispatch notifications of infrastructure events. Tools like VictorOps or PagerDuty should be employed as notification brokers/coordinators and notifications should go to email, team chat, and phone/SMS if severity warrants, and have an attached escalation policy so that it doesn't all hinge on one guy's phone not being dead. 2. there was a single database, whose performance problems had impacted production multiple times before (the post lists 4 incidents). One such performance problem was contributing to breakage at this very moment. I understand that was the thing that was trying to be fixed here, but what process allowed this to cause 4 outages over the preceding year without moving to the top of the list of things to address? Wouldn't it be wise to tweak the PgSQL configuration and/or upgrade the server before trying to integrate the hot standby to serve some read-only queries? And since a hot standby can only service reads (and afaik this is not a well-supported option in PgSQL), wouldn't most of the performance issues, which appear write-related, remain? The process seriously needs to be reviewed here. And am I reading this right, the one and only production DB server was restarted to change a configuration value in order to try to make pg_basebackup work? What impact did that have on the people trying to use the site a) while the database was restarting, and b) while the kernel settings were tweaked to accommodate the too-high max_connections value? Is it normal for GitLab to cause intermittent, few-minute downtimes like that? Or did that occur while the site was already down? 3. Spam reports can cause mass hard deletion of user data? Has this happened to other users? The target in this instance was a GitLab employee. Who has been trolled this way such that performance wasn't impacted? What's the remedy for wrongly-targeted persons? It's clear that backups of this data are not available. And is the GitLab employee's data gone now too? How could something so insufficient have been released to the public, and how can you disclose this apparently-unresolved vulnerability? By so doing, you're challenging the public to come and try to empty your database. Good thing you're surely taking good backups now! (We're going to glance over the fact that GitLab just told everyone its logical DB backups are 3 days behind and that we shouldn't worry because LVM snapshots now occur hourly, and that it only takes 16 hours to transfer LVM snapshots between environments :) ) 4. the PgSQL master deleted its WALs within 4 hours of the replica "beginning to lag" (<interrobang here>). That really needs to be fixed. Again, you probably need a serious upgrade to your PgSQL server because it apparently doesn't have enough space to hold more than a couple of hours of WALs (unless this was just a naive misconfiguration of the [min|max]_wal_size parameter, like the max_connections parameter?). I understand that transaction logs can get very large, but the disk needs to accommodate (usually a second disk array is used for WALs to ease write impact) and replication lag needs to be monitored and alarmed on. There were a few other things (including someone else downthread who pointed out that your CEO re-revealed your DB's hostnames in this write-up, and that they're resolvable via public DNS and have running sshds on port 22), but these are the big standouts for me. P.S. bonus point, just speculative: Not sure how fast your disks were, but 300GB gone in "a few seconds" sounds like a stretch. Some data may've been recoverable with some disk forensics. Especially if your Postgres server was running at the time of the deletion, some data and file descriptors also likely could've been extracted from system memory. Linux doesn't actually delete files if another process is holding their handle open; you can go into the /proc virtual filesystem and grab the file descriptor again to redump the files to live disk locations. Since your database was 400GB and too big to keep 100% in RAM, this probably wouldn't have been a full recovery, but it may have been able to provide a partial. The theoretically best thing to do in such a situation would probably be to unplug the machine ASAP after ^C (without going through formal shutdown processes that may try to "clean up" unfinished disk work), remove the disk, attach it to a machine with a write blocker, and take a full-disk image for forensics purposes. This would maximize the ability to extract any data that the system was unable to eat/destroy. In theory, I believe pulling the plug while a process kept the file descriptor open should keep you in reasonably good shape, as far as that goes after you've accidentally deleted 3/4 of your production database. The process never closes and the disk stops and the contents remain on disk, just pending unlink when the OS stops the process (this is one reason why it'd be important to block writes to the disk/be extremely careful while mounting; if the journal plays back, it may destroy these files on the next boot anyway). But someone more familiar with the FS internals would have to say definitively if it works that way or not. I recognize that such speculative/experimental recovery measures may have been intentionally forgone since they're labor intensive, may have delayed the overall recovery, and very possibly wouldn't have returned useful data anyway. Mentioning it mainly as an option to remain aware of.

YorickPeterse 10y ago
> 1. notifications go through regular email. Email should be only one > channel used to dispatch notifications of infrastructure events. Tools > like VictorOps or PagerDuty should be employed as notification > brokers/coordinators and notifications should go to email, team chat, and > phone/SMS if severity warrants, and have an attached escalation policy so > that it doesn't all hinge on one guy's phone not being dead. This is discussed in https://gitlab.com/gitlab-com/infrastructure/issues/1095 https://gitlab.com/gitlab-com/infrastructure/issues/1095. We're basically looking into moving to Prometheus monitoring combined with Pagerduty notifications. > 2. there was a single database, whose performance problems had impacted > production multiple times before (the post lists 4 incidents). One such > performance problem was contributing to breakage at this very moment. I > understand that was the thing that was trying to be fixed here, but what > process allowed this to cause 4 outages over the preceding year without > moving to the top of the list of things to address? High availability has been a thing we wanted to do for a while, but for whatever reason we just never got to it (until recently). Not sure exactly why. > Wouldn't it be wise to tweak the PgSQL configuration and/or upgrade the > server before trying to integrate the hot standby to serve some read-only > queries? The server itself is already quite powerful, and the settings should be fairly decent (e.g. we used pgtune, and spent quite a bit of time tweaking things). The servers currently have 32 cores, 440-something GB of RAM, and the disk containing the DB data uses Azure premium storage with around 700 GB of storage (we currently use 340). > And since a hot standby can only service reads (and afaik this is not a > well-supported option in PgSQL), wouldn't most of the performance issues, > which appear write-related, remain? The process seriously needs to be > reviewed here. Based on our monitoring data we have vastly more reads than writes. This means load balancing gets very interesting. Hot standby is also supported just fine out of the box, you just need something third party for the actual load balancing. > And am I reading this right, the one and only production DB server was > restarted to change a configuration value in order to try to make > pg_basebackup work? We suspect so. Chef handles restarting processes and we think it's currently still set to do a hard restart always, instead of doing a reload whenever possible. > What impact did that have on the people trying to use the site a) while the > database was restarting A few minutes of downtime as the DB is unavailable. > and b) while the kernel settings were tweaked to accommodate the too-high > max_connections value? No. We now reduced the max_connections to a lower value (1000) so we still have enough but don't to tweak any kernel settings. > Is it normal for GitLab to cause intermittent, few-minute downtimes like > that? Or did that occur while the site was already down? We've had a few too many cases like this in the past. We're aiming to resolve those, but unfortunately this is rather tricky and time consuming. > 3. Spam reports can cause mass hard deletion of user data? Yes. > Has this happened to other users? Not that I know of. > What's the remedy for wrongly-targeted persons? A better abuse system, e.g. one that makes it easier to see _who_ was reported. We're also thinking of adding a quorom kind of feature: to remove users more than 3 people need to approve it, something like that. > And is the GitLab employee's data gone now too? No. The removal procedure was throwing errors, causing it to roll back its changes. This kept happening, which prevented the user from being removed. So ironically an error saved the day here. > How could something so insufficient have been released to the public Code is written by developers, and developers are humans. Humans in turn make mistakes. Most project removal related code also existed before we started enforcing stricter performance guidelines. > and how can you disclose this apparently-unresolved vulnerability? By so > doing, you're challenging the public to come and try to empty your database There's no point in hiding it. Spending a few minutes digging through the code and you'll find it, and probably plenty other similar problems. If somebody tries to abuse it we'll deal with it on a case by case basis. > because LVM snapshots now occur hourly, and that it only takes 16 hours to > transfer LVM snapshots between environments :) LVM snapshots are stored on the host itself. As such if e.g. db1 loses data we can restore the snapshot in a few minutes. They only have to be transferred if we want to recover other hosts. Furthermore, in the Azure ARM environment the file transfer would be much faster compared to the classic environment. > 4. the PgSQL master deleted its WALs within 4 hours of the replica > "beginning to lag" (<interrobang here>). That really needs to be fixed. Yes, which is also something we're looking into. > Again, you probably need a serious upgrade to your PgSQL server because it > apparently doesn't have enough space to hold more than a couple of hours of > WALs (unless this was just a naive misconfiguration of the > [min|max]_wal_size parameter, like the max_connections parameter?) Probably just a naive configuration value since we have plenty of storage available. > There were a few other things (including someone else downthread who pointed > out that your CEO re-revealed your DB's hostnames in this write-up, and that > they're resolvable via public DNS and have running sshds on port 22), but > these are the big standouts for me. Revealing hostnames isn't really a big deal, neither is SSH running on port 22. In the worst case some bots will try to log in using "admin" usernames and the likes, which won't work. All hosts use public key authentication, and password authentication is disabled. > Not sure how fast your disks were, but 300GB gone in "a few seconds" sounds > like a stretch. Nope, after about 2 seconds the data was gone. Context: I ran said command. > Some data may've been recoverable with some disk forensics. When using psycial disks not used by anything else, maybe. However, we're talking about disks used in a cloud environment. Are they actually physical? Are they part of larger disks shared with other servers? Who knows. The chance of data recovery using special tools in a cloud environment is basically zero. > Especially if your Postgres server was running at the time of the deletion, > some data and file descriptors also likely could've been extracted from > system memory That only works for files still held on to by PostgreSQL. PostgreSQL doesn't keep all files open at all times, so it wouldn't help.

YorickPeterse 10y ago

> 1. notifications go through regular email. Email should be only one > channel used to dispatch notifications of infrastructure events. Tools > like VictorOps or PagerDuty should be employed as notification > brokers/coordinators and notifications should go to email, team chat, and > phone/SMS if severity warrants, and have an attached escalation policy so > that it doesn't all hinge on one guy's phone not being dead. This is discussed in https://gitlab.com/gitlab-com/infrastructure/issues/1095 https://gitlab.com/gitlab-com/infrastructure/issues/1095. We're basically looking into moving to Prometheus monitoring combined with Pagerduty notifications. > 2. there was a single database, whose performance problems had impacted > production multiple times before (the post lists 4 incidents). One such > performance problem was contributing to breakage at this very moment. I > understand that was the thing that was trying to be fixed here, but what > process allowed this to cause 4 outages over the preceding year without > moving to the top of the list of things to address? High availability has been a thing we wanted to do for a while, but for whatever reason we just never got to it (until recently). Not sure exactly why. > Wouldn't it be wise to tweak the PgSQL configuration and/or upgrade the > server before trying to integrate the hot standby to serve some read-only > queries? The server itself is already quite powerful, and the settings should be fairly decent (e.g. we used pgtune, and spent quite a bit of time tweaking things). The servers currently have 32 cores, 440-something GB of RAM, and the disk containing the DB data uses Azure premium storage with around 700 GB of storage (we currently use 340). > And since a hot standby can only service reads (and afaik this is not a > well-supported option in PgSQL), wouldn't most of the performance issues, > which appear write-related, remain? The process seriously needs to be > reviewed here. Based on our monitoring data we have vastly more reads than writes. This means load balancing gets very interesting. Hot standby is also supported just fine out of the box, you just need something third party for the actual load balancing. > And am I reading this right, the one and only production DB server was > restarted to change a configuration value in order to try to make > pg_basebackup work? We suspect so. Chef handles restarting processes and we think it's currently still set to do a hard restart always, instead of doing a reload whenever possible. > What impact did that have on the people trying to use the site a) while the > database was restarting A few minutes of downtime as the DB is unavailable. > and b) while the kernel settings were tweaked to accommodate the too-high > max_connections value? No. We now reduced the max_connections to a lower value (1000) so we still have enough but don't to tweak any kernel settings. > Is it normal for GitLab to cause intermittent, few-minute downtimes like > that? Or did that occur while the site was already down? We've had a few too many cases like this in the past. We're aiming to resolve those, but unfortunately this is rather tricky and time consuming. > 3. Spam reports can cause mass hard deletion of user data? Yes. > Has this happened to other users? Not that I know of. > What's the remedy for wrongly-targeted persons? A better abuse system, e.g. one that makes it easier to see _who_ was reported. We're also thinking of adding a quorom kind of feature: to remove users more than 3 people need to approve it, something like that. > And is the GitLab employee's data gone now too? No. The removal procedure was throwing errors, causing it to roll back its changes. This kept happening, which prevented the user from being removed. So ironically an error saved the day here. > How could something so insufficient have been released to the public Code is written by developers, and developers are humans. Humans in turn make mistakes. Most project removal related code also existed before we started enforcing stricter performance guidelines. > and how can you disclose this apparently-unresolved vulnerability? By so > doing, you're challenging the public to come and try to empty your database There's no point in hiding it. Spending a few minutes digging through the code and you'll find it, and probably plenty other similar problems. If somebody tries to abuse it we'll deal with it on a case by case basis. > because LVM snapshots now occur hourly, and that it only takes 16 hours to > transfer LVM snapshots between environments :) LVM snapshots are stored on the host itself. As such if e.g. db1 loses data we can restore the snapshot in a few minutes. They only have to be transferred if we want to recover other hosts. Furthermore, in the Azure ARM environment the file transfer would be much faster compared to the classic environment. > 4. the PgSQL master deleted its WALs within 4 hours of the replica > "beginning to lag" (<interrobang here>). That really needs to be fixed. Yes, which is also something we're looking into. > Again, you probably need a serious upgrade to your PgSQL server because it > apparently doesn't have enough space to hold more than a couple of hours of > WALs (unless this was just a naive misconfiguration of the > [min|max]_wal_size parameter, like the max_connections parameter?) Probably just a naive configuration value since we have plenty of storage available. > There were a few other things (including someone else downthread who pointed > out that your CEO re-revealed your DB's hostnames in this write-up, and that > they're resolvable via public DNS and have running sshds on port 22), but > these are the big standouts for me. Revealing hostnames isn't really a big deal, neither is SSH running on port 22. In the worst case some bots will try to log in using "admin" usernames and the likes, which won't work. All hosts use public key authentication, and password authentication is disabled. > Not sure how fast your disks were, but 300GB gone in "a few seconds" sounds > like a stretch. Nope, after about 2 seconds the data was gone. Context: I ran said command. > Some data may've been recoverable with some disk forensics. When using psycial disks not used by anything else, maybe. However, we're talking about disks used in a cloud environment. Are they actually physical? Are they part of larger disks shared with other servers? Who knows. The chance of data recovery using special tools in a cloud environment is basically zero. > Especially if your Postgres server was running at the time of the deletion, > some data and file descriptors also likely could've been extracted from > system memory That only works for files still held on to by PostgreSQL. PostgreSQL doesn't keep all files open at all times, so it wouldn't help.