[Reflex]

Hello guys, I need some help. I've a sm plugin which cause noticeable lags on server. After some investigation I've found that this lags caused by sqlite queries. I digged deeper and wrote small plugin to test what is going wrong. There it is:

new Handle:g_hDatabase = INVALID_HANDLE;

public OnPluginStart() {
	SQL_TConnect(OnDBConnect, "test_sqlite");
	RegConsoleCmd("sm_test_sqlite", Command_TestSqlite);
}

public OnDBConnect(Handle:owner, Handle:hndl, const String:error[], any:data)
{
	if (hndl == INVALID_HANDLE) {
		PrintToChatAll("Error in OnDBConnect: %s", error);
	}
	else {
		g_hDatabase = hndl;
		SQL_FastQuery(g_hDatabase, "DROP TABLE IF EXISTS test_sqlite");
		SQL_FastQuery(g_hDatabase, "CREATE TABLE IF NOT EXISTS test_sqlite (key VARCHAR(8) NOT NULL)");
		SQL_FastQuery(g_hDatabase, "INSERT INTO test_sqlite VALUES ('value')");
	}
}

public Action:Command_TestSqlite(client, args)
{
	SQL_TQuery(g_hDatabase, SelectCallback, "SELECT 1");
	return Plugin_Handled;
}

public SelectCallback(Handle:owner, Handle:hndl, const String:error[], any:data)
{
	if (hndl == INVALID_HANDLE) {
		PrintToChatAll("Error in SelectCallback: %s", error);
	}
	else  {
		SQL_TQuery(g_hDatabase, EmptyCallback, "UPDATE test_sqlite SET key = 'value' WHERE key = 'value'");
	}
}

public EmptyCallback(Handle:owner, Handle:hndl, const String:error[], any:data)
{
	if (hndl == INVALID_HANDLE) {
		PrintToChatAll("Error in EmptyCallback: %s", error);
	}
}

As you could see this plugin simply run two sequential threaded queries, one select then one update.

And every time I run sm_test_sqlite command in game I getting a lag. There is a screenshot http://i.imgur.com/S5ZyRdZ.png where I run it five times.

I would like to know if it is bug or if there is simple way to remove lags without touching the code.

My configuration: Linux; srcds v2377855; mm:s v1.10.2V, sm v1.6.2-dev+4558

Here, have some

[asherkin]

Is your server's filesystem amazingly terrible at writing?

[JoB2C]

This test plugin is making both threaded (SQL_T*) and non-threaded (SQL_FastQuery) queries on the same handle and without lock (but that's not the best option).

In your real plugins, be sure to use two DB handles (and not to use a previous persistent connection, with the boolean parameter, to open them) to avoid any possible dead-lock.

And use non-threaded queries only if really needed, as they can cause lags.

[Reflex]

asherkin, it's dedicated server with good hardware. The hdd seems to be ok. I've done some testing with dd. I'm not to familiar with such kind of benchmarking, maybe these values isn't good at all.

# dd if=/dev/zero of=/mnt/1/games/test.bin bs=1G count=1 conv=fdatasync
1+0 records in
1+0 records out
1073741824 bytes (1.1 GB) copied, 20.6255 s, 52.1 MB/s

# dd if=/dev/zero of=/mnt/1/games/test.bin bs=1M count=1024 conv=fdatasync
1024+0 records in
1024+0 records out
1073741824 bytes (1.1 GB) copied, 20.0652 s, 53.5 MB/s

JoB2C, I know about limitation of using both types of queries at same time, but in my plugin non threaded queries only used at startup to populate DB, and nowhere else.

Also I've tried to reproduce this issue on my home PC with Win7. And I've found that lags only appear if I bind sm_test_sqlite to button and starts to press it rapidly.

[Reflex]

Well, whatever, I've refactored a plugin to make one big all-in-one query instead of several sequential. This works well as workaround. Lags still occurs if someone abuse my plugin, so I added functionality to limit rate of queries.