{"action":"created","comment":{"url":"https://api.github.com/repos/DataDog/dd-agent/pulls/comments/25635871","id":25635871,"diff_hunk":"@@ -0,0 +1,166 @@\n+\"\"\"Pgbouncer check\n+\n+Collects metrics from the pgbouncer database.\n+\"\"\"\n+from checks import AgentCheck, CheckException\n+\n+import psycopg2 as pg\n+\n+class ShouldRestartException(Exception): pass\n+\n+class PgBouncer(AgentCheck):\n+    \"\"\"Collects metrics from pgbouncer\n+    \"\"\"\n+    SOURCE_TYPE_NAME = 'pgbouncer'\n+    RATE = AgentCheck.rate\n+    GAUGE = AgentCheck.gauge\n+\n+    STATS_METRICS = {\n+        'descriptors': [\n+            ('database', 'db'),\n+        ],\n+        'metrics': {\n+            'total_requests':       ('pgbouncer.stats.requests_per_second', RATE),\n+            'total_received':       ('pgbouncer.stats.bytes_received_per_second', RATE),\n+            'total_sent':           ('pgbouncer.stats.bytes_sent_per_second', RATE),\n+            'total_query_time':     ('pgbouncer.stats.total_query_time', GAUGE),\n+            'avg_req':              ('pgbouncer.stats.avg_req', GAUGE),\n+            'avg_recv':             ('pgbouncer.stats.avg_recv', GAUGE),\n+            'avg_sent':             ('pgbouncer.stats.avg_sent', GAUGE),\n+            'avg_query':            ('pgbouncer.stats.avg_query', GAUGE),\n+        },\n+        'query': \"\"\"SHOW STATS\"\"\",\n+    }\n+\n+    POOLS_METRICS = {\n+        'descriptors': [\n+            ('database', 'db'),\n+            ('user', 'user'),\n+        ],\n+        'metrics': {\n+            'cl_active':            ('pgbouncer.pools.cl_active', GAUGE),\n+            'cl_waiting':           ('pgbouncer.pools.cl_waiting', GAUGE),\n+            'sv_active':            ('pgbouncer.pools.sv_active', GAUGE),\n+            'sv_idle':              ('pgbouncer.pools.sv_idle', GAUGE),\n+            'sv_used':              ('pgbouncer.pools.sv_used', GAUGE),\n+            'sv_tested':            ('pgbouncer.pools.sv_tested', GAUGE),\n+            'sv_login':             ('pgbouncer.pools.sv_login', GAUGE),\n+            'maxwait':              ('pgbouncer.pools.maxwait', GAUGE),\n+        },\n+        'query': \"\"\"SHOW POOLS\"\"\",\n+    }\n+\n+    def __init__(self, name, init_config, agentConfig, instances=None):\n+        AgentCheck.__init__(self, name, init_config, agentConfig, instances)\n+        self.dbs = {}\n+\n+    def _collect_stats(self, key, db, instance_tags):\n+        \"\"\"Query pgbouncer for various metrics\n+        \"\"\"\n+\n+        metric_scope = [self.STATS_METRICS, self.POOLS_METRICS]\n+\n+        try:\n+            cursor = db.cursor()\n+            for scope in metric_scope:\n+\n+                cols = scope['metrics'].keys()\n+\n+                try:\n+                    query = scope['query']\n+                    self.log.debug(\"Running query: %s\" % query)\n+                    cursor.execute(query)\n+\n+                    results = cursor.fetchall()\n+                except pg.Error, e:\n+                    self.log.warning(\"Not all metrics may be available: %s\" % str(e))\n+                    continue\n+\n+                for row in results:\n+                    if row[0] == 'pgbouncer':\n+                        continue\n+\n+                    desc = scope['descriptors']\n+                    assert len(row) == len(cols) + len(desc)\n+\n+                    tags = [t for t in instance_tags]\n+                    tags += [\"%s:%s\" % (d[0][1], d[1]) for d in zip(desc, row[:len(desc)])]\n+\n+                    values = zip([scope['metrics'][c] for c in cols], row[len(desc):])\n+\n+                    [v[0][1](self, v[0][0], v[1], tags=tags) for v in values]\n+\n+            if not results:\n+                self.warning('No results were found for query: \"%s\"' % query)\n+\n+            cursor.close()\n+        except pg.Error, e:\n+            self.log.error(\"Connection error: %s\" % str(e))\n+            raise ShouldRestartException\n+\n+    def _get_connection(self, key, host, port, user, password, dbname, use_cached=True):\n+        \"Get and memoize connections to instances\"\n+        if key in self.dbs and use_cached:\n+            return self.dbs[key]\n+\n+        elif host != \"\" and user != \"\":\n+            try:\n+                service_check_tags = [\n+                    \"host:%s\" % host,\n+                    \"port:%s\" % port\n+                ]\n+                if dbname:\n+                    service_check_tags.append(\"db:%s\" % dbname)\n+\n+                if host == 'localhost' and password == '':\n+                    # Use ident method\n+                    connection = pg.connect(\"user=%s dbname=%s\" % (user, dbname))\n+                elif port != '':\n+                    connection = pg.connect(host=host, port=port, user=user,\n+                        password=password, database=dbname)\n+                else:\n+                    connection = pg.connect(host=host, user=user, password=password,\n+                        database=dbname)\n+                status = AgentCheck.OK\n+                self.service_check('pgbouncer.can_connect', status, tags=service_check_tags)\n+                self.log.debug('pgbouncer status: %s' % status)\n+\n+            except Exception:\n+                status = AgentCheck.CRITICAL\n+                self.service_check('pgbouncer.can_connect', status, tags=service_check_tags)\n+                self.log.debug('pgbouncer status: %s' % status)\n+                raise\n+        else:\n+            if not host:\n+                raise CheckException(\"Please specify a PgBouncer host to connect to.\")\n+            elif not user:\n+                raise CheckException(\"Please specify a user to connect to PgBouncer as.\")\n+\n+        connection.set_isolation_level(pg.extensions.ISOLATION_LEVEL_AUTOCOMMIT)\n+        # connection.autocommit = True\n+\n+        self.dbs[key] = connection\n+        return connection\n+\n+    def check(self, instance):\n+        host = instance.get('host', '')\n+        port = instance.get('port', '')\n+        user = instance.get('username', '')\n+        password = instance.get('password', '')\n+        tags = instance.get('tags', [])\n+        dbname = 'pgbouncer'","path":"checks.d/pgbouncer.py","position":151,"original_position":151,"commit_id":"cb2c3350f1b47a4c6cb883cc5510fff9c1a3b5ea","original_commit_id":"cb2c3350f1b47a4c6cb883cc5510fff9c1a3b5ea","user":{"login":"LeoCavaille","id":1788830,"avatar_url":"https://avatars.githubusercontent.com/u/1788830?v=3","gravatar_id":"","url":"https://api.github.com/users/LeoCavaille","html_url":"https://github.com/LeoCavaille","followers_url":"https://api.github.com/users/LeoCavaille/followers","following_url":"https://api.github.com/users/LeoCavaille/following{/other_user}","gists_url":"https://api.github.com/users/LeoCavaille/gists{/gist_id}","starred_url":"https://api.github.com/users/LeoCavaille/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/LeoCavaille/subscriptions","organizations_url":"https://api.github.com/users/LeoCavaille/orgs","repos_url":"https://api.github.com/users/LeoCavaille/repos","events_url":"https://api.github.com/users/LeoCavaille/events{/privacy}","received_events_url":"https://api.github.com/users/LeoCavaille/received_events","type":"User","site_admin":false},"body":"Can we put that as a constant and stop carrying it around in method args?","created_at":"2015-03-02T20:37:09Z","updated_at":"2015-03-02T20:37:09Z","html_url":"https://github.com/DataDog/dd-agent/pull/1391#discussion_r25635871","pull_request_url":"https://api.github.com/repos/DataDog/dd-agent/pulls/1391","_links":{"self":{"href":"https://api.github.com/repos/DataDog/dd-agent/pulls/comments/25635871"},"html":{"href":"https://github.com/DataDog/dd-agent/pull/1391#discussion_r25635871"},"pull_request":{"href":"https://api.github.com/repos/DataDog/dd-agent/pulls/1391"}}},"pull_request":{"url":"https://api.github.com/repos/DataDog/dd-agent/pulls/1391","id":29943304,"html_url":"https://github.com/DataDog/dd-agent/pull/1391","diff_url":"https://github.com/DataDog/dd-agent/pull/1391.diff","patch_url":"https://github.com/DataDog/dd-agent/pull/1391.patch","issue_url":"https://api.github.com/repos/DataDog/dd-agent/issues/1391","number":1391,"state":"open","locked":false,"title":"Add pgbouncer check and tests","user":{"login":"DorianZaccaria","id":5608163,"avatar_url":"https://avatars.githubusercontent.com/u/5608163?v=3","gravatar_id":"","url":"https://api.github.com/users/DorianZaccaria","html_url":"https://github.com/DorianZaccaria","followers_url":"https://api.github.com/users/DorianZaccaria/followers","following_url":"https://api.github.com/users/DorianZaccaria/following{/other_user}","gists_url":"https://api.github.com/users/DorianZaccaria/gists{/gist_id}","starred_url":"https://api.github.com/users/DorianZaccaria/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/DorianZaccaria/subscriptions","organizations_url":"https://api.github.com/users/DorianZaccaria/orgs","repos_url":"https://api.github.com/users/DorianZaccaria/repos","events_url":"https://api.github.com/users/DorianZaccaria/events{/privacy}","received_events_url":"https://api.github.com/users/DorianZaccaria/received_events","type":"User","site_admin":false},"body":"","created_at":"2015-02-24T22:38:09Z","updated_at":"2015-03-02T20:37:09Z","closed_at":null,"merged_at":null,"merge_commit_sha":"21b9132c0e8f7c470376846625f1f9c9cd284ad7","assignee":{"login":"LeoCavaille","id":1788830,"avatar_url":"https://avatars.githubusercontent.com/u/1788830?v=3","gravatar_id":"","url":"https://api.github.com/users/LeoCavaille","html_url":"https://github.com/LeoCavaille","followers_url":"https://api.github.com/users/LeoCavaille/followers","following_url":"https://api.github.com/users/LeoCavaille/following{/other_user}","gists_url":"https://api.github.com/users/LeoCavaille/gists{/gist_id}","starred_url":"https://api.github.com/users/LeoCavaille/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/LeoCavaille/subscriptions","organizations_url":"https://api.github.com/users/LeoCavaille/orgs","repos_url":"https://api.github.com/users/LeoCavaille/repos","events_url":"https://api.github.com/users/LeoCavaille/events{/privacy}","received_events_url":"https://api.github.com/users/LeoCavaille/received_events","type":"User","site_admin":false},"milestone":null,"commits_url":"https://api.github.com/repos/DataDog/dd-agent/pulls/1391/commits","review_comments_url":"https://api.github.com/repos/DataDog/dd-agent/pulls/1391/comments","review_comment_url":"https://api.github.com/repos/DataDog/dd-agent/pulls/comments{/number}","comments_url":"https://api.github.com/repos/DataDog/dd-agent/issues/1391/comments","statuses_url":"https://api.github.com/repos/DataDog/dd-agent/statuses/cb2c3350f1b47a4c6cb883cc5510fff9c1a3b5ea","head":{"label":"DataDog:dorian/pgbouncer","ref":"dorian/pgbouncer","sha":"cb2c3350f1b47a4c6cb883cc5510fff9c1a3b5ea","user":{"login":"DataDog","id":365230,"avatar_url":"https://avatars.githubusercontent.com/u/365230?v=3","gravatar_id":"","url":"https://api.github.com/users/DataDog","html_url":"https://github.com/DataDog","followers_url":"https://api.github.com/users/DataDog/followers","following_url":"https://api.github.com/users/DataDog/following{/other_user}","gists_url":"https://api.github.com/users/DataDog/gists{/gist_id}","starred_url":"https://api.github.com/users/DataDog/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/DataDog/subscriptions","organizations_url":"https://api.github.com/users/DataDog/orgs","repos_url":"https://api.github.com/users/DataDog/repos","events_url":"https://api.github.com/users/DataDog/events{/privacy}","received_events_url":"https://api.github.com/users/DataDog/received_events","type":"Organization","site_admin":false},"repo":{"id":1210071,"name":"dd-agent","full_name":"DataDog/dd-agent","owner":{"login":"DataDog","id":365230,"avatar_url":"https://avatars.githubusercontent.com/u/365230?v=3","gravatar_id":"","url":"https://api.github.com/users/DataDog","html_url":"https://github.com/DataDog","followers_url":"https://api.github.com/users/DataDog/followers","following_url":"https://api.github.com/users/DataDog/following{/other_user}","gists_url":"https://api.github.com/users/DataDog/gists{/gist_id}","starred_url":"https://api.github.com/users/DataDog/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/DataDog/subscriptions","organizations_url":"https://api.github.com/users/DataDog/orgs","repos_url":"https://api.github.com/users/DataDog/repos","events_url":"https://api.github.com/users/DataDog/events{/privacy}","received_events_url":"https://api.github.com/users/DataDog/received_events","type":"Organization","site_admin":false},"private":false,"html_url":"https://github.com/DataDog/dd-agent","description":"Datadog Agent","fork":false,"url":"https://api.github.com/repos/DataDog/dd-agent","forks_url":"https://api.github.com/repos/DataDog/dd-agent/forks","keys_url":"https://api.github.com/repos/DataDog/dd-agent/keys{/key_id}","collaborators_url":"https://api.github.com/repos/DataDog/dd-agent/collaborators{/collaborator}","teams_url":"https://api.github.com/repos/DataDog/dd-agent/teams","hooks_url":"https://api.github.com/repos/DataDog/dd-agent/hooks","issue_events_url":"https://api.github.com/repos/DataDog/dd-agent/issues/events{/number}","events_url":"https://api.github.com/repos/DataDog/dd-agent/events","assignees_url":"https://api.github.com/repos/DataDog/dd-agent/assignees{/user}","branches_url":"https://api.github.com/repos/DataDog/dd-agent/branches{/branch}","tags_url":"https://api.github.com/repos/DataDog/dd-agent/tags","blobs_url":"https://api.github.com/repos/DataDog/dd-agent/git/blobs{/sha}","git_tags_url":"https://api.github.com/repos/DataDog/dd-agent/git/tags{/sha}","git_refs_url":"https://api.github.com/repos/DataDog/dd-agent/git/refs{/sha}","trees_url":"https://api.github.com/repos/DataDog/dd-agent/git/trees{/sha}","statuses_url":"https://api.github.com/repos/DataDog/dd-agent/statuses/{sha}","languages_url":"https://api.github.com/repos/DataDog/dd-agent/languages","stargazers_url":"https://api.github.com/repos/DataDog/dd-agent/stargazers","contributors_url":"https://api.github.com/repos/DataDog/dd-agent/contributors","subscribers_url":"https://api.github.com/repos/DataDog/dd-agent/subscribers","subscription_url":"https://api.github.com/repos/DataDog/dd-agent/subscription","commits_url":"https://api.github.com/repos/DataDog/dd-agent/commits{/sha}","git_commits_url":"https://api.github.com/repos/DataDog/dd-agent/git/commits{/sha}","comments_url":"https://api.github.com/repos/DataDog/dd-agent/comments{/number}","issue_comment_url":"https://api.github.com/repos/DataDog/dd-agent/issues/comments{/number}","contents_url":"https://api.github.com/repos/DataDog/dd-agent/contents/{+path}","compare_url":"https://api.github.com/repos/DataDog/dd-agent/compare/{base}...{head}","merges_url":"https://api.github.com/repos/DataDog/dd-agent/merges","archive_url":"https://api.github.com/repos/DataDog/dd-agent/{archive_format}{/ref}","downloads_url":"https://api.github.com/repos/DataDog/dd-agent/downloads","issues_url":"https://api.github.com/repos/DataDog/dd-agent/issues{/number}","pulls_url":"https://api.github.com/repos/DataDog/dd-agent/pulls{/number}","milestones_url":"https://api.github.com/repos/DataDog/dd-agent/milestones{/number}","notifications_url":"https://api.github.com/repos/DataDog/dd-agent/notifications{?since,all,participating}","labels_url":"https://api.github.com/repos/DataDog/dd-agent/labels{/name}","releases_url":"https://api.github.com/repos/DataDog/dd-agent/releases{/id}","created_at":"2010-12-31T03:02:47Z","updated_at":"2015-03-02T00:20:02Z","pushed_at":"2015-03-02T20:25:20Z","git_url":"git://github.com/DataDog/dd-agent.git","ssh_url":"git@github.com:DataDog/dd-agent.git","clone_url":"https://github.com/DataDog/dd-agent.git","svn_url":"https://github.com/DataDog/dd-agent","homepage":"http://docs.datadoghq.com/","size":43951,"stargazers_count":251,"watchers_count":251,"language":"Python","has_issues":true,"has_downloads":false,"has_wiki":true,"has_pages":true,"forks_count":178,"mirror_url":null,"open_issues_count":161,"forks":178,"open_issues":161,"watchers":251,"default_branch":"master"}},"base":{"label":"DataDog:master","ref":"master","sha":"92470f0a2e3a943036af1a01b899bfcc0361eaeb","user":{"login":"DataDog","id":365230,"avatar_url":"https://avatars.githubusercontent.com/u/365230?v=3","gravatar_id":"","url":"https://api.github.com/users/DataDog","html_url":"https://github.com/DataDog","followers_url":"https://api.github.com/users/DataDog/followers","following_url":"https://api.github.com/users/DataDog/following{/other_user}","gists_url":"https://api.github.com/users/DataDog/gists{/gist_id}","starred_url":"https://api.github.com/users/DataDog/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/DataDog/subscriptions","organizations_url":"https://api.github.com/users/DataDog/orgs","repos_url":"https://api.github.com/users/DataDog/repos","events_url":"https://api.github.com/users/DataDog/events{/privacy}","received_events_url":"https://api.github.com/users/DataDog/received_events","type":"Organization","site_admin":false},"repo":{"id":1210071,"name":"dd-agent","full_name":"DataDog/dd-agent","owner":{"login":"DataDog","id":365230,"avatar_url":"https://avatars.githubusercontent.com/u/365230?v=3","gravatar_id":"","url":"https://api.github.com/users/DataDog","html_url":"https://github.com/DataDog","followers_url":"https://api.github.com/users/DataDog/followers","following_url":"https://api.github.com/users/DataDog/following{/other_user}","gists_url":"https://api.github.com/users/DataDog/gists{/gist_id}","starred_url":"https://api.github.com/users/DataDog/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/DataDog/subscriptions","organizations_url":"https://api.github.com/users/DataDog/orgs","repos_url":"https://api.github.com/users/DataDog/repos","events_url":"https://api.github.com/users/DataDog/events{/privacy}","received_events_url":"https://api.github.com/users/DataDog/received_events","type":"Organization","site_admin":false},"private":false,"html_url":"https://github.com/DataDog/dd-agent","description":"Datadog Agent","fork":false,"url":"https://api.github.com/repos/DataDog/dd-agent","forks_url":"https://api.github.com/repos/DataDog/dd-agent/forks","keys_url":"https://api.github.com/repos/DataDog/dd-agent/keys{/key_id}","collaborators_url":"https://api.github.com/repos/DataDog/dd-agent/collaborators{/collaborator}","teams_url":"https://api.github.com/repos/DataDog/dd-agent/teams","hooks_url":"https://api.github.com/repos/DataDog/dd-agent/hooks","issue_events_url":"https://api.github.com/repos/DataDog/dd-agent/issues/events{/number}","events_url":"https://api.github.com/repos/DataDog/dd-agent/events","assignees_url":"https://api.github.com/repos/DataDog/dd-agent/assignees{/user}","branches_url":"https://api.github.com/repos/DataDog/dd-agent/branches{/branch}","tags_url":"https://api.github.com/repos/DataDog/dd-agent/tags","blobs_url":"https://api.github.com/repos/DataDog/dd-agent/git/blobs{/sha}","git_tags_url":"https://api.github.com/repos/DataDog/dd-agent/git/tags{/sha}","git_refs_url":"https://api.github.com/repos/DataDog/dd-agent/git/refs{/sha}","trees_url":"https://api.github.com/repos/DataDog/dd-agent/git/trees{/sha}","statuses_url":"https://api.github.com/repos/DataDog/dd-agent/statuses/{sha}","languages_url":"https://api.github.com/repos/DataDog/dd-agent/languages","stargazers_url":"https://api.github.com/repos/DataDog/dd-agent/stargazers","contributors_url":"https://api.github.com/repos/DataDog/dd-agent/contributors","subscribers_url":"https://api.github.com/repos/DataDog/dd-agent/subscribers","subscription_url":"https://api.github.com/repos/DataDog/dd-agent/subscription","commits_url":"https://api.github.com/repos/DataDog/dd-agent/commits{/sha}","git_commits_url":"https://api.github.com/repos/DataDog/dd-agent/git/commits{/sha}","comments_url":"https://api.github.com/repos/DataDog/dd-agent/comments{/number}","issue_comment_url":"https://api.github.com/repos/DataDog/dd-agent/issues/comments{/number}","contents_url":"https://api.github.com/repos/DataDog/dd-agent/contents/{+path}","compare_url":"https://api.github.com/repos/DataDog/dd-agent/compare/{base}...{head}","merges_url":"https://api.github.com/repos/DataDog/dd-agent/merges","archive_url":"https://api.github.com/repos/DataDog/dd-agent/{archive_format}{/ref}","downloads_url":"https://api.github.com/repos/DataDog/dd-agent/downloads","issues_url":"https://api.github.com/repos/DataDog/dd-agent/issues{/number}","pulls_url":"https://api.github.com/repos/DataDog/dd-agent/pulls{/number}","milestones_url":"https://api.github.com/repos/DataDog/dd-agent/milestones{/number}","notifications_url":"https://api.github.com/repos/DataDog/dd-agent/notifications{?since,all,participating}","labels_url":"https://api.github.com/repos/DataDog/dd-agent/labels{/name}","releases_url":"https://api.github.com/repos/DataDog/dd-agent/releases{/id}","created_at":"2010-12-31T03:02:47Z","updated_at":"2015-03-02T00:20:02Z","pushed_at":"2015-03-02T20:25:20Z","git_url":"git://github.com/DataDog/dd-agent.git","ssh_url":"git@github.com:DataDog/dd-agent.git","clone_url":"https://github.com/DataDog/dd-agent.git","svn_url":"https://github.com/DataDog/dd-agent","homepage":"http://docs.datadoghq.com/","size":43951,"stargazers_count":251,"watchers_count":251,"language":"Python","has_issues":true,"has_downloads":false,"has_wiki":true,"has_pages":true,"forks_count":178,"mirror_url":null,"open_issues_count":161,"forks":178,"open_issues":161,"watchers":251,"default_branch":"master"}},"_links":{"self":{"href":"https://api.github.com/repos/DataDog/dd-agent/pulls/1391"},"html":{"href":"https://github.com/DataDog/dd-agent/pull/1391"},"issue":{"href":"https://api.github.com/repos/DataDog/dd-agent/issues/1391"},"comments":{"href":"https://api.github.com/repos/DataDog/dd-agent/issues/1391/comments"},"review_comments":{"href":"https://api.github.com/repos/DataDog/dd-agent/pulls/1391/comments"},"review_comment":{"href":"https://api.github.com/repos/DataDog/dd-agent/pulls/comments{/number}"},"commits":{"href":"https://api.github.com/repos/DataDog/dd-agent/pulls/1391/commits"},"statuses":{"href":"https://api.github.com/repos/DataDog/dd-agent/statuses/cb2c3350f1b47a4c6cb883cc5510fff9c1a3b5ea"}}}}