CODEWITHSUNDEEP
phpsqlmysqli
Aditya Amit
Aditya Amit

Reputation: 71

How to dynamically chose DB user among multiple users

I have been experiencing "database connection exceeds(max: 30)" problem in my website in shared hosting. In order to avoid this, I have created 10 DB users and want to distribute the load among them. Would you please help me with an efficient way to achieve this? Mine is a web application based on PHP-MySQL platform. My user base is also defined (nearly 550 users).

I am using following simple script to connect DB.

$dbhost = 'localhost';
$dbuser = 'MYDBUSER';
$dbpass = 'MYPASS';
$dbname = 'MYAPPLICATIONDB';
$conn = mysqli_connect($dbhost, $dbuser, $dbpass, $dbname);
if (!$conn) {
    die('Could not connect database.');
}

Upvotes: 0

Views: 162

Answers (2)

SpacePhoenix
SpacePhoenix

Reputation: 605

Instead of creating a new connection for every CRUD operation, create just a single connection with one database user and share that one connection with all of the CRUD operations for the script.

Some hosting plans on some hosts might well limit you to a single database user.

Creating new connections for every CRUD operation is very inefficient. In the past I've seen an example of when someone done that, it meant that page loads took ages. Once it was altered to use a single database connection which was passed around the script, it made a huge difference to page load times, which then loaded a million times faster.

Upvotes: 1

MyLibary
MyLibary

Reputation: 1771

Well, you better change your hosting account then.

A solution (which I don't really like) might be:

$dbhost = 'localhost';
$dbusers = ['MYDBUSER1', 'MYDBUSER2', 'MYDBUSER3', 'MYDBUSER4', 'MYDBUSER5'];
$dbpass = 'MYPASS';
$dbname = 'MYAPPLICATIONDB';
$conn = mysqli_connect($dbhost, array_rand($dbusers), $dbpass, $dbname);
if (!$conn) {
    die('Could not connect database.');
}

Upvotes: 1

Related Questions