sqlite3 vs mysql vs async versions

[Cat]

first of all is it possible to execute code asynchronous? for example via the library (sqlite3) https://github.com/jreese/aiosqlite
which database is better to embed in game server for perfomance , need a seperate thread for it?
it is very important question i didn't found anything about it, need to store data effectively.

[Hymns For Disco]

SQLite would probably be worse for performance as the actual query processing is done by the python process itself.

Also, you should be able to find loads of information out there about executing database code asynchronously in python.
Here's a relevant post if mine in this site: viewtopic.php?f=17&t=1464&p=9785#p11220

[Cat]

i mean asyncchrounsly in terms of 1 thread. program itself decides which piece of code to execute and example which you provided uses another thread and i think it's bad for general use because it's useful to have a queue for the sql requests and it is better to execute callback in main thread again otherwise something may go wrong

[Ayuto]

Are you looking for something like this?


See lines 50-69 for an example. It creates a worker thread, which has its own queue. Using the method add_job() you can add a job function to the worker thread (optionally args and kwargs and a result handler function). The job function is executed in a different thread while the optional result handler function is executed in the main thread again.

[Cat]

good. but Hymns For Disco, sqlite3 is still good option if you run it in another process using multiprocessing module, also may be it will be better than Ayuto version i.e seperate process instead thread even for server databases using queue also
https://docs.python.org/3/library/multiprocessing.html

[Ayuto]

I doubt multiprocess is a good choice to run SQL statements for multiple reasons:
1. Starting a multiprocess is quite expensive. So, you should only start them for CPU intensive calculations that take a little time.
2. SQL statements aren't actually CPU intensive for the game server, but for the SQL server. Of course, the SQL server could be installed on the same machine, but since that is another process, it has the ability to use different CPU cores.
3. Multiprocess requires you to have Python installed on the game server, because you can't use the Source.Python Python instance. That is because that Python instance is embedded in the srcds process and not a separate installation.

(Just noticed that you meant SQlite in combination with multiprocess, but parts of my previous statement also apply to that)

You might also want to take a look at SQLAlchemy. It's an abstraction layer for SQL and allows you to decide later which database you want to use. That makes it a good option if you plan to publish a plugin, because it allows the server owner to decide which database system he wants to use. SQLAlchemy is included in SP.