{"action":"opened","issue":{"url":"https://api.github.com/repos/timescale/timescaledb/issues/515","repository_url":"https://api.github.com/repos/timescale/timescaledb","labels_url":"https://api.github.com/repos/timescale/timescaledb/issues/515/labels{/name}","comments_url":"https://api.github.com/repos/timescale/timescaledb/issues/515/comments","events_url":"https://api.github.com/repos/timescale/timescaledb/issues/515/events","html_url":"https://github.com/timescale/timescaledb/issues/515","id":319461137,"number":515,"title":"Unexpected response time increase when # of chunks increases","user":{"login":"sspieser","id":14963451,"avatar_url":"https://avatars2.githubusercontent.com/u/14963451?v=4","gravatar_id":"","url":"https://api.github.com/users/sspieser","html_url":"https://github.com/sspieser","followers_url":"https://api.github.com/users/sspieser/followers","following_url":"https://api.github.com/users/sspieser/following{/other_user}","gists_url":"https://api.github.com/users/sspieser/gists{/gist_id}","starred_url":"https://api.github.com/users/sspieser/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/sspieser/subscriptions","organizations_url":"https://api.github.com/users/sspieser/orgs","repos_url":"https://api.github.com/users/sspieser/repos","events_url":"https://api.github.com/users/sspieser/events{/privacy}","received_events_url":"https://api.github.com/users/sspieser/received_events","type":"User","site_admin":false},"labels":[],"state":"open","locked":false,"assignee":null,"assignees":[],"milestone":null,"comments":0,"created_at":"2018-05-02T08:36:23Z","updated_at":"2018-05-02T08:36:23Z","closed_at":null,"author_association":"NONE","body":"Hello timescale!\r\n\r\nAs per our conf call, here are the problems we encounter regarding response time over partition increase. \r\nWe are leveraging the opportunity of using Postgres + Timescale, as an alternative to Oracle Database for managing big data in an energy application (time series, energy business measures).\r\n\r\nMethodology:\r\n\t- Create a new database with Timescale extension\r\n\t- Create a big table, insert data into it\r\n\t- Run queries\r\n\t- Add new empty partitions (or chunks)\r\n\t- Run the same queries\r\n\t\r\nNote: for Portgres it is easy to create empty partition; with Timescale, it looks like we have to actually insert some  data to create more chunks… so the new partitions are not exactly *empty*.\r\n\r\nOne of the query is:\r\n\r\n```\r\nBEGIN;\r\n\tinsert into collfact (time, pointid, value, mesureid, version, insertiondate, iscurrent)\r\n\tvalues (timestamp '2018-04-11 00:00:00', 10, trunc(random() * 99999),\r\n\t\t1, 2, now(), '1');\r\n\tUPDATE collfact set iscurrent = '0'\r\n\twhere time = timestamp '2018-04-11 00:00:00'\r\n\t\tand pointid = 10 and mesureid = 1 and version = 1;\r\nCOMMIT;\r\n```\r\n\r\nWhich should INSERT 1 row, and UPDATE 1 single row…\r\n\r\nExcept for the first test data, the 3 others crashed:\r\n\r\n```\r\nERROR:  out of memory\r\nDÉTAIL : Failed on request of size 200.\r\n```\r\n\r\nIn fact this is the message from Windows, under Linux we have something slightly different but seems to be due to the memory management / protection under Linux (see https://www.postgresql.org/docs/current/static/kernel-resources.html#LINUX-MEMORY-OVERCOMMIT) :\r\n\r\n```\r\nla connexion au serveur a été coupée de façon inattendue\r\n        Le serveur s'est peut-être arrêté anormalement avant ou durant le\r\n        traitement de la requête.\r\nLa connexion au serveur a été perdue. Tentative de réinitialisation : Échec.\r\nDurée : 543010,711 ms (09:03,011)\r\n!> COMMIT;\r\nVous n'êtes pas connecté à une base de données.\r\n!>\r\n```\r\n\r\nBut the issue (statement failure) is still the same even if the system does not react the same way.\r\n\r\nOther unexpected observations: the same SQL SELECT query response time increses while adding chunks:\r\n\r\n```\r\nChunks #      |    5840   |    7301   |    11319  |   14971   \r\nTime (sec.)   |      13   |      57   |       70  |      76   \r\n```\r\n\r\nSee attached files for the SQL statements we used.\r\n\r\n\r\nSo our concern here is that we expected response time stability; the dataset and number of chunks here is quite small (or even really small), wheras Timescale is supposed to address these kind of requirements very well… So do we make something the wrong weay?\r\n\r\nThanks in advance for your help,\r\nRegards,\r\nSebastien\r\n\r\nPS: file \"PGTS_INITDATA.TXT\" to build the database, table structure and test data\r\nfile \"PGTS_SOMEQUERIES.TXT\" contains the queries we run\r\nfile \"PGTS_MOREDATA.TXT\" to add some more chunks\r\n[PGTS_INITDATA.TXT](https://github.com/timescale/timescaledb/files/1966477/PGTS_INITDATA.TXT)\r\n[PGTS_SOMEQUERIES.TXT](https://github.com/timescale/timescaledb/files/1966478/PGTS_SOMEQUERIES.TXT)\r\n[PGTS_MOREDATA.TXT](https://github.com/timescale/timescaledb/files/1966479/PGTS_MOREDATA.TXT)\r\n"}}
{"actor":{"display_login":"sspieser"}}