pgbadger | A fast PostgreSQL Log Analyzer

I am trying to parse the log file generated by my RDS instance using pgBadger, so far with no results.

The log_line_prefix is set to %t:%r:%u@%d:[%p]:

A sample line in the log file looks like :

I'm conducting an audit of how much existing database tables are used and by what users as part of a database cleanup effort. Using the log files seems like a natural way to get at this data. We have pgBadger running for performance reports but a usage report as I've described doesn't exist. Does anyone know of a tool (pgBadger or otherwise) that will extract table and user information from the logs so that I can calculate summary stats on it? I'd like to leverage existing tools rather than rolling my own log parser.

We can do a different kind of analysis like slowest/time consuming/Most frequent queries in MongoDB. Is there any tool which will do that for me like PgBadger.

We're just starting to investigate using Postgres as the backend for our system which will be used with an OLTP-type workload: > 95% (possibly >99%) of the transactions will be inserting 1 row into 4 separate tables, or updating 1 row. Our test machine is running 9.5.6 (using out-of-the-box config options) on a modest cloud-hosted Windows VM with a 4-core i7 processor, with a conventional 7200 RPM disk. This is much, much slower than our targeted production hardware, but useful right now for finding bottlenecks in our basic design.

Our initial tests have been pretty discouraging. Although the insert statements themselves run fairly quickly (combined execution time is around 2ms), the overall transaction time is around 40ms, due to the commit statement taking 38 ms. Furthermore, during a simple 3-minute load test (5000 transactions), we're only seeing about 30 transactions per second, with pgbadger reporting 3 minutes spent in "commit" (38 ms avg.), and the next highest statements being the inserts at 10 (2ms) and 3 (0.6 ms) respectively. During this test, the cpu on the postgres instance is pegged at 100%

The fact that the time spent in commit is equal to the elapsed time of the test tells me the that not only is commit serialized (unsurprising, given the relatively slow disk on this system), but that it is consuming a cpu during that duration, which surprises me. I would have assumed before the fact that if we were i/o bound, we would be seeing very low cpu usage, not high usage.

In doing a bit of reading, it would appear that using Asynchronous Commits would solve a lot of these issues, but with the caveat of data loss on crashes/immediate shutdown. Similarly, grouping transactions together into a single begin/commit block, or using multi-row insert syntax improves throughput as well.

All of these options are possible for us to employ, but in a traditional OLTP application, none of them would be (you need to have fast, atomic, synchronous transactions). 35 transactions per second on a 4-core box would have unacceptable 20 years ago on other RDBMs running on much slower hardware than this test machine, which makes me think that we're doing this wrong, as I'm sure Postgres is capable of handling much higher workloads.

I've looked around but can't find some common-sense config options that would serve as starting points for tuning a Postgres instance. Any suggestions?