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
------------------------------
Original Message:
Sent: Fri December 04, 2020 08:52 AM
From: Andreas Legner
Subject: How to stop Defragmenter cleaner thread on startup ?
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
Original Message:
Sent: Fri December 04, 2020 06:13 AM
From: Ivan Zavis
Subject: 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