SQLite Lag - Printable Version

SQLite Lag - Printable Version

+- Cuberite Forum (https://forum.cuberite.org)
+-- Forum: Plugins (https://forum.cuberite.org/forum-1.html)
+--- Forum: Plugin Discussion (https://forum.cuberite.org/forum-8.html)
+--- Thread: SQLite Lag (/thread-2194.html)

Pages: 1 2

SQLite Lag - gegi123 - 10-31-2015

I'm working on an economy Plugin and use SQLite for storage. When I use 'UPDATE' in SQL the Server lags for about 3 seconds.

Code:
DB:exec("UPDATE `money` SET `money`='" .. Money .. "' WHERE `uuid`='" .. PlayerUUID .. "'")

RE: SQLite Lag - sphinxc0re - 10-31-2015

Hello and welcome to the forum Smile

The problem here is, that the plugin system is synchronous, so every non-asynchronous read or write to the fiel system will end up blocking the server. try looking at these files:

https://github.com/cuberite/gallery/blob/master/Storage.lua

https://github.com/cuberite/gallery/blob/master/Storage_SQLite.lua

These are files from the gallery plugin which also uses SQLite.

RE: SQLite Lag - xoft - 10-31-2015

Three seconds sounds like a serious issue. Does it happen every time, or is it only occasional? We're doing much harder work with SQLite in other plugins and it has no such performance problems. What platform are you using? Debug or release build?

Also please note that your code is kinda vulnerable to SQL injection. You should never put external strings into the query, but rather use prepared statements and placeholders:

local stmt = DB:prepare("UPDATE money SET money = ? WHERE uuid = ?")
stmt:bind_values({Money, PlayerUUID})  -- Binds the question-marks in the previous query to actual values
stmt:step()  -- Execute the DB query
stmt:finalize()  -- Free up the resources

This is so common that most plugins wrap it in a helper function. In the Gallery plugin that SphinxC0re linked, it's here: https://github.com/cuberite/gallery/blob/master/Storage_SQLite.lua#L79-L107

RE: SQLite Lag - gegi123 - 10-31-2015

It happens every time and the lag is between 1-6 Seconds. I'm using Debian 8 and I don't know what build I am using. Where can i find it?

EDIT: Is it possible to use MySQL instead of SQLite?

RE: SQLite Lag - xoft - 11-01-2015

Could you try installing one of the following plugins and checking if they have the same lag issue with their SQLite queries?
- ProtectionAreas (included by default)
- Gallery ( https://github.com/cuberite/gallery ; needs setup )
- Coiny ( https://github.com/mc-server/Coiny ; seems to be what you're trying to write anyway)
- LastSeen ( https://github.com/madmaxoft/LastSeen ; very easy to setup)
- Aliases ( https://github.com/madmaxoft/Aliases )

Did you download the executable, or did you compile yourself?

RE: SQLite Lag - NiLSPACE - 11-01-2015

Core has the ban and whitelist that use SQLite. You could try that as well.

RE: SQLite Lag - gegi123 - 11-01-2015

I tested every Plugin you wrote and it always lags when something changes in the SQL. For example Coiny: /money = No Lag /money give [Player] [Amount] = Lag

I downloaded the executable

RE: SQLite Lag - xoft - 11-01-2015

I can't believe our code is doing that, we're just a thin wrapper over sqlite3. Could you try opening the affected DB file in the command-line "sqlite3" utility and try to time the queries there?

Code:
sqlite3 file.sqlite

.timer on

select * from Money;

insert into Money (Money, uuid) values (10, "1234");

update Money set money = 20 where uuid = "1234";

select * from Money;

.exit

RE: SQLite Lag - gegi123 - 11-01-2015

Code:
sqlite> select * from money;

530bf20d833e37ca8446fb6cc4d9dbc4|50.0

71ee0bf53d533e1481bf4bb56e9a7b60|50.0

b71a7be8902c3a2c8341606093982b33|1000.0

f25715baaee333598e990b452ab9b073|1000.0

0d14453e0bd93f5a884e6199439d4bf4|50.0

Run Time: real 0.009 user 0.001000 sys 0.000000

sqlite> insert into money (uuid, money) values ("test", 100);

Run Time: real 0.435 user 0.000000 sys 0.001999

sqlite> update money set money = 1000 where uuid = "test";

Run Time: real 0.375 user 0.000000 sys 0.002000

sqlite> select * from money;

530bf20d833e37ca8446fb6cc4d9dbc4|50.0

71ee0bf53d533e1481bf4bb56e9a7b60|50.0

b71a7be8902c3a2c8341606093982b33|1000.0

f25715baaee333598e990b452ab9b073|1000.0

0d14453e0bd93f5a884e6199439d4bf4|50.0

test|1000.0

Run Time: real 0.001 user 0.000000 sys 0.000000

sqlite> .exit

RE: SQLite Lag - xoft - 11-01-2015

That looks reasonable. I am out of ideas.