[firebird-support] Re: Best way to delete millions of rows

Hey everyone First of all thanks for helping me, finding a solution to my problem. I was unable to reproduce the problem once i used sequential generated guid's. To do so i used the c# code from this site, if it has any interest.

Re: [firebird-support] Re: Best way to delete millions of rows

23.11.2016 9:06, Norbert Saint Georges n...@tetrasys.eu [firebird-support] wrote: > For tables of several hundred millions of recordings, if we organize > the first eight bytes in a less cahotic way, this will lighten Firebird > by making it faster? Yes, that's why sequential GUID was

[firebird-support] Re: Best way to delete millions of rows

Dimitry Sibiryakov s...@ibphoenix.com [firebird-support] a écrit : >For that Microsoft invented "sequential GUID" quite a lot time ago. > >BTW: CHAR(16) CHARACTER SET OCTETS is a better way to keep GUID in > database. For tables of several hundred millions of recordings, if we organize

Re: [firebird-support] Re: Best way to delete millions of rows

22.11.2016 21:01, 'Leyne, Sean' s...@broadviewsoftware.com [firebird-support] wrote: >> There would be a lot of advantage, for Firebird, in using this kind of guid? > > Yes, Firebird indexes use prefix compression so the leading 14 chars of the > above values > would be stored under a single

RE: [firebird-support] Re: Best way to delete millions of rows

> > @Ann > > I will run some test and see what happens if the guids are generated > > in way where the last part varies. Like this: > > 39db9ec6-178e-77b4-5d7b-d4e969b0cd98 > > 39db9ec6-178e-e4ba-54ed-92347a131663 > > 39db9ec6-178e-c95b-c709-a42e349410df > > There would be a lot of advantage, for

[firebird-support] Re: Best way to delete millions of rows

kragh.tho...@yahoo.com [firebird-support] a écrit : > @Ann > I will run some test and see what happens if the guids are generated in way > where the last part varies. Like this: > 39db9ec6-178e-77b4-5d7b-d4e969b0cd98 > 39db9ec6-178e-e4ba-54ed-92347a131663 > 39db9ec6-178e-c95b-c709-a42e349410df

[firebird-support] Re: Best way to delete millions of rows

@Vlad I tried with 1024, but with no luck - same thing happens. @Ann I will run some test and see what happens if the guids are generated in way where the last part varies. Like this: 39db9ec6-178e-77b4-5d7b-d4e969b0cd98 39db9ec6-178e-e4ba-54ed-92347a131663

[firebird-support] Re: Best way to delete millions of rows

@Vlad I tried with 1024, but with no luck - same thing happens. @Ann I will run some test and see what happens if the guids are generated in way where the last part varies. Like this: 39db9ec6-178e-77b4-5d7b-d4e969b0cd98 39db9ec6-178e-e4ba-54ed-92347a131663

[firebird-support] Re: Best way to delete millions of rows

---In firebird-support@yahoogroups.com, wrote : > Pagesize is 16384 It is OK > and pagebuffers is 256. It is *terrible* ! Set it to 1024 and look what happens > Servermode is superclassic. Not matters in this test > If I drop the primary key/index everything

Re: [firebird-support] Re: Best way to delete millions of rows

On Nov 22, 2016, at 6:56 AM, kragh.tho...@yahoo.com [firebird-support] wrote: > > Pagesize is 16384 and pagebuffers is 256. Servermode is superclassic. > If I drop the primary key/index everything works as expected. > Interesting. GUIDs produce really fat

[firebird-support] Re: Best way to delete millions of rows

Pagesize is 16384 and pagebuffers is 256. Servermode is superclassic. If I drop the primary key/index everything works as expected. //Thomas

[firebird-support] Re: Best way to delete millions of rows

---In firebird-support@yahoogroups.com, wrote : > I have finally had some more time to look into the problem I have been > having, and have isolated and > reproduced the problem. The cause of the problem seams to be a Varchar(36) ID > column containing a > guid/uuid.

Re: [firebird-support] Re: Best way to delete millions of rows

Hi,   i agree with you   i suppose that time spend by GC should be measured and if it take to much time then stop GC and try again leter. E.g. when i do delete from milions_table and then do SELECT COUNT(*) form milions_table it should not clear whole garbages and stop query until GC finished.

[firebird-support] Re: Best way to delete millions of rows

Hey I know that this will trigger garbage collection. However i don't believe this is expected behavior, when a action from one user crashes the entire server and causes the server to be unresponsive for 1-5 minutes while GC is running, even users on other databases on the same server is

[firebird-support] Re: Best way to delete millions of rows

This is not a bug, this is expected behaviour : sweep/garbage collection occures.

[firebird-support] Re: Best way to delete millions of rows

Hey I have finally had some more time to look into the problem I have been having, and have isolated and reproduced the problem. The cause of the problem seams to be a Varchar(36) ID column containing a guid/uuid. Steps to reproduce: 1) Create a table(TEST) with a single varchar(36) column

[firebird-support] Re: Best way to delete millions of rows

Thanks for your input. What file system should be used instead? As i understood our sysadmin, the file system was mounted with barriers off. And as i wrote last night, the problem is also present on windows virtual servers.

[firebird-support] Re: Best way to delete millions of rows

Hi! Firebird does not like ext4. http://www.firebirdnews.org/forced-writes-performance-impact-on-ubuntu/ http://www.firebirdnews.org/forced-writes-performance-impact-on-ubuntu/ http://www.firebirdnews.org/understanding-barrier-on-linux/

[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

[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

[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

[firebird-support] Re: Best way to delete millions of rows

Hey Vlad If i only count the deleted rows, the problem is the same, and the server hangs while performing garbage collection. 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

[firebird-support] Re: Best way to delete millions of rows

Hey Thanks for your input. What Karol suggested worked - thanks I will have a look at fb3 to see if it suffers from the same "hang" behavior when massive garbage collection is executed.

Re: [firebird-support] Re: Best way to delete millions of rows

---In firebird-support@yahoogroups.com, wrote : >> Here you delete some records, but then count whole table. >>Add same "where" condition as in "count" query > > I think the execution of select count is for garbage collection. Sure > If count of records that does not exists is

[firebird-support] Re: Best way to delete millions of rows

Hey Svein I don't really buy your premise about your car. No DBMS should stop process statements on one database as a result of statements running on another database on the same server. You are talking about a slowdown in "traffic" what i am talking about is a complete standstill on the

Re: [firebird-support] Re: Best way to delete millions of rows

> > Here you delete some records, but then count whole table. > Add same "where" condition as in "count" query > > Regards, > Vlad I think the execution of select count is for garbage collection. If count of records that does not exists is executed, will be the garbage collection executed for

[firebird-support] Re: Best way to delete millions of rows

---In firebird-support@yahoogroups.com, wrote : Hey I have a database that needs some cleanup, in some tables i need to delete millions of rows. I performed the cleanup on a copy of the production database in a development environment, using: delete from table where