On Wednesday 09 December 2009 03:05:40 Greg Smith wrote:
> On Linux having the WAL on a separate disk can improve things much more
> than you might expect, simply because of how brain-dead the filesystem
> fsync implementation is. Reducing the seeks for WAL traffic can help a
> lot too.
Not using
Richard Neill wrote:
(does the advice for 8.3 apply unchanged to 8.4?)
Yes; no changes in this area for 8.4. The main things performance
related that changed between 8.3 and 8.4 are:
1) VACUUM free space management reimplemented so that the max_fsm_*
parameters aren't needed anymore
2) defaul
2009/12/8 Lennin Caro
>
> From: niraj patel
>
> Subject: Re: [PERFORM] Optimizing Bitmap Heap Scan.
> To: "Grzegorz Jaśkiewicz"
> Cc: pgsql-performance@postgresql.org
> Date: Tuesday, December 8, 2009, 1:50 PM
>
> Hi gryzman,
>
> I have run vacuum full analyze on the cmrules tables. The version
On Tue, Dec 8, 2009 at 11:07 AM, Schmitz, David
wrote:
> So how should we proceed with this issue?
I think Tom nailed it.
...Robert
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-perfo
Lennin Caro wrote:
> I have run vacuum full
That's not usually a good idea. For one thing, it will tend to
bloat your indexes.
-Kevin
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsq
Jonathan Foy writes:
> I was wondering if that was the problem. So I'm correct in thinking that
> the failure occurred when the vacuum tried to pull its 256 MB as defined in
> the maintenance_work_mem value, and the system just did not have enough
> available...any idea why that would suddenly st
On Tue, Dec 8, 2009 at 4:31 PM, Jonathan Foy wrote:
> I was wondering if that was the problem. So I'm correct in thinking that
> the failure occurred when the vacuum tried to pull its 256 MB as defined in
> the maintenance_work_mem value, and the system just did not have enough
> available...
Co
I was wondering if that was the problem. So I'm correct in thinking that
the failure occurred when the vacuum tried to pull its 256 MB as defined in
the maintenance_work_mem value, and the system just did not have enough
available...any idea why that would suddenly start happening? The indexes I
Jonathan Foy writes:
> My vacuums have suddenly started to fail, seemingly at random. I am
> confused.
> I'm running 8.1.3, with close to a dozen servers, up to 150 databases each.
> I have 8GB of RAM. Vacuums have started to fail on all servers (though only
> the occasional vacuum) with the fo
Hi Robert,
unfortunatley its non of the things :-( see below:
- EXPLAIN SELECT * FROM xdf.xdf_admin_hierarchy
WHERE admin_place_id = 150738434
On Postgresql 8.4.1 and 8.3.8
Index Scan using pk_rdf_admin_hierarchy on rdf_admin_hierarchy
(cost=0.00..8.28 rows=1 width=34)
Index Cond: (
Robert Haas writes:
> I can think of a couple of possible explanations for the behavior you're
> seeing:
The reason it's switching from a nestloop to something else is pretty
obvious: the estimate of the number of rows coming out of the lower
join has gone from 81 to 60772. Neither of which is r
On Tue, Dec 8, 2009 at 12:37 AM, Niu Yan wrote:
> Can't use an undefined value as an ARRAY reference at
> /usr/lib/perl5/site_perl/5.8.8/Test/Parser/Dbt2.pm line 521.
I'm guessing this is intended as a bug report, but this is a
PostgreSQL mailing list, and that's a Perl error message.
...Robert
From: niraj patel
Subject: Re: [PERFORM] Optimizing Bitmap Heap Scan.
To: "Grzegorz Jaśkiewicz"
Cc: pgsql-performance@postgresql.org
Date: Tuesday, December 8, 2009, 1:50 PM
Hi gryzman,
I have run vacuum full analyze on the cmrules tables. The version of pstgres is
8.2.13. How should I change
Hello
My vacuums have suddenly started to fail, seemingly at random. I am
confused.
I'm running 8.1.3, with close to a dozen servers, up to 150 databases each.
I have 8GB of RAM. Vacuums have started to fail on all servers (though only
the occasional vacuum) with the following error:
VACUUM,ER
On Tue, Dec 8, 2009 at 8:27 AM, Schmitz, David wrot
> that is exactly the problem postgresql 8.4.1 does not consider the primary
> key but instead calculates
> a hash join. This can only result in poorer performance. I think this is a
> bug.
Your statement that "this can only result in poorer p
Richard Neill wrote:
> So far, I've set checkpoint_segments to 128, timeout to 10min, and
> completion_target to 0.8. This helps, but not as much as I'd
> hoped.
>
> But I haven't touched any of the other WAL or BG Writer settings.
>
> Where should I look next?
On our web servers, where we h
On Tue, Dec 8, 2009 at 7:12 AM, Craig Ringer
wrote:
> On 8/12/2009 6:11 PM, Thom Brown wrote:
>
>> Your output shows that the xdf_admin_hierarchy tables between versions
>> are drastically different. 8.3.8 only contains 1 row, whereas 8.4.1
>> contains 84211 rows.
>
> That's just because one of t
On Tue, 8 Dec 2009, niraj patel wrote:
Thanks very much for the analysis. It does takes 17 sec to execute when
data is not in cache.
It sounds like the table is already very much ordered by the workspaceid,
otherwise this would have taken much longer.
What I would like to ask can partitionin
Hi Matthew ,
Thanks very much for the analysis. It does takes 17 sec to execute when data is
not in cache. I cannot use "distinct" as I have aggregate operators in select
clause in original query. What I would like to ask can partitioning around
workspaceid would help ? Or any sort of selective
On Fri, 13 Nov 2009, Greg Smith wrote:
In order for a drive to work reliably for database use such as for
PostgreSQL, it cannot have a volatile write cache. You either need a write
cache with a battery backup (and a UPS doesn't count), or to turn the cache
off. The SSD performance figures you
On Tue, 8 Dec 2009, niraj patel wrote:
Group (cost=509989.19..511518.30 rows=9 width=10) (actual
time=1783.102..2362.587
rows=261 loops=1)
-> Sort (cost=509989.19..510753.74 rows=305821 width=10) (actual
time=1783.097..2121.378 rows=272211 loops=1)
Sort Key: topfamilyid
Hi gryzman,
I have run vacuum full analyze on the cmrules tables. The version of pstgres is
8.2.13. How should I change stats to 100 ?
Thanks
From: Grzegorz Jaśkiewicz
To: niraj patel
Cc: pgsql-performance@postgresql.org
Sent: Tue, 8 December, 2009 7:12:49 P
it looks like it might choose wrong plan, cos it gets the stats wrong.
Try increasing number of stats to 100.
Btw, what version it is ?
Hi All,
I have to optimize following query :
SELECT r.TopFamilyID AS FamilyID, FROM CMRules r
WHERE r.WorkspaceID =18512
GROUP BY r.TopFamilyID ;
The explain plan is as follows :
Group (cost=509989.19..511518.30 rows=9 width=10) (actual
time=1783.102..2362.587 rows
Hi Craig,
that is exactly the problem postgresql 8.4.1 does not consider the primary key
but instead calculates
a hash join. This can only result in poorer performance. I think this is a bug.
Regards
David
>-Ursprüngliche Nachricht-
>Von: Craig Ringer [mailto:cr...@postnewspapers.com
On 8/12/2009 6:11 PM, Thom Brown wrote:
Your output shows that the xdf_admin_hierarchy tables between versions
are drastically different. 8.3.8 only contains 1 row, whereas 8.4.1
contains 84211 rows.
That's just because one of them is doing a nested loop where it looks up
a single row from x
Hi Andres,
this is just one of many of these queries. There are a lot of jobs calculating
stuff for different ranges which are defined via between in the where clause.
When I leave out the between in the where clause it returns:
On Postgresql 8.4.1:
Sort (cost=5390066.42..5435347.78 rows=181
Hi David,
On Tuesday 08 December 2009 10:59:51 Schmitz, David wrote:
> >> With our data it is a performance difference from 1h16min
> >> (8.3.8) to 2h43min (8.4.1)
> On Postgresql 8.4.1
> Total runtime: 101.446 ms
> and on Postgresql 8.3.8:
> Total runtime: 29.366 ms
Hm. There obviously is more g
Hi Thom,
I did a select count(*) from xdf.xdf_admin_hierarchy and it returns 84211 on
both databases postgres 8.3.8 and 8.4.1.
The amount of data is exactly the same in both databases as they are restored
from the same dump.
Regards
David
_
Von: Thom Brown [mailto:thombr.
2009/12/8 Schmitz, David
> Hi Andres,
>
> EXPLAIN ANALYZE
> select ah.ORDER8_ID, ah.BUILTUP_ID, rl.LINK_ID, la.SIDE,
>rl.ROAD_NAME_ID, rl.LEFT_ADDRESS_RANGE_ID,
> rl.RIGHT_ADDRESS_RANGE_ID,
>rl.IS_EXIT_NAME, rl.EXPLICATABLE, rl.IS_JUNCTION_NAME,
>
Dear All,
Thanks for all your help so far. This page was particularly helpful:
http://www.westnet.com/~gsmith/content/postgresql/chkp-bgw-83.htm
(does the advice for 8.3 apply unchanged to 8.4?)
I'm still hitting issues with this though: sync is taking 7-10 seconds
and I need to get it down to n
Dear All,
Thanks for all your help so far. This page was particularly helpful:
http://www.westnet.com/~gsmith/content/postgresql/chkp-bgw-83.htm
(does the advice for 8.3 apply unchanged to 8.4?)
I'm still hitting issues with this though: sync is taking 7-10 seconds
and I need to get it down to n
Hi Andres,
EXPLAIN ANALYZE
select ah.ORDER8_ID, ah.BUILTUP_ID, rl.LINK_ID, la.SIDE,
rl.ROAD_NAME_ID, rl.LEFT_ADDRESS_RANGE_ID,
rl.RIGHT_ADDRESS_RANGE_ID,
rl.IS_EXIT_NAME, rl.EXPLICATABLE, rl.IS_JUNCTION_NAME,
rl.IS_NAME_ON_ROADSIG
Hi Andres,
This query returns for 8.4.1 and for 8.3.8 the same result:
stadistinct = -1
stanullfrac = 0
stawidth = 4
array_upper nothing
Regards
David
>-Ursprüngliche Nachricht-
>Von: Robert Haas [mailto:robertmh...@gmail.com]
>Gesendet: Dienstag, 8. Dezember 2009 05:05
>An: Kevin G
Scott Marlowe writes:
> That's a lot of work to get to COPY.
Well, yes. I though about it this way only after having read that OP is
uneasy with producing another format from his source data, and
considering it's a one-shot operation.
Ah, tradeoffs, how to find the right one!
--
dim
--
Sent
On Tue, Dec 8, 2009 at 2:08 AM, Dimitri Fontaine wrote:
> Hi,
>
> Ben Brehmer writes:
>> By "Loading data" I am implying: "psql -U postgres -d somedatabase -f
>> sql_file.sql". The sql_file.sql contains table creates and insert
>> statements. There are no
>> indexes present nor created during
Hi,
Ben Brehmer writes:
> By "Loading data" I am implying: "psql -U postgres -d somedatabase -f
> sql_file.sql". The sql_file.sql contains table creates and insert
> statements. There are no
> indexes present nor created during the load.
>
> OS: x86_64-unknown-linux-gnu, compiled by GCC gcc (G
On Tue, Dec 8, 2009 at 12:58 AM, Scott Marlowe wrote:
> On Tue, Dec 8, 2009 at 12:22 AM, Ben Brehmer wrote:
>> Thanks for all the responses. I have one more thought;
>>
>> Since my input data is split into about 200 files (3GB each), I could
>> potentially spawn one load command for each file. Wh
38 matches
Mail list logo
