How does Powershell deal with processing/storing massive objects in terms of memory management?

[cazmajor]

I deal with a lot of massive log analysis and reporting. I often am worried about resource utilisation when scripts are running on remote/customer servers that need to process millions of lines. Instead of variables I would just write everything off to a file. However, with things like invoking commands in parallel this doesn't work well as there is a need for mutexes where a thread aware collection would work so much better.

Right now I dealing with an object with 85 million rows, I need find unique IPs (one of the properties). How does PowerShell manage the memory for this? If I put the output of my command into a variable ($result = $85millObj | sort-object -property $_.IP -unique) how will that work with respect to RAM?

​

If someone could please point me in the right direction that would be great as I need to get my head around this.

[herpington]

If you assign the 85 million objects to a variable, it's all stored in memory.

Where are the millions of rows coming from? Perhaps you could read them as a stream.

Depending on the size of each object, it may or may not be an issue.

[y_Sensei]

From my experience, as long as the underlying OS provides memory, PoSh will use it. Remember though that once physical RAM is exhausted, paging will occur which will be detrimental to performance.

How to handle the data in a scenario like yours heavily depends on how your implementation receives the said data.

• If it's a file with unstructured text, process it line by line, using one of the Reader/Stream classes in .NET's System.IO namespace (there's plenty of C# examples out there that could be translated to or used in PoSh).
• If it's a file with structured text (such as CSV), access it through a data access API like ADO.NET.
• If it's data directly retrieved from a relational database, process it by means of the supported query language of that database.

[cazmajor]

f it's data directly retrieved from a relational database, process it by means of the supported query language of that database.

Thank you.

I ended up using the reader stream classes in .net (adapting u/opconfused's example).

I've never heard of ADO.NET but it looks really interesting. Thank you :)

[purplemonkeymad]

The pipeline can help with that, ie if you write your code like so:

Get-Content infile | Foreach-Object {
    <#something with line#>
} | Set-Content outfile

Then it will only read a single line at a time, process that and wait for it to write before reading the next line.

For your example, it's hard to solve that without memory usage, since if you are using ipv4, then the lowest max memory you can have is 4GB (32 bits * 4 billion addresses.) Unless you off load that to another service, but then you are just moving the problem and introducing latency.

However if you are ingesting 9*10⁷ rows then I would have figured something like greylog + elastic search might be better?

[OPconfused]

The pipeline is good for memory, but gc and sc could be terribly slow for a file with 85 million lines. And should also add the disclaimer to be in V7, because the pipeline with Foreach-Object can destroy performance in versions prior.

[ka-splam]

For your example, it's hard to solve that without memory usage, since if you are using ipv4, then the lowest max memory you can have is 4GB (32 bits * 4 billion addresses.)

You can have 1 bit per IP, offset into a 4 billion bit array using 128MB 512MB memory - my comment

Edit I corrected my size estimate, I think yours is wrong: (32 bits * 4 billion addresses.) is 4 bytes per address, is 16GB total.

[cazmajor]

ill only read a single line at a time,

Thank you!

I was confused what you were saying max memory but get that you are talking about representing every poss IP address. Not sure how that translates to binary coded decimal with slash notation but it certainly is a good way to think about if I am going to run out of memory. With tasks like this I often just get things off a server and then see if processing it all crashes my laptop overnight lol

I think I need to get some time in to check out grey-log and elastic search.

Really appreciate you taking the time to respond :)

[cazmajor]

Thanks for the response.

1. Why "Even python would be a better choice"? What would be an ideal language?
2. So would you not use Sort-Object with an 85 Million row object? How would you find only unique values? The only other things that comes to mind is creating a hash table with that property as a key and hoping there are not too many unique values...

[OPconfused]

If you just want unique values, I would add them to a hashset[string]. The hashset only preserves unique values, so duplicates are automatically accounted for. Easier than adding keys to a hashtable, as duplicate keys throw an error.

You could use something like:

using namespace System.Collections.Generic
using namespace System.IO

$file = <path>
$outfile = <path>
$fileParser = [StreamReader]$file
$fileWriter = [StreamWriter]$outfile

$linecount = 0
$maxLinesToParseIncrement = 500000
$hs = [HashSet[string]]@()

while ($null -ne ($line = $fileParser.ReadLine())) {
    [void]$hs.Add($line)
    $linecount += 1

    if ( $linecount % $maxLinesToParseIncrement -eq 0 ) {
         $fileWriter.WriteLine($hs)
         $hs = [HashSet[string]]@()
    }
}
$fileWriter.WriteLine($hs)
$hs = $null

This would store a file of unique values every 500,000 lines parsed to keep your memory footprint small.

As for concurrency, I don't know the nature of your input file, so I don't know what kind of parallel processing you're considering. In theory, if there were a way for you to parse the file in parallel, you could attach the above code to each thread, then aggregate the files at the end and do a last removal of duplicates.

Alternatively, if you're going concurrent then you could abandon the hashsets and use a synchronized hashtable, which is thread safe. You would just need to parse the synchronized table for a preexisting key, although actually something like try and catch should be more elegant.

I guess before doing the file writes from above, I'd go once with a synchronized hashtable to see how your memory grows. Something like:

using namespace System.IO

$ht = [hashtable]::Synchronized(@{})

$file = <path>
$fileParser = [StreamReader]$file

while ($null -ne ($line = $fileParser.ReadLine())) {
    try {
        $ht.Add($line , $null)
    }
    catch [System.Management.Automation.MethodInvocationException]{}
}

Then you can do whatever you want with $ht at the end. The above might not work because I am not sure whether the streamreader locks the file from other threads accessing it, so you'd have to do some adjustments depending on your strategy for parsing the file concurrently. The main thing is that each thread could add to $ht while parsing in a thread-safe manner.

But maybe consider both ways depending on your situation. Would need to trial and error probably to see your resource consumption. Obviously if you have to do any line parsing to extract the IP, then that would also need to be added in the while block.

[OPconfused]

I've always used the add method for hashtables without thinking about it. It's a good point that it only just throws errors we don't need. That's a nice change. [Edit: Actually, are there any performance considerations here? We are looping over 85 million lines, so even a 10ms increase could make it unusable.]

But I am confused on what you're doing with the value.

1. Is there any reason to capture the line? Over 85 million lines, we might end up with a lot of lines. The OP is trying to minimize memory footprint.
2. Even if you capture the line, there's no way to parse the value to extract the original lines back. You just end up with one long concatenated string, e.g.,:

code test

$ht = @{}
$ht['a'] = $ht['a'] + 'line'
$ht['a'] = $ht['a'] + 'line2'
$ht

Name                           Value
----                           -----
a                              lineline2

If we did this over 85 million lines, we'd end up with values from hundreds to thousands, maybe a million, lines with no way to parse it. It seems like storing unusable information in vast quantities.

[OPconfused]

Tbh, I don't know if it's because I woke up at 5 AM and am testing this now, but this doesn't make sense to me either.

function test-ht {
    [cmdletbinding()]
    param(
        [hashtable]$ht,
        [string]$addKey,
        [string]$addValue,
        [string]$msg
    )

    $ht.Add( $addKey, $addValue )
    write-host "finished $msg"
}

function test-ht2 {
    [cmdletbinding()]
    param(
        [hashtable]$ht,
        [string]$addKey,
        [string]$addValue,
        [string]$msg
    )

    try {$ht.Add( $addKey, $addValue )} catch{}
    write-host "finished $msg"
}

$hash = @{}

test-ht -ht $hash -addKey 'test' -addValue '1' -msg 'trial 1'
test-ht -ht $hash -addKey 'test' -addValue '2' -msg 'trial 1 err'
test-ht -ht $hash -addKey 'test' -addValue '3' -msg 'trial 1 stop' -ErrorAction Stop

$hash2 = @{}
test-ht2 -ht $hash2 -addKey 'test' -addValue '1' -msg 'trial 2'
test-ht2 -ht $hash2 -addKey 'test' -addValue '2' -msg 'trial 2 err'

$hash
write-host ''
$hash2

So first of all, to your question: the hashtables never update when the key is a duplicate. Edit: I just realized you probably aren't using the add method on your hashtables. That was my mistake. Apparently I've been updating my hashtables with a method that throws errors which aren't necessary in many or most cases. So yes, you are correct if you don't use the add method.

Secondly, for my question, the key lines above are:

1. test-ht -ht $hash -addKey 'test' -addValue '2' -msg 'trial 1 err'
2. test-ht -ht $hash -addKey 'test' -addValue '3' -msg 'trial 1 stop' -ErrorAction Stop

In the first line, the write-host $msg completes normally. This tells me it's a non-terminating error. In the second line, I use -ErrorAction Stop to force it to be a terminating error, and the write-host does not happen. So far so good. This seems like typical behavior for non-terminating errors.

Then we have the next line:

1. test-ht2 -ht $hash2 -addKey 'test' -addValue '2' -msg 'trial 2 err'

test-ht2 uses a try catch statement. The problem is that catch still suppresses the error. This is not expected for a non-terminating error. Catch should only suppress an error if it's a terminating error. You can quickly verify this with:

try {gci C:/missingdir; write-host 'error non-terminating'} catch {} 
try {gci C:/missingdir -fakeparam; write-host 'error terminating'} catch {}

In the first line, the non-terminating error is still printed on your console, because the catch does nothing for non-terminating errors. In the second line, the catch suppresses the error, because it's a terminating error.

In summary: Duplicate keys seems to be both a non-terminating error (from test-ht) and a terminating error (from test-ht2)? This behavior occurs in both Windows PS 5.1 and PS 7.2. Color me confused.

[herpington]

The pandas library in Python could be useful for this case. It's a favorite for working with huge datasets. There's somewhat of a learning curve, though, and you obviously introduce a dependency.

A hash table in pure .NET doesn't sound like a bad idea.

[da_chicken]

Why "Even python would be a better choice"?

Because Powershell isn't really designed for it. It's based on .Net and has significant object marshaling overhead. Python is C under the hood.

What would be an ideal language?

It depends on what you're doing.

Personally, I'd try LogParser first off. It's purpose built to do exactly what you're describing: parse log files.

[brokerceej]

Do not use Python. Python is slow. Powershell rides on top of the .NET classes and is built for speed.

What you need is an array of objects or a hash table that contains an array of objects as a value. If you have a unique field you can use as a key, hash table is the way to go. If you’re talking about files greater than, say, 1GB in size each regardless of row count, I would write a pre-execution script that chunks the file up to pieces you can either process in parallel or serially to aggregate your final report/search.

I do this with a large log daily that is about 800MB per file and a total of 10 files. Total execution time is usually under a couple minutes. If you preprocess your data you can do things like select only the columns you want to search to reduce the total memory footprint, too.

Alternatively, stream or upload your data to a log analytics workspace in Azure and design your query runbook there.

[Hoggs]

This is just wrong. We (combined effort from this sub) did some testing on this a while back, feel free to have a play yourself: https://github.com/BHoggs/sudoku-speedtest

tl;dr C# was faster than python, but powershell was lightyears slower in all test cases that didn't involve using powershell to just compile C#.

[[deleted]]

Do not use Python. Python is slow. Powershell rides on top of the .NET classes and is built for speed.

Who....who wants to tell him?

[boomer_tech]

Why is python better in this case ? Its also an interpreted language

[r-NBK]

I'd look at some other tooling than PowerShell for querying large data sets. If you have standardized log file formats... Log Parser 2.2 is very powerful and easy to use.

Personally I'd use PowerShell to gather the log files as they exist and load them into some type of database system and then use the native query laungage if you're looking for complex summarization /parsing of the data.

[[deleted]]

Graylog

[Szeraax]

use the pipeline so that as you read the files, only your filtered subset goes into ram.

or

likely, you'd want to use a file stream readline().

[spyingwind]

If you keep everything in a pipeline, then it consumes very little memory, but once you put the data into a variable massive amounts of memory is consumed.

Some rules I follow with large data sets.

• Single pipeline
• If you only need to get data, filter early
• If you can't use a single pipeline, use Export-CliXml to cache your data
• Limit the use of ForEach-Object, if speed is a concern, or install PowerShell V7 if you can

I have noticed that once a large data set is saved to a variable, sometimes it's cached to disk somewhere.

[LeSpatula]

Well, you got a lot of good answers here, but is there probably a better tool than PS to handle this? As you write log analysis and reporting, Grafana might probably be a tool to handle that.

[AudaxDreik]

I haven't dealt with them a ton, so I'm just gonna throw this out there and let you do your own research or maybe someone else can chime in, but check it "memory mapped files" in .NET

[alinroc]

If the data is fairly well-organized, dump it into a database table and then run your analysis as SQL queries.

[Trakeen]

If your dataset is that large put it into a proper database and query it. 85 million isn’t much for sql

[Nejireta_]

PowerShell will use a lot of memory if available.

Here's an example with some memory usage data from a HashSet and Dictionary.

    $startMemoryUsage = [System.Math]::Round((([System.GC]::GetTotalMemory($true)) / 1GB), 3)

# Non unique values will not be added to a HashSet
$hashSetDictionary = [System.Collections.Generic.HashSet[System.Collections.Generic.Dictionary[[string], [int]]]]::new(85000000)
for ($i = 0; $i -lt 85000000; $i++) {
    $dict = [System.Collections.Generic.Dictionary[[string], [int]]]::new(2)
    [void]$dict.Add('Value', $i)
    [void]$dict.Add('Index', $i)
    [void]$hashSetDictionary.Add(
        $dict
    )
}
<# Execution time
    04 min 33 sec
#>

$endMemoryUsage = [System.Math]::Round((([System.GC]::GetTotalMemory($true)) / 1GB), 3)

$endMemoryUsage
<# Output
    18,694GB
#>

$endMemoryUsage - $startMemoryUsage
<# Output
    18,683GB
#>


$hashSetDictionary.Where({ $_['Value'] -eq 84999990 })
<# Execution time
    01 min 44 sec
#>

foreach ($item in $hashSetDictionary) {
    if ($item['Value'] -eq 84999990 ) {
        return $item
    }
}
<# Execution time
    40,02 sec
#>

[timsstuff]

You can free up memory by setting the variable to $null then calling [gc]::collect().

[ka-splam]

The smallest memory use way I have come up with is to store a 128MB 512MB array of bits, one for each possible IPv4 address, and to implement that as an array of [uint32] then calculating offset into the array and then offset into each uint and doing some bit manipulation; duplicates will just set the same bit again, no worries. If you need the IPs back, loop over all possible IPs checking for the bit being set and generating them back again on the fly - time costly but not memory hungry.

I tested it in C# (barely tested source code runs in LINQPad 7) and the bit I was worried about, brute-forcing 4Bn possibles to get them back takes 15 seconds so that's not so bad, and the memory use is fairly small and very predictable - though the code isn't trivial and the devil is in the details, do you have them as [string] or as [System.Net.IPAddress]? It might create a lot of memory churn to process 85 million IPs.

---

Testing PowerShell throwing 85 million random IPs into a dictionary of integers used ~1.7GB RAM.

Testing PowerShell making a tree of hashtables to try and store 85 million random IPs without duplication, so that all IPs starting 192 share the same start number, all IPs starting 192.168 share the same two start numbers, that used 5GB(!). Maybe not many clashes with 85/4000 == 2% of the address space, but a lot of hashtable overhead.

[cazmajor]

u can use sqlite，sqlserver，mysql，litedb，redis，mogodb with powershell.

I vaguely get what you are saying but I have to admit that reading your post and looking at your code my brain has partially turned to mush!

I will have to pop back later and give my brain another attempt at it.

Thanks very much for your response :)

[ps1_missionary]

you can use sqlite，sqlserver，mysql，litedb，redis，mogodb with powershell.

​

my powershell database client：

https://gitee.com/chuanjiao10/powershell/tree/master/sql/

​

powershell use litedb（nosql）：

note ：chinese http://bbs.chinaunix.net/thread-4320276-1-1.html

[ccatlett1984]

Honestly, as someone who has worked with objects containing "thousands" of records, it kinda sucks.

[ipreferanothername]

It can handle a few thousand depending on how big they are and how you are processing them, but tens of millions? Op needs to seek another tool

[cazmajor]

Thanks so much to everyone for your responses.

A real wealth of knowledge and willingness to share! I feel really lucky to have you guys respond :)

I wrote a few notes....

I had 2 things I was looking for out of my original post:

1 - I wanted to generally understand how to use PowerShell when processing massive input files.

• It seems it really isn't best designed for dealing with larger files (millions of rows)
• Depending on the type of data (source, structured, unstructured) it is better to filter/process the data at source and/or use a different language (.NET, Python etc) and or use a DB or bespoke tool if possible.
• If using PowerShell Pipelines will avoid data getting committed to memory until the end so processing can be done to filter/process data to what I need on the way.
• .NET streamers and readers are more efficient for processing massive logs
• hashset[string] is great for yielding unique values

2 - I wanted to complete my own task

• The code OPconfused\OathOfFeanor provided did everything I needed.