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 coalesce(sum(n_tup_ins), 0) AS ins, \
  42                         coalesce(sum(n_tup_upd), 0) AS upd, \
  43                         coalesce(sum(n_tup_del), 0) 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 coalesce(sum(n_tup_ins), 0) AS ins, \
  67                         coalesce(sum(n_tup_upd), 0) AS upd, \
  68                         coalesce(sum(n_tup_del), 0) AS del, \
  69                         coalesce(sum(n_tup_hot_upd), 0) 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 coalesce(sum(n_live_tup), 0) AS live, \
 192                 coalesce(sum(n_dead_tup), 0) AS dead \
 193                 FROM pg_stat_user_tables;"
 194
 195         <Result>
 196                 Type "pg_n_tup_g"
 197                 InstancePrefix "live"
 198                 ValuesFrom "live"
 199         </Result>
 200         <Result>
 201                 Type "pg_n_tup_g"
 202                 InstancePrefix "dead"
 203                 ValuesFrom "dead"
 204         </Result>
 205
 206         MinVersion 80300
 207 </Query>
 208
 209 <Query query_plans_by_table>
 210         Statement "SELECT schemaname, relname, \
 211                         coalesce(seq_scan, 0) AS seq, \
 212                         coalesce(seq_tup_read, 0) AS seq_tup_read, \
 213                         coalesce(idx_scan, 0) AS idx, \
 214                         coalesce(idx_tup_fetch, 0) AS idx_tup_fetch \
 215                 FROM pg_stat_user_tables;"
 216
 217         <Result>
 218                 Type "pg_scan"
 219                 InstancePrefix "seq"
 220                 InstancesFrom "schemaname" "relname"
 221                 ValuesFrom "seq"
 222         </Result>
 223         <Result>
 224                 Type "pg_scan"
 225                 InstancePrefix "seq_tup_read"
 226                 InstancesFrom "schemaname" "relname"
 227                 ValuesFrom "seq_tup_read"
 228         </Result>
 229         <Result>
 230                 Type "pg_scan"
 231                 InstancePrefix "idx"
 232                 InstancesFrom "schemaname" "relname"
 233                 ValuesFrom "idx"
 234         </Result>
 235         <Result>
 236                 Type "pg_scan"
 237                 InstancePrefix "idx_tup_fetch"
 238                 InstancesFrom "schemaname" "relname"
 239                 ValuesFrom "idx_tup_fetch"
 240         </Result>
 241 </Query>
 242
 243 <Query table_states_by_table>
 244         Statement "SELECT schemaname, relname, \
 245                         n_live_tup AS live, n_dead_tup AS dead \
 246                 FROM pg_stat_user_tables;"
 247
 248         <Result>
 249                 Type "pg_n_tup_g"
 250                 InstancePrefix "live"
 251                 InstancesFrom "schemaname" "relname"
 252                 ValuesFrom "live"
 253         </Result>
 254         <Result>
 255                 Type "pg_n_tup_g"
 256                 InstancePrefix "dead"
 257                 InstancesFrom "schemaname" "relname"
 258                 ValuesFrom "dead"
 259         </Result>
 260
 261         MinVersion 80300
 262 </Query>
 263
 264 <Query disk_io>
 265         Statement "SELECT coalesce(sum(heap_blks_read), 0) AS heap_read, \
 266                         coalesce(sum(heap_blks_hit), 0) AS heap_hit, \
 267                         coalesce(sum(idx_blks_read), 0) AS idx_read, \
 268                         coalesce(sum(idx_blks_hit), 0) AS idx_hit, \
 269                         coalesce(sum(toast_blks_read), 0) AS toast_read, \
 270                         coalesce(sum(toast_blks_hit), 0) AS toast_hit, \
 271                         coalesce(sum(tidx_blks_read), 0) AS tidx_read, \
 272                         coalesce(sum(tidx_blks_hit), 0) AS tidx_hit \
 273                 FROM pg_statio_user_tables;"
 274
 275         <Result>
 276                 Type "pg_blks"
 277                 InstancePrefix "heap_read"
 278                 ValuesFrom "heap_read"
 279         </Result>
 280         <Result>
 281                 Type "pg_blks"
 282                 InstancePrefix "heap_hit"
 283                 ValuesFrom "heap_hit"
 284         </Result>
 285         <Result>
 286                 Type "pg_blks"
 287                 InstancePrefix "idx_read"
 288                 ValuesFrom "idx_read"
 289         </Result>
 290         <Result>
 291                 Type "pg_blks"
 292                 InstancePrefix "idx_hit"
 293                 ValuesFrom "idx_hit"
 294         </Result>
 295         <Result>
 296                 Type "pg_blks"
 297                 InstancePrefix "toast_read"
 298                 ValuesFrom "toast_read"
 299         </Result>
 300         <Result>
 301                 Type "pg_blks"
 302                 InstancePrefix "toast_hit"
 303                 ValuesFrom "toast_hit"
 304         </Result>
 305         <Result>
 306                 Type "pg_blks"
 307                 InstancePrefix "tidx_read"
 308                 ValuesFrom "tidx_read"
 309         </Result>
 310         <Result>
 311                 Type "pg_blks"
 312                 InstancePrefix "tidx_hit"
 313                 ValuesFrom "tidx_hit"
 314         </Result>
 315 </Query>
 316
 317 <Query disk_io_by_table>
 318         Statement "SELECT schemaname, relname, \
 319                         coalesce(heap_blks_read, 0) AS heap_read, \
 320                         coalesce(heap_blks_hit, 0) AS heap_hit, \
 321                         coalesce(idx_blks_read, 0) AS idx_read, \
 322                         coalesce(idx_blks_hit, 0) AS idx_hit, \
 323                         coalesce(toast_blks_read, 0) AS toast_read, \
 324                         coalesce(toast_blks_hit, 0) AS toast_hit, \
 325                         coalesce(tidx_blks_read, 0) AS tidx_read, \
 326                         coalesce(tidx_blks_hit, 0) AS tidx_hit \
 327                 FROM pg_statio_user_tables;"
 328
 329         <Result>
 330                 Type "pg_blks"
 331                 InstancePrefix "heap_read"
 332                 InstancesFrom "schemaname" "relname"
 333                 ValuesFrom "heap_read"
 334         </Result>
 335         <Result>
 336                 Type "pg_blks"
 337                 InstancePrefix "heap_hit"
 338                 InstancesFrom "schemaname" "relname"
 339                 ValuesFrom "heap_hit"
 340         </Result>
 341         <Result>
 342                 Type "pg_blks"
 343                 InstancePrefix "idx_read"
 344                 InstancesFrom "schemaname" "relname"
 345                 ValuesFrom "idx_read"
 346         </Result>
 347         <Result>
 348                 Type "pg_blks"
 349                 InstancePrefix "idx_hit"
 350                 InstancesFrom "schemaname" "relname"
 351                 ValuesFrom "idx_hit"
 352         </Result>
 353         <Result>
 354                 Type "pg_blks"
 355                 InstancePrefix "toast_read"
 356                 InstancesFrom "schemaname" "relname"
 357                 ValuesFrom "toast_read"
 358         </Result>
 359         <Result>
 360                 Type "pg_blks"
 361                 InstancePrefix "toast_hit"
 362                 InstancesFrom "schemaname" "relname"
 363                 ValuesFrom "toast_hit"
 364         </Result>
 365         <Result>
 366                 Type "pg_blks"
 367                 InstancePrefix "tidx_read"
 368                 InstancesFrom "schemaname" "relname"
 369                 ValuesFrom "tidx_read"
 370         </Result>
 371         <Result>
 372                 Type "pg_blks"
 373                 InstancePrefix "tidx_hit"
 374                 InstancesFrom "schemaname" "relname"
 375                 ValuesFrom "tidx_hit"
 376         </Result>
 377 </Query>
 378
 379 <Query disk_usage>
 380         Statement "SELECT pg_database_size($1) AS size;"
 381
 382         Param database
 383
 384         <Result>
 385                 Type pg_db_size
 386                 ValuesFrom "size"
 387         </Result>
 388 </Query>
 389
 390 # vim: set ft=config :
 391