MySQL connections

I have a servlet which establises a single JDBCConnectionPool used for all SQL access. However, when looking at MySQL, each session creates two database connections. Is this correct?

Did you use the com.vaadin.data.util.sqlcontainer.connection.SimpleJDBCConnectionPool.SimpleJDBCConnectionPool(String, String, String, String, int, int) constructor? There’s a parameter for the amount of initial connections, have you set it to 1?

ok - yes , I realise I had set initial at 2 and maximum at 10 - thank you.

Is the maxium limit set for one session or all current sessions? I was planning for a single connection for each session - so could I set both inital and maximum to 1.

Depends:
If you have one ConnectionPool per Session then every session will have it’s own limitation. So if you set max to one in that scenario there can be more then one connection because they are from more then one pool.
If you on the other hand have a single Connection Pool for all session, for example having one as a static Singleton, and all sessions access only this pool your maximum limitation will affect all sessions.

if we defined a static Singleton - with a single pool - is there likely to be any performance hit? The application will have approximately 20 concurrent users max in read/write mode, but will be on a fairly small server.

I would be interested under what circumstances you would recommend a static pool rather than a pool for each session?

I wouldn’t think that using a static singleton would cause much of a performance problem. We always use a single static Pool per application in all our application. Mainly because this way you have better control over how many connections were opened to the Database which was a problem in our application once because of a problem in our code.
You can just test it and if you end up having performance problems with a static pool you can still switch back.

Marius - thanks, that is really useful information. I will try a static pool and see what happens.