Sounds like this application doesn't take proper care of its transactions, you'll get 'proof' if you observe a transaction gap building when the app is running, whereas it is OK when it is not running. Make sure you commit (and not commit_retaining).

Set

-----Original Message-----
From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] On Behalf Of Olaf Kluge
Sent: 23. april 2009 13:04
To: firebird-support@yahoogroups.com
Subject: AW: [firebird-support] Problem Firebird 2.1 Superserver

Hi,

the statistics were created after the pc was restarted. The whole time, if
the one application runs, the server need every 10 seconds more memory
(RAM), but the transactions are OK.

In this time, the productions stops for a half day (every day).

After the firebird server cannot receive more memory (not more RAM and the
pagefile is full), then returns the jaybird the error message, that firebird
cannot allocate more memory.

If I close the one application, the memory-problem is eliminated, the big
memory requirements falls.

The new sql statement I will test next days.

Thank you.


I would consider changing to something like:

WITH FIRST_AUFTRAG(AUFTRAGSNR) as
(SELECT FIRST 1 AUFTRAGSNR
FROM AA_AUFTRAG_2 WHERE STATUS = 'RQ' ORDER BY AUSL_ORT)

SELECT AA2.AUFTRAGSNR, COUNT(AA2.PID) AS AnzahlPaletten,
COUNT(DISTINCT AA2.AUFTRAG_POS) AS AnzahlAuftrPos
FROM AA_AUFTRAG_2 AA2
JOIN FIRST_AUFTRAG FA ON AA2.AUFTRAGSNR = FA.AUFTRAGSNR
WHERE AA2.STATUS = 'RQ'
GROUP BY AA2.AUFTRAGSNR

But, as others have said, I don't think this in itself is the cause of your
problem and if it works OK, then it may not be worth changing. What
surprises me, is your statistics. It seems as if this WAS an active database
(almost 24 million transactions), but that no-one uses it anymore. Are you
sure that you got these statistics whilst there was a problem and not after
having stopped (or restarted) something? I suppose it could be that when
your jbird driver gives you the error message, then a lot of transactions
disappear. If so, I would expect the statistics to be more interesting
before you get the error message (you say it take a week, maybe run
statistics daily to see if a transaction gap is building up?).

Set

-----Original Message-----
From: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com] On Behalf Of Olaf Kluge
Sent: 23. april 2009 12:08
To: firebird-support@yahoogroups.com
Subject: AW: [firebird-support] Problem Firebird 2.1 Superserver

Hello,

is perhaps this query the problem?

SELECT AUFTRAGSNR, COUNT(PID) AS AnzahlPaletten,
COUNT(DISTINCT(AUFTRAG_POS)) AS AnzahlAuftrPos FROM AA_AUFTRAG_2 AA2 WHERE
STATUS = 'RQ' AND AA2.AUFTRAGSNR = (SELECT FIRST 1 AUFTRAGSNR FROM
AA_AUFTRAG_2 WHERE STATUS = 'RQ' ORDER BY AUSL_ORT) GROUP BY AUFTRAGSNR

Every 10 seconds the java applications runs it to show for new orders.

Thank you.

_________________________
I hope this helps.

Gabor

Olaf Kluge írta:

> Hello,
>
> we have a big problem with firebird 2.1.
>
> Here the gstat-statistics:
>
> Flags 0
> Checksum 12345
> Generation 23475579
> Page size 4096
> ODS version 11.1
> Oldest Trans. 23475559
> Oldest active 23475560
> Oldest snapsh. 23475560
> Next Transaktion 23475561
> Bumped Trans. 1
> Sequence No. 0
> Next attachm. ID 2063
> Implementation ID 16
> Shadow Count 0
> Page buffers 0
> Next header page 0
> Database dialect 3
> Generation date Feb 5, 2009 15:03:40
> Attributes force write
>
> Sweep interval: 20000
>
> It looks fine, but the process fbserver takes hour to hour more RAM from
> the system. From yesterday to this day increases the size from 300

Megabyte

> to 500 Megabyte. The firebird-process-size increases minutely more and

more.

>
> The pagefile increases too. The problem is, that firebird jbird driver
> returns after one week of running firebird an error message, that firebird
> cannot allocate more memory.
>
> The server:
>
> 2 Gigabyte RAM,
> P4 3000
> XP pro
>
> What can be the problem? How can I find and solve it?
>
> Thanks for helping.
>
> Best regards
>
> Olaf Kluge

------------------------------------

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Visit http://www.firebirdsql.org and click the Resources item
on the main (top) menu. Try Knowledgebase and FAQ links !

Also search the knowledgebases at http://www.ibphoenix.com

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Yahoo! Groups Links