mysql连接.在每次交易之前，我应该保持它的生命状态还是开始一个新的连接? [英] mysql connections. Should I keep it alive or start a new connection before each transaction?

问题描述

我正在使用mysql进行首次尝试，我对如何处理应用程序具有的连接有疑问.

I'm doing my first foray with mysql and I have a doubt about how to handle the connection(s) my applications has.

我现在正在做的是打开连接并保持活动状态，直到终止程序.我不时地执行mysql_ping()，并使用MYSQL_OPT_RECONNECT开始连接.

What I am doing now is opening a connection and keeping it alive until I terminate my program. I do a mysql_ping() every now and then and the connection is started with MYSQL_OPT_RECONNECT.

(我可以想到的)另一种选择是在执行任何需要我与数据库的连接的操作之前启动一个新连接，并在完成操作后将其关闭.

The other option (I can think of), would be to start a new connection before doing anything that requires my connection to the database and closing it after I'm done with it.

这两种方法的优缺点是什么? 长时间连接的副作用"是什么? 最常用的处理方法是什么?

What are the pros and cons of these two approaches? what are the "side effects" of a long connection? What is the most used method of handling this?

干杯;)

一些额外的细节

这时，我保持连接处于活动状态，并不时地ping通到现在的状态，并在需要时重新连接.

At this point I am keeping the connection alive and I ping it every now and again to now it's status and reconnect if needed.

尽管如此，当快速查询连续进行并发时，我收到服务器已离开"消息，并在一段时间后重新建立连接.

In spite of this, when there is some consistent concurrency with queries happening in quick succession, I get a "Server has gone away" message and after a while the connection is re-established.

我只是想知道这是否是长时间连接的副作用，还是仅仅是由于mysql服务器配置错误而引起的.

I'm left wondering if this is a side effect of a prolonged connection or if this is just a case of bad mysql server configuration.

有什么想法吗?

推荐答案

通常，打开连接时会产生相当多的开销.根据您希望这种情况发生的频率而定，这可能没问题，但是如果您编写的任何类型的应用程序每个程序运行所执行的不仅仅是几个命令，那么我建议您使用连接池(对于服务器类型的应用程序)或来自独立应用程序的至少一个或很少的连接保持打开状态一段时间，并可以重复用于多个事务.

In general there is quite some amount of overhead incurred when opening a connection. Depending on how often you expect this to happen it might be ok, but if you are writing any kind of application that executes more than just a very few commands per program run, I would recommend a connection pool (for server type apps) or at least a single or very few connections from your standalone app to be kept open for some time and reused for multiple transactions.

这样，即使在涉及数据库服务器之前，您也可以更好地控制在应用程序级别打开多少个连接.这是应用程序服务器为您提供的一项服务，但是如果您希望使其更小，也可以很容易地将其汇总.

That way you have better control over how many connections get opened at the application level, even before the database server gets involved. This is a service an application server offers you, but it can also be rolled up rather easily if you want to keep it smaller.

除了性能原因外，为满足需求高峰做好准备也是一个好主意.当有大量请求进入并且每个请求都尝试打开与数据库的单独连接时-或您建议的更多(每个事务)-您很快就会耗尽资源.请记住，每个连接都会占用MySQL内部的内存！

Apart from performance reasons a pool is also a good idea to be prepared for peaks in demand. When a lot of requests come in and each of them tries to open a separate connection to the database - or as you suggested even more (per transaction) - you are quickly going to run out of resources. Keep in mind that every connection consumes memory inside MySQL!

此外，您还想确保使用非root用户进行连接，因为如果您不这样做(我认为它与MySQL SUPER特权相关联)，您可能会发现自己已被锁定. MySQL为管理员保留了至少一个用于解决问题的连接，但是如果您的应用使用该特权进行连接，则当您尝试手动扑灭火灾时，所有连接都将用光.

Also you want to make sure to use a non-root user to connect, because if you don't (I think it is tied to the MySQL SUPER privilege), you might find yourself locked out. MySQL reserves at least one connection for an administrator for problem fixing, but if your app connects with that privilege, all connections would already be used up when you try to put out the fire manually.

这篇关于mysql连接.在每次交易之前，我应该保持它的生命状态还是开始一个新的连接?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！