4 ms·
Author here, self-posted. Can AMA.
by sudhirj 6y ago
Author here, self-posted. Can AMA.
- victor106 6y agoIn depth article, would be good if you can cover Java based connection pool like HickariCP. They are very widely used
- sudhirj 6y agoI've added a link to the project in the context of another comment here about deadlocks... is there anything in particular you'd like to mention? Will look into the pool in detail to see if there's any special tips and tricks they use.
- pqdbr 6y agoGreat article! We have a Rails app that uses Puma with MySQL, but so far single threaded (the Puma config has `threads 1,1`), because we migrated from Unicorn and are still worrying about thread-safety. Currently our database.yml pool size is set to 25, because we have 25 puma workers on the server. Reading your article, this seems to be overkill, since that setting is _per worker_, and since each worker is running only with 1 thread, the default 5 should be more than enough? Also, I just checked and our MySQL is configured with 151 max connections; if every worker of ours can use up to 25 connections, that can lead up to 625 connections to the database, and that's without considering Sidekiq (concurrency 25).
- sudhirj 6y agoWell, even the default 5 would probably be overkill, unless you’re spawning threads on a request that each use their own DB connections. But yes, I think you can go with the the default. Once you’re confident about being threadsafe, the Heroku deployment guide for puma is pretty good for all the little knobs to turn.
- sudhirj 6y agoRe MySQL counts, based on your notes you’re probably using 50 conns at max whatever your settings are. 25 for sidekiq (assuming you do run a hot queue) and 25 at max for the servers. Irrespective of DB pool size, I think Rails does lazy connections, so you’re never going to go over 1.
- pqdbr 6y agoI changed database.yml to use 10 connections. I didn't change the Sidekiq configuration, which still uses concurrency: 25. Strangely, tonight I've seen the first exceptions like `ActiveRecord::ConnectionTimeoutError: could not obtain a connection from the pool within 5.000 seconds (waited 5.000 seconds); all pooled connections were in use` in many, many years. All of these exceptions came from within Sidekiq Workers, which is strange because, like I pointed, I didn't change the Sidekiq concurrency, only the database.yml pool, which shouldn't affect workers.
- sudhirj 6y agoIsn’t sidekiq working off Redis? You might be adjusting the Redis concurrency instead. And if I remember correctly even with sidekiq you’re still using normal Rails and ActiveRecord, so a process that’s running 25 sidekiq jobs simultaneously is going to need 25 active record DB conns from the database.yml. I doubt the sidekiq config is overriding it in some way. But basically if your sidekiq workers and app servers are using the same database.yml values, you’ll need to set the greater of the two on both. I assumed you have different settings on app servers and workers.
- artimaeis 6y agoJust 6 days ago you published what was a pretty great writeup on message queues, now this thorough dive on connections & pools; what's your writing schedule like? At some 12k words of fairly accurate and precise technical content I'm kind of blown away by your pace.
- sudhirj 6y agoI had a few paragraphs written earlier (maybe half) over the course of the pandemic. I spent a lot of the pandemic procrastinating by trying to build my own blogging software, a more efficient version of Ghost, but it’s too much duplicated effort given what I learned at AWS reinvent. So I decided to just set up and start publishing. I write for about two hours a day, maybe four days or writing and two of editing for each article. I’d like to publish once a week, but don’t want to set that kind of goal because then I’ll write even if I have nothing interesting to say. But maybe that’s the best process anyway? Will tell you what’s working if you ask again in a couple of months :-/
- johnonolan 6y agoFounder of Ghost here - would love to hear what parts you were hoping to make more efficient / if you have any suggestions :)
- sudhirj 6y agoI was trying to make it multi tenant, move it a serverless database like DynamoDB, and rewrite the server in Go. I’m trying to make a Ghost hosting service that’s free / $5 a month for personal use. I’ve figured out a way to make the economics work with the existing 500MB disk + 1GB RAM requirements, but it would have been a lot easier with Go and Dynamo. Thanks a ton to you and the team for the work you’re doing. I think you’re making a fantastic system, and want to make it available to more people as a personal blogging platform. I just wish it could run multi tenant or with lower memory needs. I don’t suppose making it easier for others to compete with the official Ghost revenue model is a priority, so I doubt this is anything you’ll want to take up. But I’m happy to share revenues on this - I just think Ghost should be a lot more accessible to individuals around the world.
- est 6y ago> the popular servers for Python, PHP and other languages that use a global lock or assume single-threaded single-process execution This is not true for python. The article you linked mentions Gunicorn, uWSGI and CherryPy, they are threaded and can handle multiple requests per process. Even if Python does have this GIL thing, it does not lock network operations for a tcp/udp based database protocol (some C based modules do tend to lock the GIL)
- sudhirj 6y agoWill edit that, yeah. I was am struggling to explain the GIL without having to talk about the GIL, because if open that can of worms it becomes a much longer article and goes off track. All of the GL and event loop based stacks go multi threaded a the lower library levels, really. Will think about how to explain that. And will move Python off that general category.
- mekoka 6y agoI'm confused by this statement about pgBouncer et al. These systems allow you to make as many database connections as you want and manage them inefficiently, because the connections they give you are fake & cheap simulated connections that they handle with low overhead. What makes them inefficient then, if they're cheap and have low management overhead?
- sankyo 6y agoThank you for this. Very few people can explain complicated ideas with easy to follow language. You have a gift for explaining.
- sudhirj 6y agoThank you for saying that. I enjoy helping people understand things in minutes, especially if those things took me years. Being stuck at home the only way to keep doing that is writing and videos. And I have a pretty bad stammer so videos aren’t an option.
- fotta 6y agoot: do plan on adding an RSS feed? Would love to subscribe.
- hansvm 6y agoIn case the author is worried about sufficient readership, I second this motion :)
- sudhirj 6y agoI thought this blog engine already had one, I certainly didn’t disable anything. Let me check.
- sudhirj 6y agoYeah, it’s active on https://sudhir.io/rss/ https://sudhir.io/rss/ Pasting the base URL into a feed reader should usually check the /rss path as a convention.
- fotta 6y agofantastic, thank you!
- toredash 6y agoAm I reading this correctly, that with NodeJS and PostgreSQL, the usage of multiple connection poolers _combined_ is a not-so-good idea? Example, using Knex as the connection pooler for your node app, which is connected to your PgBouncer deployment? I have not considered this a fault, in one way I find it compelling to have a layer of "protection" by using Knex in addition to PgBouncer, but not I'm not that sure anymore.
- sudhirj 6y agoIt depends on how knex is used. If it gives you a method to run a DB query, and internally checks out a connection, runs the query and releases the connection, then it’s already doing exactly what would happen with a proxy. If it or you are setting up middleware to start a transaction before every request, or put a checked out connection in the request context, that’s a lot of possible inefficiency. If you really want the convenience this offers, and have request flows that do a lot of other non-DB work, you’ll need a pooling proxy. If your request handling is very database heavy and does nothing else, then automatic request level connection check out is still efficient, even without a proxy. Even if you have a proxy one real connection is going to be working the entire duration of the request anyway.
- toredash 6y agoThanks for the feedback. I have some reading to do and some code to checkout.
- tjvc 6y agoGreat article, many thanks. > Rails auto-management needs to be conservative and cautious, therefore inefficient Can you help me understand this better? Are you saying that Rails tends to hold onto connections for longer than it needs to?
- sudhirj 6y agoYeah, Rails doesn't currently strongly analyze what it needs to do. Based on my experience with it, I remember that it either 1) checks out a connection at the beginning of the request into a thread local / request context and releases it at the end of the request, or 2) checks out when the first active record work is attempted, and releases at the end of the request. Either way, if you have a request flow of 1) DB read - 50ms 2) Network request - 200ms 3) DB write - 50ms, you can see that the connection is idle for 2/3 of your request handling time. Rails does this so that if you start a transaction in step 1 you can commit it in step 3. All your writes are also automatically wrapped in a transaction, I think. In Go on the other hand, step 1 will checkout and release the connection immediately, and step 3 will checkout and release. So the connection isn't idle locked for the 200ms in step 2. But if you don't remember this explicitly and try in step 3 to finish something (transaction/advisory lock/prepared statement) that you started in step 1, you're screwed. So Rails saved you from doom by holding onto a connection for an inefficient extra 200ms, and Go shoots you in the foot in the name of efficiency.
- tjvc 6y agoThat makes a lot of sense - thanks for explaining! :)