Which is the best ?

Anon

I'm a newbie to database technology and would like to consult some questions about optimising database.

I have 2 senario:

Senario 1:
I have a database server where every database will have its username and password to access. However, each time when I need to access different database, I need to set up another connection with its own username and password. I wonder will it overload the server when too much connection be made at a time ?

Senario 2:
One username and password for all databases in the database server. This is much faster I suppose but in security sense, it 'll be quite risky that somebody might mistreating other database's information.

Which aspect should I look into when dealing with optimising database performance ?

Thank you for helping =)

Anon

My recommendation is to use PHP in safe mode, and create an account for each person on the machine.

If you use postgresql, the username of the owner of the directory will have whatever access permissions you have set up for that user, but no passwords will be passed around or stored in your PHP files, so you don't have to worry about someone rooting through PHP files for passwords

Anon

PHP has a wonderfull thing called "persistant db connections"
This means that the first time a script is called, it opens a database connection to the database server, using a username and password.

The next time "any" script needs a connection to that same database with the same username and password, PHP remembers that it already had that connection open, and it will use the same connection again.
Thus, logon/logoff delays are non-existant.

How many connections your database server can handle depends on what you tell it to handle.. this is usually not a problem.

From a securety point of view;
how many people are going to see the php sourcecode?
This is not meant ironically, it is just a question.

Anon

Please tell me if I'm wrong.

Assume I have a script where it'll make connections to a database, doing some process, then close the connection.

From what you said, does that means if there's multiple users accessing the same script at the same time, PHP will use the same connection instead of opening multiple ones (based on request)?

What if the process ends ( the script close the connection) for a single user? How do PHP deal with other user's request when one end closes the connection ? Will a new connection be made?

I'm sorry that I'm quite confused. I hope you can understand my doubt here. If not, please tell, =) and just want to tell you, YOU are very helpful.

Anon

First, this is all true only if you use "pconnect". Check the PHP manual for more information.

Assume I have a script where it'll make connections to a database, doing some >process, then close the connection.

From what you said, does that means if there's multiple users accessing the >same script at the same time, PHP will use the same connection instead of >opening multiple ones (based on request)?

Correct
PHP keeps it's connection to the database open, no matter what the scripts do.

What if the process ends ( the script close the connection) for a single user?
The connection stays open.

How do PHP deal with other user's request when one end closes the connection ? Will a new
connection be made?

The scripts are nolonger in control of the connections opening and closing.
If a script needs a connection that already exists,
meaning that it is to the same server on the same database using the same username and password, the existing one is used.
If the script needs a connection that does not exist,
(to a different server/database/username/password) a new connection is made.
If a script closes a connection, PHP keeps is open anyway.

Read the PHP manual secion about mysql functions, it will make things more clear and explain just how the connections are handled.

Anon

Also, you should keep in mind that using persistant connections requires you to configure your database for more backend servers than not using persistant connections. Luckily, the backends for weigh in at 2.5 meg or so with 2 meg shared (buffers and such) so that each new back end weighs only about a half a meg extra.

The bad news is that (at least in 6.5.3, I don't know about 7.0.2) the built in default for postgresql is only 32. Even a slow site could open and maintain 32 persistant connects regularly.

So, you need to recompile postgres with the --with-maxbackends=256 or something like that, then start it with -N 256 to make it capable of handling that many backends. Keep in mind that 256 backends is only about 128 megs of ram. At this point, the most likely problem is going to be running out of file handles, so you may have to do some tweaking of the /proc/sys/fs/file-max, and inode max

Anon

And remember that not everybody uses postgress....

Anon

Hey, I'm just helping those who do. Don't you think the users of other databases would be better served if you'd taken the time to explicitly show them how to configure your database for more backends than to post such a short, utterly useless (in terms of helping people out) message?

Anon

I thank you for all the helps, both to Vincent and Scott.

May I ask what's the draw back of having a persistant db connection ?

If there's always request to the same connection, will it be better to have a persistant connection than to have it connects upon request ?

And do forgive me, Scott, that I wasn't get you 'bout the configuring of the backend servers weigh ? May I know more about that if it doesn't trouble you much? =)

Anyway, I've came to a statement like below,
"If you need to close idle persistent connections, set a low wait_timeout in MySQL", can anyone tell me where can I find such information on how to set a low wait_timeout in MySQL ?

FYI, I'm using MySQL 3.22.27