Recent

Author Topic: FastCGI - Database connection  (Read 892 times)

egsuh

  • Sr. Member
  • ****
  • Posts: 465
FastCGI - Database connection
« on: July 13, 2020, 07:17:35 am »
This is related issue with  my previous topic ..

https://forum.lazarus.freepascal.org/index.php/topic,50513.0.html

To summarize:

I've developed a web server program both in CGI and FastCGI. The programs are all the same, except just one character in the uses clause of main program(.dpr), that uses fpCGI or uses fpFCGI.

In fpFCGI, memory-related problem of web module at wkPooled mode.

So, Now I'm testing followings.

- I have created a datamodule, which just have TIBConnection and TSQLTransaction.
- A webmodule is in wkOneShot mode. This webmodule is created at each request, and they destroyed after response. Data related components are located here, with their database is the TIBConnection of the previous data module.

- The DataModule, which contains TIBConnection, is created at main program file. That is,

Code: Pascal  [Select][+][-]
  1. program FCGI_Webserver;
  2. uses....;
  3.  
  4. begin
  5.      dm1 := TDataModule1.Create (Application);
  6.  
  7.      Application.Initialize;
  8.      Application.Run;
  9. end;  
  10.  

And I put a preparatory step in the web-module.

Code: Pascal  [Select][+][-]
  1. procedure TWebModule1.DataModuleCreate(Sender: TObject);
  2. begin
  3.     inherited;
  4.  
  5.     if not dm1.IBConnection1.Connected then dm1.IBConnection1.Connected := True;
  6.  
  7.     SQLQuery1.Database := dm1.IBConnection1;
  8.     // .... The same assignments for all DB-related components
  9. end;  

But still there happen errors not regularly. I think there is some problem at the database connection. 

What would be possible reasons (and remedy)?

Leledumbo

  • Hero Member
  • *****
  • Posts: 8273
  • Programming + Glam Metal + Tae Kwon Do = Me
Re: FastCGI - Database connection
« Reply #1 on: July 16, 2020, 11:40:51 am »
But still there happen errors not regularly.
Describe this first. "errors" is too broad.

egsuh

  • Sr. Member
  • ****
  • Posts: 465
Re: FastCGI - Database connection
« Reply #2 on: July 18, 2020, 07:50:04 am »
Quote
Describe this first. "errors" is too broad.

Yes, too broad.. but I cannot specify it.  In many cases it happens at starting transaction (when the code line number is traced), and infrequently just Access Violations (not traced).

In CGI, all operate well.

GDean

  • New member
  • *
  • Posts: 5
Re: FastCGI - Database connection
« Reply #3 on: July 18, 2020, 08:56:03 am »
cgi is single threaded perhaps fastcgi is not.


devEric69

  • Sr. Member
  • ****
  • Posts: 358
Re: FastCGI - Database connection
« Reply #4 on: July 18, 2020, 10:44:14 am »
Quote
Describe this first. "errors" is too broad.

I think so, too. The "Connected:= True" code should be in a "Try...Except..End;" frame, in order to log the connection settings used.

Quote
cgi is single threaded perhaps fastcgi is not.

Perhaps.
A multi-threaded database application - for 1 web user - means that the web connection (of the said user) can have several SQL-database-sessions-connections (the so called multi-threaded side; but without TThread ;) ).

I'm doing a quick analysis: if fastCGI (over CGI which is "wkOneShot") is indeed a multi-threaded technology (said "wkSession", over "wkPooled"), in addition to being persistent in RAM ("wkPooled"), then fastCGI should also be able to manage (for the same Web user session), several SQL sessions in parallel.


Based on what modalities?

• At the T{fp}Database: this already requires that a TWebModule is always loaded in memory: creating multiple database connections of a TWebModule seems presumptuous.
Feasible nevertheless, storing the created TDatabases-connections points in a container like T{fpSessions}List. All TDatabase instances created should have (for an ease of use) their properties like KeepConnexion, DatabaseName, connection parameters (e.g. transIsolation=read-commited, ...) in an identical way (put it another way: create a T{Generic or not}List containing the sessions-connections-on_the_database, created to iterate (with Foreach a.k.a. "For..in", or simple For) the previously said TDatabases, in order to assign them the same properties KeepConnection, DatabaseName, etc, in an identical way).


• Now, before we "get down" to the TDatabase level, already, amho, just being able to configure the creation of parallel instances of the same TfpWebDatamodule - with code that would at least always leave one instance loaded in RAM, even after the web user session has ended - that could be created several times per "wkSession" (for the same web user session), would have the merit to exist.
And it would delegate the problem of code specific for such or such database to each fpWeb user.

« Last Edit: July 20, 2020, 03:59:07 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.

GDean

  • New member
  • *
  • Posts: 5
Re: FastCGI - Database connection
« Reply #5 on: July 24, 2020, 08:22:05 am »
TWebModule is always loaded in memory: creating multiple database connections of a TWebModule seems presumptuous.....

if your application only occasionally responds to multiple requests you might get away with using EnterCriticalSection and LeaveCriticalSection to limit access to non thread safe code.  The application I am currently developing is designed to handle 100's of users so has 10+ concurrent database connections (using zeoslib) each running in its own thread.  The main application (I use silvioprog brookframeworks v5) polls for next free database thread, posts the sql request, then waits for the response, processing 10,000+ requests a second.  Stress tested with 400 threads executing over 6 miilion database requests.

Your requirements should dictate the method you use.

devEric69

  • Sr. Member
  • ****
  • Posts: 358
Re: FastCGI - Database connection
« Reply #6 on: July 24, 2020, 10:38:34 am »
Whoa :o ! You're surfing on a roll.

Quote
Stress tested with 400 threads executing over 6 miilion database requests.

The stess tests depends on many factors (scenarios): the profiling, like not to miss connection's requests from the listening sockets. Complicated. I never did. Now, assuming that the missing link is that a request should not be missed so that it does not go below a threshold of accuracy of the time sliced by a TfpTimer, for my part, i remember just my little last average known data that i consider acceptable, i.e. data that i consider centered between the minimum (nothing) and the maximum tolerances allowed with a processor core. Overall, i know that basically a TfpTimer can't go down in a reliable time arrangement, under 25ms. So, if i say to myself that if i am processing 1/0.025=40 "code units" per core of a processor, then i am happy with the average performance of that core. It's worth, what it's worth :-[ .

For the rest, it is mostly a question to profile the code loops that are most frequently executed, in order to optimize the elapsed time. And it depends on the optimization of SQL queries.

Anyway, i really want to keep this in mind for a clarity purpose: to distinguish between clones, parallel SQL connections, in short the different instances of T{fp}Connection objects on the one hand (which are unfortunately often also called "threads" in database applications), and the real instances of TThreads (with a method Run, Synchronize, ...) on the other hand. It simplifies the ride of the scale analysis.

« Last Edit: July 24, 2020, 01:00:45 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.

GDean

  • New member
  • *
  • Posts: 5
Re: FastCGI - Database connection
« Reply #7 on: July 24, 2020, 03:24:25 pm »
Whoa :o ! You're surfing on a roll.

The libraries I use are impressively fast. They are event driven so no timers needed. Framework uses Sagui library to handle tcpip traffic which is nearly as fast as nginx.... https://github.com/risoflora/brookframework

Zeoslib abstract layer (Array of IZConnection), each has its own thread. Using InterlockedIncrement and Interlockeddecrement to determine when sql requests are completed. using nginx to proxy forward http requests to local port.  All makes for a highly responsive middle tier.  Vue/quasar front end flies as a result.

Cheers.
« Last Edit: July 24, 2020, 03:26:49 pm by GDean »

devEric69

  • Sr. Member
  • ****
  • Posts: 358
Re: FastCGI - Database connection
« Reply #8 on: July 24, 2020, 04:13:25 pm »
Quote
The libraries I use are impressively fast. They are event driven so no timers needed. Framework uses Sagui library to handle tcpip traffic which is nearly as fast as nginx.... https://github.com/risoflora/brookframework

Zeoslib abstract layer (Array of IZConnection), each has its own thread. Using InterlockedIncrement and Interlockeddecrement to determine when sql requests are completed. using nginx to proxy forward http requests to local port.  All makes for a highly responsive middle tier.  Vue/quasar front end flies as a result.

All right, really, thanks for those component references. Indeed, nginx is ultra-parallelized, and Zeos calls the TThreads layer of the mysql client library.

But, the original discussion deals with the framework fpWeb ("uses fpCGI" or "uses fpFCGI", and TwebModule). Some, like me using Apache (AFAIK a server that forks itself only every x connections, in addition to knowing how to leave a CGI-bin program loaded in memory with fastCGI) + SQLdb components, or others using IIS + IBX (perhaps), are meeting the TwebModule lifetime bottleneck. We can't parallelize: we would just like to have 1 permanent connection, at least, which remains loaded in RAM... To start. Say in a different way, the problem would be that the TWebmodule of a fastCGI-bin program loaded in memory, especially the Twebmodule containing the connection, would unload itself after each request instead of remaining constantly loaded (like a listening socket).


Regards.
« Last Edit: July 24, 2020, 09:47:22 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

  • Sr. Member
  • ****
  • Posts: 465
Re: FastCGI - Database connection
« Reply #9 on: July 25, 2020, 05:48:23 am »
Well I'm using Windows 10 IIS + TSQLConnection. 

I don't think my application needs multi-threaded connection. Single connection would be enough (for now, at least).  You can wait two or three seconds until the web server respond.  Serial processing of requests by only one instance / one thread is good enough.

The issue seems not related with multiple instances (or threads). It seems something more intrinsic to fp-FCGI memory management.  The web-server error occurs with just one access to the web-server, not from many browsers. And this occurs even set Webmodule as wkOneShot (I tried this, as I thought this will save time of loading from HDD. Even this is not so important if saved on SSD). So it may not be problem of TfpWebModule. Instead, it could be related with loading the application (.exe file) itself.

After a while (from 20-30 seconds to several minutes) from error occurrence, the server works fine again. 

I can run in CGI model for now. With the developments of Hardware, the overhead of loading .exe file in CGI model, seems not so severe as in the beginning. Still FCGI model is very attractive for me, because it allows the "fastest" web server.   Problem is, I have read somewhere that the company developed FastCGI are not operating actively for a long time.

I haven't tried FCGI server without DB connection.

 

TinyPortal © 2005-2018