Hi,
We recently upgraded our database from 9.1 to 9.6. We are seeing some
unusual slow queries after the upgrade.
Sometimes the queries are faster after vacuum analyze, but not consistent.
We tried with different settings of random_page_cost, work_mem,
effective_cache_size but the query results
On 2014/04/28 07:52 PM, Jeff Janes wrote:
On Mon, Apr 28, 2014 at 10:12 AM, Michael van Rooyen
mich...@loot.co.za mailto:mich...@loot.co.za wrote:
It looks like something is causing your IO to seize up briefly. It is
common for the sync phase of the checkpoint to do that, but that would
Michael van Rooyen mich...@loot.co.za writes:
I'm trying to get to the bottom of a performance issue on a server
running PostgreSQL 9.3.1 on Centos 5.
Hm ... it seems pretty suspicious that all of these examples take just
about exactly 1 second longer than you might expect. I'm wondering
if
On Mon, Apr 28, 2014 at 10:12 AM, Michael van Rooyen mich...@loot.co.zawrote:
I'm trying to get to the bottom of a performance issue on a server running
PostgreSQL 9.3.1 on Centos 5. The machine is a dual quad-core Xeon E5620
with 24GB ECC RAM and four enterprise SATA Seagate Constellation ES
On 2014/04/28 07:50 PM, Tom Lane wrote:
Michael van Rooyen mich...@loot.co.za writes:
I'm trying to get to the bottom of a performance issue on a server
running PostgreSQL 9.3.1 on Centos 5.
Hm ... it seems pretty suspicious that all of these examples take just
about exactly 1 second longer
Michael van Rooyen mich...@loot.co.za writes:
On 2014/04/28 07:50 PM, Tom Lane wrote:
Hm ... it seems pretty suspicious that all of these examples take just
about exactly 1 second longer than you might expect. I'm wondering
if there is something sitting on an exclusive table lock somewhere,
Ghislain ROUVIGNAC wrote:
I would leave default_statistics_target alone unless you see a lot of
estimates which are off by more than an order of magnitude. Even then, it
is often better to set a higher value for a few individual columns than for
everything.
We had an issue with a
Ghislain ROUVIGNAC wrote:
Memory : In use 4 Go, Free 15Go, cache 5 Go.
If the active portion of your database is actually small enough
that it fits in the OS cache, I recommend:
seq_page_cost = 0.1
random_page_cost = 0.1
cpu_tuple_cost = 0.05
I plan to increase various parameters as follow:
Hello,
I have a customer that experience a strange behaviour related to statictics.
Threre is a vacuum analyze planned during the night.
The morning, 1 day out of 2, there are some extremely slow queries.
Those queries lasts more than 5 minutes (never waited more and cancelled
them) whereas
Ghislain ROUVIGNAC wrote:
Threre is a vacuum analyze planned during the night.
The morning, 1 day out of 2, there are some extremely slow
queries. Those queries lasts more than 5 minutes (never waited
more and cancelled them) whereas when everything is OK they last
less than 300ms.
In
On 16 Listopad 2011, 5:27, Greg Smith wrote:
On 11/14/2011 01:16 PM, Cody Caughlan wrote:
We're starting to see some slow queries, especially COMMITs that are
happening more frequently. The slow queries are against seemingly
well-indexed tables.
Slow commits like:
2011-11-14 17:47:11 UTC
On 16 Listopad 2011, 2:21, Cody Caughlan wrote:
How did you build your RAID array? Maybe I have a fundamental flaw /
misconfiguration. I am doing it via:
$ yes | mdadm --create /dev/md0 --level=10 -c256 --raid-devices=4
/dev/xvdb /dev/xvdc /dev/xvdd /dev/xvde
$ pvcreate /dev/md0
$ vgcreate
On Nov 16, 2011, at 8:52 AM, Tomas Vondra wrote:
On 16 Listopad 2011, 2:21, Cody Caughlan wrote:
How did you build your RAID array? Maybe I have a fundamental flaw /
misconfiguration. I am doing it via:
$ yes | mdadm --create /dev/md0 --level=10 -c256 --raid-devices=4
/dev/xvdb /dev/xvdc
On 16 Listopad 2011, 18:31, Cody Caughlan wrote:
On Nov 16, 2011, at 8:52 AM, Tomas Vondra wrote:
On 16 Listopad 2011, 2:21, Cody Caughlan wrote:
How did you build your RAID array? Maybe I have a fundamental flaw /
misconfiguration. I am doing it via:
$ yes | mdadm --create /dev/md0
Dne 15.11.2011 01:13, Cody Caughlan napsal(a):
The first two are what I would think would be largely read operations
(certainly the SELECT) so its not clear why a SELECT consumes write
time.
Here is the output of some pg_stat_bgwriter stats from the last couple of
hours:
Dne 14.11.2011 22:58, Cody Caughlan napsal(a):
I ran bonnie++ on a slave node, doing active streaming replication but
otherwise idle:
http://batch-files-test.s3.amazonaws.com/sql03.prod.html
bonnie++ on the master node:
http://batch-files-test.s3.amazonaws.com/sql01.prod.html
If I am
On Tue, Nov 15, 2011 at 5:16 PM, Tomas Vondra t...@fuzzy.cz wrote:
Dne 14.11.2011 22:58, Cody Caughlan napsal(a):
I ran bonnie++ on a slave node, doing active streaming replication but
otherwise idle:
http://batch-files-test.s3.amazonaws.com/sql03.prod.html
bonnie++ on the master node:
On 16 Listopad 2011, 2:21, Cody Caughlan wrote:
How did you build your RAID array? Maybe I have a fundamental flaw /
misconfiguration. I am doing it via:
$ yes | mdadm --create /dev/md0 --level=10 -c256 --raid-devices=4
/dev/xvdb /dev/xvdc /dev/xvdd /dev/xvde
$ pvcreate /dev/md0
$ vgcreate
On 11/14/2011 01:16 PM, Cody Caughlan wrote:
We're starting to see some slow queries, especially COMMITs that are
happening more frequently. The slow queries are against seemingly
well-indexed tables.
Slow commits like:
2011-11-14 17:47:11 UTC pid:14366 (44/0-0) LOG: duration: 3062.784 ms
Hi, running Postgres 9.1.1 on an EC2 m1.xlarge instance. Machine is a
dedicated master with 2 streaming replication nodes.
The machine has 16GB of RAM and 4 cores.
We're starting to see some slow queries, especially COMMITs that are
happening more frequently. The slow queries are against
On 14 Listopad 2011, 19:16, Cody Caughlan wrote:
shared_buffers = 3584MB
wal_buffers = 16MB
checkpoint_segments = 32
max_wal_senders = 10
checkpoint_completion_target = 0.9
wal_keep_segments = 1024
maintenance_work_mem = 256MB
work_mem = 88MB
shared_buffers = 3584MB
effective_cache_size
Thanks for your response. Please see below for answers to your questions.
On Mon, Nov 14, 2011 at 11:22 AM, Tomas Vondra t...@fuzzy.cz wrote:
On 14 Listopad 2011, 19:16, Cody Caughlan wrote:
shared_buffers = 3584MB
wal_buffers = 16MB
checkpoint_segments = 32
max_wal_senders = 10
On 14 Listopad 2011, 22:58, Cody Caughlan wrote:
Seems reasonable, although I'd bump up the checkpoint_timeout (the 5m is
usually too low).
Ok, will do.
Yes, but find out what that means and think about the possible impact
first. It usually improves the checkpoint behaviour but increases the
On Mon, Nov 14, 2011 at 2:57 PM, Tomas Vondra t...@fuzzy.cz wrote:
On 14 Listopad 2011, 22:58, Cody Caughlan wrote:
Seems reasonable, although I'd bump up the checkpoint_timeout (the 5m is
usually too low).
Ok, will do.
Yes, but find out what that means and think about the possible impact
Tom Lane [...@sss.pgh.pa.us] wrote:
[ shrug... ] You tell us. To me it sounds a whole lot like some client
program sitting on an open transaction that has a nonexclusive lock on
the table to be dropped. That transaction wasn't necessarily doing any
useful work; it might have just been waiting
In my experience, 13833, IDLE in transaction is your culprit. It is a
transaction that has been there for 10 hours longer than all others, and is
doing nothing at all. It has locks on a lot of objects in there. You'll have
to take the oid's in the lock table and look them up in the pg_class
On Mon, Mar 2, 2009 at 1:22 PM, Brian Cox brian@ca.com wrote:
As you can see there are only 3 transactions and 1 starts 1 hour after
the drop begins. I'm still trying to figure out how to interpret the
pg_locks output, but (presumably) you/others on this forum have more
experience at this
Brian Cox brian@ca.com writes:
select locktype,database,relation,virtualxid,virtualtransaction,pid,mode
from pg_locks order by mode;
If you hadn't left out the granted column we could be more sure,
but what it looks like to me is the DROP (pid 13842) is stuck behind
the IDLE transaction
Tom Lane [...@sss.pgh.pa.us] wrote:
If you hadn't left out the granted column we could be more sure,
but what it looks like to me is the DROP (pid 13842) is stuck behind
the IDLE transaction (pid 13833). In particular these two rows of
pg_locks look like a possible conflict:
relation
Brian Cox brian@ca.com writes:
So, the idle transaction is the problem. Thanks to you, Scott Carey and
Robert Haas for pointing this out. However, why does the drop of
ts_defects_20090227 need exclusive access to ts_transets? I assume it
must be due to this FK?
alter table
Tom Lane [...@sss.pgh.pa.us] wrote:
Well, that's certainly a sufficient reason, if perhaps not the only
reason. Dropping ts_defects_20090227 will require removal of FK triggers
on ts_transets, and we can't do that concurrently with transactions that
might be trying to fire those triggers.
Now
On Mon, Mar 02, 2009 at 02:29:31PM -0500, Tom Lane wrote:
Brian Cox brian@ca.com writes:
select locktype,database,relation,virtualxid,virtualtransaction,pid,mode
from pg_locks order by mode;
If you hadn't left out the granted column we could be more sure,
but what it looks like to me
On Mon, Mar 2, 2009 at 2:24 PM, Tim Bunce tim.bu...@pobox.com wrote:
On Mon, Mar 02, 2009 at 02:29:31PM -0500, Tom Lane wrote:
Brian Cox brian@ca.com writes:
select locktype,database,relation,virtualxid,virtualtransaction,pid,mode
from pg_locks order by mode;
If you hadn't left out the
Brian Cox brian@ca.com writes:
Actually, they're all deadlocked. The question is why?
Probably because the DROP is trying to acquire exclusive lock on its
target table, and some other transaction already has a read or write
lock on that table, and everything else is queuing up behind the
Probably because the DROP is trying to acquire exclusive lock on its
target table, and some other transaction already has a read or write
lock on that table, and everything else is queuing up behind the DROP.
It's not a true deadlock that is visible to the database, or else
Postgres would have
Cox, Brian brian@ca.com writes:
Probably because the DROP is trying to acquire exclusive lock on its
target table, and some other transaction already has a read or write
lock on that table, and everything else is queuing up behind the DROP.
It's not a true deadlock that is visible to the
Actually, they're all deadlocked. The question is why?
Here's a brief background. The ts_defects table is partitioned by
occurrence date; each partition contains the rows for 1 day. When the
data gets old enough, the partition is dropped. Since the correct
partition can be determined from the
On Sat, Feb 28, 2009 at 9:51 PM, Brian Cox brian@ca.com wrote:
Actually, they're all deadlocked. The question is why?
Here's a brief background. The ts_defects table is partitioned by occurrence
date; each partition contains the rows for 1 day. When the data gets old
enough, the partition
I'm aware you already know that information_schema is slow [1] [2], so I
just want to expose/document another case and tests I did.
I'm using the following view to check what tables depend on what other
tables.
CREATE VIEW raw_relation_tree AS
SELECT
tc_p.table_catalog AS parent_catalog,
Octavio Alvarez alvar...@alvarezp.ods.org writes:
The result, on the above view: ~80ms. Fair enough. But if I apply a
condition:
SELECT * FROM ___pgnui_relation_tree.raw_relation_tree WHERE
parent_schema child_schema;
it takes ~2 seconds (!) to complete.
I'm not sure I'm seeing the exact
On Sat, 2009-02-14 at 15:02 -0500, Tom Lane wrote:
Octavio Alvarez alvar...@alvarezp.ods.org writes:
The result, on the above view: ~80ms. Fair enough. But if I apply a
condition:
SELECT * FROM ___pgnui_relation_tree.raw_relation_tree WHERE
parent_schema child_schema;
it takes ~2
I have a two column table with over 160 million rows in it. As the size
of the table grows queries on this table get exponentially slower. I am
using version 8.1.5 32-bit on Red Hat Enterprise Linux 3. The hardware
is an Intel 3 Ghz Xeon with 4GB RAM, and 6 disks in a RAID 5
configuration. For
Tyrrill, Ed wrote:
I have a two column table with over 160 million rows in it. As the size
of the table grows queries on this table get exponentially slower. I am
using version 8.1.5 32-bit on Red Hat Enterprise Linux 3. The hardware
is an Intel 3 Ghz Xeon with 4GB RAM, and 6 disks in a RAID
Tyrrill, Ed [EMAIL PROTECTED] writes:
Index Scan using backup_location_pkey on backup_location
(cost=0.00..1475268.53 rows=412394 width=8) (actual
time=3318.057..1196723.915 rows=2752 loops=1)
Index Cond: (backup_id = 1070)
Total runtime: 1196725.617 ms
If we take that at face value it
Tyrrill, Ed wrote:
mdsdb=# \d backup_location
Table public.backup_location
Column | Type | Modifiers
---+-+---
record_id | bigint | not null
backup_id | integer | not null
Indexes:
backup_location_pkey PRIMARY KEY, btree (record_id, backup_id)
Scott Marlowe [EMAIL PROTECTED] writes:
Secondly, it might be more efficient for the planner to choose the
backup_location_rid index than the combination primary key index.
Oh, I'm an idiot; I didn't notice the way the index was set up. Yeah,
that index pretty well sucks for a query on
Tom Lane [EMAIL PROTECTED] writes:
Scott Marlowe [EMAIL PROTECTED] writes:
Secondly, it might be more efficient for the planner to choose the
backup_location_rid index than the combination primary key index.
Oh, I'm an idiot; I didn't notice the way the index was set up.
Yeah, that
Tyrrill, Ed [EMAIL PROTECTED] writes:
Tom Lane [EMAIL PROTECTED] writes:
This combination of indexes:
Indexes:
backup_location_pkey PRIMARY KEY, btree (record_id, backup_id)
backup_location_rid btree (record_id)
is really just silly. You should have the pkey and then an index on
Tom Lane [EMAIL PROTECTED] writes:
Thanks for the help guys! That was my problem. I actually need the
backup_location_rid index for a different query so I am going to keep
it.
Well, you don't really *need* it; the two-column index on (record_id,
backup_id) will serve perfectly well for
Here is a simple test case for this strange behaviour :
annonces= CREATE TABLE test.current (id INTEGER PRIMARY KEY, description
TEXT);
INFO: CREATE TABLE / PRIMARY KEY creera un index implicite
current_pkey pour la table current
CREATE TABLE
annonces= CREATE TABLE test.archive
PFC [EMAIL PROTECTED] writes:
The IN() is quite small (150 values), but the two large tables are
seq-scanned... is there a way to avoid this ?
Not in 8.1. HEAD is a bit smarter about joins to Append relations.
regards, tom lane
---(end
On Tue, Apr 25, 2006 at 07:53:15PM +0200, PFC wrote:
What version is this??
annonces= EXPLAIN ANALYZE SELECT * FROM test.current WHERE id IN (SELECT
annonce_id FROM bookmarks WHERE list_id IN ('4'));
QUERY PLAN
=?ISO-8859-1?Q?=DCmit_=D6ztosun?= [EMAIL PROTECTED] writes:
Our application uses typical queries similar to following (very simplified):
SELECT
part_id,
part_name,
(SELECT
SUM(amount) FROM part_movements M
WHERE P.part_id =3D M.part_id
) as part_amount
FROM parts P
ORDER BY
DW wrote:
Hello,
I'm perplexed. I'm trying to find out why some queries are taking a long
time, and have found that after running analyze, one particular query
becomes slow.
This query is based on a view that is based on multiple left outer joins
to merge data from lots of tables.
If I
On 11/11/05, DW [EMAIL PROTECTED] wrote:
I'm perplexed. I'm trying to find out why some queries are taking a longtime, and have found that after running analyze,one particular querybecomes slow.
i have had exactly the same problem very recently.
what helped? increasing statistics on come column.
Hello,
I'm perplexed. I'm trying to find out why some queries are taking a long
time, and have found that after running analyze, one particular query
becomes slow.
This query is based on a view that is based on multiple left outer joins
to merge data from lots of tables.
If I drop the
DW [EMAIL PROTECTED] writes:
I'm perplexed. I'm trying to find out why some queries are taking a long
time, and have found that after running analyze, one particular query
becomes slow.
This implies that the planner's default choice of plan (without any
statistics) is better than its choice
Tom Lane wrote:
It would be interesting to see EXPLAIN ANALYZE results in both cases,
plus the contents of the relevant pg_stats rows. (BTW, you need not
dump and reload to get back to the virgin state --- just delete the
relevant rows from pg_statistic.) Also we'd want to know exactly what
DW [EMAIL PROTECTED] writes:
In the meantime, again I'm new to this -- I got pg_stats; which rows are
the relevent ones?
The ones for columns that are mentioned in the problem query.
I don't think you need to worry about columns used only in the SELECT
output list, but anything used in
On Fri, 2005-05-27 at 07:52 -0500, Josh Close wrote:
Setting shared buffers above something like 10-30% of memory is counter
productive.
What is the reason behind it being counter productive? If shared
buffers are at 30%, should effective cache size be at 70%? How do
those two relate?
I didn't see iostat as available to install, but I'm using dstat to see this.
The server has constant disk reads averaging around 50M and quite a
few in the 60M range. This is when selects are being done, which is
almost always. I would think if postgres is grabbing everything from
memory that
Josh Close [EMAIL PROTECTED] writes:
There is 2 gigs of mem in this server. Here are my current settings.
max_connections = 100
shared_buffers = 5
sort_mem = 4096
vacuum_mem = 32768
effective_cache_size = 45
Shared buffers is set to 10% of total mem. Effective cache size is 90% of
On 5/31/05, Martin Fandel [EMAIL PROTECTED] wrote:
In the documentation of
http://www.powerpostgresql.com/Downloads/annotated_conf_80.html
is the shared_buffers set to 1/3 of the availble RAM. You're set
5*8/1024=391 MB SHMEM. The effective_cache_size in your
configuration is
On 5/31/05, Martin Fandel [EMAIL PROTECTED] wrote:
In the documentation of
http://www.powerpostgresql.com/Downloads/annotated_conf_80.html
is the shared_buffers set to 1/3 of the availble RAM.
Well, it says you should never use more than 1/3 of your available RAM
which is not quite the same as
Hi,
I had some disk io issues recently with NFS, I found the command 'iostat
-x 5' to be a great help when using Linux.
For example here is the output when I do a 10GB file transfer onto hdc
Device:rrqm/s wrqm/s r/s w/s rsec/s wsec/srkB/swkB/s
avgrq-sz avgqu-sz await
On 5/26/05, Christopher Kings-Lynne [EMAIL PROTECTED] wrote:
I have some queries that have significan't slowed down in the last
couple days. It's gone from 10 seconds to over 2 mins.
The cpu has never gone over 35% in the servers lifetime, but the load
average is over 8.0 right now. I'm
Few mandatory questions:
1. Do you vacuum your db on regular basis? :)
It's vacuumed once every hour. The table sizes and data are constantly changing.
2. Perhaps statistics for tables in question are out of date, did you
try alter table set statistics?
No I haven't. What would that
Josh Close [EMAIL PROTECTED] writes:
this_sQuery := \'
SELECT COALESCE( SUM( iNumSent ), 0 ) AS iNumSent
FROM adaption.tblBatchHistory_\' || this_rServerIds.iId || \'
WHERE tStamp now() - interval \'\'5 mins\'\';
\';
Here is the explain
I think you really want that seqscan to be an indexscan, instead.
I'm betting this is PG 7.4.something? If so, probably the only
way to make it happen is to simplify the now() expression to a constant:
SELECT COALESCE( SUM( iNumSent ), 0 ) AS iNumSent
FROM
Doing the query
explain
SELECT COALESCE( SUM( iNumSent ), 0 ) AS iNumSent
FROM adaption.tblBatchHistory_1
WHERE tStamp ( now() - interval '5 mins' )::text
gives me this:
Aggregate (cost=32138.33..32138.33 rows=1 width=4)
- Seq Scan on tblbatchhistory_1 (cost=0.00..31996.10 rows=56891
I have some queries that have significan't slowed down in the last
couple days. It's gone from 10 seconds to over 2 mins.
The cpu has never gone over 35% in the servers lifetime, but the load
average is over 8.0 right now. I'm assuming this is probably due to
disk io.
I need some help setting up
Josh Close wrote:
I have some queries that have significan't slowed down in the last
couple days. It's gone from 10 seconds to over 2 mins.
The cpu has never gone over 35% in the servers lifetime, but the load
average is over 8.0 right now. I'm assuming this is probably due to
disk io.
I need
On 5/26/05, Josh Close [EMAIL PROTECTED] wrote:
I have some queries that have significan't slowed down in the last
couple days. It's gone from 10 seconds to over 2 mins.
The cpu has never gone over 35% in the servers lifetime, but the load
average is over 8.0 right now. I'm assuming this is
73 matches
Mail list logo
