10 Replies Latest reply on Nov 13, 2015 10:55 AM by pthivierge

    OSIsoft.PowerShell: Data deletion hangs

    André Åsheim

      Hi,

      We have a script that deletes data every night, but after upgrading the PI Data Archive to 2015 it hangs almost every time.

      Any suggestion on how to optimize this process?

       

       [DateTime]$CurrentProcessingTime = $DateProcessingStartTime
              $TimeNowQ = Get-Date
              Write-Host $TimeNowQ "Starting step: Data deletion"
              Write-Output " "
                  $connectionforDel = New-Object System.Data.OleDb.OleDbConnection
                  $connectionforDel.ConnectionString = "Provider=PIOLEDB; Data Source="+$DataSource+";"
                  $connectionforDel.Open()        
                  while ($CurrentProcessingTime -lt $DateProcessingEndTime)
                  {
                      $PISnapShotStatistics = Get-PISnapshotStatistics -Connection $PIDataArchive
                      $PIServerEventQueue = $PISnapShotStatistics.GetValue(7).Value
                      $QueryIntervall = 10
      
                      if($PIServerEventQueue -gt 100){
                      $TimeNowQ = Get-Date
                      Write-Host $TimeNowQ "Waiting will not continue until events in the queue is less than 100"
                      Write-Output " "
                      $TimeNowQ = Get-Date
                      Write-Host $TimeNowQ "Events in queue:" $PIServerEventQueue "waiting 5 seconds"
                      Write-Output " "
                      Start-Sleep -Seconds 5
                      }
                      elseif($PIServerEventQueue -lt 100){
                          [DateTime]$QueryStartTime = $CurrentProcessingTime
                          [DateTime]$QueryEndTime = $CurrentProcessingTime.AddMinutes($QueryIntervall)
                          $QueryStartCulture = [DateTime]::Parse($QueryStartTime,([Globalization.CultureInfo]::CreateSpecificCulture('en-US')))
                          $QueryEndCulture = [DateTime]::Parse($QueryEndTime,([Globalization.CultureInfo]::CreateSpecificCulture('en-US')))
                          $QueryStart = '{0:dd-MMM-yy HH:mm:ss}' -f $QueryStartCulture 
                          $QueryEnd = '{0:dd-MMM-yy HH:mm:ss}' -f $QueryEndCulture
                          
                          $TimeNowQ = Get-Date
                          Write-Host $TimeNowQ "Deleting data for start time:" $QueryStart "end time" $QueryEnd
                          Write-Output " "
                          [string] $delquery = "Select tag From pipoint..classic sub where sub.userint1=1 and sub.userint2="+$ProcessingStartTime+" and sub.pointsource='"+$PointSource+"'"
                          $Delcommand = New-Object System.Data.OleDb.OleDbCommand
                          $Delcommand.Connection = $connectionforDel
                          $Delcommand.CommandText = $delquery
                          $delreader = $Delcommand.ExecuteReader()
                          while ($delreader.read()) {
                              $CurrentPITag = Get-PIPoint -Connection $PIDataArchive -Name $delreader.GetValue(0)
                             # $TimeNowQ = Get-Date
                              #Write-Host $TimeNowQ "Deleting data on tag:"  $CurrentPITag "start time:" $QueryStart "end time:" $QueryEnd
                             # Write-Output " "
                              Remove-PIValue -Connection $PIDataArchive -Event (Get-PIValue -PointName $delreader.GetValue(0) -StartTime $QueryStartTime -EndTime $QueryEndTime -Connection $PIDataArchive) 
                          }
      
                          Write-Output " "
                          [DateTime]$CurrentProcessingTime = $CurrentProcessingTime.AddMinutes($QueryIntervall)
                      }
                      else{
                      $TimeNowQ = Get-Date
                      Write-Host $TimeNowQ "Unable to get PI server event queue."
                      Write-Output " "
                      $TimeNowQ = Get-Date
                      Write-Host $TimeNowQ "Exiting..."
                      Write-Output " "
                      $ProsessTimeFinished = Get-Date
                      Add-PIValue -Value $ProcessFinishedValue -PIPoint $ProcessTag -Timestamp $ProsessTimeFinished 
                      $Connectionfordel.Close()
                      $connection.Close()
                      exit
                  }
      
                  }
              $TimeNowQ = Get-Date
              Write-Host $TimeNowQ "Completed: Data deletion"
      
        • Re: OSIsoft.PowerShell: Data deletion hangs
          pthivierge

          Hello Andre,

           

          It would really help to see what is happening in the PI Server Message logs at the same time it "hangs".  That should provide more information.

          And if you can provide more details about what is happening that would help as well.

           

          --

          In addition it seems that this script could be more efficient by using the AF SDK bulk calls.That means avoiding a foreach loop and multiple network calls.

          1. Read the data for all (or a subset of) the tags at once: see PIPointList.RecordedValues;
          2. Delete the data in chunks of N values: AFListData.UpdateValues

           

          If we are talking about a large amount of data: both operations could run simultaneously  In separate threads, and read data (1) can be pushed to the deletion thread (2) by using a ConcurrentQueue or a BlockingCollection.

          This way you could start delete in small chunks as soon as you start receiving the data. Also worth to mention that if the time range is very big ( which I doubt if you delete every day) then it may be good to separate the total time range into smaller intervalls.  This way it would help the client processing smaller amounts of data and would be memory-wise.

           

          That should be possible to do in Powershell ( the non-multi-threaded part at least), but How about rewriting this into C# as a command line application? (Personally I spend always too much time trying to make things work with Powershell and I prefer when the C# compiler is watching my back... but this is a very personal thought )

           

          Hope this gives you some ideas

            • Re: OSIsoft.PowerShell: Data deletion hangs
              André Åsheim

              Hi,

              This scripts runs every day, deleting only one day of data T-400D-->T-399D (USERINT2)

              I also split the actual calls into one call per tag and only 10 min of data for every call.

              Short how the deletion is done:

               

              $GlobalStart=Today-UserINT2

              $GlobalEnd=$Start-1D

              $Start=$GlobalStart

               

              While($start < $GlobalEnd) {

              If($EventQueue > 100) {

                   Wait 10 seconds

              }

               

              Elseif($EventQueue < 100){

                   For each tag{

                        Delete Start->Start+10m

                   }

                   $Start + 10m

              }

              else{

                   Close connections

                   Exit

              }

               

              I'm no developer so scripting this was my approach and I would like to keep it this way as long as that's possible.

                • Re: OSIsoft.PowerShell: Data deletion hangs
                  pthivierge

                  No problem Andre, we'll need more information to understand why you get "hangs".  If you can put some logging in your script with timestamps to see what action is taking time and also have a look at the PI Server Message Logs that will help a lot.

                  • Re: OSIsoft.PowerShell: Data deletion hangs
                    André Åsheim

                    Attached message logs for failed period and my log output from the script. It looks like it never created the second connection that lists out the actual PI tags.

                    First I have a select distenct USERINT2 to find out what time periods I need to delete

                    inside that I have a select PIPoints and run the deletion

                     

                    DataDeletionFailed.csv = PI Msg Log
                    Processing_Log_to 05.11.2015.log = Logging from script

                      • Re: OSIsoft.PowerShell: Data deletion hangs
                        pthivierge

                        Hello Andre,

                         

                        After looking at the logs here is what I can tell:

                        • What is happening in the PI server logs between the time 5.11.2015 1:06:30 5.11.2015 1:13:37 ? Is there any points locks?
                        • You may try increasing the command timeout as the error:
                          Exception calling "ExecuteReader" with "0" argument(s): "Timeout has expired
                          seems to indicate that the timeout was reached and the object dropped the connection.  I would expect PI Server logs to tell more about it.  However, this period is not covered in the logs provided.

                                  To increase the command timeout, you'll need to add the command timeout parameter in the connection string, here is an example 600s=10m, you may need more, I'll let you determine that from what you see in the logs.

                        "Provider=PIOLEDB; Command Timeout= 600; Data Source="...
                        

                         

                        1 of 1 people found this helpful
                            • Re: OSIsoft.PowerShell: Data deletion hangs
                              pthivierge

                              Thanks Andre,

                               

                              PI Server Logs have not told more that what I could previously see. 

                               

                              In addition to my previous recommendations, I would also recommend you enable PI OLEDB Provider logging.  You need to add the following in the connection string:

                              Log File ='c:\logs.txt';Log Level = 1;

                               

                              this will tell exactly how long the query to retrieve the tags takes.

                              1 of 1 people found this helpful
                                • Re: OSIsoft.PowerShell: Data deletion hangs
                                  André Åsheim

                                  Changed the script to only have one OLEDB connection, I noticed that I got a duplicate session id resulting in the second one to fail. I don't know why I used two connections, but now the second one is removed and I'll let it run for a few days to see if there is any changes. (Turned on logging and increased the timeout.)

                                   

                                  Not sure why we got an timeout earlier, as you can see the execution time for both my query is only one second. Might be same issue that I have a session active in the background that has timed out.

                                  13-Nov-2015 11:31:36.945SQL_ENGINE_INFO_1Processing19984220208SQL EngineCommand to prepare: SELECT DISTINCT  Userint2 FROM [pipoint].[classic] Where Userint1 = 1 and PointSource='c37118'
                                  13-Nov-2015 11:31:36.945SQL_ENGINE_INFO_2Processing19984216788SQL Engine[pipoint..classic classic] [PI SDK] Server->GetPoints2 search: WhereClause = "ptclassname" = "classic" AND "userint1" = 1 AND "pointsource" = "c37118"
                                  13-Nov-2015 11:31:37.522SQL_ENGINE_INFO_1Processing19984216788SQL EngineExecution time: 00:00:01

                                  ...

                                  13-Nov-2015 11:42:40.414SQL_ENGINE_INFO_1Processing19984220208SQL EngineCommand to prepare: Select tag From pipoint..classic sub where sub.userint1=1 and sub.userint2=400 and sub.pointsource='c37118'
                                  13-Nov-2015 11:42:40.57SQL_ENGINE_INFO_2Processing19984216788SQL Engine[pipoint..classic sub] [PI SDK] Server->GetPoints2 search: WhereClause = "ptclassname" = "classic" AND "userint1" = 1 AND "userint2" = 400 AND "pointsource" = "c37118"
                                  13-Nov-2015 11:42:41.568SQL_ENGINE_INFO_1Processing19984216788SQL EngineExecution time: 00:00:01

                              • Re: OSIsoft.PowerShell: Data deletion hangs
                                André Åsheim

                                Maybe worth mentioning that this is 50 hz and 10 hz data only. approx 360 tags.