SQLite Lag

[gegi123]

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 .. "'")

[sphinxc0re]

Hello and welcome to the forum

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...torage.lua

https://github.com/cuberite/gallery/blob...SQLite.lua

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

[xoft]

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...a#L79-L107

[gegi123]

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?

[xoft]

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?

[NiLSPACE]

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

[gegi123]

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

[xoft]

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

[gegi123]

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

[xoft]

That looks reasonable. I am out of ideas.