pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedcurrent transaction in productive database
4+ messages / 4 participants
[nested] [flat]
* current transaction in productive database
@ 2009-03-20 09:26 ml@bortal.de <ml@bortal.de>
0 siblings, 3 replies; 4+ messages in thread
From: ml@bortal.de @ 2009-03-20 09:26 UTC (permalink / raw)
To: pgsql-performance
Hello List,
is there a way to find out, how many transactions my currenc productive
database is doing?
I know know how much i an offer with my new database and hardware, but i
would also like to know what i actually _need_ on my current productive
system.
Is there a way to find this out?
Cheers,
Mario
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: current transaction in productive database
@ 2009-03-20 17:01 Jeff <threshar@torgo.978.org>
parent: ml@bortal.de <ml@bortal.de>
2 siblings, 0 replies; 4+ messages in thread
From: Jeff @ 2009-03-20 17:01 UTC (permalink / raw)
To: ml@bortal.de; +Cc: pgsql-performance
On Mar 20, 2009, at 5:26 AM, ml@bortal.de wrote:
> Hello List,
>
> is there a way to find out, how many transactions my currenc
> productive database is doing?
>
> I know know how much i an offer with my new database and hardware,
> but i would also like to know what i actually _need_ on my current
> productive system.
>
> Is there a way to find this out?
Are you looking to see how many transactions per second or more how
many transactions concurrently at a given time?
For the former you can use pgspy (its on pgfoundry) to get an idea of
queries per second coming in.
For the latter, just select * from pg_stat_activity where
current_query <> '<IDLE>';
--
Jeff Trout <jeff@jefftrout.com>
http://www.stuarthamm.net/
http://www.dellsmartexitin.com/
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: current transaction in productive database
@ 2009-03-20 17:34 Greg Smith <gsmith@gregsmith.com>
parent: ml@bortal.de <ml@bortal.de>
2 siblings, 0 replies; 4+ messages in thread
From: Greg Smith @ 2009-03-20 17:34 UTC (permalink / raw)
To: ml@bortal.de <ml@bortal.de>; +Cc: pgsql-performance
On Fri, 20 Mar 2009, ml@bortal.de wrote:
> is there a way to find out, how many transactions my currenc productive
> database is doing?
What you probably want here is not a true transaction count, which might
include thing that don't matter much for scaling purposes, but instead to
count things happening that involve a database change. You can find out
totals for that broken down by table using this:
select * from pg_stat_user_tables
See http://www.postgresql.org/docs/8.3/static/monitoring-stats.html for
more details. You'll want to sum the totals for inserts, updates, and
deletes to get all the normal transcations.
That will be a total since the statistics were last reset. If you want a
snapshot for a period, you can either sample at the beginning and end and
subtract, or you can use:
select pg_stat_reset();
To reset everything, wait for some period, and then look at the totals.
You may not want to do that immediately though. The totals since the
database were brought up that you'll find in the statistics views can be
interesting to look at for some historical perspective, so you should
probably save any of those that look interesting before you reset
anything.
--
* Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: current transaction in productive database
@ 2009-03-20 17:50 Euler Taveira de Oliveira <euler@timbira.com>
parent: ml@bortal.de <ml@bortal.de>
2 siblings, 0 replies; 4+ messages in thread
From: Euler Taveira de Oliveira @ 2009-03-20 17:50 UTC (permalink / raw)
To: ml@bortal.de <ml@bortal.de>; +Cc: pgsql-performance
ml@bortal.de escreveu:
> is there a way to find out, how many transactions my currenc productive
> database is doing?
>
If you're looking for number of transactions then you can query the catalogs as:
$ export myq="select sum(xact_commit+xact_rollback) from pg_stat_database"
$ psql -U postgres -c "$myq" && sleep 60 && psql -U postgres -c "$myq"
sum
-----------
178992891
(1 row)
sum
-----------
178996065
(1 row)
$ bc -q
scale=3
(178996065-178992891)/60
52.900
Depending on your workload pattern, it's recommended to increase the sleep time.
--
Euler Taveira de Oliveira
http://www.timbira.com/
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2009-03-20 17:50 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2009-03-20 09:26 current transaction in productive database ml@bortal.de <ml@bortal.de>
2009-03-20 17:01 ` Jeff <threshar@torgo.978.org>
2009-03-20 17:34 ` Greg Smith <gsmith@gregsmith.com>
2009-03-20 17:50 ` Euler Taveira de Oliveira <euler@timbira.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