Recent

Author Topic: [CLOSED] DB accessing application staying in memory  (Read 2983 times)

egsuh

  • Hero Member
  • *****
  • Posts: 1273
[CLOSED] DB accessing application staying in memory
« on: July 10, 2020, 01:38:03 am »
Can I write such a program (or library)?

What I want is keeping logged to a database, and doing DB-jobs (inserting, updating, viewing, deleting records, etc.) when requests come in. This could be a DB server in server-client relationship.

I need this function for web server.  My web-server program has run welll without any problem in CGI mode. But CGI is supposed to re-connect at every request. Up to now, the requests are not so many, and my (very low grade PC machine  :D) server had no problem in handling them.

FastCGI approach (or ISAPI/NSAPI of Delphi) is supposed to be a solution, as the application remains in the memory and responds to requests. But currently Free Pascal's fCGI is not perfect (which Michael Van Canneyt adimitted --- I have no intention to blame him for any reason. I always appreciate his contributions.)

I'm looking for various approaches. I think one possibility is to separate a database server from web-server on the same machine (and maybe later have to separate). 

Sometimes I wonder that CGI modules remain in memory (i.e. not disposed after responding to a request) for a while. IIS of Windows itself may be doing something --- I have to check this further by leaving log information to the database, etc.   If this is true, CGI approach is good enough.

How can I achieve this with Lazarus?   (on Windows 10).

« Last Edit: July 10, 2020, 01:05:56 pm by egsuh »

mangakissa

  • Hero Member
  • *****
  • Posts: 1131
Re: DB accessing application staying in memory
« Reply #1 on: July 10, 2020, 08:24:48 am »
I think Pas2JS can help you with that.
Lazarus 2.06 (64b) / FPC 3.0.4 / Windows 10
stucked on Delphi 10.3.1

PascalDragon

  • Hero Member
  • *****
  • Posts: 5446
  • Compiler Developer
Re: DB accessing application staying in memory
« Reply #2 on: July 10, 2020, 09:10:43 am »
FastCGI approach (or ISAPI/NSAPI of Delphi) is supposed to be a solution, as the application remains in the memory and responds to requests. But currently Free Pascal's fCGI is not perfect (which Michael Van Canneyt adimitted --- I have no intention to blame him for any reason. I always appreciate his contributions.)

I'm looking for various approaches. I think one possibility is to separate a database server from web-server on the same machine (and maybe later have to separate).

Instead of using CGI you could also use FPC's embedded webserver (fpHttpServer). If you have other web services running you could set up a proxy in your main web server. Alternatively you can use the new fpHttpSys (available from 3.2 on, though due to a bug in the compiler it will only work on Win64) which uses Windows' HTTP API (the same that IIS uses).

Sometimes I wonder that CGI modules remain in memory (i.e. not disposed after responding to a request) for a while. IIS of Windows itself may be doing something --- I have to check this further by leaving log information to the database, etc.   If this is true, CGI approach is good enough.

CGI applications are written as one request, one response. And IIS uses normal CreateProcess functions to load them, so IIS can't do anything special here.

egsuh

  • Hero Member
  • *****
  • Posts: 1273
Re: DB accessing application staying in memory
« Reply #3 on: July 10, 2020, 09:52:49 am »
Quote
FPC's embedded webserver (fpHttpServer).

Can I write full-fledged web server with this? Well, the scale would not so large, but my site sends questions to the browsers, and receive responses from the browsers, and save them.

Quote
CGI applications are written as one request, one response. And IIS uses normal CreateProcess functions to load them, so IIS can't do anything special here.

You are right. I checked, and just found that web modules are not destroyed after response (if set wkPooled even in CGI).  But the web modules are created at every request. Setting as wkOneShot destroys every web module after response.

devEric69

  • Hero Member
  • *****
  • Posts: 648
Re: DB accessing application staying in memory
« Reply #4 on: July 10, 2020, 10:17:42 am »
(My two cents)

In this problem, there are several layers, already. So, i don't think that adding an extra javascript layer (i don't discuss the possibilities of Pas2JS) will help to better understand the problem (nb: i'm still only using simple CGI technlology).

AFAIK, a CGI - fast or not - application is a console application that creates or recreates a web session (depending of its DOS\Bash PATH_INFO parameters); it basically creates or reload an array "$PASCAL_SESSION", and then creates a SQL "child" session of this "parent" web session.

So, already, depending on the database server you are using, there are parameters to manage the parameters of a SQL "child" session a.k.a. a SQL connection a.k.a. a MySQL thread.

❶ About the SQL server (which one do you use? I'm going to assume it's MysQL\MariaDB):
- here, there is an "axis of approach" to the same problem (CGI web server applications being also console applications): https://forum.lazarus.freepascal.org/index.php/topic,49600.msg359993.html#msg359993.
- there are hints here: https://dev.mysql.com/doc/refman/5.7/en/gone-away.html ("...The number of seconds the server waits for activity on a noninteractive connection before closing it...;...On thread startup, the session wait_timeout value is initialized from the global wait_timeout value or from the global interactive_timeout value...").
- ...

❷ concerning fast over single CGI:
Just like you wrote it, i think that i've understood (honestly i didn't read the documentation; and more importantly: I have neither read the Object Pascal code, nor tested it in the same directory as my project) that fast-CGI leaves the CGI program loaded in memory, in a kind of listening mode of the change in value of the PATH_INFO environment variable. If this is the case, I would look first and foremost, at how to raise the execution priority of the loaded fast-CGI program.

Quote
@PascalDragon wrote:
And IIS uses normal CreateProcess functions to load them, so IIS can't do anything special here.

I think this information means that there may be nothing to do on the priority side when loading fast-CGI-application with IIS (did I understand that right? But what about an already loaded fast-CGI-application?).


❸ Then, as far as logging is concerned, LCLProc.DebuglnThreadLog is enough, for me, and for now, to understand my CGI application.

« Last Edit: July 10, 2020, 10:41:34 am by devEric69 »
use: Linux 64 bits (Ubuntu 20.04 LTS).
Lazarus version: 2.0.4 (svn revision: 62502M) compiled with fpc 3.0.4 - fpDebug \ Dwarf3.

egsuh

  • Hero Member
  • *****
  • Posts: 1273
Re: DB accessing application staying in memory
« Reply #5 on: July 10, 2020, 11:26:37 am »
@devEric69

Thank you for your comments. My systems are : 
  -  Windows 10, 
  -  not console application ...  web server running on the IIS.   Everything is the same in both CGI and FastCGI, except one character   use fpCGI or fpFCGI in the "uses" clause of main program (.lpr file).

  -  DB: Firebird

Here, I'm not talking about keeping connection to the database itself. I have a TDataModule (or TFPWebModule) which contains TIBDatabase, transaction component, and some functions (like function User_Password(uid: string): string;  etc.) doing operations on the database or returning values from the database. I'd like to leave this TDataModule stay in memory always.

In CGI, this module is created and destroyed at every request, which means the TIBDataBase component on the TDataModule must re-establish connection to the database every time. I'd like to avoid this.

In FastCGI, web server program stays in the memory. In this case, the TIBDatabase component stays connected so that it doesn't reconnect at next request.

I do not know quite well, but AFAIK if a new request comes in while the pre-existing FastCGI module is processing the previous request, it creates a new process? instance? thread? (I guess new instance) of the module --- and another connection to the database.  If the first request was processed but second one is still being processed when third request comes in, then the first instance will process the third request and no more connection is necessary.

So far so good. But problem is that the fastCGI part of Free Pascal has some bug, and Michael do not have any plan to solve this right now. And I'm experiencing unexpected errors from the web-server program.

So I'm looking for other approaches that will leave DB-connection alive.

"Logging" is not issue here. I myself logged some processing just to check the creation / destruction of the datamodules and webmodules.

egsuh

  • Hero Member
  • *****
  • Posts: 1273
Re: DB accessing application staying in memory
« Reply #6 on: July 10, 2020, 12:05:33 pm »
Suddenly I feel that the problem may lie in the connection to the database itself.

PascalDragon

  • Hero Member
  • *****
  • Posts: 5446
  • Compiler Developer
Re: DB accessing application staying in memory
« Reply #7 on: July 10, 2020, 12:06:52 pm »
Quote
FPC's embedded webserver (fpHttpServer).

Can I write full-fledged web server with this? Well, the scale would not so large, but my site sends questions to the browsers, and receive responses from the browsers, and save them.

Depends on what you consider as a "full-fledged web server". It definitely allows you to serve fpWeb modules and files with both HTTP and HTTPS.

Quote
@PascalDragon wrote:
And IIS uses normal CreateProcess functions to load them, so IIS can't do anything special here.

I think this information means that there may be nothing to do on the priority side when loading fast-CGI-application with IIS (did I understand that right? But what about an already loaded fast-CGI-application?).

I was talking about a CGI application, not a FastCGI application. The latter should stay loaded while the former will always be loaded anew.

devEric69

  • Hero Member
  • *****
  • Posts: 648
Re: DB accessing application staying in memory
« Reply #8 on: July 10, 2020, 12:22:25 pm »
Quote
Everything is the same in both CGI and FastCGI, except one character   use fpCGI or fpFCGI in the "uses" clause of main program (.lpr file).

Okay, thanks for the info.

Quote
-  DB: Firebird

Good: Firebird has TransIsolation capabilities (so, it can isolate a cnx from the changes made by the others, a.k.a. multi-threading capabilities).

Quote
In CGI, this module is created and destroyed at every request, which means the TIBDataBase component on the TDataModule must re-establish connection to the database every time. I'd like to avoid this.
In FastCGI, web server program stays in the memory. In this case, the TIBDatabase component stays connected so that it doesn't reconnect at next request.

This is also what I seemed to understand from FastCGI (in diagonal reading).

Quote
I do not know quite well, but AFAIK if a new request comes in while the pre-existing FastCGI module is processing the previous request, it creates a new process? instance? thread? (I guess new instance)

I don't know either. But I would have said a new process fork parallel of the module.

Quote
So far so good.

For you. Honestly, for me, it's already too fuzzy.

Quote
But problem is that the fastCGI part of Free Pascal has some bug, and Michael do not have any plan to solve this right now. And I'm experiencing unexpected errors from the web-server program.

I don't have the Michael 's level in programming, but without a bug report with a detailed description of the context at best, or at worst a defensive [try...except...end] that succeeded at the most precise "some(s) bug(s)", i wouldn't know what to do neither.

Quote
So I'm looking for other approaches that will leave DB-connection alive.

That would fix a detected bug(s) found elsewhere ^^???

Quote
I was talking about a CGI application, not a FastCGI application. The latter should stay loaded while the former will always be loaded a new.

So, the former latter (FastCGI ) can have its post-loaded execution priority changed. No?

Quote
Suddenly I feel that the problem may lie in the connection to the database itself.

Possibly. As others have shown with mySQL, there are parameters like "the number of seconds the server waits for activity on a noninteractive connection before closing it".
« Last Edit: July 10, 2020, 02:09:18 pm by devEric69 »
use: Linux 64 bits (Ubuntu 20.04 LTS).
Lazarus version: 2.0.4 (svn revision: 62502M) compiled with fpc 3.0.4 - fpDebug \ Dwarf3.

egsuh

  • Hero Member
  • *****
  • Posts: 1273
Re: DB accessing application staying in memory
« Reply #9 on: July 10, 2020, 01:05:43 pm »
Quote
Depends on what you consider as a "full-fledged web server". It definitely allows you to serve fpWeb modules and files with both HTTP and HTTPS.

fpHTTPServer seems to deserve a consideration.

Quote
but without a bug report with a detailed description of the context at best, or at worst a defensive [try...except...end] that succeeded at the most precise "some(s) bug(s)", i wouldn't know what to do neither.

Michael knows the nature of error exactly, and he recommends to use wkOneShot even in FCGI, in which case there are not much gain from CGI (well, still the program seems to remain in the memory. Only the web module is destroyed.) He is not sure when he can look into this issue. So, actually we cannot do anything^^ 

Thank you for all your comments. We cannot expect a fixed answer for this issue, so I'll tag it as CLOSED.

PascalDragon

  • Hero Member
  • *****
  • Posts: 5446
  • Compiler Developer
Re: DB accessing application staying in memory
« Reply #10 on: July 10, 2020, 01:38:35 pm »
Quote
I was talking about a CGI application, not a FastCGI application. The latter should stay loaded while the former will always be loaded a new.

So, the former can have its post-loaded execution priority changed. No?

I have not yet worked with FastCGI, so I don't know what options the usual web servers (IIS, Apache) provide here.

Quote
but without a bug report with a detailed description of the context at best, or at worst a defensive [try...except...end] that succeeded at the most precise "some(s) bug(s)", i wouldn't know what to do neither.

Michael knows the nature of error exactly, and he recommends to use wkOneShot even in FCGI, in which case there are not much gain from CGI (well, still the program seems to remain in the memory. Only the web module is destroyed.) He is not sure when he can look into this issue. So, actually we cannot do anything^^ 

Did you write with him on the mailing list or the bug tracker? Just so I know where I could look if I want to give fixing it a try.

devEric69

  • Hero Member
  • *****
  • Posts: 648
Re: [CLOSED] DB accessing application staying in memory
« Reply #11 on: July 10, 2020, 02:26:40 pm »
Quote
So, the former latter (FastCGI ) can have its post-loaded execution priority changed. No?

I think so, at "2/3". And even if FastCGI is inoperable (if i understood correctly), loading, unloading, abnormal subsequent reloading from scratch, ... of each different  TWebModule instance in memory will be accelerated (while waiting for one of each type of instance of TWebModule, to be always loaded in memory, with an operationnal FastCGI. If that's what FastCGI technology is consisting of...).
« Last Edit: July 10, 2020, 08:39:05 pm by devEric69 »
use: Linux 64 bits (Ubuntu 20.04 LTS).
Lazarus version: 2.0.4 (svn revision: 62502M) compiled with fpc 3.0.4 - fpDebug \ Dwarf3.

egsuh

  • Hero Member
  • *****
  • Posts: 1273
Re: [CLOSED] DB accessing application staying in memory
« Reply #12 on: July 10, 2020, 11:35:59 pm »
Quote
Did you write with him on the mailing list or the bug tracker? Just so I know where I could look if I want to give fixing it a try.

I could find related threads. 

http://free-pascal-lazarus.989080.n3.nabble.com/Lazarus-fcl-web-WebLaz-Duplicate-Module-Error-td4053491.html#none
https://lazarus.lazarus.freepascal.narkive.com/UNv8Lahp/fcl-web-weblaz-duplicate-module-error

In the above links, there is following expression.

Quote
I never got around to finishing it, it is stil on my todo list.


https://forum.lazarus.freepascal.org/index.php?topic=39253.0

And here as well, the solution is setting to wkOneShot.


In bug tracker, this is classified as resolved, but seems not really. 

https://bugs.freepascal.org/view.php?id=33012
« Last Edit: July 10, 2020, 11:39:10 pm by egsuh »

devEric69

  • Hero Member
  • *****
  • Posts: 648
Re: [CLOSED] DB accessing application staying in memory
« Reply #13 on: July 11, 2020, 09:43:08 am »
For information, i've found this exchange:
http://free-pascal-lazarus.989080.n3.nabble.com/Lazarus-fcl-web-WebLaz-Duplicate-Module-Error-td4053491.html.

I went to https://bugs.freepascal.org, and i've researched the following keywords in the bugs list: webmodule, pooled ( subsequent to Dec 2017). But nothing appears.

So, i think that the bug-development_to_do_reminder(???) of the memory management of fpWeb webmodules, in FCGI mode with wkPooled, may not be completely finalized ==> I'll create it, mentioning this discussion thread.

It would be a pity, because if you have a very big server that wants and can create all the content to be broadcasted in parallel on the internet, with a lot of web clients that have very small machines and a low internet speed, fastCGI seems to be made for the former to meet the laters ^^.

(Btw, afaik, in India, they're very strong for this kind of development. In fact, they have a reputation for knowing how to create solutions that are more optimized for their market than those of the "FAAMG"tastics).

==> The request for clarification has been created here: https://bugs.freepascal.org/view.php?id=37329.
There's another thread related to this one here: https://forum.lazarus.freepascal.org/index.php/topic,50557.msg370075.html#msg370075.
« Last Edit: July 18, 2020, 02:07:48 pm by devEric69 »
use: Linux 64 bits (Ubuntu 20.04 LTS).
Lazarus version: 2.0.4 (svn revision: 62502M) compiled with fpc 3.0.4 - fpDebug \ Dwarf3.

egsuh

  • Hero Member
  • *****
  • Posts: 1273
Re: [CLOSED] DB accessing application staying in memory
« Reply #14 on: July 11, 2020, 01:12:14 pm »
 @devEdic69

Thank you very much for your posting. I don't know how to do.
Well, for now, I do not have serious problems with CGI mode. Or I can find some way to save loading time --- at least connecting to database at every request^^.   
I can wait for some time (several months? several years? Don't know). 

Actually this thread must go to the networking/web.

 

TinyPortal © 2005-2018