Quick Reference.wiki

Table of Contents Quick Reference Mysql Change Master Table size Connections By Host By User Postgresql Most active tables Table size Replication lag Locks Admin Info Settings causing a restart

Quick Reference

Mysql

Change Master

  change master to
  MASTER_HOST='',
  MASTER_PORT=3306,
  MASTER_USER='',
  MASTER_PASSWORD='',
  MASTER_LOG_FILE='',
  MASTER_LOG_POS=
  ;

Table size

  SELECT concat(table_schema, ".", table_name) AS "Table",
  round(((data_length + index_length) / 1024 / 1024), 2) "Size in MB"
  FROM information_schema.TABLES

Connections

By Host

  select substring_index(host,':', 1) as host_name, count(1) as connections from information_schema.processlist group by host_name;

By User

  select user as user, count(1) as connections from information_schema.processlist group by user;

Postgresql

Most active tables

  select relid::REGCLASS as table_name,
  n_tup_ins as inserts,
  n_tup_upd + n_tup_hot_upd as updates,
  n_tup_del as deletes,
  (n_tup_ins + n_tup_upd + n_tup_hot_upd + n_tup_del) as total
  from pg_stat_user_tables
  order by (n_tup_ins + n_tup_upd + n_tup_hot_upd + n_tup_del) desc limit 20;

Table size

  SELECT d.datname AS Name,  pg_catalog.pg_get_userbyid(d.datdba) AS Owner,
      CASE WHEN pg_catalog.has_database_privilege(d.datname, 'CONNECT')
          THEN pg_catalog.pg_size_pretty(pg_catalog.pg_database_size(d.datname))
          ELSE 'No Access'
      END AS Size
  FROM pg_catalog.pg_database d
      ORDER BY
      CASE WHEN pg_catalog.has_database_privilege(d.datname, 'CONNECT')
          THEN pg_catalog.pg_database_size(d.datname)
          ELSE NULL
      END DESC -- nulls first
      LIMIT 20

Replication lag

  SELECT
      client_hostname,
      client_addr,
      state,
      sent_offset - (
          replay_offset - (sent_xlog - replay_xlog) * 255 * 16 ^ 6 ) AS byte_lag
  FROM (
      SELECT
          client_addr, client_hostname, state,
          ('x' || lpad(split_part(sent_location,   '/', 1), 8, '0'))::bit(32)::bigint AS sent_xlog,
          ('x' || lpad(split_part(replay_location, '/', 1), 8, '0'))::bit(32)::bigint AS replay_xlog,
          ('x' || lpad(split_part(sent_location,   '/', 2), 8, '0'))::bit(32)::bigint AS sent_offset,
          ('x' || lpad(split_part(replay_location, '/', 2), 8, '0'))::bit(32)::bigint AS replay_offset
      FROM pg_stat_replication
  ) AS s;

Locks

  SELECT mode, count(mode) AS count FROM pg_locks
      GROUP BY mode ORDER BY mode;

Admin Info

Settings causing a restart

  select name,
  case context
  when 'postmaster' then 'REQUIRES RESTART'
  when 'sighup' then 'Reload config'
  when 'backend' then 'Reload config'
  when 'superuser' then 'Reload config / superuser set'
  when 'user' then 'Reload config / user set'
  end as when_changed
  from pg_settings
  where context != 'internal'
  order by when_changed;