Re: [GENERAL] Needs Suggestion
On 05/04/2011 08:39 AM, Andreas Kretschmer wrote: > SUBHAM ROY wrote: > >> My output of explain (analyze,buffers) is something like this: >> >> shared hit=3796624 read=46038 >> >> So what is meant by read here? Does it indicates number of disk reads? >> >> Does shared hit takes into account only the hit in the pg_buffercache of the >> postgres or it also takes into account the linux buffers that postgres uses? > > shared hit: read from shared mem > shared read: read from disk into shared mem > To be more precise: shared hit number of blocks directly read from PostgreSQL shared memory read number of blocks PostgreSQL asked the OS to give him (then the OS may read the blocks from its own shared memory or may ask the disk subsystem to give him (and then the disk subsystem may read it from its own shared memory (SAN cache for example) or read it from the disk)) To put it more simply: shared hit: number of blocks directly read from PostgreSQL shared memory read: number of blocks read outside of PostgreSQL shared memory (meaning it may need disk read) -- Guillaume http://www.postgresql.fr http://dalibo.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Needs Suggestion
SUBHAM ROY wrote: > My output of explain (analyze,buffers) is something like this: > > shared hit=3796624 read=46038 > > So what is meant by read here? Does it indicates number of disk reads? > > Does shared hit takes into account only the hit in the pg_buffercache of the > postgres or it also takes into account the linux buffers that postgres uses? shared hit: read from shared mem shared read: read from disk into shared mem Andreas -- Really, I'm not out to destroy Microsoft. That will just be a completely unintentional side effect. (Linus Torvalds) "If I was god, I would recompile penguin with --enable-fly." (unknown) Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889° -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Needs Suggestion
My output of explain (analyze,buffers) is something like this: shared hit=3796624 read=46038 So what is meant by *read* here? Does it indicates number of disk reads? Does *shared hit* takes into account only the hit in the pg_buffercache of the postgres or it also takes into account the *linux buffers* that postgres uses? -- Thank You, Subham Roy, CSE IIT Bombay.
Re: [GENERAL] Needs Suggestion
On 04/28/2011 02:11 AM, SUBHAM ROY wrote: I am using postgres 8.4.8, the above command explain(analyze,buffers) is not working. Is there a way to do that. Nope, the (BUFFERS) syntax was added in 9.0. Try the other suggestions about using table stats to determine what you want to know. -- Craig Ringer -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Needs Suggestion
I am using postgres 8.4.8, the above command explain(analyze,buffers) is not working. Is there a way to do that. -- Thank You, Subham Roy, CSE IIT Bombay.
Re: [GENERAL] Needs Suggestion
On Wed, Apr 27, 2011 at 16:35, SUBHAM ROY wrote: > Can I calculate the Buffer Hit ratio of a particular query in postgres? That > is how many times it finds the required page in its buffer cache, > pg_buffercache or the > linux buffer cache. You can get the information from the pg cache using EXPLAIN (ANALYZE, BUFFERS) It won't get you the stats from the linux filesystem cache though. You can perhaps use pgfincore for that in some way. -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.com/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Needs Suggestion
On 4/27/2011 9:35 AM, SUBHAM ROY wrote: Can I calculate the *Buffer Hit ratio* *of a particular query* in postgres? That is *how many times it finds the required page* in its buffer cache, pg_buffercache or the linux buffer cache. -- Thank You, Subham Roy, CSE IIT Bombay. There are plenty of stats per table, but I dont think you'll find anything per query. However, you could clear the table stats, run the query a bunch of times, then look at the table stats. Also, it'll tell you pg buffer hits, but you wont really know if it hit the linux cache or actual hard drive. Google can help you out with table stats: postgres cache hit -Andy -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Needs Suggestion
Can I calculate the *Buffer Hit ratio* *of a particular query* in postgres? That is *how many times it finds the required page* in its buffer cache, pg_buffercache or the linux buffer cache. -- Thank You, Subham Roy, CSE IIT Bombay.
Re: [GENERAL] Needs Suggestion
Sorry, but I'm not able to understand about how to use pgsnap for measuring query performance. I have installed pgsnap. when I run pgsnap it shows some error: *Connecting to test database... Adding some HTML files... Getting Misc informations... Getting General informations... sh: pg_controldata: not found Getting Global Informations... pg_buffercache unavailable! Getting Database Informations... pg_buffercache unavailable! pgstattuple unavailable! pgstattuple on indexes unavailable! Getting Current Activities Informations... Getting Statistical Informations... Getting Tools Informations... pgPool unavailable!* However,it generates few reports on the mentioned directory. But I am not able to understand how to use it to measure the performance for each query that I run. -- Thank You, Subham Roy, CSE IIT Bombay.
Re: [GENERAL] Needs Suggestion
On 20 Apr 2011, at 19:11, SUBHAM ROY wrote: > By doing \timing in psql, we enable the timing and then when we type the > query we are able to see its execution time. > Similarly, is there any way to view the number I/Os and memory usage by a > particular query. You seem to be unfamiliar with the EXPLAIN ANALYSE command. > And also the timing result that gets displayed, in which log file does it get > recorded? None, as you're doing your measurements client-side. EXPLAIN ANALYSE is server-side. Alban Hertroys -- If you can't see the forest for the trees, cut the trees and you'll see there is no forest. !DSPAM:737,4dafd8671173731696! -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Needs Suggestion
On Wed, Apr 20, 2011 at 10:41 PM, SUBHAM ROY wrote: > By doing \timing in psql, we enable the timing and then when we type the > query we are able to see its execution time. > Similarly, is there any way to view the number I/Os and memory usage by a > particular query. > > PGsnap module will describes tables,Indexes,Sequences I/O statistics and URL as follows http://pgsnap.projects.postgresql.org/fluxbb_snap_20100513/bgwriter.html pg_buffercache module will shows memory usage on particular tables. --Raghu Ram > And also the timing result that gets displayed, in which log file does it > get recorded? > > -- > Thank You, > Subham Roy, > CSE IIT Bombay. > >
[GENERAL] Needs Suggestion
By doing \timing in psql, we enable the timing and then when we type the query we are able to see its execution time. Similarly, is there any way to view the number I/Os and memory usage by a particular query. And also the timing result that gets displayed, in which log file does it get recorded? -- Thank You, Subham Roy, CSE IIT Bombay.
Re: [GENERAL] Needs Suggestion
On Tue, Apr 19, 2011 at 5:06 PM, SUBHAM ROY wrote: > Suppose Postgres is installed in two computers C1 & C2. > C1 have some database tables. How can I copy these database tables from C1 > to C2. > I mean to say that can I copy tables from postgres installed in one m/c to > another m/c. > Is there any command in postgres to do so or any other short cut technique. Assuming you don't have to worry about foreign keys etc, you can dump a table from one db and feed it to a psql session open to another: pg_dump -h host1 dbname -t tablename | psql -h host2 dbname and so on. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Needs Suggestion
Suppose Postgres is installed in two computers C1 & C2. C1 have some database tables. How can I copy these database tables from C1 to C2. I mean to say that can I copy tables from postgres installed in one m/c to another m/c. Is there any command in postgres to do so or any other short cut technique. -- Thank You, Subham Roy, CSE IIT Bombay.
