cancel
Showing results for 
Search instead for 
Did you mean: 
cancel
Showing results for 
Search instead for 
Did you mean: 

Improve performance of thingworx internal postgreSQL above 500GB storage

Highlighted
Newbie

Improve performance of thingworx internal postgreSQL above 500GB storage

Hi,

I am using thingworx internal postgreSQL hosted by ptc cloud services to store both model data and device data.
With the article Where Should I Store My Thingworx Data? it is clear to me that there is no limit of storage with postgres also it performs better upto 500GB.

In my use case I can't purge or delete records so I suspect at some point 500GB limit will reach then what would be the options for me to better the performance?
I don't want to change persistence provider, I am good with postgreSQL so please suggest me a way "How should I manage my db so the performance of queries result better even with high volume of data". I m working with 3000 devices, I have used single data table for single device. I am using data tables because I am receiving relational data. There is no real time data.

I also looked at federation concept, where I supposed to use 2 twx instances where I wanted 1 will act as primary server and 2nd will work as secondary storage. but I am not very clear with federation concept. I tried to implement it with 2 trial instance having same version but failed to connect them.

Any help would be appreciated.

Thanks,

Meenakshi

6 REPLIES 6

Re: Improve performance of thingworx internal postgreSQL above 500GB storage

Meenakshi Agrawal

There are some server side db configurations available to tune PostgresSQL.

Please refer URL for details about the same  https://www.packtpub.com/books/content/server-configuration-tuning-postgresql

For optimizing query on DB you can refer the different Run-time statistics configuration available on PostgreSQL docs.

PostgreSQL: Documentation: 9.6: Run-time Statistics

If you can provide more details about your current DB usage like I/O, read, write.

-Durgesh

Re: Improve performance of thingworx internal postgreSQL above 500GB storage

Hi Meenakshi, the way I see it, performance issues will be required to be addressed on several levels in your scenario, i can try to mention few:

1. ThingWorx Federation is one way to begin with, not sure what errors your received -  for more on Federation system setup you could give this article a try

2. Consider having High Availability on your Data Tier which is PostgreSQL in your case 

3. Optimizing purely from PostgreSQL side, may be consider having special views, materialized views to reduce the computation time on DB so data can simply be fetched with all its related attributes instead of first computing them on DB side and then returning result to your query.

Re: Improve performance of thingworx internal postgreSQL above 500GB storage

Thanks Sushant Pandey​!

As I have mentioned we are working with postgre as twx internal persistence provider that is hosted by ptc so it's monitoring and configurations are out of my hand.

I assume you got my scenario, so with your expertization what would you recommend?

Can I work with federation to keep 500gb into publisher server and after 500gb I will move historical data to subscriber server with a schedular?

I have followed the article as well as video tutorial to configure federation server, but still subscriber status is false.
the error in publisher server's logs is - "No Valid User Name Assigned To Federation Subscriber [subscriber] - subscriber will not be started"

just FYI, I am trying federation with 2 trial foundation server provided by thingworx for 30 days. Is the federation could be performed with trial instances?

I haven't tried it with sandbox and production instances.

Regards,

Meenakshi

Re: Improve performance of thingworx internal postgreSQL above  500GB storage

Meenakshi Agrawal​ , PostGreSQL performs well up to an estimated 500 Gb of data storage (this is a rough estimate dependant on use case).  For very high volume data DSE is the choice, it performs well for large data sets.

I would suggest reading this article from Adam Ressler , very helpful in selecting database for your Thingworx.

Where Should I Store My Thingworx Data?

Re: Improve performance of thingworx internal postgreSQL above 500GB storage

Meenakshi, i have sent you message separately, concerning your other query on trail instance i have not tried them myself. May be someone else who have could help here.

Re: Improve performance of thingworx internal postgreSQL above 500GB storage

Meenakshi, i recommend you to open a case with CS on this because you will need their help in setting up the federated ThingWorx. You/CS will need to likely configure the host correctly to allow reaching them from different ThingWorx instances, configuring just from the Composer might not be sufficient.