Sybase IQ direct IO 问题导致无法启动解决办法

症状:

I. 06/23 10:28:33. SAP IQ

I. 06/23 10:28:33. Version 16.0

I. 06/23 10:28:33. (64bit mode)

I. 06/23 10:28:33. Copyright 1992-2014 by SAP AG or an SAP affiliate company. All rights reserved

I. 06/23 10:28:33. Copyright (c) 2014 SAP AG or an SAP affiliate company.

I. 06/23 10:28:33. All rights reserved.

I. 06/23 10:28:33. Use of this software is governed by the Sybase License Agreement.

I. 06/23 10:28:33. Refer to http://www.sybase.com/softwarelicenses.

I. 06/23 10:28:33. 

I. 06/23 10:28:33. Processors detected: 4 (containing 64 logical processors)

I. 06/23 10:28:33. Maximum number of processors the server will use: 4 physical processor(s), 32 core(s)

I. 06/23 10:28:33. Running Linux 2.6.32-279.el6.x86_64 #1 SMP Wed Jun 13 18:24:36 EDT 2012 on X86_64

I. 06/23 10:28:33. Server built for X86_64 processor architecture

W. 06/23 10:28:33. Direct IO disabled for file '/sybiq/data/sgmsiq.db' because transparent hugepage support is enabled and madvise is not supported

I. 06/23 10:28:33. 262144K of memory used for caching

I. 06/23 10:28:33. Minimum cache size: 262144K, maximum cache size: 524288K

I. 06/23 10:28:33. Using a maximum page size of 4096 bytes

I. 06/23 10:28:33. Multiprogramming level: 105

I. 06/23 10:28:33. Automatic tuning of multiprogramming level is disabled

I. 06/23 10:28:34. Starting database "sgmsiq" (/sybiq/data/sgmsiq.db) at Fri Jun 23 2017 10:28

W. 06/23 10:28:34. Direct IO disabled for file '/sybiq/data/sgmsiq.db' because transparent hugepage support is enabled and madvise is not supported

I. 06/23 10:28:34. Database recovery in progress

I. 06/23 10:28:34.     Last checkpoint at Fri Jun 23 2017 03:43

I. 06/23 10:28:34.     Checkpoint log...

DBSPAWN ERROR:  -80

Unable to start database server

启动失败。
官方解决办法:

Symptom

  • When running a newer version of SQL Anywhere 12 or 16  on Linux, it's possible to see performance degradation versus a Windows installation.
  • On SQL Anywhere 11, SQL Anywhere 12 or SQL Anywhere 16, you see the following console message printed in the SQL Anywhere console log ():

Direct IO disabled for file '<filename>' because transparent hugepage support is enabled and madvise is not supported

Environment

  • SQL Anywhere 11
    SQL Anywhere 12
    SQL Anywhere 16
  • Linux operating system, with kernel 2.6.38 or greater

Cause

  • A problem was discovered via CR #728597 (fixed in 11.0.1.2926, 12.0.1.3842, 16.0 GA) that I/O operations can silently fail on Linux when using Direct I/O operations (O_DIRECT)
    • If a kernel with 'transparent huge page' (THP) support enabled is detected, SQL Anywhere will disable direct I/O operations for that platform with the 'Direct IO disabled' message printed in the console log
    • The problem with Direct IO operations has been reported as bug 891857 with RedHat
  • CR #740799 (fixed in 12.0.1.3953, 16.0.1636) solves a problem where there was a noticable speed difference in operation between Linux and Windows

Resolution

  • For SQL Anywhere 12 and 16 to resolve CR #728597, apply a SQL Anywhere Support Package higher than 12.0.1.3953 or 16.0.1636
  • To remove the Direct I/O message in the console log, you can either choose to disable transparent huge page (THP) support across the Linux operating system, or disable direct I/O within SQL Anywhere. Direct I/O is used to increase performance of the database server by not using the operating system disk cache.
    • To disable THP across the Operating System, use one of the following methods:
      • echo never > /sys/kernel/mm/transparent_hugepage/enabled
        echo never > /sys/kernel/mm/redhat_transparent_hugepage/enabled
      • Boot with the kernel flag 'transparent_hugepage=never'
    • To disable Direct I/O within SQL Anywhere, use one of the following methods:
      • Set the on the database server command-line
      • Set the 'SA_DISABLE_DIRECTIO=1' environment variable for the SQL Anywhere environment where SQL Anywhere is started from

 实际解决办法:

使用IQ 强制启动方式启动IQ数据库,下面是这种方法的操作步骤:

 

1、 用OS的cp命令备份.db 和 .log文件到其他的文件系统

 

2、使用如下方式启动IQ数据库

start_iq -gm 1 -gd DBA @sgmsiq.cfg -iqfrec sgmsiq sgmsiq.db

 

3、 如果使用-iqfrec 命令启动成后,使用dbisql 工具登录到IQ 数据库执行以下命令

    sp_iqcheckdb 'dropleaks database'

     

     上述命令执行成功后,执行第4步。

 

4、 执行sp_iqcheckdb 'allocation database'

如果执行完后显示"Freelist Updated" 消息表示dbcc检查成功。如果发现错误,

那么返回"Freelist Not Updated"和"Errors Detected"

 

5、 以正常方式启动IQ数据库

 

 

start_iq @sgmsiq.cfg sgmsiq.db

 

一点感想:

  1. 为什么IQ总是逼我用非主流办法来解决这些问题呢?
  2. Sybase数据库确实不行。如果忘记了IQ的dba密码,恭喜你,数据库永远无法登陆和恢复了,wtf;
  3. 不知道国内还活着几个sybase工程师,我是其中一个。

标签: sybase IQ