Re: Who mades the inserts?

Hi DD

By default Postgresql does not collect this level of detail information to
tell you which database has a high load at X point in time.

You can infer which database has this high load without increasing logging

Select * from pg_stat_database this dumps total inserts, update, scans
etc...

To track which database has the highest load dump this to a table
select * into table compare_db_stat from pg_stat_database

after another high load event occurs compare this table to the current
pg_stat_database results.

To identify which tables are being hit use pg_stat_all_tables

select * from pg_stat_all_tables order by n_tup_ins desc, n_tup_upd desc

then dump the results to a table with this command
Select * into table Stats_Compare from pg_stat_all_tables

after the high load has occurred compare the two tables to see specifically
what values have changed

The draw back with this approach pg_stats_all_tables only shows the
results for the current database, each database must be done
independently

The other option is alter the log settings to record the SQL statements,
wait for event to happen, then review the logs.
The big draw with this approach is the log files get BIG FAST..

Additional resources to review.
https://www.postgresql.org/docs/9.2/monitoring-stats.html
https://www.postgresql.org/docs/10/catalog-pg-database.html
https://wiki.postgresql.org/wiki/Disk_Usage
https://wiki.postgresql.org/wiki/Monitoring

On Mon, Mar 9, 2020 at 8:53 AM Durumdara <durumdara(at)gmail(dot)com> wrote:

> Dear Members!
>
> We have more than 200 databases in a server.
> The PGAdmin's dashboard shows me 4500 inserts periodically.
>
> I want to know which database(s) causes this.
>
> Do you know any query which can show me the inserts per databases?
>
> And I don't know it works as TPS query? So I need to make differents
> between measured values in two time point?
>
> Like here:
>
> https://dba.stackexchange.com/questions/35940/how-many-queries-per-second-is-my-postgres-executing
>
> select * from (
> SELECT current_timestamp, datname, sum(xact_commit+xact_rollback) db FROM
> pg_stat_database
> group by current_timestamp, datname
> ) t order by db desc
>
> Thank you for any advance!
>
> Best regards
> DD
>