{"action":"created","issue":{"url":"https://api.github.com/repos/couchbase/sync_gateway/issues/3358","repository_url":"https://api.github.com/repos/couchbase/sync_gateway","labels_url":"https://api.github.com/repos/couchbase/sync_gateway/issues/3358/labels{/name}","comments_url":"https://api.github.com/repos/couchbase/sync_gateway/issues/3358/comments","events_url":"https://api.github.com/repos/couchbase/sync_gateway/issues/3358/events","html_url":"https://github.com/couchbase/sync_gateway/issues/3358","id":303214064,"number":3358,"title":"Slow queries for changes_view and unstable replications","user":{"login":"ont","id":350218,"avatar_url":"https://avatars2.githubusercontent.com/u/350218?v=4","gravatar_id":"","url":"https://api.github.com/users/ont","html_url":"https://github.com/ont","followers_url":"https://api.github.com/users/ont/followers","following_url":"https://api.github.com/users/ont/following{/other_user}","gists_url":"https://api.github.com/users/ont/gists{/gist_id}","starred_url":"https://api.github.com/users/ont/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/ont/subscriptions","organizations_url":"https://api.github.com/users/ont/orgs","repos_url":"https://api.github.com/users/ont/repos","events_url":"https://api.github.com/users/ont/events{/privacy}","received_events_url":"https://api.github.com/users/ont/received_events","type":"User","site_admin":false},"labels":[{"id":108421679,"url":"https://api.github.com/repos/couchbase/sync_gateway/labels/icebox","name":"icebox","color":"ededed","default":false}],"state":"open","locked":false,"assignee":{"login":"adamcfraser","id":8866453,"avatar_url":"https://avatars3.githubusercontent.com/u/8866453?v=4","gravatar_id":"","url":"https://api.github.com/users/adamcfraser","html_url":"https://github.com/adamcfraser","followers_url":"https://api.github.com/users/adamcfraser/followers","following_url":"https://api.github.com/users/adamcfraser/following{/other_user}","gists_url":"https://api.github.com/users/adamcfraser/gists{/gist_id}","starred_url":"https://api.github.com/users/adamcfraser/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/adamcfraser/subscriptions","organizations_url":"https://api.github.com/users/adamcfraser/orgs","repos_url":"https://api.github.com/users/adamcfraser/repos","events_url":"https://api.github.com/users/adamcfraser/events{/privacy}","received_events_url":"https://api.github.com/users/adamcfraser/received_events","type":"User","site_admin":false},"assignees":[{"login":"adamcfraser","id":8866453,"avatar_url":"https://avatars3.githubusercontent.com/u/8866453?v=4","gravatar_id":"","url":"https://api.github.com/users/adamcfraser","html_url":"https://github.com/adamcfraser","followers_url":"https://api.github.com/users/adamcfraser/followers","following_url":"https://api.github.com/users/adamcfraser/following{/other_user}","gists_url":"https://api.github.com/users/adamcfraser/gists{/gist_id}","starred_url":"https://api.github.com/users/adamcfraser/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/adamcfraser/subscriptions","organizations_url":"https://api.github.com/users/adamcfraser/orgs","repos_url":"https://api.github.com/users/adamcfraser/repos","events_url":"https://api.github.com/users/adamcfraser/events{/privacy}","received_events_url":"https://api.github.com/users/adamcfraser/received_events","type":"User","site_admin":false}],"milestone":{"url":"https://api.github.com/repos/couchbase/sync_gateway/milestones/22","html_url":"https://github.com/couchbase/sync_gateway/milestone/22","labels_url":"https://api.github.com/repos/couchbase/sync_gateway/milestones/22/labels","id":2407118,"number":22,"title":"2.1.0","description":"","creator":{"login":"djpongh","id":21346920,"avatar_url":"https://avatars3.githubusercontent.com/u/21346920?v=4","gravatar_id":"","url":"https://api.github.com/users/djpongh","html_url":"https://github.com/djpongh","followers_url":"https://api.github.com/users/djpongh/followers","following_url":"https://api.github.com/users/djpongh/following{/other_user}","gists_url":"https://api.github.com/users/djpongh/gists{/gist_id}","starred_url":"https://api.github.com/users/djpongh/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/djpongh/subscriptions","organizations_url":"https://api.github.com/users/djpongh/orgs","repos_url":"https://api.github.com/users/djpongh/repos","events_url":"https://api.github.com/users/djpongh/events{/privacy}","received_events_url":"https://api.github.com/users/djpongh/received_events","type":"User","site_admin":false},"open_issues":98,"closed_issues":50,"state":"open","created_at":"2017-03-23T15:23:08Z","updated_at":"2018-03-21T18:40:49Z","due_on":null,"closed_at":null},"comments":4,"created_at":"2018-03-07T18:42:01Z","updated_at":"2018-03-23T17:21:33Z","closed_at":null,"author_association":"NONE","body":"### Sync Gateway version\r\n```\r\n{\"couchdb\":\"Welcome\",\r\n\"vendor\":{\"name\":\"Couchbase Sync Gateway\",\"version\":1.5},\r\n\"version\":\"Couchbase Sync Gateway/1.5.1(4;cb9522c)\"}\r\n```\r\n\r\n### Operating system\r\nUsing official docker container `1.5.1-community     4e8916a9642c`\r\n\r\n### Configuration\r\n- Two servers with 112Gb RAM.\r\n- Couchbase has 50Gb RAM for its main bucket on each server (100GB in total) and forms cluster.\r\n- Sync gateway has 30Gb available RAM on each server.\r\n- We have over 45,000,000 (45 million) documents in our main bucket sharded over 2-server couchbase cluster\r\n- Over 7000 active online users balanced over two sync-gatway instances.\r\n- Each user have from 2 to 8 channels.\r\n- 1-3 of this channels may have more than 3000 documents\r\n\r\n\r\n### Config file\r\n```javascript\r\n{\r\n  \"log\": [\"HTTP+\"],\r\n  \"compressResponses\": false,\r\n  \"couchbaseKeepaliveInterval\": 10,\r\n  \"databases\": {\r\n    \"babycare\": {\r\n      \"rev_cache_size\": 500,\r\n      \"revs_limit\": 100,\r\n      \"sync\": \"function sync(doc, oldDoc) {... some complex sync function ...}\",\r\n      \"username\": \"...\",\r\n      \"password\": \"...\",\r\n      \"server\": \"http://some-server:8091\",\r\n      \"bucket\": \"some-bucket\"\r\n    }\r\n  }\r\n}\r\n```\r\n\r\n### Log output\r\nPart of log for two retries to `changes_view` for `1cac3667-1ee9-4e6b-9ac1-ba8297170af7`\r\nhttps://gist.github.com/ont/7795f1bae03823de27f5ba6ddc125d98\r\n\r\n### Expected behavior\r\nAfter nginx/sync-gateway/couchbase restarting system work as expected:\r\n\r\n- stable connections for longpolls\r\n- minimal reconnections from client to nginx balancer\r\n- fast responses from sync gateway\r\n\r\n### Actual behavior\r\nAfter more than 5 hours of downtime for one of our couchbase servers we got strange behavior of whole stack (nginx + sync-gateway + couchbase). \r\nWhat happens:\r\n1. after each reboot for one of the nginx or sync-gateway all of our >10000 active connections are dropped\r\n2. each mobile client then try to reconnect to nginx\r\n3. we observe big spike of requests to nginx\r\n4. after some time we can see **very slow** requests to **changes_view** in sync-gateway logs:\r\n    ```\r\n    2018-03-07T13:48:29.947Z changes_view: Query took 8m41.557951084s to return 198 rows, options = db.Body{\"stale\":false, \"startkey\":[]interface {}{\"1cac3667-1ee9-4e6b-9ac1-ba8297170af7\", 0x1}, \"endkey\":[]interface {}{\"1cac3667-1ee9-4e6b-9ac1-ba8297170af7\", 0x5513016}}\r\n    ```\r\n    Query took `8m41.557951084s`!!! It is very slow.\r\n5. seems that it doesn't depend on amount of returned rows. We can see slow queries even for 1-2 rows returned.\r\n6. when query time becomes larger than nginx's timeout settings then nginx drops active connections and client retry its request again. We have this settings:\r\n```\r\nkeepalive_requests 1000;\r\nkeepalive_timeout  360s;\r\nproxy_read_timeout 360s;\r\nproxy_send_timeout 360s;\r\n```\r\n7. query time to changes_view doesn't decrease over time\r\n\r\n\r\n### Steps to reproduce\r\n1. stop nginx, SG or couchbase for long period\r\n2. make some new documents on clients\r\n3. bring nginx+SG+couchbase online again  \r\n4. observe slow  changes_view queries under heavy loading during client reconnections.\r\n\r\n### Some more info\r\nSome metrics from grafana:\r\n![graphs](https://user-images.githubusercontent.com/350218/37109909-fd95797e-226d-11e8-8a7e-e8c024cc1be8.png)\r\nHere you can observe orange periodical spikes (requests per seconds). During this periods nginx writes lot timeout-to-upstream messages to error log.  After a while this spikes are gone but nginx timeouts and slow queries are not.\r\n\r\nIn couchbase admin panel we can observe never completed `babycare/_design/sync_gateway` view.\r\n![couch](https://user-images.githubusercontent.com/350218/37110233-ebc9eddc-226e-11e8-8d97-9277d9ae664e.png)\r\nIt always stuck around 1-5% of progress.\r\n\r\nDuring problems on SG we are able to successfully execute one-shot replications requests and longpolls which are returns **small amount** of rows. \r\n```\r\ncurl 'http://some-server/database/_changes?feed=normal&heartbeat=30000&style=all_docs&filter=sync_gateway%2Fbychannel&channels=some-channel-uuid' --cookie 'SyncGatewaySession=....'\r\n```\r\nUnfortunately queries which are returns thousand of rows stuck forever.\r\n\r\n### Questions\r\n1. Does it our problem that we store many documents in single channel?\r\n2. Does horizontal or vertical scaling resolves our problem? (more couchbase servers and sync-gateway instances or more RAM/cache for couchbase/sync-gateway)\r\n3. What is proper way for recovering big amount of data from sync-gateway+couchbase cluster? Currently we recover all user's documents via single longpoll query."},"comment":{"url":"https://api.github.com/repos/couchbase/sync_gateway/issues/comments/375739726","html_url":"https://github.com/couchbase/sync_gateway/issues/3358#issuecomment-375739726","issue_url":"https://api.github.com/repos/couchbase/sync_gateway/issues/3358","id":375739726,"user":{"login":"ont","id":350218,"avatar_url":"https://avatars2.githubusercontent.com/u/350218?v=4","gravatar_id":"","url":"https://api.github.com/users/ont","html_url":"https://github.com/ont","followers_url":"https://api.github.com/users/ont/followers","following_url":"https://api.github.com/users/ont/following{/other_user}","gists_url":"https://api.github.com/users/ont/gists{/gist_id}","starred_url":"https://api.github.com/users/ont/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/ont/subscriptions","organizations_url":"https://api.github.com/users/ont/orgs","repos_url":"https://api.github.com/users/ont/repos","events_url":"https://api.github.com/users/ont/events{/privacy}","received_events_url":"https://api.github.com/users/ont/received_events","type":"User","site_admin":false},"created_at":"2018-03-23T17:21:33Z","updated_at":"2018-03-23T17:21:33Z","author_association":"NONE","body":"@adamcfraser it seems that I incorrectly remembered what we have rebooted, sorry for disinformation :)\r\n\r\nI can confirm that nginx rebooting doesn't affect changes view queries. But SG rebooting does:\r\n![nginx-reboot-sg-reboot](https://user-images.githubusercontent.com/350218/37843180-c8a9f358-2ef6-11e8-8ea7-5b6340d3b475.png)\r\nFirst I rebooted nginx, then I rebooted two SG instances.\r\n\r\nIs it normal? Does I correctly understand that SG stores `/{db}/_local/{hash}` values in local memory? So after SG rebooting any client will get mismatch between local and remote `since` values and do \"full query\".\r\n\r\nIf so it is very dangerous because we already got situation where mobile clients are able to DDoS couchbase + SG cluster when `changes_view` query doesn't automatically stabilized on its own. It can appears again and it is slightly difficult to debug and predict (how many vCPUs we need and similar).\r\n\r\nAnd how SG behave if we randomly distribute requests between multiple SG? Does every fresh not-initialized instance of SG cause mobile client to do \"full query\" and store `since` value into SG's `/{db}/_local/{hash}` again and again?"}}
{"actor":{"display_login":"ont"}}