RE: [firebird-support] Re: how can the creation of fb_sort files be avoided? 2.2 GB of fb_sort files
<< it may be worth considering and adjunct piece of software to Firebird, called "HqBird", which uses Firebird as its base database engine. "HqBird" was designed for Firebird databases that are by necessity very large and require more power and capabilities than the standard version may offer. A similar situation has arisen with the equally powerful PostgreSQL database engine where when extensive power is required, the EnterpriseDB database engine has been developed using PostgreSQL as a base. EnterpriseDB can now provide Oracle-like capabilities at a fraction of the cost>> Nice analogy, but not entirely accurate... Paul
Re: [firebird-support] Re: how can the creation of fb_sort files be avoided? 2.2 GB of fb_sort files
On 2018-08-08 20:58, 'River~~' river14ap...@gmail.com [firebird-support] wrote: > Suggestion: if you have admin rights on the machine install a ramdisk > (there are several available for Windows and MS themselves offered > them for some earlier Windows versions) as say R: and tell Windows to > use R: for temp files. > > In older versions of Windows you set up > > TEMP=R: > TMP=R: > > as the environment of a batch file that runs the database - I am out > of touch with Windows now but I guess you can still do something > similar - ask your favourite Windows guru how you do that these days. > > The effect is that the software (both Windows and Firebird) thinks > it's writing normal files but in reality the entire structure > (folders and everything) is built in RAM. > > Just a thought offered in case it helps I think it is easier to change the Firebird configuration of TempCacheLimit as suggested earlier in this thread than to set up a ramdisk. It is probably also better for performance, but I haven't checked that. A ramdisk might be a better choice for Classic though, as in Classic, the TempCacheLimit is per connection. Mark
Re: [firebird-support] Re: how can the creation of fb_sort files be avoided? 2.2 GB of fb_sort files
Suggestion: if you have admin rights on the machine install a ramdisk (there are several available for Windows and MS themselves offered them for some earlier Windows versions) as say R: and tell Windows to use R: for temp files. In older versions of Windows you set up TEMP=R: TMP=R: as the environment of a batch file that runs the database - I am out of touch with Windows now but I guess you can still do something similar - ask your favourite Windows guru how you do that these days. The effect is that the software (both Windows and Firebird) thinks it's writing normal files but in reality the entire structure (folders and everything) is built in RAM. Just a thought offered in case it helps R~~ On Wed, 8 Aug 2018, 03:06 bieten2...@yahoo.de [firebird-support], < firebird-support@yahoogroups.com> wrote: > > > Hi Dalton, Alexey and Dimitry, > > thanks for your answers, I very much appreciate the support. > > It is correct, that I do not want to get rid of the sorting files, I just > want them to stay in RAM where I suspect that it could be advantageous for > performance. > Unfortunately I can't tell you what specific hard drives are in use because > I can't access them, but I was told that they are commercial HDD (not > SSD) . > Raid is most certainly employed, which one I do not know. > The Database File as well as Firebird DBMS are installed on the same > machine. This server is exclusively for the Firebird DBMS + Databases, > there is no other software trying to contest resources. > An ERP Software which is installed on an application-server connects to > the Firebird-DB-Server using port 3050. Said application is made available > to many clients using Windows RDP/ RDS. > Windows RDP/ RDS is not installed on the application server but on > dedicated RDP/ RDS servers. > I have not analysed the queries because I have not written them and lack > the necessary tools (and know-how). Query analysis is an issue that would > require a Firebird specialist. > My hope was, that by following the performance guides, notable performance > increases may be archived. > Optimal results can only be realized with an in-depth analysis, I am very > aware of that but I would like to get the "simple tuning" right first. > Alexey told me, that the TempCacheLimit can be set to 20 GB. > My current setting is 567108864 (540 MB), that's much too low, I will > increase it. > > After reading all the FB guides online I tried to optimize the > Firebird.conf to the best of my ability (see below). > Maybe someone will spot a bad setting and give me a tip :-) > > #RootDirectory = > #DatabaseAccess = Full > #ExternalFileAccess = None > #UdfAccess = Restrict UDF > #TempDirectories = > #LegacyHash = 1 > #Authentication = native > #AuditTraceConfigFile = > #MaxUserTraceLogSize = 10 > DefaultDbCachePages = 2048 > #DatabaseGrowthIncrement = 134217728 > #FileSystemCacheThreshold = 65536 > #FileSystemCacheSize = 0 > #RemoteFileOpenAbility = 0 > TempBlockSize = 2048576 > TempCacheLimit = 567108864 > #CompleteBooleanEvaluation = 0 > #DeadlockTimeout = 10 > #MaxUnflushedWrites = 100 > #MaxUnflushedWriteTime = 5 > #BugcheckAbort = 0 > #OldColumnNaming = 0 > #OldSetClauseSemantics = 0 > #RelaxedAliasChecking = 0 > #ConnectionTimeout = 180 > #DummyPacketInterval = 0 > #RemoteServiceName = gds_db > #RemoteServicePort = 3050 > #RemoteAuxPort = 0 > #TcpRemoteBufferSize = 8192 > #TcpNoNagle = 1 > #RemoteBindAddress = > LockMemSize = 99614720 > #LockGrantOrder = 1 > #LockAcquireSpins = 0 > LockHashSlots = 30011 > #EventMemSize = 65536 > #CpuAffinityMask = 1 > #UsePriorityScheduler = 1 > #PrioritySwitchDelay = 100 > #PriorityBoost = 5 > #GCPolicy = combined > #GuardianOption = 1 > #ProcessPriorityLevel = 0 > #IpcName = FIREBIRD > #RemotePipeName = interbas > #Redirection = 0 > > Best Regards, > James > > >
Re: [firebird-support] Re: how can the creation of fb_sort files be avoided? 2.2 GB of fb_sort files
Hi James, I have a couple of questions about your configuration before I give any suggestions. You specify you are wanting to get rid of sort files. Sort files are a tool the engine uses to hold information before sending them to the user. Depending upon your queries, sort files are required -especially if you use order by clauses (sort plans). I am assuming that the real reason you are asking the question is due to performance issues. So the question you should be asking is, how do I make my sorts/queries faster... In regards to your hardware you specified the following Windows 2008 R2 Server (Virtual Machine) Running Firebird 2.5.8.27089 in SuperClassic Mode 65,53 GB Ram available Database Size 58,96 GB 8 CPU Cores But, the big questions are, what hard drives are installed in the server, what are their performance specs, what are the data channels that those drives are connected to and how many, what raid configuration(if any) is setup as well as where your OS is installed, where the swap file is installed, where the temp directories (for the firebird user, system user and the firebird conf file states) are pointing, where the database files are sitting and what other applications/processes may be using or contesting drive and cpu resources. The next series of questions are, how big a tuple are you requesting, how big is your result set, how often do you run the query, is your data normalised and if so, to what level, how your indexes are setup along with how you optimised your queries. Firebird can run in high volume, realtime environments as long as you understand the bottlenecks that can affect your system. best regards Dalton On Tue, 7 Aug 2018 at 03:38, bieten2...@yahoo.de [firebird-support] < firebird-support@yahoogroups.com> wrote: > > > Hi everyone, > > it would be great if I where to receive an answer to this question :-) > I am unsure whether the issue is so easy to answer that everyone thinks it > is self explanatory > or that it is a rather challenging question which requires lots of > in-depth Fire Bird knowledge ^^ > > Best Regards, > James > > > ---In firebird-support@yahoogroups.com, wrote : > > Hallo Everyone! > > Although I spend a lot of time reading about performance tuning and > optimizing Firebird according to the guides that the Firebird Team has > published over the years, I could not figure out how to stop Firebird from > creating about 2.2 GB of fb_sort files in the C:\Windows\Temp directory. > > As described in other discussions the files disappear when the database is > not being used intensively but I would not like to see them created in the > first place. > > As far as I see it, it is disadvantageous, when temp-files are created on > the slow hard disk instead of keeping them in the fast RAM. I suspect that > this also applies to Firebird. > > You guys will very probably require system information in order to help: > Windows 2008 R2 Server (Virtual Machine) > Running Firebird 2.5.8.27089 in SuperClassic Mode > 65,53 GB Ram available > Database Size 58,96 GB > 8 CPU Cores > > > What do I have to do in order to stop the creation of fb_sort files? > My concern is that Firebird performance is suffering because of this. > I could of course also be mistaken and fb_sort-Files do not have a > (negative) influence on performance. > If this were true, then I would be relieved :-) > > Thanks a lot for your competent advice in advance and best Regards, > James > >
Re: [firebird-support] Re: how can the creation of fb_sort files be avoided? 2.2 GB of fb_sort files
Hello, Since you have enough RAM, you can increase TempCacheLimit in firebird.conf, for example, set 20Gb. Fb_sort_xxx files will be created only if sorting will require more than TempCacheLimit. Regards, Alexey Kovyazin IBSurgeon On 07.08.2018 10:35, bieten2...@yahoo.de [firebird-support] wrote: Hi everyone, it would be great if I where to receive an answer to this question :-) I am unsure whether the issue is so easy to answer that everyone thinks it is self explanatory or that it is a rather challenging question which requires lots of in-depth Fire Bird knowledge ^^ Best Regards, James ---In firebird-support@yahoogroups.com, wrote : Hallo Everyone! Although I spend a lot of time reading about performance tuning and optimizing Firebird according to the guides that the Firebird Team has published over the years, I could not figure out how to stop Firebird from creating about 2.2 GB of fb_sort files in the C:\Windows\Temp directory. As described in other discussions the files disappear when the database is not being used intensively but I would not like to see them created in the first place. As far as I see it, it is disadvantageous, when temp-files are created on the slow hard disk instead of keeping them in the fast RAM. I suspect that this also applies to Firebird. You guys will very probably require system information in order to help: Windows 2008 R2 Server (Virtual Machine) Running Firebird 2.5.8.27089 in SuperClassic Mode 65,53 GB Ram available Database Size 58,96 GB 8 CPU Cores What do I have to do in order to stop the creation of fb_sort files? My concern is that Firebird performance is suffering because of this. I could of course also be mistaken and fb_sort-Files do not have a (negative) influence on performance. If this were true, then I would be relieved :-) Thanks a lot for your competent advice in advance and best Regards, James
Re: [firebird-support] Re: how can the creation of fb_sort files be avoided? 2.2 GB of fb_sort files
07.08.2018 9:35, bieten2...@yahoo.de [firebird-support] wrote: > it would be great if I where to receive an answer to this question :-) > I am unsure whether the issue is so easy to answer that everyone thinks it is > self explanatory Sort files are created when you issue queries with plan SORT. Check your queries and change them to avoid sorting. -- 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/
