src/postgresql_default.conf

   1 # Pre-defined queries of collectd's postgresql plugin.
   2 #
   3 # Do not edit this file. If you want to change any of the query definitions,
   4 # overwrite them in collectd.conf instead.
   5 #
   6 # This file is distributed under the same terms as collectd itself.
   7
   8 <Query backends>
   9         Statement "SELECT count(*) AS count \
  10                 FROM pg_stat_activity \
  11                 WHERE datname = $1;"
  12
  13         Param database
  14
  15         <Result>
  16                 Type "pg_numbackends"
  17                 ValuesFrom "count"
  18         </Result>
  19 </Query>
  20
  21 <Query transactions>
  22         Statement "SELECT xact_commit, xact_rollback \
  23                 FROM pg_stat_database \
  24                 WHERE datname = $1;"
  25
  26         Param database
  27
  28         <Result>
  29                 Type "pg_xact"
  30                 InstancePrefix "commit"
  31                 ValuesFrom "xact_commit"
  32         </Result>
  33         <Result>
  34                 Type "pg_xact"
  35                 InstancePrefix "rollback"
  36                 ValuesFrom "xact_rollback"
  37         </Result>
  38 </Query>
  39
  40 <Query queries>
  41         Statement "SELECT sum(n_tup_ins) AS ins, \
  42                         sum(n_tup_upd) AS upd, \
  43                         sum(n_tup_del) AS del \
  44                 FROM pg_stat_user_tables;"
  45
  46         <Result>
  47                 Type "pg_n_tup_c"
  48                 InstancePrefix "ins"
  49                 ValuesFrom "ins"
  50         </Result>
  51         <Result>
  52                 Type "pg_n_tup_c"
  53                 InstancePrefix "upd"
  54                 ValuesFrom "upd"
  55         </Result>
  56         <Result>
  57                 Type "pg_n_tup_c"
  58                 InstancePrefix "del"
  59                 ValuesFrom "del"
  60         </Result>
  61
  62         MaxVersion 80299
  63 </Query>
  64
  65 <Query queries>
  66         Statement "SELECT sum(n_tup_ins) AS ins, \
  67                         sum(n_tup_upd) AS upd, \
  68                         sum(n_tup_del) AS del, \
  69                         sum(n_tup_hot_upd) AS hot_upd \
  70                 FROM pg_stat_user_tables;"
  71
  72         <Result>
  73                 Type "pg_n_tup_c"
  74                 InstancePrefix "ins"
  75                 ValuesFrom "ins"
  76         </Result>
  77         <Result>
  78                 Type "pg_n_tup_c"
  79                 InstancePrefix "upd"
  80                 ValuesFrom "upd"
  81         </Result>
  82         <Result>
  83                 Type "pg_n_tup_c"
  84                 InstancePrefix "del"
  85                 ValuesFrom "del"
  86         </Result>
  87         <Result>
  88                 Type "pg_n_tup_c"
  89                 InstancePrefix "hot_upd"
  90                 ValuesFrom "hot_upd"
  91         </Result>
  92
  93         MinVersion 80300
  94 </Query>
  95
  96 <Query queries_by_table>
  97         Statement "SELECT schemaname, relname, \
  98                         n_tup_ins AS ins, \
  99                         n_tup_upd AS upd, \
 100                         n_tup_del AS del \
 101                 FROM pg_stat_user_tables;"
 102
 103         <Result>
 104                 Type "pg_n_tup_c"
 105                 InstancePrefix "ins"
 106                 InstancesFrom "schemaname" "relname"
 107                 ValuesFrom "ins"
 108         </Result>
 109         <Result>
 110                 Type "pg_n_tup_c"
 111                 InstancePrefix "upd"
 112                 InstancesFrom "schemaname" "relname"
 113                 ValuesFrom "upd"
 114         </Result>
 115         <Result>
 116                 Type "pg_n_tup_c"
 117                 InstancePrefix "del"
 118                 InstancesFrom "schemaname" "relname"
 119                 ValuesFrom "del"
 120         </Result>
 121
 122         MaxVersion 80299
 123 </Query>
 124
 125 <Query queries_by_table>
 126         Statement "SELECT schemaname, relname, \
 127                         n_tup_ins AS ins, \
 128                         n_tup_upd AS upd, \
 129                         n_tup_del AS del, \
 130                         n_tup_hot_upd AS hot_upd \
 131                 FROM pg_stat_user_tables;"
 132
 133         <Result>
 134                 Type "pg_n_tup_c"
 135                 InstancePrefix "ins"
 136                 InstancesFrom "schemaname" "relname"
 137                 ValuesFrom "ins"
 138         </Result>
 139         <Result>
 140                 Type "pg_n_tup_c"
 141                 InstancePrefix "upd"
 142                 InstancesFrom "schemaname" "relname"
 143                 ValuesFrom "upd"
 144         </Result>
 145         <Result>
 146                 Type "pg_n_tup_c"
 147                 InstancePrefix "del"
 148                 InstancesFrom "schemaname" "relname"
 149                 ValuesFrom "del"
 150         </Result>
 151         <Result>
 152                 Type "pg_n_tup_c"
 153                 InstancePrefix "hot_upd"
 154                 InstancesFrom "schemaname" "relname"
 155                 ValuesFrom "hot_upd"
 156         </Result>
 157
 158         MinVersion 80300
 159 </Query>
 160
 161 <Query query_plans>
 162         Statement "SELECT coalesce(sum(seq_scan), 0) AS seq, \
 163                           coalesce(sum(seq_tup_read), 0) AS seq_tup_read, \
 164                           coalesce(sum(idx_scan), 0) AS idx, \
 165                           coalesce(sum(idx_tup_fetch), 0) AS idx_tup_fetch \
 166                 FROM pg_stat_user_tables;"
 167
 168         <Result>
 169                 Type "pg_scan"
 170                 InstancePrefix "seq"
 171                 ValuesFrom "seq"
 172         </Result>
 173         <Result>
 174                 Type "pg_scan"
 175                 InstancePrefix "seq_tup_read"
 176                 ValuesFrom "seq_tup_read"
 177         </Result>
 178         <Result>
 179                 Type "pg_scan"
 180                 InstancePrefix "idx"
 181                 ValuesFrom "idx"
 182         </Result>
 183         <Result>
 184                 Type "pg_scan"
 185                 InstancePrefix "idx_tup_fetch"
 186                 ValuesFrom "idx_tup_fetch"
 187         </Result>
 188 </Query>
 189
 190 <Query table_states>
 191         Statement "SELECT sum(n_live_tup) AS live, sum(n_dead_tup) AS dead \
 192                 FROM pg_stat_user_tables;"
 193
 194         <Result>
 195                 Type "pg_n_tup_g"
 196                 InstancePrefix "live"
 197                 ValuesFrom "live"
 198         </Result>
 199         <Result>
 200                 Type "pg_n_tup_g"
 201                 InstancePrefix "dead"
 202                 ValuesFrom "dead"
 203         </Result>
 204
 205         MinVersion 80300
 206 </Query>
 207
 208 <Query query_plans_by_table>
 209         Statement "SELECT schemaname, relname, \
 210                         coalesce(seq_scan, 0) AS seq, \
 211                         coalesce(seq_tup_read, 0) AS seq_tup_read, \
 212                         coalesce(idx_scan, 0) AS idx, \
 213                         coalesce(idx_tup_fetch, 0) AS idx_tup_fetch \
 214                 FROM pg_stat_user_tables;"
 215
 216         <Result>
 217                 Type "pg_scan"
 218                 InstancePrefix "seq"
 219                 InstancesFrom "schemaname" "relname"
 220                 ValuesFrom "seq"
 221         </Result>
 222         <Result>
 223                 Type "pg_scan"
 224                 InstancePrefix "seq_tup_read"
 225                 InstancesFrom "schemaname" "relname"
 226                 ValuesFrom "seq_tup_read"
 227         </Result>
 228         <Result>
 229                 Type "pg_scan"
 230                 InstancePrefix "idx"
 231                 InstancesFrom "schemaname" "relname"
 232                 ValuesFrom "idx"
 233         </Result>
 234         <Result>
 235                 Type "pg_scan"
 236                 InstancePrefix "idx_tup_fetch"
 237                 InstancesFrom "schemaname" "relname"
 238                 ValuesFrom "idx_tup_fetch"
 239         </Result>
 240 </Query>
 241
 242 <Query table_states_by_table>
 243         Statement "SELECT schemaname, relname, \
 244                         n_live_tup AS live, n_dead_tup AS dead \
 245                 FROM pg_stat_user_tables;"
 246
 247         <Result>
 248                 Type "pg_n_tup_g"
 249                 InstancePrefix "live"
 250                 InstancesFrom "schemaname" "relname"
 251                 ValuesFrom "live"
 252         </Result>
 253         <Result>
 254                 Type "pg_n_tup_g"
 255                 InstancePrefix "dead"
 256                 InstancesFrom "schemaname" "relname"
 257                 ValuesFrom "dead"
 258         </Result>
 259
 260         MinVersion 80300
 261 </Query>
 262
 263 <Query disk_io>
 264         Statement "SELECT coalesce(sum(heap_blks_read), 0) AS heap_read, \
 265                         coalesce(sum(heap_blks_hit), 0) AS heap_hit, \
 266                         coalesce(sum(idx_blks_read), 0) AS idx_read, \
 267                         coalesce(sum(idx_blks_hit), 0) AS idx_hit, \
 268                         coalesce(sum(toast_blks_read), 0) AS toast_read, \
 269                         coalesce(sum(toast_blks_hit), 0) AS toast_hit, \
 270                         coalesce(sum(tidx_blks_read), 0) AS tidx_read, \
 271                         coalesce(sum(tidx_blks_hit), 0) AS tidx_hit \
 272                 FROM pg_statio_user_tables;"
 273
 274         <Result>
 275                 Type "pg_blks"
 276                 InstancePrefix "heap_read"
 277                 ValuesFrom "heap_read"
 278         </Result>
 279         <Result>
 280                 Type "pg_blks"
 281                 InstancePrefix "heap_hit"
 282                 ValuesFrom "heap_hit"
 283         </Result>
 284         <Result>
 285                 Type "pg_blks"
 286                 InstancePrefix "idx_read"
 287                 ValuesFrom "idx_read"
 288         </Result>
 289         <Result>
 290                 Type "pg_blks"
 291                 InstancePrefix "idx_hit"
 292                 ValuesFrom "idx_hit"
 293         </Result>
 294         <Result>
 295                 Type "pg_blks"
 296                 InstancePrefix "toast_read"
 297                 ValuesFrom "toast_read"
 298         </Result>
 299         <Result>
 300                 Type "pg_blks"
 301                 InstancePrefix "toast_hit"
 302                 ValuesFrom "toast_hit"
 303         </Result>
 304         <Result>
 305                 Type "pg_blks"
 306                 InstancePrefix "tidx_read"
 307                 ValuesFrom "tidx_read"
 308         </Result>
 309         <Result>
 310                 Type "pg_blks"
 311                 InstancePrefix "tidx_hit"
 312                 ValuesFrom "tidx_hit"
 313         </Result>
 314 </Query>
 315
 316 <Query disk_io_by_table>
 317         Statement "SELECT schemaname, relname, \
 318                         coalesce(heap_blks_read, 0) AS heap_read, \
 319                         coalesce(heap_blks_hit, 0) AS heap_hit, \
 320                         coalesce(idx_blks_read, 0) AS idx_read, \
 321                         coalesce(idx_blks_hit, 0) AS idx_hit, \
 322                         coalesce(toast_blks_read, 0) AS toast_read, \
 323                         coalesce(toast_blks_hit, 0) AS toast_hit, \
 324                         coalesce(tidx_blks_read, 0) AS tidx_read, \
 325                         coalesce(tidx_blks_hit, 0) AS tidx_hit \
 326                 FROM pg_statio_user_tables;"
 327
 328         <Result>
 329                 Type "pg_blks"
 330                 InstancePrefix "heap_read"
 331                 InstancesFrom "schemaname" "relname"
 332                 ValuesFrom "heap_read"
 333         </Result>
 334         <Result>
 335                 Type "pg_blks"
 336                 InstancePrefix "heap_hit"
 337                 InstancesFrom "schemaname" "relname"
 338                 ValuesFrom "heap_hit"
 339         </Result>
 340         <Result>
 341                 Type "pg_blks"
 342                 InstancePrefix "idx_read"
 343                 InstancesFrom "schemaname" "relname"
 344                 ValuesFrom "idx_read"
 345         </Result>
 346         <Result>
 347                 Type "pg_blks"
 348                 InstancePrefix "idx_hit"
 349                 InstancesFrom "schemaname" "relname"
 350                 ValuesFrom "idx_hit"
 351         </Result>
 352         <Result>
 353                 Type "pg_blks"
 354                 InstancePrefix "toast_read"
 355                 InstancesFrom "schemaname" "relname"
 356                 ValuesFrom "toast_read"
 357         </Result>
 358         <Result>
 359                 Type "pg_blks"
 360                 InstancePrefix "toast_hit"
 361                 InstancesFrom "schemaname" "relname"
 362                 ValuesFrom "toast_hit"
 363         </Result>
 364         <Result>
 365                 Type "pg_blks"
 366                 InstancePrefix "tidx_read"
 367                 InstancesFrom "schemaname" "relname"
 368                 ValuesFrom "tidx_read"
 369         </Result>
 370         <Result>
 371                 Type "pg_blks"
 372                 InstancePrefix "tidx_hit"
 373                 InstancesFrom "schemaname" "relname"
 374                 ValuesFrom "tidx_hit"
 375         </Result>
 376 </Query>
 377
 378 <Query disk_usage>
 379         Statement "SELECT pg_database_size($1) AS size;"
 380
 381         Param database
 382
 383         <Result>
 384                 Type pg_db_size
 385                 ValuesFrom "size"
 386         </Result>
 387 </Query>
 388
 389 # vim: set ft=config :
 390