Raised This Month: $ Target: $400
 0% 

SQL Stats


Post New Thread Reply   
 
Thread Tools Display Modes
Author Message
portocala
Member
Join Date: Jun 2010
Old 06-10-2010 , 15:33   SQL Stats
Reply With Quote #1

Hello,

I'm working on a plugin that records live statistics of players on my CS 1.6 Server (kills, deaths, hs, online time, etc) and I'm wondering if SQL is able to handle this without lag. I intend to display the results on a webpage.

I worry about DeathMsg event, because there will be more data traffic (think about a 22 players Deathmatch Server). Generaly speaking it's not much, but it is for SQL ?

Every kill / hs / death is send to MySQL Database with SQL_PrepareQuery and SQL_Execute.

I respectfully request your opinions about that.

Excuse me if I made mistakes of expression.
portocala is offline
Brreaker
Senior Member
Join Date: Oct 2009
Location: Constanta, Romania
Old 06-10-2010 , 16:43   Re: SQL Stats
Reply With Quote #2

I think MySQL can handle it, also, is your server performant enough to handle this ?
__________________
There are 10 kinds of people.Those who understand binary, and those who don't.
Also, for those who understand binary, there is a donation tab too!
No steam || PM support!
Brreaker is offline
Send a message via MSN to Brreaker Send a message via Yahoo to Brreaker
portocala
Member
Join Date: Jun 2010
Old 06-11-2010 , 02:56   Re: SQL Stats
Reply With Quote #3

Quote:
Originally Posted by Brreaker View Post
I think MySQL can handle it, also, is your server performant enough to handle this ?
Yes. No problems about the server's configurations (CPU, RAM, etc).

Quote:
Originally Posted by unnyquee View Post
Use threaded queries.
Do you mean SQL_ThreadQuery ? I had problems using this. Every map end / start I got errors ([MySQL] Thread worker was unable to start.), so I started code using SQL_PrepareQuery and SQL_Execute which by now works perfectly.

Can you explain the difference between SQL_ThreadQuery and SQL_PrepareQuery+SQL_Execute ? Honestly, I have searched but not found anything about it.

Quote:
Originally Posted by fang View Post
Try psychostats?
I used Psychostats, but now I haven't an external server to perform the log processing, so I thought to build my own way to get these information on my website.



Thanks for the replies!


Edited:

If I have this:

Code:
new Handle:Query;

public event_death() {
    new killer = read_data(1);
    new victim = read_data(2);
    new name_killer[32]; get_user_name(killer, name_killer, 31);
    new name_victim[32]; get_user_name(victim, name_victim, 31);
    
    SQL_QuoteString(g_SqlConnection, name_killer, 31, name_killer)
    SQL_QuoteString(g_SqlConnection, name_victim, 31, name_victim)
    
    Query = SQL_PrepareQuery(g_SqlConnection, "SELECT kills FROM %s WHERE name='%s';", table, name_killer)
    SQL_Execute(Query);
    
    if(SQL_NumResults(Query)) {
        new kills = SQL_ReadResult(Query, 0);
        SQL_FreeHandle(Query);
        Query = SQL_PrepareQuery(g_SqlConnection, "UPDATE %s SET kills='%d' WHERE name='%s';",table, kills + 1, name_killer) 
        SQL_Execute(Query);
    }
    
    SQL_FreeHandle(Query);
}

What happens when 'double/triple-kill' ? Is it OK for SQL ?

Last edited by portocala; 06-11-2010 at 03:31.
portocala is offline
Sylwester
Veteran Member
Join Date: Oct 2006
Location: Poland
Old 06-11-2010 , 05:57   Re: SQL Stats
Reply With Quote #4

Quote:
Originally Posted by portocala View Post
Do you mean SQL_ThreadQuery ? I had problems using this. Every map end / start I got errors ([MySQL] Thread worker was unable to start.), so I started code using SQL_PrepareQuery and SQL_Execute which by now works perfectly.

Can you explain the difference between SQL_ThreadQuery and SQL_PrepareQuery+SQL_Execute ? Honestly, I have searched but not found anything about it.
It's all explained in sqlx tutorial:
Quote:
Originally Posted by Hawk552 View Post
The true power lies in threaded querying. Threaded querying means nothing short of a rewrite of an entire plugin that's written with DBI, but has huge advantages, especially on slow connections. The idea of a threaded query is essentially that a new thread (a sub-process) with which its only goal is to send, monitor, and inform of the things that happen to a query. Because it is on a seperate thread, a query that would normally take 1 second (and 1 second of total connection loss for all clients in the server) still takes the same amount of time, but does not interrupt gameplay
So, forget about prepare/execute query and go back to using threaded queries. I recently posted sqlx example that uses sqlite (file on localhost):
http://forums.alliedmods.net/showthr...66#post1204466
Maybe it will help you fix your problems with threaded queries.
__________________
Impossible is Nothing
Sylwester is offline
portocala
Member
Join Date: Jun 2010
Old 06-11-2010 , 07:31   Re: SQL Stats
Reply With Quote #5

OK, Thank you!

I rewrote the code using SQL_ThreadQuery but it is not working at all.

I get:

Code:
L 06/11/2010 - 12:41:57: Function "event_death" was not found
L 06/11/2010 - 12:41:57: [AMXX] Displaying debug trace (plugin "stats_gg.amxx")
L 06/11/2010 - 12:41:57: [AMXX] Run time error 19: function not found 
L 06/11/2010 - 12:41:57: [AMXX]    [0] stats_gg.sma::plugin_init (line 23)
L 06/11/2010 - 12:42:02: [MySQL] Invalid database handle: 0
L 06/11/2010 - 12:42:02: [AMXX] Displaying debug trace (plugin "stats_gg.amxx")
L 06/11/2010 - 12:42:02: [AMXX] Run time error 10: native error (native "SQL_PrepareQuery")
L 06/11/2010 - 12:42:02: [AMXX]    [0] stats_gg.sma::authorizedx (line 76)
L 06/11/2010 - 12:42:02: [AMXX]    [1] stats_gg.sma::client_putinserver (line 57)
.
.
.
And there is my entire plugin:

Code:
#include <amxmodx>
#include <amxmisc>
#include <cstrike>
#include <sqlx>
#include <fakemeta>

#define PLUGINNAME    "gg stats"
#define VERSION        "1.0"
#define AUTHOR        "me"

#define table           "gg_statsx13"
#define table2          "gg_stats"

new Handle:g_SqlX;
new Handle:g_SqlConnection;
new g_error[512];
new clientcheck[32];

public plugin_init()
{
    register_plugin(PLUGINNAME, VERSION, AUTHOR);
    register_event("DeathMsg", "event_death", "a");
    sql_init();
}

public sql_init()
{
    new host[64], user[64], pass[64], db[64], errorcode;

    get_cvar_string("amx_sql_host", host, 63);
    get_cvar_string("amx_sql_user", user, 63);
    get_cvar_string("amx_sql_pass", pass, 63);
    get_cvar_string("amx_sql_db", db, 63);

    g_SqlX = SQL_MakeDbTuple(host, user, pass, db);
    g_SqlConnection = SQL_Connect(g_SqlX,errorcode,g_error,511);
    
    if (g_SqlConnection == Empty_Handle) set_fail_state(g_error);
    else 
    {
        new query1[1001]; format(query1, 1000, "CREATE TABLE IF NOT EXISTS `%s`(`name` varchar(100) NOT NULL default '', `kills` int(10) NOT NULL default '0',`hs` int(10) NOT NULL default '0',`deaths` int(10) NOT NULL default '0', `time` int(10) NOT NULL default '0',`firstseen` varchar(100) NOT NULL default '',", table);
        new query2[1001]; format(query2, 1000, "`lastseen` varchar(100) NOT NULL default '',`steam` int(10) NOT NULL default '0',`nonsteam` int(10) NOT NULL default '0',`lastip` varchar(100) NOT NULL default '',`wins` int(10) NOT NULL default '0',`points` int(10) NOT NULL default '0');");
        new cache[1001]; format(cache, 1000, "%s%s", query1, query2);
        SQL_ThreadQuery(g_SqlX, "DefaultHandle", cache);
    }
}

public client_connect(id) 
{
    clientcheck[id] = 0;
}

public client_putinserver(id) 
{
    if(clientcheck[id] == 1) authorizedx(id);
    else clientcheck[id] = 1;
}

public client_authorized(id) 
{
    if(clientcheck[id] == 1) authorizedx(id);
    else clientcheck[id] = 1;
}

public authorizedx(id) 
{
    new name[32]; get_user_name(id, name, 31);
    new authid[32]; get_user_authid(id, authid, 31);
    new ip[32]; get_user_ip(id, ip, 31, 1);
    SQL_QuoteString(g_SqlConnection, name, 31, name);
    
    new cache[1001];
    
    format(cache, 1000, "SELECT * FROM %s WHERE name='%s';", table, name);
    SQL_ThreadQuery(g_SqlX, "handle_firstcheck", cache, name, 31);
    
    format(cache, 1000, "SELECT wins FROM %s WHERE name='%s';", table2, name);
    SQL_ThreadQuery(g_SqlX, "handle_wins", cache, name, 31);
    
    format(cache, 1000, "SELECT points FROM %s WHERE name='%s';", table2, name);
    SQL_ThreadQuery(g_SqlX, "handle_points", cache, name, 31);
    
    format(cache, 1000, "UPDATE %s SET lastip='%s' WHERE name='%s';", table, ip, name);
    SQL_ThreadQuery(g_SqlX, "DefaultHandle", cache);
    
    if(contain(authid, "STEAM_0") != -1) 
    {
        format(cache, 1000, "SELECT steam FROM %s WHERE name='%s';", table, name);
        SQL_ThreadQuery(g_SqlX, "handle_steam", cache, name, 31);
    } 
    else 
    {
        format(cache, 1000, "SELECT nonsteam FROM %s WHERE name='%s';", table, name);
        SQL_ThreadQuery(g_SqlX, "handle_nonsteam", cache, name, 31);
    }
        
}

public handle_firstcheck(FailState,Handle:Query,Error[],Errcode,Data[],DataSize)
{
    if(!SQL_NumResults(Query)) 
    {
        new currentTime[32]; get_time("%d %b %Y - %H:%M", currentTime, 31);
        new cache[1000];
        format(cache, 1000, "INSERT INTO %s (name, kills, hs, deaths, time, firstseen, lastseen, steam, nonsteam, lastip, wins, points) values ('%s', '0', '0', '0', '0', '%s', '%s', '0', '0', '', 0, 0);",table, Data, currentTime, currentTime);
        SQL_ThreadQuery(g_SqlX, "DefaultHandle", cache);
    }
}

public handle_wins(FailState,Handle:Query,Error[],Errcode,Data[],DataSize)
{
    if(SQL_NumResults(Query)) 
    {
        new wins = SQL_ReadResult(Query, 0);
        new cache[1000];
        format(cache, 1000, "UPDATE %s SET wins='%d' WHERE name='%s';",table, wins, Data);
        SQL_ThreadQuery(g_SqlX, "DefaultHandle", cache);
    }
}

public handle_points(FailState,Handle:Query,Error[],Errcode,Data[],DataSize)
{
    if(SQL_NumResults(Query)) 
    {
        new points = SQL_ReadResult(Query, 0);
        new cache[1000];
        format(cache, 1000, "UPDATE %s SET points='%d' WHERE name='%s';",table, points, Data);
        SQL_ThreadQuery(g_SqlX, "DefaultHandle", cache);
    }
}

public handle_steam(FailState,Handle:Query,Error[],Errcode,Data[],DataSize)
{
    if(SQL_NumResults(Query)) 
    {
        new steam = SQL_ReadResult(Query, 0);
        new cache[1000];
        format(cache, 1000, "UPDATE %s SET steam='%d' WHERE name='%s';",table, steam + 1, Data) ;
        SQL_ThreadQuery(g_SqlX, "DefaultHandle", cache);
    }
}

public handle_nonsteam(FailState,Handle:Query,Error[],Errcode,Data[],DataSize)
{
    if(SQL_NumResults(Query)) 
    {
        new nonsteam = SQL_ReadResult(Query, 0);
        new cache[1000];
        format(cache, 1000, "UPDATE %s SET nonsteam='%d' WHERE name='%s';",table, nonsteam + 1, Data) ;
        SQL_ThreadQuery(g_SqlX, "DefaultHandle", cache);
    }
}

public event_death() 
{
    new killer = read_data(1);
    new victim = read_data(2);
    new name_killer[32]; get_user_name(killer, name_killer, 31);
    new name_victim[32]; get_user_name(victim, name_victim, 31);
    SQL_QuoteString(g_SqlConnection, name_killer, 31, name_killer);
    SQL_QuoteString(g_SqlConnection, name_victim, 31, name_victim);
    
    new cache[1001];
    
    if(read_data(3)) 
    {
        format(cache, 1000, "SELECT hs FROM %s WHERE name='%s';", table, name_killer);
        SQL_ThreadQuery(g_SqlX, "handle_hs", cache, name_killer, 31);
    }
    else 
    {
        format(cache, 1000, "SELECT kills FROM %s WHERE name='%s';", table, name_killer);
        SQL_ThreadQuery(g_SqlX, "handle_kills", cache, name_killer, 31);
    }
    
    format(cache, 1000, "SELECT deaths FROM %s WHERE name='%s';", table, name_victim);
    SQL_ThreadQuery(g_SqlX, "handle_deaths", cache, name_victim, 31);
}

public handle_hs(FailState,Handle:Query,Error[],Errcode,Data[],DataSize)
{
    if(SQL_NumResults(Query))
    {
        new hs = SQL_ReadResult(Query, 0);
        new cache[1000];
        format(cache, 1000, "UPDATE %s SET hs='%d' WHERE name='%s';",table, hs + 1, Data);
        SQL_ThreadQuery(g_SqlX, "DefaultHandle", cache);
    }
}

public handle_kills(FailState,Handle:Query,Error[],Errcode,Data[],DataSize)
{
    if(SQL_NumResults(Query)) 
    {
        new kills = SQL_ReadResult(Query, 0);
        new cache[1000];
        format(cache, 1000, "UPDATE %s SET kills='%d' WHERE name='%s';",table, kills + 1, Data);
        SQL_ThreadQuery(g_SqlX, "DefaultHandle", cache);
    }
}

public handle_deaths(FailState,Handle:Query,Error[],Errcode,Data[],DataSize)
{
    if(SQL_NumResults(Query)) 
    {
        new deaths = SQL_ReadResult(Query, 0);
        new cache[1000];
        format(cache, 1000, "UPDATE %s SET deaths='%d' WHERE name='%s';",table, deaths + 1, Data);
        SQL_ThreadQuery(g_SqlX, "DefaultHandle", cache);
    }
}

public client_disconnect(id) 
{
    clientcheck[id] = 0;

    new name[32]; get_user_name(id, name, 31);
    new currentTime[32]; get_time("%d %b %Y - %H:%M", currentTime, 31);
    SQL_QuoteString(g_SqlConnection, name, 31, name);
    
    new cache[1001];
    
    format(cache, 1000, "SELECT time FROM %s WHERE name='%s';", table, name);
    SQL_ThreadQuery(g_SqlX, "handle_time", cache, name, 31);
    
    format(cache, 1000, "UPDATE %s SET lastseen='%s' WHERE name='%s';",table, currentTime, name);
    SQL_ThreadQuery(g_SqlX, "DefaultHandle", cache);
}

public handle_time(FailState,Handle:Query,Error[],Errcode,Data[],DataSize)
{
    if(SQL_NumResults(Query)) 
    {
        new timex = SQL_ReadResult(Query, 0);
        new player = find_player("a", Data);
        new cache[1000];
        format(cache, 1000, "UPDATE %s SET time='%d' WHERE name='%s';",table, timex + get_user_time(player, 1), Data);
        SQL_ThreadQuery(g_SqlX, "DefaultHandle", cache);    
    }
}

public DefaultHandle(FailState,Handle:Query,Error[],Errcode,Data[],DataSize)
{
    
}

public plugin_end() 
{ 
    SQL_FreeHandle(g_SqlX);
}
There must be some scripting mistakes, but I can't see where.
I don't expect you to do all the work for me, I just need some tips.

Thanks again!
portocala is offline
unnyquee
Senior Member
Join Date: Jun 2009
Location: Constanta, Romania
Old 06-10-2010 , 17:10   Re: SQL Stats
Reply With Quote #6

Use threaded queries.
__________________
unnyquee is offline
fang
Senior Member
Join Date: Nov 2007
Location: New Jersey
Old 06-10-2010 , 18:01   Re: SQL Stats
Reply With Quote #7

Try psychostats?
fang is offline
Brreaker
Senior Member
Join Date: Oct 2009
Location: Constanta, Romania
Old 06-11-2010 , 03:39   Re: SQL Stats
Reply With Quote #8

It should be, as far as I know you may enter up to 150 data/second
__________________
There are 10 kinds of people.Those who understand binary, and those who don't.
Also, for those who understand binary, there is a donation tab too!
No steam || PM support!
Brreaker is offline
Send a message via MSN to Brreaker Send a message via Yahoo to Brreaker
ProIcons
Senior Member
Join Date: Jan 2009
Location: Greece - Salonica
Old 06-11-2010 , 07:47   Re: SQL Stats
Reply With Quote #9

If you make the SQL Update while a Player Kills Someone else, if mysql is on another machine from your server the server will be freeze from 1 as to 5 seconds, I Suggest you to save players's stats on variables and when the Map finish or when you want with a command to update all the kills,
__________________
function rb return $regsubex($$1-,/(.)/g,$+($chr(2) $+ $chr(3),$r(2,15),$chr(2),\1))
ProIcons is offline
Sylwester
Veteran Member
Join Date: Oct 2006
Location: Poland
Old 06-11-2010 , 13:07   Re: SQL Stats
Reply With Quote #10

@portocala: That error log does not match with the code. Maybe you forgot to copy .amxx file to plugins folder before testing it.
Quote:
Originally Posted by ProIcons View Post
If you make the SQL Update while a Player Kills Someone else, if mysql is on another machine from your server the server will be freeze from 1 as to 5 seconds
That will never happen if you use threaded queries.
__________________
Impossible is Nothing
Sylwester is offline
Reply



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 05:20.


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