Hi,
I am currently a student of IIT Bombay. I am doing a project on "Benchmark
design". For that I need to measure the performance of various queries in
databases.
I want to know how can we measure the execution time of a query in Postgres
(Explain analyze will not do). Also is there any tools available in Linux
for measuring the performance of queries of databases such as Oracle 11g,
Postgres, etc.
Any suggestions will be very helpful.

--
Thank You,
Subham Roy,
CSE IIT Bombay.

Search Discussions

  • 3dmashup at Mar 27, 2011 at 2:57 pm
    Subham,

    I would start with reviewing Prof Mike Stonebrakers and Dr Paula
    Hawthorns paper http://portal.acm.org/citation.cfm?doid=582095.582097
    you can also look at Perftrack
    http://citeseerx.ist.psu.edu/viewdoc/download?doi=10.1.1.101.7063&rep=rep1&type=pdf

    regards

    3dmashUp
    On 3/27/2011 6:22 AM, SUBHAM ROY wrote:
    Hi,
    I am currently a student of IIT Bombay. I am doing a project on
    "Benchmark design". For that I need to measure the performance of
    various queries in databases.
    I want to know how can we measure the execution time of a query in
    Postgres (Explain analyze will not do). Also is there any tools
    available in Linux for measuring the performance of queries of
    databases such as Oracle 11g, Postgres, etc.
    Any suggestions will be very helpful.

    --
    Thank You,
    Subham Roy,
    CSE IIT Bombay.
  • Gurjeet Singh at Mar 27, 2011 at 4:50 pm

    On Sun, Mar 27, 2011 at 9:22 AM, SUBHAM ROY wrote:

    Hi,
    I am currently a student of IIT Bombay. I am doing a project on "Benchmark
    design". For that I need to measure the performance of various queries in
    databases.
    I want to know how can we measure the execution time of a query in Postgres
    (Explain analyze will not do). Also is there any tools available in Linux
    for measuring the performance of queries of databases such as Oracle 11g,
    Postgres, etc.
    Any suggestions will be very helpful.
    If EXPLAIN ANALYZE is not sufficient for your purpose, then you need
    elaborate what exactly are you looking for.

    Regards,
    --
    Gurjeet Singh
    EnterpriseDB Corporation
    The Enterprise PostgreSQL Company
  • SUBHAM ROY at Mar 27, 2011 at 5:02 pm
    Actually, I want to run some set of queries in postgres on a HUGE data set.
    I have to compute the actual execution time for each of those queries. So
    how can I do that in Postgres ?

    Suppose in Oracle following thing can be done :
    *
    set timing on;
    select stuff from mytab;

    Elapsed: 00:00:02.82

    *Likewise, can I do this in Postgres? "set timing on" does not work in
    Postgres.
    Also is there any free tools available in Linux for doing so?
    On Sun, Mar 27, 2011 at 10:20 PM, Gurjeet Singh wrote:
    On Sun, Mar 27, 2011 at 9:22 AM, SUBHAM ROY wrote:

    Hi,
    I am currently a student of IIT Bombay. I am doing a project on "Benchmark
    design". For that I need to measure the performance of various queries in
    databases.
    I want to know how can we measure the execution time of a query in
    Postgres (Explain analyze will not do). Also is there any tools available in
    Linux for measuring the performance of queries of databases such as Oracle
    11g, Postgres, etc.
    Any suggestions will be very helpful.
    If EXPLAIN ANALYZE is not sufficient for your purpose, then you need
    elaborate what exactly are you looking for.

    Regards,
    --
    Gurjeet Singh
    EnterpriseDB Corporation
    The Enterprise PostgreSQL Company

    --
    Thank You,
    Subham Roy,
    CSE IIT Bombay.
  • Gurjeet Singh at Mar 27, 2011 at 5:21 pm
    In these (Postgres) mailing lists, top-posting is not preferred. See my
    response below.
    On Sun, Mar 27, 2011 at 1:01 PM, SUBHAM ROY wrote:

    On Sun, Mar 27, 2011 at 10:20 PM, Gurjeet Singh wrote:
    On Sun, Mar 27, 2011 at 9:22 AM, SUBHAM ROY wrote:

    Hi,
    I am currently a student of IIT Bombay. I am doing a project on
    "Benchmark design". For that I need to measure the performance of various
    queries in databases.
    I want to know how can we measure the execution time of a query in
    Postgres (Explain analyze will not do). Also is there any tools available in
    Linux for measuring the performance of queries of databases such as Oracle
    11g, Postgres, etc.
    Any suggestions will be very helpful.
    If EXPLAIN ANALYZE is not sufficient for your purpose, then you need
    elaborate what exactly are you looking for.

    Actually, I want to run some set of queries in postgres on a HUGE data
    set.
    I have to compute the actual execution time for each of those queries. So
    how can I do that in Postgres ?

    Suppose in Oracle following thing can be done :
    *
    set timing on;
    select stuff from mytab;

    Elapsed: 00:00:02.82

    *Likewise, can I do this in Postgres? "set timing on" does not work in
    Postgres.
    Also is there any free tools available in Linux for doing so?
    Postgres' command-line clinet is psql, and it has a meta-command

    \timing (or \t for short)

    This will show you the query execution time, including the network roundtrip
    it takes to send the query and receive the response.

    If you don't want to account for network round trips, the the server has a
    GUC parameter called log_min_duration_statement [1]. Either you change it's
    value in postgresql.conf (so it affects all connections thereafter), or use
    SQL command

    set log_min_duration_statement = 0;

    as the first command in all your sessions to log all queries.

    psql's \t command results in times being shown right in the psql session,
    but setting log_min_duration_statement results in query and time taken beong
    logged in server logs (usually under $PGDATA/pg_log/ or in syslog).

    [1]
    http://www.postgresql.org/docs/current/static/runtime-config-logging.html#RUNTIME-CONFIG-LOGGING-WHEN

    HTH,
    --
    Gurjeet Singh
    EnterpriseDB Corporation
    The Enterprise PostgreSQL Company
  • Tomáą Pospíąil at Mar 27, 2011 at 7:13 pm
    You could use this in psql. Don't know how precise is it, but is enought for initial testing.

    postgres=# \timing
    Timing is on.
    postgres=# create index dx on diplomka using gist(data);
    CREATE INDEX
    Time: 236752.569 ms

    ------------ Původní zpráva ------------
    Od: SUBHAM ROY <subham.iem@gmail.com>
    Předmět: Re: [HACKERS] Needs Suggestion
    Datum: 27.3.2011 19:02:32
    ----------------------------------------
    Actually, I want to run some set of queries in postgres on a HUGE data set.
    I have to compute the actual execution time for each of those queries. So
    how can I do that in Postgres ?

    Suppose in Oracle following thing can be done :
    *
    set timing on;
    select stuff from mytab;

    Elapsed: 00:00:02.82

    *Likewise, can I do this in Postgres? "set timing on" does not work in
    Postgres.
    Also is there any free tools available in Linux for doing so?
    On Sun, Mar 27, 2011 at 10:20 PM, Gurjeet Singh wrote:
    On Sun, Mar 27, 2011 at 9:22 AM, SUBHAM ROY wrote:

    Hi,
    I am currently a student of IIT Bombay. I am doing a project on "Benchmark
    design". For that I need to measure the performance of various queries in
    databases.
    I want to know how can we measure the execution time of a query in
    Postgres (Explain analyze will not do). Also is there any tools available in
    Linux for measuring the performance of queries of databases such as Oracle
    11g, Postgres, etc.
    Any suggestions will be very helpful.
    If EXPLAIN ANALYZE is not sufficient for your purpose, then you need
    elaborate what exactly are you looking for.

    Regards,
    --
    Gurjeet Singh
    EnterpriseDB Corporation
    The Enterprise PostgreSQL Company

    --
    Thank You,
    Subham Roy,
    CSE IIT Bombay.

  • Dimitri Fontaine at Mar 28, 2011 at 11:04 am

    SUBHAM ROY writes:
    I want to know how can we measure the execution time of a query in Postgres
    (Explain analyze will not do). Also is there any tools available in Linux
    for measuring the performance of queries of databases such as Oracle 11g,
    Postgres, etc.
    Any suggestions will be very helpful.
    Try pgbench (in contribs) and then Tsung, that could help you run a test
    suite and get time reports. See also pgbench-tools.

    http://www.westnet.com/~gsmith/content/postgresql/pgbench-tools.htm

    Regards,
    --
    Dimitri Fontaine
    http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-hackers @
categoriespostgresql
postedMar 27, '11 at 1:23p
activeMar 28, '11 at 11:04a
posts7
users5
websitepostgresql.org...
irc#postgresql

People

Translate

site design / logo © 2022 Grokbase