{"id":9954,"date":"2016-04-27T10:58:20","date_gmt":"2016-04-27T08:58:20","guid":{"rendered":"https:\/\/blog.zhaw.ch\/icclab\/?p=9954"},"modified":"2016-04-27T11:21:23","modified_gmt":"2016-04-27T09:21:23","slug":"influxdb-design-guidelines-to-avoid-performance-issues","status":"publish","type":"post","link":"https:\/\/blog.zhaw.ch\/icclab\/influxdb-design-guidelines-to-avoid-performance-issues\/","title":{"rendered":"InfluxDB design guidelines to avoid performance issues"},"content":{"rendered":"

Cyclops, ICCLab’s Rating-Charging-Billing<\/a> solution for Cloud providers, capable of collecting usage records from both OpenStack<\/a> and CloudStack<\/a>, has been working with time series data from the very beginning. Our choice for database technology that is highly optimised to handle such data was InfluxDB<\/a> written in GO.<\/p>\n

Time series data is generally a sequence of data points – in our case either Usage Data Records or Charge Data Records. These datasets often have hundreds of millions of rows, including timestamps and large quantities of immutable fields. Entries do not typically change after they are added to the database, where new entries are being appended rather than operated on.<\/p>\n

Persistence is a challenge for this kind of data, as datasets may quickly grow larger than the server’s capacity. Distributed computing overcomes this by letting sections of the dataset live on separate data nodes, perhaps even replicating them across multiple nodes.<\/p>\n

Even though InfluxDB is currently under active development and expecting a major 1.0 release this summer, it already supports clustering, HA deployments and data replication. As can be seen in the picture below, retention policies are in place for phasing out old data automatically as it becomes less relevant.<\/p>\n

\"RP\"<\/a>
Retention policy syntax and example<\/figcaption><\/figure>\n

Continuous queries are optimal for regularly downsampling data, as well as pre-computing expensive queries in real-time. InfluxDB automatically and periodically runs the query and stores the result in a measurement for future use.<\/p>\n

\"CQ\"<\/a>
Continuous query example<\/figcaption><\/figure>\n

During the development of RCB Cyclops it was paramount to design and optimise our database schema in order to achieve better than out-of-the-box performance – which was possible by combining InfluxData’s design guidelines<\/a>, as well as understanding our data records. InfluxDB lets you specify fields and tags, both being key\/value pairs where the difference is that tags are automatically indexed.<\/p>\n

Because fields are not being indexed at all, on every query where InfluxDB is asked to find a specified field, it needs to sequentially scan every value of the field column. This behaviour is generally not preferred as it can increase response times significantly, especially on larger data sets. Of course workloads like this can be parallelised by the use of seek and limit operations on the client side. It is however more beneficial to rearrange the database schema and simply specify those fields as tags, getting constant access.<\/p>\n

However, there is a huge caveat – a series cardinality being a major factor that affects RAM requirements. Based on the most recent InfluxDB hardware sizing guidelines<\/a>, you will need around 2-4 GB of RAM for a low load with less than 100,000 unique series. Imagine that your database consists of one measurement that has only two tags, but those values are highly dynamic, both in the thousands. This would result in the need for more than 32 GB, because InfluxDB would try to construct an inverted index in memory, which would always be growing with the cardinality.<\/p>\n

A rule of thumb would be to persist highly dynamic values as fields and only use tags for GROUP BY<\/a> clauses and InfluxQL functions<\/a>, carefully designing your application around it. That kind of data would otherwise mean a new series every time something is added and a large increase in the inverted index stored in memory. It is therefore necessary to think about the most common queries that will get executed, and optimise the schema accordingly. On the other hand, tag keys and values are stored only once and always as strings, where field values and timestamps are stored per-point. This however doesn’t affect the memory footprint, only the storage requirements.<\/p>\n

The most recent InfluxDB 0.12 also offers new features for query management<\/a>, in the style of “fail fast” approach. Administrators can now use these functions and specify hard limits in order to guarantee a certain level of throughput, effectively enforcing clients to retry in case of timeouts, instead of completely hogging the system.<\/p>\n

\"QM\"<\/a>
Sanity checks and desired hard limits<\/figcaption><\/figure>\n

In order to effectively utilise InfluxDB’s underlying shard spaces (based on configured retention policy) the most performant write queries are those containing points with increasing timestamps. Also, sending single points is much more expensive than sending batches of 100-5000 points each.<\/p>\n

As far as we now know, InfluxDB’s future releases intend to support time zones and geospatial functions, allowing for more responsive queries. Don’t forget to follow us on Twitter @rcb_cyclops<\/a> to stay up to date on the latest RCB Cyclops development and use of time series databases in the cloud computing domain.<\/p>\n

Schlagw\u00f6rter: Cyclops<\/a>, database<\/a>, guidelines<\/a>, InfluxDB<\/a>, Optimisation<\/a>, performance<\/a>, Schema<\/a>
<\/div>","protected":false},"excerpt":{"rendered":"

Cyclops, ICCLab’s Rating-Charging-Billing solution for Cloud providers, capable of collecting usage records from both OpenStack and CloudStack, has been working with time series data from the very beginning. Our choice for database technology that is highly optimised to handle such data was InfluxDB written in GO. Time series data is generally a sequence of data […]<\/p>\n","protected":false},"author":220,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"ngg_post_thumbnail":0,"footnotes":""},"categories":[1],"tags":[420,525,768,765,766,416,767],"features":[],"class_list":["post-9954","post","type-post","status-publish","format-standard","hentry","category-allgemein","tag-cyclops","tag-database","tag-guidelines","tag-influxdb","tag-optimisation","tag-performance","tag-schema"],"yoast_head":"\nInfluxDB design guidelines to avoid performance issues - Service Engineering (ICCLab & SPLab)<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/blog.zhaw.ch\/icclab\/influxdb-design-guidelines-to-avoid-performance-issues\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"InfluxDB design guidelines to avoid performance issues\" \/>\n<meta property=\"og:description\" content=\"Cyclops, ICCLab’s Rating-Charging-Billing solution for Cloud providers, capable of collecting usage records from both OpenStack and CloudStack, has been working with time series data from the very beginning. Our choice for database technology that is highly optimised to handle such data was InfluxDB written in GO. Time series data is generally a sequence of data […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/blog.zhaw.ch\/icclab\/influxdb-design-guidelines-to-avoid-performance-issues\/\" \/>\n<meta property=\"og:site_name\" content=\"Service Engineering (ICCLab & SPLab)\" \/>\n<meta property=\"article:published_time\" content=\"2016-04-27T08:58:20+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2016-04-27T09:21:23+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/blog.zhaw.ch\/icclab\/files\/2016\/04\/RP-300x67.png\" \/>\n<meta name=\"author\" content=\"skov\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"skov\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"4 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/blog.zhaw.ch\/icclab\/influxdb-design-guidelines-to-avoid-performance-issues\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/blog.zhaw.ch\/icclab\/influxdb-design-guidelines-to-avoid-performance-issues\/\"},\"author\":{\"name\":\"skov\",\"@id\":\"https:\/\/blog.zhaw.ch\/icclab\/#\/schema\/person\/8b7fede5121e922f9967491667d6c0c5\"},\"headline\":\"InfluxDB design guidelines to avoid performance issues\",\"datePublished\":\"2016-04-27T08:58:20+00:00\",\"dateModified\":\"2016-04-27T09:21:23+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/blog.zhaw.ch\/icclab\/influxdb-design-guidelines-to-avoid-performance-issues\/\"},\"wordCount\":781,\"commentCount\":1,\"image\":{\"@id\":\"https:\/\/blog.zhaw.ch\/icclab\/influxdb-design-guidelines-to-avoid-performance-issues\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/blog.zhaw.ch\/icclab\/files\/2016\/04\/RP-300x67.png\",\"keywords\":[\"Cyclops\",\"database\",\"guidelines\",\"InfluxDB\",\"Optimisation\",\"performance\",\"Schema\"],\"articleSection\":[\"*.*\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/blog.zhaw.ch\/icclab\/influxdb-design-guidelines-to-avoid-performance-issues\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/blog.zhaw.ch\/icclab\/influxdb-design-guidelines-to-avoid-performance-issues\/\",\"url\":\"https:\/\/blog.zhaw.ch\/icclab\/influxdb-design-guidelines-to-avoid-performance-issues\/\",\"name\":\"InfluxDB design guidelines to avoid performance issues - Service Engineering (ICCLab & SPLab)\",\"isPartOf\":{\"@id\":\"https:\/\/blog.zhaw.ch\/icclab\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/blog.zhaw.ch\/icclab\/influxdb-design-guidelines-to-avoid-performance-issues\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/blog.zhaw.ch\/icclab\/influxdb-design-guidelines-to-avoid-performance-issues\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/blog.zhaw.ch\/icclab\/files\/2016\/04\/RP-300x67.png\",\"datePublished\":\"2016-04-27T08:58:20+00:00\",\"dateModified\":\"2016-04-27T09:21:23+00:00\",\"author\":{\"@id\":\"https:\/\/blog.zhaw.ch\/icclab\/#\/schema\/person\/8b7fede5121e922f9967491667d6c0c5\"},\"breadcrumb\":{\"@id\":\"https:\/\/blog.zhaw.ch\/icclab\/influxdb-design-guidelines-to-avoid-performance-issues\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/blog.zhaw.ch\/icclab\/influxdb-design-guidelines-to-avoid-performance-issues\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/blog.zhaw.ch\/icclab\/influxdb-design-guidelines-to-avoid-performance-issues\/#primaryimage\",\"url\":\"https:\/\/blog.zhaw.ch\/icclab\/files\/2016\/04\/RP.png\",\"contentUrl\":\"https:\/\/blog.zhaw.ch\/icclab\/files\/2016\/04\/RP.png\",\"width\":1132,\"height\":252},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/blog.zhaw.ch\/icclab\/influxdb-design-guidelines-to-avoid-performance-issues\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Startseite\",\"item\":\"https:\/\/blog.zhaw.ch\/icclab\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"InfluxDB design guidelines to avoid performance issues\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/blog.zhaw.ch\/icclab\/#website\",\"url\":\"https:\/\/blog.zhaw.ch\/icclab\/\",\"name\":\"Service Engineering (ICCLab & SPLab)\",\"description\":\"A Blog of the ZHAW Zurich University of Applied Sciences\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/blog.zhaw.ch\/icclab\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/blog.zhaw.ch\/icclab\/#\/schema\/person\/8b7fede5121e922f9967491667d6c0c5\",\"name\":\"skov\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/secure.gravatar.com\/avatar\/f1b79ba6d811b3b4570a0ae437958b1d3644c013db0b0293cdf77f69f8996bcb?s=96&d=mm&r=g\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/f1b79ba6d811b3b4570a0ae437958b1d3644c013db0b0293cdf77f69f8996bcb?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/f1b79ba6d811b3b4570a0ae437958b1d3644c013db0b0293cdf77f69f8996bcb?s=96&d=mm&r=g\",\"caption\":\"skov\"},\"url\":\"https:\/\/blog.zhaw.ch\/icclab\/author\/skov\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"InfluxDB design guidelines to avoid performance issues - Service Engineering (ICCLab & SPLab)","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/blog.zhaw.ch\/icclab\/influxdb-design-guidelines-to-avoid-performance-issues\/","og_locale":"en_US","og_type":"article","og_title":"InfluxDB design guidelines to avoid performance issues","og_description":"Cyclops, ICCLab’s Rating-Charging-Billing solution for Cloud providers, capable of collecting usage records from both OpenStack and CloudStack, has been working with time series data from the very beginning. Our choice for database technology that is highly optimised to handle such data was InfluxDB written in GO. Time series data is generally a sequence of data […]","og_url":"https:\/\/blog.zhaw.ch\/icclab\/influxdb-design-guidelines-to-avoid-performance-issues\/","og_site_name":"Service Engineering (ICCLab & SPLab)","article_published_time":"2016-04-27T08:58:20+00:00","article_modified_time":"2016-04-27T09:21:23+00:00","og_image":[{"url":"https:\/\/blog.zhaw.ch\/icclab\/files\/2016\/04\/RP-300x67.png","type":"","width":"","height":""}],"author":"skov","twitter_card":"summary_large_image","twitter_misc":{"Written by":"skov","Est. reading time":"4 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/blog.zhaw.ch\/icclab\/influxdb-design-guidelines-to-avoid-performance-issues\/#article","isPartOf":{"@id":"https:\/\/blog.zhaw.ch\/icclab\/influxdb-design-guidelines-to-avoid-performance-issues\/"},"author":{"name":"skov","@id":"https:\/\/blog.zhaw.ch\/icclab\/#\/schema\/person\/8b7fede5121e922f9967491667d6c0c5"},"headline":"InfluxDB design guidelines to avoid performance issues","datePublished":"2016-04-27T08:58:20+00:00","dateModified":"2016-04-27T09:21:23+00:00","mainEntityOfPage":{"@id":"https:\/\/blog.zhaw.ch\/icclab\/influxdb-design-guidelines-to-avoid-performance-issues\/"},"wordCount":781,"commentCount":1,"image":{"@id":"https:\/\/blog.zhaw.ch\/icclab\/influxdb-design-guidelines-to-avoid-performance-issues\/#primaryimage"},"thumbnailUrl":"https:\/\/blog.zhaw.ch\/icclab\/files\/2016\/04\/RP-300x67.png","keywords":["Cyclops","database","guidelines","InfluxDB","Optimisation","performance","Schema"],"articleSection":["*.*"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/blog.zhaw.ch\/icclab\/influxdb-design-guidelines-to-avoid-performance-issues\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/blog.zhaw.ch\/icclab\/influxdb-design-guidelines-to-avoid-performance-issues\/","url":"https:\/\/blog.zhaw.ch\/icclab\/influxdb-design-guidelines-to-avoid-performance-issues\/","name":"InfluxDB design guidelines to avoid performance issues - Service Engineering (ICCLab & SPLab)","isPartOf":{"@id":"https:\/\/blog.zhaw.ch\/icclab\/#website"},"primaryImageOfPage":{"@id":"https:\/\/blog.zhaw.ch\/icclab\/influxdb-design-guidelines-to-avoid-performance-issues\/#primaryimage"},"image":{"@id":"https:\/\/blog.zhaw.ch\/icclab\/influxdb-design-guidelines-to-avoid-performance-issues\/#primaryimage"},"thumbnailUrl":"https:\/\/blog.zhaw.ch\/icclab\/files\/2016\/04\/RP-300x67.png","datePublished":"2016-04-27T08:58:20+00:00","dateModified":"2016-04-27T09:21:23+00:00","author":{"@id":"https:\/\/blog.zhaw.ch\/icclab\/#\/schema\/person\/8b7fede5121e922f9967491667d6c0c5"},"breadcrumb":{"@id":"https:\/\/blog.zhaw.ch\/icclab\/influxdb-design-guidelines-to-avoid-performance-issues\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/blog.zhaw.ch\/icclab\/influxdb-design-guidelines-to-avoid-performance-issues\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/blog.zhaw.ch\/icclab\/influxdb-design-guidelines-to-avoid-performance-issues\/#primaryimage","url":"https:\/\/blog.zhaw.ch\/icclab\/files\/2016\/04\/RP.png","contentUrl":"https:\/\/blog.zhaw.ch\/icclab\/files\/2016\/04\/RP.png","width":1132,"height":252},{"@type":"BreadcrumbList","@id":"https:\/\/blog.zhaw.ch\/icclab\/influxdb-design-guidelines-to-avoid-performance-issues\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Startseite","item":"https:\/\/blog.zhaw.ch\/icclab\/"},{"@type":"ListItem","position":2,"name":"InfluxDB design guidelines to avoid performance issues"}]},{"@type":"WebSite","@id":"https:\/\/blog.zhaw.ch\/icclab\/#website","url":"https:\/\/blog.zhaw.ch\/icclab\/","name":"Service Engineering (ICCLab & SPLab)","description":"A Blog of the ZHAW Zurich University of Applied Sciences","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/blog.zhaw.ch\/icclab\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/blog.zhaw.ch\/icclab\/#\/schema\/person\/8b7fede5121e922f9967491667d6c0c5","name":"skov","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/f1b79ba6d811b3b4570a0ae437958b1d3644c013db0b0293cdf77f69f8996bcb?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/f1b79ba6d811b3b4570a0ae437958b1d3644c013db0b0293cdf77f69f8996bcb?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/f1b79ba6d811b3b4570a0ae437958b1d3644c013db0b0293cdf77f69f8996bcb?s=96&d=mm&r=g","caption":"skov"},"url":"https:\/\/blog.zhaw.ch\/icclab\/author\/skov\/"}]}},"_links":{"self":[{"href":"https:\/\/blog.zhaw.ch\/icclab\/wp-json\/wp\/v2\/posts\/9954","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/blog.zhaw.ch\/icclab\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/blog.zhaw.ch\/icclab\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/blog.zhaw.ch\/icclab\/wp-json\/wp\/v2\/users\/220"}],"replies":[{"embeddable":true,"href":"https:\/\/blog.zhaw.ch\/icclab\/wp-json\/wp\/v2\/comments?post=9954"}],"version-history":[{"count":7,"href":"https:\/\/blog.zhaw.ch\/icclab\/wp-json\/wp\/v2\/posts\/9954\/revisions"}],"predecessor-version":[{"id":9965,"href":"https:\/\/blog.zhaw.ch\/icclab\/wp-json\/wp\/v2\/posts\/9954\/revisions\/9965"}],"wp:attachment":[{"href":"https:\/\/blog.zhaw.ch\/icclab\/wp-json\/wp\/v2\/media?parent=9954"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blog.zhaw.ch\/icclab\/wp-json\/wp\/v2\/categories?post=9954"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blog.zhaw.ch\/icclab\/wp-json\/wp\/v2\/tags?post=9954"},{"taxonomy":"features","embeddable":true,"href":"https:\/\/blog.zhaw.ch\/icclab\/wp-json\/wp\/v2\/features?post=9954"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}