10 ms·
Scaling the GitLab database
- d4l3k 9y agoDid you consider trying NewSQL DBs that would let you scale horizontally with much less complexity? Curious to see if the performance is comparable yet.
- tschellenbach 9y agoPostgres is generally a much better choice for relational data. For their hosted platform they should eventually consider Cassandra for some of the most commonly used tables. (the table storing revisions for instance)
- YorickPeterse 9y agoNo, because it probably would have required a complete rewrite of GitLab to make it work.
- kodablah 9y agoUser mentioned "trying". With an app that gets this much use, it might be reasonable to take a small piece and fork all writes to both the current + an experimental DB and then A/B test the reads. If this type of abstraction requires "a complete rewrite of GitLab" for "trying NewSQL DBs" then something is amiss.
- mnutt 9y agoGiven that Gitlab is open source, adding a NewSQL DB into the mix would be pushing that dependency to downstream users, who would then have to take on the maintenance burden. It may end up being the right call from a performance standpoint but just too complex to ask users to manage.
- kodablah 9y agoThis burden already exists with dependencies (and we should probably say downstream "admins" instead of "users" to clarify). A self-hosted app should not be hamstrung by its initial dependency choices. If tested and implemented properly it can go from optional experimental flag, to multiple-storage-backends-supported abstraction, to [maybe] even we're-migrating-to-new-storage-backend, etc. The primary downside in these cases is the flexibility often provided to plugin and extension writers which marries them to early tech choices. The more admins that demand low change count on forward movement are indirectly the reason many companies choose to offer SaaS only where the visibility and flexibility are removed. Note, I'm not saying this is the case with GitLab at all and I assume Postgres will remain the primary choice for most of their DB uses in perpetuity, but for some use cases there is better tech to use and the only reason to say no shouldn't just be a dependency addition (though it is often a good reason among many to say no).
- deleted 9y ago[deleted]
- YorickPeterse 9y agoIt would require a complete rewrite for at least the data layer, as everything right now assumes an RDBMS. Further it would complicate installation as now everybody would have to use a fancy "NewSQL" database, some of which you may not even be able to run yourself.
- tytytytytytytyt 9y agoAre you talking about NoSQL databases, like Mongo and Redis?
- Floegipoky 9y agoI think NewSQL refers to database systems that still enforce ACID transactions, but use various strategies to avoid the scalability difficulties that have traditionally been associated with RDBMS as both consistent and available systems. https://en.wikipedia.org/wiki/NewSQL https://en.wikipedia.org/wiki/NewSQL
- tschellenbach 9y agoRunning PG bouncer is a very basic optimization. You typically start out with PG bouncer in your stack if you have experience running Postgres. If you're new to running your own postgres databases you should also check out Wall-e: https://github.com/wal-e/wal-e https://github.com/wal-e/wal-e And the awesome pg stat statements https://www.postgresql.org/docs/10/static/pgstatstatements.html https://www.postgresql.org/docs/10/static/pgstatstatements.h...
- derefr 9y agoI’ve set up both pgbouncer and wal-e in the past, and they’re both worth it to have from effectively the very beginning. But it leaves me wondering: is there any sort of packaging of Postgres and these “friends” into a single opinionated virtual appliance, such that I could just stick up a couple of VMs with the same image with different role tags, and get a good cluster with automatic transparent N:M proxying, automatic transparent backup-and-restore, etc? In other words, is there a product that is to Postgres as Gitlab CE is to Github (or like Dokku is to Heroku): a “host it yourself, but easily, and starting from a scale of 1 with no overhead” equivalent of a SaaS service?
- snuxoll 9y agoIf you're deploying on K8S at least Crunchy Data has you covered [0], I haven't used it myself but it's the closest I've been able to find without going with something like EnterpriseDB. I'm pretty sure there's some ansible/puppet/chef/saltstack code out there to build something similar as well. [0] https://github.com/CrunchyData/crunchy-containers https://github.com/CrunchyData/crunchy-containers
- mslot 9y agoThere's a postgres operator by Zalando which sets up postgres with WAL-E and auto failover using Patroni. https://github.com/zalando-incubator/postgres-operator https://github.com/zalando-incubator/postgres-operator
- craigkerstiens 9y ago
- RandomBK 9y agoThis article is also very useful in showing just how far you can push Postgres _without_ reaching for any of these optimizations. I've seen too many projects worry about these things very early on in their lifecycle, when in reality they are no where close to having enough traffic to cause a problem.
- dfox 9y agoAnd almost certainly you can push postgresql even more by throwing hardware (ie. RAM) at the problem, although this is probably the point when it stops being worthwhile for reasonably sane applications (I've seen deployments where single pgsql server with 2TB+ RAM was cheaper solution than this kind of optimisation, due to all the technical debt in applications)
- mamcx 9y agoGreat point. Is easy to overlook how much you can do with a decent RDBMS. And for the small data that gitlab use, I believe still exist a lot of big wins on performance. Is just that the new generation not pay much attention to Sql databases... P.D: I don't mean the gitlab developers, just on general
- nickh9000 9y agoThe whole point why you should worry from the beginning is so that you don't have to re-work everything and put a huge risk to the business when you have to do it.
- RandomBK 9y agoCertainly. However, there's a difference between keeping scalability in mind, and actually implementing/maintaining a complex database scaling system at the onset of a project. Edit: I would also add that most projects I've seen tend to undergo one or more large refactors / redesigns before growing to a size where complex DB scaling is needed. This is, of course, speaking from the perspective of a small startup or pet project. If you're writing a new feature for an existing product where you can assume that it'll have lots of users, then you would obviously build scaling right from the start.
- vincentdm 9y agoFunny thing is that they publicly own up to their performance problems in an unusual way: in their comparison with GitHub (https://about.gitlab.com/comparison/ https://about.gitlab.com/comparison/) they list "Fast page load" as a feature that GitLab lacks and GitHub has. Nevertheless, the slowness is really annoying, especially because their product is so good on all other accounts. If scaling their database can help speed things up, I bet they will be glad to remove this embarrassing "missing feature". In marketing terms, having fast page load would be called a "qualifier". For example: you expect a hotel to provide toilet paper. You won't pick any hotel because of it, but you will surely avoid one that doesn't.
- teraflop 9y agoThat's probably the best "feature comparison" page I've ever seen. (At least, the best written by a creator of one of the products being compared.)
- deleted 9y ago[deleted]
- sytse 9y agoThanks! We try to keep it fair. I love that our marketing team is on board with listing our missing features very publicly https://about.gitlab.com/features/#missing https://about.gitlab.com/features/#missing
- deleted 9y ago[deleted]
- dominotw 9y ago> 2/3 of Enterprises Use GitLab wow that a lot higher that i would have expected. Linked bitrise page used " randomly selected 10,000 apps as a base, that are getting built regularly on Bitrise" , not "enterprises". How did you comeup with 2/3 number? First line of your about page is a lie?
- treve 9y ago
- twojustin 9y agoMy team is currently using the hosted cituscloud, which uses PG Bouncer. They note that the reason for sharding is because of high writes. We've actually seen big benefits for moving over to a sharded setup via Citus just as much for the read performance. By sharding by customer we're able to more effectively leverage Postgres caching and elastically scale. Since switching over, our database has performed and scaled much better than when we were on a single node. There is some up front migration work. But that's limited some minor patching of activeRecord ORM, and changes to migration scripts. We considered casandra, elastic search, as well as dynamoDB, but the amount of migration changes in the application logic would take an unacceptable amount of time.
- craigkerstiens 9y agoCraig from Citus here. Thanks for the kind words Justin, as you mention there is some upfront work, but once it's in place it becomes fairly manageable. We've been working to make that upfront work easier as well with libraries that allow things to be more drop-in (ActiveRecord-multi-tenant: https://github.com/citusdata/activerecord-multi-tenant https://github.com/citusdata/activerecord-multi-tenant and Django-multitenant: https://github.com/citusdata/django-multitenant https://github.com/citusdata/django-multitenant)
- contrahax 9y agoPraying for a http://docs.sequelizejs.com/ http://docs.sequelizejs.com/ plugin
- sb8244 9y agoOne of the things that I'm now excited about for citus is multi node IO capacity. It should improve write and read latencies in the situation where a single node disk is getting saturated. * Edit. I AM excited for this. Typo'd now = not previously
- craigkerstiens 9y agoNot sure I fully follow on the multi node IO capacity, can you share a bit more on the workload that you're concerned about? Edit: Thanks for the clarification, the typo part in particular through me off, makes sense now.
- karmakaze 9y agoOther options available: - CockroachDB 1.1 - AWS RDS Aurora PostgreSQL-compatibility [1] https://news.ycombinator.com/item?id=15458900 https://news.ycombinator.com/item?id=15458900 [2] https://news.ycombinator.com/item?id=13072861 https://news.ycombinator.com/item?id=13072861
- web007 9y agoAurora RDS-PG still isn't GA, as in isn't available in all datacenters. Edit: Apparently it is GA as of 6 days ago, but still only available in 4 regions https://aws.amazon.com/blogs/aws/now-available-amazon-aurora-with-postgresql-compatibility/ https://aws.amazon.com/blogs/aws/now-available-amazon-aurora...
- lozenge 9y agoThere are only 9 regions with at least 3 AZs (as required by Aurora design), it's available in 4 of them.
- elvinyung 9y agoCockroachDB is a really cool product, but I think by all accounts it is still nowhere near performant enough to be used in production.
- forgot-my-pw 9y agoAlternative is Google Spanner. Also still new, but already in GA for 5 months now.
- deleted 9y ago[deleted]
- nickh9000 9y agoApparently I should stop using gitlab.com By choosing to not shard your data you have basically put a cap on scalability... or at least one which will be reached faster than if you were to decide to shard.
- YorickPeterse 9y agoFollowing that same logic you probably should stop using a lot of websites out there.
- awj 9y agoWeb service architectures are like shoes. You want what fits now, not what you hope will fit later. Chances are very good that preemptive scaling will just result in unnecessary development overhead and an architecture/set of abstractions that still fail to handle the expected load since they weren't battle tested against it.
- nimbius 9y agofor anyone new to gitlab, it is a bear. even their own cluster/HA documentation is mostly just a thin roadmap to a final goal. a statement of intent if you will. There are just far too many moving parts in this software and the glaring problem of "ruby doesnt scale well" is everpresent. packaged releases depend entirely on a bundled chef installation to get everything out the door and running. The software tries to do too much. CI/wiki/code review/git/container repository/analytics package. CI sharding per team and carving up gitlab into departments and groups has been my only solution so far, and its just distributed risk at that. its the difference between the unix ethos of do one thing and do it well, versus the F35 approach of do everything forever.
- brianwawok 9y agoOn the flipside, I quite enjoy Gitlab. CI/CD that is very easy to use. I can self host it - so no $600 a month contract like some of the other guys. Decent Issues that are not as heavyweight as Jira. Place for code / CI/CD / Issues to all be in one place.
- YorickPeterse 9y ago> and the glaring problem of "ruby doesnt scale well" is everpresent GitLab has many performance problems (and many have been solved over the years), but Ruby has thus far not been one of them.
- GrayShade 9y agoI remember installing GitLab on a small NAS-type box. It was a while ago, but on each start-up it ran a nodejs tool to pre-compile some assets, I suppose. On that machine, it took 10-15 minutes to start. Afterwards, the unicorn workers kept getting killed because the default memory limit (128 MB) wasn't enough to process more than literally a couple of requests. It did work, but pages took 2-5 seconds to load, which I couldn't stand. For anyone needing a small Git Web UI, I suggest https://github.com/go-gitea/gitea https://github.com/go-gitea/gitea. EDIT: Oh, you're that guy ^^;. Anyway, my rant was probably a bit harsh. I know that GitLab isn't really written for hardware comparable to a Raspberry Pi and that it's used by small and large teams all over the world. But on my hardware Gitea renders pages in 20-100 ms, which is orders of magnitude faster than GitLab. I'm sure part of this is the fault of the language.
- knownothing 9y agoIt's interesting to see what parts of the product GitLab is struggling with compared to GitHub, e.g. https://githubengineering.com/stretching-spokes/ https://githubengineering.com/stretching-spokes/
- YorickPeterse 9y agoMost of our problems thus far have been the result of misuse of our resources one way or another. GitHub on the other hand probably dealt with these issues earlier on (or are able to hide them somehow, perhaps by throwing a lot of physical hardware at the problem), thus they can now focus on dealing with problems caused by reaching the limits of their (old) systems.
- sytse 9y agoI'm very impressed with the level GitHub database testing is at https://githubengineering.com/mysql-testing-automation-at-github/ https://githubengineering.com/mysql-testing-automation-at-gi... We still got a lot of work ahead of us. But Yorick and Greg their work has been outstanding. Regarding Spokes we're not planning something like that at this time. From https://gitlab.com/gitlab-org/gitaly/issues/650 https://gitlab.com/gitlab-org/gitaly/issues/650 "GitHub eventually moved to Spokes https://githubengineering.com/building-resilience-in-spokes/ https://githubengineering.com/building-resilience-in-spokes/ that had multiple fileservers for the same repository. We will probably not need that. We run networked storage in the cloud where the public cloud provider is responsible for the redundancy of the files. To do cross availability zone failover we'll use GitLab Geo."
- knownothing 9y agoThanks for chiming in, sytse and YorickPeterse! Re: misuse, I remember this incident https://news.ycombinator.com/item?id=11245652 https://news.ycombinator.com/item?id=11245652 made me chuckle and cry a little. If you build it, it will be misused.
- bzillins 9y agoLooking through the public Grafana dashboards I noticed that the database backups page is not showing any data points (http://monitor.gitlab.net/dashboard/db/backups http://monitor.gitlab.net/dashboard/db/backups). Is this meant to a public page?
- sytse 9y agoThis is meant to be a public page. It loads async. After 3 seconds I see the following https://www.dropbox.com/s/5mt5f77e60yogk1/Screenshot%202017-10-30%2015.50.05.png?dl=0 https://www.dropbox.com/s/5mt5f77e60yogk1/Screenshot%202017-...
- YorickPeterse 9y agoThere was an issue with the dashboard causing it to try and load data from the wrong place, this has been fixed. See https://gitlab.com/gitlab-com/infrastructure/issues/3115 https://gitlab.com/gitlab-com/infrastructure/issues/3115 for more info.
- mbid 9y agoSimple -- just clean the database from time to time [1]. BTW, I like how the banner on the bottom of [1] wants me to "Try GitLab Enterprise Edition risk-free for 30 days." currently. [1] https://about.gitlab.com/2017/02/10/postmortem-of-database-outage-of-january-31/ https://about.gitlab.com/2017/02/10/postmortem-of-database-o...
- tkyjonathan 9y agoI am a bit disappointed at this article and the hierarchy of how to optimise postgres. As a MySQL specialist that has worked on a few Postgres projects, I can tell you that Postgres has a VERY RICH toolkit to speed things up. Between optimising queries and load balancing, you have indexing, partitioning.. (Postgres even allows you to index EACH partition differently) and triggers to do summary/rollup tables for you. Its just that Postgres may need a little more coding and is less automatic than MySQL that people shy away from it.. I do like the time and information spent about spreading load accross database servers and not nearly enough people are considering splitting reads and writes from a master/slave setup. Its just that its frustrating to me that engineers view databases as some sort of mysterious black box that you need to solve by working around it or replacing it entirely.
- ericfrederich 9y agoSounds like a design flaw... adding a centralized database to augment a decentralized version control system. Comments, issues, pull requests, wiki, etc... should all be first class Git objects. I should be able to create pull requests while offline and push them up when I'm back online.
- cbcoutinho 9y agoI would rather baseline git be light and barebones - all the extra stuff is nice to have from a provider, but being part of git itself would make it bloat out.
- stephenr 9y agoIssues can be in dvcs with something like BugsEveryehere. Git has native "pull requests" it's just not web based, it's email based. A good wiki is markdown docs in a git/hg repo and something like Gollum to serve web viewers. So - in a way those things can all be handled as regular content in git/hg.
- justinclift 9y agoNot seeing any mention of using something like Memcached (or anything similar) in front of PG for caching. Is that because it's already been done and not in scope? Asking because for (at least) web applications, using Memcached (or similar) can significantly reduce the load on the backend database. :)
- sytse 9y agoWe have a lot of caching in Redis, but that connects with the Rails app, not in front of PG. This way it can store the result (for example a page fragment) instead of just the db query that is part of it.
- justinclift 9y agoCool, just making sure. :)
- deleted 9y ago[deleted]
- mschuster91 9y ago> A side effect of using transaction pooling is that you cannot use prepared statements, as the PREPARE and EXECUTE commands may end up running in different connections; producing errors as a result. Uhhh... WHAT? I'll simply sit by and watch for SQL injection vulnerabilities resulting from this change. Either you have a very good SQL query writer engine with rock solid escaping or you will get pwned by this.
- YorickPeterse 9y agoYou can have perfectly valid input escaping without needing prepared statements. Many ORMs handle this without the need for PREPARE/EXECUTE.
- mschuster91 9y ago> You can have perfectly valid input escaping without needing prepared statements. People have bypassed this far too often for my taste, and there is a reason why SQL (and other injections) are Top 1 of OWASP (1st place actually, both in RC1 and 2 of 2017, but also in the 2013 edition). Never, ever trust any form of escaping or it will bite you. Hard.
- ris 9y ago> Never, ever trust any form of escaping or it will bite you. Hard. This is an absurd dogmatic statement. Everybody trusts a multitude of escapings everywhere every day all over the web without even knowing it.
- anonacct37 9y agoHow much do you trust the postgres developers? https://www.postgresql.org/docs/9.1/static/libpq-exec.html#LIBPQ-EXEC-ESCAPE-STRING https://www.postgresql.org/docs/9.1/static/libpq-exec.html#L...
- flukus 9y agoWhat is transaction pooling anyway? A transaction exists for a specific action, it shouldn't be shared among users or multiple connections. It sounds like they've taken transactions and removed the isolation. I don't know what the data access is like, but I get the impression that it's like some "enterprise" patterns I've seen where data access is too "encapsulated", ie, each DAL opens it's own connection, pulls it's chunk of data and each request involves many of these instead of having each request open the connection and pass it down to the data access layers.
- dantiberian 9y ago> Sharding would also affect the process of contributing changes to GitLab as every contributor would now have to make sure a shard key is present in their queries. Why is this a problem? If you need to make a change to improve scalability, it doesn't seem unreasonable to ask contributors to follow code and performance guidelines.
- maktouch 9y agoWe're moving away from gitlab because the performance is too much to bear. We're hearing "aaaaaarg I really hate gitlab" a few times a day. The pipeline pages takes 7 seconds to load. It's really bad. We stopped using issues because just listing them was a chore. Pushing 1 file takes at least 30 seconds to 1 minute. Anyways, the work falls on me to replace it. We're going with Phabricator and Jenkins.
- josegonzalez 9y agoIs that self-hosted Gitlab or their hosted version? Would you mind sharing details on your setup? Just curious as we're currently building out our own setup.
- maktouch 9y agoRight now: - Self hosted Gitlab CE - Gitlab runners in pre-emptibles nodes with autoscaling Future (almost done, still in testing phase): - Phabricator (for git, code review and issues) - Jenkins ("lightweight" CI, more details at the bottom) - Google Cloud Container Builder ("Heavyweight" CI) Our main repository is in Phabricator, but it has mirroring to Google Cloud Repository (for backup and faster heavyweight builds) On commits, we run the lightweight CI in Jenkins. What I mean by lightweight is that, there's only a few runners and what it does is check what has changed. We have a mono-repository with over 30 microservices, rebuilding all of them is a waste of time and resources, so we have scripts and easy to use manifests that defines what to do. We also have a lot of small CI checks that is just not worth it to run externally, like linting. Here's what our homegrown manifests looks like build_graphql: when_changed: - graphql - node_libraries/player-graphdb - node_libraries/player-auth - node_libraries/player-models script_build: graphql/cloudbuild.yaml script_skip: graphql/cloudbuild-skip.yaml The script skip takes the last successful build and retag the docker images to the current one. The build script sends the instructions to Google Cloud Container Builder to build the docker image, run the tests, and push it. We use Cloud Container Builder because managing those CI servers really sucks. Let me know what else you want to know.
- deepsun 9y ago> 2. Use a connection pooler ... I had my jaw dropped on that point -- they didn't use connection pooling? WTF? I'm seeing connection pools on pretty much any project I've worked on, even under 1/req/day load, it's pretty much "must have" since 90s. And it's really just a library (like dbcp2) and a few lines of config, with almost no drawbacks. I cannot believe engineers at GitLab level are so bad at, well, engineering. I'm losing faith in their produce, please show me that I'm wrong.
- maccard 9y agoWhy are they bad at engineering just for not using a feature that you were aware of? If anything gitlab have proven to be reasonably good at engineering, given they’ve made it this far with a single postgre instance with no tweaking. It also speaks wonders for pg itself.
- YorickPeterse 9y agoWe've had connection pooling on the client side since forever, just like any other Rails project. This article specifically talks about server side pooling.
- vthriller 9y ago> A side effect of using transaction pooling is that you cannot use prepared statements, as the PREPARE and EXECUTE commands may end up running in different connections. There is a way to use prepared statements, it's called pre_prepare [0]. You still need to tweak your code though to not execute PREPARE directly but rather put statements in special table, but you do that only once, and after that you EXECUTE like before. Another minor (in my view) caveat is that you need to reset pgbouncer↔postgres connections each time you update that statements table. [0] https://github.com/dimitri/preprepare https://github.com/dimitri/preprepare