{"action":"created","issue":{"url":"https://api.github.com/repos/postgres-x2/postgres-x2/issues/319","labels_url":"https://api.github.com/repos/postgres-x2/postgres-x2/issues/319/labels{/name}","comments_url":"https://api.github.com/repos/postgres-x2/postgres-x2/issues/319/comments","events_url":"https://api.github.com/repos/postgres-x2/postgres-x2/issues/319/events","html_url":"https://github.com/postgres-x2/postgres-x2/issues/319","id":123872857,"number":319,"title":"Reduce the time of fetching snapshot to improve TPS and QPS","user":{"login":"scarbrofair","id":6965925,"avatar_url":"https://avatars.githubusercontent.com/u/6965925?v=3","gravatar_id":"","url":"https://api.github.com/users/scarbrofair","html_url":"https://github.com/scarbrofair","followers_url":"https://api.github.com/users/scarbrofair/followers","following_url":"https://api.github.com/users/scarbrofair/following{/other_user}","gists_url":"https://api.github.com/users/scarbrofair/gists{/gist_id}","starred_url":"https://api.github.com/users/scarbrofair/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/scarbrofair/subscriptions","organizations_url":"https://api.github.com/users/scarbrofair/orgs","repos_url":"https://api.github.com/users/scarbrofair/repos","events_url":"https://api.github.com/users/scarbrofair/events{/privacy}","received_events_url":"https://api.github.com/users/scarbrofair/received_events","type":"User","site_admin":false},"labels":[],"state":"open","locked":false,"assignee":null,"milestone":null,"comments":1,"created_at":"2015-12-25T10:45:59Z","updated_at":"2016-01-04T06:20:16Z","closed_at":null,"body":"       As I described on the postgres_x2 development  meeting in Beijing, the effort of fetching snapshot from gtm/gtm_proxy, consumed  considerable time of the whole life of a SQL. So, if we want to reduce the time of handling SQL in postgres_x2, may be we need to reduce the cost on fetching snapshot.\r\n      Currently, for a SQL, coordinator need to fetch snapshot twice:  one time for the  parse analysis/planning; and another time for the execution of the transaction. This just follows postgres, but not suitable to postgres_x2, because for postgres, the cost of get snapshot is insignificant. And, on the other side, once an application is on line, it is rare to change database schema.\r\n     So, may be we can create a rough snapshot on coordinator when a coordinator backend has a long life, since once the backend finish last SQL, it will save last RecentXmin and latestCompletedXid which can be used to create a rough snapshot for the MVCC test when  parse analysis/planning for next SQL. \r\n     I create a patch to verify my idea: introducing a GUC on coordinator, which can tell coordinator can make rough snapshot when  parse analysis/planning, and if the system need to change schema, we can turn off this GUC and coordinator will work as gtm of current version. Once we finish this DDL, we can turn it on.\r\n    I take pressure test on a simple environment: one gtm, one datanode one coordinator on one machine (turn the new GUC on), And test it with following commands: \r\n     pgbench -c N -j N -T 60   (for TPS)\r\n    pgbench  -S -c N -j N -T 60  (for QPS)\r\n    And when N is 64, I can get the max TPS and QPS.\r\n    I test it when N is 64 128 256, I can see TPS improves 5% and QPS improves 10%, compared with current gtm.\r\n    Note, if N is less than 64, no improvement on TPS\\QPS."},"comment":{"url":"https://api.github.com/repos/postgres-x2/postgres-x2/issues/comments/168592796","html_url":"https://github.com/postgres-x2/postgres-x2/issues/319#issuecomment-168592796","issue_url":"https://api.github.com/repos/postgres-x2/postgres-x2/issues/319","id":168592796,"user":{"login":"koichi-szk","id":985469,"avatar_url":"https://avatars.githubusercontent.com/u/985469?v=3","gravatar_id":"","url":"https://api.github.com/users/koichi-szk","html_url":"https://github.com/koichi-szk","followers_url":"https://api.github.com/users/koichi-szk/followers","following_url":"https://api.github.com/users/koichi-szk/following{/other_user}","gists_url":"https://api.github.com/users/koichi-szk/gists{/gist_id}","starred_url":"https://api.github.com/users/koichi-szk/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/koichi-szk/subscriptions","organizations_url":"https://api.github.com/users/koichi-szk/orgs","repos_url":"https://api.github.com/users/koichi-szk/repos","events_url":"https://api.github.com/users/koichi-szk/events{/privacy}","received_events_url":"https://api.github.com/users/koichi-szk/received_events","type":"User","site_admin":false},"created_at":"2016-01-04T06:20:16Z","updated_at":"2016-01-04T06:20:16Z","body":"Sorry taking long for this issue.\r\n\r\nAnother issue in pgbench is in readonly queries, which scales but absolute performance is not good.   Yes snapshot issue will be some improvement.   For more improvement in readonly queries, I'm thinking unifying coordinator and datanode will work.    This is a big challenge but we will have a chance to save additional communication cost between coordinator and datanode.    On the other hand, I think that we need to incorporate DTM framework from Oleg. "}}