Re: [firebird-support] Re: Sort error No free space found in temporary...
Hello Alexey, > Hi, > > I remember I answered incorrectly that time :) > > TempCacheLimit is always per server instance. Makes sense. Thanks for the clarification. :-) -- With regards, Thomas Steinmaurer http://www.upscene.com Professional Tools and Services for Firebird FB TraceManager, IB LogManager, Database Health Check, Tuning etc. > For Classic it means it is allocated per each connection (since each > connection has own server process), for SuperClassic and SuperServer > temp space will be shared between connections, since there is the single > server process. > > Regards, > Alexey Kovyazin > IBSurgeon > www.ib-aid.com > > > > > On 02.09.2017 20:46, Thomas Steinmaurer t...@iblogmanager.com > [firebird-support] wrote: >> >> Hi Thomas, >> >> > From what i can read on the internet TempCacheLimit, is per server >> > process, and not per attachment. >> >> In SuperServer architecture, right, but if I remember correctly, you >> have mentioned in one of your first posts that you are using >> SuperClassic, right? >> >> > http://firebird.1100200.n4.nabble.com/TempCacheLimit-td4309475.html >> >> Talking about SuperServer. >> >> > >> https://www.mail-archive.com/firebird-support@yahoogroups.com/msg10199.html >> >> Talking about SuperClassic with answers that it is per connection. >> >> > >> > >> > I believe I have found the statements that caused the problem, first >> one >> > was executede about 400/second and contained a union, that was replaced >> > by union all in the hope that would eliminate the need for temp cache. >> >> UNION vs. UNION ALL. UNION is removing duplicates, thus some sort of >> applying DISTINCT with first sorting to remove duplicates is applied. >> >> DISTINCT, sorting, group by (internally first doing a sort) will >> internally produce temporary data. >> >> > The second statement is a really nasty dynamically build common table >> > expression containing upwards of 10 union.. Here we are in the process >> > of moving this search to Elastisearch. >> > >> > We just purchased FB TraceManager at our company to weed performance >> > issues - Thanks for a great product : ) >> >> Great to hear. Thanks. :-) >> >> -- >> With regards, >> Thomas Steinmaurer >> http://www.upscene.com/ >> >> Professional Tools and Services for Firebird >> FB TraceManager, IB LogManager, Database Health Check, Tuning etc. >> >> > >
Re: [firebird-support] Re: Sort error No free space found in temporary...
Hi, If you have big sort files (you can check their existence manually in temp folder or use HQbird FBBDataGuard to monitor them for you), increasing TempCacheLimit to fit all of them can speed up sorting queries (with GROUP BY and ORDER BY, for example). However, the better approach is to decrease record sets which should be sorted. For example, many developers use ORDER BY for internal processing in stored procedures - usually it can be simply removed. Regards, Alexey Kovyazin IBSurgeon On 03.09.2017 20:47, kragh.tho...@yahoo.com [firebird-support] wrote: 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 important could this have an negative effect? Of course provided that the server has plenty of RAM.
Re: [firebird-support] Re: Sort error No free space found in temporary...
Hi, I remember I answered incorrectly that time :) TempCacheLimit is always per server instance. For Classic it means it is allocated per each connection (since each connection has own server process), for SuperClassic and SuperServer temp space will be shared between connections, since there is the single server process. Regards, Alexey Kovyazin IBSurgeon www.ib-aid.com On 02.09.2017 20:46, Thomas Steinmaurer t...@iblogmanager.com [firebird-support] wrote: Hi Thomas, > From what i can read on the internet TempCacheLimit, is per server > process, and not per attachment. In SuperServer architecture, right, but if I remember correctly, you have mentioned in one of your first posts that you are using SuperClassic, right? > http://firebird.1100200.n4.nabble.com/TempCacheLimit-td4309475.html Talking about SuperServer. > https://www.mail-archive.com/firebird-support@yahoogroups.com/msg10199.html Talking about SuperClassic with answers that it is per connection. > > > I believe I have found the statements that caused the problem, first one > was executede about 400/second and contained a union, that was replaced > by union all in the hope that would eliminate the need for temp cache. UNION vs. UNION ALL. UNION is removing duplicates, thus some sort of applying DISTINCT with first sorting to remove duplicates is applied. DISTINCT, sorting, group by (internally first doing a sort) will internally produce temporary data. > The second statement is a really nasty dynamically build common table > expression containing upwards of 10 union.. Here we are in the process > of moving this search to Elastisearch. > > We just purchased FB TraceManager at our company to weed performance > issues - Thanks for a great product : ) Great to hear. Thanks. :-) -- With regards, Thomas Steinmaurer http://www.upscene.com/ Professional Tools and Services for Firebird FB TraceManager, IB LogManager, Database Health Check, Tuning etc.
Re: [firebird-support] Re: Sort error No free space found in temporary...
Hi Thomas, > From what i can read on the internet TempCacheLimit, is per server > process, and not per attachment. In SuperServer architecture, right, but if I remember correctly, you have mentioned in one of your first posts that you are using SuperClassic, right? > http://firebird.1100200.n4.nabble.com/TempCacheLimit-td4309475.html Talking about SuperServer. > https://www.mail-archive.com/firebird-support@yahoogroups.com/msg10199.html Talking about SuperClassic with answers that it is per connection. > > > I believe I have found the statements that caused the problem, first one > was executede about 400/second and contained a union, that was replaced > by union all in the hope that would eliminate the need for temp cache. UNION vs. UNION ALL. UNION is removing duplicates, thus some sort of applying DISTINCT with first sorting to remove duplicates is applied. DISTINCT, sorting, group by (internally first doing a sort) will internally produce temporary data. > The second statement is a really nasty dynamically build common table > expression containing upwards of 10 union.. Here we are in the process > of moving this search to Elastisearch. > > We just purchased FB TraceManager at our company to weed performance > issues - Thanks for a great product : ) Great to hear. Thanks. :-) -- With regards, Thomas Steinmaurer http://www.upscene.com/ Professional Tools and Services for Firebird FB TraceManager, IB LogManager, Database Health Check, Tuning etc.
Re: [firebird-support] Re: Sort error No free space found in temporary...
Hi, > Thanks for the advise Thomas I will increase the value. > After we changed TempCacheLimit to 567108864, we saw the problem once > under heavy load - for a very sourt perio this time. Increased TempCacheLimit value as a side-effect of serving more temp stuff through RAM instead of spilling onto disk, thus somehow makes sense to see the too many open files less frequent then. > Is it correct > understood that once TempCacheLimit was reached Firebird used /tmp/ > until the proccess reached the max open files? Sounds reasonable, yes. > Regarding TempBlockSize and TempCacheLimit do you or any other know of a > way to monitor if the configured value is too low or the limit is about > to be reached? No, but I became a bit rusty regarding Firebird lately. :-) > Since the server has plenty of RAM i was thinking about configuring > TempCacheLimit to 2, 4 or 6 Gb, if this has no negative effect? As you have mentioned that you are using SuperClassic, mind that TempCacheLimit is maximum memory for temp stuff PER client connections in this architecture, thus if configured too high with a high number of concurrent connections, you may end up in exhausting host RAM, resulting in swapping to disk if hitting boundaries. > Likewise if there is no negative effect, is there anything to gain from > increasing TempBlockSize from 2 megabytes to perhaps 4 or 6? I doubt you will see a real difference. Beside that, perhaps you are able to pin the SQL statement producing the temp data load e.g. by using the Trace API. -- With regards, Thomas Steinmaurer http://www.upscene.com/ Professional Tools and Services for Firebird FB TraceManager, IB LogManager, Database Health Check, Tuning etc.
Re: [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 unlimited unlimited seconds > Max file size unlimited unlimited bytes > Max data size unlimited unlimited bytes > Max stack size 8388608 unlimited bytes > Max core file size 0 unlimited bytes > Max resident set unlimited unlimited bytes > Max processes 514866 514866 > processes > Max open files 4096 4096 files > Max locked memory 65536 65536 bytes > Max address space unlimited unlimited bytes > Max file locks unlimited unlimited locks > Max pending signals 514866 514866 signals > Max msgqueue size 819200 819200 bytes > Max nice priority 0 0 > Max realtime priority 0 0 > Max realtime timeout unlimited unlimited us > > Max Open Files seems a bit low, is that the reason? Very likely, so try to increase the Max open files limit and restart the Firebird process. Increasing the limits might be a bit tricky, cause it may differ across Linux distros (to have new limits being persistent) and even more interestingly, how processes are started (e.g. via user based SSH session vs. started as systemd service etc.) Try to Google for your Linux distro. So, best check if new limits have been applied is always done via: sudo cat /proc//limits Hope this helps. -- With regards, Thomas Steinmaurer http://www.upscene.com/ Professional Tools and Services for Firebird FB TraceManager, IB LogManager, Database Health Check, Tuning etc.
