# Historian Table Partitioning **URL:** \ **Category:** Ignition\ **Created:** [August 8, 2018, 8:24pm UTC](https://forum.inductiveautomation.com/t/historian-table-partitioning/19155 "2018-08-08T20:24:46Z")\ **Posts on this page:** 20\ **Page:** 1
**Author:** ![jlteel](https://avatars.discourse-cdn.com/v4/letter/j/c67d28/32.png) [@jlteel](https://forum.inductiveautomation.com/u/jlteel)\ **Post date:** [August 8, 2018, 8:24pm UTC](https://forum.inductiveautomation.com/t/historian-table-partitioning/19155/1 "2018-08-08T20:24:46Z")
A system that we set up has a large number of tags and is recording data via the historian at a very fast rate, such that between 30 and 40 million rows are being added to a historian table each day. Currently, I have the historian configured to create a new partition each day. Performance in recalling trend data for a long period of time is very, very poor. Does anyone have suggestions on how I might best improve the performance? Should I partition even more frequently? Should I used “pre-processed partitions,” and if so, can someone explain what the “window size” parameter actually does? Should I create additional historian connections to my database and divide the tags up among them? Other suggestions? Thanks very much! ---
**Author:** ![jlteel](https://avatars.discourse-cdn.com/v4/letter/j/c67d28/32.png) [@jlteel](https://forum.inductiveautomation.com/u/jlteel)\ **Post date:** [August 21, 2018, 2:09pm UTC](https://forum.inductiveautomation.com/t/historian-table-partitioning/19155/2 "2018-08-21T14:09:46Z")
Any thoughts on this? I’m not sure where to turn next. ---
**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\ **Post date:** [August 21, 2018, 2:46pm UTC](https://forum.inductiveautomation.com/t/historian-table-partitioning/19155/3 "2018-08-21T14:46:59Z")
I would use wide tables in transaction groups instead of the historian. More data in fewer rows, common t\_stamp column. Index on t\_stamp. Much lower load on the DB. You lose the individual deadband controls for optimized recording, but it sounds like that doesn’t apply. ---
**Author:** ![R.Alamsha](https://avatars.discourse-cdn.com/v4/letter/r/e5b9ba/32.png) [@R.Alamsha](https://forum.inductiveautomation.com/u/R.Alamsha)\ **Post date:** [August 21, 2018, 2:48pm UTC](https://forum.inductiveautomation.com/t/historian-table-partitioning/19155/4 "2018-08-21T14:48:26Z")
> [@jlteel](#): > > 30 and 40 million rows are being added to a historian table each day. Currently Looks like a Big Data project. You need to look into some Big data solutions like Apache Kafka which has the ability to store and serve large volumes of historical data. You can PubSub massive volumes of data from Ignition to Kafka broker. You may have to build a "Kafka PubSub client module" for Ignition. There's no JDBC driver for Kafka. I couldn't find any other better solution. This is a very good challenge and you must take it head on. Check the professional solutions and services provided by Confluent. > **[Confluent | Apache Kafka Reinvented for the Cloud](https://www.confluent.io/)** > > Confluent makes it easy to connect your apps, data systems, and entire business with secure, scalable, fully managed Kafka and real-time data streaming, processing, and analytics. ---
**Author:** ![R.Alamsha](https://avatars.discourse-cdn.com/v4/letter/r/e5b9ba/32.png) [@R.Alamsha](https://forum.inductiveautomation.com/u/R.Alamsha)\ **Post date:** [August 21, 2018, 4:40pm UTC](https://forum.inductiveautomation.com/t/historian-table-partitioning/19155/5 "2018-08-21T16:40:43Z")
> [@jlteel](#): > > 30 and 40 million rows are being added to a historian table each day. Which db do you have? Are you using TG for datalogging?. If so, can you provide TG config details? At what rate data is updated?. Like, no of rows per x secs?. ---
**Author:** ![jlteel](https://avatars.discourse-cdn.com/v4/letter/j/c67d28/32.png) [@jlteel](https://forum.inductiveautomation.com/u/jlteel)\ **Post date:** [August 21, 2018, 5:13pm UTC](https://forum.inductiveautomation.com/t/historian-table-partitioning/19155/6 "2018-08-21T17:13:30Z")
Thanks for your feedback, guys. Phil, your idea might have been the right answer if it had occurred to me about a year ago. But at this point, I’m tied into using the historian because we have an entire “graphical replay” system with dozens and dozens of templates that is dependent on using system.tag.queryTagHistory() functions. I’m sure I could figure out a way around using the historian, but with the number of tags our system has, I’m not sure it’s feasible to recommend that path now. R.Alamsha, the site has a PI historian already, and this information is duplicated there. PI functionality is fine, so we don’t want another Big Data solution. The issue I have is with the Ignition trending/charting. Retrieving data over a one month or longer period is almost infeasible. Yes, I’m logging using a historical tag group. It executes at once per second, which is the customer requirement. Way before I started this project, I ran this hypothetical architecture past IA, and was told that we’d have no trouble. Well, we have no trouble storing the data, but long queries are just terrible. The trending system is almost unusable. Any additional thoughts? Thanks again. ---
**Author:** ![R.Alamsha](https://avatars.discourse-cdn.com/v4/letter/r/e5b9ba/32.png) [@R.Alamsha](https://forum.inductiveautomation.com/u/R.Alamsha)\ **Post date:** [August 21, 2018, 5:52pm UTC](https://forum.inductiveautomation.com/t/historian-table-partitioning/19155/7 "2018-08-21T17:52:26Z")
> [@jlteel](#): > > Well, we have no trouble storing the data, but long queries are just terrible. The trending system is almost unusable. Any additional thoughts? Do you mean, you need a better Data Visualization solution?. ---
**Author:** ![Brian](https://avatars.discourse-cdn.com/v4/letter/b/97f17d/32.png) [@Brian](https://forum.inductiveautomation.com/u/Brian)\ **Post date:** [August 21, 2018, 6:26pm UTC](https://forum.inductiveautomation.com/t/historian-table-partitioning/19155/8 "2018-08-21T18:26:23Z")
You don’t have your easy charts setup for raw data resolution, do you? Or some really big number in the fixed resolution mode? I don’t think either of these is the default for an easy chart, but it doesn’t hurt to ask… ---
**Author:** ![jlteel](https://avatars.discourse-cdn.com/v4/letter/j/c67d28/32.png) [@jlteel](https://forum.inductiveautomation.com/u/jlteel)\ **Post date:** [August 21, 2018, 7:37pm UTC](https://forum.inductiveautomation.com/t/historian-table-partitioning/19155/9 "2018-08-21T19:37:03Z")
No, the problem is not with the visualization, but the slow query speeds when populating a trend with any significant duration. It doesn’t matter whether I’m using an Easy Chart, or a system.tag.queryHistory() function: if I request data spanning much beyond a week or so, the speed of the query is not acceptable. This issue, as @pturmel alluded, is one of database performance. That’s why I asked about table partitioning and for more information about “pre-processed partitions.” I think this is just a limitation of SQL Server or any other database server. When I have tens of millions of rows per table, it’s just going to be slow. But if I partition the tables further, then many of my queries will span more tables, which will also be slow. So I don’t know if there’s a sweet spot with the partitions, if pre-processing is helpful, or if I just need to tell my customer that they’re going to have to live with poor performance. ---
**Author:** ![jlteel](https://avatars.discourse-cdn.com/v4/letter/j/c67d28/32.png) [@jlteel](https://forum.inductiveautomation.com/u/jlteel)\ **Post date:** [August 21, 2018, 7:37pm UTC](https://forum.inductiveautomation.com/t/historian-table-partitioning/19155/10 "2018-08-21T19:37:37Z")
No, the issue isn’t with the Easy Chart, but thanks. ---
**Author:** ![dkhayes117](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/dkhayes117/32/68344_2.png) [@dkhayes117](https://forum.inductiveautomation.com/u/dkhayes117)\ **Post date:** [August 21, 2018, 7:45pm UTC](https://forum.inductiveautomation.com/t/historian-table-partitioning/19155/11 "2018-08-21T19:45:17Z")
> [@jlteel](#): > > I think this is just a limitation of SQL Server or any other database server. You might try asking on StackOverflow, they have some really good DB forums IMHO. ---
**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\ **Post date:** [August 21, 2018, 10:30pm UTC](https://forum.inductiveautomation.com/t/historian-table-partitioning/19155/12 "2018-08-21T22:30:20Z")
I haven’t had to optimize the Historian, as I don’t ever put millions of rows in it. /-: But I’d start with studying all of the indices on the tables to make sure the queries are optimizable and optimization statistics are present. I would make sure there is a [BRIN index](https://www.postgresql.org/docs/9.5/static/indexes-types.html) on the t\_stamp column, and a separate b-tree index on the tag id column. Recommended indexing would vary with DB brand, of course. Larger partitions should be tolerable with proper indices. The [EXPLAIN ANALYZE …](https://www.postgresql.org/docs/9.5/static/sql-explain.html) command will show you what the optimizer decides and how well it works. I wouldn’t use anything older than PostgreSQL 9.5. ---
**Author:** ![R.Alamsha](https://avatars.discourse-cdn.com/v4/letter/r/e5b9ba/32.png) [@R.Alamsha](https://forum.inductiveautomation.com/u/R.Alamsha)\ **Post date:** [August 22, 2018, 9:11am UTC](https://forum.inductiveautomation.com/t/historian-table-partitioning/19155/13 "2018-08-22T09:11:18Z")
> [@jlteel](#): > > I think this is just a limitation of SQL Server or any other database server. When I have tens of millions of rows per table, it's just going to be slow.. Check out this writeup.. > [@Ignition + Apache Kafka =\> Build Massively Scalable Projects - Demo](http://forum.inductiveautomation.com/t/ignition-apache-kafka-build-massively-scalable-projects-demo/19317/2): > > Let’s build a Big Data Kafka client module for Ignition – "IgnitionKafkaClient.modl": Why do we need a “Big Data Kafka client” for Ignition?: Few years back, most of us would have never imagined a SCADA project generating and logging millions of records per day into a historian database. Today, Big Data has become the hottest topic among SCADA professionals including this forum. Even highly accomplished SCADA veterans are becoming bit nervous and taking a back step when this topic is opened up… ---
**Author:** ![joeyjojoshabadu](https://avatars.discourse-cdn.com/v4/letter/j/f9ae1b/32.png) [@joeyjojoshabadu](https://forum.inductiveautomation.com/u/joeyjojoshabadu)\ **Post date:** [September 5, 2018, 4:45pm UTC](https://forum.inductiveautomation.com/t/historian-table-partitioning/19155/14 "2018-09-05T16:45:24Z")
If the width of your plot is 1000 pixels then there is no point in returning more than 1000 datapoints. If you are plotting a whole day you will have 86,400 samples but can only display 1000. If you set the pre-processed partition roll up time period to 60s then you will have another table per partition with one sample per minute or 1440 samples per day. If your query/plot doesn’t require more than 1 sample per minute then it should use the pre-processed table. I suppose if you are plotting a week it will have to hit 7 pre-processed tables with an individual query, but maybe 7 queries is faster than downsampling 500k samples for each series? I ran in to some funny business where the database faulted for unknown reason and then ignition stopped populating the pre-processed table after the database connection was restored so long time period plots didn’t have any data, so it might not be fully baked. ---
**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\ **Post date:** [September 5, 2018, 4:53pm UTC](https://forum.inductiveautomation.com/t/historian-table-partitioning/19155/15 "2018-09-05T16:53:49Z")
> [@joeyjojoshabadu](#): > > If the width of your plot is 1000 pixels then there is no point in returning more than 1000 datapoints. It matters if you wish to use the built-in zooming and panning. Those operations do not requery for the changed resolution. ---
**Author:** ![joe.tamaki](https://avatars.discourse-cdn.com/v4/letter/j/cdc98d/32.png) [@joe.tamaki](https://forum.inductiveautomation.com/u/joe.tamaki)\ **Post date:** [October 9, 2018, 8:06pm UTC](https://forum.inductiveautomation.com/t/historian-table-partitioning/19155/16 "2018-10-09T20:06:10Z")
We have a historian performing well with 48 million rows per 12 hrs. Our partitioning is set to 12hr intervals. We’re using Debian OS with MariaDB and TokuDB storage engine. If changing OS and storage engines is an option for you then I think this would work. ---
**Author:** ![jlteel](https://avatars.discourse-cdn.com/v4/letter/j/c67d28/32.png) [@jlteel](https://forum.inductiveautomation.com/u/jlteel)\ **Post date:** [October 10, 2018, 2:27am UTC](https://forum.inductiveautomation.com/t/historian-table-partitioning/19155/17 "2018-10-10T02:27:08Z")
Thank you, Joe. When you say that this performs well, does it perform well when querying data that spans a month or more? That would be 60+ joins, and I can’t imagine that being very speedy. But if it is, I’ll consider switching, or at least using your suggested architecture for future projects. ---
**Author:** ![joe.tamaki](https://avatars.discourse-cdn.com/v4/letter/j/cdc98d/32.png) [@joe.tamaki](https://forum.inductiveautomation.com/u/joe.tamaki)\ **Post date:** [October 10, 2018, 7:09pm UTC](https://forum.inductiveautomation.com/t/historian-table-partitioning/19155/18 "2018-10-10T19:09:56Z")
I did some trials using system.tag.queryTagHistory for 2 months of data, Aug 1st to Oct 1st. Tags were a mix of bools, integers and reals 3 tags took 5.7s, 5 tags took 4.7s, 6 tags took 4.4s, 7 tags took 5.4s. Here are the results and script. > Number of tags: 3 > returnSize :10000 > Query start time: 2018-10-10 12:21:22:022 > Query end time: 2018-10-10 12:21:27:027 > Query duration in millisecs: 5750 > Number of tags: 5 > returnSize :10000 > Query start time: 2018-10-10 12:21:55:055 > Query end time: 2018-10-10 12:22:00:000 > Query duration in millisecs: 4703 > Number of tags: 6 > returnSize :10000 > Query start time: 2018-10-10 12:23:23:023 > Query end time: 2018-10-10 12:23:28:028 > Query duration in millisecs: 4406 > Number of tags: 7 > returnSize :10000 > Query start time: 2018-10-10 12:24:24:024 > Query end time: 2018-10-10 12:24:29:029 > Query duration in millisecs: 5453 ```auto startQueryTime = system.date.now() tags =[mix of Bool, Integer, Real tags removed for IP reasons] returnSize = 10000 dataset = system.tag.queryTagHistory(paths = tags, startDate ='2018-08-01', endDate='2018-10-01', returnSize=returnSize, aggregationMode='Maximum') endQueryTime = system.date.now() print 'Number of tags: ' + str(len(tags)) print 'returnSize :' +str(returnSize) print 'Query start time: ' +str(system.date.format(startQueryTime,'yyyy-MM-dd HH:mm:ss:sss')) print 'Query end time: ' +str(system.date.format(endQueryTime,'yyyy-MM-dd HH:mm:ss:sss')) print 'Query duration in millisecs: ' + str(system.date.millisBetween(startQueryTime,endQueryTime)) ``` For direct history queries our most common day to day use would be easy charts with up to a week of data and they respond well. Load in less than 1 second. This might be off topic but might help. For reports that show aggregates of weeks or months of data we archive history data into other tables to make the queries faster. We run gateway timer scripts every 5 mins or 1 hour etc. The scripts query history for the info we are interested in say energy used per motor then store the results in a different table giving us energy used per motor per hour. Maybe that approach could help? ---
**Author:** ![jlteel](https://avatars.discourse-cdn.com/v4/letter/j/c67d28/32.png) [@jlteel](https://forum.inductiveautomation.com/u/jlteel)\ **Post date:** [October 11, 2018, 2:08am UTC](https://forum.inductiveautomation.com/t/historian-table-partitioning/19155/19 "2018-10-11T02:08:03Z")
Joe, I really appreciate you taking the time to do those tests. Honestly, these are remarkable results compared to what we’re seeing. My database is Microsoft SQL Server because it’s the corporate standard for this organization. But my Easy Charts will actually time out when trying to retrieve one month of data for just two or three tags. And you have twice as much data as I do. Do you really think it’s just the database engines that are the bottleneck? These are VMs – managed by the customer’s IT staff. Any chance that resources on the database server could cause this sort of issue? What were the primary reasons for using MariaDB instead of MySQL? Just trying to keep to more traditional open source, or were there actual performance reasons? Thanks also for the recommendation to pre-aggregate the data for reporting purposes. We actually do that already, and it’s been very helpful in speeding up report generation. Thanks again for your time. Jeff ---
**Author:** ![mpoulsen921](https://avatars.discourse-cdn.com/v4/letter/m/b4bc9f/32.png) [@mpoulsen921](https://forum.inductiveautomation.com/u/mpoulsen921)\ **Post date:** [October 11, 2018, 10:39am UTC](https://forum.inductiveautomation.com/t/historian-table-partitioning/19155/20 "2018-10-11T10:39:39Z")
Hi Jeff, I don’t know if this could be related to your problem, but if so it might be worth a shot. I had a problem with the historian and the store&forward, where some of the data was dropped when forwarded to the historian. To fix the problem I set the ‘Max Active’ property in the setup of the DB connection on the gateway to 50 instead of 8. I also adjusted the partition units and length for the historian. Instead of having the partition length to be a year I changed it to be a week instead. I can see that you already have this set to a day. What happens if you lower this even more? This fixed my problem. FYI I read somewhere that it is advice able to only have 1-2 million rows in each partition - can’t find it now though… [Next page](https://forum.inductiveautomation.com/t/historian-table-partitioning/19155.md?page=2)