Hi Helen,

Thanks for your response.

I will do a sweep tonight (when DB writes are minimal) on the DB to see if this keeps the DB operational more than a couple of days.

In regards to reporting what messages are appearing, I'll try and take more note. When the Web-Application was trying to access the DB, it hangs for a few minutes (perhaps 5-10) and then comes back with a timeout error. I will also use the IBEasy tool and see what error message that comes up with, I'll wait longer than 5 mins ...

Thank you I have the gfix utility

Really, your firebird.conf has all the defaults and nothing there is extraordinary. While it might be a good idea to increase the page size from 4KB to 8KB, I don't see the page size, nor the configured db cache size, nor the MaxFileSystemCache. Yesterday you ignored most of what I spent quite a lot of time writing...and so far you are not telling us anything useful about your problem.

page size is in gstat -h info ie 4096 (sent yesterday), the MaxFileSystemCache is commented out in the firebird.conf file (sent yesterday) and configured db cache size I would imagine it's the default size ie no values changed. Sorry that you feel I ignored what you said, but that was not the intention, I value your expertise and as you can tell, I'm trying to figure out why after the DB reaches a certain size around 4GB (4mths in this case) it all of a sudden fails. ie queries are not executed, and connectivity to the DB fails. ie IBEasy comes up with unavailable database errors. The system sets itself straight only after a reboot but fails after a few days. I'm trying to supply whatever info you ask for, if I know where to find it. As the system was rebooted yesterday, it is functioning OK for the time being until a few more days ...

Nevertheless, it will be interesting to see how long the DB holds after I do a sweep tonight. If the sweep was the problem, then it should be stable for at least 1mth ????

Thanks to all! Back to the drawing board ...

Regards,

Theodore

----- Original Message -----
From: Helen Borrie
To: firebird-support@yahoogroups.com
Sent: Thursday, November 19, 2009 12:29 PM
Subject: Re: [firebird-support] Re: My Firebird fails on Large database size, only way to fix is by reboot



At 10:19 AM 19/11/2009, you wrote:

>Please pardon my ignorance Eduardo, I never do any maintenance on the DB.

That is the most likely cause of your current problems. However, you still have not reported what messages you have received at the times when you could not connect to your database. Because of that, nobody can really advise you about what to do.

>Could you knidly advise me how to do a sweep

You need the gfix tools. Assuming Windows, you will find gfix.exe in the \bin directory of the Firebird installation.

Docs here:
http://www.firebirdsql.org/manual/gfix.html
(or you can go to the Doc Index and download the gfix manual as a PDF)

Do a sweep only at this stage and perhaps a validation. Don't try to do a mend unless you see error messages indicating damage.

>I think I can do the backup/restore and know how to configure pagesize through firebird.conf

You cannot configure page size through firebird.conf. You can do it as a parameter of restore. When you restore, do NOT use the -replace_database option. Restore to a database file of a different name, connect to it with your favourite query tool and do some selects. If you are happy with it, then

1. Put the old database offline (disconnect all connections).
2. Rename the old database.
3. Move the restored database to the right location for production.
4. Rename the restored database to the correct name for production.

>If you see anything else odd in firebird.conf please advise me so I can adjust the parameters.

Really, your firebird.conf has all the defaults and nothing there is extraordinary. While it might be a good idea to increase the page size from 4KB to 8KB, I don't see the page size, nor the configured db cache size, nor the MaxFileSystemCache. Yesterday you ignored most of what I spent quite a lot of time writing...and so far you are not telling us anything useful about your problem.

Your gstat -h stats told us that the system had recently done more than one garbage collection and that the last effective GC probably cleared out garbage from about 10,000 transactions. That's good. If there were a lot records represented in that garbage, then your system has probably been trying to clear that out for quite some time and your application code will continue to accumulate more mismanaged record versions. Keep an eye on that garbage. You can get an estimate of how much garbage is hanging around by calculating the gap between the Oldest Snapshot and the Oldest Transaction.

If you really don't understand how to do good transaction management in your client code then you *will* need to fall back on housekeeping to deal with the resulting garbage. I would suggest getting all connections off-line once a week and doing a sweep. Then, once a month (at worst), you should do a backup. As long as you are doing the weekly sweep, you could make your backups with the -g switch (which will suppress the gbak -b normal behaviour of performing garbage collection).

I suggest also that you configure GCPolicy to do 'background' garbage collection. Currently you have it at the default 'mixed', which means that every select on a table is kicking off GC on that table. If it happens to be a table that gets frequent bulk deletes or updates, co-operative GC could cause quite a slowdown.

Don't forget to --
-- remove the # symbol from the parameter when you edit it
-- save firebird.conf and make sure you saved firebird.conf and not firebird.conf.txt
-- stop and restart the Firebird service after this reconfiguration.

./heLen






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



No virus found in this incoming message.
Checked by AVG - www.avg.com
Version: 8.5.425 / Virus Database: 270.14.62/2499 - Release Date: 11/12/09 14:33:00


[Non-text portions of this message have been removed]