Java Solutions: monitor

Wednesday, May 13, 2020

PostgreSQL: Monitoring the status of your queries currently running in the database

The pg_stat_activity view has one row per server process, showing information related to the current activity of that process.

pg_stat_activity view

Column	Type	Description
`datid`	`oid`	OID of the database this backend is connected to
`datname`	`name`	Name of the database this backend is connected to
`pid`	`integer`	Process ID of this backend
`usesysid`	`oid`	OID of the user logged into this backend
`usename`	`name`	Name of the user logged into this backend
`application_name`	`text`	Name of the application that is connected to this backend
`client_addr`	`inet`	IP address of the client connected to this backend. If this field is null, it indicates either that the client is connected via a Unix socket on the server machine or that this is an internal process such as autovacuum.
`client_hostname`	`text`	Host name of the connected client, as reported by a reverse DNS lookup of `client_addr`. This field will only be non-null for IP connections, and only when log_hostname is enabled.
`client_port`	`integer`	TCP port number that the client is using for communication with this backend, or `-1` if a Unix socket is used
`backend_start`	`timestamp with time zone`	Time when this process was started, i.e., when the client connected to the server
`xact_start`	`timestamp with time zone`	Time when this process' current transaction was started, or null if no transaction is active. If the current query is the first of its transaction, this column is equal to the `query_start` column.
`query_start`	`timestamp with time zone`	Time when the currently active query was started, or if `state` is not `active`, when the last query was started
`state_change`	`timestamp with time zone`	Time when the `state` was last changed
`waiting`	`boolean`	True if this backend is currently waiting on a lock
`state`	`text`	Current overall state of this backend. Possible values are: `active`: The backend is executing a query. `idle`: The backend is waiting for a new client command. `idle in transaction`: The backend is in a transaction, but is not currently executing a query. `idle in transaction (aborted)`: This state is similar to `idle in transaction`, except one of the statements in the transaction caused an error. `fastpath function call`: The backend is executing a fast-path function. `disabled`: This state is reported if track_activities is disabled in this backend.
`query`	`text`	Text of this backend's most recent query. If `state` is `active` this field shows the currently executing query. In all other states, it shows the last query that was executed.

For example, you can use the following query to see the state, text, and running period of your statement.

SELECT pid, usename, query, state, age(clock_timestamp(), query_start)
FROM pg_stat_activity
WHERE usename = '<your database login>';

If you want to see if your query is holding or waiting on any locks, you can run the query below.

SELECT a.pid, a.state, L.mode, L.locktype, L.granted
FROM pg_stat_activity a
INNER JOIN pg_locks L on a.pid = L.pid
WHERE a.usename = '<your database login>'
order by L.granted desc;

References:

1. Monitoring Database Activity

2.Useful PostgreSQL Queries For Monitoring Purpose

-----------------------------------------------------------------------------------------------------------------

Watch the blessing and loving online channel: SupremeMasterTV live

If you have ever asked yourself these questions, this is the book for you. What is the meaning of life? Why do people suffer? What is in control of my life? Why is life the way it is? How can I stop suffering and be happy? How can I have a successful life? How can I have a life I like to have? How can I be the person I like to be? How can I be wiser and smarter? How can I have good and harmonious relations with others? Why do people meditate to achieve enlightenment? What is the true meaning of spiritual practice? Why all beings are one? Read the book for free here.

Thursday, February 7, 2019

Thread concurrency: wait() and notify() / IllegalMonitorStateException

When a thread enters a synchronized block, it acquired the lock and becomes the owner of the lock. For example, if you have multiple threads reading from a queue, you do synchronized (queue) and the queue becomes the lock. Once thread T1 enters this synchronized block, it owns the queue lock.

Now, the queue is empty and there is nothing to read, so you want thread T1 to wait until the queue has something. The odd thing is that you cannot call T1.wait() or Thread.currentThread.wait() which will throw an IllegalMonitorStateException. Instead you call queue.wait(), which puts your thread into a Waiting state and makes it release the queue lock.

synchronized (queue) {

try {

while(queue.isEmpty()){

queue.wait();

}

} catch (InterruptedException e) {

System.out.println("Reader is waking up!");

}

Object value = queue.poll();

}

Similarly, you have to call queue.notify() to change a Waiting thread into a Runnable one.

synchronized (queue) {

queue.add(obj);

queue.notify()

}

Rule of Thumb: Never use the wait() method together with nested synchronized blocks! A wait() call only releases one lock, and the thread enters the Waiting state holding the other lock, which is prone to deadlock.

When you use synchronized method, the object "this" is the lock. You may call this.wait() or just wait() in the synchronized method to make the thread go to the Waiting state and release the lock. And this.notify() will make a Waiting thread Runnable.

-----------------------------------------------------------------------------------------------------------------

Watch the blessing and loving online channel: SupremeMasterTV live