Informix 14.10.FC9 reverse scans

So with some help from new features in Informix 14.10.FC7 I found that reverse scans happen by default.

Reverse scans can be bad as under contention they may have to be retried and require more cpu time.

[informix@localhost IDS.14.10.FC9W1]$ onstat -g ppf | awk ‘($13!=0) { print $0}’

IBM Informix Dynamic Server Version 14.10.FC9W1AETL — On-Line — Up 1 days 06:57:25 — 185996 Kbytes
2023-02-05 23:17:27

Partition profiles
partnum lkrqs lkwts dlks touts isrd iswrt isrwt isdel bfrd bfwrt seqsc rvscn rs_rt rhitratio name
0x100195 462 0 0 0 116 160 0 0 355 174 0 28 0 100 sysadmin:informix.idx_mon_ckpt_1

So the index sysadmin:informix.idx_mon_ckpt_1 is having reverse scans, why is this?

[informix@localhost IDS.14.10.FC9W1]$ grep idx_mon_ckpt_1 $INFORMIXDIR/etc/sysadmin/*.sql
/opt/IBM/IDS.14.10.FC9W1/etc/sysadmin/sch_tasks.sql:”create table informix.mon_checkpoint (ID integer, intvl integer, type char(12), caller char(10), clock_time int, crit_time float, flush_time float, cp_time float, n_dirty_buffs int, plogs_per_sec int, llogs_per_sec int, dskflush_per_sec int, ckpt_logid int, ckpt_logpos int, physused int, logused int, n_crit_waits int, tot_crit_wait float, longest_crit_wait float, block_time float);create unique index idx_mon_ckpt_1 on mon_checkpoint(intvl);grant select on mon_checkpoint TO ‘db_monitor’ as informix;”,

So the index is on mon_checkpoint, ok what accesses that table?

[informix@localhost IDS.14.10.FC9W1]$ grep mon_checkpoint $INFORMIXDIR/etc/sysadmin/*.sql | grep intvl | tail -1
/opt/IBM/IDS.14.10.FC9W1/etc/sysadmin/sch_tasks.sql:”insert into mon_checkpoint select $DATA_SEQ_ID, intvl, type, caller, clock_time, crit_time, flush_time, cp_time, n_dirty_buffs, plogs_per_sec, llogs_per_sec, dskflush_per_sec, ckpt_logid, ckpt_logpos, physused, logused, n_crit_waits, tot_crit_wait, longest_crit_wait, block_time FROM sysmaster:syscheckpoint WHERE intvl > (select NVL(max(intvl),0) from mon_checkpoint)”,

set explain on;
select NVL(max(intvl),0) from mon_checkpoint

gives

QUERY: (OPTIMIZATION TIMESTAMP: 02-05-2023 23:31:32)

select NVL(max(intvl),0) from mon_checkpoint

Estimated Cost: 1
Estimated # of Rows Returned: 1

1) informix.mon_checkpoint: INDEX PATH

(1) Index Name: informix.idx_mon_ckpt_1
    Index Keys: intvl   (Key-Only)  (Aggregate)  (Serial, fragments: ALL)

Query statistics:

Table map :


Internal name Table name


type rows_prod est_rows rows_cons time


group 1 1 0 00:00.00

[informix@localhost IDS.14.10.FC9W1]$ onstat -g ses 47
Your evaluation license will expire on 2023-05-05 17:08:46

IBM Informix Dynamic Server Version 14.10.FC9W1AETL — On-Line — Up 1 days 07:15:45 — 185996 Kbytes
2023-02-05 23:35:46

session effective #RSAM total used dynamic
id user user tty pid hostname threads memory memory explain
47 informix – 1 1264330 localhost 1 90112 73664 off

Program :
/opt/IBM/IDS.14.10.FC9W1/bin/dbaccess

tid name rstcb flags curstk status
123 sqlexec 45a8eac0 Y–P— 3808 cond wait netnorm –

Memory pools count 2
name class addr totalsize freesize #allocfrag #freefrag
47 V 47d81040 86016 15704 77 11
47*O0 V 48b52040 4096 744 1 1

name free used name free used
overhead 0 6704 scb 0 144
opentable 0 4904 filetable 0 744
log 0 16536 temprec 0 2208
gentcb 0 1592 ostcb 0 2992
sqscb 0 26512 hashfiletab 0 552
osenv 0 2504 sqtcb 0 7792
fragman 0 336 sapi 0 144

sqscb info
scb sqscb optofc pdqpriority optcompind directives
45c421c0 48bf6028 0 0 2 1

Sess SQL Current Iso Lock SQL ISAM F.E.
Id Stmt type Database Lvl Mode ERR ERR Vers Explain
47 – sysadmin CR Not Wait 0 0 9.24 On

Last parsed SQL statement :
select NVL(max(intvl),0) from mon_checkpoint

[informix@localhost IDS.14.10.FC9W1]$ onstat -g tpf 123
Your evaluation license will expire on 2023-05-05 17:08:46

IBM Informix Dynamic Server Version 14.10.FC9W1AETL — On-Line — Up 1 days 07:15:58 — 185996 Kbytes
2023-02-05 23:35:59

Thread profiles
tid lkreqs lkw dl to lgrs totll totpl isrd iswr isrw isdl isct isrb lx drd bfr bfw lsus lsmx seq rvscn rs_rt
123 151 0 0 0 0 0 0 72 0 0 0 0 0 0 0 152 0 0 0 0 1 0

So we have a reverse scan!

I guess we would need a descending index as we to avoid the reverse scan!

Advertisement

Informix 14.10.FC9W1 is out!


Personal Development

Smart person

4:30 Rule #1 – “Stand up straight with your shoulders back” “Where this counter that you share with lobsters rates you in terms of your hierarchical position, determines the ratio of negative emotion to positive emotion that you feel.”

16:23 Rule #2 – “Treat yourself like you’re someone that you care about.” “Why people are contemptuous of themselves – the reasons do not justify the mistreatment of yourself.” 22:53

Rule #3 – “Make friends with people that want the best for you.” “Like you have an ethical responsibility to take care of yourself, you have an ethical responsibility to surround yourself with people who have the courage, faith, and wisdom to wish you well when you have done something good and to stop you when you have done something destructive.”

25:44 Rule #4 – “Compare yourself to who you were yesterday, not to who someone else is today.” “you have to be careful who you compare yourself to.”

37:23 Rule #5 – “Do not let your children do anything that makes you dislike them.” “pursue what’s meaningful and you’ll encounter that which you least want to encounter.”

48:53 Rule #6 – “(Set your house in perfect order before you criticize the world)” “Resentful people that wanna change the world should not be trusted.”

58:47 Rule #7 – “Do what is meaningful, not what is expedient” “You have to understand that you could not only do what the Nazi camp guards did in Auschwitz, but that you could actually enjoy it… and then you have to decide that you’re not gonna do that anymore.”

Rule #8 “Tell the truth — or, at least, don’t lie”

1:05:01 Rule #9 – “Assume that the person you’re listening to might know something you don’t.” “What you don’t know is more important than what you know.”

1:11:44 Rule #11 – “Don’t bother children when they’re skateboarding.” “You want to adopt responsibility for [your] life. To tell the truth. Understand that [your] failure to participate fully in being leaves a hole that’s precisely the size of [your] soul in the cultural landscape.”

1:17:09 Rule #12 – “Pet a cat when you encounter one on the street.” “You have to be alert when you’re suffering to the unexpected beauty in life.”

1:24:32 Question: “(What would you do in a situation where people are not listening to what you have to say?)” Answer: “Do not cast pearls before swine.” “If people are not listening to you. Stop talking to them. Watch them, and they will tell you what they’re up to.”

1:26:17 Question: “Where do you fall short in these 12 rules, is it a constant adjustment?” Answer: “You never attain the ideal. Not only that, it recedes as you approach it.” “It’s not so much that there isn’t such a thing as a good person. It’s that our idea of what constitutes good isn’t right.” “You learn things painfully. When you learn something painfully, a part of you has to die – that’s the pain.” “Life is a constant process of death and rebirth. To participate in that fully is to allow yourself to be redeemed by it.” “Until the entire world is redeemed, we all fall short.”


SQL Server Always On Availability Group Performance

See

https://blogs.msdn.microsoft.com/psssql/2018/04/05/lesson-learned-from-an-availability-group-performance-case/?utm_source=dlvr.it&utm_medium=twitter

which references

https://blogs.msdn.microsoft.com/saponsqlserver/2013/04/21/sql-server-2012-alwayson-part-11-performance-aspects-and-performance-monitoring-i/

https://blogs.msdn.microsoft.com/saponsqlserver/2013/04/24/sql-server-2012-alwayson-part-12-performance-aspects-and-performance-monitoring-ii/

https://blogs.msdn.microsoft.com/psssql/2018/04/05/troubleshooting-data-movement-latency-between-synchronous-commit-always-on-availability-groups/

https://blogs.msdn.microsoft.com/psssql/2018/04/05/troubleshooting-sql-server-scheduling-and-yielding/


SQL Server on Linux update

SQL Server on Linux

Since 2017 CU4, the SQL Server Agent is no longer a seperate package.

To enable SQL Server Agent:

either

  • sudo /opt/mssql/bin/mssql-conf set sqlagent.enabled true
  • sudo systemctl restart mssql-server

OR

  • During setup export MSSQL_AGENT_ENABLED=’true’

 

Replication Agents on Linux are in preview aka.ms/sqleap

 

  • Replication Agents are packaged in the server package.
  • Replication can be setup across Windows/Linux and use AD Auth
  • Docker support is currently being tested and will come soon.
  • Replication Agents on Linux can be managed via SSMS on Windows and normally tools as mainly done via stored procs
  • Snapshot,Transactional and Merge Replication are supported
  • Publisher,Distribtutor or Subscriber can be on Linux
  • SQLOps Studio can be used to configure snapshot replication via stored procs

 

 


CPU Issues and Speculative Execution

http://smooth1.co.uk/security/CPU_issues.html

Updated Linux kernel patches and added HardenedBSD.


CPU Issues and Speculative Execution

http://smooth1.co.uk/security/CPU_issues.html

added

  • Cloud Providers – Linode,Open Telekom Cloud
  • Hypervisor – QEMU
  • Operating Systems – FreeBSD,OpenBSD,Gentoo,Container Linux (CoreOS)
  • Userland – GCC compiler,WebKit
  • Misc Vendor fixes – Raspberry Pi

 

Updated Linux kernel patches with more information


Windows Process Monitor

Windows Proccess Monitor – https://docs.microsoft.com/en-gb/sysinternals/downloads/procmon


CPU Issues and Speculative Execution

http://smooth1.co.uk/security/CPU_issues.html

added

  • Debian,Fedora
  • LLVM,ZFS,Lustre
  • Oracle Sparc,RISC-V
  • Oracle,Nexenta,Scality,Pure Storage,Wonderware,Rockwell Automation,Synology,F5,Qubes,Qubes,Fortinet,Netap

Updated Google cloud section to mention Retpoline a compile time fix for Variant 2 (part of  Spectre) with “almost no performance loss.”


CPU Issues and Speculative Execution

A comprehensive blog post on CPU Issues and Speculative Execution:

http://smooth1.co.uk/security/CPU_issues.html