Recent

Author Topic: Running database access as Windows service  (Read 909 times)

egsuh

  • Hero Member
  • *****
  • Posts: 1373
Running database access as Windows service
« on: May 28, 2024, 10:54:44 am »
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

  • Hero Member
  • *****
  • Posts: 6268
Re: Running database access as Windows service
« Reply #1 on: May 28, 2024, 11:02:22 am »
What kind of data are transferrable? Only integer, string, etc., or any streams?
Isn't that something only you can answer  :P

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

  • Hero Member
  • *****
  • Posts: 1373
Re: Running database access as Windows service
« Reply #2 on: May 28, 2024, 11:11:00 am »
Quote
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.

Quote
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

  • Hero Member
  • *****
  • Posts: 1373
Re: Running database access as Windows service
« Reply #3 on: May 28, 2024, 11:16:55 am »
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

  • Hero Member
  • *****
  • Posts: 14820
  • Censorship about opinions does not belong here.
Re: Running database access as Windows service
« Reply #4 on: May 28, 2024, 11:18:11 am »
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
« Last Edit: May 28, 2024, 11:21:04 am by Thaddy »
bitrate is always calculated like this:sample rate * bitdepth * number of channels.

rvk

  • Hero Member
  • *****
  • Posts: 6268
Re: Running database access as Windows service
« Reply #5 on: May 28, 2024, 11:18:33 am »
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.

Quote
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.
« Last Edit: May 28, 2024, 11:21:54 am by rvk »

Thaddy

  • Hero Member
  • *****
  • Posts: 14820
  • Censorship about opinions does not belong here.
Re: Running database access as Windows service
« Reply #6 on: May 28, 2024, 11:27:45 am »
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 )
« Last Edit: May 28, 2024, 11:39:01 am by Thaddy »
bitrate is always calculated like this:sample rate * bitdepth * number of channels.

egsuh

  • Hero Member
  • *****
  • Posts: 1373
Re: Running database access as Windows service
« Reply #7 on: May 28, 2024, 11:44:41 am »
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

  • Hero Member
  • *****
  • Posts: 6268
Re: Running database access as Windows service
« Reply #8 on: May 28, 2024, 11:54:08 am »
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

  • Hero Member
  • *****
  • Posts: 1373
Re: Running database access as Windows service
« Reply #9 on: May 28, 2024, 12:41:27 pm »
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

  • Hero Member
  • *****
  • Posts: 14820
  • Censorship about opinions does not belong here.
Re: Running database access as Windows service
« Reply #10 on: May 28, 2024, 12:56:54 pm »
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.
« Last Edit: May 28, 2024, 12:59:03 pm by Thaddy »
bitrate is always calculated like this:sample rate * bitdepth * number of channels.

egsuh

  • Hero Member
  • *****
  • Posts: 1373
Re: Running database access as Windows service
« Reply #11 on: May 28, 2024, 01:07:22 pm »
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.
« Last Edit: May 28, 2024, 01:09:55 pm by egsuh »

 

TinyPortal © 2005-2018