Raised This Month: $12 Target: $400
 3% 

Efficient Database Insertion


Post New Thread Reply   
 
Thread Tools Display Modes
Author Message
Potato Uno
Veteran Member
Join Date: Jan 2014
Location: Atlanta, Georgia
Old 11-13-2015 , 23:20   Efficient Database Insertion
Reply With Quote #1

So I am working on a plugin that logs events to a database. The event firings vary vastly. Some fire ~ at every game frame and some fire once every 5 minutes. As of right now, all callbacks record the data to a database using SQL_TQuery.

However, for such rapid event logging, is it efficient to keep on calling SQL_TQuery that frequently? Are transactions better? Give me some advice here. I have a feeling that spamming SQL_TQuery on average 15 times a second isn't a good idea but I really don't know. Transactions also have that other issue where the whole query is stored in memory (maybe even compiled into bytecode?) until the entire transaction is executed. Furthermore, if one of the queries in the transaction fails for some reason, the whole transaction is nuked.

This is an sqlite database, so each query takes microseconds (~ 0 ms) to execute. However, that still doesn't mean I'd use a shitty solution if there is a better way of doing it.

EDIT: Forgot to mention. All queries are INSERT INTO queries, so the callback is basically a blank function that logs errors if there is an SQL error.

The question boils down to: What is the most efficient way of inserting data to a database when the database is being asked to insert stuff at ~ 15 single queries/second?

Last edited by Potato Uno; 11-13-2015 at 23:39.
Potato Uno is offline
Impact123
Veteran Member
Join Date: Oct 2011
Location: Germany
Old 11-14-2015 , 03:19   Re: Efficient Database Insertion
Reply With Quote #2

A transaction should send the same amount of individual queries to the database so I'm not sure if that really makes that much of a difference.

This depends on your table structure (mind posting it and a sample insert?) but I'd use a query like this.
PHP Code:
INSERT INTO my_log (messageVALUES ('message1'),('message2'
Which would only be one query.
__________________

Last edited by Impact123; 11-14-2015 at 03:48.
Impact123 is offline
Mehis
Senior Member
Join Date: Mar 2013
Location: basement
Old 11-14-2015 , 04:28   Re: Efficient Database Insertion
Reply With Quote #3

I'd use this if adding full records.

PHP Code:
INSERT INTO table VALUES (xyz
Would it be possible to just save the logs as an arraylist and on map change do the queries? It of course depends on what you are logging.
Mehis is offline
Potato Uno
Veteran Member
Join Date: Jan 2014
Location: Atlanta, Georgia
Old 11-14-2015 , 09:35   Re: Efficient Database Insertion
Reply With Quote #4

Here's the table structure and stuff (probably more information than asked):

Spoiler


And Mehis that doesn't work because some arguments need to be null on the table. It's somewhat convoluted - I think the spoiler should make some more sense.
Potato Uno is offline
Potato Uno
Veteran Member
Join Date: Jan 2014
Location: Atlanta, Georgia
Old 11-14-2015 , 10:17   Re: Efficient Database Insertion
Reply With Quote #5

I think the idea here is, is spamming multiple SQL_TQuery calls more inefficient (and not like by 10% but like 50% more) than calling one transaction? I can care less how hard the database thread has to work since that runs off-thread. Since this is SQLite, the query lasts like 0 ms, (and 1 frame is 15 ms), so that's not even a concern. It's pawn performance that might be the killer here since that shares the same thread with srcds.

I mean if there is a negligible difference between a transaction and multiple SQL_TQuery calls (all queries are on the same default priority level - FYI), then I'd obviously take SQL_TQuery spam since transactions have that risk where if one query fails for some reason, the entire transaction is dropped.
Potato Uno is offline
Impact123
Veteran Member
Join Date: Oct 2011
Location: Germany
Old 11-14-2015 , 18:34   Re: Efficient Database Insertion
Reply With Quote #6

I hoped your table structure is a little bit simpler so you could temporarily store the data you'd insert into an finite array which will be used to construct the multi insert query when full or some time has passed.

I have no better idea at the moment so i tested how big the difference would be with 10 simple queries to the sqlite database. The code using a transaction looked like this.
PHP Code:
char query[1024];

Transaction txn SQL_CreateTransaction();
for (
int i=0<= 10i++)
{
    
Format(querysizeof(query), "INSERT INTO `test` (`id`) VALUES (%d)"GetRandomInt(1999999));
    
txn.AddQuery(query);
}

SQL_ExecuteTransaction(g_hDbHandletxnTXN_OnSuccessTXN_OnFailure); 
It took 0.014 ms on average on my machine for 10 queries, 100 took around 0.057 ms.

Then i used normal queries where the code looked like this.
PHP Code:
char query[1024];

for (
int i=0<= 10i++)
{
    
Format(querysizeof(query), "INSERT INTO `test` (`id`) VALUES (%d)"GetRandomInt(1999999));
    
g_hDbHandle.Query(SQLT_ErrorCheckCallbackquery);

This took 0.015 ms on average on my machine for 10 queries, 100 took around 0.075 ms.

Is that what you were interested in?
__________________

Last edited by Impact123; 11-14-2015 at 19:09.
Impact123 is offline
tommie113
AlliedModders Donor
Join Date: Oct 2013
Old 11-14-2015 , 19:30   Re: Efficient Database Insertion
Reply With Quote #7

I have not looked too much into details in the topic, but if you are worrying about running too many queries (slowing down server perhaps), you could aswell save all the data and run the queries OnMapEnd.
At that time it will not disturb anyone anyways.
__________________
No longer taking requests due to lack of time and interrest.
Only helping out with minor things through forum.
tommie113 is offline
Potato Uno
Veteran Member
Join Date: Jan 2014
Location: Atlanta, Georgia
Old 11-14-2015 , 20:56   Re: Efficient Database Insertion
Reply With Quote #8

@tommie: Nope, that won't work (45 minutes / mvm game x 60 seconds / minute x 15 queries / second). That's asking for a disaster right there with RAM (the number is 40k).

@Impact: That benchmark was really useful. So it is 30% slower (yikes) to spam SQL_TQuery multiple times than to run a full transaction.

There is a 60 second timer loop that runs forever throughout the game in the exact same plugin. Is it wiser to haul all the queries to a transaction object, and then once that 60 second timer loop fires, execute the transaction and make a new transaction object? Would there be potentially any issues with that? (I'd have to catch the transaction being executed under OnPluginEnd or a commandlistener of "quit".)

Last edited by Potato Uno; 11-14-2015 at 20:57.
Potato Uno is offline
Impact123
Veteran Member
Join Date: Oct 2011
Location: Germany
Old 11-14-2015 , 22:00   Re: Efficient Database Insertion
Reply With Quote #9

The transaction execution alone took about 0.005 ms with 900 (number shouldn't matter) queries added to it. If you don't add all the queries at once (which took me 0.4 ms) but over the time of those 60 seconds it should be fine. I can send you the code i used to test this via pm if you want, you might get slightly different results.
__________________

Last edited by Impact123; 11-14-2015 at 22:12.
Impact123 is offline
Powerlord
AlliedModders Donor
Join Date: Jun 2008
Location: Seduce Me!
Old 11-15-2015 , 14:18   Re: Efficient Database Insertion
Reply With Quote #10

Keep in mind that, in transactions, if a single query fails, all the queries are rolled back.

That is, assuming you're using a transactional database (i.e. InnoDB table types in MySQL).
__________________
Not currently working on SourceMod plugin development.
Powerlord is offline
Reply


Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump


All times are GMT -4. The time now is 06:30.


Powered by vBulletin®
Copyright ©2000 - 2024, vBulletin Solutions, Inc.
Theme made by Freecode