AnsweredAssumed Answered

AFListData.UpdateValues performance limits

Question asked by jfauchard on Jul 24, 2019
Latest reply on Aug 1, 2019 by sraposo

Like • Show 0 Likes0
Comment • 14

Hello everyone,

I'm actually having some performance issues about updating a high volume of attributes (not tags).
I would like to know what is the reasonable limit about the number of AFValue to send to UpdateValues ?

Right now I have around 1 200 000 attributes value to update at once (it may not be optimal but this is a client demand).
I'm doing 5 different AFListData.UpdateValues in different threads to increase the speed, but I'm getting kicked by the timeout before the update can end (5 minutes timeout).

Let's imagine I can't cut on the number of attributes, and I need to achieve the fastest update possible. What would be the optimal solution ?

Thank you

sraposo Jul 30, 2019 2:22 PM

Correct Answer

Hey Jason,

Frequently writing to a lot of static attributes (attributes without a data reference) isn't a great idea.

There is a table in the SQL backend that keeps track of all changes in the AF Server. This table is named FindChanges_AT.

Every OSIsoft AF Client (PI Vision, Asset Analytics, Notifications, etc...) leverages the FindChanges method in the AF SDK to identify what changed in the AF Server in a given time interval.

Now depending on the version the behavior is a little different.

Prior to 2017, 1 change to a static attribute = 1 entry in the FindChanges_AT table. If the table goes above 1M records, you may run into performance issues with ALL OSIsoft AF Clients. The table retains 7 days worth of changes by default.

After 2017, 1 change to a static attribute = 1 entry in the FindChanges_AT table, however we have a SPROC that will go and remove "duplicate entries" on a certain interval (I can't remember off the top of my head the interval time). An entry is considered duplicate if it is the same type of change on the same object. So multiple changes (ValueUpdate) to the same static attribute, after the time interval the SPROC runs on, will equal 1 change only.

If your customer is running a version prior to 2017, this is a VERY VERY bad idea.

If they are running 2017, we have a known issue with the new SPROC we introduced and the rate at which duplicate items are cleaned out is smaller by at least 2 orders of magnitude than in 2017 SP2.

If they are running 2017 R2 or later, you can get away with more frequent writes to static attributes. However, if there are more than 1M unique changes in the FindChanges_AT table... you will run into performance issues. If this customer uses AF a lot (especially event frames), 1M unique changes is pretty easy to reach in 7 days IF there are an excessive amount of numerous static attribute updates.

I think each situation is unique, but from my experience, if I were a customer, I would use more tags in this type of situation. It is an additional up front cost, but if you end up spending a ton of time troubleshooting system wide performance issues (that may be impacting production) and determine you need to use those tags, the cost is so much more.... It's not obvious if the first effects manifest in PI Vision or Asset Analytics...

My rule of thumb, time series data goes in the PI Data Archive. It's super highly performant for this type of data.

Relational data (metadata) goes in AF.

Thanks,

Seb

See the reply in context

No one else had this question

Outcomes

rschmitz

Jul 26, 2019 12:05 PM

Hi Jason,

I've been thinking about this question a bit and I think we're going to need a little more context as there are too many variables still in play with your question/situation.

If I understand you correctly, everything you're altering is going to be a non-PIPoint data reference AFAttribute, is that correct? (I understand you're not updating tags directly, but I want to also ensure that you aren't updating attributes that are configured to read from tags as well). So what is the underlying Data Reference type for these attributes?

The reason the underlying Data Reference type interests me is because when using "None" (static) Reference Types, those changes are processed differently than say PI Points or Analysis Data References and have implications for the performance of your system as a result. Specifically, changes for static attributes are stored in the SQL back-end of AF and with sufficient changes to these static attributes the SQL database can grow quickly and cause performance issues. So I think knowing the data reference may help to elucidate some of the issue. My mind jumps to this, because I recall a time prior to Analysis Data References in AF (introduced in 2.6) when this was bigger issue. It's worth noting that if you're planning on updating these attributes more than about once a week, we suggest storing the data in a PI Point for performance sake.

I was reading through the AFDatabase.CheckIn() method documentation, and unfortunately it doesn't have much guidance on what is a reasonable amount of changes at one time. That being said, I know that even if you send all of the changes over at once, they're processed in chunks on the AF Server side of things. So perhaps it would behoove you to send them over in chunks rather than all at once. For example, make a list of 200 AFListData objects (arbitrary choice in size), check those all in, and then create another list of 200 changes to be checked in and see if you run into issues with timeouts.

The other thing I wanted to bring up is using performance counters to track down where bottlenecks in this process are. specifically, I think have monitoring the Windows Performance Counters for CPU, Memory, and Disk I/O on the AF and SQL Servers are going to be most useful. Also it's worth asking if you have any other heavy-hitting custom applications that are hitting AF in a similar way? Could they be offset in their execution timing to prevent resource competition? Do you have other applications that are accessing different SQL databases on the SQL Server that might cause resource contention on the SQL Server? Are you using SQL Express - as this will limit the amount of memory it's willing to consume and is limited to a single Core on your CPU.

Lastly, have you done any testing to find at what amount of changes you're running into issues? Will 500 check ins at once work fine, but it breaks down around 10000 at once?

Cheers,

Rob

Actions

Dan Fishman

@ rschmitz on Jul 26, 2019 12:33 PM

Similar to what Rob mention, I would recommend chunks of 500 or 1,000. In the past, I have used the .NET PartitionClass to help "chunk" my AF objects.. Of course, you could create the chunks yourself. I then might using something like a Parallel.ForEach and set the MaxDegreeOfParellelism relatively small and typically not more than 4 so I do not become a resource hog on SQL. I believe you'll have better performance checking in each chunck if required and could use the CheckedOutMode.ObjectsCheckedOutThisThread enum in the CheckIn overload.

1 person found this helpful

Actions

David Hearn @ Dan Fishman on Jul 26, 2019 1:01 PM

The ChunkedBy extension method was added to the AF SDK in the 2018 SP2 (2.10.5) release which will break an IEnumerable into chunks. The documentation includes an example on how to use this method.

Actions

rschmitz

@ David Hearn on Jul 26, 2019 1:05 PM

David, is the .ChunkedBy() Method only valid for AFSearches or for use with IEnumerable<T> collection? The documentation states "This extension method breaks up search results into chunks", so I'm wondering if the wording is just off here.

Actions

David Hearn @ rschmitz on Jul 26, 2019 1:22 PM

It was added to the AF SDK mainly to make it easier to work with search results, but it is an extension method that will work any IEnumerable<T> collection and break it into chunks.

Actions

jfauchard @ rschmitz on Jul 26, 2019 1:49 PM

Hello Robert,

If I understand you correctly, everything you're altering is going to be a non-PIPoint data reference AFAttribute, is that correct?

This is correct. The value Type is a String and the Data Reference is "None"

It's worth noting that if you're planning on updating these attributes more than about once a week, we suggest storing the data in a PI Point for performance sake.

We have around 26 000 elements that would require 2 tags per element, our client asked us recently to reduce the amount of tags consumed so I don't think he would be happy to consume almost 60 000 tags. I agree it would have been the better solution.

For example, make a list of 200 AFListData objects (arbitrary choice in size), check those all in, and then create another list of 200 changes to be checked in and see if you run into issues with timeouts.

It's the solution I chose so far, using his and Parallel.ForEach execution. I've split all those attributes into AFListData of different sizes (could be between 40 to around 800 changes) for a result of 20 minutes of running time.

The other thing I wanted to bring up is using performance counters to track down where bottlenecks in this process are. specifically, I think have monitoring the Windows Performance Counters for CPU, Memory, and Disk I/O on the AF and SQL Servers are going to be most useful. Also it's worth asking if you have any other heavy-hitting custom applications that are hitting AF in a similar way? Could they be offset in their execution timing to prevent resource competition? Do you have other applications that are accessing different SQL databases on the SQL Server that might cause resource contention on the SQL Server? Are you using SQL Express - as this will limit the amount of memory it's willing to consume and is limited to a single Core on your CPU.

We are using a non Express version, we usually try to offset all executions of our different applications to avoid bottleneck on the server.

Lastly, have you done any testing to find at what amount of changes you're running into issues? Will 500 check ins at once work fine, but it breaks down around 10000 at once?

I didn't really have the time to find the break down point, I know that I was reaching timeout with 200 000+ but it doesn't really mean it was close to the actual limit.

Thank you very much for your answer,

Jason

Actions

skwan @ jfauchard on Jul 29, 2019 11:42 PM

Jason,

Are your AF attributes set as "Configuration Item"?

Actions

jfauchard @ skwan on Jul 30, 2019 6:25 AM

Stephen,

No they are not. They are set as "<None>".

Actions

skwan @ jfauchard on Jul 30, 2019 4:09 PM

Jason,

In case you're wondering why I asked and you were not aware, it's because attributes configured as "Configuration Item" requires a checkin/checkout. As such, there's an extra step and thus impacts performance.

2 people found this helpful

Actions

sraposo @ jfauchard on Jul 30, 2019 2:22 PM

Hey Jason,

Frequently writing to a lot of static attributes (attributes without a data reference) isn't a great idea.

There is a table in the SQL backend that keeps track of all changes in the AF Server. This table is named FindChanges_AT.

Every OSIsoft AF Client (PI Vision, Asset Analytics, Notifications, etc...) leverages the FindChanges method in the AF SDK to identify what changed in the AF Server in a given time interval.

Now depending on the version the behavior is a little different.

If your customer is running a version prior to 2017, this is a VERY VERY bad idea.

If they are running 2017, we have a known issue with the new SPROC we introduced and the rate at which duplicate items are cleaned out is smaller by at least 2 orders of magnitude than in 2017 SP2.

My rule of thumb, time series data goes in the PI Data Archive. It's super highly performant for this type of data.

Relational data (metadata) goes in AF.

Thanks,

Seb

6 people found this helpful

Actions

jfauchard @ sraposo on Jul 31, 2019 7:14 AM

Hello Sebastien,

Thank you very much for this very detailed answer.
I see your point and will try to use those information to make my point to my client.

I'm not surprised at all, we were not a fan of the chosen solution, and were trying to make it work anyway.

Would you think an AFTable would suit my need in this situation ? Since we don't want to keep data that are older than 40 days, it seems overkill to use tags.

Regards,

Jason

Actions

sraposo @ jfauchard on Aug 1, 2019 12:16 PM

AFTable could work, but you need to mindful of it's size. If it's a stored table, I wouldn't go much over 10k rows. The reason is that the table is cached in the clients and the select statements from the table lookup data reference are ran client side. If the data is in a relational database, you could use a linked table. To limit the size for each table lookup select statement, you can use a parametrized query:

https://livelibrary.osisoft.com/LiveLibrary/content/en/server-v12/GUID-D88A1275-A1CA-4D06-8987-0E5B88F5C978

Let us know if you have any questions !

2 people found this helpful

Actions

jfauchard @ sraposo on Aug 1, 2019 1:39 PM

Once again, thank you!

It will workout for me thanks to all your answer. For this particular issue we will probably handle those data with an external SQLite database since it would be the easiest way to implement it without hurting performance for other applications.

Actions

sraposo @ jfauchard on Aug 1, 2019 6:00 PM

For the provider don't use version 5.3.7: https://bugs.mysql.com/bug.php?id=86054

I had a case where this was causing the AF Server to crash. It throws a HeapCorruption exception that can't be caught in our code ...

1 person found this helpful

Actions

14 Replies

rschmitz Jul 26, 2019 12:05 PM
Mark Correct
Correct Answer
Hi Jason,

I've been thinking about this question a bit and I think we're going to need a little more context as there are too many variables still in play with your question/situation.

If I understand you correctly, everything you're altering is going to be a non-PIPoint data reference AFAttribute, is that correct? (I understand you're not updating tags directly, but I want to also ensure that you aren't updating attributes that are configured to read from tags as well). So what is the underlying Data Reference type for these attributes?

The reason the underlying Data Reference type interests me is because when using "None" (static) Reference Types, those changes are processed differently than say PI Points or Analysis Data References and have implications for the performance of your system as a result. Specifically, changes for static attributes are stored in the SQL back-end of AF and with sufficient changes to these static attributes the SQL database can grow quickly and cause performance issues. So I think knowing the data reference may help to elucidate some of the issue. My mind jumps to this, because I recall a time prior to Analysis Data References in AF (introduced in 2.6) when this was bigger issue. It's worth noting that if you're planning on updating these attributes more than about once a week, we suggest storing the data in a PI Point for performance sake.

I was reading through the AFDatabase.CheckIn() method documentation, and unfortunately it doesn't have much guidance on what is a reasonable amount of changes at one time. That being said, I know that even if you send all of the changes over at once, they're processed in chunks on the AF Server side of things. So perhaps it would behoove you to send them over in chunks rather than all at once. For example, make a list of 200 AFListData objects (arbitrary choice in size), check those all in, and then create another list of 200 changes to be checked in and see if you run into issues with timeouts.

The other thing I wanted to bring up is using performance counters to track down where bottlenecks in this process are. specifically, I think have monitoring the Windows Performance Counters for CPU, Memory, and Disk I/O on the AF and SQL Servers are going to be most useful. Also it's worth asking if you have any other heavy-hitting custom applications that are hitting AF in a similar way? Could they be offset in their execution timing to prevent resource competition? Do you have other applications that are accessing different SQL databases on the SQL Server that might cause resource contention on the SQL Server? Are you using SQL Express - as this will limit the amount of memory it's willing to consume and is limited to a single Core on your CPU.

Lastly, have you done any testing to find at what amount of changes you're running into issues? Will 500 check ins at once work fine, but it breaks down around 10000 at once?

Cheers,
Rob
Like • Show 0 Likes0
Actions
- Dan Fishman @ rschmitz on Jul 26, 2019 12:33 PM
  Mark Correct
  Correct Answer
  Similar to what Rob mention, I would recommend chunks of 500 or 1,000. In the past, I have used the .NET PartitionClass to help "chunk" my AF objects.. Of course, you could create the chunks yourself. I then might using something like a Parallel.ForEach and set the MaxDegreeOfParellelism relatively small and typically not more than 4 so I do not become a resource hog on SQL. I believe you'll have better performance checking in each chunck if required and could use the CheckedOutMode.ObjectsCheckedOutThisThread enum in the CheckIn overload.
  1 person found this helpful
  Like • Show 1 Like1
  Actions
  - David Hearn @ Dan Fishman on Jul 26, 2019 1:01 PM
    Mark Correct
    Correct Answer
    The ChunkedBy extension method was added to the AF SDK in the 2018 SP2 (2.10.5) release which will break an IEnumerable into chunks. The documentation includes an example on how to use this method.
    Like • Show 0 Likes0
    Actions
    - rschmitz @ David Hearn on Jul 26, 2019 1:05 PM
      Mark Correct
      Correct Answer
      David, is the .ChunkedBy() Method only valid for AFSearches or for use with IEnumerable<T> collection? The documentation states "This extension method breaks up search results into chunks", so I'm wondering if the wording is just off here.
      Like • Show 0 Likes0
      Actions
      - David Hearn @ rschmitz on Jul 26, 2019 1:22 PM
        Mark Correct
        Correct Answer
        It was added to the AF SDK mainly to make it easier to work with search results, but it is an extension method that will work any IEnumerable<T> collection and break it into chunks.
        Like • Show 0 Likes0
        Actions
- jfauchard @ rschmitz on Jul 26, 2019 1:49 PM
  Mark Correct
  Correct Answer
  Hello Robert,
  
  If I understand you correctly, everything you're altering is going to be a non-PIPoint data reference AFAttribute, is that correct?
  This is correct. The value Type is a String and the Data Reference is "None"
  
  It's worth noting that if you're planning on updating these attributes more than about once a week, we suggest storing the data in a PI Point for performance sake.
  We have around 26 000 elements that would require 2 tags per element, our client asked us recently to reduce the amount of tags consumed so I don't think he would be happy to consume almost 60 000 tags. I agree it would have been the better solution.
  
  For example, make a list of 200 AFListData objects (arbitrary choice in size), check those all in, and then create another list of 200 changes to be checked in and see if you run into issues with timeouts.
  It's the solution I chose so far, using his and Parallel.ForEach execution. I've split all those attributes into AFListData of different sizes (could be between 40 to around 800 changes) for a result of 20 minutes of running time.
  
  The other thing I wanted to bring up is using performance counters to track down where bottlenecks in this process are. specifically, I think have monitoring the Windows Performance Counters for CPU, Memory, and Disk I/O on the AF and SQL Servers are going to be most useful. Also it's worth asking if you have any other heavy-hitting custom applications that are hitting AF in a similar way? Could they be offset in their execution timing to prevent resource competition? Do you have other applications that are accessing different SQL databases on the SQL Server that might cause resource contention on the SQL Server? Are you using SQL Express - as this will limit the amount of memory it's willing to consume and is limited to a single Core on your CPU.
  We are using a non Express version, we usually try to offset all executions of our different applications to avoid bottleneck on the server.
  
  Lastly, have you done any testing to find at what amount of changes you're running into issues? Will 500 check ins at once work fine, but it breaks down around 10000 at once?
  I didn't really have the time to find the break down point, I know that I was reaching timeout with 200 000+ but it doesn't really mean it was close to the actual limit.
  
  Thank you very much for your answer,
  Jason
  Like • Show 0 Likes0
  Actions
  - skwan @ jfauchard on Jul 29, 2019 11:42 PM
    Mark Correct
    Correct Answer
    Jason,
    
    Are your AF attributes set as "Configuration Item"?
    Like • Show 0 Likes0
    Actions
    - jfauchard @ skwan on Jul 30, 2019 6:25 AM
      Mark Correct
      Correct Answer
      Stephen,
      
      No they are not. They are set as "<None>".
      Like • Show 0 Likes0
      Actions
      - skwan @ jfauchard on Jul 30, 2019 4:09 PM
        Mark Correct
        Correct Answer
        Jason,
        In case you're wondering why I asked and you were not aware, it's because attributes configured as "Configuration Item" requires a checkin/checkout. As such, there's an extra step and thus impacts performance.
        2 people found this helpful
        Like • Show 0 Likes0
        Actions
  - sraposo @ jfauchard on Jul 30, 2019 2:22 PM
    Unmark Correct
    Correct Answer
    Hey Jason,
    
    Frequently writing to a lot of static attributes (attributes without a data reference) isn't a great idea.
    
    There is a table in the SQL backend that keeps track of all changes in the AF Server. This table is named FindChanges_AT.
    
    Every OSIsoft AF Client (PI Vision, Asset Analytics, Notifications, etc...) leverages the FindChanges method in the AF SDK to identify what changed in the AF Server in a given time interval.
    
    Now depending on the version the behavior is a little different.
    
    Prior to 2017, 1 change to a static attribute = 1 entry in the FindChanges_AT table. If the table goes above 1M records, you may run into performance issues with ALL OSIsoft AF Clients. The table retains 7 days worth of changes by default.
    
    After 2017, 1 change to a static attribute = 1 entry in the FindChanges_AT table, however we have a SPROC that will go and remove "duplicate entries" on a certain interval (I can't remember off the top of my head the interval time). An entry is considered duplicate if it is the same type of change on the same object. So multiple changes (ValueUpdate) to the same static attribute, after the time interval the SPROC runs on, will equal 1 change only.
    
    If your customer is running a version prior to 2017, this is a VERY VERY bad idea.
    
    If they are running 2017, we have a known issue with the new SPROC we introduced and the rate at which duplicate items are cleaned out is smaller by at least 2 orders of magnitude than in 2017 SP2.
    
    If they are running 2017 R2 or later, you can get away with more frequent writes to static attributes. However, if there are more than 1M unique changes in the FindChanges_AT table... you will run into performance issues. If this customer uses AF a lot (especially event frames), 1M unique changes is pretty easy to reach in 7 days IF there are an excessive amount of numerous static attribute updates.
    
    I think each situation is unique, but from my experience, if I were a customer, I would use more tags in this type of situation. It is an additional up front cost, but if you end up spending a ton of time troubleshooting system wide performance issues (that may be impacting production) and determine you need to use those tags, the cost is so much more.... It's not obvious if the first effects manifest in PI Vision or Asset Analytics...
    
    My rule of thumb, time series data goes in the PI Data Archive. It's super highly performant for this type of data.
    
    Relational data (metadata) goes in AF.
    
    Thanks,
    Seb
    6 people found this helpful
    Like • Show 3 Likes3
    Actions
    - jfauchard @ sraposo on Jul 31, 2019 7:14 AM
      Mark Correct
      Correct Answer
      Hello Sebastien,
      
      Thank you very much for this very detailed answer.
      I see your point and will try to use those information to make my point to my client.
      I'm not surprised at all, we were not a fan of the chosen solution, and were trying to make it work anyway.
      
      Would you think an AFTable would suit my need in this situation ? Since we don't want to keep data that are older than 40 days, it seems overkill to use tags.
      
      Regards,
      Jason
      Like • Show 0 Likes0
      Actions
      - sraposo @ jfauchard on Aug 1, 2019 12:16 PM
        Mark Correct
        Correct Answer
        AFTable could work, but you need to mindful of it's size. If it's a stored table, I wouldn't go much over 10k rows. The reason is that the table is cached in the clients and the select statements from the table lookup data reference are ran client side. If the data is in a relational database, you could use a linked table. To limit the size for each table lookup select statement, you can use a parametrized query:
        
        https://livelibrary.osisoft.com/LiveLibrary/content/en/server-v12/GUID-D88A1275-A1CA-4D06-8987-0E5B88F5C978
        Let us know if you have any questions !
        2 people found this helpful
        Like • Show 0 Likes0
        Actions
        jfauchard @ sraposo on Aug 1, 2019 1:39 PM
        Mark Correct
        Correct Answer
        Once again, thank you!
        
        It will workout for me thanks to all your answer. For this particular issue we will probably handle those data with an external SQLite database since it would be the easiest way to implement it without hurting performance for other applications.
        
        Like • Show 0 Likes0
        Actions
        sraposo @ jfauchard on Aug 1, 2019 6:00 PM
        Mark Correct
        Correct Answer
        For the provider don't use version 5.3.7: https://bugs.mysql.com/bug.php?id=86054
        
        I had a case where this was causing the AF Server to crash. It throws a HeapCorruption exception that can't be caught in our code ...
        1 person found this helpful
        Like • Show 0 Likes0
        Actions

AFListData.UpdateValues performance limits

Outcomes

Related Content

Recommended Content