Tom Lane t...@sss.pgh.pa.us writes:
2. I'm unconvinced by the proposed changes to accumulate backend-local
I/O counters, too. The fact of the matter is that those counters are
left over from Berkeley days, a time when PG hackers tended to do their
performance measurements in standalone
I wrote:
I'm not enamored of saved_file either, it seems like the wrong
part of speech somehow. Maybe save_in_file?
Actually ... what is the point of letting users control the filename at
all? It seems like the only useful nondefault value is the empty string
(to suppress storing the stats).
ITAGAKI Takahiro itagaki.takah...@oss.ntt.co.jp writes:
Here is an updated version of contrib/pg_stat_statements patch.
I've committed this with significant revisions. Other than the points
already mentioned in previous messages:
* I removed the proposed changes to the behavior of the core
Thank you for many works.
Tom Lane t...@sss.pgh.pa.us wrote:
1. The proposed change to track system/user CPU time presents an
enormous cost, and no argument has been made to show that there is any
comparable benefit.
2. I'm unconvinced by the proposed changes to accumulate backend-local
On Fri, Jan 2, 2009 at 20:20, Tom Lane t...@sss.pgh.pa.us wrote:
I wrote:
* the startup/shutdown hooks will be installed in the postmaster
process, but the patch expects them to be executed in a child process.
I think nothing will happen.
OK, I figured out what is happening there: the patch
ITAGAKI Takahiro itagaki.takah...@oss.ntt.co.jp writes:
Alex Hunsaker bada...@gmail.com wrote:
How about just pg_stat_statements.track ?
I renamed the variables to:
- pg_stat_statements.limit
- pg_stat_statements.track
- pg_stat_statements.saved_file
Hmm, this has got a
I wrote:
* the startup/shutdown hooks will be installed in the postmaster
process, but the patch expects them to be executed in a child process.
I think nothing will happen.
OK, I figured out what is happening there: the patch makes it work by
means of this expedient:
diff -cprN
Alex Hunsaker bada...@gmail.com writes:
... So Im going to mark it as
ready for commmiter.
Has this patch been tested on Windows? (Or more generally, with EXEC_BACKEND?)
The reason I ask is that eyeballing the code suggests a couple of major
problems in that area:
* the startup/shutdown
On Thu, Jan 1, 2009 at 17:28, Tom Lane t...@sss.pgh.pa.us wrote:
Alex Hunsaker bada...@gmail.com writes:
... So Im going to mark it as
ready for commmiter.
Has this patch been tested on Windows? (Or more generally, with
EXEC_BACKEND?)
I was under the impression thats where Itagaki-san
I wrote:
* in an EXEC_BACKEND situation, we re-execute
process_shared_preload_libraries() when starting a fresh backend
(but not in other kinds of child processes, which is why the other
problem is a problem). This means re-executing the _PG_init function,
which will try to redefine the
On Thu, Jan 1, 2009 at 19:59, Tom Lane t...@sss.pgh.pa.us wrote:
I wrote:
* in an EXEC_BACKEND situation, we re-execute
process_shared_preload_libraries() when starting a fresh backend
(but not in other kinds of child processes, which is why the other
problem is a problem). This means
On Mon, Dec 29, 2008 at 15:46, Alex Hunsaker bada...@gmail.com wrote:
On Thu, Dec 25, 2008 at 23:04, ITAGAKI Takahiro
itagaki.takah...@oss.ntt.co.jp wrote:
Here is an updated version of contrib/pg_stat_statements patch.
Im going to do some more testing, give the typedef Chunk stuff another
On Thu, Dec 25, 2008 at 23:04, ITAGAKI Takahiro
itagaki.takah...@oss.ntt.co.jp wrote:
Here is an updated version of contrib/pg_stat_statements patch.
Im going to do some more testing, give the typedef Chunk stuff another
look (you did fix the race/not protected by a spinlock you found
earlier
Here is an updated version of contrib/pg_stat_statements patch.
Alex Hunsaker bada...@gmail.com wrote:
I think the explain_analyze_format guc is a clever way of getting
around the explain analyze verbose you proposed earlier. But I dont
see any doc updates for it.
Documentation is added.
On Mon, Dec 22, 2008 at 00:44, ITAGAKI Takahiro
itagaki.takah...@oss.ntt.co.jp wrote:
Alex Hunsaker bada...@gmail.com wrote:
A few comments:
Is there a reason you add sourceText to QueryDesc? AFAICT you can do
ActivePortal-sourceText and it will always be populated correctly.
That's for
Alex Hunsaker bada...@gmail.com wrote:
A few comments:
Is there a reason you add sourceText to QueryDesc? AFAICT you can do
ActivePortal-sourceText and it will always be populated correctly.
That's for nested statements (SQLs called in stored functions).
ActivePortal-sourceText shows text
ITAGAKI Takahiro escreveu:
- A new GUC variable 'explain_analyze_format' is added.
I'm afraid that this variable name doesn't tell what it means. What about
'explain_analyze_stats_format' or even 'explain_stats_format'?
--
Euler Taveira de Oliveira
http://www.timbira.com/
--
Sent via
Here is an updated version of pg_stat_statements.
[Changes]
- A new GUC variable 'explain_analyze_format' is added.
- Statistics counters are converted into a variable 'BufferStats'.
Vladimir Sitnikov sitnikov.vladi...@gmail.com wrote:
Can I ask my question once again?
Why don't you want to
On Sun, 7 Dec 2008, Alex Hunsaker wrote:
(dual core machine, --enable-debug, --enable-cassert build)
pgbench -c 2 -T60 -n -f test.sql
HEAD: tps = 9.674423
PATCH: tps = 9.695784
Two general suggestions here, not specific to this patch:
While it's good to do most testing with debug and
ITAGAKI Takahiro [EMAIL PROTECTED] writes:
I thought that output of new counters are too wide and it brakes
compatibility of EXPLAIN ANALYZE. On the other hand, we don't have to
think of compatibility in EXPLAIN ANALYZE VERBOSE because it is newly
added in 8.4. However, overheads should be
On the other hand, we don't have to
think of compatibility in EXPLAIN ANALYZE VERBOSE because it is newly
added in 8.4.
Uh, it exists for me in 8.2.9.
Welcome to psql 8.2.9, the PostgreSQL interactive terminal.
Type: \copyright for distribution terms
\h for help with SQL commands
Robert Haas [EMAIL PROTECTED] writes:
On the other hand, we don't have to
think of compatibility in EXPLAIN ANALYZE VERBOSE because it is newly
added in 8.4.
Uh, it exists for me in 8.2.9.
The current behaviour is newly added in 8.4. In 8.2 it meant something
completely different and quite
Robert Haas [EMAIL PROTECTED] writes:
On the other hand, we don't have to
think of compatibility in EXPLAIN ANALYZE VERBOSE because it is newly
added in 8.4.
Uh, it exists for me in 8.2.9.
EXPLAIN VERBOSE has existed at least back to 7.0, probably further.
However, we've felt free to whack
As stuff matures and becomes indispensable we could consider moving it to the
regular EXPLAIN or implement some way to specify precisely which data the user
wants. Or just say XML/table data/whatever will solve the problem for us.
I think some way to specify precisely which data the user wants
I'm not sure what the best way is though. I don't think continuing to
add keywords between EXPLAIN and the start of the query is very
scalable. Putting parentheses around the option list seems like it
might eliminate a lot of grammar headaches:
Do you think it is required to invent special
On Tue, Dec 9, 2008 at 01:20, Greg Smith [EMAIL PROTECTED] wrote:
On Sun, 7 Dec 2008, Alex Hunsaker wrote:
(dual core machine, --enable-debug, --enable-cassert build)
pgbench -c 2 -T60 -n -f test.sql
HEAD: tps = 9.674423
PATCH: tps = 9.695784
Two general suggestions here, not specific to
On Mon, Dec 8, 2008 at 23:28, ITAGAKI Takahiro
[EMAIL PROTECTED] wrote:
Alex Hunsaker [EMAIL PROTECTED] wrote:
I was assigned to review this.
Thanks for your reviewing.
I assume that the basic concepts are ok and focus of discussion is in:
- New counters in struct Instrumentation.
Yes this is one reasonable option, as is the idea of using XML or a
table and making it the client's problem. Neither are going to happen
for this release I think.
And in any case it will always be useful to have an option to print
all the available information anyways so we make as well
On Tue, Dec 9, 2008 at 8:53 PM, Robert Haas [EMAIL PROTECTED] wrote:
On Tue, Dec 9, 2008 at 12:44 PM, Greg Stark [EMAIL PROTECTED]
wrote:
Yes this is one reasonable option, as is the idea of using XML or a table
and making it the client's problem. Neither are going to happen for this
On Tue, Dec 9, 2008 at 12:44 PM, Greg Stark [EMAIL PROTECTED] wrote:
Yes this is one reasonable option, as is the idea of using XML or a table
and making it the client's problem. Neither are going to happen for this
release I think.
Agreed.
And in any case it will always be useful to have an
On Sun, Dec 7, 2008 at 19:13, Alex Hunsaker [EMAIL PROTECTED] wrote:
On Tue, Dec 2, 2008 at 02:35, ITAGAKI Takahiro
[EMAIL PROTECTED] wrote:
Here is an update version of contrib/pg_stat_statements.
Hello again!
I was assigned to review this.
... Some other things I accidentally left out.
Tom Lane [EMAIL PROTECTED] wrote:
Please split this into two separate patches that can be separately
evaluated.
Sure. I want to disucuss only where to add counters of buffer usage
and cpu usage, or they should not be added. However, it seems to
affect future of EXPLAIN ANALYZE, so we might
Alex Hunsaker [EMAIL PROTECTED] wrote:
#define GUCNAME(name) (statistics. name)
Why statistics?
Would not something like stat_statements make more sense? Statistics
seems fairly arbitrary...
Not to use duplicated statements words;
variable names contains statements already.
-
Alex Hunsaker [EMAIL PROTECTED] wrote:
I was assigned to review this.
Thanks for your reviewing.
I assume that the basic concepts are ok and focus of discussion is in:
- New counters in struct Instrumentation.
(buffer usage and CPU usage)
- Should EXPLAIN ANALYZE show those counters.
Vladimir Sitnikov [EMAIL PROTECTED] wrote:
2. EXPLAIN ANALYZE VERBOSE shows buffer statistics in 'actual' section.
I do not get the point of VERBOSE.
As far as I understand, explain analyze (without verbose) will anyway add
overhead for calculation of gets/hits/cpu. Why discard that
2. EXPLAIN ANALYZE VERBOSE shows buffer statistics in 'actual' section.
I do not get the point of VERBOSE.
As far as I understand, explain analyze (without verbose) will anyway add
overhead for calculation of gets/hits/cpu. Why discard that information
in
non verbose mode? Just to
On Tue, Dec 2, 2008 at 02:35, ITAGAKI Takahiro
[EMAIL PROTECTED] wrote:
Here is an update version of contrib/pg_stat_statements.
Hello again!
I was assigned to review this.
Submission review:
Is the patch in standard form? Yes
Does it apply cleanly to current HEAD? Yes (with fuzz)
Does it
On Fri, 5 Dec 2008, Vladimir Sitnikov wrote:
Oracle's approach is to have the explain command stuff the results into a
table. That has advantages for tools, especially if you want to be able to
look at plans generated by other sessions.
I do not believe that workflow makes sense. I have never
The main benefit is that you can track how EXPLAIN plans change over time.
It is not required to output plan *into* some table to be able track it over
time. If EXPLAIN returns a table, it is up to you to perform insert into
history select * from explain(...).
Workflow that does not make sense
2. EXPLAIN ANALYZE VERBOSE shows buffer statistics in 'actual' section.
I do not get the point of VERBOSE.
As far as I understand, explain analyze (without verbose) will anyway add
overhead for calculation of gets/hits/cpu. Why discard that information in
non verbose mode? Just to make the
Vladimir Sitnikov [EMAIL PROTECTED] writes:
I wish there was a way to get the results of explain into some table. I wish
it was the default output format. That would make life of pgAdmin easier,
and improve readability even in psql. Do not you think there is something
wrong with having
Vladimir Sitnikov [EMAIL PROTECTED] writes:
I wish there was a way to get the results of explain into some table. I
wish
it was the default output format. That would make life of pgAdmin
easier,
and improve readability even in psql. Do not you think there is something
wrong with
Here is an update version of contrib/pg_stat_statements.
New modifications from the last version are:
1. New counters in struct Instrumentation.
2. EXPLAIN ANALYZE VERBOSE shows buffer statistics in 'actual' section.
3. Buffer statistics counsters are not reset to zero anymore.
1. New
Martin Pihlak [EMAIL PROTECTED] writes:
ITAGAKI Takahiro wrote:
But is the idea of extending QueryDesc generally acceptable? Is it OK
to make a copy of the query string?
The only thing I'm worried about is that QueryDesc lives longer than
its queryText. Can I assume it never occurs?
I
Tom Lane [EMAIL PROTECTED] wrote:
I'm trying to figure out what is the status of this patch? I'm not sure
if there's any point in applying it, when contrib/pg_stat_statements
hasn't been updated to make use of it.
Here is an updated patch to use QueryDesc.queryText and supports nested
Hello,
I have two concerns regarding the patch:
A) I am not sure if it is good to have a single contention point (pgss-lock
= LWLockAssign()). I guess that would impact scalability, especially on a
multi-cpu systems. I guess the real solution will come when PostgreSQL have
a pool for sql
Martin Pihlak [EMAIL PROTECTED] wrote:
Attached is a patch that adds sourceText to QueryDesc.
It worked fine surprisingly :) . Internal and C functions don't use
executor, so we can ignore trivial function calls (ex. operators).
Furthermore, it is ok if QueryDesc doesn't have queryText
because
ITAGAKI Takahiro wrote:
But is the idea of extending QueryDesc generally acceptable? Is it OK
to make a copy of the query string?
The only thing I'm worried about is that QueryDesc lives longer than
its queryText. Can I assume it never occurs?
I just finished validating this -- it seems
ITAGAKI Takahiro wrote:
It might be possbile to add a queryText field into QueryDesc
and all of the codes using QueryDesc initialize the field with
their own query texts, but it requires modifications in many
different modules and copying query text might be needed.
I don't want to do it only
Martin Pihlak [EMAIL PROTECTED] wrote:
ITAGAKI Takahiro wrote:
I'd like to submit pg_stat_statements contrib module
Nice work! There is one feature I'd like to request -- we need to be able
to also track nested statements. This would greatly simplify diagnosing
performance problems in
Here is an updated version of contrib/pg_stat_statements module.
I wrote:
A shutdown hook is probably not needed because I can use proc_exit()
or shmem_exit() for the purpose.
I added shutdown hook eventually because MyProc has been reset already
in on_shmem_exit(). A callback that resets
On Mon, 2008-10-27 at 17:00 +0900, ITAGAKI Takahiro wrote:
Martin Pihlak [EMAIL PROTECTED] wrote:
ITAGAKI Takahiro wrote:
I'd like to submit pg_stat_statements contrib module
Nice work! There is one feature I'd like to request -- we need to be able
to also track nested statements.
Hannu Krosing [EMAIL PROTECTED] wrote:
Martin Pihlak [EMAIL PROTECTED] wrote:
we need to be able to also track nested statements.
I tried your request, but found it's hard to determine query text
where the executing plan comes.
Are you sure you need query _text_ ?
Why not just
On Oct 17, 2008, at 4:30 AM, Vladimir Sitnikov wrote:
Decibel! [EMAIL PROTECTED] wrote:
I had tried to use a normal table for store stats information,
but several acrobatic hacks are needed to keep performance.
I guess it is not really required to synchronize the stats into
some physical
ITAGAKI Takahiro wrote:
Hello,
I'd like to submit pg_stat_statements contrib module, that counts up
incoming statements in shared memory and summarizes the result as a view.
It is just a statements-version of pg_stat_user_functions.
Sounds very good, but why contrib and not along with the
Magnus Hagander [EMAIL PROTECTED] wrote:
I'd like to submit pg_stat_statements contrib module
Sounds very good, but why contrib and not along with the rest of the
stats stuff in the main backend (with an on/off switch if the overhead
is high)?
That's because it could be done as a contrib
ITAGAKI Takahiro wrote:
Magnus Hagander [EMAIL PROTECTED] wrote:
I'd like to submit pg_stat_statements contrib module
Sounds very good, but why contrib and not along with the rest of the
stats stuff in the main backend (with an on/off switch if the overhead
is high)?
That's because it
Magnus Hagander wrote:
ITAGAKI Takahiro wrote:
Magnus Hagander [EMAIL PROTECTED] wrote:
I'm not sure what should be in the main and what should not.
Why is pg_freespacemap still in contrib?
I don't know, why is it? :-)
I guess that was a joke, given the smiley, but I'll bite:
1.
ITAGAKI Takahiro wrote:
I'd like to submit pg_stat_statements contrib module, that counts up
incoming statements in shared memory and summarizes the result as a view.
It is just a statements-version of pg_stat_user_functions.
Nice work! There is one feature I'd like to request -- we need to
Decibel! [EMAIL PROTECTED] wrote:
I had tried to use a normal table for store stats information,
but several acrobatic hacks are needed to keep performance.
I guess it is not really required to synchronize the stats into some
physical table immediately.
I would suggest keeping all the data
On Oct 11, 2008, at 4:05 AM, ITAGAKI Takahiro wrote:
I'd like to submit pg_stat_statements contrib module, that counts up
incoming statements in shared memory and summarizes the result as a
view.
It is just a statements-version of pg_stat_user_functions.
Awesome!
I attach WIP version of
Decibel! [EMAIL PROTECTED] wrote:
How hard would it be to dump this information to a table, or some
other more-permanent form of storage? Of course there would need to
be some means of cleaning that up over time, but if it's a simple
table you can DELETE from, we could put the burden
ITAGAKI Takahiro wrote:
We need to avoid using normal UPDATEs to increment counters
because it requires row-level exclusive locks and kills concurrency.
My idea was modifying heap tuples directly in pages:
buffer = ReadBuffer(stats_rel, blknum);
LockBuffer(buffer,
Alvaro Herrera [EMAIL PROTECTED] wrote:
ITAGAKI Takahiro wrote:
My idea was modifying heap tuples directly in pages:
heap_inplace_update?
It writes WAL. I don't think we need WAL here,
and it is enough to write out pages every checkpoints.
Regards,
---
ITAGAKI Takahiro
NTT Open Source
Hello,
Postgres has configurations to log statements into server logs, but there
are few convenience ways to summarize the logs as a view. Some external
projects like pgFouine or PQA have the functionality, but they consume
much CPU resources and cannot summarize in real-time -- summarize-time
is
65 matches
Mail list logo
