IDS Forum

Re: IDS 11.50/AIX 5.3 Slowness Issue?

Posted By: Eric Rowell
Date: Tuesday, 12 April 2011, at 4:18 p.m.

In Response To: Re: IDS 11.50/AIX 5.3 Slowness Issue? (rroussey@comcast.net)

Bob,

On Mon, Apr 11, 2011 at 4:36 PM, rroussey@comcast.net
<rroussey@comcast.net>wrote:

> How many LUNs are you hitting? How many dbspaces / chunks are on those
> LUNs?
> Does Compellent have a utility to show queue depth on the SAN? You may have
> too many threads hitting a lot of chunks on one big LUN and backing up
> those
> queues?
>
> Bob
>
> ----- Original Message -----
> From: "Art Kagel" <art.kagel@gmail.com>
> To: ids@iiug.org
> Sent: Monday, April 11, 2011 4:06:01 PM
> Subject: Re: IDS 11.50/AIX 5.3 Slowness Issue? [23383]
>
> The only obvious problem that I see is the 2MB stripe block size on the
> disk
> array. Unlike filesystems, Informix performs best with small stripe blocks
> ideally in the 32K to 64K range. That is because Informix only ever either
> reads or writes a single page (4K on AIX as you know) or what's known as a
> Big Read which is eight pages (so 32K). That may explain at least some of
> the reduction in IO rates when using Informix versus dd in either of two
> apparently opposite ways: 1) Informix is pulling in 2MB every time it asks
> for 4K that is causing a 500x hit on bandwidth reading unnecessary data. -
> or - 2) When Informix asks for 4K and 4MB is read, any following sequential
> reads are satisfied from the controller cache not from the SAN itself so
> the
> SAN statistics are misleading.
>
> HUH? What I'm saying is that either you are wasting lots of cache pages and
> available IO bandwidth on 492 - 499 unread Informix pages with each IO, or
> your actual data rate as experienced by Informix is much higher than the
> SAN
> is seeing. The final arbitrator of which is truth is the query performance
> which you are saying is running 10x slower than raw IO on the devices using
> dd. No database will every reach dd's simplicity and speed, but this isn't
> even close. I'm going to go for the wasted bandwidth interpretation here
> and say the there is a real slowdown, not a phantom caused by looking at
> the
> wrong level of the system.
>
> The remedy is to rebuild the disk array using a 32K or 64K block size. Also
> look at reducing your RA_ parameters in the ONCONFIG file. I have not seen
> the output from onstat -p or onstat -g iov but I suspect you are also
> experiencing excessive read-ahead within Informix, but that's probably not
> the main problem.
>
> Oh, and stick with increasing the AIX IO queue depth to 64 or more. On the
> system I was having a problem on it was set to only 8, but 64 seemed to
> work
> very well in combination with other changes (most related to tuning AIX
> properly for running Informix on the P7 processors - so that doesn't apply
> to you).
>
> 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 1:07 PM, Eric Rowell <erowell@gmail.com> wrote:
>
> > ART thank you for your questions. There was a second half to my post
> > which was lost. There are the answers to your questions:
> >
> > RAW DIsks
> >
> > AIX IO Queue Depth: 32 (has been set to 256 with no difference in
> > Informix)
> >
> > RAID 10 (Compellent will Tier Data but this is set to a fixed Tier), 9
> > Disk, 2M Block, 4G FC Drives 15K.
> >
> > The Temp DB are on difference disk on the same SAN.
> >
> > SAN cache 3.5GB per controller (2 Controllers)
> >
> > Table has about 5GB of data
> >
> > Currently 4K page (I have tried the same process with a 12K and 16K
> > page, Margin of Error performance change 1-2%)
> >
> > IBM Informix Dynamic Server Version 11.50.FC7 -- On-Line -- Up 2
> > days 23:07:25 -- 1742592 Kbytes
> > Segment Summary:
> > id key addr size ovhd class
> > blkused blkfree
> > 7340042 52574801 700000010000000 1681735680 20141248 R*
> > 410576 4
> > 7340044 52574802 700000080000000 98304000 1153600 V
> > 16813 7187
> > 7340043 52574803 700000090000000 4374528 52672 M
> > 1067 1
> > Total: - - 1784414208 - -
> > 428456 7192
> >
> > ** I have increased and decreased the memory values with no difference.
> >
> > gfd pathname bytes read page reads bytes write page writes
> > io/s
> > 5 dms_vol01 2409951232 588367 0 0
> > 157.5
> > 37 dms_vol44 256000000 62500 0 0
> > 75.3
> > 116 dms_vol37 768000000 187500 0 0
> > 121.7
> > 186 dms_vol29 256000000 62500 0 0
> > 82.6
> >
> > partnum lkrqs lkwts dlks touts isrd iswrt isrwt isdel bfrd
> > bfwrt seqsc rhitratio
> > 0xb00002 0 0 0 0 170884 0 0 0 1
> > 0 1 4294930196
> > * I think this ppf was after the query was completed.
> >
> > The odd thing to me is when testing under linux I see a performance
> > difference when changing from 4K to 16K blocks. Other odd thing is
> > how fast and many io/s I can do from the OS compared to within
> > Informix. I expect over-head but not going from 75MB/s to 5MB/s.
> >
> >
> >
> > On Mon, Apr 11, 2011 at 12:38 PM, Art Kagel <art.kagel@gmail.com> wrote:
> > > 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.
> > >>
> > >
> > >
> >
> >
> >
> > --
> > Eric B. Rowell
> >
>
> --20cf307cfe9e1195f304a0aa1aeb
>
>
>
> *******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
>
> *******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>

--
Eric B. Rowell

--20cf3071cbd60bb74a04a0be63b6

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.