Skip to content

Instantly share code, notes, and snippets.

@hjort
Created October 8, 2011 04:50
Show Gist options
  • Save hjort/1271886 to your computer and use it in GitHub Desktop.
Save hjort/1271886 to your computer and use it in GitHub Desktop.
Collecting stats from multiple databases
database id: 31983, name: bench
start_connection(153778808)
find_database(31983) = 153778808
relid relname seq_scan idx_scan
31993 pgbench_history 0
31990 pgbench_accounts 1 1000
31984 pgbench_branches 1001 0
31987 pgbench_tellers 945 0
find_database(31983) = 153778808
relid relname seq_scan idx_scan
2617 pg_operator 0 216
1261 pg_auth_members 0 0
2600 pg_aggregate 0 2
1136 pg_pltemplate 0 0
2614 pg_listener 0
database id: 30919, name: curso
start_connection(153778752)
find_database(30919) = 153778752
relid relname seq_scan idx_scan
30920 municipios 2 0
find_database(30919) = 153778752
relid relname seq_scan idx_scan
2617 pg_operator 0 480
1261 pg_auth_members 0 0
2600 pg_aggregate 0 48
1136 pg_pltemplate 0 0
2614 pg_listener 0
database id: 11564, name: postgres
start_connection(153775184)
find_database(11564) = 153775184
relid relname seq_scan idx_scan
find_database(11564) = 153775184
relid relname seq_scan idx_scan
2617 pg_operator 0 744
1261 pg_auth_members 0 0
2600 pg_aggregate 0 4
1136 pg_pltemplate 0 0
2614 pg_listener 0
database id: 30918, name: rodrigo
start_connection(153778696)
find_database(30918) = 153778696
relid relname seq_scan idx_scan
find_database(30918) = 153778696
relid relname seq_scan idx_scan
2617 pg_operator 0 72
1261 pg_auth_members 0 0
2600 pg_aggregate 0 6
1136 pg_pltemplate 0 0
2614 pg_listener 0
database id: 16385, name: zabbix
start_connection(153769808)
find_database(16385) = 153769808
relid relname seq_scan idx_scan
17096 profiles 0 0
17397 usrgrp 0 0
16423 applications 0 0
17147 regexps 0 0
16454 conditions 0 0
find_database(16385) = 153769808
relid relname seq_scan idx_scan
2614 pg_listener 0
2612 pg_language 0 0
11472 sql_sizing 0
16834 pg_toast_16804 0 0
17152 pg_toast_17147 0 0
release_connection(153778808)
release_connection(153778752)
release_connection(153775184)
release_connection(153778696)
release_connection(153769808)
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <libpq-fe.h>
#include <uthash.h>
typedef struct db_struct
{
int id; /* key */
char name[10];
PGconn *conn;
UT_hash_handle hh; /* makes this structure hashable */
} DBconn;
DBconn *databases = NULL;
int
add_database(int datid, char *datname)
{
DBconn *db;
db = (DBconn *) malloc(sizeof(DBconn));
db->id = datid;
strcpy(db->name, datname);
db->conn = NULL;
HASH_ADD_INT(databases, id, db); /* id: name of key field */
}
int
name_sort(DBconn * a, DBconn * b)
{
return strcmp(a->name, b->name);
}
void
sort_by_name()
{
HASH_SORT(databases, name_sort);
}
DBconn *
find_database(int datid)
{
DBconn *db;
HASH_FIND_INT(databases, &datid, db); /* db: output pointer */
fprintf(stdout, "find_database(%d) = %d\n", datid, (int) db);
return db;
}
int
start_connection(DBconn * db)
{
char conninfo[255];
PGconn *conn;
fprintf(stdout, "start_connection(%d)\n", (int) db);
//printf("db = %d, db->conn = %d, db->name = %s\n", (int) db, (int) db->conn, db->name);
if (!db->conn)
{
sprintf(conninfo, "dbname=%s user=%s", db->name, "postgres");
//printf("conninfo = %s\n", conninfo);
conn = PQconnectdb(conninfo);
//printf("conn = %d\n", (int) conn);
if (PQstatus(conn) != CONNECTION_OK)
{
fprintf(stderr, "Connection to database %s failed: %s",
db->name, PQerrorMessage(conn));
}
else
{
db->conn = conn;
}
}
return 0;
}
int
release_connection(DBconn * db)
{
fprintf(stdout, "release_connection(%d)\n", (int) db);
if (db->conn)
{
PQfinish(db->conn);
}
return 0;
}
int
remove_database(DBconn * db)
{
// fprintf(stdout, "remove_database(%d)\n", (int) db);
release_connection(db);
HASH_DEL(databases, db);
free(db);
return 0;
}
#define USER_TABLES 1
#define SYSTEM_TABLES 2
#define ALL_TABLES 3
void
show_table_stats(int datid, int filter)
{
DBconn *db;
PGresult *res;
int nFields, i, j;
char sql[100];
//fprintf (stdout, "show_table_stats(%d, %d)\n", datid, filter);
db = find_database(datid);
if (db && db->conn)
{
strcpy(sql, "SELECT relid, relname, seq_scan, idx_scan FROM pg_stat_");
if (filter == USER_TABLES)
strcat(sql, "user");
else if (filter == SYSTEM_TABLES)
strcat(sql, "sys");
else
strcat(sql, "all");
strcat(sql, "_tables");
strcat(sql, " LIMIT 5");
// fprintf(stdout, "conn: %d, sql: %s\n", (int) db->conn, sql);
// printf("status: %d, db: %s\n", PQstatus(db->conn), PQdb(db->conn));
res = PQexec(db->conn, sql);
if (PQresultStatus(res) != PGRES_TUPLES_OK)
{
fprintf(stderr, "SELECT command failed: %s", PQerrorMessage(db->conn));
PQclear(res);
//exit_nicely(conn);
// FIXME: should not let go from this point on...
printf("SELECT command failed: %s", PQerrorMessage(db->conn));
}
nFields = PQnfields(res);
for (i = 0; i < nFields; i++)
printf("%-20s", PQfname(res, i));
printf("\n");
for (i = 0; i < PQntuples(res); i++)
{
for (j = 0; j < nFields; j++)
printf("%-20s", PQgetvalue(res, i, j));
printf("\n");
}
PQclear(res);
}
}
static void
exit_nicely(PGconn * conn)
{
PQfinish(conn);
exit(1);
}
int
main(int argc, char **argv)
{
const char *conninfo;
PGconn *conn;
PGresult *res;
int nFields, i, j;
char *datname;
int datid;
DBconn *db;
/*
* If the user supplies a parameter on the command line, use it as the
* conninfo string; otherwise default to setting dbname=postgres and using
* environment variables or defaults for all other connection parameters.
*/
if (argc > 1)
conninfo = argv[1];
else
conninfo = "dbname = postgres";
/* Make a connection to the database */
conn = PQconnectdb(conninfo);
//printf("conn = %d\n", (int) conn);
/* Check to see that the backend connection was successfully made */
if (PQstatus(conn) != CONNECTION_OK)
{
fprintf(stderr, "Connection to database failed: %s",
PQerrorMessage(conn));
exit_nicely(conn);
}
res =
PQexec(conn,
"SELECT oid, datname FROM pg_database WHERE datname !~ '^template' ORDER BY oid");
if (PQresultStatus(res) != PGRES_TUPLES_OK)
{
fprintf(stderr, "SELECT command failed: %s", PQerrorMessage(conn));
PQclear(res);
exit_nicely(conn);
}
nFields = PQnfields(res);
/*for (i = 0; i < nFields; i++)
printf("%-15s", PQfname(res, i));
printf("\n\n"); */
for (i = 0; i < PQntuples(res); i++)
{
/*for (j = 0; j < nFields; j++)
printf("%-15s", PQgetvalue(res, i, j));
printf("\n");*/
datid = atoi(PQgetvalue(res, i, 0));
datname = PQgetvalue(res, i, 1);
add_database(datid, datname);
}
PQclear(res);
/* close the connection to the database and cleanup */
PQfinish(conn);
// loop through all databases
sort_by_name();
for (db = databases; db != NULL; db = (DBconn *) (db->hh.next))
{
printf("\ndatabase id: %d, name: %s\n", db->id, db->name);
start_connection(db);
// printf("user tables:\n");
show_table_stats(db->id, USER_TABLES);
// printf("system tables:\n");
show_table_stats(db->id, SYSTEM_TABLES);
}
printf("\n");
// free resources
while (databases)
{
db = databases;
remove_database(db);
}
return 0;
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment