Thread: Needs Suggestion
Hi,<br />I am currently a student of IIT Bombay. I am doing a project on "Benchmark design". For that I need to measure theperformance of various queries in databases. <br />I want to know how can we measure the execution time of a query inPostgres (Explain analyze will not do). Also is there any tools available in Linux for measuring the performance of queriesof databases such as Oracle 11g, Postgres, etc.<br /> Any suggestions will be very helpful.<br clear="all" /><br />--<br />Thank You,<br />Subham Roy,<br />CSE IIT Bombay.<br /><br />
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. >
On Sun, Mar 27, 2011 at 9:22 AM, SUBHAM ROY <subham.iem@gmail.com> wrote:
If EXPLAIN ANALYZE is not sufficient for your purpose, then you need elaborate what exactly are you looking for.
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
EnterpriseDB Corporation
The Enterprise PostgreSQL Company
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?
--
Thank You,
Subham Roy,
CSE IIT Bombay.
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 <singh.gurjeet@gmail.com> wrote:
On Sun, Mar 27, 2011 at 9:22 AM, SUBHAM ROY <subham.iem@gmail.com> 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.
In these (Postgres) mailing lists, top-posting is not preferred. See my response below.
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,
--
On Sun, Mar 27, 2011 at 1:01 PM, SUBHAM ROY <subham.iem@gmail.com> wrote:
Actually, I want to run some set of queries in postgres on a HUGE data set.On Sun, Mar 27, 2011 at 10:20 PM, Gurjeet Singh <singh.gurjeet@gmail.com> wrote:On Sun, Mar 27, 2011 at 9:22 AM, SUBHAM ROY <subham.iem@gmail.com> 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.
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
EnterpriseDB Corporation
The Enterprise PostgreSQL Company
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 <singh.gurjeet@gmail.com>wrote: > > > On Sun, Mar 27, 2011 at 9:22 AM, SUBHAM ROY <subham.iem@gmail.com> 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. > > >
SUBHAM ROY <subham.iem@gmail.com> 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