Informix

nested-group-icon.png

DB2

Expand all | Collapse all

How to stop Defragmenter cleaner thread on startup ?

  • 1.  How to stop Defragmenter cleaner thread on startup ?

    Posted Fri December 04, 2020 06:13 AM

    Hello,

    how can I set Informix to NOT run Defragmenter cleaner thread on start ?

    I was disable all ph_task in sysadmin, bounce instance, but it was run automatically again on startup.

    My Issue is, that on startup, Informix engine add to much share memory segments (12GB) , and after that, these Shared memory can not be free, even with onmode -F
    Normally, engine can run with 4GB Share memory ...

    Process that i see on startup is dbWorker2.
    SQL is: SELECT partnum FROM sysmaster:sysptnhdr WHERE sysmaster:bitval(flags,'0x10000000') = 1

    In online.log I see this:
    10:55:58  Defragmenter cleaner thread now running
    10:55:58  Defragmenter cleaner thread cleaned:0 partitions
    10:58:01  Dynamically allocated new virtual shared memory segment (size 128000KB)
    10:58:01  Memory sizes:resident:30732 KB, virtual:6096000 KB, message:0, bufferpool:2490072, SHMTOTAL:33554432 KB
    10:58:04  Dynamically allocated new virtual shared memory segment (size 128000KB)
    ...

    Version Informix is: 14.10.FC2WE
    PS1: I know that I have to much databases on one instance (40), but it is server for testing/develop ...
    PS2: update statistics, defragment ... I run manually - when I needed...



    ------------------------------
    Ivan Zavis
    ------------------------------


  • 2.  RE: How to stop Defragmenter cleaner thread on startup ?

    Posted Fri December 04, 2020 08:53 AM
    That shmem segment allocation that you're showing here occurred two minutes after the Defragmenter cleaner thread finished, so I'm not convinced the two things have anything to do with each other.

    Try to catch an 'onstat -g ses 0' and 'onstat -a' as closely as possible to such allocation as see what's running and what's consuming memory.
    Reading "many databases", next question would be how many tables, indices, partitions actually?

    HTH,
     Andreas

    ------------------------------
    Andreas Legner
    ------------------------------



  • 3.  RE: How to stop Defragmenter cleaner thread on startup ?

    Posted Fri December 04, 2020 09:46 AM

    Andreas Legner

    I can restart engine right now - but, I try later, and post detail, but while share memory was added, I se only one SQL running on server:

    SELECT partnum FROM sysmaster:sysptnhdr WHERE sysmaster:bitval(flags,'0x10000000') = 1



    ------------------------------
    Ivan Zavis
    ------------------------------



  • 4.  RE: How to stop Defragmenter cleaner thread on startup ?

    Posted Fri December 04, 2020 09:57 AM

    ... and, one database has about 4.000 tables, and 12.000 indexes, couple of store procedures, functions, views ... x 40 databases on one instance (1TB dbspace on 11 chunks)
    Maybe, problem is in to much extent ... because I was importing databases with -D options, I never do that before on my servers...

    I don't want to allow Informix to allocate too much dbspace for database - because database on that server will not grow to much.
    That was doing dbimport without -D options (and this is OK when database grows)



    ------------------------------
    Ivan Zavis
    ------------------------------



  • 5.  RE: How to stop Defragmenter cleaner thread on startup ?

    Posted Fri December 04, 2020 11:13 AM
    Maybe, there is no issue with defragment cleaner, but really questions is:

    what is this flags '0x10000000' mean, and what Informix try to do on startup with this SQL ?

    SELECT partnum FROM sysmaster:sysptnhdr WHERE sysmaster:bitval(flags,'0x10000000') = 1

    I have more than 800.000 rows in sysmaster:sysptnhdr, with NO index on "flags".

    This SQL Estimated Cost: 838619
    Estimated # of Rows Returned: 81183

    1) sysmaster:informix.sysptnhdr: SEQUENTIAL SCAN

    Filters: informix.bitval(sysmaster:informix.sysptnhdr.flags ,'0x10000000' )= 1

    but I still don't understand why he need 12GB of Share memory to do that

    I will try to add (permanent) index on sysmaster:informix.sysptnhdr.flags if it is possible ...
    Then I will try to update version of engine ...

    Maybe is time to do backup before that :)

    In onstat -a I found these:

    session 42 ---------------------------------------------------------------
    sdblock heapsz flags statement ('*' = Open cursor)
    4e3b4028 4968 ----- -
    4e3b42e8 12504 ----- INSERT INTO ph_run ( run_id, run_task_id, run_task_seq, run_retcode, run_duration, run_ztime, run_btime, run_mttime ) VALUES ( 0, ?, ?, ?, ?, ?, ?, ? )
    4e3b45a8 17056 ----- SELECT MAX(run_task_seq) FROM ph_run A, ph_task B WHERE A.run_task_id = ? AND A.run_task_id = B.tk_id AND A.run_time + B.tk_delete < CURRENT
    4e3b4868 8928 ----- DELETE FROM ph_run WHERE run_task_id = ? AND run_task_seq <= ?
    4e3b4b28 26256 ----- UPDATE ph_task SET ( tk_next_execution, tk_total_executions, tk_total_time, tk_attributes ) = ( ?, tk_total_executions + ?, tk_total_time + ?, BITANDNOT(tk_attributes, 514 ) ) WHERE tk_id = ?

    session 41 ---------------------------------------------------------------
    sdblock heapsz flags statement ('*' = Open cursor)
    4e3a8028 4968 ----- -
    4e3a82e8 12504 ----- INSERT INTO ph_run ( run_id, run_task_id, run_task_seq, run_retcode, run_duration, run_ztime, run_btime, run_mttime ) VALUES ( 0, ?, ?, ?, ?, ?, ?, ? )
    4e3a85a8 80328 ----- SELECT MAX(run_task_seq) FROM ph_run A, ph_task B WHERE A.run_task_id = ? AND A.run_task_id = B.tk_id AND A.run_time + B.tk_delete < CURRENT
    4e3a8868 8928 ----- DELETE FROM ph_run WHERE run_task_id = ? AND run_task_seq <= ?
    4e3a8b28 26312 ----- UPDATE ph_task SET ( tk_next_execution, tk_total_executions, tk_total_time, tk_attributes ) = ( ?, tk_total_executions + ?, tk_total_time + ?, BITANDNOT(tk_attributes, 514 ) ) WHERE tk_id = ?
    4e3a8de8 19872 ----- *SELECT partnum FROM sysmaster:sysptnhdr WHERE sysmaster:bitval(flags, '0x10000000') = 1
    4e3a90a8 5992 ----- <SPL(sysmaster:560), stmt(7):EXPRESSION (<unknown> <unknown> <unknown> )>

    session 40 ---------------------------------------------------------------
    sdblock heapsz flags statement ('*' = Open cursor)
    4f7c6028 4968 ----- -
    4f7c62e8 97976 ----- *SELECT FIRST 1 {+first_rows} tk_id, tk_sequence, tk_result_table, tk_execute, tk_delete, tk_start_time, tk_stop_time, tk_next_execution, tk_frequency, tk_attributes, tk_type, tk_name, tk_dbs, CASE WHEN tk_next_execution - CURRENT < INTERVAL (-365) DAY(3) TO DAY THEN -31536000 ELSE (tk_next_execution - CURRENT)::INTERVAL SECOND(9) TO SECOND::char(20)::integer END as tm_rem, (decode(tk_sunday, 't',1,0) + decode(tk_monday, 't',2,0) + decode(tk_tuesday, 't',4,0) + decode(tk_wednesday, 't',8,0) + decode(tk_thursday, 't',16,0) + decode(tk_friday, 't',32,0) + decode(tk_saturday, 't',64,0))::integer as tm_days, tk_total_time / decode(tk_total_executions,0,1,tk_total_executions) FROM ph_task WHERE BITAND(tk_attributes, 512) = 0 AND tk_next_execution IS NOT NULL AND tk_enable ORDER BY tk_next_execution, tk_priority
    4f7c65a8 114624 ----- SELECT FIRST 1 {+first_rows} tk_id, tk_sequence, tk_result_table, tk_execute, tk_delete, tk_start_time, tk_stop_time, tk_next_execution, tk_frequency, tk_attributes, tk_type, tk_name, tk_dbs, 0, (decode(tk_sunday, 't',1,0) + decode(tk_monday, 't',2,0) + decode(tk_tuesday, 't',4,0) + decode(tk_wednesday, 't',8,0) + decode(tk_thursday, 't',16,0) + decode(tk_friday, 't',32,0) + decode(tk_saturday, 't',64,0))::integer as tm_days, tk_total_time / decode(tk_total_executions,0,1,tk_total_executions) FROM ph_task WHERE tk_name = ? OR tk_id = ?
    4f7c6868 73864 ----- UPDATE ph_task SET ( tk_attributes, tk_sequence ) = ( BITOR(tk_attributes, 512), ? ) WHERE tk_id = ?
    4f7c6de8 6040 ----- <SPL(sysadmin:598), stmt(0):EXPRESSION (<unknown> <unknown> <unknown> )>
    4f7c70a8 4968 ----- <SPL(sysadmin:598), stmt(1):EXPRESSION (<unknown> <unknown> )>
    4f7c7368 17320 ----- *SELECT value FROM ph_version WHERE object='IWA' AND type='version'

    Sess SQL Current Iso Lock SQL ISAM F.E.
    Id Stmt type Database Lvl Mode ERR ERR Vers Explain
    42 sysadmin DR Wait 5 0 0 - Off
    41 SELECT sysadmin DR Wait 5 0 0 - Off
    40 sysadmin DR Wait 5 0 0 - Off
    39 sysadmin CR Not Wait 0 0 - Off

    ------------------------------
    Ivan Zavis
    ------------------------------



  • 6.  RE: How to stop Defragmenter cleaner thread on startup ?

    Posted Fri December 04, 2020 05:23 PM
    Hi Ivan,

    you can't define your own index on sysptnhdr as this isn't a real table.

    Yet that flag 0x10000000 indeed is interesting
    -> could you test setting PFSC_BOOST 0 in onconfig?

    Problem might warrant a support case.

    BR,
     Andreas

    ------------------------------
    Andreas Legner
    ------------------------------



  • 7.  RE: How to stop Defragmenter cleaner thread on startup ?

    Posted Sat December 05, 2020 03:56 PM
    Edited by Ivan Zavis Sat December 05, 2020 04:12 PM
    Hi Andreas

    This is it ! (by Michael Jackson)

    Thank you very much !

    When I set PFSC_BOOST 0 on my $ONCONFIG share memory is OK - engine not add any segment on startup !

    Info for everyone about PFSC is there:
    https://informix.hcldoc.com/14.10/help/index.jsp?topic=%2Fcom.ibm.adref.doc%2Fids_adr_pfsc_boost.htm

    PS: Update to (latest) version 14.10.FC4W1WE did not resolve problem (without PFSC_BOOST 0)

    This topic should by renamed to: "PFSC is allocating to much share memory on startup" but I can't do this ... sorry.


  • 8.  RE: How to stop Defragmenter cleaner thread on startup ?

    Posted Fri December 04, 2020 09:22 AM

    Ivan,

     

     

    Not sure that running update statistics "on demand" is the best practice. Statistics are critical to have the optimizer do its job the best way. If you ever forget to have them up to date, your best queries may run slow.

    Also disabling all the tasks may not be so good.

     

    Just my 0.002 cents

     






  • 9.  RE: How to stop Defragmenter cleaner thread on startup ?

    Posted Fri December 04, 2020 09:47 AM

    Eric Vercelletto

    I know that.

    I was stopping Automatics Update Statistics, once upon  time, when AUS crashed Informix engine, because it was BUG to not support database for AUS other that English locale ... and leave server that way.

    I do update statistics regular once/day to all table in database ... databases is relative small according to hw power of today servers ...

    I was disabled all task for testing purpose only because:

    - there is nothing in scheduler task now

    - there is no connections from outside engine

    - somethings eat my memory on server every time after startup, and I want know who and why



    ------------------------------
    Ivan Zavis
    ------------------------------



  • 10.  RE: How to stop Defragmenter cleaner thread on startup ?

    Posted Fri December 04, 2020 10:03 AM

    Ok, just checking ��

     

    Why don't you try Art Kagel Do Stats ?

     

    It is smarter than AUS, better implemented and free

     

    Art will give you where you can find it

     

    Eric

     






  • 11.  RE: How to stop Defragmenter cleaner thread on startup ?

    Posted Fri December 04, 2020 10:19 AM

    Yes, I know Do Stats, but like I said:

    I do things in that way:
    Every day I run update statistics medium or high on full databases - and it should be the best statistics - I think ? Databases is not to big and all is done max in 1 hours by night.

    If it running to long - I tell the client to buy server with SSD HDD :)
    If is still to long - I tell the client to buy server with Nvme HDD :)

    Just joking, but I work with couple server with nvme hdd - and that is amazing ... !

    Sorry but we are away from theme ...



    ------------------------------
    Ivan Zavis
    ------------------------------



  • 12.  RE: How to stop Defragmenter cleaner thread on startup ?

    Posted Fri December 04, 2020 01:07 PM

    Sorry to disturb in that case

     

    Eric Vercelletto
    Data Management Architect and Owner / Begooden IT Consulting
    Board of Directors, International Informix Users group
    IBM Champion 2013,2014,2015,2016,2017,2018,2019,2020
    ibm-champion-rgb-130px

    Tel:     +33(0) 298 51 3210
    Mob : +33(0)626 52 50 68
    skype: begooden-it
    Google Hangout: eric.vercelletto@begooden-it.com
    Email:
    eric.vercelletto@begooden-it.com
    www :
    http://www.vercelletto.com
    www  https://kandooerp.org