Обсуждение: Question about PostgreSQL logging configuration
Hi, I have some interesting question about PostgreSQL logging configuration. I want log all 'mod' statements with their execution times and all statements longer than 10ms (also with their execution times). Now I have 3 setting to play with: log_min_duration_statement log_duration log_statement But it seems that I could not have configuration which doing what I looking for. Because: log_min_duration_statement=10ms log_duration = off log_statement = mod will log almost everything what I need but db did not log execution times for mod statements in that case. Alternatively if I try: log_min_duration_statement=10ms log_duration = on log_statement = mod db will log execution times of every query (not only mod and ones longer 10ms) thus producing lot noise and exceptionally large log files. I could not keep log_min_duration_statement=0 on database with 20000+ requests per second. Is there any good solution for that task? -- Maxim Boguk Senior Postgresql DBA http://www.postgresql-consulting.ru/ Phone RU: +7 910 405 4718 Phone AU: +61 45 218 5678 Skype: maxim.boguk Jabber: maxim.boguk@gmail.com МойКруг: http://mboguk.moikrug.ru/ "People problems are solved with people. If people cannot solve the problem, try technology. People will then wish they'd listened at the first stage."
-----BEGIN PGP SIGNED MESSAGE----- Hash: RIPEMD160 > I want log all 'mod' statements with their execution times and all > statements longer than 10ms (also with their execution times). You cannot combine things as you want. However, it seems a fairly minor loss - why would you care about how fast sub-10ms mods ran? - -- Greg Sabino Mullane greg@turnstep.com End Point Corporation http://www.endpoint.com/ PGP Key: 0x14964AC8 201211292253 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -----BEGIN PGP SIGNATURE----- iEYEAREDAAYFAlC4LdsACgkQvJuQZxSWSsjiuwCdEtQ7XaWK1hswHtEepp34Ocbm exMAoM88g++nBZjQl5Vh7KA+Lt8uCWrQ =i11P -----END PGP SIGNATURE-----
>> I want log all 'mod' statements with their execution times and all >> statements longer than 10ms (also with their execution times). > > You cannot combine things as you want. However, it seems a fairly > minor loss - why would you care about how fast sub-10ms mods > ran? Trouble if I try that way, than database log for mod statements over 10ms duration will be written in wrong style: 2012-11-30 08:06:28 MSK [vxid:229/5138506 txid:1345713884] [INSERT] LOG: duration: 10.158 ms 2012-11-30 08:06:28 MSK [vxid:325/5420118 txid:0] [UPDATE] LOG: execute S_5: update applicant_adv_subscription ... instead of more usual way: 2012-11-30 08:08:21 MSK [vxid:307/5334684 txid:0] [SELECT] LOG: duration: 16.085 ms execute S_42: select ... So at end I getting logs which are incompatible with pgFouine (and with my custom log analyzer as well). Is there any serious reason why log_duration = on uses so strange output style? I could not think any reasonable case for use log_duration = on without log_statements = all. And log_duration = on with log_statements = all produce results which are equivalent to log_min_duration_statement=0, but log_min_duration_statement=0 produce logs which are much more easy to parse and read in general. Is there anyone who consciously used log_duration = on without log_statements = all ? And if yes - what for? I might be very wrong, but log_duration seems close to useless knob in reality. Kind Regards, Maksym
-----BEGIN PGP SIGNED MESSAGE----- Hash: RIPEMD160 > So at end I getting logs which are incompatible with pgFouine (and > with my custom log analyzer as well). Ah, well, that's hardly Postgres' fault is it? :) You might want to look into other log software - there are at least two others I know of off the top of my head (PGSI, pgbadger) that might be better at parsing. Or submit a bug to the pgfouine folks. > Is there anyone who consciously used log_duration = on without > log_statements = all ? > And if yes - what for? > > I might be very wrong, but log_duration seems close to useless knob in reality. - From the docs on log_duration: "The difference between setting this option and setting log_min_duration_statement to zero is that exceeding log_min_duration_statement forces the text of the query to be logged, but this option doesn't. Thus, if log_duration is on and log_min_duration_statement has a positive value, all durations are logged but the query text is included only for statements exceeding the threshold. This behavior can be useful for gathering statistics in high-load installations." - -- Greg Sabino Mullane greg@turnstep.com End Point Corporation http://www.endpoint.com/ PGP Key: 0x14964AC8 201211292327 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -----BEGIN PGP SIGNATURE----- iEYEAREDAAYFAlC4Na4ACgkQvJuQZxSWSshi8QCfcUzZMvs9ZNDK6faLrY+3Tj2R qSAAn0GJNnKhIvpX25Il5z+PLbLk7VYo =A3W/ -----END PGP SIGNATURE-----