ctive_cache_size = 5000MB
default_statistics_target = 100
log_min_duration_statement = 1000
log_checkpoints = on
log_connections = on
log_disconnections = on
log_temp_files = 0
track_counts = on
autovacuum = on
log_autovacuum_min_duration = 0
Thanks for any ideas!
Rob
--
Sent via pgsql-perfor
Scott Marlowe wrote:
> On Tue, Feb 2, 2010 at 12:11 PM, Rob wrote:
>
>> postgresql.conf:
>> max_connections = 1100
>> work_mem = 32MB
>>
>
> 32MB * 1000 = 32,000MB... And that's if you max out connections and
> they each only do 1 sort.
Kevin Grittner wrote:
> Rob wrote:
>
>
>> 8gb ram
>> ~240 active databases
>> 800+ db connections via tcp.
>>
>
> 8 GB RAM divided by 800 DB connections is 10 MB per connection. You
> seriously need to find some way to use connection poolin
t=0.00..33.62 rows=945 width=8) (actual
time=8.189..8.202 rows=2 loops=1)
Filter: (istf = true)
-> Index Scan using i_destnum on current (cost=0.00..2149.40 rows=534
width=108) (actual time=62.365..517.454 rows=722 loops=2)
Index Cond: (current.destnum = &qu
There are 4 entries (wanted to make the playing field level for this
test). There are 2 with true for istf and 2 with false.
Rob
Stephan Szabo wrote:
> On Thu, 1 Mar 2007, Rob Schall wrote:
>
>
>> Question for anyone...
>>
>> I tried posting to the bugs, and
would benefit from most of
the table data it needed being prepopulated in shared buffers. Is this
the case?
--
Rob Wultsch
wult...@gmail.com
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref
On Wed, Mar 17, 2010 at 7:30 AM, Tom Lane wrote:
> Greg Smith writes:
>> Rob Wultsch wrote:
>>> At a minimum I assume that if both of the commands were started at
>>> about the same time they would each scan the table in the same
>>> direction and whichever cr
On Tue, May 25, 2010 at 4:26 PM, David Jarvis wrote:
> shared_buffers = 1GB
> temp_buffers = 32MB
> work_mem = 32MB
> maintenance_work_mem = 64MB
> effective_cache_size = 256MB
Shouldn't effective_cache_size be significantly larger?
--
Rob Wultsch
wult...@gmail.com
ql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>
I think it would be interesting to create a ram disk and insert into
it. In the MySQL community even thought MyISAM has fallen out of use
the Memory table (based on MyISAM) is still
e? Wouldn't a global temporary table have content that is not
visible between db connections? A db session many not be the same as a
user session.
--
Rob Wultsch
wult...@gmail.com
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
the crappy box lied about
fsync'ing data and your server is not. Did you purchase a raid card
with a bbu? If so, can you set the write cache policy to write-back?
--
Rob Wultsch
wult...@gmail.com
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
ted the issue.
>>
>>
>>
>> create table test_time (time timestamp);
>>
>> delete from test_time;
>>
>> insert into test_time select now();
>
>
> Use timeofday() instead, now() returns the transaction starting time.
Is this part of the SQL
gt; Header)
> GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431 2EB0 389D 1DC2 3172 0C99
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>
That is not true eit
ing a drop down menu for other version of the
manual for a page. I have not had time to write a patch, but I think it is
something that MySQL does better that pg.
As an example take a look at the page on select for MySQL:
http://dev.mysql.com/doc/refman/5.1/en/select.html .
If you want a earlier or later version they are easily accessible via a link
on the left.
--
Rob Wultsch
wult...@gmail.com
most obvious
> example where the atomic write implementation seems to always make disabling
> full_page_writes safe.
>
For the sake of argument, has PG considered using a double write
buffer similar to InnodB?
--
Rob Wultsch
wult...@gmail.com
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
On Fri, Oct 22, 2010 at 10:28 AM, Kevin Grittner
wrote:
> Rob Wultsch wrote:
>
>> has PG considered using a double write buffer similar to InnodB?
>
> That seems inferior to the full_page_writes strategy, where you only
> write a page twice the first time it is writt
On Fri, Oct 22, 2010 at 12:05 PM, Kevin Grittner
wrote:
> Rob Wultsch wrote:
>
>> I would think full_page_writes=off + double write buffer should be
>> far superior, particularly given that the WAL is shipped over the
>> network to slaves.
>
> For a reasonably brie
On Fri, Oct 22, 2010 at 1:15 PM, Kevin Grittner
wrote:
> Rob Wultsch wrote:
>
>> not needing full_page_writes would make geographically dispersed
>> replication possible for certain cases where it is not currently
>> (or at least rather painful).
>
> Do you have any
On Tue, Oct 26, 2010 at 5:41 AM, Robert Haas wrote:
> On Fri, Oct 22, 2010 at 3:05 PM, Kevin Grittner
> wrote:
>> Rob Wultsch wrote:
>>
>>> I would think full_page_writes=off + double write buffer should be
>>> far superior, particularly given that the WA
On Tue, Oct 26, 2010 at 7:25 AM, Robert Haas wrote:
> On Tue, Oct 26, 2010 at 10:13 AM, Rob Wultsch wrote:
>> The double write buffer is one of the few areas where InnoDB does more
>> IO (in the form of fsynch's) than PG. InnoDB also has fuzzy
>> checkpoints (which h
On Wed, Oct 27, 2010 at 6:55 PM, Robert Haas wrote:
> On Wed, Oct 27, 2010 at 12:41 AM, Rob Wultsch wrote:
>> On Tue, Oct 26, 2010 at 7:25 AM, Robert Haas wrote:
>>> On Tue, Oct 26, 2010 at 10:13 AM, Rob Wultsch wrote:
>>>> The double write buffer is one of the fe
removed?
--
Rob Wultsch
wult...@gmail.com
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
rmance when using commit_delay compared to the default.
>
> Anyway I would recommended right now to stick with the default and
> not really use it. It does the sync absorbtion well if you have two
> many users (though not perfect).
Sounds like this setting should go away unless there is
have a raid card? Is it properly configured?
--
Rob Wultsch
wult...@gmail.com
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
e an idea that
> avoids the problems that have been observed with other hint systems,
> that could lead to valuable discussion.
>
> That seems to me to characterize the nuance.
Where exactly are the problems with other systems noted? Most other
systems have this option so saying &quo
Merlin Moncure wrote:
> On 10/28/06, Simon Riggs <[EMAIL PROTECTED]> wrote:
>> On Thu, 2006-10-26 at 11:06 -0400, Merlin Moncure wrote:
>> > On 10/26/06, Carlo Stonebanks <[EMAIL PROTECTED]> wrote:
>> > > This is pretty interesting - where can I read more on this?
>> Windows isn't
>> > > actually h
the ram.
Don't take this as god's honest fact but just keep it in mind when
considering a Xeon solution, it may be worth your time to do some deeper
research into this. There is some on this here
http://www4.tomshardware.com/cpu/20030422/
Rob
---(end of br
-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of scott.marlowe
Sent: Tuesday, May 11, 2004 2:23 PM
To: Paul Tuckfield
Cc: [EMAIL PROTECTED]; Matthew Nuzum; [EMAIL PROTECTED]; Rob
Fielding
Subject: Re: [PERFORM] Configuring PostgreSQL to minimize impact of
had so much free
I/O we recently installed another app on there (based on mysql, sorry)
which runs concurrently, and itself 4 times faster than it originally did...
YMMV, just my 2p.
--
Rob Fielding
[EMAIL PROTECTED]
www.dsvr.co.uk Development Designer Servers Ltd
--
2
channel gets 40MB/sec max (pg_xlog :)
And HOW LONG does it take to detect drives during POSTo never
mind ... I really just wanna rant :) There should be a free counseling
service for enraged sysops.
--
Rob Fielding
[EMAIL PROTECTED]
www.dsvr.co.uk Development
ed the postmaster.
Kind of off-topic, but a Linux kernel parameter that's often not set on
database servers is elevator=deadline which sets up the io scheduling
algorithm. The algorithm can be viewed/set at runtime for example the
disk /dev/sdc in /sys/block/sdc/queue/scheduler.
Rob
27;ve figured out that the second plan is slow, because it is writing a
huge result set to disk (+200MB). This doesn't make sense to me,
since sort_mem is 32000.
Is there a way to tell the optimizer to use Nested Loop plan always
instead of the Merge/Join plan? Turning off enable_mergejoin is
. This
particular query has been one of the reasons. Maybe this change will
help us stay with Postgres.
Thanks,
Rob
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
eavily. With
Postgres, we just tried it and it worked. This is the first query
where we ran out of ideas to try.
BTW, everybody's help on this list is fantastic. Usually, I can find
the answer to my question (and have been doing so for 3 years) on this
list without asking.
Thanks,
Rob
ough
we are planning on adding more memory to production.
Rob
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
s were required.
Are there plans for explicit hints to the planner?
Thanks,
Rob
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
wn the app on Sat at 3 a.m and reimport with a fixed time period
than to live through reindexing/vacuuming which may deadlock. Am I
missing something?
Thanks,
Rob
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http:
e, but it seemed like the clients were locked for the entire
"reindex table bla" command.
Sorry for lack of detail, but I didn't expect these issues so I wasn't
keeping track of the system state as closely as I should have. Next
time. :-)
Thanks,
Rob
diosyncrasies without
changing the SQL. It would be like a style sheet in Latex (or Scribe :-)
if you are familiar with these typesetting languages.
Comments?
Rob
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
lues aren't changing, but other values
in the table are.
Any suggestions how to make vacuuming more effective and reducing the
time it takes to vacuum? I'd settle for less frequent vacuuming or
perhaps index rebuilding. The database can be re-i
wade through
5m rows.
Assuming I vacuum every 15 minutes, it would seem like max_fsm_pages
should be 1000, because that's about what was reclaimed. The default
is 1. Do I need to change this?
Sorry to be so dense, but I just don't know the right values
e'll see how it goes.
> ... or configure autovacuum to VACUUM a table when it has 10% dead
> tuples.
This solution doesn't really fix the fact that VACUUM consumes the
disk while it is running. I want to avoid the erratic performance on
my web server when
dor: DELL Model: PERCRAID Mirror Rev: V1.0
Type: Direct-AccessANSI SCSI revision: 02
Two 10K disks attached.
Rob
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
#x27;ll be running tests in the
coming month(s :-) with Oracle to see how it performs under the same
load and hardware. I'll keep this group posted.
Rob
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
But swapping out processes could account for
> the perceived slowdown in interactive response.
The box is a bit memory starved, and we'll be addressing that
shortly. I don't think it accounts for 3 minute queries, but perhaps
it might. vacuum_mem is 32mb, btw.
Rob
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
orce postgresql to use
the full index and still stick with the shorter "field_name in ('...',
'...')" syntax?
If anyone has any thoughts please let me know. Also it strikes me that
perhaps the optimizer could be tweaked to treat the first case like the
second one.
rom -w 128 -r
128 to -r 16 -w 8192. None of these mattered much when vacuum is
running.
This is a RAID1 box with two disks. Even with vacuum and one other
postmaster running, it's still got to get a lot of blocks through the
I/O system.
Rob
---(end of broadcast)--
win here.
That's usually our case. My company almost always has "groupware"
problems to solve. Every record has a "realm" (security) foreign key
and typically another key. The infrastructure puts the security
key on queries to avoid returning the wrong realm's data.
Rob
nly about 20 different values of f2, so it would be much
better to aggregate without sorting. This is the type of query which
runs while vacuum runs and I'm sure the two are just plain
incompatible. vacuum is read intensive and this query is write
intensive.
Rob
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
hits disk probably 3 times as much as the
simpler query judging by the amount of time this query takes (33 vs 88
secs). BTW, adding an index to t1.f2 has seriously negative effects
on many other DSS queries.
I'm still not sure that the sort problem is our only problem when
vacuum runs. It&
ually, but I'm not sure.
I'll be finding out this week.
> To convince it to do the right thing you would have to do either:
>
> SELECT a, t2.name
> FROM (SELECT avg(f1),f2 FROM t1 GROUP BY f2) AS t1
> JOIN t2 USING (f2)
>
> Or use a subquery
oth functionally
> equivalent.
I'll find out soon enough. :-)
Rob
---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
en complex queries,
oracle wins for our problem.
It looks like we'll be migrating to oracle for this project from these
preliminary results. It's not just the planner problems. The
customer is more familiar with oracle, and the vacuum performance is
another problem.
Rob
--
, and how we'll go
forward. We have budget in 2003 to spend on this, but only if the
situation can be resolved. Otherwise, we'll have to respect the data
we are seeing, and think about our choice of technologies.
Thanks for the feedback.
Rob
---(end of broadc
scott.marlowe writes:
> t2 was 'vacuum full'ed and analyzed, right? Just guessing.
Fresh import. I've been told this includes a ANALYZE.
Rob
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Tom Lane writes:
> Rob Nagler <[EMAIL PROTECTED]> writes:
> > q5 and q6 are too complex to discuss here,
>
> How do you expect us to get better if you don't show us the problems?
With all due respect and thanks for the massive amount of help, I have
presented the
45gb raid 5
array and 1gb of ram. I have read everything I could find on Pg performance
tweaked all the variables that were suggested and nothing. Which is why I
subscribed to this list, just been lurking so far but this caught my eye.
Rob
-Original Message-
From: [EMAIL PROTECTED]
[mai
For the record I am running on SuSE with a pretty much stock kernel. Not to
sound naïve, but is turning of HT something done in the bios?
Rob
-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Bill Moran
Sent: Friday, October 31, 2003 9:56 AM
To: [EMAIL
e
it was running so badly - i just wouldn't think it would do that.
--
Rob Fielding
[EMAIL PROTECTED]
www.dsvr.co.uk Development Designer Servers Ltd
---(end of broadcast)---
TIP 9: the planner will ignore your des
50 ) / 8.2 = shared_buffers
decrease random_page_cost to 0.3 and wack up sort mem by 16 times,
effective cache size to about 50% RAM (depending on your other settings)
and try that for starters.
--
Rob Fielding
[EMAIL PROTECTED]
www.dsvr.co.uk Development Designer S
er but for people trying to
tune their db...
Thanks
Rob
---(end of broadcast)---
TIP 8: explain analyze is your friend
1 29696 10228 16036 801368 0 0 4038 561 1566 3288 16
12 72
Sorry it's so long but I thought some brief info would be better than
not. Thanks for reading,
--
Rob Fielding
Development
Designer Servers Ltd
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
refer to keep as much RAM available for runtime at this time.
I'm still hoping that perhaps the uber-pgadmin Mr Lane might reply about
my WAL issue :) however I'm getting the feeling now the server is
running with a much higher level of performance than it has been. Won't
know
action log file 0071001F
Looks kinda automated, but the times aren't quite even at around 6-10
minutes apart.
cheers,
--
Rob Fielding
[EMAIL PROTECTED]
www.dsvr.co.uk Development Designer Servers Ltd
---(end of broadcast)--
L. Using a crash unsafe product will yield undesirable
results when a server crashes. It is also faster for many use cases.
InnoDB is crash safe. It is just that simple.
--
Rob Wultsch
wult...@gmail.com
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to y
flip bits and postgres does not checksum data,
so it will not automatically detect corruption for you. I would steer
well clear of SATA unless you are going to be using a fs like ZFS
which checksums data. I would hope that a SAN would detect this for
you, but I have no idea.
--
Rob Wultsch
wult...
t/versioning/
--
Rob Wultsch
wult...@gmail.com
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
32 bit pg should not matter.
Shared buffers and similar variables will be another matter.
Why the heck are you running 32 bit pg on a 64 bit system? You are
almost certainly doing it wrong.
--
Rob Wultsch
wult...@gmail.com
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresq
On Sun, Apr 21, 2013 at 5:46 AM, sunil virmani wrote:
> My DB version is little old - 8.1.18.
>
Your db is exceptionally old and very much unsupported. Vacuum has
massively improved since 8.1 .
See http://www.postgresql.org/support/versioning/ regarding supported
versions.
le_b_xref has an index on (table_a_id,
table_b_id). There're FK defined on the xref table, hence why I'm
deleting from it first.
Does anyone have any ideas as to what I can do to make the deletes any
faster? I'm running out of ideas!
Thanks in advance,
--
Rob Emery
--
Sent
f indexing strategy would one
use on that table? Doesn't make sense to do BTREE. Is there a better way to
structure it?
Any and all advice/tips/questions appreciated!
Thanks,
Rob
Hey all,
Lots of interesting suggestions! I'm loving it.
Just came back to this a bit earlier today and made a sample table to see
what non-index performance would be. Constructed data just like above (used
12M rows and 80% true for all 100 boolean columns)
Here's an analyze for what I'd expect
11'::"bit")
=
B'1'::"bit")
Rows Removed by Filter: 1191
Total runtime: 9359.593 ms
(4 rows)
*Time: 9360.072 ms*
On Thu, Apr 21, 2016 at 3:34 PM, Rob Imig wro
73 matches
Mail list logo
