[firebird-support] Re: Best way to delete millions of rows
> ---In firebird-support@yahoogroups.com, wrote : > > Hey Vlad > If i only count the deleted rows, the problem is the same, and > > the server hangs while performing garbage collection. Sad. Probably, table have a lot of indices ? IO subsystem, amount of RAM, page size and page cache size also matters. Also interesting if filesystem is mounted with "barrier=on" option > On the other hand, if I count the rows not deleted, no garbage collection is > performed - that makes sens as the index > on created makes sure that no delete records/rows are accessed. This is > useful to know as i can make sure that other > parts of the application never issues a query for rows older that 6 months. It still will clean up record versions left after UPDATE's > Now the garbage collection will be deferred and happen during backup. Is it > possible that the same "hang" behavior will > happen during backup as a result of the deferred garbage collection? gbak have no magic, it just reads the tables and put data into backup file. Regards, Vlad
[firebird-support] Re: Best way to delete millions of rows
Hi Thomas, only from curiosity: what is your filesystem? I had a similar issue some time ago, when we used an OpenSuse with btrfs. After switching to ext4 everything was ok. I never investigated the problem deeper, but I think it could be the snapshot feature of btrfs that has slowed down the database. But just maybe. Regards Martin
[firebird-support] On Connect triggers are dangerous, aren't they?
Hi! I am reading message Yahoo! Groups https://beta.groups.yahoo.com/neo/groups/firebird-support/conversations/messages/129876 https://beta.groups.yahoo.com/neo/groups/firebird-support/conversations/messages/129876 Yahoo! Groups https://beta.groups.yahoo.com/neo/groups/firebird-support/conversations/messages/129876 > SYSDBA users can see other connected users using monitoring tables > but is it possible to implement such feature for non-SYSDBA users? Are View on beta.groups.yahoo.com https://beta.groups.yahoo.com/neo/groups/firebird-support/conversations/messages/129876 Preview by Yahoo and I wonder is there mechanism to prevent this trigger become dangerous. E.g. if there is the slightest chance that error will be thrown in the on connect trigger then the database will be completely unavailable. When any block can be solution, but it is not 100% either. Some uneasiness is about using on connect trigger feature... J.
Re: [firebird-support] On Connect triggers are dangerous, aren't they?
01.11.2016 15:59, jonatan.laurit...@yahoo.dk [firebird-support] wrote: > I wonder is there mechanism to prevent this trigger become dangerous. E.g. if > there is the > slightest chance that error will be thrown in the on connect trigger then the > database > will be completely unavailable. Not quite so. SYSDBA can log in with DB triggers disabled and fix/deactivate them. -- WBR, SD. ++ Visit http://www.firebirdsql.org and click the Documentation item on the main (top) menu. Try FAQ and other links from the left-side menu there. Also search the knowledgebases at http://www.ibphoenix.com/resources/documents/ ++ Yahoo Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/firebird-support/ <*> Your email settings: Individual Email | Traditional <*> To change settings online go to: http://groups.yahoo.com/group/firebird-support/join (Yahoo! ID required) <*> To change settings via email: firebird-support-dig...@yahoogroups.com firebird-support-fullfeatu...@yahoogroups.com <*> To unsubscribe from this group, send an email to: firebird-support-unsubscr...@yahoogroups.com <*> Your use of Yahoo Groups is subject to: https://info.yahoo.com/legal/us/yahoo/utos/terms/
RE: [firebird-support] On Connect triggers are dangerous, aren't they?
<> gbak -nodbtriggers - Suppresses Database Triggers from running [Firebird 2.1] Regards Paul Beach
[firebird-support] Re: Best way to delete millions of rows
Hey Vlad The table has 4 index, one from a primary key, another from a foreign key, and 2 on other columns. The system is a virtual machine with 8 Gb of ram, page size is 16K and page buffers 512. The IO sub system is a Dell DAS, with 16 SAS 15K disks in raid 10. Our sysadmin ran a test on the disk, and the result was 7500 IOPS. The filesystem is EXT4 mounted without barrier, and using LVM volumes. I hope this is meaningful to you, if not, I will ask the sysadmin again. I was able to reproduce the problem on our production server witch is a Virtual windows server with 16 cores and 128 Gb of Ram. The IO system is a fiber channel SAN, no more than 6 months old. I have also reproduced the problem on a pre prod environment(Virtual windows server). However i was unable to reproduce the problem on my laptop. Could this be a issue with running Firebird on virtual servers? The production database server was moved to a virtual server about 6 months ago, and our tests showed a 30-50% performance increase on all our bulk inserts/updates and general program/website performance. However we did not test how deletes performed. Regarding gbak, I was under the impression that gbak would perform garbage collection during backup? Thanks for your help so far.
