Menu ▾ ▴

#33 ASE 16 SP 03 PL07 has some abnormal logical read/physical read #s.

v1.0_(example)
open
nobody
None
1
2025-02-14
2020-06-03
Anonymous
No

Hi JPM,

we have been using ASEMON for last 4 years, and it is our goto tool for lot of performance issues resolutiona. its amazing!!!

Now, we are in the process of upgrading our ASE to ASE 16 SP03 PL07, and ASEMON logger V3.0.6.
And we are seeing some abnormal numbers in logical i/os and physical i/os.
But in reality, when we observe same processes on ASE side, we don't see any such #s.
We upgrade ASEMON logger to 3.1.4, still the same problems.

Again , i think, this behviour is visible to some specific programs like "java" or "perl".

1> set forceplan on
2> set rowcount 5
3> select
4> Loggedindt=convert (varchar, A.Loggedindatetime, 111)+' '+convert (varchar, A.Loggedindatetime, 108)+'.'+convert(varchar, datepart(ms,A.Loggedindatetime)),
5> program_name,
6> LReads=sum(convert(numeric(24,0),LogicalReads)),
7> PReads=sum(convert(numeric(24,0),PhysicalReads)),
8> PgRead=sum(convert(numeric(24,0),PagesRead)),
9> PWrites=sum(convert(numeric(24,0),PhysicalWrites)),
10> PgWritten=sum(convert(numeric(24,0),PagesWritten))
11> from IMAG_UAT_CnxActiv A, IMAG_UAT_Cnx C
12> where A.Timestamp >='06/03/2020 00:00'
13> and A.Timestamp <'06/03/2020 23:59'
14> and A.Loggedindatetime = C.Loggedindatetime
15> and A.Spid=C.Spid
16> and A.Kpid=C.Kpid
17>
18> group by A.Loggedindatetime, A.Spid, UserName, program_name, ipaddr, hostname, hostprocess, clientname, clienthostname, clientapplname, execlass
19> order by PReads desc
20> set rowcount 0
21> set forceplan off
22> go
Loggedindt program_name LReads PReads PgRead PWrites PgWritten

2020/06/03 08:40:57.160 java.lang.Thread 736176186 9223372036565714715 7498 1 1
2020/06/03 08:40:57.160 java.lang.Thread 770299377 9223372036337230464 3111 1 1
2020/06/03 07:00:42.160 sybase-source-timestamp 9111016453 23671133 0 1 1
2020/06/02 19:00:43.160 sybase-source-timestamp 7381703870 19192216 0 0 0
2020/06/03 01:05:14.160 ImagineFinancingLoad.pl 625218491 3977159 85105 40 79

(5 rows affected)

For now, i have updated sql_process_statistics.php, and changed all numeric(16,0) to (24,0), so that the report doesn't fail.

Are we missing anything here ?

thanks,
Nischal.

2 Attachments
asemon_process_pread_high.png
asemon_process_pread_high_error.png

Discussion

  • Jean-Paul Martin

    Jean-Paul Martin - 2020-06-04

    Hi Nischal
    I am pleased you appreciate Asemon.
    I don't observe these wrong values with recent asemon_logger (3.1xx) on ASE V16SP03PL07 or PL08 at my Linux or AIX customers
    Which plateform are you running on ?

    Can you do a direct SELECT like :
    select Timestamp,PhysicalReads from IMAG_UAT_CnxActiv where PhysicalReads > 10000000
    and see if you have very abnormal values

    Asemon_logger should filters these values at collect time, using this configuration in Cnx_Activ.xml config file :

      <filterHighValues>
    <col highvaluerate="5000000" defaultvalue="0"> LogicalReads </col>
    <col highvaluerate="5000000" defaultvalue="0"> PhysicalReads </col>
  </filterHighValues>

    Do you use the correct the correct collector config ?
    In your config file you should have :

                 <md> CnxActiv_switch.xml                            </md>

    Best regards
    Jpm

     

  • Jean-Paul Martin

    Jean-Paul Martin - 2020-06-04

    Hi Nischal,
    forget my previous message.
    After verification I have the same bad values in my ASE 16 Linux servers (more than 9 000 000 000 000 000 000 PhysicalReads) several times in a day. I have to recheck my asemon_logger filter

    Anyway, I didn't see the problem because I also have a filter in asemon_report. The query does :

          CPUTm=sum(convert(numeric(16,0),case when CPUTime <1500000000  then CPUTime else 0 end)), 
      LReads=sum(convert(numeric(16,0),case when LogicalReads > 2000000000 then 0 else LogicalReads end)), -- filter abnormal values, avoid overflow
      PReads=sum(convert(numeric(16,0),case when PhysicalReads > 2000000000 then 0 else PhysicalReads end)),  -- filter abnormal values, avoid overflow
      PgRead=sum(convert(numeric(16,0),PagesRead)),

    Did you upgraded asemon_report too ?

    Best regards
    Jpm

     

  • Anonymous

    Anonymous - 2020-06-05

    I am using 2 version of asemon_report 3.0.9 and 3.0.4. I'll upgrade them to latest version and verify again.

    on the other note, is it "asemon_logger" generating such high numbers or the mon tables themselves on ASE? any idea?

     

  • Jean-Paul Martin

    Jean-Paul Martin - 2020-06-08

    I found the problem with the filtering of very high PhysicalIO in asemon_logger : the filtering didn't work in some cases. Anyway , use a recent asemon_report to filter theses bad values on the client side.
    Best regards
    Jpm

     

  • Piers Collins

    Piers Collins - 2025-02-13

    Hi JP
    we upgraded to the latest asemon_logger and asemon_report v3.2.4 and I'm seeing this

    [Thu Feb 13 14:27:24.167947 2025] [php:error] [pid 5152:tid 1304] [client xx.xxx.xx:60797] PHP Fatal error:  Uncaught PDOException: SQLSTATE[ZZZZZ]: <<Unknown error>>: 247 [SAP][ASE ODBC Driver][Adaptive Server Enterprise]Arithmetic overflow during explicit conversion of INT NULL value '-9223372036854775799' to a NUMERIC field .\n (SQLExecute[247] at ext\\pdo_odbc\\odbc_stmt.c:254) in C:\\AsemonReportSRV_PHP8\\Asemon_report\\COMMON\\connectArchiveServer.php:237\nStack trace:\n#0 C:\\AsemonReportSRV_PHP8\\Asemon_report\\COMMON\\connectArchiveServer.php(237): PDO->query()\n#1 C:\\AsemonReportSRV_PHP8\\Asemon_report\\COMMON\\getgriddata.php(159): asemon_query()\n#2 {main}\n  thrown in C:\\AsemonReportSRV_PHP8\\Asemon_report\\COMMON\\connectArchiveServer.php on line 237, referer: http://myserv/Asemon_report/Asemon_report.php
[Thu Feb 13 14:40:26.806759 2025] [php:error] [pid 5152:tid 1304] [client xx.xx.xxx.xx:61237] PHP Fatal error:  Uncaught PDOException: SQLSTATE[ZZZZZ]: <<Unknown error>>: 247 [SAP][ASE ODBC Driver][Adaptive Server Enterprise]Arithmetic overflow during explicit conversion of INT NULL value '-9223372036854775799' to a NUMERIC field .\n (SQLExecute[247] at ext\\pdo_odbc\\odbc_stmt.c:254) in C:\\AsemonReportSRV_PHP8\\Asemon_report\\COMMON\\connectArchiveServer.php:237\nStack trace:\n#0 C:\\AsemonReportSRV_PHP8\\Asemon_report\\COMMON\\connectArchiveServer.php(237): PDO->query()\n#1 C:\\AsemonReportSRV_PHP8\\Asemon_report\\COMMON\\getgriddata.php(159): asemon_query()\n#2 {main}\n  thrown in C:\\AsemonReportSRV_PHP8\\Asemon_report\\COMMON\\connectArchiveServer.php on line 237, referer: http://myserv/Asemon_report/Asemon_report.php
[Thu Feb 13 15:47:35.612313 2025] [php:error] [pid 5152:tid 1304] [client xx.xx.xxx.xx:63558] PHP Fatal error:  Uncaught PDOException: SQLSTATE[ZZZZZ]: <<Unknown error>>: 247 [SAP][ASE ODBC Driver][Adaptive Server Enterprise]Arithmetic overflow during explicit conversion of INT NULL value '-9223372036854775799' to a NUMERIC field .\n (SQLExecute[247] at ext\\pdo_odbc\\odbc_stmt.c:254) in C:\\AsemonReportSRV_PHP8\\Asemon_report\\COMMON\\connectArchiveServer.php:237\nStack trace:\n#0 C:\\AsemonReportSRV_PHP8\\Asemon_report\\COMMON\\connectArchiveServer.php(237): PDO->query()\n#1 C:\\AsemonReportSRV_PHP8\\Asemon_report\\COMMON\\getgriddata.php(159): asemon_query()\n#2 {main}\n  thrown in C:\\AsemonReportSRV_PHP8\\Asemon_report\\COMMON\\connectArchiveServer.php on line 237, referer: http://myserv/Asemon_report/Asemon_report.php
[Thu Feb 13 16:10:43.270717 2025] [php:error] [pid 5152:tid 1304] [client xx.xx.xxx.xx:64515] PHP Fatal error:  Uncaught PDOException: SQLSTATE[ZZZZZ]: <<Unknown error>>: 247 [SAP][ASE ODBC Driver][Adaptive Server Enterprise]Arithmetic overflow during explicit conversion of INT NULL value '-9223372036854775793' to a NUMERIC field .\n (SQLExecute[247] at ext\\pdo_odbc\\odbc_stmt.c:254) in C:\\AsemonReportSRV_PHP8\\Asemon_report\\COMMON\\connectArchiveServer.php:237\nStack trace:\n#0 C:\\AsemonReportSRV_PHP8\\Asemon_report\\COMMON\\connectArchiveServer.php(237): PDO->query()\n#1 C:\\AsemonReportSRV_PHP8\\Asemon_report\\COMMON\\getgriddata.php(159): asemon_query()\n#2 {main}\n  thrown in C:\\AsemonReportSRV_PHP8\\Asemon_report\\COMMON\\connectArchiveServer.php on line 237, referer: http://myserv/Asemon_report/Asemon_report.php

    We see issues with the PagesRead so similar to the issues in this thread just a different column

    Is the fix to add a similar case statement in sql_process_statistics ?

    LReads=sum(convert(numeric(16,0),case when LogicalReads > 2000000000 then 0 else LogicalReads end)), -- filter abnormal values, avoid overflow PReads=sum(convert(numeric(16,0),case when PhysicalReads > 2000000000 then 0 else PhysicalReads end)), -- filter abnormal values, avoid overflow PgRead=sum(convert(numeric(16,0),PagesRead)),

    Regards

    Piers

     

  • Anonymous

    Anonymous - 2025-02-14

    Hi Piers,

    since the bad value is negative the case should be :

    PgRead=sum(convert(numeric(16,0),case when PagesRead<0 then 0 when PagesRead > 2000000000 then 0 else PagesRead end)),

    and I think I should raise the filter for LogicalReads to 2 000 000 000 000 since theis Sybase counter is a BIGINT and it can really value more than 2 billion during a sampling of this column over 30s (the default sapling period for this collector)

    Anyway, in a first time add the filter on PgRead col and tell me if it fixes your problem

    Regards
    JP

     

  • Piers Collins

    Piers Collins - 2025-02-14

    Hi JP,

    yes that has fixed it.

    Great product and great support.

    Thanks

    Piers

     

Anonymous
Anonymous

Add attachments
Cancel





Want the latest updates on software, tech news, and AI?
Get latest updates about software, tech news, and AI from SourceForge directly in your inbox once a month.