Running database access as Windows service

[egsuh]

I have written a web server, which is CGI way, and it accesses database (firebird). 
As you know, this approach has to connect to database at every request.
In most cases, this does not cause any problem. But when there were many requests, I found the database operation is too slow.

So I'm thinking of a daemon program that stays in memory and runs functions/procedures called from web module. This will be running as a service on Windows.

Do you think this is feasible? Web module will call the daemon/service module, and passes and receives data from it. What kind of data are transferrable? Only integer, string, etc., or any streams?

[rvk]

What kind of data are transferrable? Only integer, string, etc., or any streams?

Isn't that something only you can answer 

First I would investigate why lots of connection cause a slowdown (there could be other reasons).

But if you really want to go the daemon way... you might consider json as inbetween format. But that also kinda depends on what you are transferring in cgi to your client. What format do you use there?

[egsuh]

First I would investigate why lots of connection cause a slowdown (there could be other reasons).

You'll know that connecting to DB would take rather long time. So if many web-requests come in in a short period of time, database is slowed down or get stuck, as every request will try to make a connection. Currently I think this is the most serious cause of slow down.

But if you really want to go the daemon way... you might consider json as inbetween format. But that also kinda depends on what you are transferring in cgi to your client. What format do you use there?

Well... if I can pass string type, whether in JSON type or other format, it would suffice I guess. Simply I'm wondering whether it is possible to pass TStream as parameter. If TStream is passable then practically I can pass any object.

[egsuh]

BTW, other operations should be done anyway once. But connection, which may stay connected after the first connection, has to repeat disconnection and reconnection, which is absolutely unnecessary.

[Thaddy]

CGI is not suitable for many connections. FastCGI performs better. Reason:
CGI has an instance per connection. FastCGI not. But it also depends on your server.
So your problem is not the number of connections, but the availability of resources (memory and cores). That won't change with a service or daemon.

See: https://en.wikipedia.org/wiki/Common_Gateway_Interface

[rvk]

You'll know that connecting to DB would take rather long time.

Not if the Firebird database is installed locally on that machine.
But ok. If you think it's the cause, then one connection could work.
But... now you have one connection and multiple clients that want to retrieve data at the same time.
How are you going to handle that?
You'll need some kinda pool system and the request are handled sequentially instead of simultaneous.

But if you really want to go the daemon way... you might consider json as inbetween format. But that also kinda depends on what you are transferring in cgi to your client. What format do you use there?

Well... if I can pass string type, whether in JSON type or other format, it would suffice I guess. Simply I'm wondering whether it is possible to pass TStream as parameter. If TStream is passable then practically I can pass any object.

You can't pass a TStream because that's just a pointer. A pointer to what? You would need a separate connection for the datastream to do that.

If you need a large blob of data to be transferred, you could use base64 encoding (optionally with gzip compression).

BTW, other operations should be done anyway once. But connection, which may stay connected after the first connection, has to repeat disconnection and reconnection, which is absolutely unnecessary.

Connection/reconnection is the least of your problems (which you will find out when trying to create your daemon).

And indeed like Thaddy states... the problem might not be the multiple connections to your Firebird server but the multiple connections to your CGI (each time the CGI gets loaded etc).

BTW. I use PHP for these pusposes myself. But I haven't got much load on the server.

[Thaddy]

I am sure it is the latter. E.g. I run a small script on one of my servers that is a version of FPC itself renamed as fpc.cgi (yes, that works! just provide a simple web interface) and that chokes on more than 4 connections. With FastCGI it can handle upto 400 simultanious connections with reasonable speed. And that server is NOT high-end: 4 cores, 16GB.
(My play ground )

BTW, your idea of running it as a Daemon/service is good anyway. I always do that for production. Just make an estimate of the hardware requirements you will need.

(The reason fpc  works is that it prints to stdout and that happens to be how cgi's work too. The resulting exe can be downloaded through a temp link to my server )

[egsuh]

Well, it may look funny but I opened the taskmanager of my (Windows Server 2019) server and looked at it. There appear my CGI exe process, and disappear. I tested it my self, and with only single connection, it does not show on the taskmanager panel or just very shortly if it does.
And when there are many requests there appear four or more my app processes on the task manager. But the whole CPU share does not over 15% even in that time. The problem is, connection to database via FlameRobin is not easy when there are many my processes. It takes time, possibly more than 2-3 seconds. At other times the connection is prompt.

This does not prove connection itself takes long time, but I'd like to remove them first because they are redundant.

I can run the same web module in FastCGI mode. However FastCGI causes exceptions. I have written about it several times. Anyway I'd like to create FastCGI and try it, as writing a new daemon would take long time. 

[rvk]

But the whole CPU share does not over 15% even in that time. The problem is, connection to database via FlameRobin is not easy when there are many my processes. It takes time, possibly more than 2-3 seconds. At other times the connection is prompt.

And that still seems to be a resource problem on your server.

Connecting to a database is the least intensive operation.
Loading and unloading your CGI is much much much more intensive.
And because the resources of your server are full, the subsequent connecting via FlameRobin also gets slow.

You could probably test this really easy by moving the Firebird database to another server. Then, when there are multiple connections via your web, try to connect (from another client on your network) to your Firebird database with Flamerobin.

But... like already mentioned... creating a daemon isn't a bad idea by itself.

[egsuh]

I found the reason of "get stuck". One webpage has many concentrated accesses to database (plus internal pascal code operations --- allocating and releasing memory) to display the contents stored in the database. Processing this page is not a big problem when run alone, but this takes many seconds when there are other operations (or DB access).  I made FastCGI, but this does not help for this case.

[Thaddy]

You can not test CGI with just a single connection..... Isn't that obvious?'

What you should observe is that there is a CGI running for every single connection...
And they must be running at the same time: a CGI program exits when done.

[egsuh]

You can not test CGI with just a single connection..... Isn't that obvious?'

What you should observe is that there is a CGI running for every single connection...
And they must be running at the same time: a CGI program exits when done.

Yes, I know how CGI works. I'm saying a "single request" takes long time to process.

[egsuh]

I found the reason of my server's slowdown. It was in the procedure I wrote in Firebird.

I defined a structure of Name=value pairs. Let's say its field name is CF.

1=tiger
2=lion
3=leopard
4=elephant

et cetra.

This is stored as TStrings.Text in a BLOB filed, so that  I can retrieve directly from my pascal app.

With pre-defined paris, I can save them in one field. But there are "other" responses, which are not pre-defined in the previous pair-list.
So I save them as one record per animal, with code, and caption.

    code       caption
   100001   shark
   100002   hyena
      .... 

And I wrote a stored procedure function that retrieves all of them at once,

Code: SQL  [Select][+][-]

1.      SELECT CF FROM FixedTable WHERE (cond) INTO :CF;
2.      FOR SELECT code, caption FROM OpenTable WHERE (cond) do BEGIN
3.           :CF = :CF || code || '=' || caption || ';';   -- Delimiter is ; in the pascal TStrings definition
4.      END;
5.  
6.      suspend;   -- this returns CF as a out-field of this procedure
7.  

       

But this takes long time, as the number of records increase in the OpenTable.  Just selecting the records from OpenTable does not take much time.
So, I need to change some procedures, or have to change the structure of FixedTable to one record for each animal.

The slowdown had nothing to do with "connecting to DB".

[rvk]

Code: SQL  [Select][+][-]

1.      SELECT CF FROM FixedTable WHERE (cond) INTO :CF;
2.      FOR SELECT code, caption FROM OpenTable WHERE (cond) do BEGIN
3.           :CF = :CF || code || '=' || caption || ';';   -- Delimiter is ; in the pascal TStrings definition
4.      END;
5.  
6.      suspend;   -- this returns CF as a out-field of this procedure
7.  

       
But this takes long time, as the number of records increase in the OpenTable.  Just selecting the records from OpenTable does not take much time.
So, I need to change some procedures, or have to change the structure of FixedTable to one record for each animal.

You might want to begin investigating the PLAN for "cond" (the WHERE clause) and see which indexes are used.

BTW. You have the SUSPEND at the end of the PROCEDURE. Shouldn't you have it inside the FOR SELECT loop?
You want SUSPEND to pass the record back to the client for each record, so the client can iterate through the records.
Also see https://firebirdsql.org/refdocs/langrefupd21-psql-forselect.html

I also have the feeling this can be done much easier in just one SELECT and JOIN but can't say for sure without knowing the entire table structures.

The slowdown had nothing to do with "connecting to DB".

Good.

[egsuh]

@rvk

Thank you for your interest. "where clause" has nothing to do with any problem.

What I was trying to do is to make a single string from multiple records. If written in Pascal, this would be the same as:

Code: Pascal  [Select][+][-]

1. var
2.     cf: string;
3.     MyTStringsClass: {descendant of} TStringList;
4. begin
5.          sqlquery.sql.text := 'select cf from fixedtable where (cond)';
6.          sqlquery.Open;
7.          cf := sqlquery.fieldbyname('cf').AsString;
8.          sqlquery.close;
9.  
10.          sqlquery.sql.text := 'select code, caption from Open table where (cond)';
11.          sqlquery.open;
12.          sqlquery.first;
13.          while not sqlquery.eof do begin
14.               cf += format(';%s=%s', [sqlquery.fieldbyname('code').asstring , sqlquery.fieldbyname('caption').asstring]);
15.               sqlquery.next;
16.          end;
17.  
18.          MyTStringsClass,DelimitedText := cf;   // MyTStringsClass,Delimiter = ';'
19. end;
20.  

It's because each part --- DB structure, and MyTStringsClass's usage is best suited in these formats. So I gave up some optimization, which was not "some" but "large".

I don't think this process (combining many strings into one string) would take much time if run in Lazarus, but it took long (more than 10 seconds with about 3-400 rows in Opentable) when run in Firebird SQL, which slowed down web server. So I changed my algorithm and have no problem now.

BTW. You have the SUSPEND at the end of the PROCEDURE. Shouldn't you have it inside the FOR SELECT loop?

As I explained, the whole procedure returns only single row.