0

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?

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)

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.

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.

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()?

  • Just a note about your question structure... The first paragraph of your question should describe your issue so that we can see what your question is from the front page without clicking to view further. – Kevin B May 05 '15 at 14:51
  • The documentation you've been googling for days to find can be found right here, where you downloaded the mysql package: https://www.npmjs.com/package/mysql#pooling-connections you just create the pool, then use it. it's that simple. – Kevin B May 05 '15 at 14:54
  • I know, that's what I found, but it didn't helped my with my specific problem/aim to accomplish like I said. –  May 05 '15 at 14:55
  • I see your opened issue now, and am curious... Why are you so deadset on having only a single connection? I don't understand why you wouldn't be ok with having, say, 10. Having just 1 connection will severely limit the speed of your application. – Kevin B May 05 '15 at 14:58
  • Well, I managed to run this with a mysql-pool connection than with these mysql-single-queries but this is just not enough documentation if I'm doing it right or not. Because the console.log outputs another connection.threadId every time I refresh the page for example. And I thought I'm using just one non-closing opened mysql-connection. To be more specific: http://stackoverflow.com/questions/30052273/cant-send-fetched-data-to-my-socket-io-stream –  May 05 '15 at 15:00
  • Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/77011/discussion-between-kevin-b-and-vay). – Kevin B May 05 '15 at 15:03
  • English isn't my mother language. You mean by "deadset" that I'm against a single connection? This is what I just *assumed* to be the right way for my kind of website. But as mentioned in my link there are also errors I ran into, because somehow every page-refresh of my website increased the mysql-update by one... but this shouldn't be the case at all and that's why I even switched to nodejs. It's kinda hard to explain and much too read. Sorry for that. –  May 05 '15 at 15:05

1 Answers1

0

No, don't worry about connection pooling. It is not a big deal in 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.

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.

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.

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.

Also, consider combining multiple client SQL statements into Stored Procedures to cut down on back-and-forth.

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.

Rick James
  • 135,179
  • 13
  • 127
  • 222
  • Thanks for your answer. Can you give me an example for your last sentence? So I could understand that a little bit better? Also this is my current code (finally working): http://stackoverflow.com/questions/30064272/remove-a-square-bracket-and-receiving-json-properly-in-nodejs-websocket-envirome?noredirect=1 What connectionLimit would you suggest to use for the pool and why? And does this code clears out all settings like you said already or do I have to add some code somewhere? Appreciate your time! –  May 06 '15 at 01:01
  • Also you said "especially if the client is far from the server". So at the moment I'm using OpenShift hosted somewhere in USA, but I live here in Germany, Berlin. Do you think as soon as my website gets approx. 100 online users or something (don't know what value would be an issue), I should think about renting a own nodejs Server hosted in my country? –  May 06 '15 at 01:04
  • Re: last sentence: Is this a web application? Using AJAX or not? Does the user click some button and you bring up a new page? – Rick James May 06 '15 at 01:17
  • Yes, it's a web application. It's kind of a long story why I do this, but I'm using PHP/MySQL both hosted here and a nodejs-server (OpenShift) hosted in USA. I'm also using AJAX, but this won't be used much in general, I think. I tried to make this website as user-friendly as possible, so there are some links to a new page without AJAX, but in general there aren't many pages at all (if that matters). Wait a second, I type another comment to your edited answer. –  May 06 '15 at 01:27
  • As you said in your edited last sentence that the client has to be near the MySQL server, I think it's good the way it is now, because as I said the PHP/MySQL hoster is here in Germany. So nodejs is just being used as a WebSocket to realize this web application as a realtime-web application. What do you mean by `log_output=TABLE`? Do you refer to nodejs or MySQL? I never used this function, so I can't really tell. Well, I'm kinda new to pooling a MySQL connection and don't even know how this works in PHP. So first I would've to figure out how to combine them as you said. But I tried to use –  May 06 '15 at 01:32
  • (continued comment) But I tried to use as much JOINs as possible if needed. Also trying to save most fetched data from the database client-side in a PHP-Session to reduce mysql-queries. So the only mysql-pooling that happens right now happens in nodejs to realize the WebSocket functionality. –  May 06 '15 at 01:34
  • Sorry, I am ignorant of what nodejs and WebSocket do or don't do. – Rick James May 06 '15 at 04:13