ta as
inserts? This takes a lot more time to restore.
--
Brad Nicholson 416-673-4106
Database Administrator, Afilias Canada Corp.
---(end of broadcast)---
TIP 8: explain analyze is your friend
to do it.
Finally, am I correct in assuming that as long as the postmaster is shut down
moving the log is safe?
You are correct. Moving the WAL files with the postmaster running would
be a very bad thing.
--
Brad Nicholson 416-673-4106
Database Administrator, Afilias Canada Corp.
ntly.
Also, are you you using VACUUM FULL (if so, you certainly don't want to be).
--
Brad Nicholson 416-673-4106[EMAIL PROTECTED]
Database Administrator, Afilias Canada Corp.
---(end of broadcast)---
TIP 9: In versions below 8.0, the p
test for quite a bit longer. Get your
buffers dirty and exercised, and see what things look like then.
Also, if you have the disk, offload your wal files to a separate disk.
--
Brad Nicholson 416-673-4106
Database Administrator, Afilias Canada Corp.
---(end of broadcast)--
On Fri, 2007-02-16 at 20:01 +0530, Gauri Kanekar wrote:
>
> I want the planner to ignore a specific index.
> I am testing some query output. For that purpose i dont want the
> index.
> I that possible to ignore a index by the planner.
If the indexed field is an intger, add 0 to
on a separate partition.
Any ideas where the problem could lie? Could having the wal files on
the same data partition cause long running commits when there is plenty
of IO to spare?
--
Brad Nicholson 416-673-4106
Database Administrator, Afilias Canada Corp.
---(e
On Thu, 2007-09-13 at 10:15 -0400, Brad Nicholson wrote:
> I'm having a problem with long running commits appearing in my database
> logs. It may be hardware related, as the problem appeared when we moved
> the database to a new server connected to a different disk array. The
>
On Thu, 2007-09-13 at 11:10 -0400, Tom Lane wrote:
> Brad Nicholson <[EMAIL PROTECTED]> writes:
> > On Thu, 2007-09-13 at 10:15 -0400, Brad Nicholson wrote:
> >> I'm having a problem with long running commits appearing in my database
> >> logs. It may be har
On Thu, 2007-09-13 at 12:12 -0400, Greg Smith wrote:
> On Thu, 13 Sep 2007, Brad Nicholson wrote:
> I'd be curious to see how you've got your background writer configured to
> see if it matches situations like this I've seen in the past. The
> parameters controllin
On Thu, 2007-09-13 at 12:19 -0400, Brad Nicholson wrote:
> On Thu, 2007-09-13 at 12:12 -0400, Greg Smith wrote:
> > On Thu, 13 Sep 2007, Brad Nicholson wrote:
>
> > I'd be curious to see how you've got your background writer configured to
> > see if it matches
eporting.
A sysadmin looked at cache usage on the disk array. The read cache is
being used heavily, and the write cache is not.
--
Brad Nicholson 416-673-4106
Database Administrator, Afilias Canada Corp.
---(end of broadcast)---
TIP 3: Have you c
Unsure if this is
vendor specific bias or not, but considering the source (which not
vendor specific), I don't think so.
--
Brad Nicholson 416-673-4106
Database Administrator, Afilias Canada Corp.
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org
On Wed, 2010-03-17 at 09:11 -0400, Justin Pitts wrote:
> On Mar 17, 2010, at 9:03 AM, Brad Nicholson wrote:
>
> > I've been hearing bad things from some folks about the quality of the
> > FusionIO drives from a durability standpoint.
>
> Can you be more specific abo
unt of load on the server. That might
be something to watch of for for those that are testing them.
>
> On Mar 17, 2010, at 9:18 AM, Brad Nicholson wrote:
>
> > On Wed, 2010-03-17 at 09:11 -0400, Justin Pitts wrote:
> >> On Mar 17, 2010, at 9:03 AM, Brad Nichols
On Wed, 2010-03-17 at 14:11 -0400, Justin Pitts wrote:
> On Mar 17, 2010, at 10:41 AM, Brad Nicholson wrote:
>
> > On Wed, 2010-03-17 at 09:52 -0400, Justin Pitts wrote:
> >> FusionIO is publicly claiming 24 years @ 5TB/day on the 80GB SLC device,
> >> which wear
ql.org/docs/8.4/interactive/wal-configuration.html
--
Brad Nicholson 416-673-4106
Database Administrator, Afilias Canada Corp.
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
, bcoz my table has lot of
> updates and deletes. We also has the weekly maintance of VACUUM, but
> still reindex takes lot of time.
This is your problem. You should enable autovaccuum, let the vacuums
happen more frequently, and this problem will go away. You will still
have to fix the
on-Enterprise grade SSD's floating
around though.
--
Brad Nicholson 416-673-4106
Database Administrator, Afilias Canada Corp.
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
which
> allows them to run with write cache enabled. As a side effect - they
> are insanely expensive. :)
Texas Memory Systems also have these.
--
Brad Nicholson 416-673-4106
Database Administrator, Afilias Canada Corp.
--
Sent via pgsql-performance mailing list (pgsql-performance@postg
if anybody has seen that
> > behavior..
>
> I have trouble believing how synchronous_commit=off could be slower than
> 'on'.
>
I wonder if it could be contention on wal buffers?
Say I've turned synchronous_commit off, I drive enough traffic fill up
my wal_buffers. I assume that we would have to start writing buffers
down to disk before allocating to the new process.
--
Brad Nicholson 416-673-4106
Database Administrator, Afilias Canada Corp.
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
do think
that in both cases, having a solution that works, easily, out of the
"box" will meet the needs of most users.
There is also the issue of perception/adoption here as well. One of my
colleagues mentioned that at PG East that he repeatedly heard people
talking (negatively) about the
;
> 2) the above query creates a plan that does a sequential scan & filter on
> every partition. Why won't it only hit the correct partitions? Is it due to
> the way the date was specified? or maybe the "at time zone" syntax?
Do you have constraint_exclusion turned on?
--
Brad Nicholson 416-673-4106
Database Administrator, Afilias Canada Corp.
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
er failure.
So, hopefully there will be some interesting competition later this year in the
medium price range enterprise ssd market.
I'll be doing some testing on Enterprise grade SSD's this year. I'll
also be looking at some hybrid storage products that use as SSD's as
acceler
the drive fails and drops writes
on those, they could be rebuilt - assuming your system can function
without the index(es) temporarily.
--
Brad Nicholson 416-673-4106
Database Administrator, Afilias Canada Corp.
--
Sent via pgsql-performance mailing list (pgsql-performance@
On 8/10/2010 2:28 PM, Greg Smith wrote:
Brad Nicholson wrote:
What about putting indexes on them? If the drive fails and drops
writes on those, they could be rebuilt - assuming your system can
function without the index(es) temporarily.
Dumping indexes on SSD is one of the better uses for
it is not read only - controlled write may be more accurate).
In case of failure, a rebuild + resubscribe gets you back to the same
consistency. If you have high IO requirements, and don't have the
budget to rack up extra disk arrays to meet them, it could be an option.
--
Brad Nicho
On 8/10/2010 3:28 PM, Karl Denninger wrote:
Brad Nicholson wrote:
On 8/10/2010 2:38 PM, Karl Denninger wrote:
Scott Marlowe wrote:
On Tue, Aug 10, 2010 at 12:13 PM, Karl Denninger wrote:
ANY disk that says "write is complete" when it really is not is entirely
unsuitable fo
ays full of spinning disks to flash
based solutions the price comparison evens itself out even more.
--
Brad Nicholson 416-673-4106
Database Administrator, Afilias Canada Corp.
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
dm to
change to an outer join as for now.
You can also enable log_lock_waits and the lock waits will appear in
your Postgres logs.
--
Brad Nicholson 416-673-4106
Database Administrator, Afilias Canada Corp.
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To
getting bloated?
Also, is there a reason why you do nightly vacuums instead of letting
autovacuum handle the work? We started doing far less vacuuming when we
let autovacuum handle things.
--
Brad Nicholson 416-673-4106
Database Administrator, Afilias Canada Corp.
--
Sent via pgsql-perf
8.2 would bloat other tables.
--
Brad Nicholson 416-673-4106
Database Administrator, Afilias Canada Corp.
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
those
blocks from (and you can't this info from Postgres).
--
Brad Nicholson 416-673-4106
Database Administrator, Afilias Canada Corp.
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/
a warning turning fsync off, the dangers of async_commit, and
the potential problems with disabling full_page_writes might be worth
mentioning on this page, unless you want to leave that buried in the
attached references.
--
Brad Nicholson 416-673-4106
Database Administrator, Afilias Canad
n AIX can you trace why it is CPU bound? What else is taking the CPU
time, anything?
Also, can you provide the output of pg_config from your AIX build?
--
Brad Nicholson 416-673-4106
Database Administrator, Afilias Canada Corp.
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
y will speed up.
Can you post the output of explain analyze for that query on both AIX
and Linux? That will show where the time is being spent.
If it is being spent in the bitmap index scan, try setting
effective_io_concurrency to 0 for Linux, and see what effect that has.
--
Brad Nichols
FAICT from googling, AIX does have posix_fadvise, though maybe it
doesn't do anything useful ...
regards, tom lane
If there is an easy way to check if it does do anything useful? If so,
I can check it out.
--
Brad Nicholson 416-673-4106
Database Administrator, Afil
536 29 5 38 28
1 4 1548985 66491 0 0 0 1978 3215 0 2739 28291 22672 23 4 41 32
3 3 1548985 66422 0 0 0 1732 2469 0 2852 71865 30850 28 5 38 29
--
Brad Nicholson 416-673-4106
Database Administrator, Afilias Canada Corp.
---(e
Joshua D. Drake wrote:
Brad Nicholson wrote:
I'm investigating a potential IO issue. We're running 7.4 on AIX
5.1. During periods of high activity (reads, writes, and vacuums),
we are seeing iostat reporting 100% disk usage. I have a feeling
that the iostat numbers are misleadi
table, correct? I'm running 8.0.3.
VACUUM FULL requires an exclusive lock on the table that it's vacuuming.
Chances are something else has a lock on the table is blocking the
vacuum from obtaining the necessary lock. Check pg_locks for ungranted
locks, you'll probably find that t
ries back at the end of the month, but
> while you've got it it'd be awfully nice if we could use it as a testbed
We have PSeries boxes here that won't be going away anytime soon. If
there are any specific test cases that need to run, I should be able to
find the time to do i
s with indexes. Reindexing or clustering your tables might have
helped. Both are blocking operations.
How to avoid it in the future is simple. Upgrade to a modern version of
Postgres and vacuum your database properly. People work on this thing
for a reason :-)
--
Brad Nicholson 416-673-4106
D
transaction. Deal with those. Make sure every single transaction your
app initiates commits or rolls back every single time.
You'll generally find them in pg_stat_activity, but not always. ps may
show you idle transactions not showing as idle in pg_stat_activity
--
Brad Nich
On Mon, 2006-08-28 at 16:39 +0200, Michal Taborsky - Internet Mall
wrote:
> I just put together a view, which helps us in indentifying which
> database tables are suffering from space bloat, ie. they take up much
> more space than they actually should. I though this might be useful for
> some fo
On Thu, 2006-09-21 at 07:52 -0700, yoav x wrote:
> Hi
>
> After upgrading DBI and DBD::Pg, this benchmark still picks MySQL as the
> winner (at least on Linux
> RH3 on a Dell 1875 server with 2 hyperthreaded 3.6GHz CPUs and 4GB RAM).
> I've applied the following parameters to postgres.conf:
>
>
On Fri, 2006-09-22 at 13:14 -0400, Charles Sprickman wrote:
> Hi all,
>
> I still have an dual dual-core opteron box with a 3Ware 9550SX-12 sitting
> here and I need to start getting it ready for production. I also have to
> send back one processor since we were mistakenly sent two. Before I do
On Wed, 2006-10-04 at 07:38 -0500, Dave Dutcher wrote:
> > -Original Message-
> > From: [EMAIL PROTECTED]
> > [mailto:[EMAIL PROTECTED] On Behalf Of
> > Adnan DURSUN
> > i want to be can read an execution plan when
> > i look at it.
> > So, is there any doc about how it
On Tue, 2006-10-03 at 18:29 -0700, Tomeh, Husam wrote:
> >> * When any session updates the data that already in shared
> buffer,
> >>does Postgres synchronize the data both disk and shared buffers area
> >> immediately ?
>
> Not necessarily true. When a block is modified in the shared buff
> this kind of application?
> Reimer
Not that I'm aware of. Depending on what the problems transactions are,
setting up a replica on a separate machine and running those
transactions against the replica might be the solution.
--
Brad Nicholson 416-673-4106
Database Administ
ws, that could be
> costly.
Sounds to me like that could result in autovacuum kicking off while
doing large data loads. This sounds suspiciously like problem someone
on -novice was having - tripping over a windows autovac bug while doing
a data load
http://archives.postgresql.org/pgsql-novi
s.
We always checkpoint at the checkpoint timeout (every 5 minutes).
During this one checkpoint, I'm seeing transactions running 2-3 seconds.
During this time, writes are < 5/minute.
Relevant settings:
shared_buffers = 1
checkpoint_segments = 30
checkpoint_timeout = 300
What giv
On Thu, 2007-11-29 at 16:14 +, Simon Riggs wrote:
> On Thu, 2007-11-29 at 10:10 -0500, Brad Nicholson wrote:
> > I have a legacy system still on 7.4 (I know, I know...the upgrade is
> > coming soon).
> >
> > I have a fairly big spike happening once a day, every day
On Mon, 2008-04-28 at 19:35 +0530, Gauri Kanekar wrote:
> Peter,
>
> We are doing vacuum full every alternate day. We also do vacuum
> analyze very often.
> We are currently using 8.1.3 version.
> Auto vacuum is already on. But the table1 is so busy that auto vacuum
> don't get sufficient chance
#x27;d look, but what you really
need is to figure out the hit ratio on the buffer pool and go from
there.
> Does anyone have any suggestions per a better approach or maybe a way to
> improve the performance for the above query ?
You should be able to use the blocks hit vs block read data
over and over on my 8 core opteron server and
> it ran the load factor up by almost exactly 1.0. Under our normal
> daily load, it sits at 1.9 to 2.5, and it climbed to 2.9 under the new
> load of running that query over and over. So, it doesn't seem to be
> blocking or anything.
the lag increases between the two database.
You should sign up to the Slony list and ask your question there.
--
Brad Nicholson 416-673-4106
Database Administrator, Afilias Canada Corp.
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your
s bloat and eventual slowdowns to table access which manifest in
higher IO usage across the board.
If you really are dead set on vacuuming only at night, you may want to
do a careful analysis of which tables need to be vacuumed and when, and
trigger manual vacuums from cron.
--
Brad Nicholson 416-673-4106
Database Administrator, Afilias Canada Corp.
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
14.505 CUT [561402]DEBUG: forked new backend, pid=516338
socket=9
2009-03-04 09:56:14.506 CUT [561402]DEBUG: forked new backend, pid=2199744
socket=9
--
Brad Nicholson 416-673-4106
Database Administrator, Afilias Canada Corp.
--
Sent via pgsql-performance mailing list (pgsql-p
s
are the correct technology?
--
Brad Nicholson 416-673-4106
Database Administrator, Afilias Canada Corp.
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
Has anybody independently verified the results?
How many times have the run the plug test? I've read other reports of
people (not on Postgres) losing data on this drive with the write cache
on.
--
Brad Nicholson 416-673-4106
Database Administrator, Afilias Canada Corp.
--
Sent via
is you PG
version, no amount of investigation into other areas is going to change
that. Your company is simply wasting money by ignoring this and blindly
hoping that the problem will be something else.
It can be a difficult battle, but it can be won.
--
Brad Nicholson 416-673-4106
Database Administrator, A
his products - if you tell them and
> market dictades to react (see degeneration of performace before 1.11
> firmware).
>
> perhaps its time to act and not only to complain about the fact.
Or, you could just buy higher quality equipment that was designed with
this in mind.
There
ooking
at preventative maintenance instead of fixing it after its broken.
Ensure that autovacuum is running for the database (assuming that you
are on a relatively modern version of PG), and possibly tune it to be
more aggressive (we can help).
This will ensure that the condition never comes up.
p
tgres to the latest release, even if you have to do it from the
> source code, takes almost no time at all compared to the time you've already
> burned trying to solve this problem.
Actually, the biggest pain going beyond 8.2 is the change to implicit
casting.
> Do the upg
0
> | 6
> 2010-02-17 08:15:51.067886 | 789 | 0
> | 1
>
> perhaps some stats buffering occurring or something or some general
> misunderstanding of some of these tunables?
>
> --
> Jeff Trout
> http:
64 matches
Mail list logo
