0

I'm building my first single-producer/single-consumer app in which the consumer takes items off the queue and stores them in a MySQL database.

Previously, when it was a single thread app, I would open a connection to the DB, send the query, close the connection, and repeat every time new info came in.

With a producer-consumer setup, what is the better way to handle the DB connection? Should I open it once before starting the consumer loop (I can't see a problem with this, but I'm sure that one of you fine folks will point it out if there is one)? Or should I open and close the DB connection on each iteration of the loop (seems like a waste of time and resources)?

This software runs on approximately 30 small linux computers and all of them talk to the same database. I don't see 30 simultaneous connections being an issue, but I'd love to hear your thoughts.

Apologies if this has been covered, I couldn't find it anywhere. If it has, a link would be fantastic. Thanks!

EDIT FOR CLARITY My main focus here is the speed of the consumer thread. The whole reason for switching from single- to multi-threaded was because the single-threaded version was missing incoming information because it was busy trying to connect to the database. Given that the producer thread is expected to start dumping info into the buffer at quite a high rate, and given that the buffer will be limited in size, it is very important that the consumer work through the buffer as quickly as possible while remaining stable.

Kyle G.
  • 870
  • 2
  • 10
  • 22
  • unless you have more than 500 connections/sec don't worry. also you can use caching mechanisms such as memcached to avoid connection on read only queries. – Sam Dec 17 '13 at 19:11
  • Not sure I follow, @SamD. Which route are you suggesting? Also, all of the queries will be INSERTs. – Kyle G. Dec 17 '13 at 19:12
  • open a connection to the DB, send the query, close the connection – Sam Dec 17 '13 at 19:13
  • @SamD Awesome, thanks. Now, suppose that ends up taking too long (the connections are going through a mesh network and we've experienced some definite lagging in the past)... Would there be a problem with leaving a connection open the whole time? – Kyle G. Dec 17 '13 at 19:17
  • it would be better to use memcached to cache the queries to avoid redundant connections. It's not a good idea to leave connections open. get more servers or load balances. – Sam Dec 17 '13 at 19:19
  • I don't see how the producer-consumer details are relevant to the question. This question is purely about writing to mysql. I don't see a reason why 30 concurrent connections should be a problem, nor long-lasting connecting. – shx2 Dec 18 '13 at 18:34
  • @shx2 quite frankly I wasn't entirely sure that it would be relevant. However, given that this is new territory for me, I decided to err on the side of giving too much info rather than too little. – Kyle G. Dec 19 '13 at 18:39
  • I probably should have been more clear about the reason for my question: speed. The size of my buffer is quite limited, and the producer is expected to be generating new items for the queue quite quickly, so I'd like to go with the option that will empty the buffer as quickly as possible while remaining stable. – Kyle G. Dec 19 '13 at 18:41
  • fair enough. considering editing your question, adding these details. – shx2 Dec 19 '13 at 18:56

1 Answers1

0

Your MySQL shouldn't have any problems handling connections in the hundreds, if not thousands.

On each of your consumers you should set up a connection pool use that from your consumer. If you consume the messages in a single thread (per application) the pool only needs to use one connection but it's also fine to consume and start parallel threads that all use one connection.

The reason for using a connection pool is that it will handle re connection and keep alive for you. Just ask it for one connection and have it promise that it will work (it does this by running a small query against the database). If you don't use a connection for a while and it get's terminated the pool will just create a new one.

Andreas Wederbrand
  • 38,065
  • 11
  • 68
  • 78
  • Thanks Andreas, I'll look into using pools (never used them before). Is there a good place to start learning about them? – Kyle G. Dec 19 '13 at 22:57
  • You should read about it at the mysql documentation site http://dev.mysql.com/doc/refman/5.6/en/connector-j-usagenotes-spring-config-connpooling.html – Andreas Wederbrand Dec 20 '13 at 05:02