At 02:56 AM 23/04/2006, you wrote:

>We have huge database ( 30 GB) that seems to be working ok on
>Windows. We need to port this database to Linux. Transportable
>backup file is about 20GB in size. Biggest table is about 185
>millions of records.
>
>This backup file can be restored on the Windows server, but when we
>try to restore it on the Linux box FB gets deadlocked when it needs
>to activate indexes. We have managed to restore database without
>index activation. Then we have activated indexes manually one by
>one… all but the indexes on the biggest table. Any attempt to
>activate or drop and restore indexes on the 185 Mln. records table
>cause either deadlock , if run through the script, or when we run it
>interactively, then after a certain period of time we get an error
>that FB cannot read from the temporary file in the temp directory…
>sort operation failed..

If you don't configure sort space explicitly in
firebird.conf, the engine puts its sort files in
/tmp. It's likely your /tmp directory is
either on an ext2 partition or the partition
itself is smaller than the size you need for sorts on this large table.

Try configuring your temp sort space explicitly
in a non-ext2 partition that's physically large
enough to accommodate the largest table +
more. The configuration parameter you want is
TempDirectories. You can configure more than one
location but a sort file has to be able to
accommodate at least all of that table in one temp space.

The default is
# TempDirectories =
which tells the engine to use /temp

An example configuration would be (removing the # comment marker)
TempDirectories = /home/firebird
This will use /home/firebird until the entire partition is exhausted.

If you want to limit the amount of sort space,
add an argument in bytes. For example, the
following will use up to ~ 5 Gb before blowing:
TempDirectories = /home/firebird 5450000000

Suppose you have your /var directory on its own
partition and you want the engine to put sort
files there by default but keep a suitably sized
space available for huge sorts when needed. You could configure it like this:
TempDirectories = /var;/home/firebird 5450000000

Ensure that, wherever you consign the sort files,
the user the user:group firebird:firebird has rwx permissions.

>We have checked , and we have enough disk space on the Linux box.

Also check the partitioning of the disk.

btw, Firebird 1.5 will use RAM for sorts if
there's enough available. You can configure this space as well.

# ----------------------------
# In-memory sorting module
#
# The amount of memory allocated for each sort block.
#
# Type: integer
#
#SortMemBlockSize = 1048576

#
# The maximum amount of memory to be allocated by the in-memory
# sorting module.
#
# For Classic servers, this setting is defaulted to 8 MB.
# Although it can be increased, the value applies to each client
# connection/server instance and thus consumes a lot of memory.
#
# Type: integer
#
#SortMemUpperLimit = 67108864

./heLen