Hello Thomas,

> 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.
>
>
> The Firebird log is shows a lot of errors at the same time.
> Cannot dump the monitoring data operating system directive open failed Too many
> open files
> And
> Cannot initialize the shared memory region operating system directive open
> failed Too many open files

"Too many open files" usually means that the Firebird process isn't allowed to acquire more file handles from the OS.

What is the output of?

sudo cat /proc/<pid of firebird process>/limits

>
>
> After this the following changes was made to the config
> TempBlockSize = 2048576(changed from default value)
> TempCacheLimit = 567108864(changed from default value)

With TempCacheLimit, you basically tell Firebird how much RAM it is allowed to use for temporary data structure caused by sorting, group by etc. before spilling temporary stuff out onto disk.

> (I took the values from the firebird_25_superclassic_64bit.conf from ib-aid)
>
>
> However today same thing happened once again. Does anyone know the solution to
> this?
>
>
> TempDirectories in config is set to default, so I guess /tmp/firebird i used,
> this disk has about 12Gb free space at the time of writing. Could all this
> space have been consumed and causing the errors?

If TempDirectories is commented out (thus not explicitly set), Firebird will use whatever the OS returns as disk location for temporary data.


Do you monitor disk usage somehow to correlate e.g. increased disk usage with error message above?

>
>
> A few weeks back we moved the database from a Windows Server 2008R2 to CentOs
> to accommodate an increased load, on the database, before this we hand never
> seen this kind of error. The windows server used a default firebird config file
> except for DefaultDbCachePages that was configured to 1024.
>
>
> Firebird: SuperClassic 2.5.7
> Server: Virtual server, 16 cores, 128Gb of Ram, Multi disk SSD SAN.

--
With regards,
Thomas Steinmaurer
http://www.upscene.com

Professional Tools and Services for Firebird
FB TraceManager, IB LogManager, Database Health Check, Tuning etc.