To close or not to close connection?

[Anon]

I have a Flash game that creates tons of queries against a MySQL database. Naturally the server is getting bogged down and I had to up the MAX_CONNECTIONS to 999 in the 'my.cnf' file.

My question is, should my PHP scripts use mysql_close() after the neccesary query has been made? I use mysql_connect() to open the connection, but rarely ever use the correponding mysql_close(). My understanding was that this is only neccesary when mysql_pconnect() is being used.

Each user is likely to hit the database at least once every minute, while they play the game. If I use SHOW_PROCESSLIST, it returns around 100 connections, half of which are sleeping.

Just looking for the best performance for a heavily used database.

[trooper]

Brian

The thing you really want to consider is the load on the server (which you are doing)

if you do close a db connection then you will free up an avaliable resource.

try running your game with the mysql_close() enabled and see if there is any improvement in performance.

Also what happens when lots of people are running the game at the same time?

Answer is the server gets bogged down

Try it and see if there is any performance improvement

Gary Mailer

[mithril]

Gary's quite right... the only way to answer this kind of question is to run benchmarks on the different possible situations and see what happens.

On a different note, mysql_close() has absolutely no effect on persistent connections (it'd destroy the purpose of a persistent connection if it did🙂. Persistent connections are cleaned up periodically by the database engine itself. I believe MySQL defaults to 1 hour, but you can change that in my.cnf.

-geoff

[Anon]

I guess I will have to add the mysql_close() function to all of my scripts and let you guys know what happens. I have been reluctant to do that because there are 18 scripts, but you seem to have indicated that I may be on the right track. That was the push I needed to give it a go.

Thanks for your input.

[Anon]

So if I understand you correctly, mysql_pconnect() would probably exasperate the situation by leaving even more processes running?

How long do connections stay open if the mysql_connect() function is used? I thought they ended when the script exited, but I see too many sleeping processes to beleive that to be true.

[mithril]

Not necessarily.

The way mysql_pconnect()) works is that it will try to use an open persistent connection resource which matches the username and password in the mysql_pconnect() function. If no resource is immediately available, it opens a new connection, uses it, stores the username and password for that connection, and keeps the connection alive after the script run is finished.

http://www.php.net/manual/en/features.persistent-connections.php

Connections opened with mysql_connect() do die once the script is finished running. However if youre Flash game is creating and calling those connections directly during the process of it's use, technically the script never finsihes running (at least as far as MySQL is concerned). That's probably why you're seeing so many sleeping processes. If that's the case, using mysql_close() should help you solve that problem. You can also minimize impact by reading the resource id returned my mysql_connect() into a var, then use that resource as the optional resource is argument in mysql_select_db() and mysql_query() thus explictly hardcoding a particular query set to a specific connection id. HTH.

-geoff

[Anon]

So what your telling me is that persistant connections while be recycled rather than creating additional persistant connections?

Lets assume that player "A" logs into the game. If the script uses mysql_pconnect() to access that player's record, the connection remains open once the script has exited. If player "B" logs in, and the same PHP script is executed, is it possible that the same process ID may be utilized to connect to MySQL? The script will always use the same id and password for access.

If this is the case, would that be more efficient than using mysql_connect() with the subsequent mysql_close()? It seems to appear that way, since sleeping processes will be activated for the connection, rather than forking new processes (more CPU load).

I realize that the best way to determine this is by benchmarking the various conditions, which I am preparing to do.

[mithril]

You're not calling mysql_connect() and mysql_close() for every query you're running are you?!? If you are it's no wonder you've got so many "sleeping" processes lying around. Just call mysql_connect() prior to the first query, then mysql_close() at the end of the script. Your script will reuse the connection for each query on the db.

Both mysql_connect() and mysql_pconnect() open connection resources which can be "recycled". The difference of course is that persistent connections aren't closed at the end of the script's execution. What happens is that the connection resources are "linked" to the username/password which you supplied. Any call to mysql_query() will try to use any open connection resource which is currently free. You usually aren't going to find any "connection swapping" going on since each script load creates a connection and the scripts are executed so quickly. If you're concerned about this, you can pass the resource id# returned by mysql_connect() to mysql_query(), effectively tying each instance of the script to the connection resource it opened. Calls to mysql_pconnect() will try to first "take over" an idling persistent connection and return it's resource id#. If no connections are free, it then creates a new connection and returns the new id.

The easiest way to think of it is to imagine you have a car which once it's started [mysql_connect()] will only allow the person [username/password] who turned the key to press the gas pedal. With a normal connection you don't need to restart the car everytime you reach a stop sign [end of a query], just hit the gas [new query]. When you reach your destination, you shut the car off [mysql_close()]. Now you tell need to restart it when you want to go home.

With a persistant connection, the car isn't turned off when you get where you're going... it keeps running for $x minutes. However, only the person which started it can get back in a drive off and if the car is currently in use mysql_pconnect() will create that person a new car (wouldn't that be nice🙂 and give them the key.

Hope this answers your question. If not, take a read through the Persistent Connections section in the PHP Manual. If explains it all pretty clearly.... and without any dodgy car analogies🙂

-geoff

[Anon]

None of my scripts currently use mysql_close() at all. I am considering placing that function at the very end of each script, in order to free up the connection resource, and minimize server load. I am assuming that this would be useful since I am not using persistant connections.

I read the Persistant connection documentation and found some cautionary language regarding the number of file handles required to handle this.

I am still unsure as to the potential benefits of persistant vs. non-persistant connections in my case. I beleive that my database is very heavily loaded:

Aborted_clients 49
Aborted_connects 0
Bytes_received 10689479
Bytes_sent 2568571563
Connections 7261
Created_tmp_disk_tables 1
Created_tmp_tables 5
Created_tmp_files 0
Delayed_insert_threads 0
Delayed_writes 0
Delayed_errors 0
Flush_commands 1
Handler_delete 906
Handler_read_first 21981
Handler_read_key 158330
Handler_read_next 232712
Handler_read_prev 672521
Handler_read_rnd 237235
Handler_read_rnd_next 233787043
Handler_update 11950
Handler_write 11875
Key_blocks_used 67025
Key_read_requests 499826
Key_reads 66969
Key_write_requests 5743
Key_writes 4295
Max_used_connections 208
Not_flushed_key_blocks 0
Not_flushed_delayed_rows 0
Open_tables 171
Open_files 231
Open_streams 0
Opened_tables 15055
Questions 172507
Select_full_join 4
Select_full_range_join 0
Select_range 2
Select_range_check 0
Select_scan 58387
Slave_running OFF
Slave_open_temp_tables 0
Slow_launch_threads 63
Slow_queries 39
Sort_merge_passes 0
Sort_range 1
Sort_rows 248039
Sort_scan 10024
Table_locks_immediate 154167
Table_locks_waited 1492
Threads_cached 3
Threads_created 852
Threads_connected 76
Threads_running 31
Uptime 4790

I will be testing the various solutions presented in this thread, and hopefully post my results in the next day or two.

[Anon]

Allied to this:-

I am accessing two databases, ncea and site1, and have to swap from one to the other depending on the actual query I am doing at a particular moment.

I assume I would be better to use mysql_db_query, and use the resource #id. I do not do this at the moment, as when I wrote the code I had not noticed this option, but am thinking of changing my code to use mysql_db_query.

Is this beter practice in your opinion?

Paul

[mithril]

In my opinion, no it's not.

The reason is mysql_db_query() has been deprecated and we can expect it to be removed from the source tree at some undetermined point in the future. Once it's removed, and you upgrade to the PHP version (or higher) from which it has been removed, you'll just have to change all your code back.

There are 3 solutions which immediately come to mind for connecting to multiple db's in a script run:

1. Open a connection for each database.

  $ncea = mysql_connect("host", "user", "pass");
  mysql_select_db("ncea", $ncea);

  $site1 = mysql_connect("host", "user", "pass");
  mysql_select_db("ncea", $site1);

    // close both connections at end of script
  mysql_close($ncea);
  mysql_close($site1);

Advantages: Now you just pass the appropriate resource id ($ncea or $site1) into your mysql_query() calls. This will ensure that your query always uses the database it needs.

Drawbacks: You need to open 2 connections instead of just one. This may have a performance hit on your server. Just how much of a hit is impossible to say as it's dependant on a lot of different factors, but if you're dealing with a moderate to relatively large number of page requests I'd stay away from this one.

2. Continous calling of mysql_select_db()

This is probably what you're doing right now. Simply call this function to change the active database as dictated by the placement of your queries.

Advantages: Lower server overhead than #1 as you only need to have one open MySQL connection per script execution.

Drawbacks: Requires more code, the code is less elegant and readable, and it's more expensive on the programmer.

3. Modify your SQL queries and drop mysql_select_db() all together.

If you specify the database name in the SQL query itself, you don't need to select the database.

ie.

  SELECT FROM ncea.foo WHERE bar = 'free beer'
  SELECT FROM site1.foo WHERE bar = 'I think the one on the left likes me🙂'

Advantages: Solves your problem of database connection management. It's standard ANSI 92 SQL so it's not likely to change anytime in the near future.

Disadvantages: As with #2, it requires a bit more programmer overhead.

Summary:

Personally, I'd go with #3. It's more elegant and less prone to problems with support or increased server overhead. You're already talking about modifying all your scripts so, unless you've got several hundred queries to edit, it's prolly not going to take you much longer.

HTH.

-geoff