连接池的Andr​​oid应用程序，连接到一个PostgreSQL数据库 [英] Connection pooling for an Android app which connects to a Postgresql DB

问题描述

我想有某种故障安全的地方，我的Andr​​oid的社交网络式的应用程序，这样，当很多用户都是通过它连接到我的PostgreSQL数据库，它继续运作，并处理大量并发连接。

I want to have some kind of failsafe in place for my Android social-network-style app so that when a lot of users are connected through it to my Postgresql database, it continues to function and to handle a large number of concurrent connections.

我不知道有多少用户期望在最大。负载，但约70用户在任何给定的时间将是很好的。

I don't know how many users to expect at max. load but about 70 users at any given time would be nice.

什么是通过code /基础设施，以实现这一目标的最佳途径？

What would be the best way to achieve this through code/infrastructure?

我已经计划使用一个小型Web服务来处理数据库连接，但我不知道我会怎样处理在我结束了使用任何平台的连接池，无论是它的PHP，Python或Ruby。

I already plan to use a small web service to handle the database connections, but I'm not sure how I would handle the connection pooling in any platform I end up using, whether its PHP, Python or Ruby.

推荐答案

每个平台都有不同的连接池接口。你需要阅读的文档，可以使用特定的平台（红宝石+ Rails的或其他），或使用一个通用池中层衣像PgBouncer。

How to do connection pooling

Each platform has a different connection pooling interface. You'll need to read the documentation for the specific platform you use (Ruby+Rails or whatever), or use a generic pooling midlayer like PgBouncer.

与一个工具（比如，与Zend框架PHP）回答将有无关与其他工具（如Ruby on Rails的）答案。即使您选择像PgBouncer，还有详细介绍相关的平台是如何处理事务的寿命，汇集模式选择基于应用程序的需求，等等。

Answers relating to one tool (say, PHP with Zend Framework) will have nothing to do with answers relating to another tool (like Ruby on Rails). Even if you choose something like PgBouncer, there are still details related to how the platform handles transaction lifetimes, pooling mode to choose based on app needs, etc.

所以，你需要首先确定你使用的是什么，你需要用它做什么。 然后的研究如何建立它的连接池。 （有很多工具，它只是自动）。

So you need to first determine what you're using and what you need to do with it. Then study how to set up its connection pooling. (With many tools it's just automatic).

如果你仍然坚持的阅读文档的平台后，选择的，提出新的详细和具体的的问题，适当地标记为平台。

If you're still stuck after reading the documentation for the platform you choose, ask a new detailed and specific question tagged appropriately for the platform.

不要让您的应用程序直接连接到PostgreSQL。 尤其的，如果它是在随机的客户端网络。

Don't have your app connect directly to PostgreSQL. Especially if it's over the Internet from random clients.

使用Web服务器PostgreSQL服务器附近，有它接受Web服务请求经纪人通过一个良好定义的Web API访问数据库的短事务that're范围要求为伟大的程度越好。

Use a web server near the PostgreSQL server and have it accept web service requests to broker access to the database via a well defined web API with short transactions that're scoped to request to as great as extent as possible.

这是不是收到智慧只是一个案件 - 有充分的理由这样做，而且严重的问题，从随机的设备上运行的PostgreSQL在互联网上

This isn't just a case of received wisdom - there are good reasons to do it, and serious problems with running PostgreSQL from random devices over the Internet.

通过交谈，PG直行过许多客户端的网络问题包括：

Issues with talking to Pg straight over the Internet from many clients include:

• 每个PostgreSQL后端是有成本的，无论是闲置或不。 PgBouncer在交易池模式有助于这一点在一定程度上。

• Each PostgreSQL backend has a cost, whether idle or not. PgBouncer in transaction pooling mode helps with this to some degree.

连接迷路随机当你工作在互联网上。无线滴，动态IP服务的IP地址发生变化，移动服务，淡出或最大输出容量或只是错开以及高数据包丢失在那里。这使你有很多的PostgreSQL连接不确定的状态，可能与开放的交易，让您＆LT; IDLE＆GT;在交易的问题和需要，以允许更多的连接，而不是真正在做的工作。

Connections get lost randomly when you're working over the Internet. WiFi drops, IP address changes on dynamic IP services, mobile service that fade out or max out in capacity or just stagger along with high packet loss there. This leaves you with lots of PostgreSQL connections in indeterminate states, probably with open transactions, giving you <IDLE> in transaction issues and the need to allow a lot more connections than are really doing work.

这是事务性 - 如果事情好好尝试一下完成，你可以终止交易，并知道它会不会产生影响

It's transactional - if something doens't finish you can terminate the transaction and know it'll have no effect.

一个服务器从你的Andr​​oid设备上的应用程序响应HTTP Web服务请求作为一个经纪人的数据库访问可以是一个很大的优势。

A server responding to HTTP web service requests from your app on Android devices to act as a broker for database access can be a big advantage.

• 您可以定义一个版本的API，所以当你介绍新功能或需要改变你不必打破老客户的API。这是可能使用存储过程或大量的意见PG，但能得到笨重。

• You can define a versioned API, so when you introduce new features or need to change the API you don't have to break old clients. This is possible with Pg using stored procedures or lots of views but can get clunky.

您严格控制数据库访问和事务的寿命范围。

You strictly control the scope of database access and transaction lifetimes.

您可以定义幂等API，在运行相同的请求多次，仅在其之一。 （我强烈建议做，因为下一个点的这一点）。

You can define an idempotent API, where running the same request multiple times only has an effect one. (I strongly recommend doing this because of the next point).

一切是无状态的，可以有短暂超时。如果有什么不工作，你只是重试。

Everything is stateless and can have short time-outs. If something doesn't work you just retry it.

通过一个池中的每个数据库连接断开，所以你没有空闲会话坐在一起。每个数据库后端正在努力实现最大吞吐量。

Every database connection goes via a pool, so you don't have idle sessions sitting around. Every database backend is working hard for maximum throughput.

您可以排队的工作了，而不是试图做吨，同时和颠簸的服务器。 （您也可以在交易池模式下做到这一点与PgBouncer）。

You can queue work up rather than trying to do tons concurrently and thrashing the server. (You can also do this with PgBouncer in transaction pooling mode).

...并重新您的编辑更改的问题的意思是：

... and re your edit to change the question's meaning:

您又重表现实在是一个完全不同的问题（应该preferably张贴这样）。在很短的版本：完全不可能predict不工作量多了很多信息，比如每个客户端应用程序请求的数据库请求的数量，类型的数据，类型的查询，数据的大小，查询的频率，缓存的实用性， ...... 不休。任何人谁声称明确回答这个问题，要么是第一个真正的精神在历史上或完全充满了它。

Your "Also" re performance is really a totally different question (and should preferably be posted as such). The very short version: totally impossible to predict without a lot more info on the workload, like number of db requests per client app request, kind of data, kind of queries, size of data, frequency of queries, practicality of caching, ...... endlessly. Anyone who claims to definitively answer that question is either the first true psychic in history or completely full of it.

您需要弄清楚大概是你的数据的大小，查询模式，等会。找出你能承受多少缓存在这样的Redis / memcached的，你如何过时让它得到，什么级别的缓存失效，你需要一个中层衣缓存。确定是否你的热数据集（您访问了很多）将适合在RAM与否。确定索引经常查询表是否适合在RAM与否。找出你粗糙的读/写平衡是什么，你写多少有可能被插入只（追加）以上的普通OLTP（插入/更新/删除）。虚拟了一个数据集和一些客户的工作负载。 然后的你就可以开始回答这个问题 - 也许。要做到这一点对你也有可模拟失速/烟消云散客户等。

You need to figure out roughly what your data size, query patterns, etc will be. Figure out how much you can afford to cache in a midlayer cache like redis/memcached, how stale you can let it get, what level of cache invalidation you need. Determine whether your "hot" dataset (that you access a lot) will fit in RAM or not. Determine whether the indexes for frequently queried tables will fit in RAM or not. Figure out what your rough read/write balance is and how much your writes are likely to be insert-only (append) or more regular OLTP (insert/update/delete). Dummy up a data set and some client workloads. Then you can start answering that question - maybe. To do it right you also have to simulate stalled/vanished clients, etc.

请参阅为什么它不只是一个又？

See why it's not just an "Also?".

这篇关于连接池的Andr​​oid应用程序，连接到一个PostgreSQL数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！