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
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.
---(end
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 planner will ignore your desire
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.
---(end of broadcast
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 misleading. I can
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 the request from the vacuum is ungranted.
--
Brad Nicholson 416-673
, 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 it.
--
Brad Nicholson 416-673-4106
Database Administrator
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
Database Administrator, Afilias Canada Corp
. 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 Nicholson 416-673-4106
Database Administrator
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 folk
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 should be
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 buffers,
the
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 Administrator, Afilias Canada Corp
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)---
TIP 2: Don't 'kill
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 it.
--
Brad
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.
---(end
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
disk array is a lower
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 hardware related, as the problem appeared when we
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 controlling the all scan are the ones you'd
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 situations like this I've seen in the past
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 checked our extensive FAQ
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-novice/2007-11/msg00025.php
--
Brad Nicholson 416-673-4106
Database Administrator, Afilias Canada Corp
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 gives?
--
Brad Nicholson
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, at the same
time. It happens
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 to
query ?
You should be able to use the blocks hit vs block read data in the
pg_stat_database view (for the overall database), and drill down into
pg_statio_user_tables/pg_statio_all_tables to get more detailed data if
you want.
--
Brad Nicholson 416-673-4106
Database Administrator, Afilias Canada
. Needless to say, this is horrible
for concurrency. Must grab locks in increasing order to avoid
possible deadlocks.
I'd be concerned about that running routinely.
--
Brad Nicholson 416-673-4106
Database Administrator, Afilias Canada Corp.
--
Sent via pgsql-performance mailing list (pgsql
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 subscription:
http
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
-03-04 09:56: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
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
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 pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http
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, Afilias Canada Corp.
--
Sent via
on consumer grade HDD, I wouldn't run them on consumer
grade SSD either.
--
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
aggressive (we can help).
This will ensure that the condition never comes up.
ps - if you do go with the route specify, no need to VACUUM after the
CLUSTER. CLUSTER gets rid of the dead tuples - nothing for VACUUM to
do.
--
Brad Nicholson 416-673-4106
Database Administrator, Afilias Canada Corp
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 upgrade, you won't regret it.
Agree.
--
Brad Nicholson 416-673-4106
Database
of some of these tunables?
--
Jeff Trout j...@jefftrout.com
http://www.stuarthamm.net/
http://www.dellsmartexitin.com/
--
Brad Nicholson 416-673-4106
Database Administrator, Afilias Canada Corp.
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make
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)
To make changes to your subscription:
http://www.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 about that? Durability over what time
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 Nicholson wrote:
I've been hearing bad things from some folks about the quality of the
FusionIO drives from a durability
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 levels across 100GB of actual installed
/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
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 underlying bloat a last time though.
--
Brad Nicholson 416-673-4106
Database
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
Memory Systems also have these.
--
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
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
functionality.
--
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
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
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
accelerators mixed with lower cost storage.
--
Brad
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@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org
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
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 Nicholson 416-673-4106
Database Administrator
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 Denningerk...@denninger.net wrote:
ANY disk that says write is complete when it really is not is entirely
unsuitable
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:
http://www.postgresql.org/mailpref
will ask the sysadm 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
and/or indexes 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
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/pgsql
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 Canada Corp.
--
Sent via pgsql-performance
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
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 Nicholson 416-673-4106
Database Administrator, Afilias Canada Corp
accordingly.
AFAICT 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
64 matches
Mail list logo
