How to stop Defragmenter cleaner thread on startup ?

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 ------------------------------ #Informix

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 ------------------------------

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

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 ------------------------------

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 ------------------------------

... 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 ------------------------------

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

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 ------------------------------

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') = 1I 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 ------------------------------

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 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

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 ------------------------------

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. #Informix