Is there a Postgres equivalent of tkprofs for tuning?
I have some questions about tuning. The PostgreSQL documentation
says that you don't need to worry about index maintenance and tuning
with PostgreSQL. I'm used to systems that work a lot better if they
are periodically re-orged or re-indexed. Is it true that one need
not be concerned wit
Dear Sir
I would appreciate your advice on the following :-
I have been unable to find any references to tuning parameters for a
database. Hence when a database is mounted and opened these tuning
parameters come into operation. This is so in oracle where tuning parameters
are placed in the init.
On Mon, Mar 24, 2008 at 4:56 PM, Jack <[EMAIL PROTECTED]> wrote:
>
>
>
>
> Is there a Postgres equivalent of tkprofs for tuning?
you might want to provide an explanation of what tkprofs is and which
parts you're interested in emulating in pgsql.
Have you read up on explain, explain analyze, and t
tkprof generates performance characteristics for an arbitrary number
of SQL statements. The output includes measurements for the individual
SQL statements as well as cumulated values for the sequence of SQL
statements being measured. Data is presented in a two dimensional
format with the first dime
Peter Kovacs написа:
[...]
BTW, I had a bookmark for a good tutorial on explain analyze, but the
page is no longer available:
http://www.pervasive-postgres.com/instantkb13/article.aspx?id=10120 .
Can you suggest a good tutorial?
I'm not sure what was the tutorial at the above URL but this what
On Mon, Mar 24, 2008 at 6:56 PM, Jack <[EMAIL PROTECTED]> wrote:
> Is there a Postgres equivalent of tkprofs for tuning?
No. But what you could do is combine several Postgres things to try
and get the same information.
IIRC, you could use:
- log_parser_stats
- log_planner_stats
- log_executor_st
On Mon, Apr 7, 2008 at 9:33 AM, Carol Walter <[EMAIL PROTECTED]> wrote:
> I have some questions about tuning. The PostgreSQL documentation says that
> you don't need to worry about index maintenance and tuning with PostgreSQL.
> I'm used to systems that work a lot better if they are periodically r
[EMAIL PROTECTED] (Carol Walter) writes:
> I have some questions about tuning. The PostgreSQL documentation
> says that you don't need to worry about index maintenance and tuning
> with PostgreSQL. I'm used to systems that work a lot better if they
> are periodically re-orged or re-indexed. Is i
On Mon, Apr 07, 2008 at 12:45:38PM -0400, Chris Browne wrote:
> versions of PostgreSQL. e.g. - with v7.2, there were patterns of
> updates that would leave portions of indexes not usable, but the issue
> was rectified in ~7.4, and people have not been observing problems
> relating to this former s
On Montag, 7. April 2008 Scott Marlowe wrote:
> You can monitor things like index and table bloat with the pg_stat_*
> series of views.
Are there scripts to automate this look at stats?
Or could one draw graphs from that values, to visualize how well the db
is? E.g., if you could calculate a % va
Michael Monnerie wrote:
On Montag, 7. April 2008 Scott Marlowe wrote:
You can monitor things like index and table bloat with the pg_stat_*
series of views.
Are there scripts to automate this look at stats?
Or could one draw graphs from that values, to visualize how well the db
is? E.g
> Are there scripts to automate this look at stats?
> Or could one draw graphs from that values, to visualize how well the db
> is? E.g., if you could calculate a % value, you could make RRD stats to
> see it's change over time. Is there any project on this?
I don't know about rrd graphing it,
On Dienstag, 8. April 2008 paul rivers wrote:
> I don't see a direct way to monitor bloat from pg_stat*. If I'm
> wrong, please set me straight.
>
> For example, monitoring index bloat would involve deciding how many
> pages an index would ideally consume, based on either sampling the
> table you
Hi.. I seem to be running into a bottle neck on a query, and I'm not
sure what the bottleneck is .
The machine is a dual-processor p3 750 with 2 gigs of (pc100) memory,
and 3 72 gig disks setup
in raid 5. Right now i'm just testing our db for speed (we're porting
from oracle) .. later on
We'r
Hi,
I am running a query on postgres 7.1.3 at Red Hat 7.2 (2 CPUs, 1.5G RAM, 2
drive disk array).
select count(*) from contact a, contact_discard b where a.contacthandle <>
b.contacthandle;
Table contact has over 9 million rows, contact_diacard has around 259,000
rows, both tables define contac
Hi,
I'm having trouble optimizing PostgreSQL for an admittedly heinous
worst-case scenario load.
testbed:
dual P3 1.3 GHz box with 2GB RAM
two IDE 120G drives on separate channels (DMA on), OS on one, DB on the
other, some swap on each (totalling 2.8G).
RH Linux 8.
I've installed PG 7.3.4 from s
I'm looking at tuning a PostgreSQL installation and I'm finding the
documentation confusing. (I'll also add that tuning in general is a
mystery to me!)
What are the main 'knobs' that can be used to tune PostgreSQL? If
someone can tell me that I can concentrate on learning about those
those initial
"Michael T. Halligan" <[EMAIL PROTECTED]> writes:
> The query sorts through about 80k rows.. here's the query
> --
> SELECT count(*) FROM (
> SELECT DISTINCT song_id FROM ssa_candidate WHERE
> style_id IN (
>
Not sure about tuning, but it seems to me that this query would be much more
effective if it's rewritten like this (especially if style_id columns on both
tables are indexed):
SELECT count(DISTINCT song_id) AS X
FROM ssa_candidate SC JOIN station_subgenre SS ON SC.style_id = SS.style_id
WHERE SS.
Thanks, that sped things up a bit, from 7.6 sec. to about 5.5 sec. However
the plan still includes a sequential scan on ssa_candidate:
Aggregate (cost=12161.11..12161.11 rows=1 width=35)
-> Merge Join (cost=11611.57..12111.12 rows=19996 width=35)
-> Sort (cost=11488.27..11488.27 r
This in interesting effect, that I ran into more than once, and I have a
question concerning this:
We see that the cost for this query went from roughly 12,000,000 to about
12,000. Of course, we cannot assume that the time of execution will be
directly proportional to this, and also, the weight fa
ter than your first query),
since it's using in, but I'm sure that someone can suggest something faster.
Thanks,
Peter Darley
-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED]]On Behalf Of Zhang, Anna
Sent: Tuesday, January 29, 2002 7:57 AM
To: [EMAIL PROTECTE
On Tue, Jan 29, 2002 at 10:57:01AM -0500, Zhang, Anna wrote:
> Hi,
> I am running a query on postgres 7.1.3 at Red Hat 7.2 (2 CPUs, 1.5G RAM, 2
> drive disk array).
> select count(*) from contact a, contact_discard b where a.contacthandle <>
> b.contacthandle;
What are you trying to do with th
"Zhang, Anna" <[EMAIL PROTECTED]> writes:
> select count(*) from contact a, contact_discard b where a.contacthandle <>
> b.contacthandle;
Did you really mean "<>" here? If so, the plan you showed us is not for
this query. The above query is going to take approximately forever to
execute :-(
I
takes only a few
minues.
Thanks!
Anna Zhang
-Original Message-
From: Ross J. Reedstrom [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, January 29, 2002 11:39 AM
To: Zhang, Anna
Cc: [EMAIL PROTECTED]
Subject: Re: [ADMIN] tuning SQL
On Tue, Jan 29, 2002 at 10:57:01AM -0500, Zhang, Anna wrote
On Tue, Jan 29, 2002 at 12:23:17PM -0500, Zhang, Anna wrote:
> Thanks Peter Darley, Ross J. Reedstrom and Tom lane!!
> How silly am I! Your messages reminds me. Actually I want to insert rows of
> contact_discard table which are not exists in contact table to contact table
> (some duplicates in tw
On Tue, 29 Jan 2002 17:45:34 + (UTC), [EMAIL PROTECTED] ("Zhang,
Anna") wrote:
>Thanks Peter Darley, Ross J. Reedstrom and Tom lane!!
>How silly am I! Your messages reminds me. Actually I want to insert rows of
>contact_discard table which are not exists in contact table to contact table
>(so
Cliff Pratt wrote:
What are the main 'knobs' that can be used to tune PostgreSQL?
http://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server
--
Greg Smith 2ndQuadrant US Baltimore, MD
PostgreSQL Training, Services and Support
g...@2ndquadrant.com www.2ndQuadrant.us
--
Sent via pgsql-a
(Posted to General a while back but perhaps this is a more appropriate
list since I got no responses. :)
How can I tell if writing to the WAL is a bottleneck? In other words,
how can I tell if upgrading the disk the WAL is on will improve
performance? (I do have the WAL on its own disk.)
-Jona
Hi all, i send this mesage because i just made a migration from my old
mysql db on a more robust postgres server.
I'm using version 8.1 on a linux debian 4.0
i have a dual core XEON 2.33GHz and 2Gb of ram ... 2 disk 15k mirrored
database works good but the performance are horrible! i hope is my
Hi.
I have a 1.8TB PG database, we're doing "fairly heavy" batch updates on.
Say 2/3 of
the database monthly in a background batch process. The system is
working really
well and performing good, but we're always haunting more speed (and
smaller amount of WAL-log).
So I tried to look into how
Greetings. :)
Having just gotten our database all planned out and implimented, and rolled
out live, I'd like to now take the time to sit back and tweak the system up
to get the most bang for my processing buck so to speak.
The database itself isn't that large, comprising 14 tables. Most of th
Just to complete the thread.
Ross
- Forwarded message from "Zhang, Anna" <[EMAIL PROTECTED]> -
From: "Zhang, Anna" <[EMAIL PROTECTED]>
To: "'Ross J. Reedstrom'" <[EMAIL PROTECTED]>
Subject: RE: [ADMIN] tuning SQL
Date: Tue,
At 03:47 PM 8/25/2003, Jonathan Ellis wrote:
>(Posted to General a while back but perhaps this is a more appropriate
>list since I got no responses. :)
>
>How can I tell if writing to the WAL is a bottleneck? In other words, how
>can I tell if upgrading the disk the WAL is on will improve perform
On Fri, Jan 16, 2009 at 04:18:02PM +0100, Francesco Andreozzi wrote:
> Hi all, i send this mesage because i just made a migration from my old
> mysql db on a more robust postgres server.
> I'm using version 8.1 on a linux debian 4.0
> i have a dual core XEON 2.33GHz and 2Gb of ram ... 2 disk 15k m
Just a quick question - why do you use quite old release 8.1 instead of
8.3.5 ?
Post only changes in postgresql.conf and explain analyze of your slow
query.
Oleg
On Fri, 16 Jan 2009, Francesco Andreozzi wrote:
Hi all, i send this mesage because i just made a migration from my old mysql
db on
On Fri, Jan 16, 2009 at 4:37 PM, Oleg Bartunov wrote:
> Just a quick question - why do you use quite old release 8.1 instead of
> 8.3.5 ?
I presume it's because it is the version that comes by default in
Debian Etch. So I'll take this opportunity to comment about
backports.org. If you use that r
Hi Francesco
most probabely this is due to the auto vacuum option on and if auto vacuum
is on then the in condition of high transaction on database it slows the
speed of the hole database.Set it off in postgres.conf and vacuum and
reindex transactional tables manualy with in every two million
tra
On Montag, 19. Januar 2009, BRAHMA PRAKASH TIWARI wrote:
| most probabely this is due to the auto vacuum option on and if auto vacuum
| is on then the in condition of high transaction on database it slows the
| speed of the hole database.Set it off in postgres.conf and vacuum and
| reindex transact
On Mon, Jan 19, 2009 at 5:35 AM, BRAHMA PRAKASH TIWARI
wrote:
> Hi Francesco
>
> most probabely this is due to the auto vacuum option on and if auto vacuum
> is on then the in condition of high transaction on database it slows the
> speed of the hole database.Set it off in postgres.conf and vacuum
Hi there,
I'm going to pg_restore a database dump of about 220 GiB (uncompressed,
but most data is BLOBs). The machine has 8 GiB of memory and 8 cores.
Is there any advice to speed up restoring, postgresql.conf-wise?
I already have a script which does the data loading and index creation
in parall
Hi,
I'm using Postgres 6.5.2 in a high-volume application that has on the order of a
hundred inserts and selects (but mostly inserts) a minute. My loadaverage hovers
around 1.0, and can go much, much higher (50-80) during vacuum and queries on
fairly large tables (1M rows). This is on a 4-CPU Int
Tino Schwarze a écrit :
> [...]
> I'm going to pg_restore a database dump of about 220 GiB (uncompressed,
> but most data is BLOBs). The machine has 8 GiB of memory and 8 cores.
> Is there any advice to speed up restoring, postgresql.conf-wise?
>
> I already have a script which does the data loadi
On Sat, Feb 21, 2009 at 12:14 AM, Guillaume Lelarge
wrote:
> Tino Schwarze a écrit :
>> [...]
>> I'm going to pg_restore a database dump of about 220 GiB (uncompressed,
>> but most data is BLOBs). The machine has 8 GiB of memory and 8 cores.
>> Is there any advice to speed up restoring, postgresql
On Wed, 15 Mar 2000, Brian Baquiran wrote:
> > 3) Turn off flush to disk
> > (option -F )
> > (very little risk)
If this parameter is set, increase much the speed of fast periodic inserts,
updates. Because if postgres run without -F, every single statement generate
a sync() syscall.
> I'd rather
"Hernan J. Gonzalez" wrote:
>
> -- Brian Baquiran <[EMAIL PROTECTED]> wrote:
> > Hi,
> >
> > I'm using Postgres 6.5.2 in a high-volume
> > application that has on the order of a
> > hundred inserts and selects (but mostly inserts) a
> > minute.
> Some suggestions:
>
> 1) Increase the -B and -S
In case anyone is still interested, I was able to improve the load considerably
by wrapping a group of 10-15 INSERTS into a single transaction. I didn't know
that each insert/query not explicitly wrapped in a transaction got it's own
transaction (and the associated overhead) by default.
Load is n
Hi!
On Mon, 13 Mar 2000 17:30:10 +0800
Brian Baquiran <[EMAIL PROTECTED]> wrote:
[snipped]
> I'm running postmaster with the options -B600 -N300. I'm using the RedHat RPMs
> >from a pretty-much-stock RH6.1 installation. Can I tune postgres to load more
> data into memory?
You should increase
I have an application that did many inserts also. Only I was running
on a much lower powered machine and could get nowhere nere your 100
per minute rate. What I did has re-write the application to batch the
inserts and then use COPY. COPY it turns out is _much_ faster than
INSERT maybe by an o
-- Brian Baquiran <[EMAIL PROTECTED]> wrote:
> Hi,
>
> I'm using Postgres 6.5.2 in a high-volume
> application that has on the order of a
> hundred inserts and selects (but mostly inserts) a
> minute. My loadaverage hovers
> around 1.0, and can go much, much higher (50-80)
> during vacuum and que
> 2) Increase the default linux shared memory
> (this may be necesary for the previous
> item to work)
> (for example, in the postgresql.init script
> add the line:
> echo "1" > /proc/sys/kernel/shmmax
> )
Does anyone know how this would be changed in the kernel config?
I can't find it i
Hello,
We will be consolidating from 4 databases to 2 and want to make sure that
these parameters are the only ones that need changing. Please advise.
Current Future
= =
Ma
Hi All,
We have a postgres database in which couple of tables get bloated due to
heavy inserts and deletes. Auto vacuum is running. My question is how
can I make auto vacuum more aggressive? I am thinking of enabling
autovacuum_vacuum_cost_delay and autovacuum_vacuum_cost_limit
parameters. Can
Hello,
I read some articles about PGSQL tuning (7.4.x).
We have a dedicated DB server with 6 GB of RAM. So I'm trying to
determine the the size of my shared buffers.
Ho wcan I find the biggest size of a database ?
Thanks,
---(end of broadcast)---
TI
Barbara Stephenson writes:
> We will be consolidating from 4 databases to 2 and want to make sure that
> these parameters are the only ones that need changing. Please advise.
> Current Future
> =
Thank ypu!
Tom Lane wrote:
Barbara Stephenson writes:
We will be consolidating from 4 databases to 2 and want to make sure that
these parameters are the only ones that need changing. Please advise.
Current Future
=
pgsql-admin@postgresql.org
Subject: Re: [ADMIN] tuning our database by increasing shared buffer
Thank ypu!
Tom Lane wrote:
Barbara Stephenson
<mailto:barb...@turbocorp.com> writes:
We will be consolidating from 4 databases to 2 and want to make
sure that
these parameters
Run a vacuum verbose and look at the output at the end.
Word is that as of 8.4 these parameters will autotune themselvs.
--- On Wed, 24/6/09, Allgood, John wrote:
> From: Allgood, John
> Subject: Re: [ADMIN] tuning our database by increasing shared buffer
> To: "Barbara Step
jallg...@ohl.com
www.ohl.com
-Original Message-
From: Glyn Astill [mailto:glynast...@yahoo.co.uk]
Sent: Wednesday, June 24, 2009 9:46 AM
To: Barbara Stephenson; Tom Lane; Allgood, John
Cc: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] tuning our database by increasing shared buffer
Run a
"Allgood, John" wrote:
> What exactly am I looking for
> INFO: free space map contains 2667 pages in 22 relations
> DETAIL: A total of 3008 page slots are in use (including overhead).
> 3008 page slots are required to track all free space.
> Current limits are: 153600 page slots, 1000 relat
On Tue, Mar 23, 2010 at 2:54 PM, Bhella Paramjeet-PFCW67
wrote:
> Hi All,
>
>
>
> We have a postgres database in which couple of tables get bloated due to
> heavy inserts and deletes. Auto vacuum is running. My question is how can I
> make auto vacuum more aggressive? I am thinking of enabling
>
we ran into the same problem, had big table, played with vacuum cost and
delay, but can't shrink too much because of heavy insert and delete.
we ended up with using slony for upgrade, also have data copy from fresh
because of inital replication to shrink our large table, with minimum
controlled dow
we ran into the same problem, had big table, played with vacuum cost and
delay, but can't shrink too much because of heavy insert and delete.
we ended up with using slony for upgrade, also have data copy from fresh
because of inital replication to shrink our large table, with minimum
controlled dow
-Original Message-
From: Scott Marlowe [mailto:scott.marl...@gmail.com]
Sent: Tuesday, March 23, 2010 2:42 PM
To: Bhella Paramjeet-PFCW67
Cc: pgsql-admin@postgresql.org; pgsql-performa...@postgresql.org
Subject: Re: [ADMIN] tuning auto vacuum for highly active tables
On Tue, Mar 23, 2010
nks
> Paramjeet Kaur
>
> -Original Message-
> From: Scott Marlowe [mailto:scott.marl...@gmail.com]
> Sent: Tuesday, March 23, 2010 2:42 PM
> To: Bhella Paramjeet-PFCW67
> Cc: pgsql-admin@postgresql.org; pgsql-performa...@postgresql.org
> Subject: Re: [ADMIN] tuning a
Scott Marlowe escribió:
> On Tue, Mar 23, 2010 at 5:28 PM, Bhella Paramjeet-PFCW67
> wrote:
> > Hi Scott,
> >
> > Thanks for replying.
> > Can you explain what you mean by increase the number of threads or how I
> > can increase the number of threads? I just have 2 tables that are very
> > activ
Alvaro Herrera escribió:
> Also, keep in mind that max_workers is a new setting in 8.3. Since the
> OP is running 8.2, he can only get one "worker". Presumable he needs to
> disable autovac for those two very active tables and setup a cron job to
> process them in their own schedule.
Err, sorry
68 matches
Mail list logo
