Thread: SQL Connections

Not having much knowledge of this, I may be quite wrong.

But it would be less stressing to the SQL server if connections were only made when required.. would it not?

Originally Posted by Austin P

Not having much knowledge of this, I may be quite wrong.

But it would be less stressing to the SQL server if connections were only made when required.. would it not?

well in my eyes i think the everlasting connection seems like a better idea, let's say you have donations/voting/highscores all sql checking constantly. random number maybe like 500 connections an hour....
i feel as if making 500 connections an hour would be a great idea but keeping the connection open is probably worse
hence why i'm curious of peoples views on this matter

just connect, do what you need and then disconnect. no point in leaving a connection open. also, do things like highscores upon log out.

Originally Posted by SGPK

In your opinion, which is better

Keeping SQL connections alive 24/7, checking every 10 seconds to ensure connection is alive. Reconnect if not.

or....

Create a new connection every time, execute the sql, close the connection.

Yeh you should keep one connection open and not use this destroy connection crap. You don't need to check it you can just make a getter like getConnection() and in that method you can add the check to see if its closed. So it'll check every-time you get the connection.

Imo if you have a lot of players you shouldn't be creating a new connection everytime like the post above me says you should be re-using the current connection.

They give benefits and examples of re-using the connection.
[Only registered and activated users can see links. ]

Originally Posted by Shiver

just connect, do what you need and then disconnect. no point in leaving a connection open. also, do things like highscores upon log out.

Dont comment if your comment is based on complete shit you saw in a PI source.

Originally Posted by SGPK

well in my eyes i think the everlasting connection seems like a better idea, let's say you have donations/voting/highscores all sql checking constantly. random number maybe like 500 connections an hour....
i feel as if making 500 connections an hour would be a great idea but keeping the connection open is probably worse
hence why i'm curious of peoples views on this matter

What about having the game just check every now and then, say like every two minutes. Just check for any updates, changes, or queries, and then if there's a donation reward, voting reward, or highscore change that needs to be done, it's done.

If the player deserving the donation reward is online, it sends it to them, (granted their inventory space allows it, otherwise just a notification message telling them to prepare for the item(s) in two or so minutes)

Much the same for voting rewards. It could be done using the same stradegy as global server messages that are often seen, or even done per account preferance, based on whatever time set the user so chooses, some users like their highscores to be updated immediate, whereas others may not care.

Originally Posted by Graham

Keep the connection open. If your server ever scales beyond a handful of players, closing it and opening it for each query or transaction would lead to really bad performance. On Emps we typically have between 100-500 queries per second depending on the time of day, and we've needed to tune things carefully to be able to handle this:

- keeping a pool of connections open
- using the InnoDB table engine (which uses row locking so allows more concurrency than MyISAM)
- make InnoDB only commit once per second instead of once per transaction
- tweaking the file system settings to commit the journal every 60 seconds (the above two would be bad if you were a bank or something important, but for a private server losing up to 60 seconds of data in a power loss/crash isn't important)
- configured MySQL to have a larger InnoDB buffer pool, log file and log buffer - as well as various other settings I can't remember now

Btw I think an idle connection to MySQL won't harm anything. Sure, it'll consume a bit of memory in MySQL and your server (this is probably better than allocating and deallocating that all the time though, this would happen if you kept opening/closing it) - but it'll probably have a thread in MySQL that is sitting there blocking, so won't be consuming any CPU power. So I don't see any reason for not keeping the connections open.

oh how i'd really love to see your sql classes :L

but on topic, as for keeping the connection, a connection usually closes after 15 seconds doesn't it? you'd have to keep opening it back up every 15 seconds. and not entirely sure about pooling but i'm assuming several connections to the same database?
and one last thing i'm concerned about, you said sending all the queries once per second or once per 60 seconds?

Originally Posted by Graham

MySQL can be configured to have longer timeouts.

The queries are executed as required, it's the file system that commits every 60 seconds.

so a majority of performance configurations are done through website settings