Re: pg_stat_statements

I wrote:
> I will try to measure overheads of logging in some implementation:
> 1. Log statements and dump them into server logs.
> 2. Log statements and filter them before to be written.
> 3. Store statements in shared memory.
> I know 1 is slow, but I don't know what part of it is really slow;

I tested overheads of SQL logging with pgbench.
$ pgbench -s10 -c10 -t10000 -n -S -M prepared

logging type | tps | %
-----------------------+-------+--------
0. no logging | 10651 | 100.0%
1. log to pg_log/* | 6535 | 61.4%
2. log to /dev/null | 8347 | 78.4%
3. store in memory | 10280 | 96.5%

As expected, 1 is 40% slower than no logging settings. Also, filtering
logs before written into files seems not to be a perfect solution.
Redirecting logs to /dev/null is the *fastest* filter, but there was
30% of overhead. On the other hand, 3 has only 3.5% of overhead.

I think storing SQLs in server memory is worth trying even if there
are some troubles, for example, memory management. We can use either
hooks and dtrace for the purpose, but I'm working hook-method because
of portability.

I'll send a core patch and an extension module to -patches. I hope only
the patch is to be applied in the core. The extension module would be
better to be developed separately from the core.

Regards,
---
ITAGAKI Takahiro
NTT Open Source Software Center