I want to have ONE single mysql-connection used by EVERY user that selects the data all the time and updates it if specific conditions are met (like a placed bid). Most preferably even then if no user is visiting the website, if that's even possible?
我希望每个用户都使用一个单独的mysql连接,它始终选择数据并在满足特定条件时更新它(如已下标)。最优选的是,如果没有用户访问该网站,即使这样甚至可能?
So, in the last days I'm google'ing all the time, trying so hard to figure out to solve my issue, but it seems there are no people with enough knowledge to help me with my problem. So I try to ask my question as simple as possible without confusing you with my code. (But if you're interested seeing the code: http://pastebin.com/dRFzWtEH)
所以,在最后几天我一直在谷歌,努力想要解决我的问题,但似乎没有人有足够的知识来帮助我解决我的问题。所以我尽量简单地问我的问题,而不会让你的代码混淆。 (但如果您对查看代码感兴趣:http://pastebin.com/dRFzWtEH)
However, this is all about an auction website with live-countdown-timer and I just want to run a node.js server that SELECTs data every second and sends it to a WebSocket to show all users visiting that website the countdown and price-updates (on bids) in realtime.
然而,这只是一个带有live-countdown-timer的拍卖网站,我只是想运行一个node.js服务器,每秒选择一次数据并将其发送到WebSocket,以显示访问该网站的所有用户倒计时和价格更新(在出价上)实时。
I accomplished this whole task by using single-mysql-queries but then I ran into errors. Then the author of the GitHub node-mysql-module suggested me to use a MySQL Pool. But there is like no content at all to find about my specific aim stated in my first sentence of this question.
我通过使用单mysql查询完成了这整个任务,但后来遇到了错误。然后GitHub node-mysql-module的作者建议我使用MySQL Pool。但是,根据我在这个问题的第一句中所说明的具体目标,没有任何内容可以找到。
Now I want to ask in general, how could I accomplish this and is this even possible or does at least one user has to be on my website?
现在我想问一下,我怎么能做到这一点,这是否可能,或者至少有一个用户必须在我的网站上?
What would the code/code-structure/logical process look like?
代码/代码结构/逻辑过程是什么样的?
And I guess I don't need to close the connection at all, so I won't need functions like connection.end()?
我想我根本不需要关闭连接,所以我不需要像connection.end()这样的函数?
1 个解决方案
#1
No, don't worry about connection pooling. It is not a big deal in MySQL.
不,不要担心连接池。这在MySQL中并不是什么大问题。
Furthermore a "pool" has a problem -- it must clear out all settings, @variables, transaction state, etc, etc, before allowing the next 'client' to use the pooled connection. This can take time, especially if the client is far from the server.
此外,“池”存在问题 - 在允许下一个“客户端”使用池化连接之前,它必须清除所有设置,@变量,事务状态等。这可能需要一些时间,特别是如果客户端远离服务器。
MySQL's connection/disconnection time is very low, unlike competing products.
与竞争产品不同,MySQL的连接/断开时间非常短。
If you are developing a Web product, then keep in mind that HTTP is "stateless". That is, you cannot hang onto a connection from one 'page' to the next 'page. Hence, no 'state' can be saved.
如果您正在开发Web产品,请记住HTTP是“无状态”的。也就是说,您无法挂起从一个“页面”到下一个页面的连接。因此,不能保存“国家”。
Edit
If you have "Across the pond" latency problems (100-200ms between US and Europe), client-side connection pool could be very useful. However, if the pool software is injecting commands to reset things, that could totally defeat the pooling.
如果你有“跨越池塘”的延迟问题(美国和欧洲之间100-200ms),客户端连接池可能非常有用。但是,如果池软件正在注入重置事件的命令,那么可能会完全破坏池。
If you can turn on the 'general log' (in a hosted service, you may have to use log_output=TABLE), do so to see what extra commands are injected.
如果您可以打开“常规日志”(在托管服务中,您可能必须使用log_output = TABLE),请执行此操作以查看注入了哪些额外命令。
Also, consider combining multiple client SQL statements into Stored Procedures to cut down on back-and-forth.
另外,考虑将多个客户端SQL语句组合到存储过程中以减少来回。
Also consider either moving the MySQL server closer to the client, or moving the client closer to the MySQL server, depending on how the end-user to client back-and-forth compares to the client to MySQL traffic.
还要考虑将MySQL服务器移近客户端,或者将客户端移近MySQL服务器,具体取决于最终用户与客户端之间来回与MySQL流量的比较。
#1
No, don't worry about connection pooling. It is not a big deal in MySQL.
不,不要担心连接池。这在MySQL中并不是什么大问题。
Furthermore a "pool" has a problem -- it must clear out all settings, @variables, transaction state, etc, etc, before allowing the next 'client' to use the pooled connection. This can take time, especially if the client is far from the server.
此外,“池”存在问题 - 在允许下一个“客户端”使用池化连接之前,它必须清除所有设置,@变量,事务状态等。这可能需要一些时间,特别是如果客户端远离服务器。
MySQL's connection/disconnection time is very low, unlike competing products.
与竞争产品不同,MySQL的连接/断开时间非常短。
If you are developing a Web product, then keep in mind that HTTP is "stateless". That is, you cannot hang onto a connection from one 'page' to the next 'page. Hence, no 'state' can be saved.
如果您正在开发Web产品,请记住HTTP是“无状态”的。也就是说,您无法挂起从一个“页面”到下一个页面的连接。因此,不能保存“国家”。
Edit
If you have "Across the pond" latency problems (100-200ms between US and Europe), client-side connection pool could be very useful. However, if the pool software is injecting commands to reset things, that could totally defeat the pooling.
如果你有“跨越池塘”的延迟问题(美国和欧洲之间100-200ms),客户端连接池可能非常有用。但是,如果池软件正在注入重置事件的命令,那么可能会完全破坏池。
If you can turn on the 'general log' (in a hosted service, you may have to use log_output=TABLE), do so to see what extra commands are injected.
如果您可以打开“常规日志”(在托管服务中,您可能必须使用log_output = TABLE),请执行此操作以查看注入了哪些额外命令。
Also, consider combining multiple client SQL statements into Stored Procedures to cut down on back-and-forth.
另外,考虑将多个客户端SQL语句组合到存储过程中以减少来回。
Also consider either moving the MySQL server closer to the client, or moving the client closer to the MySQL server, depending on how the end-user to client back-and-forth compares to the client to MySQL traffic.
还要考虑将MySQL服务器移近客户端,或者将客户端移近MySQL服务器,具体取决于最终用户与客户端之间来回与MySQL流量的比较。