pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedLogging just SQL commands in an SQL script format
4+ messages / 3 participants
[nested] [flat]
* Logging just SQL commands in an SQL script format
@ 2007-01-24 20:45 Campbell, Lance <lance@uiuc.edu>
2007-01-25 13:21 ` Re: Logging just SQL commands in an SQL script format Rigmor Ukuhe <rigmor.ukuhe@finestmedia.ee>
2007-01-25 14:56 ` Re: Logging just SQL commands in an SQL script format Daniel Cristian Cruz <danielcristian@gmail.com>
0 siblings, 2 replies; 4+ messages in thread
From: Campbell, Lance @ 2007-01-24 20:45 UTC (permalink / raw)
To: pgsql-admin
I want to log all SQL commands as an executable script file for use in
performance testing. Currently there is a lot of information in the log
that is generated that would not be executable. Below is an example of
what I would want to see; which is just the SQL.
Example of log output:
Select a,b,c from calendar where a=12;
Select a,b,c from calendar where a=13;
Example:
1) I would take a snap shot of our production database.
2) I would then turn on SQL logging.
3) I would put the copy of the database onto a test server.
4) After a few days of logging the SQL commands I would copy the file to
the test server.
5) I would then have a basic script that would display the start time,
execute the postgres SQL log (pgsql -d database_name -f
sql_log_file_name), and then display the end time.
Now I can test configuration changes and index modifications on real
world data to see what impact the changes would have. I just keep
reloading the database, change the configurations settings, and then
execute the script again.
The key is that the SQL log be in a format that can be executed as a
postgres SQL file.
How do I do this?
Thanks,
Lance Campbell
Project Manager/Software Architect
Web Services at Public Affairs
University of Illinois
217.333.0382
http://webservices.uiuc.edu
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Logging just SQL commands in an SQL script format
2007-01-24 20:45 Logging just SQL commands in an SQL script format Campbell, Lance <lance@uiuc.edu>
@ 2007-01-25 13:21 ` Rigmor Ukuhe <rigmor.ukuhe@finestmedia.ee>
2007-01-25 16:53 ` Re: Logging just SQL commands in an SQL script format Campbell, Lance <lance@uiuc.edu>
1 sibling, 1 reply; 4+ messages in thread
From: Rigmor Ukuhe @ 2007-01-25 13:21 UTC (permalink / raw)
To: pgsql-admin
Campbell, Lance wrote:
> I want to log all SQL commands as an executable script file for use in
> performance testing. Currently there is a lot of information in the log
> that is generated that would not be executable. Below is an example of
> what I would want to see; which is just the SQL.
>
>
>
> Example of log output:
>
> Select a,b,c from calendar where a=12;
>
> Select a,b,c from calendar where a=13;
>
>
Hi,
Wouldn't SQL statements in log possible be out of order, also transaction info
would be missing?
Even if you do it in application-side, logging query start time and transaction
info, and feed this data to some application that starts to excecute them in
correct time and context, you wouldnt be able to reliably replicate exact
behavior, cause configuration change might change query times too much.
But i see it _can_ be useful in some cases.
Rigmor
>
>
>
> How do I do this?
>
>
>
> Thanks,
>
>
>
>
>
>
>
> Lance Campbell
>
> Project Manager/Software Architect
>
> Web Services at Public Affairs
>
> University of Illinois
>
> 217.333.0382
>
> http://webservices.uiuc.edu
>
>
>
> !DSPAM:5,45b7c785287772091230466!
--
Rigmor Ukuhe
Finestmedia Ltd | Software Development Team Manager
gsm : (+372)56467729 | tel : (+372)6558043 | e-mail : rigmor.ukuhe@finestmedia.ee
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Logging just SQL commands in an SQL script format
2007-01-24 20:45 Logging just SQL commands in an SQL script format Campbell, Lance <lance@uiuc.edu>
2007-01-25 13:21 ` Re: Logging just SQL commands in an SQL script format Rigmor Ukuhe <rigmor.ukuhe@finestmedia.ee>
@ 2007-01-25 16:53 ` Campbell, Lance <lance@uiuc.edu>
0 siblings, 0 replies; 4+ messages in thread
From: Campbell, Lance @ 2007-01-25 16:53 UTC (permalink / raw)
To: Rigmor Ukuhe <rigmor.ukuhe@finestmedia.ee>; pgsql-admin
I actually wrote a java application that can strip out the SQL and build
a SQL script file.
If you take a snap shot of your database and then log the SQL commands
you have at least a real world snapshot of the SQL being executed for a
given time. This gives you some level of measurement when comparing
some settings like memory allocation and index changes.
You are correct it is not perfect. But it does give you a way to
measure the performance difference to some degree. Performance tuning a
database is considered an art by many because every database is
different and every server is different. I am just trying to take a
little of the art out of the equation.
Thanks,
Lance Campbell
Project Manager/Software Architect
Web Services at Public Affairs
University of Illinois
217.333.0382
http://webservices.uiuc.edu
-----Original Message-----
From: pgsql-admin-owner@postgresql.org
[mailto:pgsql-admin-owner@postgresql.org] On Behalf Of Rigmor Ukuhe
Sent: Thursday, January 25, 2007 7:21 AM
To: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] Logging just SQL commands in an SQL script format
Campbell, Lance wrote:
> I want to log all SQL commands as an executable script file for use in
> performance testing. Currently there is a lot of information in the
log
> that is generated that would not be executable. Below is an example
of
> what I would want to see; which is just the SQL.
>
>
>
> Example of log output:
>
> Select a,b,c from calendar where a=12;
>
> Select a,b,c from calendar where a=13;
>
>
Hi,
Wouldn't SQL statements in log possible be out of order, also
transaction info
would be missing?
Even if you do it in application-side, logging query start time and
transaction
info, and feed this data to some application that starts to excecute
them in
correct time and context, you wouldnt be able to reliably replicate
exact
behavior, cause configuration change might change query times too much.
But i see it _can_ be useful in some cases.
Rigmor
>
>
>
> How do I do this?
>
>
>
> Thanks,
>
>
>
>
>
>
>
> Lance Campbell
>
> Project Manager/Software Architect
>
> Web Services at Public Affairs
>
> University of Illinois
>
> 217.333.0382
>
> http://webservices.uiuc.edu
>
>
>
> !DSPAM:5,45b7c785287772091230466!
--
Rigmor Ukuhe
Finestmedia Ltd | Software Development Team Manager
gsm : (+372)56467729 | tel : (+372)6558043 | e-mail :
rigmor.ukuhe@finestmedia.ee
---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Logging just SQL commands in an SQL script format
2007-01-24 20:45 Logging just SQL commands in an SQL script format Campbell, Lance <lance@uiuc.edu>
@ 2007-01-25 14:56 ` Daniel Cristian Cruz <danielcristian@gmail.com>
1 sibling, 0 replies; 4+ messages in thread
From: Daniel Cristian Cruz @ 2007-01-25 14:56 UTC (permalink / raw)
To: ; +Cc: pgsql-admin
Look at pgfouine.
It can extract SQL commands from log, and build a xml file to be used
with tsung to generate some load testing.
Kind regards...
On 1/24/07, Campbell, Lance <lance@uiuc.edu> wrote:
>
>
>
>
> I want to log all SQL commands as an executable script file for use in
> performance testing. Currently there is a lot of information in the log
> that is generated that would not be executable. Below is an example of what
> I would want to see; which is just the SQL.
>
>
>
> Example of log output:
>
> Select a,b,c from calendar where a=12;
>
> Select a,b,c from calendar where a=13;
>
>
>
> Example:
>
> 1) I would take a snap shot of our production database.
>
> 2) I would then turn on SQL logging.
>
> 3) I would put the copy of the database onto a test server.
>
> 4) After a few days of logging the SQL commands I would copy the file to the
> test server.
>
> 5) I would then have a basic script that would display the start time,
> execute the postgres SQL log (pgsql –d database_name –f sql_log_file_name),
> and then display the end time.
>
>
>
> Now I can test configuration changes and index modifications on real world
> data to see what impact the changes would have. I just keep reloading the
> database, change the configurations settings, and then execute the script
> again.
>
>
>
> The key is that the SQL log be in a format that can be executed as a
> postgres SQL file.
>
>
>
> How do I do this?
>
>
>
> Thanks,
>
>
>
>
>
>
>
> Lance Campbell
>
> Project Manager/Software Architect
>
> Web Services at Public Affairs
>
> University of Illinois
>
> 217.333.0382
>
> http://webservices.uiuc.edu
>
>
--
Daniel Cristian Cruz
Analista de Sistemas
Especialista postgreSQL e Linux
Instrutor Certificado Mandriva
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2007-01-25 16:53 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-01-24 20:45 Logging just SQL commands in an SQL script format Campbell, Lance <lance@uiuc.edu>
2007-01-25 13:21 ` Rigmor Ukuhe <rigmor.ukuhe@finestmedia.ee>
2007-01-25 16:53 ` Campbell, Lance <lance@uiuc.edu>
2007-01-25 14:56 ` Daniel Cristian Cruz <danielcristian@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox