I'm setting up a data-logger which is polling data from a restful web service (from a PLC installed on a production facility) and I need to write it on a PostgreSQL database.
我正在设置一个数据记录器,它从一个安静的Web服务(来自安装在生产设施上的PLC)中轮询数据,我需要在PostgreSQL数据库上编写它。
I usually need to read data every 30 seconds from 5 different machines, 24/24h and 6 days per week. That would be around 15.000 connections to the database everyday, if I decide to close the connection every time after the queries. I'm assuming that all 5 machines will read in different times, but of course we can reduce it to 3.000 queries, if I decide to read all of them simultaneously.
我通常需要每隔30秒从5台不同的机器读取数据,每周24小时24小时和6天。如果我决定在查询后每次关闭连接,那么每天大约会有15.000个连接到数据库。我假设所有5台机器都会在不同的时间读取,但当然我们可以将它减少到3.000个查询,如果我决定同时读取所有这些机器的话。
What is the best way to achieve a persistent connection using PostgreSQL? My doubt is that creating a database "handler" class and returning a "Connection" object to use could be affected by timeouts or errors (when the connection closes itself I'd not be able to log any data).
使用PostgreSQL实现持久连接的最佳方法是什么?我怀疑的是,创建数据库“处理程序”类并返回要使用的“连接”对象可能会受到超时或错误的影响(当连接自身关闭时,我无法记录任何数据)。
2 个解决方案
#1
1
Keep connections open
You seem to be saying that you have five JDBC clients that each need to do a query or write every half-minute around the clock for six days per week. If that is the case, I see no need for disconnecting the connection at all. Just have each JDBC client maintain an open connection.
您似乎在说,您有五个JDBC客户端,每个客户端需要进行查询,或者每半天每半小时写一次,每周六天。如果是这种情况,我认为根本不需要断开连接。让每个JDBC客户端保持一个开放的连接。
Why do you believe it is necessary to close your connections between the half-minute calls? If there are other factors to consider, edit your Question to clarify.
为什么你认为有必要关闭半分钟通话之间的联系?如果还有其他因素需要考虑,请编辑您的问题以澄清。
Be sure to test your JDBC connection as it may be lost with a network interruption or a restart of Postgres server. If the connection fails, open another.
请务必测试JDBC连接,因为它可能会因网络中断或重新启动Postgres服务器而丢失。如果连接失败,请打开另一个。
You can maintain a connection between your app and the Postgres server indefinitely. Keep in mind two things: txn timeout and fragility of network.
您可以无限期地维护应用程序与Postgres服务器之间的连接。请记住两件事:txn超时和网络的脆弱性。
Transaction timeout
Each client connection has default settings. One of these is idle_in_transaction_session_timeout (integer). If you keep a transaction longer than this limit, the transaction is rolled back and your connection (session) is closed.
每个客户端连接都有默认设置。其中一个是idle_in_transaction_session_timeout(整数)。如果您将事务保留的时间超过此限制,则会回滚事务并关闭您的连接(会话)。
If you know you will have transactions open for long durations, you can disable the timeout feature. Not generally recommended. Probably irrelevant in your use case. If you use transactions at all, your description sounds like they will be brief.
如果您知道长时间打开事务,则可以禁用超时功能。一般不推荐。可能与您的用例无关。如果你使用交易,你的描述听起来很简短。
To quote the documentation:
引用文档:
idle_in_transaction_session_timeout (integer)Terminate any session with an open transaction that has been idle for longer than the specified duration in milliseconds. This allows any locks held by that session to be released and the connection slot to be reused; it also allows tuples visible only to this transaction to be vacuumed. See Section 24.1 for more details about this.
使用空闲时间超过指定持续时间(以毫秒为单位)的打开事务终止任何会话。这允许释放该会话持有的任何锁,并重新使用连接槽;它还允许仅对此事务可见的元组进行清理。有关详细信息,请参见第24.1节。
The default value of 0 disables this feature.
默认值0禁用此功能。
Fragility of networks
Network connections are by their nature tenuous and fragile. Less experienced programmers tend to under appreciate this challenge as the development environment tends to be vastly more reliable than real-world deployment environments.
网络连接本质上是脆弱的。由于开发环境比实际部署环境更加可靠,因此经验不足的程序员倾向于不了解这一挑战。
A programmer must always take extra care when using network connections. See the fallacies of distributed computing. You must assume you will have network interruptions and failed database connections. Test the validity of your db connection each time you do work. Catch exceptions thrown by your JDBC driver. Use transactions where appropriate to protect the integrity of your data. That's the main reason transactions were invented: we expect failures.
程序员在使用网络连接时必须格外小心。查看分布式计算的谬误。您必须假设您将遇到网络中断和数据库连接失败。每次工作时都要测试数据库连接的有效性。捕获JDBC驱动程序抛出的异常。适当时使用事务来保护数据的完整性。这是交易发明的主要原因:我们期待失败。
In other words, there is no such thing as a ”persistent connection“ that you mention in the Question. There are simply connections. Whether kept open for 50 milliseconds or 50 days is irrelevant: all connections are at risk of failure at any moment. So, again, expect failure.
换句话说,你在问题中没有提到“持久连接”这样的事情。有简单的联系。无论是开放50毫秒还是50天都是无关紧要的:所有连接都随时都有失败的风险。所以,再次,期待失败。
Be sure to test for network failure in your development-testing cycles. There are high-tech ways to do that. And there is a low-tech favorite of mine: Pull the Ethernet cable while running.
确保在开发测试周期中测试网络故障。有很多高科技的方法可以做到这一点。我的最爱是低技术:在运行时拉动以太网电缆。
Consider deploying your app and the Postgres server on the same box if otherwise practical (enough cores, enough RAM, enough stability). A local connection between app and database will be *much much * more reliable (and faster) than distributed (networked) across machines. But other deployment issues may dictate separate machines. System-administration is all about trade-offs.
如果可行(足够的内核,足够的RAM,足够的稳定性),请考虑在同一个盒子上部署您的应用程序和Postgres服务器。应用程序和数据库之间的本地连接将比跨机器的分布式(联网)更加可靠(和更快)。但是其他部署问题可能会决定单独的机器。系统管理就是权衡取舍。
Threads
If using threads in your app, be sure they do not share a JDBC connection. Each thread should use its own connection.
如果在应用程序中使用线程,请确保它们不共享JDBC连接。每个线程都应该使用自己的连接。
You mentioned a "database handler" to "get a connection". Not sure what you meant. Generally I recommend a DataSource object on which you call getConnection. Your JDBC driver should provide an implementation.
您提到了“获取连接”的“数据库处理程序”。不确定你的意思。通常我建议您调用getConnection的DataSource对象。您的JDBC驱动程序应该提供实现。
Do not use connection pool
Connection pools are often suggested for database work, sometimes too often suggested reflexively without properly considering the pros and cons. Connection pools bring their own risks and complexities.
连接池通常被建议用于数据库工作,有时经常建议反思性地没有正确考虑利弊。连接池带来了自己的风险和复杂性。
And connection pools are most useful when you have many clients making intermittent calls to the database. Your situation is the opposite, few clients making many frequent regular calls to the database. So I would say a connection pool is contraindicated for you.
当许多客户端间歇性地调用数据库时,连接池最有用。您的情况恰恰相反,很少有客户经常拨打数据库。所以我会说连接池是禁忌的。
#2
1
Best way to share connections is a connection pooling, like DBCP for example.
共享连接的最佳方式是连接池,例如DBCP。
If you have different machines connecting to the database that is little bit more difficult. I would set up a service on a different machine or on one of the machines. for example via REST or another likely interface.
如果你有不同的机器连接到数据库,这有点困难。我会在不同的机器或其中一台机器上设置服务。例如通过REST或其他可能的接口。
In the end make sure to check your PostgreSQL config aswell. A good guide you will find in the postgres wiki.
最后一定要检查你的PostgreSQL配置。你会在postgres wiki中找到一个很好的指南。
#1
1
Keep connections open
You seem to be saying that you have five JDBC clients that each need to do a query or write every half-minute around the clock for six days per week. If that is the case, I see no need for disconnecting the connection at all. Just have each JDBC client maintain an open connection.
您似乎在说,您有五个JDBC客户端,每个客户端需要进行查询,或者每半天每半小时写一次,每周六天。如果是这种情况,我认为根本不需要断开连接。让每个JDBC客户端保持一个开放的连接。
Why do you believe it is necessary to close your connections between the half-minute calls? If there are other factors to consider, edit your Question to clarify.
为什么你认为有必要关闭半分钟通话之间的联系?如果还有其他因素需要考虑,请编辑您的问题以澄清。
Be sure to test your JDBC connection as it may be lost with a network interruption or a restart of Postgres server. If the connection fails, open another.
请务必测试JDBC连接,因为它可能会因网络中断或重新启动Postgres服务器而丢失。如果连接失败,请打开另一个。
You can maintain a connection between your app and the Postgres server indefinitely. Keep in mind two things: txn timeout and fragility of network.
您可以无限期地维护应用程序与Postgres服务器之间的连接。请记住两件事:txn超时和网络的脆弱性。
Transaction timeout
Each client connection has default settings. One of these is idle_in_transaction_session_timeout (integer). If you keep a transaction longer than this limit, the transaction is rolled back and your connection (session) is closed.
每个客户端连接都有默认设置。其中一个是idle_in_transaction_session_timeout(整数)。如果您将事务保留的时间超过此限制,则会回滚事务并关闭您的连接(会话)。
If you know you will have transactions open for long durations, you can disable the timeout feature. Not generally recommended. Probably irrelevant in your use case. If you use transactions at all, your description sounds like they will be brief.
如果您知道长时间打开事务,则可以禁用超时功能。一般不推荐。可能与您的用例无关。如果你使用交易,你的描述听起来很简短。
To quote the documentation:
引用文档:
idle_in_transaction_session_timeout (integer)Terminate any session with an open transaction that has been idle for longer than the specified duration in milliseconds. This allows any locks held by that session to be released and the connection slot to be reused; it also allows tuples visible only to this transaction to be vacuumed. See Section 24.1 for more details about this.
使用空闲时间超过指定持续时间(以毫秒为单位)的打开事务终止任何会话。这允许释放该会话持有的任何锁,并重新使用连接槽;它还允许仅对此事务可见的元组进行清理。有关详细信息,请参见第24.1节。
The default value of 0 disables this feature.
默认值0禁用此功能。
Fragility of networks
Network connections are by their nature tenuous and fragile. Less experienced programmers tend to under appreciate this challenge as the development environment tends to be vastly more reliable than real-world deployment environments.
网络连接本质上是脆弱的。由于开发环境比实际部署环境更加可靠,因此经验不足的程序员倾向于不了解这一挑战。
A programmer must always take extra care when using network connections. See the fallacies of distributed computing. You must assume you will have network interruptions and failed database connections. Test the validity of your db connection each time you do work. Catch exceptions thrown by your JDBC driver. Use transactions where appropriate to protect the integrity of your data. That's the main reason transactions were invented: we expect failures.
程序员在使用网络连接时必须格外小心。查看分布式计算的谬误。您必须假设您将遇到网络中断和数据库连接失败。每次工作时都要测试数据库连接的有效性。捕获JDBC驱动程序抛出的异常。适当时使用事务来保护数据的完整性。这是交易发明的主要原因:我们期待失败。
In other words, there is no such thing as a ”persistent connection“ that you mention in the Question. There are simply connections. Whether kept open for 50 milliseconds or 50 days is irrelevant: all connections are at risk of failure at any moment. So, again, expect failure.
换句话说,你在问题中没有提到“持久连接”这样的事情。有简单的联系。无论是开放50毫秒还是50天都是无关紧要的:所有连接都随时都有失败的风险。所以,再次,期待失败。
Be sure to test for network failure in your development-testing cycles. There are high-tech ways to do that. And there is a low-tech favorite of mine: Pull the Ethernet cable while running.
确保在开发测试周期中测试网络故障。有很多高科技的方法可以做到这一点。我的最爱是低技术:在运行时拉动以太网电缆。
Consider deploying your app and the Postgres server on the same box if otherwise practical (enough cores, enough RAM, enough stability). A local connection between app and database will be *much much * more reliable (and faster) than distributed (networked) across machines. But other deployment issues may dictate separate machines. System-administration is all about trade-offs.
如果可行(足够的内核,足够的RAM,足够的稳定性),请考虑在同一个盒子上部署您的应用程序和Postgres服务器。应用程序和数据库之间的本地连接将比跨机器的分布式(联网)更加可靠(和更快)。但是其他部署问题可能会决定单独的机器。系统管理就是权衡取舍。
Threads
If using threads in your app, be sure they do not share a JDBC connection. Each thread should use its own connection.
如果在应用程序中使用线程,请确保它们不共享JDBC连接。每个线程都应该使用自己的连接。
You mentioned a "database handler" to "get a connection". Not sure what you meant. Generally I recommend a DataSource object on which you call getConnection. Your JDBC driver should provide an implementation.
您提到了“获取连接”的“数据库处理程序”。不确定你的意思。通常我建议您调用getConnection的DataSource对象。您的JDBC驱动程序应该提供实现。
Do not use connection pool
Connection pools are often suggested for database work, sometimes too often suggested reflexively without properly considering the pros and cons. Connection pools bring their own risks and complexities.
连接池通常被建议用于数据库工作,有时经常建议反思性地没有正确考虑利弊。连接池带来了自己的风险和复杂性。
And connection pools are most useful when you have many clients making intermittent calls to the database. Your situation is the opposite, few clients making many frequent regular calls to the database. So I would say a connection pool is contraindicated for you.
当许多客户端间歇性地调用数据库时,连接池最有用。您的情况恰恰相反,很少有客户经常拨打数据库。所以我会说连接池是禁忌的。
#2
1
Best way to share connections is a connection pooling, like DBCP for example.
共享连接的最佳方式是连接池,例如DBCP。
If you have different machines connecting to the database that is little bit more difficult. I would set up a service on a different machine or on one of the machines. for example via REST or another likely interface.
如果你有不同的机器连接到数据库,这有点困难。我会在不同的机器或其中一台机器上设置服务。例如通过REST或其他可能的接口。
In the end make sure to check your PostgreSQL config aswell. A good guide you will find in the postgres wiki.
最后一定要检查你的PostgreSQL配置。你会在postgres wiki中找到一个很好的指南。