Improving performance with system catalog caching

This topic includes the following sections:

 

Important

It is important that you carefully follow the instructions in this topic to ensure that system catalogs are loaded and unloaded correctly, and that shared memory is allocated and freed correctly.

On UNIX, only system administrators should attempt to use system catalog caching. We’ve tested the procedures documented here on our systems, but don’t guarantee that they will work on all systems because shared memory mechanisms vary from one operating system to another.

Caching system catalogs

You can improve performance by instructing xfODBC to cache system catalogs. When a system catalog is cached, the xfODBC driver consults the cached catalog in memory rather than rereading the catalog from disk for each new command.

To cache system catalogs,

1. Add a syngenload command to one of the following files (which are in the synergyde\connect directory):

As distributed, these files include a sample syngenload command that’s been commented out. To use the command, uncomment the line and change the username, password, and connect file. See Using syngenload below for information on the syntax.

If necessary, you can run syngenload from the command line (see Running syngenload from the command line (Windows and OpenVMS) below). However, we recommend specifying the syngenload command in one of the above files, which are read each time the Synergy/DE OpenNet Server service (SynSQL) is started.

2. On UNIX or OpenVMS, do one of the following: 
rm $GENESIS_HOME/synodbccache.dat

$vortexipc:==$CONNECTDIR:vortexipc.exe
$vortexipc /d
$vortexipc /c 1000 0 0

Note the following:

Using syngenload

To load (cache) a system catalog into memory or unload a system catalog from memory, use syngenload with the -l or -u option. The syngenload program is in the connect directory and has the following syntax:

syngenload -option

where option is one of the following:

-l "user/pswd" connect_file

Cache the system catalog stored in the directory specified in the dictsource line of connect_file. User must be one of the users defined in the system catalog, and pswd must be the password for the user. The quotes around user/pswd are required.

-m user/pswd connect_file base_size

Display shared memory segment addresses. User must be one of the users defined in the system catalog, and pswd must be the password for the user. See Adjusting the shared memory subsystem settings below for more information.

-u connect_file

Unload the system catalog stored in the directory specified in the dictsource line of connect_file.

/?

Display a list of syngenload options.

For example, the following command loads the system catalog specified by the sodbc_sa connect file using the default DBADMIN administrative user:

syngenload -l "DBADMIN/MANAGER" sodbc_sa

The next example unloads the system catalog specified by the sodbc_sa connect file:

syngenload -u sodbc_sa

When loading a system catalog, syngenload opens the system catalog files—just like an ODBC-enabled application. So if an ODBC-enabled application can’t open the system catalog, syngenload won’t be able to either.

Note the following:

Running syngenload from the command line (Windows and OpenVMS)

Important

If necessary, you can run syngenload from the command line for short-term testing purposes. However, we recommend putting the syngenload command in one of the files listed in step 1 above. These are read each time the SynSQL service is started.

Before running syngenload from the command line on Windows or OpenVMS, you must set the VORTEX_SHM_FILE environment variable to synodbccache.dat in the connect\synodbc directory. (On UNIX, this is set for you.) For example:

set VORTEX_SHM_FILE=%GENESIS_HOME%\synodbccache.dat

On Windows, you must also stop and then restart vtxshm before running syngenload:

1. If vtxshm is running (check this in Windows Task Manager), stop it by doing one of the following: 
vtxshm -t

Vtxshm is a part of the xfODBC caching mechanism and is automatically started by the SynSQL service. However, when vtxshm has been started by SynSQL, it isn’t available to syngenload if you start syngenload from the command line.

2. Start vtxshm from the command line: 
start /B vtxshm -s

You can now run syngenload from the command line.

Using logging to determine if a system catalog is cached

To determine if a system catalog is cached or has been unloaded from shared memory, use Synergy driver logging or Synergy DBMS logging.

Synergy driver logging

Synergy driver logging lists the path and name of the shared memory file and lists errors encountered while attempting to use shared memory. To use Synergy driver logging, add the following lines to your connect file:

logfile file_spec
loglevel 1

where file_spec is the path and filename of the log file you want to create. For example, here’s the sample connect file with these lines:

dictsource "C:\Program Files\Synergex\SynergyDE\connect\synodbc\dict\"
datasource ";C:\\Program Files\\Synergex\\SynergyDE\\connect\\synodbc\\dat;"
XFDBTUT=C:\Program Files\Synergex\SynergyDE\connect\synodbc\dat
logfile c:\temp\connect.log
loglevel 1

Note the following:

Synergy DBMS logging

Synergy DBMS logging tells you if the system catalog files are open and logs reads to the files. (These files are named GENESIS_* and are listed in the System Catalog Files table.) If the system catalog files are read as you access your database with xfODBC, the system catalog is not cached. (If the system catalogs are cached, the files will be read to create the cache, but not to access data.) For information on Synergy DBMS logging, see Synergy DBMS logging.

Troubleshooting system catalog caching

Following are some common problems that can occur with system catalog caching.

Cannot allocate shared memory

On Windows, if you get a “cannot allocate shared memory” error, the Synergy/DE OpenNet Server service (SynSQL) probably isn’t running. If you’re having trouble starting SynSQL, check the Windows event log for information. The sqld program has an -l option that writes more detailed information to the Windows event log. See sqld program for information.

On OpenVMS, this error may indicate that one or more of the vortexipc lines in STARTNET.COM is commented out. See step 2 in Caching system catalogs above for information.

Cannot attach to shared memory (Windows)

If you attempt to run syngenload from the command line, you may get the following error: “ERROR: SHARED MEMORY error: Cannot attach to shared memory: The operation completed successfully.” This may indicate that syngenload cannot access the vtxshm program. If you see this, follow the instructions in Running syngenload from the command line (Windows and OpenVMS) above.

Invalid parameter or argument (UNIX)

If you encounter an “Invalid Parameter” or “Invalid Argument” error when caching a system catalog on a UNIX system, you will need to adjust shared memory subsystem settings. The syngenload program uses this subsystem when you instruct it to load a system catalog. There are two shared memory subsystem settings for caching:

Before you make any adjustments, however, try caching your system catalog by using syngenload from the command line. If you get an “Invalid Parameter” or “Invalid Argument” error, follow the steps in Adjusting the shared memory subsystem settings below. If you don’t get an error, do the following:

1. Set Synergy driver logging. (See Enabling Synergy driver logging.)
2. Use an ODBC-enabled application to connect to the database. Then check the file created by Synergy driver logging to see if there is an “Invalid Parameter” or “Invalid Argument” error after the synodbccache.dat entry. If there is, follow the steps in Adjusting the shared memory subsystem settings below. If you don’t see an error after this entry, caching is working correctly.
3. Turn off Synergy driver logging.

Adjusting the shared memory subsystem settings

For most UNIX systems, you’ll need to adjust the shared memory segment size, but you may not need to set the base address. On Linux systems, however, it’s almost certain that you’ll need to set the base address as well. In either case, follow the instructions below.

1. Reboot your machine.
2. Make sure there is no synodbccache.dat file in the $CONNECTDIR/synodbc directory. If it’s there, delete it.
3. Estimate the size of your cache by totaling the size (in bytes) of all the GENESIS*.is1 files in your system catalog. Then add 100,000 to the sum. You’ll use this figure as the base size (base_size in step 4 and step 6) for shared memory segment settings.
4. Use the tman utility, a memory analyzer, to determine if there is enough shared memory space. Use the following syntax: 
tman -n base_size base_size

If this causes an error, ask your system administrator to reconfigure the shared memory subsystem to allow a maximum size that’s at least twice as large as base_size. (This usually requires a system reboot.)

5. Divide base_size by 1,024. Then open trim.ini in a text editor and set shmem_seg_size to the result of the calculation. (The shmem_seg_size setting specifies a value in kilobytes.)

Note that the goal in setting shmem_seg_size is to create a shared memory segment that’s large enough for the entire system catalog, which is very important if you also set VORTEX_SHM_BASE. When this environment variable is set, the system catalog must be in a single shared memory segment. (If it’s split between two or more segments, they must be contiguous—something we can’t control.)

6. Use syngenload to cache your system catalog. If you get an “Invalid Parameter” or “Invalid Argument” error, try increasing the shmem_seg_size setting in trim.ini by 500. Then go back to step 4, increasing base_size by 512,000. If you still get the error, use syngenload with the following syntax: 
syngenload -m user/pwd connect_file base_size

This will display three shared memory segment addresses. 

VORTEX_SHM_BASE values for size 512000
SID: 1376262, VORTEX_SHM_BASE: 00303040 Native: 40303000
SID: 1409031, VORTEX_SHM_BASE: 00003840 Native: 40380000
SID: 1441800, VORTEX_SHM_BASE: 00D03F40 Native: 403FD000

Now set the VORTEX_SHM_BASE environment variable to the first address (in the example above, this is 00303040), and start again at step 4. (See VORTEX_SHM_BASE for information on setting this environment variable.)

Note that the goal in setting VORTEX_SHM_BASE is to set the base address to one that’s high enough to make the cache available to all programs that use it. (For example, if you don’t set this environment variable and the Synergy runtime is loaded into memory, the address may not be available; the runtime may have allocated it for something else before the cache is attached.) If you find in the final step that the first shared segment address listed isn’t high enough, set VORTEX_SHM_BASE to the second address listed—then the third if the second doesn’t work.

7. Check the synodbccache.dat file to ensure that the shared memory ID is the same for all entries. (This file is generated in the $CONNECTDIR/synodbc directory when a system catalog is cached.) Check this by comparing values in the second column (the shared memory ID column) of the synodbccache.dat file. All entries in the second column must have the same value. For example: 
SODBC_SA_GENESIS_TABLES    1507332  00000008  65536  0      5048
SODBC_SA_GENESIS_COLUMNS   1507332  00000008  65536  5048   42108
SODBC_SA_GENESIS_INDEXES   1507332  00000008  65536  47156  7768
SODBC_SA_GENESIS_XCOLUMNS  1507332  00000008  65536  0      12004

(The third column lists base addresses. The sixth column lists the size of cached entries.)

8. If the shared memory ID is not the same for all entries, the cache has been loaded into more than one segment, which may cause errors. In this case, add the total size of the generated cache (the sum of the values in the sixth column of the synodbccache.dat file), unload the cache using syngenload, and start again at step 4.
9. Once you’ve made it to this step, syngenload should be able to load the system catalog correctly. But we need to find out if the Synergy driver can access the cache. To do this, set Synergy driver logging, use an ODBC-enabled application to access your Synergy database, and then check the log. If you see an “Invalid Parameter” error after the synodbccache.dat entry, you need to use a higher address for shared memory; go back to step 6. If you don’t get this error after the synodbccache.dat entry, caching is working correctly.
10. Turn off Synergy driver logging.

Viewing and removing shared segments

You can use the UNIX ipcs utility to view and remove shared memory segments that are in use. For example, if you’ve deleted the synodbccache.dat file, you can use ipcs to remove shared memory segments that are still allocated for caching. The second column lists shared memory IDs (which correspond to the shared memory IDs listed in synodbccache.dat). 

------ Shared Memory Segments --------
key        shmid    owner    perms    bytes     nattch     status
0x00000000 196608   maryw    644      65536     0
0x00000000 229377   maryw    644      65536     0
0x00000000 491522   maryw    644      65536     0
0x00000000 524291   maryw    644      65536     0
0x00000000 1507332  root     644      10240000  0

Correcting other caching problems

If you find that system catalogs are not loading or unloading correctly, try the following.

On Windows,

1. Stop the Synergy/DE OpenNet Server service (SynSQL). (See Stopping and removing SQL OpenNet.)
2. Open the Windows Task Manager and end the task named vtxshm if it exists.
3. Delete the synodbccache.dat file in your GENESIS_HOME directory.
4. Follow the caching instructions from the beginning.

On UNIX and OpenVMS,

1. Delete the synodbccache.dat file (in your GENESIS_HOME directory).
2. Reboot the system.
3. Follow the caching instructions from the beginning.