connecting to mySQL

Question

One of the ERP applications I worked with was configured in such a way that there was only 1 user (for example USER A) who connected to the database. Any user of the application (workforce was in the thousands) who logged on to the system and tried to do anything was in effect calling USER A to connect to the database and execute queries for him. The database was Oracle.

I was wondering how to achieve a similar thing with mySQL. I have a web application built with php and mySQL database. I expect different people to query the database via the web. Currently when a user opens up the web page, a connection to the database is made via a single db user. At the end of the query, I close the connection. However the database has a maximum user connection of 10 which in my understanding means one user can only establish a max of 10 connections. I do not want to have to create several users for all the people who try to use my application (I do not even know the number of people who will use the application and I do not believe this will be a scalable solution)

Answer 1

You should look for a db connection caching mechanism as a component for either your web server or your programming language. Such a mechanism will reuse connections transparently for you.

Answer 2

If the database connection is refused return HTTP error 502. If connections are closed at the end of each pageload they should only last ~100ms, so concurrent connections will be low for most situations.

Should you need to adjust it, edit my.cnf to increase concurrent connections:

max_connections = 150
max_user_connections = 150

If traffic is very high you can enable persistent MySQL connections in PHP, or cache your content so not to hammer the database.

Hope that helps!