IDS Forum

Re: IDS 11.50/AIX 5.3 Slowness Issue?

Posted By: Art Kagel
Date: Monday, 11 April 2011, at 12:39 p.m.

In Response To: IDS 11.50/AIX 5.3 Slowness Issue? (Eric Rowell)

Are the chunks using RAW device, COOKED device, or COOKED filesystem files?
If COOKED FILES, do you have O_DIRECT enabled and set to "2" to enable
CONCURRENT_IO? If COOKED FILES, what is the filesystem type and what is the
AIX IO Queue Depth set to (is the that "32" below)? Are the temp dbspaces
on the same physical SAN structure as the data dbspaces for this table?
What is the underlying structure of the physical array containing the data
chunk(s) (ie RAID level, stripe block size, etc.)? How much cache on the
SAN? How much data is in this table? Pagesize of the dbspace(s)
containing the table's partition(s)?

What are the IO service times on the chunks as reported by Informix (onstat
-g iof)? On the tables (onstat -g ppf)? How large is the buffer cache for
this table's dbspace(s)?

Art

Art S. Kagel
Advanced DataTools (www.advancedatatools.com)
Blog: http://informix-myview.blogspot.com/

Disclaimer: Please keep in mind that my own opinions are my own opinions and
do not reflect on my employer, Advanced DataTools, the IIUG, nor any other
organization with which I am associated either explicitly, implicitly, or by
inference. Neither do those opinions reflect those of other individuals
affiliated with any entity with which I am affiliated nor those of the
entities themselves.

On Mon, Apr 11, 2011 at 12:20 PM, Eric Rowell <erowell@gmail.com> wrote:

> OK I have sent the following to support but because it is a
> performance issue we are on a slow boat traveling in circles.
>
> I'm sure this is related to a configuration issue but I cannot figure
> which. I've got a feeling I'm overlooking something very easy.
>
> Any suggestions on what area I should focus on would be helpful. I
> have tried OS related and Informix Configuration changes to no good
> conclusion (I can list if anyone would like... 100 or so changes made
> and reverted). Masters of Informix guide me in my time of darkness.
>
> Problem:
> When running anything from within Informix we get no more than
> 5-7MB/sec (as seen by looking at the SAN). When selecting we get very
> flat activity until the process is done. My Laptop running VMware
> with Informix runs faster...
>
> Baseline Information:
> This occurs if the connection is made via Local Loopback (Apps
> connect here), Remotely (DW pulls) or Shared Memory (Admins).
> Server is 90% Idle when queries run.
> When accessing the commandline, data can be moved at a rate between
> 75-125MB/sec using dd. This includes to and from the informix
> chuncks.
>
> Configuration:
> IDS: 11.50
> AIX: 5.3 TL9
> 4GL: 7.50 (Application is local to the DB Server)
> Vxfs: 5.0.3 (Used to provide replication - Planing to remove ASAP)
>
> IBM P520 (2 CPU - Dual Core, 8G, Dual 2G HBA)
> Compellent Model 30 SAN 8G Fiber connected via switch (Disabled
> Tiering for this server for testing Tier 1 FC Storage)
>
> AIX AIO Settings: (Have tried smaller and larger values with no difference)
> MIN: 200
> MAX: 800
> REQ: 16384
> PRI: 39
> State: Available
> Fast Path: enable
>
> Test Query Per support (Queries take 30 minutes to run, dd of the data
> is less then 3 minutes):
> time dbaccess product <<!
>
> UNLOAD TO /dev/null
> SELECT * FROM job_rules;
> !
>
> Test Query Per support:
> time dbaccess product <<!
> CREATE TEMP TABLE speed_test(
>
> dept_code char(3) not null ,
>
> community char(2) not null ,
>
> lot char(4) not null ,
>
> unit_id char(2),
>
> optid char(7) not null ,
>
> option_category char(3) not null ,
>
> group_seq integer,
>
> gen_flag char(1),
>
> create_per char(10)
>
> default user,
>
> create_dt date
>
> default today,
>
> updat_per char(10)
>
> default user,
>
> updat_dt date
>
> default today,
>
> updat_time datetime hour to second
>
> default current hour to second
>
> ) FRAGMENT BY ROUND ROBIN IN tempdbs01, tempdbs02, tempdbs03, tempdbs04;
>
> INSERT INTO speed_test
> SELECT * FROM job_rules
> !
>
> Fiber Card Performance:
> FC SCSI Adapter Driver Information
> No DMA Resource Count: 0
> No Adapter Elements Count: 0
> No Command Resource Count: 0
>
> IP over FC Traffic Statistics
> Input Requests: 0
> Output Requests: 0
> Control Requests: 0
> Input Bytes: 0
> Output Bytes: 0
>
> FC SCSI Traffic Statistics
> Input Requests: 542306
> Output Requests: 2000560
> Control Requests: 64
> Input Bytes: 9420192051
> Output Bytes: 25358181400
>
> HBA Configuration:
> FC Adapter fcs2
> Description FC Adapter
> Status Available
> Location 0B-08
> Maximum number of COMMANDS to queue to the adapter [2048] +#
> Maximum Transfer Size [0x1000000] +
> Preferred AL_PA [0x1] +
> INIT Link flags [pt2pt] +
> Long term DMA [0x8000000] +
>
> Disk Device Configuration has been adjust up and down but doesn't
> effect the runtime of Informix. dd does see beter response with a
> higher queue depth than the default 32.
>
>
>
> *******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>

--20cf3071ced8947de504a0a7370c

Messages In This Thread

• IDS 11.50/AIX 5.3 Slowness Issue?
  Eric Rowell -- Monday, 11 April 2011, at 12:20 p.m.
  • Re: IDS 11.50/AIX 5.3 Slowness Issue?
    Art Kagel -- Monday, 11 April 2011, at 12:39 p.m.
  • Re: IDS 11.50/AIX 5.3 Slowness Issue?
    Eric Rowell -- Monday, 11 April 2011, at 1:07 p.m.
    • Re: IDS 11.50/AIX 5.3 Slowness Issue?
      Fernando Nunes -- Monday, 11 April 2011, at 1:54 p.m.
    • Re: IDS 11.50/AIX 5.3 Slowness Issue?
      Eric Rowell -- Tuesday, 12 April 2011, at 3:54 p.m.
    • Re: IDS 11.50/AIX 5.3 Slowness Issue?
      Eric Rowell -- Tuesday, 12 April 2011, at 3:57 p.m.
      • Re: IDS 11.50/AIX 5.3 Slowness Issue?
        John Miller iii -- Tuesday, 12 April 2011, at 6:44 p.m.
        • Re: IDS 11.50/AIX 5.3 Slowness Issue?
          Eric Rowell -- Thursday, 19 May 2011, at 4:28 p.m.
          • Re: IDS 11.50/AIX 5.3 Slowness Issue?
            Art Kagel -- Thursday, 19 May 2011, at 7:08 p.m.
            • Re: IDS 11.50/AIX 5.3 Slowness Issue?
              Eric Rowell -- Friday, 20 May 2011, at 9:44 a.m.
    • Re: IDS 11.50/AIX 5.3 Slowness Issue?
      Eric Rowell -- Tuesday, 12 April 2011, at 3:59 p.m.
  • Re: IDS 11.50/AIX 5.3 Slowness Issue?
    MARK JALKIEWICZ -- Monday, 11 April 2011, at 3:41 p.m.
    • Re: IDS 11.50/AIX 5.3 Slowness Issue?
      Eric Rowell -- Monday, 11 April 2011, at 4:01 p.m.
  • Re: IDS 11.50/AIX 5.3 Slowness Issue?
    Art Kagel -- Monday, 11 April 2011, at 4:06 p.m.
    • Re: IDS 11.50/AIX 5.3 Slowness Issue?
      rroussey@comcast.net -- Monday, 11 April 2011, at 4:36 p.m.
      • Re: IDS 11.50/AIX 5.3 Slowness Issue?
        Eric Rowell -- Tuesday, 12 April 2011, at 4:18 p.m.
      • Re: IDS 11.50/AIX 5.3 Slowness Issue?
        Eric Rowell -- Tuesday, 12 April 2011, at 4:20 p.m.
  • Re: IDS 11.50/AIX 5.3 Slowness Issue?
    Eric Rowell -- Tuesday, 12 April 2011, at 4:17 p.m.

IDS Forum is maintained by Administrator with WebBBS 5.12.