[firebird-support] Re: High write access on disk

Hey Dimitry Thanks for the answer. Just to be sure, /tmp should be mounted with tmpfs, like in this archicle? https://linoxide.com/file-system/use-tmpfs-rhel-centos-7-0/ https://linoxide.com/file-system/use-tmpfs-rhel-centos-7-0/

[firebird-support] Re: High write access on disk

Hey Dimitry Just to clarify, one could make a RAM drive with tmpfs, eg /ramdrive, and then specify this path in firebird.config under "TempDirectories"? If correctly understood, does this provide any benefits over just allocation more memory for TempCacheLimit, under Firebird 3? Thomas

[firebird-support] Re: High write access on disk

Hey Alexey First of all, thanks for all the help. Second, have you or anyone else working professional with Firebird thought about writing a book about Firebird optimisation - I would for sure buy a copy.

[firebird-support] Re: High write access on disk

Hey Alexey Thanks for the answer, I only have one question left, do I need to adjust linux page cache(vm.pagecache) according to amount of RAM allocated for DefaultDBCachePages, so that memory is not over "subscribed"? Eg. if DefaultDBCachePages is increased should i then decrease

Re: [firebird-support] Re: High write access on disk

Hey Alexey Its not that I see high traffic to /tmp as a problem, I was just worried that it was becoming a bottleneck in our system. Great to know that HQbird has a way of tracking these qureys, I have already been looking into HQbird especially because of prepared statements. Regarding the

[firebird-support] Re: High write access on disk

Hey Alexey Thanks for the advise, I changed TempCacheLimit to 21, last night and it looks like it had a positive effect on the disk queue size on sda. I have read a lot about Firebird tuning however I have never come across that limit on fb 2.5 https://pasteboard.co/IGsU650A.png

[firebird-support] High write access on disk

Hey Where I work we run a website with at growing number of users, over a period of two months we have seen Firebird slow down at peek hours, where the number of concurrent website users is about 6.000. Usually there is about 250 attachments, however when the slowdown occurs the number rises

[firebird-support] Upgrade from superclassic to superserver

Hey I am in the process of upgrading our Firebird database from 2.5.7 to 3.0.3. While making the upgrade, I want to switch from SuperClassic to SuperServer in order to gain better scalability. However configuring SuperServer i new to me. Background information: The database server is a 16

[firebird-support] Periodic server slowdown

Hey At my company we run a fairly large(by our standard) website whit about 3000 concurrent users, under normal circumstances this runs fine, there is between 50 and 80 attachments and web requests are served in under 100 on average. H The traffic is "bursty" and at certain intervals eg. when

[firebird-support] Re: Sort error No free space found in temporary...

Hey Thanks for the clarification, that is also the only explanation that made sense to me, after i had a look at the optimized config published at ib-aid.com. Alexey perhaps you can answer this, is there anything to gain from configuring TempCacheLimit to use 2, 4 or 6Gb? Or perhaps more

[firebird-support] Re: Sort error No free space found in temporary...

Hey From what i can read on the internet TempCacheLimit, is per server process, and not per attachment. http://firebird.1100200.n4.nabble.com/TempCacheLimit-td4309475.html http://firebird.1100200.n4.nabble.com/TempCacheLimit-td4309475.html

[firebird-support] Re: Sort error No free space found in temporary...

Hey Thomas Thanks for the answer. The command output is this: Max cpu time unlimitedunlimitedseconds Max file size unlimitedunlimitedbytes Max data size unlimitedunlimitedbytes Max

[firebird-support] Sort error No free space found in temporary...

Hey The other day we had a major slowdown on a database - one server with a single database. Some of our queries received this error: Sort error No free space found in temporary directories operating system directive open failed All queries that received this error used union in some way.

[firebird-support] Re: Improve remote query speed on Windows Server 2012 VPS

Does MYTABLE contain any blob columns with data that needs to be fetched?

[firebird-support] Re: firebird schema changes during backup

Dimitry, could you please elaborate on your point of view? What can go wrong during this kind of operation? Is there a risk of database/backup corruption? ---In firebird-support@yahoogroups.com, wrote : 24.01.2017 13:47, kragh.thomas@... mailto:kragh.thomas@...

[firebird-support] Re: firebird schema changes during backup

Thanks for the reply's To be clear when the change scripts is applied to production they have been tested on our CI server and in our pre-prod environment. We also uses the same pattern as Sean described, where script files are numbered and applied in the correct order

[firebird-support] firebird schema changes during backup

Hey On a product of ours we use continuous deployment where code and database schema changes is deployed quite often. Sometimes we would like to make a new release during database backup. So my questions are: 1) How does Firebird handle schema changes during backup? 2) And is it safe to

[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.

[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

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

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

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

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

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.

[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

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

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 created < dateadd(-6 month to current_date) followed by a count(*) on

Re: [firebird-support] Delete causes server to hang

Hey Tim The code does not do anything active, but we run backup 4 times a day. I have tried batching the delete, so that one batch runs in a autonomous transaction, and made at count on the table after each batch, but with no luck. The idea was to reduce the number of deleted rows that the

Re: [firebird-support] Delete causes server to hang

Hey Helen Thanks for hour reply, let me see if i can clarify the problem, the actual delete of the 90.000 rows is not the problem, this completes in 10-30 seconds. That is just fine, performance does not matter here. I have 2 web servers, on each of them, there is a background thread that

[firebird-support] Re: Delete causes server to hang

Hey Sean Yes there is a index, however I dont see the relevans, unless updating the index is the reason why the server hangs.

[firebird-support] Delete causes server to hang

Hey On my website i have a tabel where i log some web requests - about 90.000/hour. Every hour this SQL is executed in a stored procedure: delete from requests where created < dateadd(-7 day to current_timestamp); This results in about 90.000 deleted rows. However, after the