Re: [firebird-support] Re: Strange behavior on very large table
16 KB page size is ok and is maximal for FB1.5 to FB3. During problem time run gfix -h and show us the results and also gstat results for that tabe and indexes on it. P.S. to recreate index you do not need to set it inactive. You can activate already active index. But this operation can be done (safe) only when noone else work on that database.Also running -mend without looking about errors on the database is not good option, as you can loose same data. Regards,Karol Bieniaszewski null
[firebird-support] Re: Strange behavior on very large table
Thanks Helen. Just a few clarifications, that might help whittle this down a bit... >Given that this table is "temporary" storage, one supposes that you are deleting rows from it regularly. Do you happen to be deleting 900,000 rows each day before you load up the latest batch of 900,000? Yes, that is correct. Each morning it does a "kill & fill" on this table. All 900,000 rows are deleted, then a new set of that data is loaded from a CSV file via an external application. This happens about 2 hours after the system has completed its backup procedures. >If you're not doing any particular housework on it (restoring from backup and/or resetting the indexes periodically), then it would be normal to expect degrading performance until the next time that housekeeping is done. For about the last 6 months we have put the database through a regular gfix mend process once a week, following with a backup of the database and then a restore. This doesn't seem to have changed the behavior, however. The behavior is quite unpredictable. It seems that any attempt to access the table after the Kill & Fill has been done, or within a few hours of it, sends the server off into a 100% CPU processor load for hours. But if this is done 6 hours after the kill & fill, then it seems to work just fine. I have also seen the same issues if I attempt to access that table after a database server is rebooted. Not sure if that makes any difference here at all, or signifies anything else that could be going on? Myles
Re: [firebird-support] Strange behavior on very large table
Myles Wakeham wrote: > The issue is with one very large table that contains about 900,000 > rows. This table is used as a temporary stora ge of data that is > loaded every 24 hours from a CSV file, via an external program. The > loading takes about an hour to run, but works reliably. We are not > seeing this process changing in terms of time when it is run. > What we are seeing is that for a period of about 3-4 hours after > the morning data load is completed (about 4AM our time), any attempt > to use that table seems to be triggering some very long loading or > reindexing process. Given that this table is "temporary" storage, one supposes that you are deleting rows from it regularly. Do you happen to be deleting 900,000 rows each day before you load up the latest batch of 900,000? With Classic, you have cooperative garbage collection, which means the first transaction that selects on that table following the completion of the bulk delete will get hit with GC of all those old back versions. > This appears on doing certain queries that > involve indexes, or to re-create an index. It would be hard to say whether the dog is wagging its tail or the tail is wagging the dog. You don't exactly say how this table ebbs and flows but bulk inserts of course will cause the indexes to be updated, along with clearing out the junk in the indexes that was created by deletes. Indexes are not recreated; nor are they created until the engine is instructed to do so. Indexes are *rebuilt* when a database is restored from backup and also by ALTER INDEX INACTIVE followed by ALTER INDEX ACTIVE which you are probably doing regularly if it's true that this table is constantly being subjected to bulk deletes and inserts. The engine doesn't otherwise mess with indexes. If you're not doing any particular housework on it (restoring from backup and/or resetting the indexes periodically), then it would be normal to expect degrading performance until the next time that housekeeping is done. > It seems to show some > sort of caching between RAM and disk going on. I have set the sort > size in firebird.conf from the default of 500mb to about 5GB to > handle this, but that does not seem to have made a difference. On Classic, there's no point in this. If anything, it will make it worse if you have more than a handful of concurrent users. The sort files on a big table are going to go to disk regardless, one way or another. The engine can't split a sort file so that part of it is in RAM and part in TempDirectories. The operating system might decide to page out some RAM to disk - I seem to recall that's possible on some systems with older Fb versions but I can't swear to it. > All other tables are working just fine. It is only this one table > and this problem only has appeared in the past 3 weeks or so.& > nbsp; Prior to that, it ran without issue. This suggests to me that > it has something to do with us reaching some critical volume (as the > row count has increased at about 20% per year and continues to). There's nothing like that and 900K rows is not an excessive size. Rebuilding the indexes on that table would be an easy thing to try during some time when the table isn't in use. It would be worth considering how long it has been since the last backup-and-restore cycle, too. Cheers, Helen --- This email has been checked for viruses by AVG. https://www.avg.com
[firebird-support] Re: Strange behavior on very large table
Thank you for your comment. Very helpful. I ran gstat on the database, and on the table in question. It is the default 16K page size. Are you saying that a larger page size would be more appropriate? If so, how large would you suggest? And is it correct that the only way to change the page size would be to backup the database and then restore it with a page option to change out the size?
ODP: [firebird-support] Strange behavior on very large table
Hi. Run gstat and look at info about this table. Look also at page size of your database. It can be small in 1.5 version, if yes, change it to bigger value especially 16K. PS. 900,000 rows is quite small table also for Firebird 1.5 regards, Karol Bieniaszewski
[firebird-support] Re: UDFs for Firebird 1.5 on CentOS 7 64 Bit
Thanks for your reply. The problem isn't that what you are saying *should* be done. The problem is that this database has over 200 tables, and 1200 stored procedures and the process to do it is long (ie. over a month of work likely). I did manage to get a solution here by converting all stored procedures away from rfunc library over to FreeAdhocUDF library and finding similar UDF function and using that. This seems to work with later CentOS 7 64 bit environments, and this has become a stop-gap solution. But clearly you are correct in that this needs to be upgraded. It is really just a matter of timing and choosing the right platform to move it to. I also have to deal with client applications and libraries since the bulk of the client apps that connect to the database are PHP and there are issues with what client libraries for what versions work with what PHP versions. The whole stack has to be upgraded at the same time, making this process even more burdensome. Myles
[firebird-support] Strange behavior on very large table
I have a client with a Firebird 1.5 Classic server that is in the process of being upgraded to a later version. The database is quite complex and this upgrade will take some time. However the database has been working well for the past 8 years, and just in the past few weeks we have seen something unusual. The database server is runnnig on CentOS Linux, and has 16GB of RAM. The disk capacity is at about 60%, so it has plenty of space to grow. It has about 75 users, but even in peak times we see no more than about 3 active threads in the process logs on Linux. I'm not seeing this being bound by CPU at all. The system has sweep turned off, and is backed up once every 24 hours with no settings to stop sweep at the time of backup. The issue is with one very large table that contains about 900,000 rows. This table is used as a temporary storage of data that is loaded every 24 hours from a CSV file, via an external program. The loading takes about an hour to run, but works reliably. We are not seeing this process changing in terms of time when it is run. What we are seeing is that for a period of about 3-4 hours after the morning data load is completed (about 4AM our time), any attempt to use that table seems to be triggering some very long loading or reindexing process. This appears on doing certain queries that involve indexes, or to re-create an index. It seems to show some sort of caching between RAM and disk going on. I have set the sort size in firebird.conf from the default of 500mb to about 5GB to handle this, but that does not seem to have made a difference. All other tables are working just fine. It is only this one table and this problem only has appeared in the past 3 weeks or so. Prior to that, it ran without issue. This suggests to me that it has something to do with us reaching some critical volume (as the row count has increased at about 20% per year and continues to). Has anyone seen this behavior before, and was able to remedy it? Clearly this is an old and no longer supported server version, so we are migrating to FB 2.5 Classic however this will take time and I was hoping that there might be a solution we can implement as an interim measure. Thanks in advance for any suggestions. Myles
