The following are the two most common causes of server unresponsiveness, which can be detected by looking in the Sybase IQ message file:
Insufficient disk space. See the section “Insufficient disk space” for actions to take.
Insufficient room in main or temp buffer cache. See “Managing buffer caches” in Chapter 4, “Managing System Resources” of the Performance and Tuning Guide.
If your server seems to be prone to unresponsiveness, either while processing or during shutdown, use the start_iq command line option -z and the Sybase IQ database option QUERY_PLAN = 'ON' to log useful information in the Sybase IQ message (.iqmsg) and server log (.srvlog) files. In addition to logging this information, there are other steps you can take to determine the cause of the problem:
Check both the Sybase IQ message
file and the server log file for You have run
out of space... messages. If you have run out
of IQ main store or IQ temporary store, add the appropriate dbspace
with the CREATE DBSPACE command. See the section “Insufficient disk space” for more information
on resolving out of space issues.
Setting the database options MAIN_RESERVED_DBSPACE_MB and TEMP_RESERVED_DB_SPACE_MB to large enough values to handle running out of space during a DDL COMMIT or CHECKPOINT is also important. A few hundred MB should be enough, but these options can be set higher for a large database. For more information, see the section “IQ main store and IQ temporary store space management”.
Determine if the Sybase IQ server process (iqsrv15) is consuming CPU cycles by monitoring the CPU usage for a few minutes at the operating system level. Record this information. If the CPU usage changes, then the Sybase IQ server process should be processing normally.
If the Sybase IQ server CPU usage is normal, you can examine what the server is doing, i.e., what statement the server is currently executing. For details on capturing this information and logging server requests, see the sections “Finding the currently executing statement” and “Logging server requests”.
If there are no out of space indications, use dbisql on a new or existing connection to gather the information listed in Table 14-1, in the specified order.
Command |
Informational purpose |
|---|---|
SELECT db_name() |
Database name |
CHECKPOINT |
Checkpoint can succeed |
sa_conn_properties ># sa_conn_properties.out |
Connection information |
sa_conn_info ># sa_conn_info.out |
Connection information |
sa_db_properties ># sa_db_properties.out |
Database property information |
sa_eng_properties ># sa_eng_properties.out |
Server property information |
sp_iqstatus ># sp_iqstatus.out |
Database status information |
sp_iqconnection ># sp_iqconnection.out |
Connection information |
sp_iqtransaction ># sp_iqtransaction.out |
Transaction information |
If you cannot resolve the issue, contact Sybase Technical Support for assistance. They can use the information you have just gathered to help diagnose the problem. See “Reporting problems to Technical Support”.
When the server is unresponsive, you can generate a stack trace for each Sybase IQ thread by creating a file named DumpAllThreads or dumpallthreads in the $IQDIR15/logfiles directory (the %ALLUSERSPROFILE\%\SybaseIQ\logfiles folder on Windows 32 and 64 platforms, C:\ProgramData\SybaseIQ\logfiles for Vista 64).
Starting Sybase IQ as recommended, using the Program Manager or start_iq command, sets the IQDIR15 variable automatically. If the IQDIR15 variable is not set, create the DumpAllThreads file in the directory in which iqsrv15 was started.
The Sybase IQ server detects the presence of the DumpAllThreads file and writes a stack trace for each IQ thread in the stack trace file stktrc-YYYYMMDD-HHNNSS_#.iq. After the stack traces are written to the stack trace file, the DumpAllThreads file is deleted.
This stack trace information can be used by Sybase Technical Support to help diagnose the problem. See “Reporting problems to Technical Support”.
If you can connect to the database, run the IQ UTILITIES buffer cache monitor on the main and temp (private) buffer caches for 10 minutes with a 10 second interval:
Connect to the database or use the existing connection.
CREATE TABLE #dummy_monitor(c1
INT);
IQ UTILITIES MAIN INTO #dummy_monitor
START MONITOR
'-append -debug -interval 10 -file_suffix
iqdbgmon';
IQ UTILITIES PRIVATE INTO #dummy_monitor
START MONITOR '-append -debug -interval
10
-file_suffix iqdbgmon';
Let the process run for 10 minutes, then stop
the buffer cache monitor:
IQ UTILITIES MAIN INTO #dummy_monitor
STOP MONITOR;
IQ UTILITIES PRIVATE INTO #dummy_monitor
STOP MONITOR;
For more information on monitoring buffer caches, see the section “Monitoring the buffer caches” in Chapter 5, “Monitoring and Tuning Performance” of the Performance and Tuning Guide.
Check near the end of the Sybase IQ message
file for the message Resource count 0,
which may be followed by an Open Cursor message.
These messages indicate a resource depletion, which can cause a
deadlock. The immediate solution is to reduce the number of active connections
using CTRL-C or the DROP CONNECTION command.
The long term solution to avoid a deadlock due to resource depletion is one or a combination of the following:
Restrict the number of users on the server by reducing the value of the -gm server startup option
Add another secondary server to a multiplex
Increase the processing capacity of the hardware by adding CPUs
