MySQL Connection Time

ID:151778
 
Apr 21 2009, 1:22 pm
When I connect to my MySQL database (not on localhost... not exactly anyway) the entire game locks and waits for the process to complete. Is there any way to make the game NOT wait for the result or is this something I must suffer through?
Apr 21 2009, 1:56 pm
AJX wrote:
When I connect to my MySQL database (not on localhost... not exactly anyway) the entire game locks and waits for the process to complete. Is there any way to make the game NOT wait for the result or is this something I must suffer through?

Run the query on a spawn()?
Apr 21 2009, 2:43 pm
In response to CriticalBotch
CriticalBotch wrote:
Run the query on a spawn()?

Not a query, initial connection.
Ran in a verb... tried adding spawn() on the new (its a datum) for giggles, didn't change anything.
Apr 21 2009, 2:46 pm
In response to AJX
AJX wrote:
CriticalBotch wrote:
Run the query on a spawn()?

Not a query, initial connection.
Ran in a verb... tried adding spawn() on the new (its a datum) for giggles, didn't change anything.

Hmm. As far as I know, then, no. Best option is to generate the connection when the world loads, so you at least front-load the delay.

I could be wrong of course; love to have someone correct me :)
Apr 21 2009, 3:25 pm (Edited on Apr 21 2009, 3:39 pm)
Yeah, the world will also lock up if you query large amounts of data, so be careful(spawn() doesn't help). I make one global connection in my game and just do a check to make sure the connection has not dropped.

var
    DBConnection/dbcon=new()
world
    New()
        ..()
        dbcon.Connect("dbi:mysql:database:localhost:3306","user","pass")
proc
    MySql_Con()
        if(!dbcon.IsConnected())
            dbcon.Connect("dbi:mysql:database:localhost:3306","user","pass")
        //Do Whatever


You could also set the connect_timeout in MySql really high so it never drops the connection.
Apr 21 2009, 3:39 pm
In response to Soldierman
I'm curious if you're sure about this. I've inserted over 2000 rows all at once in less than a quarter of a second.
Apr 21 2009, 3:54 pm (Edited on Apr 21 2009, 4:02 pm)
In response to Alathon
You need to select alot of data in order to cause the lock up, not insert data. Insert a large amount of data and then write a select statement to get all the data. You will see that the world will lock up until the data is returned. It takes about 25000 rows for my configuration to start locking up.
Apr 21 2009, 4:26 pm
In response to Soldierman
Thats interesting. I'll give this a test run when I have time to insert some more data.
Apr 21 2009, 7:36 pm
This really seems related to the connection speed between you and the MySQL server. Being on dial-up I've seen some pretty bad hang-ups from MySQL and Export() due to how long they take.
Apr 21 2009, 8:10 pm
In response to Nadrew
Nadrew wrote:
This really seems related to the connection speed between you and the MySQL server. Being on dial-up I've seen some pretty bad hang-ups from MySQL and Export() due to how long they take.

My server is on my network... In fact it is on my computer, running through a virtual machine of ubuntu. My ping to my virtual computer is 1-4 MS. (1 being average, 4 being rare) But I get about a 2 second freeze when trying to connect with BYOND. (Whereas my MySQL Administrator/Query Browser will connect instantaneously.)
Apr 21 2009, 8:52 pm
In response to AJX
I am doing the same thing on my computer. My host OS is Vista and Ubuntu is running in VMware with mysql 5.0 installed. I host the game on Vista and Connecting to mysql is instant for me, just like using Query Browser or another mysql manager program.

What is your computer specs?
Apr 21 2009, 9:13 pm
In response to Soldierman
What is your computer specs?
X2 2.01
1.5 G ram

Surely that couldn't be making it so I can connect instantly with query browser and not with byond? :S
Apr 21 2009, 10:27 pm
In response to AJX
Yeah. your right that was a pointless question :) .I am curious as to how bad your connection time is. What if you ran a simple recurring Connect() and Disconnect() loop like in the code below. Is the game playable? can you move around? On my system I don't notice any difference between before or after I run it.


var
    DBConnection/dbcon=new()
mob
    verb
        ConnectDisconnect()
            while(src)
                sleep(1)
                dbcon.Connect("dbi:mysql:database:ipaddress:3306","user","pass")
                dbcon.Disconnect()
Apr 21 2009, 10:37 pm
In response to Soldierman
Soldierman wrote:
Yeah. your right that was a pointless question :) .I am curious as to how bad your connection time is. What if you ran a simple recurring Connect() and Disconnect() loop like in the code below. Is the game playable? can you move around? On my system I don't notice any difference between before or after I run it.

Huge... Unplayable. 1 tick of function, 2 seconds of fail.
Apr 21 2009, 11:54 pm
In response to AJX
Run the above code he supplied in an entirely new project please, and then run profiler and post the results here.
Apr 22 2009, 1:05 am (Edited on Apr 22 2009, 1:33 am)
In response to Alathon
EDIT: Cleared all that crap out...

Ok, so: Solved the problem.
It was an issue with DNS...
Just added skip_name_resolve to the config file and it would do 1 connection per tick no sweat. :) Thanky much for your guys' help.