I'm reading about PostgreSQL monitoring tool. I get a lot of information like database size etc. but can't find info about CPU and memory usage.
Are these informations provided by PostgreSQL server or do I have to get it by my native client application (for example, find PID of PostgreSQL and call OS API function).
Postgresql – How to get CPU usage of PostgreSQL server
monitoringpostgresql
Related Question
- Postgresql – How should I tune Postgresql for 20 GB of RAM
- SQL Server Agent Monitoring – How to Monitor SQLAgent in SQL Server 2008 R2
- PostgreSQL – Procedural Languages Overhead (plpython, plsql, pllua)
- Postgresql – Monitoring Amazon RDS with pgtop
- Mysql – How to get CPU usage in MySQL per user
- PostgreSQL LWLock: lock_manager issue
- PostgreSQL – Managing Memory Usage with 9000 Tables
Best Answer
That's not really a PostgreSQL monitoring tool, that's PostgreSQL's internal facilities for diagnostics, monitoring and database statistics.
For host monitoring you want a tool like Icinga (modernized Nagios) or Zabbix. When monitoring PostgreSQL the
check_postgres
tool is likely to be of interest.If you're interested in getting the CPU use of a particular PostgreSQL backend process from your own custom programs, you must
SELECT pg_backend_pid()
or get the pid of the task of interest frompg_stat_activity
then use OS-level tools and APIs to collect the desired information.If you desired, you could write a PostgreSQL C extension function to collect the information and return it as a row or rowset over the PostgreSQL protocol, so you could
SELECT my_system_stats()
. See:Essentially, you write a library in C using the PostgreSQL APIs, compile it using the
pgxs
make helper and have the server dynamically load the resulting shared object. Then you tell it what C functions are callable from SQL usingCREATE FUNCTION
. You can then call them like any other function. C extensions can call OS-level APIs or do pretty much whatever they want, though it is generally very unwise for them to create threads, write directly to PostgreSQL files/filehandles, or read/write PostgreSQL sockets.