Community discussions

MikroTik App
  4. RouterOS
  5. Scripting

Having the "where" filter in scripting signifantly increases the execution time and increases CPU Usage

Post Reply
 
vincej
just joined
Topic Author
Posts: 3
Joined: Fri Oct 08, 2021 2:30 pm

Having the "where" filter in scripting signifantly increases the execution time and increases CPU Usage

Sat Sep 09, 2023 1:49 pm

Hello all, I'm trying to write a script for SNMP consumption to count the concurrent connections in connection tracking in the Mikrotik router using the following lines
Code: Select all
/ip firewall connection print count-only where protocol=icmp   # counts ICMP connections
/ip firewall connection print count-only where protocol=tcp      # counts ICMP connections
/ip firewall connection print count-only where protocol=udp     # counts ICMP connections
/ip firewall connection print count-only where protocol!=icmp && protocol!=udp && protocol!=tcp  # counts other connection types
However, this script significantly increases the execution time by upto 20 seconds on my Hex, especially when the concurrent connections are around 7,000+ and CPU usage increases significantly as well compared to just simply counting all connections using the code below
Code: Select all
/ip firewall connection print count-only   # counts all types of connection
Is there any work around this? I didn't expect the performance hit to be that much signifant, the requirement is I have to count the ICMP, TCP and UDP connections and have it available in SNMP, perhaps there is another OID to access with less significant performance hit?
Top
 
msatter
Forum Guru
Forum Guru
Posts: 2915
Joined: Tue Feb 18, 2014 12:56 am
Location: Netherlands / Nīderlande

Re: Having the "where" filter in scripting signifantly increases the execution time and increases CPU Usage

Sat Sep 09, 2023 2:42 pm

total - just counted (tcp/udp/icmp) = other connection type.

Saves you one complex line (fourth one)

And the script:
Code: Select all
{
:local icmpCount [/ip firewall connection print count-only where protocol="icmp"]
:local tcpCount [/ip firewall connection print count-only where protocol="tcp"]
:local udpCount [/ip firewall connection print count-only where protocol="udp"]
:local totalCount [/ip firewall connection print count-only]
:local otherCount ($totalCount - $udpCount - $tcpCount - $icmpCount)
:put "icmp: $icmpCount"
:put "tcp: $tcpCount"
:put "udp: $udpCount"
:put "other: $otherCount"
:put "Total: $totalCount"
}
Using that and count in a different way. You test if it is faster than using print:
Code: Select all
{
:local icmpCount [:len [/ip firewall connection find protocol="icmp"]]
:local tcpCount [:len [/ip firewall connection find protocol="tcp"]]
:local udpCount [:len [/ip firewall connection find protocol="udp"]]
:local totalCount [:len [/ip firewall connection find]]
:local otherCount ($totalCount - $udpCount - $tcpCount - $icmpCount)
:put "icmp: $icmpCount"
:put "tcp: $tcpCount"
:put "udp: $udpCount"
:put "other: $otherCount"
:put "Total: $totalCount"
}
\\//,
Last edited by msatter on Sat Sep 09, 2023 3:20 pm, edited 1 time in total.
Top
 
vincej
just joined
Topic Author
Posts: 3
Joined: Fri Oct 08, 2021 2:30 pm

Re: Having the "where" filter in scripting signifantly increases the execution time and increases CPU Usage

Sat Sep 09, 2023 3:19 pm

Saves you one complex line (fourth one)
It does so, and I can definitely use that work around, but even the TCP line is taking too long around 20+ seconds just for TCP when the connection count is around 7-10k+
Code: Select all
/ip firewall connection print count-only where protocol=tcp


compared to simply counting everything and removing the where clause, which only takes 1 second to run constantly. Compared to adding a "where=xxx" clause which causes the query to become x20 slower (and increases as the connection count increases) is a huge impact
Code: Select all
/ip firewall connection print count-only
Top
 
msatter
Forum Guru
Forum Guru
Posts: 2915
Joined: Tue Feb 18, 2014 12:56 am
Location: Netherlands / Nīderlande

Re: Having the "where" filter in scripting signifantly increases the execution time and increases CPU Usage

Sat Sep 09, 2023 3:21 pm

Updated my earlier posting and let me know if that way is faster?
Top
 
msatter
Forum Guru
Forum Guru
Posts: 2915
Joined: Tue Feb 18, 2014 12:56 am
Location: Netherlands / Nīderlande

Re: Having the "where" filter in scripting signifantly increases the execution time and increases CPU Usage

Sat Sep 09, 2023 5:36 pm

And a special one, but here I don't see an improvement in speed but maybe with a huge table it will work faster:
Code: Select all
{
:global udpCount; :global tcpCount; :global icmpCount
/ip firewall connection
:set $udpCount 0; :set $tcpCount 0; :set $icmpCount 0
:local start [:tonsec [:timestamp]]
:foreach k,line in=[/ip firewall connection find] do={:set $name ([get $line protocol]."Count"); [[:parse ":global $name; :set \$$name (\$$name+1)"]];};
:put "Parse nsec: $([:tonsec [:timestamp]] - $start)"
:local totalCount [:len [/ip firewall connection find]]
:local otherCount ($totalCount - $udpCount - $tcpCount - $icmpCount)
:put "icmp: $icmpCount"
:put "tcp: $tcpCount"
:put "udp: $udpCount"
:put "other: $otherCount"
:put "Total: $totalCount"
}
This script only goes once through the connection table and uses dynamic variable names to count each type of traffic. It will count all types and store those in global variables.

Took me two hours to find out how to do this so I really hope it will be faster on huge tables.

Update: times in nanoseconds for each. I have only a small connection table:
Print nsec: 279269101
Find nsec: 430737469
Parse nsec: 597848922
Top
 
User avatar
Amm0
Forum Guru
Forum Guru
Posts: 3597
Joined: Sun May 01, 2016 7:12 pm
Location: California

Re: Having the "where" filter in scripting signifantly increases the execution time and increases CPU Usage

Sat Sep 09, 2023 6:37 pm

It might be quicker to just fetch all the connection via "print" and then looping to get all the data:
Code: Select all
:global getconncounts do={ 
    /ip firewall connection {
        :local startConns [:timestamp]
        :local conns [print proplist=protocol as-value]
        :local startLoop [:timestamp]
        :local counts {tcp=0;udp=0;icmp=0;other=0}
        :foreach conn in $conns do={
            :if ($conn->"protocol" = "tcp") do={
                :set ($counts->"tcp") (($counts->"tcp")+1)
            } else={
                :if ($conn->"protocol" = "udp") do={
                    :set ($counts->"udp") (($counts->"udp")+1)
                } else={
                    :if ($conn->"protocol" = "icmp") do={
                        :set ($counts->"icmp") (($counts->"icmp")+1)
                    } else={
                        :set ($counts->"other") (($counts->"other")+1)
                    }
                }
            }
        }
        :local endTime [:timestamp]
        :set ($counts->"_time_fetching") ($startLoop-$startConns)
        :set ($counts->"_time_processing") ($endTime-$startLoop)
        :set ($counts->"_time_total") ($endTime-$startConns)
        :set ($counts->"total") [:len $conns]
        :return $counts
    }
}
:global conncounts [$getconncounts]
:put $conncounts
:put "UDP: $($conncounts->"udp")"
:put "TCP: $($conncounts->"tcp")"
:put "ICMP: $($conncounts->"icmp")"
:put "other: $($conncounts->"other")"
:put "total: $($conncounts->"total")"

I still like know why "where" is slow, but I believe you. But I'm not sure looping [find] + [get] is quicker – perhaps – be curious. But guess is that the "get" using .id from [:find] is slow if "where" is slow. My script assumes fetching all connections to memory be quicker, even if it has extraneous data. But dunno...

edit: added proplist= to initial "print" script since we don't need all data in-memory, just the protocols. And still open question still if proplist= might slow down too like where...
Top
 
msatter
Forum Guru
Forum Guru
Posts: 2915
Joined: Tue Feb 18, 2014 12:56 am
Location: Netherlands / Nīderlande

Re: Having the "where" filter in scripting signifantly increases the execution time and increases CPU Usage

Sun Sep 10, 2023 1:01 am

Nice scipt Amm0 and it is two to three times faster than the others. I took the liberty to make the loop even faster and only print the times in only nanoseconds.
Code: Select all
{
:local getconncounts do={ 
        :local startConns [:timestamp]
        :local connsnapshot [/ip firewall connection print proplist=protocol as-value]
        :local startLoop [:timestamp]
        :local counts {tcp=0;udp=0;icmp=0;other=0}
        	:foreach conn in $connsnapshot do={:set ($counts->($conn->"protocol")) (($counts->($conn->"protocol"))+1)}
        :set ($counts->"other") ( [:len $connsnapshot] - ($counts->"udp") - ($counts->"tcp") - ($counts->"icmp") )
        :local endTime [:timestamp]
        :set ($counts->"Msatter: _time_fetching") [:tonsec ($startLoop-$startConns)]
        :set ($counts->"_time_processing") [:tonsec ($endTime-$startLoop)]
        :set ($counts->"_time_total") [:tonsec ($endTime-$startConns)]
        :set ($counts->"total") [:len $connsnapshot]
        :return $counts
}
:local start [:tonsec [:timestamp]]
:local conncounts [$getconncounts]
:put $conncounts
:put "UDP: $($conncounts->"udp")"
:put "TCP: $($conncounts->"tcp")"
:put "ICMP: $($conncounts->"icmp")"
:put "other: $($conncounts->"other")"
:put "total: $($conncounts->"total")"
}
Did some benchmarking between the if-then-else and direct naming of variables:
Code: Select all
Ammo:    _time_fetching=36833443;_time_processing=22681377;_time_total=59514820;icmp=5;other=0;tcp=5;total=430;udp=420
Msatter: _time_fetching=37988982;_time_processing=9209711;_time_total=47198693;icmp=5;other=0;tcp=5;total=430;udp=420
                     
Ammo:    _time_fetching=38856161;_time_processing=22287878;_time_total=61144039;icmp=5;other=0;tcp=5;total=438;udp=428
Msatter: _time_fetching=35763124;_time_processing=8821951;_time_total=44585075;icmp=5;other=0;tcp=5;total=438;udp=428
                     
Ammo:    _time_fetching=32369788;_time_processing=19872340;_time_total=52242128;icmp=0;other=0;tcp=5;total=384;udp=379
Msatter: _time_fetching=31878868;_time_processing=8294992;_time_total=40173860;icmp=0;other=0;tcp=5;total=384;udp=379
                     
Ammo:    _time_fetching=52879527;_time_processing=21451338;_time_total=74330865;icmp=0;other=0;tcp=5;total=417;udp=412
Msatter: _time_fetching=37590822;_time_processing=9968770;_time_total=47559592;icmp=0;other=0;tcp=5;total=417;udp=412
                        
Ammo:    _time_fetching=39877700;_time_processing=24086156;_time_total=63963856;icmp=0;other=0;tcp=5;total=469;udp=464
Msatter: _time_fetching=39269780;_time_processing=9487751;_time_total=48757531;icmp=0;other=0;tcp=5;total=469;udp=464
                     
Ammo:    _time_fetching=41335018;_time_processing=23836596;_time_total=65171614;icmp=0;other=0;tcp=5;total=465;udp=460
Msatter: _time_fetching=41098119;_time_processing=9817690;_time_total=50915809;icmp=0;other=0;tcp=5;total=465;udp=460
Top
Post Reply

Who is online

Users browsing this forum: No registered users and 11 guests
  4. RouterOS
  5. Scripting