Can I Store This Much Info With Sql Without Lagging A Server?

[BlackEyes]

I want to create a bot that follows the same path the person with the top time did in a bunnyhop map. A reasonable rate to store the information I think is about 10 times a second. The average map is maybe 1 minute in length. So I would need to store about 600 records in a database for every map. Each record must contain the top player's position, velocity, and view angles. The server I play on has about 300 maps to play, so a lot of information has to be stored. What would be an efficient way to do this?

Edited May 9, 2013 by BlackEyes

[Guest The_Monkey]

Use a MySQL database with MyISAM tables and you should be fine.

 

You can use INNODB, but they are a hair slower...however, you sacrifice table locks.

[Papa John]

Understand that while one may be more efficient than another it will only be in milliseconds. Even 10 times a second isn't really all that much data and, assuming you have a decent host, shouldn't be a problem for them no matter what database engine you use.

Edited May 9, 2013 by Papa John

[BlackEyes]

Okay I've started making this plugin of mine that does all this stuff. It saves position (x, y, z) and angles (yaw & pitch). Problem is it is only saving ~10 records per second. I am recording at about 100 records per second. I am not sure if it is something I can fix. Here is how it inserts:

 

DB_SaveGhost(client)
{
   if(g_MapID != 0)
   {
       decl String:query[128];
       Format(query, sizeof(query), "DELETE FROM ghost WHERE MapID=%d", g_MapID);
       g_hGhost = CloneHandle(g_frame[client]);
       SQL_TQuery(g_DB, DB_SaveGhost_Callback1, query, client);
       DB_LoadGhost();
   }
   else
   {
       LogError("Failed to start saving the ghost. g_MapID == 0.");
   }
}

public DB_SaveGhost_Callback1(Handle:owner, Handle:hndl, String:error[], any:data)
{
   if(hndl != INVALID_HANDLE)
   {
       LogMessage("Ghost deleted on %s", g_mapname);

       new size = GetArraySize(g_hGhost);
       decl String:query[512];

       for(new i=0; i<size; i++)
       {
           Format(query, sizeof(query), "INSERT INTO ghost VALUES (%d, %d, %d, %f, %f, %f, %f, %f)",
               g_MapID,
               GetClientID(data),
               i,
               GetArrayCell(g_hGhost, i, 0),
               GetArrayCell(g_hGhost, i, 1),
               GetArrayCell(g_hGhost, i, 2),
               GetArrayCell(g_hGhost, i, 3),
               GetArrayCell(g_hGhost, i, 4));
           SQL_TQuery(g_DB, DB_SaveGhost_Callback2, query);
       }

   }
   else
   {
       LogError("%s", error);
   }
}

public DB_SaveGhost_Callback2(Handle:owner, Handle:hndl, String:error[], any:data)
{
   if(hndl == INVALID_HANDLE)
       LogError("%s", error);
}

Edited June 8, 2013 by BlackEyes

[Papa John]

Okay I've started making this plugin of mine that does all this stuff. It saves position (x, y, z) and angles (yaw & pitch). Problem is it is only saving ~10 records per second. I am recording at about 100 records per second. I am not sure if it is something I can fix. Here is how it inserts:

 

While I can't think of why this would be the case, you might just not be able to process it fast enough. In which case you may have to cache the information locally and insert it into the database at a later time.

 

Also if you haven't already look at the documentation page here and try using prepared statements.

[BlackEyes]

The prepared statements seem to be crashing the server. Maybe since it's not threaded and I'm trying to store 10s of thousands of records every time I store info. I also tried storing the information locally with SQLite but that was just as slow as the SQL server I was storing it to. Here's a question that might lead to a solution. What happens if I start storing bunch of these records, half way through the storing I decide to delete all the records, including the ones that haven't been added yet, in order to store a new set of recorded information? Basically would it work if I just saved the ghost, and re-saved a new ghost again before it was finished storing all those records?

Edited June 8, 2013 by BlackEyes

[Papa John]

The problem is that since the queries are run asynchronously they will all ultimately return a result but not necessarily in the the order that you sent them. So, for example, if you send a query to insert data and then instantly send another query to drop the table, the drop table query might return/run faster than the insert does and cause an error. It might help to understand what you want to achieve with all this data and give the code some context.

[Dreae]

The prepared statements seem to be crashing the server. Maybe since it's not threaded and I'm trying to store 10s of thousands of records every time I store info. I also tried storing the information locally with SQLite but that was just as slow as the SQL server I was storing it to. Here's a question that might lead to a solution. What happens if I start storing bunch of these records, half way through the storing I decide to delete all the records, including the ones that haven't been added yet, in order to store a new set of recorded information? Basically would it work if I just saved the ghost, and re-saved a new ghost again before it was finished storing all those records?

 

Since you're using SQL_Execute for prepared statements your queries will be run synchronously on the main game thread, meaning since you're inserting so many rows the game will be unplayable. The most likely result of deleting records/tables in the middle of storing them is either SQL errors (table doesn't exist) or insert errors (records from the old ghost being stored among ones for the new ghost) depending on the structure of your database. Also, when using SQL_TQuery be sure if you run any non-threaded (synchronous) SQL operations (SQL_Query, SQL_FastQuery) that you call SQL_LockDatabase before running the non-threaded operations, and SQL_UnlockDatabase when you're done or you run the risk of corrupting data or driver memory.

 

On a side note, while SQL is very fast I doubt it was ever intended for the sort of real-time storage you're attempting, the queries may run quickly, but the overhead of processing them and sending them over a network is going to slow you down. You might want to try caching the data locally and inserting it later, like John suggested, or writing to a text file. Peace-Maker's excellent plugin Bot Mimic may be able to provide you with some ideas on how to get around the problem of storing the data. In Bot Mimic data is first stored to an array while the player records movement, and then written to a text file after the recording is done. For playback the plugin simply reads movements from the text file and stores them in another array.

Edited June 8, 2013 by Dreae

[.Syntax]

Just an FYI for you dabblers, Firebird (open source SQL DB) handles far more data than this extremely well. I've used it with systems that write about 200GB per day of data transactions, and maintains multi TB DB's (largest was roughly 5TB in size).

 

http://firebirdsql.org/