Firebird shutdown что это
Firebird shutdown что это
The first part of this section describes the shutdown and startup options up to Firebird 2.0. There is a separate section at the end which discusses the new states for starting and stopping a database using Firebird 2.0 onwards.
Database Shutdown
If there is maintenance work required on a database, you may wish to close down that database under certain circumstances. This is different from stopping the Firebird server as the server may well be running other databases which you do not wish to affect.
The command to close a database is:
The TIMEOUT parameter is the time, in seconds, that the shutdown must complete in. If the command cannot complete in the specified time, the shutdown is aborted. There are various reasons why the shutdown may not complete in the given time and these vary with the mode of the shutdown and are described below.
The OPTION parameter is one of the following:
When a database is closed, the SYSDBA or the database owner can still connect to perform maintenance operations or even query and update the database tables.
Preventing New Connections
-at[tach] : this parameter prevents any new connections to the database from being made with the exception of the SYSDBA and the database owner. The shutdown will fail if there are any sessions connected after the timeout period has expired. It makes no difference if those connected sessions belong to the SYSDBA, the database owner or any other user. Any connections remaining will terminate the shutdown with the following details:
Anyone other than the SYSDBA or database owner, attempting to connect to the database will see the following:
Connections in the database will still be able to start new transactions or complete old ones.
Preventing New Transactions
-tr[an] : prevents any new transactions from being started and also prevents new connections to the database. If there are any active transactions after the timeout period has expired, then the shutdown will fail as follows:
Force Closure
-f[orce] : shuts down with no regard for the connection or transaction status of the database. No new connections or transactions are permitted and any active sessions are terminated along with any active transactions.
Anyone other than SYSDBA or the database owner trying to connect to the database during the timeout period will not be able to connect successfully or start any (new) transactions.
Warning
Starting a Database
Once all maintenance work required on a database has been carried out, you need to restart the database to allow normal use again. (See shutdown option above for details of closing a database.)
The following example shows a closed database being started.
New Startup and Shutdown States in Firebird 2.0
The above discussion of stopping and starting a database apply to all versions of the server up to version 2.0. From 2.0 the commands will work as described above, but a new state has been added to define exactly how the database is to be stopped or started. The commands change from those described above to the following:
STATE is new in Firebird 2.0 and is one of the following:
There is a hierarchy of states for a database. The above list shows them in order with normal at the top and full at the bottom.
This hierarchy is important, you cannot shutdown a database to a higher or equal level that it currently is, nor can you startup a database to a lower or equal level.
If you need to identify which level a database is currently running at, gstat will supply the answers. The following example puts a database fully online then progressively shuts it down to fully offline. At each stage, gstat is run to extract the Attributes of the database.
Инструменты пользователя
Инструменты сайта
Боковая панель
Введение
Статьи и часто задаваемые вопросы
Установка и настройка
Обработка ошибок
Полезные запросы SQL
Тематические группы
Содержание
Версии сервера
Формат
Описание
Эта утилита является одним из основных инструментов администратора БД. Утилита gfix позволяет:
Все параметры утилиты приведены в следующей таблице
Примеры
Чистка sweep происходит в фоновом режиме и может выполняться параллельно работе пользователей. Этот способ более предпочтителен, чем чистка gbak при восстановлении БД. Утилита gbak не делает полной чистки, так как остаются версии удаленных записей и записей отмененных транзакций. Принудительную чистку можно сделать так:
Чистка базы данных происходит автоматически через определенное количество (по умолчанию 20 000) транзакций. Расчет интервала делается из старейшей транзакции, зарегистрированной в области TIP (Инвентарная страница транзакций), и из старейшей активной транзакции. Когда инвентарный номер старейшей активной транзакции окажется больше на указанный интервал, чем инвентарный номер старейшей зарегистрированной в TIP транзакции, произойдет автоматический запуск чистки. Изменить интервал на 10 000, например, можно так:
Если же вместо 10 000 указать 0, автоматическая чистка для данной БД вообще будет отменена. Как уже говорилось, чистка не требует монопольного доступа к базе данных, однако если БД очень большая, и много пользователей интенсивно с ней работают, чистка может заметно замедлить работу с БД. В этом случае, перед чисткой рекомендуется вначале отключить базу данных.
Однако такой радикальный способ рекомендуется использовать с осторожностью: пользователи, которые на этот момент продолжали работу с БД, потеряют результаты своей работы. Вначале вместо «-force» лучше попробовать более «мягкие» дополнительные параметры «-attach» или «-tran».
После того, как база данных закрыта, и с ней выполнены все необходимые действия, ее нужно открыть командой
Другим способом установить размер кэша по умолчанию для всех вновь создаваемых БД, является изменение конфигурационного файла firebird.conf, который находится в папке Firbird.
Это обычный текстовый файл, в котором все параметры закомментированы (первым символом идет «#»). Нужно снять комментарий, и установить новое значение нужного параметра. То есть, вместо
Однако более предпочтительным способом для этих целей является утилита gfix, так как она позволяет установить собственный размер кэша для каждой базы данных. Если какой то БД пользуются реже, размер кэша для нее можно оставить по умолчанию, или даже уменьшить.
База данных может работать в одном из двух режимов доступа: только для чтения, или для чтения / записи (по умолчанию). Если вам понадобилось запретить пользователям модифицирование данных, вы можете поменять режим командой:
Операции изменения режима занимают время! Не забудьте потом вернуть режим read_write, иначе пользователи не смогут вносить изменения в БД.
По умолчанию, все БД работают с включенным Forced Writes и отключать этот режим не рекомендуется. Если все же вы не удовлетворены производительностью БД, и при этом стопроцентно уверены в своем серверном ПК, можете попробовать отключить Forced Writes командой:
выведет на экран все неназначенные страницы, которые являются мусором. Если на экран ничего не вышло, значит, таких страниц в БД нет.
Если база данных разрушилась, можно вместо нее активировать теневую копию командой
Firebird Database Housekeeping Utility
1. Introduction
Gfix allows attempts to fix corrupted databases, starting and stopping of databases, resolving ‘in limbo’ transactions between multiple databases, changing the number of page buffers and so on. Gfix is a general purpose tool for system administrators (and database owners) to use to make various ‘system level’ changes to their databases.
Almost all the gfix commands have the same format when typed on the command line:
The commands and their options are described in the following sections. The database name is the name of the primary database file which for a single file database is simply the database name and for multi-file databases, it is the first data file added.
Coming up in the remainder of this manual, we will discuss the following:
Command line options for the gfix database utility.
Shadow file handling.
Cache and buffer handling.
Starting and stopping a database.
And much, much more …
2. Command Line Options
3. Gfix Commands
Allows the username of the SYSDBA user, or the owner of the database to be specified. This need not be supplied if the ISC_USER environment variable has been defined and has the correct value.
Supplies the password for the username specified above. This need not be supplied if the ISC_PASSWORD environment variable has been defined and have the correct value.
To define the username and password as environment variables on a Linux system:
Alternatively, on Windows:
This is very insecure as it allows anyone who can access your session the ability to perform DBA functions that you might not want to allow.
Supplies the role name to use for the username specified.
Use trusted authentication.
The line that starts with ‘Unable to perform’ above, has had to be split to fit on the page of the PDF file. In reality, it is a single line.
However, passing the username and password works:
4. Shadow Files
A shadow file is an additional copy of the primary database file(s). More than one shadow file may exist for any given database and these may be activated and de-activated at will using the `gfix`utility.
The following descriptions of activating and de-activating shadow files assume that a shadow file already exists for the database. To this end, a shadow was created as follows:
It can be seen that the database now has two separate shadow files created, but as they are manual, they have not been activated. We can see that shadows are in use if we use gstat as follows:
Sometimes, it takes gstat a while to figure out that there are shadow files for the database.
Shadow file details can be found in the RDB$FILES table within the database.
4.1. Activating Shadows
The command to activate a database shadow is:
This makes the shadow file the new database file, and the users are able to continue processing data as normal and without loss.
In the event that your main database file(s) become corrupted or unreadable, the DBA can activate a shadow file. Once activated, the file is no longer a shadow file, and a new one should be created to replace it. Additionally, the shadow file should be renamed (at the operating system prompt) to the name of the old database file that it replaces.
It should be noted that activating a shadow while the database itself is active can lead to corruption of the shadow. Make sure the database file is really unavailable before activating a shadow.
Once a shadow file has been activated, you can see the fact that there are active shadows in the output from gstat :
The DBA can set up the database to automatically create a new shadow file in the event of a current shadow being activated. This allows a continuous supply of shadow files and prevents the database ever running without one.
4.2. Killing Shadows
The command to kill all unavailable database shadows, for a specific database, is:
In the event that a database running with shadow files loses a shadow, or a shadow becomes unusable for some reason, the database will stop accepting new connections until such time as the DBA kills the faulty shadow and, ideally, creates a new shadow to replace the broken one.
The following (contrived) example, shows what happens when the database loses a shadow file, and an attempt is made to connect to that database. There are two sessions in the following example, one is connected to the database while the second deletes a shadow file and then tries to connect to the database. The command line prompts shows which of the two sessions we are using at the time.
First, the initial session is connected to the database and can see that there are two shadow files attached:
In the second session, we delete one of the shadow files, and then try to connect to the database
The database now has a single shadow file where before it had two. It is noted, however, that gstat still shows the database as having two shadows, even when one has been removed.
5. Set Database Page Buffers
The database cache is an area of RAM allocated to store (cache) database pages in memory to help improve the efficiency of the database performance. It is far quicker to read data from memory that it is to have to physically read the data from disc.
The size of the database cache is dependent on the database page size and the number of buffers allocated, a buffer is the same size as a database page, and whether the installation is using Classic or Superserver versions of Firebird.
In a Classic and SuperClassic Server installation, by default, each connection to the database gets its own relatively small cache of 256 pages (was 75 before Firebird 3.0) while Superserver creates a much larger cache of 8196 pages (was 2048 before Firebird 3.0) which is shared between all the connections.
Usually, page buffers value is set as parameter DefaultDbCachePages in firebird.conf (and, in databases.conf since Firebird 3.0), but it also can be set directly in the database header using gfix tool (and this setting will override firebird.conf and databases.conf, so be careful).
The command to set the number of cache pages is:
This command allows you to change the number of buffers (pages) allocated in RAM to create the database cache.
You cannot change the database page size in this manner, only the number of pages reserved in RAM.
One parameter is required which must be numeric and between 50 (the minimum) and 2147483646 (the maximum since Firebird 2.5, before it was 131072).
Please don’t consider page buffers value as an ultimate parameter to improve Firebird’s performance, and don’t set it too high without clear understanding what are you doing.
The setting applies only to the database you specify. No other databases running on the same server are affected.
The new value of page buffers will be allocated at the first connection to the database.
There is a difference in behaviour between Firebird architectures: for SuperServer, if number of page buffers was changed while database had active connection, the old value will be used until all connections disconnect; for Classic/SuperClassic, new connections will use the new value of page buffers immediately after the change.
The following example shows the use of gstat to read the current number of buffers, the gfix utility being used to set the buffers to 4,000 pages and gstat being used to confirm the setting. The value of zero for page buffers indicates the default setting for the server type is in use.
6. Limbo Transaction Management
Limbo transactions can occur when an application is updating two (or more) databases at the same time, in the same transaction. At COMMIT time, Firebird will prepare each database for the COMMIT and then COMMIT each database separately.
In the event of a network outage, for example, it is possible for part of the transaction to have been committed on one database but the data on the other database(s) may not have been committed. Because Firebird cannot tell if these transactions (technically sub-transactions) should be committed or rolled back, they are flagged as being in limbo.
Gfix offers a number of commands to allow the management of these limbo transactions.
The following examples of limbo transactions are based on Firebird 1.5 and have kindly been provided by Paul Vinkenoog. Because of the limitation of my setup, I am unable to create limbo transactions in my current location.
In the spirit of consistency, however, I have renamed Paul’s servers and database locations to match the remainder of this document.
6.1. Listing Limbo Transactions
If the command is run against the remote database then nothing will be listed because that database does not have any limbo transactions — the transaction that went into limbo, when the network failed, for example, was initiated on the local database.
An example of this is shown below.
6.2. Committing Or Rolling Back
When a limbo transaction has been detected, the DBA has the option of committing or rolling back one or more of the transactions reported as being in limbo.
When more than one transaction is listed, the DBA can either commit or roll back all transactions in limbo, or a specific transaction number.
To commit every limbo transaction on the database, the following command would be used:
If the DBA wanted to commit a single transaction, then the command would change to the following:
Where TXN is the transaction number to be committed.
You cannot commit or rollback a transaction that is not in limbo. If you try, the following will occur:
6.3. Automatic Two-phase Recovery
The command line to carry out automatic two-phase recovery is:
As above, TXN is a single transaction number from the list of limbo transactions.
7. Cache Manager
However, when called this option simply displays the help page again.
The question that immediately springs to my mind is, if we can shutdown the cache manager with this option, how do we start it back up again?
8. Changing The Database Mode
Databases can be set to run in one of two modes, read only — where no updates are permitted, and read/write — where both reading and writing of data is permitted. By default, Firebird creates read/write databases and as such, all read/write databases must be placed on a file system which allows writing to take place.
Should you wish to put a Firebird database on a CD, for example, you wouldn’t be able to do so. After a new database has been populated with data it can be changed to read only mode, and then used on a CD (or other read only file systems) with no problems.
Firebird uses SQL internally to maintain its internal structures with details about transactions, for example, and this is the reason that a database must be placed on a read/write file system regardless of whether only SELECT statements are run or not.
Only databases in dialect 3 can be changed to read only mode.
The command to set the required mode for a database is:
The command takes two parameters, the MODE which must be one of the following:
the database cannot be written to.
the database can be written to.
The meaning of the two modes should be quite meaningful.
The second parameter is a database name to apply the mode change to.
The following example shows how to put a database into read only mode, and then change it back again. The example also shows what happens when you try to update the database while running in read only mode.
If there are any connections to the database in read/write mode when you attempt to convert the database to read only, the attempt will fail as shown below with Firebird 1.5.
Under Firebird 2, the error message is more self-explanatory:
9. Setting The Database Dialect
The dialect of the database is simply a term that defines the specific features of the SQL language that are available when accessing that database. There are three dialects at present (Firebird version 2.0), these are:
Dialect 3 databases allow numbers ( DECIMAL and NUMERIC data types) to be stored as BIGINT when the precision is greater than 9. The TIME data type is able to be used and stores time data only. The DATE data type stores on date information. Double quotes can be used but only for identifiers that are case dependent, not for string data which has to use single quotes.
The command to change the SQL dialect for a database is:
The DIALECT parameter is simply 1 or 3.
The following example changes a database to use dialect 3 which will allow many newer features of SQL 92 to be used.
Because you cannot use gstat remotely, you may also use the isql command SHOW SQL DIALECT from a remote location to see which dialect your client and database are using, as follows:
Although dialect 2 is possible on the client, trying to set a dialect of 2 will fail on the server as the following example shows.
To set dialect 2 for your client connection, you use isql as follows:
The WARNING line above has had to be split to fit on the page of the PDF version of this manual. In reality, it is a single line of text.
10. Database Housekeeping And Garbage Collection
10.1. Garbage
Garbage, for want of a better name, is the detritus that Firebird leaves around in the database after a rollback has been carried out. This is basically a copy of the row(s) from the table(s) that were being updated (or deleted) by the transaction prior to the rollback.
Almost all garbage is created by committed transactions. Since around V2.5 transactions that rollback are cleaned up immediately — assuming that Firebird is still running.
The major cause of garbage build-up is long-running transactions that require Firebird to keep old versions of records that are frequently updated. Another source of garbage is an application strategy that deletes records and never revisits them.
What actually happens on delete is that Firebird stores a «deleted stub» with the full record as a back version. Until the delete is mature — meaning that all active transactions started after the delete was committed — the old version must be preserved.
Imagine a table that’s indexed and accessed by date. On some schedule, records age out and are deleted. In the application, records are accessed by date, and the deleted records are so old no query ever asks for them. So there they sit, taking up space and doing no good to anyone. Even with a garbage collect thread, some active transaction has to stumble over deleted records or records with unneeded back versions before the record will be garbage collected.
In cooperative garbage collection, that particular record will be cleaned up immediately (or at least when the transaction gets some cycles). The dedicated garbage collection thread should clean up all the records on a page, but not until an active transaction tells it that there’s a page that needs cleaning.
Because Firebird uses multi-generational architecture, every time a row is updated or deleted, Firebird keeps a copy in the database. These copies use space in the pages and can remain in the database for some time, especially if there are no active transactions stumbling across them!
There are a number of causes of garbage:
Remnants from a committed transaction. This is the main cause of garbage since around Firebird version 2.5.
Remnants from an aborted (rolled back) transaction may exist in Firebird versions prior to 2.5, newer versions perform immediate clean up after a rollback however, if the Firebird Server, the Operating System or the physical server crashed, then these remnants may still exist, even in later versions of Firebird.
Applications, described above, which delete database records, but then, subsequently, never revisit those deleted versions to garbage collect them automatically.
With regard to the remnants from aborted or rolled back transactions, Firebird (now) carries out record-keeping to facilitate save points. This housekeeping allows Firebird to identify and, if necessary, undo all changes made by a transaction in the event that it is rolled back, or which failed due to a lost connection.
If a failed transaction is rolled back in either manner, its state is set to committed as there are no differences between a failed transaction and one in which it committed after making no changes.
These remnants are simply older copies of the rows that were being updated by the respective transactions. The differences are that:
Whenever a subsequent transaction reaches garbage from a committed transaction, that garbage is automatically cleared out, but see above for reasons where this may not take place often enough.
Rolled back garbage looks just like record versions created by active transactions. Those records can be accessed either sequentially (during a full table scan) or by index — assuming that the index entry was made before the crash that left the garbage around. The index entries will exist in the case of all but the last change made. When one transaction reads a record version created by a transaction that’s listed in the transaction bit vector as active, the reader attempts to get a lock on the apparently active transaction id. If the lock request succeeds, then the other transaction is dead and the reader will either clean up the mess or notify the garbage collect thread to do so.
Firebird will automatically sweep through the database and remove the remnants of rolled back transactions and this has two effects:
The space recovered is made available for reuse by the same table, however, if this results in the page becoming completely empty, then it can be used for any purpose within the database.
The performance of the database may be affected while the sweep is in progress.
Theoretically, it is possible to clear out old rolled back transactions’ garbage with a database backup by gbak tool. Gbak reads every table sequentially and thus visits every row in every table. Applications which also visit every row in one or more tables, will also cause the garbage in those tables to be collected. Neither will affect the database’s OIT (Oldest Intersting Transaction) or OST (Oldest Snapshot) settings however.
In practice, better do not consider backup as an alternative for sweep, because sweep does more things than collective garbage collection.
In the Super Server version of Firebird 2.0, garbage collection has been vastly improved. There are now three different ways of operation and these are configurable by setting the GCPOLICY parameter in the firebird.conf configuration file. By default, Super Server uses combined while Classic Server uses cooperative. The other option is background.
Classic Server ignores the setting and always uses cooperative garbage collection.
10.1.1. Record Versions
Normally, when a «back» or old version of a row in a table is created, it will be stored on the same page as the newest version. This is usually fine as the back version is not normally a complete copy of the old version, merely a list of differences from the newest version. Enough information is retained in the old version, to be able to recreate it, if necessary.
If the database is suffering from a lack of garbage collecting, either deliberately, or down to the application design, then it is possible that there will be a build up of enough back versions to fill the target page. When the chain of old versions gets too big, Firebird has to move the old versions to a different page which, if it occurs as part of an UPDATE statement, as it normally will, the UPDATE will run a lot slower than usual and will greatly increase the cost of subsequent garbage collection against that table.
10.1.2. Cooperative Garbage Collection
This is the default setting, indeed the only setting, that Classic Server uses. In this mode, the normal operation — as described above — takes place. When a full scan is performed (perhaps during a backup) old versions of the rows are deleted at that point in time. Record versions which are old enough that no active transactions have any interest in them will be removed, as will any versions created by failed transactions, if there are any present. (Which there shouldn’t be!)
10.1.3. Background garbage Collection
Super Server has, even since before version 1.0, performed background garbage collection where the server informs the garbage collector about old versions of updated and deleted rows when they are ready to be cleaned up. This helps avoid the need to force a full scan of each record in the database tables to get the garbage collector to remove these old versions. An active transaction has to recognize the need for garbage collection and notify the server which puts that record id on a list for the garbage collect thread. So an unvisited record will not attract the garbage collector unless another record on that page is read and does need cleanup.
When all rows in a table are read by the server, any old record versions are flagged to the garbage collector as being ready to be cleared out. They are not deleted by the scanning process as in the cooperative method. The garbage collector runs as a separate background thread and it will, at some point, remove these older record versions from the database.
10.1.4. Combined Garbage Collection
This is the default garbage collection method for Super Server installations. In this mode, both the above methods are used together.
10.2. Setting Sweep Interval
The default sweep interval for a new database is 20,000. The sweep interval is the difference between the Oldest Snapshot Transaction, or OST and the Oldest Interesting Transaction or OIT.
This doesn’t mean that every 20,000 transaction a sweep will take place. It will take place when the difference between the OST and the OIT is greater than the sweep interval.
An interesting transaction is one which has not yet committed. It may be still active, in limbo or may have been rolled back. (Limbo transactions are never garbage collected.)
The sweep facility runs through the database and gets rid of old rows in tables that are out of date. This prevents the database from growing too big and helps reduce the time it takes to start a new transaction on the database.
If you find that starting a new transaction takes a long time, it may be a good idea to run a manual sweep of the database in case the need for a sweep is causing the hold-up.
Alternatively, another idea is to run gstat with the switches set to show old record versions. If that shows a problem, then it may be a good idea to start looking for long running transactions.