Created
January 21, 2015 22:14
-
-
Save smathieu/532b10ebf2a8c64695c6 to your computer and use it in GitHub Desktop.
Postgres Lock Monitoring
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| class Jobs::Cron::Metrics::Postgres < QueueClassicPlus::Base | |
| @queue = :cron | |
| lock! | |
| def self.perform | |
| sql = <<-SQL | |
| SELECT date_part('epoch', now() - query_start) AS connection_age | |
| FROM pg_catalog.pg_stat_activity | |
| WHERE datname = '#{database}' | |
| ORDER BY connection_age DESC NULLS LAST | |
| LIMIT 1 | |
| SQL | |
| res = execute(sql) | |
| duration = res['connection_age'].to_f | |
| Librato.measure "postgres.connections.oldest", duration | |
| { | |
| waiting: "AND waiting", | |
| not_waiting: "AND NOT waiting", | |
| all: "", | |
| }.each do |key, condition| | |
| sql = <<-SQL | |
| SELECT COUNT(1) AS waiting_count | |
| FROM pg_catalog.pg_stat_activity | |
| WHERE datname = '#{database}' #{condition} | |
| SQL | |
| res = execute(sql) | |
| Librato.measure "postgres.connections.waiting_count.#{key}", res["waiting_count"].to_f | |
| end | |
| sql = %Q( | |
| SELECT | |
| pg_stat_activity.pid, | |
| pg_class.relname, | |
| pg_locks.transactionid, | |
| pg_locks.granted, | |
| substr(pg_stat_activity.query,1,30) AS query_snippet, | |
| EXTRACT(EPOCH FROM now() - pg_stat_activity.query_start) AS "age" | |
| FROM pg_stat_activity,pg_locks | |
| LEFT OUTER JOIN pg_class | |
| ON (pg_locks.relation = pg_class.oid) | |
| WHERE pg_stat_activity.query <> '<insufficient privilege>' | |
| AND pg_locks.pid = pg_stat_activity.pid | |
| AND pg_locks.mode = 'ExclusiveLock' | |
| AND pg_stat_activity.pid <> pg_backend_pid() | |
| ORDER BY age DESC | |
| LIMIT 1 | |
| ) | |
| res = execute(sql) | |
| Librato.measure "postgres.locks.all.oldest", res["age"].to_f | |
| sql = %Q( | |
| SELECT bl.pid AS blocked_pid, | |
| ka.query AS blocking_statement, | |
| now() - ka.query_start AS blocking_duration, | |
| kl.pid AS blocking_pid, | |
| a.query AS blocked_statement, | |
| EXTRACT(EPOCH FROM now() - a.query_start) AS blocked_duration | |
| FROM pg_catalog.pg_locks bl | |
| JOIN pg_catalog.pg_stat_activity a | |
| ON bl.pid = a.pid | |
| JOIN pg_catalog.pg_locks kl | |
| JOIN pg_catalog.pg_stat_activity ka | |
| ON kl.pid = ka.pid | |
| ON bl.transactionid = kl.transactionid AND bl.pid != kl.pid | |
| WHERE NOT bl.granted | |
| ORDER BY blocked_duration DESC | |
| LIMIT 1 | |
| ) | |
| res = execute(sql) | |
| Librato.measure "postgres.locks.blocking.oldest", res["blocked_duration"].to_f | |
| # Instument locks per table | |
| sql = %{ | |
| SELECT COUNT(pg_class.relname) AS count, pg_class.relname AS relname | |
| FROM pg_locks | |
| LEFT JOIN pg_class ON (pg_locks.relation = pg_class.oid) | |
| WHERE pg_locks.locktype = 'relation' | |
| AND pg_locks.relation IS NOT NULL | |
| AND pg_class.relname NOT IN ('pg_locks', 'pg_class') | |
| GROUP BY pg_class.relname | |
| } | |
| res = ActiveRecord::Base.connection.select_all(sql) | |
| res.each do |row| | |
| Librato.measure "postgres.locks_by_relation", row["count"].to_i, | |
| source: row["relname"], | |
| sporadic: true | |
| end | |
| # Log blocking queries | |
| sql = %Q( | |
| SELECT bl.pid AS blocked_pid, | |
| ka.query AS blocking_statement, | |
| now() - ka.query_start AS blocking_duration, | |
| kl.pid AS blocking_pid, | |
| a.query AS blocked_statement, | |
| EXTRACT(EPOCH FROM now() - a.query_start) AS blocked_duration | |
| FROM pg_catalog.pg_locks bl | |
| JOIN pg_catalog.pg_stat_activity a ON bl.pid = a.pid | |
| JOIN pg_catalog.pg_locks kl | |
| JOIN pg_catalog.pg_stat_activity ka ON kl.pid = ka.pid | |
| ON bl.transactionid = kl.transactionid AND bl.pid != kl.pid | |
| WHERE NOT bl.granted | |
| ) | |
| res = ActiveRecord::Base.connection.select_all(sql) | |
| res.each do |row| | |
| Rails.logger.info(%{Blocked Statement `%s` has been blocked by `%s` for %0.2fs} % | |
| [row["blocked_statement"], | |
| row["blocking_statement"], | |
| row["blocked_duration"]]) | |
| end | |
| # Log locked queries | |
| sql = %Q( | |
| SELECT | |
| pg_stat_activity.pid, | |
| pg_stat_activity.query AS query, | |
| MAX(EXTRACT(EPOCH FROM age(now(),pg_stat_activity.query_start))) AS "age" | |
| FROM pg_stat_activity, pg_locks left | |
| OUTER JOIN pg_class ON (pg_locks.relation = pg_class.oid) | |
| WHERE pg_stat_activity.query <> '<insufficient privilege>' | |
| AND pg_locks.pid = pg_stat_activity.pid | |
| AND pg_locks.mode = 'ExclusiveLock' | |
| AND pg_stat_activity.pid <> pg_backend_pid() | |
| AND age(now(), pg_stat_activity.query_start) > interval '1 minute' | |
| GROUP BY pg_stat_activity.pid, pg_stat_activity.query | |
| ) | |
| res = ActiveRecord::Base.connection.select_all(sql) | |
| res.each do |row| | |
| Rails.logger.info(%{Locked Statement `%s` has been locked for %0.2fs} % | |
| [row["query"], | |
| row["age"]]) | |
| end | |
| end | |
| private | |
| def self.execute(sql) | |
| res = ActiveRecord::Base.connection.execute(sql) | |
| res.first ? res.first.to_h : {} | |
| end | |
| def self.database | |
| @database ||= ActiveRecord::Base.connection.current_database | |
| end | |
| end | |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment