IDS Forum

Re: AW: Performance degradation over a period

Posted By: Art Kagel
Date: Friday, 5 February 2010, at 9:28 a.m.

In Response To: Re: AW: Performance degradation over a period (NANDKISHOR SINGARE)

Symptom not cause. Could be the buffer cache isn't sized properly for this
transaction.

Art

Art S. Kagel
Advanced DataTools (www.advancedatatools.com)
IIUG Board of Directors (art@iiug.org)

See you at the 2010 IIUG Informix Conference
April 25-28, 2010
Overland Park (Kansas City), KS
www.iiug.org/conf

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 Fri, Feb 5, 2010 at 7:43 AM, NANDKISHOR SINGARE <ns_singare@yahoo.com>wrote:

> Thanks Art! The increas in chekpoint time and commit transaction after 500
> records in each thread - can it be related to slowdown of performance? any
> thoughts on this?
>
> Regards,
> Nandkishor
> -----------
>
> If fewer than 70% of the rows result in an INSERT it is MUCH faster to do
> the UPDATE first and if it returns zero rows updated then do the INSERT.
> The failed INSERTs have to be undone from the index which among other
> things
> makes the index less efficient over time.
>
> I don't think that this is the ONLY problem here, there's something else
> going on, but this will improve things a bit even once you figure out why
> there's a slowdown over time.
>
> Art
>
> Art S. Kagel
> Advanced DataTools (www.advancedatatools.com)
> IIUG Board of Directors (art@iiug.org)
>
> See you at the 2010 IIUG Informix Conference
> April 25-28, 2010
> Overland Park (Kansas City), KS
> www.iiug.org/conf
>
> 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 Fri, Feb 5, 2010 at 5:21 AM, NANDKISHOR SINGARE
> <ns_singare@yahoo.com>wrote:
>
> > Hi Andreas,
> >
> > The process runs for around 2 hours. But the process becomes slow after
> 5-6
> > minutes. Only one index on table. The application creates this table and
> > index
> > and then records get inserted. After inserting around 70K records(within
> > first
> > 3-4 minutes), the records are updated one by one. The logic inserts row
> in
> > table and if duplicate row error is thrown then runs update SQL. This
> > combination of "insert row - if duplicate then update record" become slow
> > over
> > period. Also checkpoint duration also increases to 50-90 seconds. The
> > insert/update is commited only after 500 transactions. Any clues please?
> >
> > Thanks,
> > Nandkishor
> > ----------
> >
> > Hi,
> >
> > what do you mean by some period: seconds, minutes, hours, days, months ?
> >
> > Many indexes on the tables ?
> >
> > New tables/indexes, when the application starts ?
> >
> > Regards,
> > Andreas
> >
> > >
> > -------------------------------------------
> > SPAR Österreichische Warenhandels-AG
> > Hauptzentrale
> > A - 5015 Salzburg, Europastrasse 3
> > FN 34170 a
> >
> > Tel: +43 662 4470 84923
> > Fax:
> > Mobile: +43 664 6259575
> > E-Mail: Andreas.KUTSCHE@spar.at
> > Internet: http://www.spar.at
> >
> > Wichtiger Hinweis: Der Inhalt dieser E-Mail kann vertrauliche und
> rechtlich
> > geschützte Informationen, insbesondere Betriebs- oder
> Geschäftsgeheimnisse,
> > enthalten, zu deren Geheimhaltung der Empfänger verpflichtet ist. Die
> > Informationen in dieser E-Mail sind ausschließlich für den Adressaten
> > bestimmt. Sollten Sie die E-Mail irrtümlich erhalten haben so ersuchen
> wir
> > Sie, die Nachricht von Ihrem System zu löschen und sich mit uns in
> > Verbindung
> > zu setzen.
> > Über das Internet versandte E-Mails können leicht manipuliert oder unter
> > fremdem Namen erstellt werden. Daher schließen wir die rechtliche
> > Verbindlichkeit der in dieser Nachricht enthaltenen Informationen aus.
> Der
> > Inhalt der E-Mail ist nur rechtsverbindlich, wenn er von uns schriftlich
> > bestätigt und gezeichnet wird.
> > Sollte trotz der von uns verwendeten Virus-Schutzprogramme durch die
> > Zusendung
> > von E-Mails ein Virus in Ihre Systeme gelangen, haften wir nicht für
> evtl.
> > hieraus entstehende Schäden.
> > Wir danken für Ihr Verständnis.
> >
> > Important notice: The contents of this e-mail may contain confidential
> and
> > legally protected information that is in particular related to
> operational
> > and
> > trade secrets, which the recipient is obliged to treat as confidential.
> The
> > information in this e-mail is made available exclusively for use by the
> > addressee. In the event that the e-mail may have been sent to you in
> error,
> > we
> > would ask you to kindly delete this communication from your system and to
> > contact us.
> > E-mails sent via the Internet can be easily manipulated or sent out under
> > someone else's name. We therefore do not accept legal liability for the
> > information contained in this communication. The contents of the e-mail
> are
> > only legally binding if they have been confirmed and signed by us in
> > writing.
> > If, in spite of our using Antivirus protection software, a virus may have
> > penetrated your system through the sending of this e-mail, we do not
> accept
> > liability for any damage that may possibly arise as a result of this.
> > We trust that you appreciate our position.
> >
> > -------------------------------------------
> > -----Ursprüngliche Nachricht-----
> >
> > > Von: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] Im Auftrag von
> > > NANDKISHOR SINGARE
> > > Gesendet: Freitag, 5. Februar 2010 10:31
> > > An: ids@iiug.org
> > > Betreff: Performance degradation over a period [18917]
> > >
> > > Hi,
> > >
> > > Environment: Linux x86_64, IDS 11.50.FC3.
> > >
> > > Application is insert and update intensive. The inserts/updates are
> very
> > > fast
> > > initially but slow down and take more time later. The application runs
> > > with 10
> > > threads which commit transaction after 500 inserts/updates. The
> > > checkpoints
> > > also take more time after some period and application becomes very
> slow.
> > > What can be causing delayed transactions after some period.
> > > Please guide.
> > >
> > > Following are config params-
> > > -----------------------------
> > > PHYSFILE 1048470
> > > PHYSBUFF 1024
> > > LOGFILES 35
> > > LOGSIZE 10000
> > > DYNAMIC_LOGS 2
> > > LOGBUFF 64
> > > LTXHWM 70
> > > LTXEHWM 80
> > > NETTYPE ipcshm,1,50,CPU
> > > LISTEN_TIMEOUT 60
> > > MAX_INCOMPLETE_CONNECTIONS 1024
> > > MULTIPROCESSOR 1
> > > VPCLASS
> > > VP_MEMORY_CACHE_KB 0
> > > SINGLE_CPU_VP 0
> > > CLEANERS 8
> > > AUTO_AIOVPS 1
> > > DIRECT_IO 0
> > > LOCKS 200000
> > > DEF_TABLE_LOCKMODE page
> > > RESIDENT 1
> > > SHMVIRTSIZE 2048000
> > > SHMADD 8192
> > > EXTSHMADD 8192
> > > SHMTOTAL 0
> > > SHMVIRT_ALLOCSEG 0,3
> > > SHMNOACCESS
> > > CKPTINTVL 300
> > > AUTO_CKPTS 1
> > > RTO_SERVER_RESTART 0
> > > BLOCKTIMEOUT 3600
> > > TXTIMEOUT 300
> > > DEADLOCK_TIMEOUT 60
> > > LTAPEBLK 32
> > > LTAPESIZE 0
> > > FILLFACTOR 90
> > > MAX_FILL_DATA_PAGES 0
> > > BTSCANNER num=1,threshold=5000,rangesize=-1,alice=6,compression=default
> > > ONLIDX_MAXMEM 128000
> > > MAX_PDQPRIORITY 100
> > > DS_MAX_QUERIES
> > > DS_TOTAL_MEMORY 512000
> > > DS_MAX_SCANS 1048576
> > > DS_NONPDQ_QUERY_MEM 512
> > > RA_PAGES 64
> > > RA_THRESHOLD 16
> > > BUFFERPOOL
> > >
> >
> default,buffers=10000,lrus=8,lru_min_dirty=50.000000,lru_max_dirty=60.5000
> > > 00
> > > BUFFERPOOL
> > >
> >
> size=2K,buffers=500000,lrus=8,lru_min_dirty=50.000000,lru_max_dirty=60.000
> > > 000
> > > AUTO_LRU_TUNING 1
> > > VPCLASS cpu,num=7,noage
> > > LOCKS 2000
> > > --------------------------
> > > onstat -p output
> > > --------------------------
> > > IBM Informix Dynamic Server Version 11.50.FC3 -- On-Line -- Up 17 days
> > > 05:56:44 -- 3164116 Kbytes
> > >
> > > Profile
> > > dskreads pagreads bufreads %cached dskwrits pagwrits bufwrits %cached
> > > 21516802 94283915 17657895098 99.88 11827207 28999243 283105574 95.84
> > >
> > > isamtot open start read write rewrite delete commit rollbk
> > > 1020886880 1585668 23913359 639061512 25057378 788878 8907 122212 101
> > >
> > > gp_read gp_write gp_rewrt gp_del gp_alloc gp_free gp_curs
> > > 0 0 0 0 0 0 0
> > >
> > > ovlock ovuserthread ovbuff usercpu syscpu numckpts flushes
> > > 0 0 0 68114.80 2302.94 1243 29911
> > >
> > > bufwaits lokwaits lockreqs deadlks dltouts ckpwaits compress seqscans
> > > 1399786 11 717002473 0 0 880 2970498 254229
> > >
> > > ixda-RA idx-RA da-RA RA-pgsused lchwaits
> > > 260446 3327945 9654376 13248107 14058331
> > >
> > > ---------------------------
> > > Thanks,
> > > Nandkishor
> > > -------------
> > >
> > >
> > >
> >
> **************************************************************************
> > > *****
> > > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
> >
> >
> >
>
> *******************************************************************************
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
> >
>
>
>
> *******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>

--0023545bd094d9be54047edb41be

Messages In This Thread

• Performance degradation over a period
  NANDKISHOR SINGARE -- Friday, 5 February 2010, at 4:31 a.m.
  • AW: Performance degradation over a period
    Andreas.KUTSCHE@spar.at -- Friday, 5 February 2010, at 4:47 a.m.
    • Re: AW: Performance degradation over a period
      NANDKISHOR SINGARE -- Friday, 5 February 2010, at 5:21 a.m.
      • Re: *** SPAM *** Re: AW: Performance degradation o
        Alexandre Marini -- Friday, 5 February 2010, at 5:30 a.m.
      • Re: AW: Performance degradation over a period
        Art Kagel -- Friday, 5 February 2010, at 7:18 a.m.
        • Re: AW: Performance degradation over a period
          NANDKISHOR SINGARE -- Friday, 5 February 2010, at 7:43 a.m.
          • Re: AW: Performance degradation over a period
            Art Kagel -- Friday, 5 February 2010, at 9:28 a.m.
      • Re: AW: Performance degradation over a period
        georgepalmer@cox.net -- Friday, 5 February 2010, at 7:49 p.m.

IDS Forum is maintained by Administrator with WebBBS 5.12.