Re: Nginx + Lua + SQlite

[agentzh]

Hello!

On Thu, Apr 4, 2013 at 10:58 AM, Raimondas Budrius wrote:
> I would like to ask you, if I'm using Nginx as web server, what module you
> recommend me to use for connection with sqlite database? I found your
> module: ngx_lua. Using this module how I can connect to sqlite database?
> Thank you for your response.
>

Well, unlike most other SQL databases, SQLite is serverless:

http://www.sqlite.org/serverless.html

which means that using SQLite directly in Nginx will block the Nginx
worker processes and ruin the performance.

It is recommended to find or write a TCP service wrapper around SQLite
and use ngx_lua's cosocket API to develop a nonblocking pure Lua
driver as the lua-resty-mysql library:

https://github.com/agentzh/lua-resty-mysql

BTW, I'm cc'ing the openresty-en mailing list:
https://groups.google.com/group/openresty-en And you're recommended to
join the list and discuss such things there :)

Thanks!
-agentzh

[Tianzhou Chen]

Resurrect this thread.

Embedding SQLite directly into OpenResty will allow to deliver the solution in a single binary. A lot of applications don't require high performance while a single binary including webserver, database is quite attractive.

Thanks
Tianzhou

[Aapo Talvensaari]

On 25 May 2016 at 09:08, Tianzhou Chen <tianzh...@gmail.com> wrote:

Resurrect this thread.

Embedding SQLite directly into OpenResty will allow to deliver the solution in a single binary. A lot of applications don't require high performance while a single binary including webserver, database is quite attractive.

I absolutely agree! Having some real SQL type of database embedded directly inside OpenResty would be a killer thing to have (whether or not it will archieve 10M concurrent connections). And there is already a feasible upgrade path when you need more scalability: https://github.com/rqlite/rqlite (actually I'm thinking of writing OpenResty client for that). These guys seems to be happy with SQLite: http://www.learnbchs.org/.

I have also build many services on top of SQLite with PHP and I have rarely hit any problems. Maybe it is not webscale, but it surely gets its job done, with minimal dependencies and servers.

[Aapo Talvensaari]

On Wednesday, 25 May 2016 10:40:30 UTC+3, Aapo Talvensaari wrote:

And there is already a feasible upgrade path when you need more scalability: https://github.com/rqlite/rqlite

To correct myself:

 when you need more scalability -> when you need more reliability (fault tolerance).

[Robert Paprocki]

I'll voice a bit of friendly contention here. To me this smacks a bit much of "and the kitchen sink" philosophy. While I'm very much in favor of OpenResty continuing to grow and develop, I think we should be very cautious about what gets added into the core of the project. Tossing on more and more large-scale features like a native database server feels very anti-Unix philosophy (which we all embrace to some extent, otherwise we wouldn't be here ;) ). It also certainly directly violates the design philosophy of Nginx/OpenResty (non-blocking, high-performance architecture). Throwing that away in the name of shiny features feels very dirty, and could be dangerous for someone searching for a high-performance server who then implements a poorly-performing feature and expects it to work out of the box. Perhaps as preemptive Lua threading support is added (if that's even on the roadmap) the impact of the blocking nature of something like SQLite could be minimized, but I suspect that's a low priority and far off in the future.

Also worth a pedantic note, OpenResty isn't currently delivered as a single binary. It's a single project, but multiple binaries (the Nginx binary itself, LuaJIT, CJSON, etc), are delivered as separate binaries.

--
You received this message because you are subscribed to the Google Groups "openresty-en" group.
To unsubscribe from this group and stop receiving emails from it, send an email to openresty-en...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Aapo Talvensaari]

On Wednesday, 25 May 2016 19:03:33 UTC+3, rpaprocki wrote:

Also worth a pedantic note, OpenResty isn't currently delivered as a single binary. It's a single project, but multiple binaries (the Nginx binary itself, LuaJIT, CJSON, etc), are delivered as separate binaries.

Yeah, embedding something directly doesn't necessarily mean that it should go to the core (if core is considered as Lua Nginx (Stream) Module or lua-resty-core). And Nginx supports modules now, so that could be another route. I suppose you could use something like this: http://scilua.org/ljsqlite3.html directly from OpenResty or that rqlite (nonblocking on Nginx side).

I wouldn't worry about people using things wrong and expecting something. Btw. native database server we are talking here is a single C source file, and that's about it.

[Lord Nynex]

Hello, 

I wanted to share my opinion on this matter. 

I have personally found myself in a situation where I would like to use sqlite for dev with openresty. I've also had similar situations with a litany of other applications not including sqlite. I agree with Robert that embedding things into openresty for convenience are antithetical to nginx and openrestys design goals. There is already a fairly steep learning curve to nginx and openresty. New users must understand lua, luajit, nginx, code cache, shared dict, how to find a proper module that fits openresty (ie not using socket etc). Adding sqlite will add a new world of bugs and development issues around multiple worker processes reading/writing the same sqlitedb at the same time. This may be an acceptable issue for development but it is certainly not viable for software aimed at horizontal scalability. 

IMHO, I think there are two 'issues' here. 

- The desire for a single statically compiled binary

- The need for a 'buffering layer' in IO intensive operations. 

The first issue is somewhat achievable with lua byte code and AR. Lately I've been wondering what would go into creating ngx modules in golang. It's certainly possible, but I have not tried. This would open a lot of new possibilities.Another possibility is encapsulating the entire openresty package inside a compiled go binary (a'la bindata). Or maybe even something like afero fs. 

The second issue is a bit more involved. There is a need to perform blocking operations in a non-blocking way. There is one project that provides an insecure C daemon that accepts subshell commands over a unix socket. I think this module has great importance and has been overlooked. I think an interesting compromise here is a new type of worker process like the master proc. It would be very nice to have a monitored process within nginx that is capable of executing subshell calls or heavy weight IO operations without blocking the workers entirely. 

If this additional process had a flexible API, none of these ecosystem gripes would exist. Plainly put, if you want sqlite, you should be able to delegate these transactions to a dedicated process and wait for a response in a non-blocking way. 

Anyways, just my opinion.

-Brandon

--

[Yichun Zhang (agentzh)]

Hello!

On Tue, May 24, 2016 at 11:08 PM, Tianzhou Chen wrote:
> Resurrect this thread.
>
> Embedding SQLite directly into OpenResty will allow to deliver the solution
> in a single binary. A lot of applications don't require high performance
> while a single binary including webserver, database is quite attractive.
>

Embedding the LMDB-based SQLite3 implementation into OpenResty might
be a good fit:

https://github.com/LMDB/sqlightning

I haven't looked at this sqlightning project seriously myself yet. But
LMDB itself looks like a great fit for the OpenResty model :)

Best regards,
-agentzh

[Abdul Hakeem]

Tarantool already has an NGINX upstream module with a database engine that
supports NoSQL, MySQL and Memcached protocols.
https://github.com/tarantool/nginx_upstream_module

[Yichun Zhang (agentzh)]

Hello!

On Fri, May 27, 2016 at 2:00 PM, Abdul Hakeem wrote:
> Tarantool already has an NGINX upstream module with a database engine that
> supports NoSQL, MySQL and Memcached protocols.
> https://github.com/tarantool/nginx_upstream_module
>

Apparently a self-contained OpenResty app server is much more
attractive to me :) Well, just my 2 cents.

Regards,
-agentzh

[Abdul Hakeem]

I am almost positive Tarantool can be embedded into OpenRestry, without reinventing the wheel.
I have a need for a Lua scripting environment haven't with an embedded NoSQL/SQL, I haven't given Tarantool much thoughts for now, but its on my radar.

[luis....@interactive3g.com]

Hi,

 

Sophia db [1] could be a good fit as well. It is the store that tarantool uses for persistence and it has a couple of lua bindings [2]. Both the features list and the performance numbers are impressive.

 

That being said, I have no real experience with it.

 

[1] http://sophia.systems/v2.1/index.html

 

[2] https://github.com/Wiladams/LJIT2Sophia and https://github.com/mkottman/lua-sophia

 

 

-----Original Message-----
From: "Yichun Zhang (agentzh)" <age...@gmail.com>
Sent: Friday, 27 May, 2016 21:42
To: "openresty-en" <openre...@googlegroups.com>
Subject: Re: [openresty-en] Re: Nginx + Lua + SQlite

[Avant Yao]

Hello!

How about solutions from http://www.sqlite.org/cvstrac/wiki?p=SqliteNetwork  ?   netSQLite , SQL4Sockets ...

[Nesvarbu Nereikia]

Hi,

would using LuaSqlite: (http://lua.sqlite.org/index.cgi/home) from content_by_lua   block nginx? Openresty docs say that content_by_lua is executed in a new spawned coroutine (https://openresty-reference.readthedocs.io/en/latest/Directives/#content_by_lua), does that mean it will be handled by nginx event queue and not block on reading sqlite files?

[reflection_probe]

Lua uses cooperative multitasking, that means that "running in a coroutine" means nothing unless asynchronous tasks yield. since LuaSqlite was not designed to run in nginx, any blocking IO that it does will not yield the coroutine, resulting in blocking the entire worker process. wouldn't recommend using it unless it provides a zero-G interface where you can do all the IO yourself.

[Nesvarbu Nereikia]

Thanks for the comprehensive and clear answer. If sqlite is zero-G lib, it should be well suited to be made into an openresty module. Somebody please do it!) I don't have enough expertise for this.

[reflection_probe]

openresty doesn't support sqlite directly, but it shouldn't need to. you should be able to communicate with an existing server using LuaRestyMySQLLibrary just fine.

you really should be using an external server anyway because nginx uses multiple worker processes (and therefore multiple Lua VMs).

[Gene Unigovski]

I think some support for sqlite library could be implemented via brand new `ngx.run_worker_thread` API - https://github.com/openresty/lua-nginx-module#ngxrun_worker_thread - which should allow to invoke blocking sqlite library functions

[reflection_probe]

using multithreading just so you can treat blocking APIs as asynchronous
is a bad idea, and tends to hurt performance. something could probably
be hacked together, but it would be a very bad solution.

[Nesvarbu Nereikia]

Yes I know about the support for MySQL, I am thinking about the use of SQLite though as an option for a DB that doesn't need to be hosted in it's own process. My prime use-case is a cheap VM that requires only one vCPU with one process with event queue for asynchronous IO.

Don't understand your argument that I should be using external server because nginx uses multiple worker processes. I understand the number of worker processes is cofigurable, and one process for one vCPU in a cheap VM is what I'm after. Can you elaborate on your argument please?

[reflection_probe]

linux uses pre-emptive scheduling, this means that multiple processes
can run on one core, and in fact they have to. running an external MySQL
server is not going to be as big of a bottleneck as SQLite would be on
your nginx server by using synchronous/blocking I/O.

my argument was based on the incorrect assumption that SQLite didn't
support multiple processes accessing the same file at once. I did some
more research in the background and found out that is indeed not the
case. so even if you did have multiple worker processes you would be
fine in terms of avoiding database corruption.

the only problem then would be that any SQLite operations would block
that worker process from handling any new or existing requests until it
finished. the fact that you only have 1 worker process means that every
request would completely halt the server until it was finished with any
database operations, which sounds like a big denial of service
vulnerability to me.

would not recommend using blocking operations on openresty.

[Nesvarbu Nereikia]

Thanks on linux pre-emptive scheduling explanation. Guess context switching of those processes on one core is not big of a performance loss?

Yes, blocking IO on nginx is what I am trying to avoid. I was thinking if SQLite is a zero-g lib, then it can be made into a non-blocking, event queue based DB that doesn't have to be hosted in a separate process.

Basically creating openresty SQLite module by injecting IO primitives into SQLite lib so that it would not block, but instead would be put onto an event queue. Is that something worth doing?

[reflection_probe]

> Thanks on linux pre-emptive scheduling explanation. Guess context
> switching of those processes on one core is not big of a performance loss?

it's not a huge performance loss. adding a database server definitely
isn't going to do much compared to the overhead that the rest of the OS
already has on nginx if you really only have one core. the best thing
you could do is get another core, put the entire system on core 0 and
have only nginx on core 1.

Kamatera offers stripped-down installs that only have SSH & a package
manager. (& python but that can and should be removed) then you install
anything else you need and that's all that's running on the machine.
that's your best bet if you can't add another core.

> Yes, blocking IO on nginx is what I am trying to avoid. I was thinking
> if SQLite is a zero-g lib, then it can be made into a non-blocking,
> event queue based DB that doesn't have to be hosted in a separate process.

I have no reason to believe otherwise. after all, plenty of applications
use SQLite to serve many requests concurrently. just not sure exactly if
they use non-blocking SQLite or just spawn OS threads.

SQLite may already offer a non-blocking API.

> Basically creating openresty SQLite module by injecting IO primitives
> into SQLite lib so that it would not block, but instead would be put
> onto an event queue. Is that something worth doing?

probably. just make sure nobody else has done it. I'm sure you'd have
tons of interested users in no time.