> 2021年09月09日信息消化 ### When Less is More: Database Connection Scaling origin: [When Less is More: Database Connection Scaling](https://richyen.com/postgres/2021/09/03/less-is-more-max-connections.html) ##### Introduction As a PostgreSQL Support Engineer, one common scenario we experience is a slow system on a reasonably powerful machine. In these cases, we often see that max_connections is set to 10,000 or more, sometimes even as high as 30,000. While we will advise that max_connections is too high and needs to be lowered, the usual response is, “Well, most of those connections are idle, so they shouldn’t affect performance.” This statement is not true, as an idle connection is not weightless. In this article, we’ll explore why a high idle connection count can be detrimental to database performance. 作为一名PostgreSQL支持工程师，我们经常遇到的一个情况是，在一台相当强大的机器上，系统很慢。在这些情况下，我们经常看到max_connections被设置为10,000或更多，有时甚至高达 30,000。虽然我们会建议说max_connections太高了，需要降低，但通常的反应是："嗯，这些连接大部分都是空闲的，所以它们不应该影响性能。" 这种说法是不正确的，因为空闲的连接并不是没有重量的。在这篇文章中，我们将探讨为什么高空闲连接数会对数据库性能造成损害。 #### The Math It is natural for a DBA or sysadmin (or an application developer) to configure PostgreSQL for the expected connection load. If, after discussion between teams on a project, the anticipated number of clients on an enterprise application would be 10,000, `max_connections = 10000` would get coded into `postgresql.conf` However, it seems that often times these values are decided by the business and not necessarily by those who have more intimate knowledge of the hardware. For example, in many enterprise data centers, a database server might be provisioned with 128 CPU cores. With hyperthreading on, the machine can handle 256 processes. At EDB, we would typically expect a comfortable load on a core to be around 4 processes. If we put this in a formula, it should look something like this: 对于DBA或系统管理员（或应用开发者）来说，为预期的连接负载配置PostgreSQL是很自然的。如果在一个项目的团队之间讨论后，企业应用的预期客户数量是10000个，那么`max_connections = 10000`就会被编码到`postgresql.conf`中。然而，似乎很多时候，这些值是由企业决定的，而不一定是由那些对硬件有更深入了解的人决定。例如，在许多企业的数据中心，数据库服务器可能被配置为128个CPU核心。在超线程开启的情况下，该机器可以处理256个进程。在EDB，我们通常期望一个核心上的舒适负载是4个进程左右。如果我们把它放在一个公式中，它应该是这样的: ``` max_connections = #cores * 4 ``` In the case of this large enterprise machine, `256 * 4 = 1024`, so `max_connections` should be set to no higher than 1024. Even this value is highly contested in the community, where many experts believe that `max_connections` should not exceed a few hundred. 在这个大型企业机器的情况下，`256 * 4 = 1024`，所以`max_connections`应该设置为不高于1024。即使这个值在社区中也有很大争议，许多专家认为`max_connections'不应超过几百个。 ##### “But the connections are all idle!” While it would be intuitive to think that it would be harmless to set `max_connections = 30000` and promise that the vast majority of those connections to be idle, I would encourage proponents of the high `max_connections` setting to think more deeply about the implications of having so many connections. In particular, we must recall that PostgreSQL is a process-based application, which means that the underlying operating system needs to perform context switches to run queries and perform the underlying interfacing with hardware. On particularly busy systems with high CPU counts there’s risk of being affected by cache line contention, which [I previously wrote about](https://richyen.com/postgres/2016/01/14/numa_spinlocks_issue.html) 虽然直觉上认为设置 "max_connections = 30000 "是无害的，并承诺这些连接中的绝大多数都是空闲的，但我鼓励高 "max_connections "设置的支持者更深入地思考拥有这么多连接的影响。特别是，我们必须记得PostgreSQL是一个基于进程的应用程序，这意味着底层操作系统需要执行上下文切换来运行查询和执行与硬件的底层接口。在CPU数量较多的特别繁忙的系统中，有可能受到高速缓存线争用的影响，[我以前写过](https://richyen.com/postgres/2016/01/14/numa_spinlocks_issue.html) Even if the operating system could handily manage thousands of processes simultaneously, Postgres’ supervisor process (i.e., `postmaster`) needs to keep tabs on each process/backend it has forked because of an incoming connection. This management done by `postmaster` is can become expensive, as non-idle queries require `postmaster` to get a snapshot of what’s visible/invisible, committed/uncommitted (aka, Transaction Isolation), and that requires [scanning a list of processes and their snapshot information](https://github.com/postgres/postgres/blob/317632f3073fc06047a42075eb5e28a9577a4f96/src/backend/storage/ipc/procarray.c). The larger the process list, the longer it will take to `GetSnapshotData()` 即使操作系统可以轻松地同时管理数以千计的进程，Postgres的主管进程（即`postmaster`'）也需要对它因进入的连接而分叉的每个进程/后端进行监控。这种由`postmaster`完成的管理是昂贵的，因为非空闲查询需要`postmaster`获得可见/不可见、已提交/未提交的快照（又称事务隔离），而这需要[扫描进程列表及其快照信息](https://github.com/postgres/postgres/blob/317632f3073fc06047a42075eb5e28a9577a4f96/src/backend/storage/ipc/procarray.c)。进程列表越大，"GetSnapshotData() "的时间就越长。 ##### A Simple Example To illustrate this point, I put together a [very rudimentary test](https://github.com/richyen/toolbox/blob/master/pg/jdbc/loadTest.java) to basically do the following: - open a connection - run `SELECT 1` - keep the connection open until the program ends While running this simple program to keep thousands of idle connections open, I ran `pgbench` on the side and gathered the `tps` output (with flags `--client=10 --transactions=5000``). The results of this test are below:  Granted, this was an older maching (128GB RAM on a 32-CPU VM with spindle disks on CentOS 7), but I think it still has the capability of illustrating the effect of increasing the number of idle connections. As we can see, when the number of idle connections increases, the throughput decreases. I ran two tests and their results are fairly similar. The spikes towards the end may have to do with caching or some other background activity. I purposely kept `autovacuum = on` for the two tests because a real system will likely have autovacuum on. For a third test, I turned off autovacuum, and while the performance was marginally better (because the active processes are no longer competing with autovacuum for I/O resources), we still see that **scaling up idle connections will negatively affect performance**. 当然，这是一个较早的maching（在CentOS 7上带有主轴磁盘的32-CPU VM上的128GB内存），但我认为它仍然有能力说明增加空闲连接数的效果。我们可以看到，当空闲连接数增加时，吞吐量会下降。我进行了两次测试，其结果相当相似。最后的峰值可能与缓存或其他一些后台活动有关。在这两项测试中，我特意保持 "自动真空=开启"，因为真正的系统可能会开启自动真空。在第三个测试中，我关闭了autovacuum，虽然性能略有提高（因为活动进程不再与autovacuum争夺I/O资源），但我们仍然看到，扩大空闲连接会对性能产生负面影响。 ##### How to Achieve High Throughput If `max_connections` cannot be set to more than a few hundred, how do we achieve high throughput on a very busy enterprise-grade application? One of the simplest ways to address this is by using a connection pooler like `pgbouncer`, which will allow the thousands of application connections to share a relatively small pool of database sessions. One of the advantages of doing this is that because `max_connections` can be kept low, administrators can be more generous with `work_mem`, as each of the fewer processes can get a larger share of the memory pool. Other ways of addressing heavy client application traffic include leveraging a mix of replication with HAProxy to achieve read scaling. 如果`max_connections`不能设置为几百个以上，我们如何在一个非常繁忙的企业级应用上实现高吞吐量？解决这个问题最简单的方法之一是使用`pgbouncer`这样的连接池，这将允许成千上万的应用程序连接共享一个相对较小的数据库会话池。这样做的好处之一是，由于`max_connections`可以保持在较低的水平，管理员可以对`work_mem`更加慷慨，因为每个较少的进程可以获得更大的内存池份额。其他解决大量客户端应用流量的方法包括利用HAProxy的混合复制来实现读取扩展。 ##### Conclusion We’ve briefly explored how connection scaling negatively affects database performance. [Andres Freund has written a more comprehensive article on this very topic](https://www.citusdata.com/blog/2020/10/08/analyzing-connection-scalability/), and more analysis and insights can be found in his post. From both his tests and mine, it is very clear that when it comes to `max_connections`, the reality is often the case that less is more, and additional software and tools should be employed to achieve better throughput. 我们已经简单地探讨了连接扩展是如何对数据库性能产生负面影响的。[Andres Freund就这个话题写了一篇更全面的文章](https://www.citusdata.com/blog/2020/10/08/analyzing-connection-scalability/)，更多的分析和见解可以在他的文章中找到。从他和我的测试中可以看出，当涉及到 "最大连接数 "时，现实情况往往是少即是多，应该采用额外的软件和工具来实现更好的吞吐量。 ### A Layman’s Guide to Recreational Mathematics Videos origin: [A Layman’s Guide to Recreational Mathematics Videos](https://samenright.com/2021/08/31/a-laymans-guide-to-recreational-mathematics-videos/) I have watched a *lot* of maths videos on the internet**. The medium of YouTube is quite well suited to maths**; maths books are frequently either boring or are really about maths history/psychology/sociology. People sometimes ask me for recommendations of maths channels and videos to watch, so I thought I would write this guide to have something to point them to. There are a number of channels that are good for formal education, like [Khan Academy](https://www.youtube.com/c/khanacademy) or [Organic Chemistry Tutor](https://www.youtube.com/c/TheOrganicChemistryTutor). There are also other channels that upload high-quality lectures, like the [Royal Institution](https://www.youtube.com/user/TheRoyalInstitution) and the channels of [various](https://www.youtube.com/user/GreshamCollege) [universities](https://www.youtube.com/channel/UCLnGGRG__uGSPLBLzyhg8dQ). But I don’t even study maths at university, so here I’m only going to discuss channels I watch for fun. #### Explainer channels [3Blue1Brown](https://www.youtube.com/channel/UCYO_jab_esuFRV4b17AJtAw) **3Blue1Brown** (real name Grant Sanderson) is my favourite maths YouTuber. He animates his videos with a software he created called **Manim**, which is now also used by VcubingX and Reducible. Even if you didn’t understand anything he was talking about, 3B1B’s videos are still worth watching for the pure art and enthusiasm. My favourite videos of his are the ones about error-correcting codes, Dirichlet’s theorem and his interactive quaternion explainer. He has recurring series on deep learning, differential equations, linear algebra and calculus, all of which are excellent high-level overviews of the respective topics. [Numberphile](https://www.youtube.com/user/numberphile/featured) This is the most well-known maths channel. It’s possible that 3B1B’s new releases get more attention, but Numberphile has been going for longerya and has a much larger archive. I enjoyed their recentreciente [series](https://www.youtube.com/watch?v=R4OvBB9KHMA) [of](https://www.youtube.com/watch?v=0zT16q3p24M) [videos](https://www.youtube.com/watch?v=6X2D497is6Y) featuring Neil Sloane, the founder of the Online Encyclopaedia of Integer Sequences. Some classics include the video on the [Josephus problem](https://www.youtube.com/watch?v=uCsD3ZGzMgE), the interview with [Terence Tao](https://www.youtube.com/watch?v=MXJ-zpJeY3E&t=6s), and the videos with [Ron](https://www.youtube.com/watch?v=HX8bihEe3nA&t=1s) [Graham](https://www.youtube.com/watch?v=GuigptwlVHo). It’s worth mentioning that [James Grime](https://www.youtube.com/c/singingbanana), [Katie Steckles](https://www.youtube.com/c/KatieSteckles), and [Henry Segerman](https://www.youtube.com/c/HenrySegerman/featured), frequent contributors to Numberphile, also have their own channels. > 看了这个 [The Golden Ratio (why it is so irrational) - Numberphile](https://www.youtube.com/watch?v=sj8Sg8qnjOg) > > 真的太强了。The Golden Ratio, the most irrational number, flower seeding... ```python # golder radio x=1+1/x x= (1±sqrt(5))/2 =1.618=-0.618 π = 3 + a bit. ``` [Stand-up Maths](https://www.youtube.com/user/standupmaths) Matt Parker describes himself as a stand-up mathematician: part comedian and part mathematician. He first received wide recognition from his Numberphile appearances and now he does live shows with his group [Festival of the Spoken Nerd](https://festivalofthespokennerd.com/). His best videos are: his stand-up routine about [spreadsheets](https://www.youtube.com/watch?v=UBX2QQHlQ_I&t=646s), his videos about the [hilarious](https://www.youtube.com/watch?v=_tpNuulTeSQ) [superpermutation saga](https://www.youtube.com/watch?v=OZzIvl1tbPo), and his [investigation](https://www.youtube.com/watch?v=PtKhbbcc1Rc) into whether “land area” assumes a country is perfectly flat. He also has a second channel, the highlight of which is the time he ran untested viewer-submitted code [on his Christmas tree](https://www.youtube.com/watch?v=v7eHTNm1YtU&t=3s). [Mathologer](https://www.youtube.com/c/Mathologer) Another excellent channel. This one is of intermediate production value between the guy-with-whiteboard channels and the 3B1B cinematic masterpieces. He has a great [video](https://www.youtube.com/watch?v=YuIIjLr6vUA) addressing the [infamous Numberphile claim](https://www.youtube.com/watch?v=w-I6XTVZXww) that the sum of all natural numbers is -1/12. Mathologer is strongest in [animating](https://www.youtube.com/watch?v=-dhHrg-KbJ0) [proofs](https://www.youtube.com/watch?v=Lk_QF_hcM8A). I am especially pleased by his Simpsons-[themed](https://www.youtube.com/watch?v=PQRttF8-iqA) [videos](https://www.youtube.com/watch?v=w4zqR7GhrqQ&list=PLWDqsVZb838P3DsIBapVsyDkVsM9OxLHa&index=28). [Eddie Woo](https://www.youtube.com/c/misterwootube/featured) This is probably the channel here with the fewest prerequisites, since Eddie is a **maths secondary school teacher** and his videos are just recordings of his lessons. But if you need to brush up on something needed for one of the other channels, I recommend him. He also covers some topics you may not know about, like how the [RSA encryption](https://www.youtube.com/watch?v=4zahvcJ9glg) [algorithm works](https://www.youtube.com/watch?v=oOcTVTpUsPQ). He suitably has teaching awards and [nominations](https://www.youtube.com/watch?v=igxb-K92StQ) for being really good. ##### Problem-solving channels [Flammable Maths](https://www.youtube.com/c/papaflammy/featured) Flammable Maths is one of the most active members of the YouTube maths community. The level of assumed knowledge varies massively between videos and even within them. He also has a meme-y aesthetic and sense of humourhumor that can become a bit much at times. His Christmas specials are good: these two videos featured many other well-known maths personalities, and he goes through problems every day during ‘Papa Flammy’s advent calendar’. [MindYourDecisions](https://www.youtube.com/user/MindYourDecisions) Presh Talwalkar, or MindYourDecisions, is the clickbait of maths on YouTube. Did you know that [*only* 6% of Korean 11-year-olds could solve this problem](https://www.youtube.com/watch?v=tYLS5FJ9GWs)?! All of his videos have the same basic format of working through some problem, animated with Powerpoint. Some random ones I liked: [the 25 horses problem](https://www.youtube.com/watch?v=i-xqRDwpilM) and some [deceptively simple](https://www.youtube.com/watch?v=xnE_sO7PbBs) [geometry problems](https://www.youtube.com/watch?v=BgrWHOocYZA). #### Other channels that sometimes talk about maths [Vsauce](https://www.youtube.com/channel/UC6nSFpj9HTCZ5t-N3Rm3-HA) Vsauce is perhaps the most popular educational YouTuber, and he has touched on maths a number of times. I recommend his videos on the [Banach-Tarski paradox](https://www.youtube.com/watch?v=s86-Z-CbaHA), the [napkin ring problem](https://www.youtube.com/watch?v=J51ncHP_BrY), and the [brachistochrone](https://www.youtube.com/watch?v=skvnj67YGmw&t=9s). I have to say, I respect how much detail he goes into, especially in the Banach-Tarski video. It has so many views that it’s plausible that, of all people in the world who know what the Banach-Tarski paradox is, more than 50% of them learned it from Vsauce. #### Music and fun You probably know Tom Lehrer’s [periodic table song](https://www.youtube.com/watch?v=AcS3NOQnsQM&t=102s), and you may have even seen Daniel Radcliffe [sing it on The Graham Norton Show](https://www.youtube.com/watch?v=rSAaiYKF0cs). What you may not know, however, is that Tom Lehrer had an entire careercareer as a mathematical musician! My favourites are [‘New Math’](https://www.youtube.com/watch?v=UIKGV2cTgqA), [‘That’s Mathematics’](https://www.youtube.com/watch?v=2VZbWJIndlQ), and my girlfriend and I are obsessed with [‘Lobachevsky’](https://www.youtube.com/watch?v=qU_j5cQ2sfQ). [TheGermanFox](https://www.youtube.com/c/TheGermanFox) has only uploaded three videos, but his [musical proof](https://www.youtube.com/watch?v=8KJtazJMyl0) of why *e* is irrational is actually really good and I can’t get it out of my head. [Vihart](https://www.youtube.com/user/Vihart/featured) makes fun, usually short, videos, some highlights being [this one](https://www.youtube.com/watch?v=4niz8TfY794) about musicmúsica theory and the [Pi](https://www.youtube.com/watch?v=Y4Lc5-6L1pE) [Day](https://www.youtube.com/watch?v=AHrth9lOfzo) [rants](https://www.youtube.com/watch?v=imfqczglelI). Pi Day (March 14th) used to inspire a lot more enthusiasm, but I guess it’s sufficiently mainstream now that it’s no longer cool? ### Misc - *plot twist 大どんでん返し* - languishing, mudding days - https://www.ted.com/talks/adam_grant_how_to_stop_languishing_and_start_finding_flow