What's New in InterBase 7

by Bill Todd

(C) Copyright 2002 by Bill Todd - All Rights Reserved

Multiprocessor Support

Since InterBase consists of over a million lines of legacy C code that was not written with the idea of multiprocessor computers in mind, converting the InterBase kernel and related subsystems to provide multiprocessor support may well be the biggest change in the history of the product. This project had several goals.

1. Support current and emerging multiprocessor technologies including symmetric multiprocessing (SMP) and cache-coherent non-uniform memory access (NUMA).
2. Make the engine fully multithreaded to realize maximum concurrency.
3. Introduce a memory locking scheme that is both resource efficient and highly granular.
4. Cetralized memory access management to track synchronization.
5. Allow multiple readers to access a shared variable or data structure simultaneously.
6. Deadlock detection and prevention.

Multithreading

Making the InterBase engine fully multithreaded was necessary to allow multiple tasks to truly execute concurrently on a multiprocessor machine. However, it also provides a major performance benefit for multiuser applications on single CPU systems. In prior versions of IB once a SQL statement begain execution on a single CPU machine other pending tasks would not gain access to the CPU until the executing statement finished. If a statement took a long time to complete other users applications with statements waiting to be executed would become unresponsive while waiting their turn. InterBase 7's new multithreading architecture allows the CPU to be shared among all running statements according to the operating systems time slicing model. While this does not reduce the total time for all statements to execute it does allow statements to share the CPU so that statements with a short execution time will finish quickly instead of waiting for a long running statement to finish. This presents end-users with the appearance of a faster more responsive system.

InterBase 7 does not support decomposing a single SQL statement into multiple execution threads that can run simultaineously on multiple processors. To take full advantage of InterBase's multiprocessor support you must have a multiuser environment where multiple connections to the InterBase server are executing SQL statements simultaneously.

Controlling Shared Memory Access

When designing a method of controlling access to shared variables and data structures in a multithreaded environment the granularity of the locking scheme has a significant impact on performance. It is best to lock and unlock each individual variable and data structure when it is accessed instead of locking entire functions or modules.

Since most accesses to shared variables are read accesses it is also desirable to use a locking scheme that provides shared read locks and exclusive write locks. With this scheme multiple threads can obtain simultaineous read locks on the same variable. As long as a read lock exists no thread can obtain a write lock. When one thread holds a write lock no other thread can obtain either a read or write lock on that varaible or data structure.

Access to shared variables and data structures is controlled using mutexes allocated by the operating system. Not all operating systems support read/write mutexes, for example, Windows does not. For operating systems that do not provide read/write mutexes this functionality is implemented in InterBase.

Using operating system mutexes introduces another problem because allocating a mutex requires both time and resources. In a legacy application with a large number of shared variables and datastructures to protect achieving high lock granularity by allocating a mutex for each variable or data structure would consume too many resources. InterBase achieves both high granularity and low resource usage by creating a pool of shared mutexes. The size of the pool is based on the number of CPU's in the system. When a mutex is requried it is allocated from the pool. When the mutex is no longer needed it is returned to the pool.

Memory access throughout the InterBase engine is controlled by a single central subsystem. This subsystem manages the mutex pool and also mantains statistics and state information that aids the InterBase engineering team in debugging and analyzing problems.

Controlling Processor Affinity

By default the InterBase 7 server uses all of the processors in the system it is running on. If InterBase is not running on a dedicated system you may not want InterBase to use all of the CPU's in the system. The Windows version of InterBase supports the CPU_AFFINITY directive in the ibconfig file. This directive can be used to affine InterBase to any processor or combination of processors in the system. Each processor is identified by a bit so a value of 1 indicates the first processor, 2 the second processor, 4 the third processor and 8 the fourth processor. For example,

CPU_AFFINITY 6

would attach InterBase to the second and third processors in the system. There is no way to control which processors are used on a Linux or Solaris system. On these platforms InterBase will always use all available processors.

What You Will See

If you have a Windows system with multiple processors install InterBase 6.5 and run two or more SQL statements simultaneously while looking at the Performance tab in Task Manager. On a dual CPU system you should see CPU usage at about 50%.

Now install InterBase 7 and repeat the same test. You will see both CPU's being used at the same time.

Using the MAX_THREADS Configuration Parameter

By default the InterBase 7 engine can have up to 100 threads active at one time. You can change the maximum number of active threads using the new MAX_THREADS parameter in the ibconfig file. For example, the following line limits InterBase 7 to 50 active threads.

MAX_THREADS  50

The best value for MAX_THREADS depends on what the client applications are doing. If many clients are doing the same task you may get improved performance by reducing MAX_THREADS to reduce contention. Where many clients are performing diverse tasks a higher number may work better.

MAX_THREADS does not limit the number of threads that the InterBase Server can create. Lowering MAX_THREADS will not prevent clients from connecting to the server. MAX_THREADS only limits the number of threads that can be active at one time.

Performance Monitoring Features

While InterBase is largely self tuning and does not require the minstrations of a fulltime DBA, the lack of adequate performance monitoring tools has been a problem for some users. Understanding how resources are being used in a production environment can be important for capacity planning. With resource usage information you can see impending problems before they occur and increase the capacity of the database server before performance degrades and users complain.

Transactions that are left open for a long time can degrade system performance. GSTAT has always provided the oldest interesting transaction number (OIT), oldest active transaction number (OAT) and the next transaction number to let you see if a long running transaction is causing trouble, but the information stopped there. In prior versions of InterBase you had no way to identify the owner of the offending transaction or to terminate it. A runaway query is another problem that requires intervention by the DBA. If a user starts a query that runs for a long time and consumes a lot of system resources performance can degrade for other users. The DBA needs a way to identify the query and either terminate it or reduce its priority to restore system performance.

Temporary Tables

InterBase has always maintained a great deal of performance information internally but this information as never been accessable before. The ideal way to make performance information available is through a set of temporary tables. Providing performance information in table form lets DBA's and tools vendors examine information using standard SQL queries. Temporary tables can be joined to provide powerful views of the performance information. Using temporary tables and SQL also allows new information to be added to the tables in the form of new columns without breaking existing applications that use the data.

A temporary table is a table that is private to the attachement (connection) that creates it. Unlike a permanent table a temporary table is dropped after the application that creates it detaches from the database. Multiple users can create temporary tables with the same name at the same time without name collisions.

Temporary tables also have an ON COMMIT clause in the their CREATE TEMPORARY TABLE statement that determines what happens to the temporary table's data when the transaction that created the table commits. The syntax is ... ON COMMIT {PRESERVE | DELETE} ROWS. All of the temporary tables used for monitoring the runtime state of InterBase are predefined by the system and have the ON COMMIT DELETE ROWS option.

Before I continue I may as well give you the bad news. Temporary tables are not surfaced for users in InterBase 7. Fortunately, now that 90% of the work has been done, the odds that we will see them in the next release have increased dramatically.

Temporary Tables for Monitoring the InterBase Server

An InterBase database is described by the static metadata in its system tables. The system tables describe the structure of the database. At runtime, however, the InterBase server is very dynamic, even chaotic, with many users attached to variaous databases performing different tasks. The focus of the monitoring features in InterBase 7 is on how existing computing resources are being used by the InterBase server. The resources of interest are the CPU, memory, device I/O and network I/O.

Resource use by four database objects is covered by the monitoring system. The objects are the database, attachments, transactions and statements. For an open database there are database wide parameters that affect the database's performance. These include the OIT, OAT, oldest snapshot transaction number and next transaction number as well as the number of cache buffers currently allocated in the cache manager.

For attachments the username has been available through the API in prior versions but there are other things you need to know about each attachment. These include the IP address of the user, what statements they are running, how long they have been connected and the total amount of I/O to the database.

For active transactions you need to know the transaction number and how long they have been active. Transactions that are never closed are a common source of poor database performance. When performance suffers you not only need to determine if an active transaction is the problem. You also need to identify it and be able to roll it back.

For SQL statements the amount of memory being used and the fraction of CPU time being consumed are important. The number of SQL statements executing and their aggregate resource usage is also important for capacity planning.

Providing the Performance Data

Performance data is provided in the eight temporary tables shown in the following table and is available for the database you are currently connected to.

The Temporary Tables

The diagram below shows the relationships between the temporary tables.

Temporary Table Relationships

Refreshing the Temporary Tables

To refresh the temporary tables commit your transaction and run your SELECT again. The temporary tables are emptied when the transaction that is accessing them ends.

Structure of the Temporary Tables

The following eight tables show the structure of each of the temporary tables. Executing the SHOW SYSTEM command in ISQL will show the temporary tables at the end of the list of system tables.

TMP$DATABASE

TMP$TRANSACTIONS

TMP$ATTACHMENTS

TMP$STATEMENTS

TMP$POOLS

TMP$PROCEDURES

TMP$RELATIONS

TMP$POOL_BLOCKS

Querying the Temporary Tables

To query the InterBase temporary tables you must connect to an InterBase database. The information in the temporary tables is for that database only. For example, if you issue the query

SELECT TMP$USER, TMP$USER_IP_ADDR FROM TMP$ATTACHMENTS 

you will get a list of all of the users connected to the database that you are connected to. If other users are attached to other databases you will not see them in the list. If users work with more than one database on the same server and you want to know the total number of attached users you must connect to each database in turn, query the number of attachemtns, then total the results of all of the queries. The same is true of the other tables. If you query TMP$DATABASE for the number of active transactions you will get the number of active transactions in the database you are connected to.

Querying the TMP$ tables is no different than querying any other tables. You can use all of the features of InterBase SQL. You can also create views and stored procedures that return information from the TMP$ tables.

Useful Database Statustics

The following query returns a number of useful statistics about the database you are connected to. Calculated fields are used to show the difference between the oldest active transaction and the oldest interesting transaction and the ratio of pages read from the cache to pages read from disk.

SELECT 
TMP$DATABASE_PATH, 
TMP$ATTACHMENTS, 
TMP$ACTIVE_THREADS,
TMP$MAXIMUM_MEMORY, 
TMP$TRANSACTIONS, 
TMP$TRANSACTION_COMMITS,
TMP$TRANSACTION_ROLLBACKS, 
TMP$TRANSACTION_DEADLOCKS,
TMP$TRANSACTION_CONFLICTS, 
TMP$NEXT_TRANSACTION, 
TMP$OLDEST_ACTIVE,
TMP$OLDEST_INTERESTING, 
(TMP$OLDEST_ACTIVE - TMP$OLDEST_INTERESTING) AS TMP$INTERESTING_INACTIVE,
TMP$PAGE_READS, 
TMP$PAGE_WRITES, 
TMP$PAGE_FETCHES,
(TMP$PAGE_FETCHES / TMP$PAGE_READS) AS TMP$CACHE_RATIO,
TMP$SWEEP_ACTIVE
FROM TMP$DATABASE;

If you want to see what is happening to your database over time create the following table and stored procedure. Write an application that calls LOG_DB_STATISTICS at regular intervals and you will build a picture of what happens as your database is being used.

CREATE TABLE DB_STATISTICS (
STAT_DATE DATE,
STAT_TIME TIME,
ATTACHMENTS INTEGER,
ACTIVE_THREADS INTEGER,
MAXIMUM_MEMORY INTEGER,
ACTIVE_TRANSACTIONS INTEGER,
COMMITS INTEGER,
ROLLBACKS INTEGER,
DEADLOCKS INTEGER,
CONFLICTS INTEGER,
NEXT_TRANSACTION INTEGER,
OAT INTEGER,
OIT INTEGER,
OAT_MINUS_OIT INTEGER,
PAGE_READS INTEGER,
PAGE_WRITES INTEGER,
PAGE_FETCHES INTEGER,
FETCH_RATIO INTEGER,
SWEEP_ACTIVE CHAR(1)
)

CREATE PROCEDURE LOG_DB_STATISTICS
AS
BEGIN
  INSERT INTO DB_STATISTICS
  SELECT
  CURRENT_DATE,
  CURRENT_TIME,
  TMP$ATTACHMENTS,
  TMP$ACTIVE_THREADS,
  TMP$MAXIMUM_MEMORY,
  TMP$TRANSACTIONS,
  TMP$TRANSACTION_COMMITS,
  TMP$TRANSACTION_ROLLBACKS,
  TMP$TRANSACTION_DEADLOCKS,
  TMP$TRANSACTION_CONFLICTS,
  TMP$NEXT_TRANSACTION,
  TMP$OLDEST_ACTIVE,
  TMP$OLDEST_INTERESTING,
  (TMP$OLDEST_ACTIVE - TMP$OLDEST_INTERESTING) AS TMP$INTERESTING_INACTIVE,
  TMP$PAGE_READS,
  TMP$PAGE_WRITES,
  TMP$PAGE_FETCHES,
  (TMP$PAGE_FETCHES / TMP$PAGE_READS) AS TMP$CACHE_RATIO,
  TMP$SWEEP_ACTIVE
  FROM TMP$DATABASE;
END

The 10 Statements with the Most Execution Time

This query shows the ten statements with the most execution time in descending order.

SELECT a.tmp$user, s.tmp$timestamp, s.tmp$sql, s.tmp$quantum
FROM tmp$statements s, tmp$attachments a
WHERE a.tmp$attachment_id = s.tmp$attachment_id
ORDER BY s.tmp$quantum DESC ROWS 10;

Stored Procedures Executed Most Often

The following statement shows the ten most often executed stored procedures.

SELECT TMP$PROCEDURE_NAME, TMP$INVOCATIONS,
TMP$QUANTUM, TMP$PAGE_READS, TMP$PAGE_WRITES,
(TMP$PAGE_READS + TMP$PAGE_WRITES) AS TMP$PAGE_IO,
TMP$PAGE FETCHES
FROM TMP$PROCEDURES
ORDER BY TMP$INVOCATIONS DESC ROWS 10;

Stored Procedures by Execution Time

This statement shows the ten stored procedures that have the highest total execution time.

SELECT TMP$PROCEDURE_NAME, TMP$INVOCATIONS,
TMP$QUANTUM, TMP$PAGE_READS, TMP$PAGE_WRITES,
(TMP$PAGE_READS + TMP$PAGE_WRITES) AS TMP$PAGE_IO,
TMP$PAGE FETCHES
FROM TMP$PROCEDURES
ORDER BY TMP$QUANTUM DESC ROWS 10;

Active Transactions by Age

This query shows the 50 longest running active transactions in descending order by execution time.

SELECT T.TMP$TRANSACTION_ID, T.TMP$TIMESTAMP, T.TMP$STATE, 
T.TMP$QUANTUM, T.TMP$POOL_MEMORY,
A.TMP$USER, A.TMP$USER_IP_ADDR
FROM TMP$TRANSACTIONS T JOIN TMP$ATTACHMENTS A
ON T.TMP$ATTACHMENT_ID = A.TMP$ATTACHMENT_ID
ORDER BY T.TMP$TIMESTAMP ROWS 50

Active Transactions by Execution Time

This statement returns the top 50 transactions by the amount of execution time they have used.

SELECT T.TMP$TRANSACTION_ID, T.TMP$TIMESTAMP, T.TMP$STATE, 
T.TMP$QUANTUM, T.TMP$POOL_MEMORY,
A.TMP$USER, A.TMP$USER_IP_ADDR
FROM TMP$TRANSACTIONS T JOIN TMP$ATTACHMENTS A
ON T.TMP$ATTACHMENT_ID = A.TMP$ATTACHMENT_ID
ORDER BY T.TMP$QUANTUM DESC ROWS 50

Active Transactions by Memory Usage

This statement selects the top 50 transactions based on the amount of pool memory used.

SELECT T.TMP$TRANSACTION_ID, T.TMP$TIMESTAMP, T.TMP$STATE, 
T.TMP$QUANTUM, T.TMP$POOL_MEMORY,
A.TMP$USER, A.TMP$USER_IP_ADDR
FROM TMP$TRANSACTIONS T JOIN TMP$ATTACHMENTS A
ON T.TMP$ATTACHMENT_ID = A.TMP$ATTACHMENT_ID
ORDER BY T.TMP$POOL_MEMORY DESC ROWS 50

Changes Made by Specified User

This query shows the number of active transactions and the number of rows selected, inserted, updated and deleted by the user specified in the WHERE clause. Note that this query can return multiple rows if the user has multiple connections to the database. If you want the totals for all of the user's connections use the second query below.

SELECT TMP$USER, TMP$USER_IP_ADDR, TMP$TIMESTAMP, TMP$STATE,
TMP$TRANSACTIONS, TMP$RECORD_SELECTS,
TMP$RECORD_INSERTS, TMP$RECORD_UPDATES, TMP$RECORD_DELETES
FROM TMP$ATTACHMENTS
WHERE TMP$USER = 'SYSDBA'

SELECT TMP$USER, 
SUM(TMP$TRANSACTIONS) AS TMP$TRANSACTIONS,
SUM(TMP$RECORD_SELECTS) AS TMP$RECORD_SELECTS,
SUM(TMP$RECORD_INSERTS) AS TMP$RECORD_INSERTS,
SUM(TMP$RECORD_UPDATES) AS TMP$RECORD_UPDATES,
SUM(TMP$RECORD_DELETES) AS TMP$RECORD_DELETES
FROM TMP$ATTACHMENTS
WHERE TMP$USER = 'SYSDBA'
GROUP BY TMP$USER

Attachments by Execution Time

This query lists connections by total execution time.

SELECT A.TMP$USER, A.TMP$USER_IP_ADDR, A.TMP$QUANTUM,
A.TMP$POOL_MEMORY, (A.TMP$PAGE_READS + A.TMP$PAGE_WRITES) AS TMP$PAGE_IO,
A.TMP$TIMESTAMP
FROM TMP$ATTACHMENTS A
ORDER BY A.TMP$QUANTUM DESC

Attachements by I/O

The following SELECT lists all connections in descending order by the total number of disk reads and writes.

SELECT A.TMP$USER, A.TMP$USER_IP_ADDR, A.TMP$QUANTUM,
A.TMP$POOL_MEMORY, (A.TMP$PAGE_READS + A.TMP$PAGE_WRITES) AS TMP$PAGE_IO,
A.TMP$TIMESTAMP
FROM TMP$ATTACHMENTS A
ORDER BY 5 DESC

Updating the Temporary Tables

You can also update some of the temporary tables to control InterBase. Suppose you identify a transaction that has been active for too long and you want to roll it back.

UPDATE TMP$TRANSACTION SET TMP$STATE = 'ROLLBACK'
WHERE (TMP$TRANSACTION_ID = 12345)

To rollback a limbo transaction:

UPDATE TMP$TRANSACTION SET TMP$STATE = 'ROLLBACK'
WHERE (TMP$TRANSACTION_ID = 12345)

To commit a limbo transaction:

UPDATE TMP$TRANSACTION SET TMP$STATE = 'COMMIT'
WHERE (TMP$TRANSACTION_ID = 12345)

To cancel an attachment's current operation:

UPDATE TMP$ATTACHMENTS SET TMP$STATE = 'CANCEL'
WHERE (TMP$ATTACHMENT_ID = 12345)

To shutdown an attachment set the attachment's state to SHUTDOWN. Shutting down an attachment detaches the user from the database and disconnects the user's connection to the server.

UPDATE TMP$ATTACHMENTS SET TMP$STATE = 'SHUTDOWN'
WHERE (TMP$ATTACHMENT_ID = 12345)

To stop an executing statement:

UPDATE TMP$STATEMENTS SET TMP$STATE = 'CANCEL'
WHERE (TMP$STATEMENT_ID = 12345)

Security for Temporary System Tables

InterBase 6.5 introduced the ability to grant and revoke priviledges on the InterBase system tables. The system tables have a default priviledge of SELECT for PUBLIC. The temporary tables used for performance monitoring are accessible by the database owner and SYSDBA only. One of those users must grant access to any other users that require it.

68 Character Metadata Names

The names of objects in an InterBase database are now limited to 67 characters plus a null terminator.

New On Disk Structure - ODS 11

IB 7 introduces a new on disk structure, ODS 11. This new structure is required to support the new Boolean data type and the new 68 character metadata names. IB 7 can still read ODS 10 databases. You can convert a database from ODS 10 to ODS 11 by backing it up and restoring it using IB 7.

New Database File Extension

The default file extension for IB database files has been changed from .GDB to .IB. This change was made to eliminate the conflict between the .GDB extension and the System Restore facility on Windows ME and XP. System Restore automatically copies files with the .GDB extension each time the fiel is touched. This causes performance to suffer. Switching to the .IB extension eliminates this problem.

New Name for the Security Database

The IB security database has been renamed from ISC4.GDB to ADMIN.IB. To continue using your existing security database back it up and restore it using IB 7.

With IB 7 you can use any name you choose for the security database. The ibconfig file contains a new parameter, ADMIN_DB, that specifies the name for the security database. This parameter has the form:

ADMIN_DB DBName.EXT

IB Configuration File Renamed on Linux and Solaris

The IB configuration file, formerly named isc_config on Linux and Solaris, has been renamed ibconfig for consistency across platforms.

New Keywords

The following keywords are new in InterBase 7.

Boolean

True

False

Unknown

The following are likely to become keywords in a future realease.

Global

Temporary

Preserve

Boolean Data Type

InterBase 7 supports a SQL 99 compliant Boolean data type. The Boolean type is implemented as a 16 bit integer and can have one of three values; True, False or Unknown. Unknown indicates that the Boolean field is null.

      Example:

      CREATE TABLE AWARDS_1 (
      IS_ELIGIBLE BOOLEAN, 
      NAME VARCHAR(20)
      );
      
      INSERT INTO AWARDS_1 VALUES(TRUE, 'Jim Smith');
      
      INSERT INTO AWARDS_1 VALUES(FALSE, 'John Butler');

      SELECT * FROM AWARDS_1 WHERE isEligible = TRUE;
    
    

Executing a query, using IBConsole, against a table with a Boolean field will return True, False or Unknown. Executing the same query using the API functions will return 1 for True, 0 for False and NULL for Unknown.

     For ESQL and DSQL programmers, we define the following type in ibase.h:

     define SQL_BOOLEAN 590
    

Goodbye SET TERM

Calling SET TERM to define an alternate terminating character is no longer necessary when creating stored procedures and triggers. InterBase will parse CREATE TRIGGER and CREATE PROCEDURE statements correctly using the default terminator which is a semicolon. SET TERM still works so old scripts will run without change. IBConsole and InterBase Express still require the use of SET TERM.

Improved Security for External Tables

To solve potential security problems with external tables the location for external tables is now restricted. External tables must be in one of two locations.

• The tables must be in the <InterBase Home>\ext directory.
• The external tables must be in the directory specified by the EXTERNAL_FILE_DIRECTORY directive in the ibconfig file.

Client Version Detection

Three new API functions let applications determine the InterBase client version.

• isc_get_client_version()
• isc_get_client_major_version()
• isc_get_client_minor_version()

Type 4 JDBC Driver

InterClient is now a type 4 JDBC driver. It is no longer necessary to install InterServer on your InterBase server to use JDBC connectivity.

If you do not wish to upgrade all of your clients to the new type 4 driver you can continue to run InterServer on your InterBase server. with InterServer running both type 3 and type 4 InterClient drivers will be able to connect to your InterBase server.

To upgrade clients to the new type 4 driver copy the new interclient.jar file to the machine and make sure it is the first instance of interclient.jar on the CLASSPATH.

(C) Copyright 2002 by Bill Todd - All Rights Reserved. Reproduction in any form without the written consent of the author is prohibited.