{"action":"created","comment":{"url":"https://api.github.com/repos/crate/crate/pulls/comments/60228495","id":60228495,"diff_hunk":"@@ -0,0 +1,161 @@\n+.. highlight:: psql\n+.. _systables:\n+\n+===============================\n+Troubleshooting with sys-Tables\n+===============================\n+\n+Crate maintains a set of diagnostic tables in the **sys** schema. It\n+consists currently of ten tables that provide an overview of the\n+cluster state. If something is going wrong and you initially don’t\n+know why, they help you to analyze, identify the problem and start\n+mitigating. While there is `detailed documentation about each single\n+table <https://crate.io/docs/reference/sql/system.html>`__, this guide\n+runs you through the most common situations.\n+\n+Step 1: Health Check\n+====================\n+\n+A good point to start is the table **sys.check** that maintains a number\n+of health checks, you may know from the admin UI. Order them by\n+severity::\n+\n+    cr> SELECT description FROM sys.checks WHERE NOT passed ORDER BY severity DESC;\n+    ...\n+\n+If a check fails, the description offers some explanation on how to\n+proceed. The table reports checks that verify your cluster layout, give\n+recommendations for configuration options, and warn you on incompatible\n+software versions. More will be added as you go.\n+\n+Step 2: Activity in the Cluster\n+===============================\n+\n+Statements that are currently executed on the server are tracked in the\n+tables **sys.jobs** and **sys.operations**. They give you the\n+opportunity to view the ongoing activity in the cluster.\n+\n+For these to work you have to enable status logging with::\n+\n+    cr> SET GLOBAL stats.enabled = true;\n+    SET OK (... sec)\n+\n+before you query these tables. Each syntactically correct request that\n+got parsed and planned is listed in the **sys.jobs** table while it’s\n+executed::\n+\n+    cr> SELECT id as job_uuid, date_format(started) AS start, stmt FROM sys.jobs;\n+    ...\n+\n+Once you identified the dedicated job UUID, you can kill that job with\n+the **KILL** command. A single job is splitted into several operations\n+which run, depending on the query, on distributed nodes of your cluster.\n+The table has also a hidden object attribute **_node** indicating on\n+which node Crate actually executes the operation::\n+\n+    cr> SELECT _node['name'], _node['hostname'], * FROM sys.operations;\n+    ...\n+\n+Find out more about the **_node** object in the next sections. If there\n+are no current jobs nor operations that are causing problems, check the\n+recorded history of finished jobs and operations in the tables\n+**sys.jobs_log** and **sys.operations_log**, respectively.\n+\n+Step 3: Analyzing Cluster Resources\n+===================================\n+\n+Sometimes it’s not a single query that causes problems, but a component\n+of your distributed cluster. To find out more about it, check the table\n+**sys.cluster**, which holds a single row containing the name and id of\n+the current master along with several other settings. To list all\n+available data, run::\n+\n+    cr> SHOW COLUMNS IN cluster FROM sys;\n+    ...\n+\n+While **sys.cluster** contains information about the cluster as a whole,\n+**sys.nodes** maintains more detailed information about each Crate\n+instance. This can be useful to track down misbehaving data nodes since\n+its CPU is overloaded or it has an outdated Java version::\n+\n+    cr> SELECT name, load['1'], os_info['jvm']['version'] FROM sys.nodes;\n+    ...\n+\n+To list all nodes using more than 90 per cent of the memory, type::\n+\n+    cr> SELECT * FROM sys.nodes WHERE mem['used_percent'] > 90;\n+    ...\n+\n+The table also contains the performance metrics like the load average,\n+disk, memory, heap, or network throughput. Running::\n+\n+    cr> SHOW columns IN nodes FROM sys;\n+    ...\n+\n+lists all available attributes. This object has the same structure as\n+the **_node** attribute of **sys.operations** from the previous\n+section.\n+\n+Step 4: Insights about Partitions, Shards, and Replication\n+==========================================================\n+\n+Crate divides the rows of each table into shards that are distinctively\n+distributed to all nodes in your cluster. Replication uses the same\n+mechanism to add redundancy and thus resilience to your data. While most\n+of the time Crate takes transparently care of distributing and\n+replicating the shards, it’s useful during troubleshooting to actually\n+find out some more about these data structures. The **sys.shards** table\n+provides access to the status and size of shards, its names and IDs::\n+\n+    cr> SHOW COLUMNS IN shards FROM sys;\n+    ...\n+\n+The cluster state is somewhat delicate when nodes join or leave, since\n+in those situations shards have to be rearranged to ensure that each of\n+them is replicated to different nodes. As long as the **state**\n+attribute is ‘STARTED’ for all shards, the cluster is in a stable state,\n+otherwise Crate is occupied with some background activity. The cluster\n+state indicators on the admin UI evaluate these values as well.\n+\n+The **sys.shards** table contains even more information about the\n+rebalancing activities. Whenever Crate needs to copy one shard to\n+another node -- since that may be necessary to ensure there are enough\n+replica of it distributed in the cluster -- you can monitor the progress\n+of that operation with the **recovery** object::\n+\n+    cr> SELECT DISTINCT partition_ident, recovery['size']['percent'], recovery['total_time'] FROM sys.shards WHERE primary;","path":"docs/best_practice/systables.txt","position":126,"original_position":126,"commit_id":"6b97ae4bc2b892cc52b8d02d44a83073d1bb71f0","original_commit_id":"6b97ae4bc2b892cc52b8d02d44a83073d1bb71f0","user":{"login":"dobe","id":48020,"avatar_url":"https://avatars.githubusercontent.com/u/48020?v=3","gravatar_id":"","url":"https://api.github.com/users/dobe","html_url":"https://github.com/dobe","followers_url":"https://api.github.com/users/dobe/followers","following_url":"https://api.github.com/users/dobe/following{/other_user}","gists_url":"https://api.github.com/users/dobe/gists{/gist_id}","starred_url":"https://api.github.com/users/dobe/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/dobe/subscriptions","organizations_url":"https://api.github.com/users/dobe/orgs","repos_url":"https://api.github.com/users/dobe/repos","events_url":"https://api.github.com/users/dobe/events{/privacy}","received_events_url":"https://api.github.com/users/dobe/received_events","type":"User","site_admin":false},"body":"this query does not really help for what you just described:\r\n\r\nsomething like this would be more helpful:\r\nselect schema_name, table_name, partition_ident, status,  recovery['size']['percent'] ...\r\nwhere primary and status <> ...\r\n\r\n\r\n\r\n\r\n","created_at":"2016-04-19T13:19:23Z","updated_at":"2016-04-19T13:19:23Z","html_url":"https://github.com/crate/crate/pull/3459#discussion_r60228495","pull_request_url":"https://api.github.com/repos/crate/crate/pulls/3459","_links":{"self":{"href":"https://api.github.com/repos/crate/crate/pulls/comments/60228495"},"html":{"href":"https://github.com/crate/crate/pull/3459#discussion_r60228495"},"pull_request":{"href":"https://api.github.com/repos/crate/crate/pulls/3459"}}},"pull_request":{"url":"https://api.github.com/repos/crate/crate/pulls/3459","id":66864163,"html_url":"https://github.com/crate/crate/pull/3459","diff_url":"https://github.com/crate/crate/pull/3459.diff","patch_url":"https://github.com/crate/crate/pull/3459.patch","issue_url":"https://api.github.com/repos/crate/crate/issues/3459","number":3459,"state":"open","locked":false,"title":"Added documentation about troubleshooting with sys-tables.","user":{"login":"Nils-Magnus","id":1684438,"avatar_url":"https://avatars.githubusercontent.com/u/1684438?v=3","gravatar_id":"","url":"https://api.github.com/users/Nils-Magnus","html_url":"https://github.com/Nils-Magnus","followers_url":"https://api.github.com/users/Nils-Magnus/followers","following_url":"https://api.github.com/users/Nils-Magnus/following{/other_user}","gists_url":"https://api.github.com/users/Nils-Magnus/gists{/gist_id}","starred_url":"https://api.github.com/users/Nils-Magnus/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/Nils-Magnus/subscriptions","organizations_url":"https://api.github.com/users/Nils-Magnus/orgs","repos_url":"https://api.github.com/users/Nils-Magnus/repos","events_url":"https://api.github.com/users/Nils-Magnus/events{/privacy}","received_events_url":"https://api.github.com/users/Nils-Magnus/received_events","type":"User","site_admin":false},"body":"This is the outcome of our systables story.","created_at":"2016-04-18T14:37:23Z","updated_at":"2016-04-19T13:19:23Z","closed_at":null,"merged_at":null,"merge_commit_sha":"68a979e54cc360c901916a4e0b5464af6af2a8c3","assignee":null,"milestone":null,"commits_url":"https://api.github.com/repos/crate/crate/pulls/3459/commits","review_comments_url":"https://api.github.com/repos/crate/crate/pulls/3459/comments","review_comment_url":"https://api.github.com/repos/crate/crate/pulls/comments{/number}","comments_url":"https://api.github.com/repos/crate/crate/issues/3459/comments","statuses_url":"https://api.github.com/repos/crate/crate/statuses/6b97ae4bc2b892cc52b8d02d44a83073d1bb71f0","head":{"label":"crate:n/systable-doc","ref":"n/systable-doc","sha":"6b97ae4bc2b892cc52b8d02d44a83073d1bb71f0","user":{"login":"crate","id":4048232,"avatar_url":"https://avatars.githubusercontent.com/u/4048232?v=3","gravatar_id":"","url":"https://api.github.com/users/crate","html_url":"https://github.com/crate","followers_url":"https://api.github.com/users/crate/followers","following_url":"https://api.github.com/users/crate/following{/other_user}","gists_url":"https://api.github.com/users/crate/gists{/gist_id}","starred_url":"https://api.github.com/users/crate/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/crate/subscriptions","organizations_url":"https://api.github.com/users/crate/orgs","repos_url":"https://api.github.com/users/crate/repos","events_url":"https://api.github.com/users/crate/events{/privacy}","received_events_url":"https://api.github.com/users/crate/received_events","type":"Organization","site_admin":false},"repo":{"id":9342529,"name":"crate","full_name":"crate/crate","owner":{"login":"crate","id":4048232,"avatar_url":"https://avatars.githubusercontent.com/u/4048232?v=3","gravatar_id":"","url":"https://api.github.com/users/crate","html_url":"https://github.com/crate","followers_url":"https://api.github.com/users/crate/followers","following_url":"https://api.github.com/users/crate/following{/other_user}","gists_url":"https://api.github.com/users/crate/gists{/gist_id}","starred_url":"https://api.github.com/users/crate/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/crate/subscriptions","organizations_url":"https://api.github.com/users/crate/orgs","repos_url":"https://api.github.com/users/crate/repos","events_url":"https://api.github.com/users/crate/events{/privacy}","received_events_url":"https://api.github.com/users/crate/received_events","type":"Organization","site_admin":false},"private":false,"html_url":"https://github.com/crate/crate","description":"Crate.IO: The fast, scalable, easy to use SQL database with native full text search","fork":false,"url":"https://api.github.com/repos/crate/crate","forks_url":"https://api.github.com/repos/crate/crate/forks","keys_url":"https://api.github.com/repos/crate/crate/keys{/key_id}","collaborators_url":"https://api.github.com/repos/crate/crate/collaborators{/collaborator}","teams_url":"https://api.github.com/repos/crate/crate/teams","hooks_url":"https://api.github.com/repos/crate/crate/hooks","issue_events_url":"https://api.github.com/repos/crate/crate/issues/events{/number}","events_url":"https://api.github.com/repos/crate/crate/events","assignees_url":"https://api.github.com/repos/crate/crate/assignees{/user}","branches_url":"https://api.github.com/repos/crate/crate/branches{/branch}","tags_url":"https://api.github.com/repos/crate/crate/tags","blobs_url":"https://api.github.com/repos/crate/crate/git/blobs{/sha}","git_tags_url":"https://api.github.com/repos/crate/crate/git/tags{/sha}","git_refs_url":"https://api.github.com/repos/crate/crate/git/refs{/sha}","trees_url":"https://api.github.com/repos/crate/crate/git/trees{/sha}","statuses_url":"https://api.github.com/repos/crate/crate/statuses/{sha}","languages_url":"https://api.github.com/repos/crate/crate/languages","stargazers_url":"https://api.github.com/repos/crate/crate/stargazers","contributors_url":"https://api.github.com/repos/crate/crate/contributors","subscribers_url":"https://api.github.com/repos/crate/crate/subscribers","subscription_url":"https://api.github.com/repos/crate/crate/subscription","commits_url":"https://api.github.com/repos/crate/crate/commits{/sha}","git_commits_url":"https://api.github.com/repos/crate/crate/git/commits{/sha}","comments_url":"https://api.github.com/repos/crate/crate/comments{/number}","issue_comment_url":"https://api.github.com/repos/crate/crate/issues/comments{/number}","contents_url":"https://api.github.com/repos/crate/crate/contents/{+path}","compare_url":"https://api.github.com/repos/crate/crate/compare/{base}...{head}","merges_url":"https://api.github.com/repos/crate/crate/merges","archive_url":"https://api.github.com/repos/crate/crate/{archive_format}{/ref}","downloads_url":"https://api.github.com/repos/crate/crate/downloads","issues_url":"https://api.github.com/repos/crate/crate/issues{/number}","pulls_url":"https://api.github.com/repos/crate/crate/pulls{/number}","milestones_url":"https://api.github.com/repos/crate/crate/milestones{/number}","notifications_url":"https://api.github.com/repos/crate/crate/notifications{?since,all,participating}","labels_url":"https://api.github.com/repos/crate/crate/labels{/name}","releases_url":"https://api.github.com/repos/crate/crate/releases{/id}","deployments_url":"https://api.github.com/repos/crate/crate/deployments","created_at":"2013-04-10T09:17:16Z","updated_at":"2016-04-19T09:21:58Z","pushed_at":"2016-04-19T13:16:27Z","git_url":"git://github.com/crate/crate.git","ssh_url":"git@github.com:crate/crate.git","clone_url":"https://github.com/crate/crate.git","svn_url":"https://github.com/crate/crate","homepage":"https://crate.io","size":63327,"stargazers_count":1227,"watchers_count":1227,"language":"Java","has_issues":true,"has_downloads":true,"has_wiki":false,"has_pages":false,"forks_count":129,"mirror_url":null,"open_issues_count":45,"forks":129,"open_issues":45,"watchers":1227,"default_branch":"master"}},"base":{"label":"crate:systable-doc","ref":"systable-doc","sha":"ba3c3aba654c9bc0b977a8197d7848b7c74f2604","user":{"login":"crate","id":4048232,"avatar_url":"https://avatars.githubusercontent.com/u/4048232?v=3","gravatar_id":"","url":"https://api.github.com/users/crate","html_url":"https://github.com/crate","followers_url":"https://api.github.com/users/crate/followers","following_url":"https://api.github.com/users/crate/following{/other_user}","gists_url":"https://api.github.com/users/crate/gists{/gist_id}","starred_url":"https://api.github.com/users/crate/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/crate/subscriptions","organizations_url":"https://api.github.com/users/crate/orgs","repos_url":"https://api.github.com/users/crate/repos","events_url":"https://api.github.com/users/crate/events{/privacy}","received_events_url":"https://api.github.com/users/crate/received_events","type":"Organization","site_admin":false},"repo":{"id":9342529,"name":"crate","full_name":"crate/crate","owner":{"login":"crate","id":4048232,"avatar_url":"https://avatars.githubusercontent.com/u/4048232?v=3","gravatar_id":"","url":"https://api.github.com/users/crate","html_url":"https://github.com/crate","followers_url":"https://api.github.com/users/crate/followers","following_url":"https://api.github.com/users/crate/following{/other_user}","gists_url":"https://api.github.com/users/crate/gists{/gist_id}","starred_url":"https://api.github.com/users/crate/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/crate/subscriptions","organizations_url":"https://api.github.com/users/crate/orgs","repos_url":"https://api.github.com/users/crate/repos","events_url":"https://api.github.com/users/crate/events{/privacy}","received_events_url":"https://api.github.com/users/crate/received_events","type":"Organization","site_admin":false},"private":false,"html_url":"https://github.com/crate/crate","description":"Crate.IO: The fast, scalable, easy to use SQL database with native full text search","fork":false,"url":"https://api.github.com/repos/crate/crate","forks_url":"https://api.github.com/repos/crate/crate/forks","keys_url":"https://api.github.com/repos/crate/crate/keys{/key_id}","collaborators_url":"https://api.github.com/repos/crate/crate/collaborators{/collaborator}","teams_url":"https://api.github.com/repos/crate/crate/teams","hooks_url":"https://api.github.com/repos/crate/crate/hooks","issue_events_url":"https://api.github.com/repos/crate/crate/issues/events{/number}","events_url":"https://api.github.com/repos/crate/crate/events","assignees_url":"https://api.github.com/repos/crate/crate/assignees{/user}","branches_url":"https://api.github.com/repos/crate/crate/branches{/branch}","tags_url":"https://api.github.com/repos/crate/crate/tags","blobs_url":"https://api.github.com/repos/crate/crate/git/blobs{/sha}","git_tags_url":"https://api.github.com/repos/crate/crate/git/tags{/sha}","git_refs_url":"https://api.github.com/repos/crate/crate/git/refs{/sha}","trees_url":"https://api.github.com/repos/crate/crate/git/trees{/sha}","statuses_url":"https://api.github.com/repos/crate/crate/statuses/{sha}","languages_url":"https://api.github.com/repos/crate/crate/languages","stargazers_url":"https://api.github.com/repos/crate/crate/stargazers","contributors_url":"https://api.github.com/repos/crate/crate/contributors","subscribers_url":"https://api.github.com/repos/crate/crate/subscribers","subscription_url":"https://api.github.com/repos/crate/crate/subscription","commits_url":"https://api.github.com/repos/crate/crate/commits{/sha}","git_commits_url":"https://api.github.com/repos/crate/crate/git/commits{/sha}","comments_url":"https://api.github.com/repos/crate/crate/comments{/number}","issue_comment_url":"https://api.github.com/repos/crate/crate/issues/comments{/number}","contents_url":"https://api.github.com/repos/crate/crate/contents/{+path}","compare_url":"https://api.github.com/repos/crate/crate/compare/{base}...{head}","merges_url":"https://api.github.com/repos/crate/crate/merges","archive_url":"https://api.github.com/repos/crate/crate/{archive_format}{/ref}","downloads_url":"https://api.github.com/repos/crate/crate/downloads","issues_url":"https://api.github.com/repos/crate/crate/issues{/number}","pulls_url":"https://api.github.com/repos/crate/crate/pulls{/number}","milestones_url":"https://api.github.com/repos/crate/crate/milestones{/number}","notifications_url":"https://api.github.com/repos/crate/crate/notifications{?since,all,participating}","labels_url":"https://api.github.com/repos/crate/crate/labels{/name}","releases_url":"https://api.github.com/repos/crate/crate/releases{/id}","deployments_url":"https://api.github.com/repos/crate/crate/deployments","created_at":"2013-04-10T09:17:16Z","updated_at":"2016-04-19T09:21:58Z","pushed_at":"2016-04-19T13:16:27Z","git_url":"git://github.com/crate/crate.git","ssh_url":"git@github.com:crate/crate.git","clone_url":"https://github.com/crate/crate.git","svn_url":"https://github.com/crate/crate","homepage":"https://crate.io","size":63327,"stargazers_count":1227,"watchers_count":1227,"language":"Java","has_issues":true,"has_downloads":true,"has_wiki":false,"has_pages":false,"forks_count":129,"mirror_url":null,"open_issues_count":45,"forks":129,"open_issues":45,"watchers":1227,"default_branch":"master"}},"_links":{"self":{"href":"https://api.github.com/repos/crate/crate/pulls/3459"},"html":{"href":"https://github.com/crate/crate/pull/3459"},"issue":{"href":"https://api.github.com/repos/crate/crate/issues/3459"},"comments":{"href":"https://api.github.com/repos/crate/crate/issues/3459/comments"},"review_comments":{"href":"https://api.github.com/repos/crate/crate/pulls/3459/comments"},"review_comment":{"href":"https://api.github.com/repos/crate/crate/pulls/comments{/number}"},"commits":{"href":"https://api.github.com/repos/crate/crate/pulls/3459/commits"},"statuses":{"href":"https://api.github.com/repos/crate/crate/statuses/6b97ae4bc2b892cc52b8d02d44a83073d1bb71f0"}}}}