:meta-keywords: cubrid logging, slow query, error log, deadlock detect, cubrid fail-over, cubrid fail-back :meta-description: Troubleshoot CUBRID database and High Availability nodes by consulting logs. *************** Troubleshooting *************** .. _sql-log-check: Checking SQL Log ================ SQL log of CAS -------------- When a specific error occurs, generally you can check SQL logs of broker application server(CAS) One SQL log file is generated per each CAS; it is hard to find an SQL log in which an error occurred because SQL log files are many when CAS processes are many. However, SQL log file name includes CAS ID in the end part, you can find easily if you know the CAS ID in which an error occurred. .. note:: SQL log file name in a CAS is _.sql.log(see :ref:`broker-logs`); is CAS ID. Function getting CAS information -------------------------------- :func:`cci_get_cas_info` function or :ref:`cubrid.jdbc.driver.CUBRIDConnection.toString() method in JDBC `) prints out the information including the broker host and CAS ID in which the query is executed when the query is run; with this information, you can find an SQL log file of that CAS easily. :: :,, e.g. 127.0.0.1:33000,1,12916 Application log --------------- If you specify the connection URL for printing out the log in the application, you can check the CAS ID which brought an error when an error occurs in the specific query. The following are examples that an application log is written when an error occurs. **JDBC application log** :: Syntax: syntax error, unexpected IdName [CAS INFO - localhost:33000,1,30560],[SESSION-16],[URL-jdbc:cubrid:localhost:33000:demodb::********:?logFile=driver_1.log&logSlowQueries=true&slowQueryThresholdMillis=5]. **CCI application log** :: Syntax: syntax error, unexpected IdName [CAS INFO - 127.0.0.1:33000, 1, 30560]. Slow query ---------- When a slow query occurs, you should find the reason of a slow query by an application log and an SQL log of CAS. To find where the cause exists when slow query occurs(in the application-broker section? or in the broker-DB section?), you should check application log or SQL log of CAS because CUBRID is composed of 3-tiers; application-broker-DB server. There is a slow query in the application log but that is not printed as slow query in the SQL log of CAS; then there will be a cause to make the speed low in the application-broker section. Some examples are as below. * Check if there is a low speed of the network between application and broker. * Check if there is a case where CAS was restarted by checking the broker log (located in the **$CUBRID/log/broker** directory). If it is revealed that the number of CASes is insufficient (please tune for any slow queries first), you should increase the number of CASes. To do so, the value of :ref:`MAX_NUM_APPL_SERVER ` should be increased appropriately. Also, increase the value of :ref:`max_clients ` if needed. If application log and CAS SQL log show the slow query log together and there is almost no gab between the slow query times of application log and the CAS SQL log, the cause which the query was slow will exist between the broker and DB server. For example, the query execution in the DB server was slow. There are examples of each application log when a slow query occurs. **JDBC application log** :: 2013-05-09 16:25:08.831|INFO|SLOW QUERY [CAS INFO] localhost:33000, 1, 12916 [TIME] START: 2013-05-09 16:25:08.775, ELAPSED: 52 [SQL] SELECT * from db_class a, db_class b **CCI application log** :: 2013-05-10 18:11:23.023 [TID:14346] [DEBUG][CONHANDLE - 0002][CAS INFO - 127.0.0.1:33000, 1, 12916] [SLOW QUERY - ELAPSED : 45] [SQL - select * from db_class a, db_class b] Slow query information in an application and in a broker is stored in each file when the setting is as following. * The slow query information in application is stored in application log file when the value of **logSlowQueries** property in the connection URL is set to **yes** and the value of **slowQueryThresholdMillis** is set; it is stored to the application logfile specified with the **logFile** property (see :func:`cci_connect_with_url` and :ref:`jdbc-connection-conf`). * The slow query information in broker is stored in the $CUBRID/log/broker/sql_log directory when **SLOW_LOG** of :ref:`broker-configuration` is set to ON and **LONG_QUERY_TIME** is set. Server Error Log ================ You can get various information from the server error log by setting **error_log_level** parameter in cubrid.conf. The default of **error_log_level** is **NOTIFICATION**. For how to set this parameter, see :ref:`error-parameters`. .. 4957 .. 10703 Detecting Overflow Keys or Overflow Pages ------------------------------------------ When overflow keys or overflow pages occur, **NOTIFICATION** messages are written to the server error log. Through this message, users can detect DB performance became slow because of overflow keys or overflow pages. If possible, overflow keys or overflow pages should not appear. That is, it is better not to use the index on the big size column, and not to define the record size largely. :: Time: 06/14/13 19:23:40.485 - NOTIFICATION *** file ../../src/storage/btree.c, line 10617 CODE = -1125 Tran = 1, CLIENT = testhost:csql(24670), EID = 6 Created the overflow key file. INDEX idx(B+tree: 0|131|540) ON CLASS hoo(CLASS_OID: 0|522|2). key: 'z ..... '(OID: 0|530|1). ........... Time: 06/14/13 19:23:41.614 - NOTIFICATION *** file ../../src/storage/btree.c, line 8785 CODE = -1126 Tran = 1, CLIENT = testhost:csql(24670), EID = 9 Created a new overflow page. INDEX i_foo(B+tree: 0|149|580) ON CLASS foo(CLASS_OID: 0|522|3). key: 1(OID: 0|572|578). ........... Time: 06/14/13 19:23:48.636 - NOTIFICATION *** file ../../src/storage/btree.c, line 5562 CODE = -1127 Tran = 1, CLIENT = testhost:csql(24670), EID = 42 Deleted an empty overflow page. INDEX i_foo(B+tree: 0|149|580) ON CLASS foo(CLASS_OID: 0|522|3). key: 1(OID: 0|572|192). .. 9620 Detecting log recovery time --------------------------- When DB sever is started or backup volume is restored, you can check the duration of the log recovery by printing out the **NOTIFICATION** messages, the starting time and the ending time of the log recovery, to the server error log or an error log file of restoredb. In these messages, the number of logs and the number of log pages to redo are written together. :: Time: 06/14/13 21:29:04.059 - NOTIFICATION *** file ../../src/transaction/log_recovery.c, line 748 CODE = -1128 Tran = -1, EID = 1 Log recovery is started. The number of log records to be applied: 96916. Log page: 343 ~ 5104. ..... Time: 06/14/13 21:29:05.170 - NOTIFICATION *** file ../../src/transaction/log_recovery.c, line 843 CODE = -1129 Tran = -1, EID = 4 Log recovery is finished. .. 6128 Detecting a Deadlock -------------------- Locks related information is written to the server error log. The server error log file is saveed in the **$CUBRID/log/server** directory as named in *__