Re: [PERFORM] Slow execution of SET ROLE, SET search_path and RESET ROLE

On 2017-11-07 18:48:14 +0100, Ulf Lohbrügge wrote: > Hi, > > 2017-11-07 16:11 GMT+01:00 Andres Freund <and...@anarazel.de>: > > > Hi, > > > > On 2017-11-07 11:11:36 +0100, Ulf Lohbrügge wrote: > > > I'm using PostgreSQL 9.5.9 on Debian and experien

Re: [PERFORM] Slow execution of SET ROLE, SET search_path and RESET ROLE

ot; That is weird. > Besides those peaks in statement duration, my application performs (i.e. > has acceptable response times) most of the time. > > Is there anything I can do to improve performance here? > Any help is greatly appreciated! Can you manually reproduce the problem? What times

Re: [PERFORM] query of partitioned object doesnt use index in qa

On September 15, 2017 1:42:23 PM PDT, Tom Lane wrote: >One thing you could consider doing about this is creating an index >on (body ->> 'SID'::text), which would prompt ANALYZE to gather >statistics >about that expression. Even if the index weren't actually used in the

Re: [PERFORM] Questionaire: Common WAL write rates on busy servers.

On 2017-04-28 01:29:14 +0200, Tomas Vondra wrote: > I can confirm this observation. I bought the Intel 750 NVMe SSD last year, > the device has 1GB DDR3 cache on it (power-loss protected), can do ~1GB/s of > sustained O_DIRECT sequential writes. But when running pgbench, I can't push > more than

Re: [PERFORM] Questionaire: Common WAL write rates on busy servers.

On 2017-04-27 10:29:48 -0700, Joshua D. Drake wrote: > On 04/27/2017 09:34 AM, Andres Freund wrote: > > On 2017-04-27 09:31:34 -0700, Joshua D. Drake wrote: > > > On 04/27/2017 08:59 AM, Andres Freund wrote: > > > > > > > I would agree it isn't yet a wide

Re: [PERFORM] Questionaire: Common WAL write rates on busy servers.

On 2017-04-27 09:31:34 -0700, Joshua D. Drake wrote: > On 04/27/2017 08:59 AM, Andres Freund wrote: > > > > > Ok, based on the, few, answers I've got so far, my experience is indeed > > skewed. A number of the PG users I interacted with over the last couple > &

Re: [PERFORM] Questionaire: Common WAL write rates on busy servers.

Hi, On 2017-04-24 21:17:43 -0700, Andres Freund wrote: > I've lately seen more and more installations where the generation of > write-ahead-log (WAL) is one of the primary bottlenecks. I'm curious > whether that's primarily a "sampling error" of mine, or whether that's &

[PERFORM] Questionaire: Common WAL write rates on busy servers.

_size (9.5+) / checkpoint_segments (< 9.5), checkpoint_timeout and wal_buffers? - Could you quickly describe your workload? Feel free to add any information you think is pertinent ;) Greetings, Andres Freund -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make ch

Re: [PERFORM] pgsql-performance issue

Hi, On 2016-08-20 08:38:43 +, debasis.mohar...@ipathsolutions.co.in wrote: > I have a PostgreSQL 9.5 instance running on Windows 8 machine with 4GB of > RAM.This server is mainly used for inserting/updating large amounts of data > via copy/insert/update commands, and seldom for running select

Re: [HACKERS] [PERFORM] 9.4 -> 9.5 regression with queries through pgbouncer on RHEL 6

On 2016-07-04 16:30:51 +0300, Vladimir Borodin wrote: > > > 13 июня 2016 г., в 21:58, Vladimir Borodin <r...@simply.name> написал(а): > > > >> > >> 13 июня 2016 г., в 0:51, Andres Freund <and...@anarazel.de > >> <mailto:and...@anarazel.de

Re: [HACKERS] [PERFORM] 9.4 -> 9.5 regression with queries through pgbouncer on RHEL 6

On 2016-06-13 21:58:30 +0300, Vladimir Borodin wrote: > > > 13 июня 2016 г., в 0:51, Andres Freund <and...@anarazel.de> написал(а): > > > > Hi Vladimir, > > > > Thanks for these reports. > > > > On 2016-06-13 00:42:19 +0300, Vladimir Borodi

Re: [PERFORM] 9.4 -> 9.5 regression with queries through pgbouncer on RHEL 6

ithout symbols > Warning: > Processed 537137 events and lost 7846 chunks! You can reduce the overhead by reducing the sampling frequency, e.g. by specifying -F 300. Greetings, Andres Freund -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make chan

Re: [PERFORM] 9.4 -> 9.5 regression with queries through pgbouncer on RHEL 6

Hi, On 2016-06-02 14:18:26 +0300, Антон Бушмелев wrote: > UP. repeat tests on local vm.. reults are discouraging > OSPG TPS AVG latency > Centos 7 9.5.3 23.711023 168.421 > Centos 7 9.5.3 26.609271 150.188 > Centos 7 9.5.3 25.220044

Re: [PERFORM] Queries intermittently slow

On 2016-01-07 13:34:51 -0500, Tom Lane wrote: > It's fairly well established that the implementation of transparent > huge pages in Linux kernels from the 2.6-or-so era sucks, and you're > best off turning it off if you care about consistency of performance. I think the feature wasn't introduced

Re: [PERFORM] Proposal for unlogged tables

On 2016-01-04 19:12:22 +0200, Mark Zealey wrote: > If there was a command to flush a specific unlogged table to disk it would > work around all these issues no? Perhaps if you marked the table as read > only at the same time it would flush it to disk and ensure no more data > could be written to

Re: [PERFORM] Proposal for unlogged tables

On 2016-01-04 16:38:40 +0200, Mark Zealey wrote: > I don't know how the internals work but unlogged tables definitely flushed > to disk and persist through normal server restarts. It is just according to > the docs if the server ever has an unclean shutdown the tables are truncated > even if they

Re: [PERFORM] One long transaction or multiple short transactions?

On 2015-10-17 10:26:01 -0500, Jim Nasby wrote: > Except inserts *do* take a lot of locks, just not user-level locks. > Operations like finding a page to insert into, seeing if that page is in > shared buffers, loading the page into shared buffers, modifying a shared > buffer, getting the relation

Re: [PERFORM] Having some problems with concurrent COPY commands

On 2015-10-13 07:14:01 -0700, Shaun Thomas wrote: > On Tue, Oct 13, 2015 at 2:32 AM, Heikki Linnakangas wrote: > > 80% of the CPU time is spent in the b-tree comparison function. > > In the logs, my duration per COPY command increases from about 1400ms > for one process to about

Re: [PERFORM] Having some problems with concurrent COPY commands

to lazy to write them up if not. Greetings, Andres Freund -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] Multi processor server overloads occationally with system process while running postgresql-9.4

connections are normally 50. This email is nearly impossible to read. But it sounds a bit like you need to disable transparent hugepages and/or zone_reclaim mode. Greetings, Andres Freund -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscr

Re: [PERFORM] Hmmm... why does CPU-intensive pl/pgsql code parallelise so badly when queries parallelise fine? Anyone else seen this?

On 2015-07-08 23:38:38 -0400, Tom Lane wrote: and...@anarazel.de (Andres Freund) writes: On 2015-07-08 15:38:24 -0700, Craig James wrote: From my admittedly naive point of view, it's hard to see why any of this matters. I have functions that do purely CPU-intensive mathematical

Re: [PERFORM] Hmmm... why does pl/pgsql code parallelise so badly when queries parallelise fine? Anyone else seen this?

On 2015-07-09 10:30:35 +, Graeme B. Bell wrote: Well, that requires reviewing the source code of the run script and such. No, of course it doesn't. It appears that you didn't look at the repo or read my previous mail before you wrote this. FFS, I *ran* some of the tests and

Re: [PERFORM] Hmmm... why does pl/pgsql code parallelise so badly when queries parallelise fine? Anyone else seen this?

On 2015-07-08 11:13:04 +, Graeme B. Bell wrote: I'm guessing you are maybe pressed for time at the moment because I already clearly included this on the last email, as well as the links to the alternative benchmarks with the same problem I referred to on both of my last emails which are

Re: [PERFORM] Hmmm... why does CPU-intensive pl/pgsql code parallelise so badly when queries parallelise fine? Anyone else seen this?

On 2015-07-08 13:46:53 -0500, Merlin Moncure wrote: On Wed, Jul 8, 2015 at 12:48 PM, Craig James cja...@emolecules.com wrote: On Tue, Jul 7, 2015 at 10:31 PM, Joshua D. Drake j...@commandprompt.com Using Apache Fast-CGI, you are going to fork a process for each instance of the function

Re: [PERFORM] Hmmm... why does CPU-intensive pl/pgsql code parallelise so badly when queries parallelise fine? Anyone else seen this?

On 2015-07-08 15:38:24 -0700, Craig James wrote: From my admittedly naive point of view, it's hard to see why any of this matters. I have functions that do purely CPU-intensive mathematical calculations ... you could imagine something like is_prime(N) that determines if N is a prime number. I

Re: [PERFORM] 9.5alpha1 vs 9.4

should not be the case. Could you show the query plan for this statement in both versions? Any chance that there's a parameter type mismatch for $1? Greetings, Andres Freund -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http

Re: [PERFORM] PostgreSQL disk fragmentation causes performance problems on Windows

the budget for this, then please let's talk about it because right now nobody is working on it. I think this is overestimating the required effort quite a bit. While not trivial, it's also not that complex to make this work. Greetings, Andres Freund -- Sent via pgsql-performance mailing list (pgsql

Re: [PERFORM] PostgreSQL disk fragmentation causes performance problems on Windows

On 2015-04-29 10:06:39 +0200, Andres Freund wrote: Hi, On 2015-04-23 19:47:06 +, Jan Gunnar Dyrset wrote: I am using PostgreSQL to log data in my application. A number of rows are added periodically, but there are no updates or deletes. There are several applications that log

Re: [PERFORM] PostgreSQL disk fragmentation causes performance problems on Windows

that happens only in the right cirumstances. Greetings, Andres Freund -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] MusicBrainz postgres performance issues

would a fault batter possibly cause this? Many controllers disable write-back caching when the battery is dead. Greetings, Andres Freund -- Andres Freund http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-performance

Re: [PERFORM] MusicBrainz postgres performance issues

mostly workload. Greetings, Andres Freund -- Andres Freund http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http

Re: [PERFORM] MusicBrainz postgres performance issues

care of most of the other disadvantages. Greetings, Andres Freund -- Andres Freund http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your

Re: [PERFORM] MusicBrainz postgres performance issues

On 2015-03-15 20:42:51 +0300, Ilya Kosmodemiansky wrote: On Sun, Mar 15, 2015 at 8:20 PM, Andres Freund and...@2ndquadrant.com wrote: On 2015-03-15 11:09:34 -0600, Scott Marlowe wrote: shared_mem of 12G is almost always too large. I'd drop it down to ~1G or so. I think that's

Re: [PERFORM] MusicBrainz postgres performance issues

a full postgres backend, with all it's overhead, but use a much more lightweight pooler connection. Greetings, Andres Freund -- Andres Freund http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-performance mailing list

Re: [PERFORM] MusicBrainz postgres performance issues

On 2015-03-15 20:54:51 +0300, Ilya Kosmodemiansky wrote: On Sun, Mar 15, 2015 at 8:46 PM, Andres Freund and...@2ndquadrant.com wrote: That imo doesn't really have anything to do with it. The primary benefit of a BBU with writeback caching is accelerating (near-)synchronous writes. Like

Re: [PERFORM] MusicBrainz postgres performance issues

the search for replacement buffers is cheaper with a smaller shared buffers setting. Greetings, Andres Freund -- Andres Freund http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-performance mailing list (pgsql

Re: [PERFORM] Performance bug in prepared statement binding in 9.2?

endpoints. Greetings, Andres Freund -- Andres Freund http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http

Re: [PERFORM] Postgres slave not catching up (on 9.2)

bound? 3) Does the workload involve loads of temporary tables or generally transactions locking lots of tables exclusively in one transaction? Greetings, Andres Freund -- Andres Freund http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services

Re: [PERFORM] Turn off Hyperthreading! WAS: 60 core performance with 9.3

this. I'm not sure if it has been backported by redhat, but there definitely have been significant improvement in SMT aware scheduling after vanilla 2.6.32. Greetings, Andres Freund -- Andres Freund http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training

Re: [PERFORM] query against pg_locks leads to large memory alloc

max_pred_locks_per_transaction is less than 30k. What was the precise error message when that happened? Greetings, Andres Freund -- Andres Freund http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-performance mailing list

Re: [PERFORM] 60 core performance with 9.3

On 2014-07-11 12:40:15 +1200, Mark Kirkwood wrote: On 01/07/14 22:13, Andres Freund wrote: On 2014-07-01 21:48:35 +1200, Mark Kirkwood wrote: - cherry picking the last 5 commits into 9.4 branch and building a package from that and retesting: Clients | 9.4 tps 60 cores (rwlock

Re: [PERFORM] Postgres Replaying WAL slowly

relations. Say DISCARD TEMP inside a transaction (with several subxacts) or so? So we probaly really should fix the bad scaling. Greetings, Andres Freund -- Andres Freund http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql

Re: [PERFORM] 60 core performance with 9.3

On 2014-07-01 21:48:35 +1200, Mark Kirkwood wrote: On 27/06/14 21:19, Andres Freund wrote: On 2014-06-27 14:28:20 +1200, Mark Kirkwood wrote: My feeling is spinlock or similar, 'perf top' shows kernel find_busiest_group kernel _raw_spin_lock as the top time users. Those don't tell

Re: [PERFORM] Postgres Replaying WAL slowly

was to build with -fno-omit-frame-pointer - that normally shows the callers, even if it can't generate a proper symbol name. Soni: Do you use Hot Standby? Are there connections active while you have that problem? Any other processes with high cpu load? Greetings, Andres Freund -- Andres

Re: [PERFORM] Postgres Replaying WAL slowly

large amounts of temporary relations? Greetings, Andres Freund -- Andres Freund http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your

Re: [PERFORM] Postgres Replaying WAL slowly

pg_database WHERE datname = current_database()); Yah, i thought about that too, but verified I am in the correct DB. Just for clarity sake: So these are probably relations created in uncommitted transactions. Possibly ON COMMIT DROP temp tables? Greetings, Andres Freund -- Andres

Re: [PERFORM] Postgres Replaying WAL slowly

be better to implement ReleaseLocksTree() by sorting the subxid list and bsearch that while iterating RecoveryLockList. Greetings, Andres Freund -- Andres Freund http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-performance

Re: [PERFORM] 60 core performance with 9.3

at least give the callers for kernel level stuff. For more information compile postgres with -fno-omit-frame-pointer. Greetings, Andres Freund -- Andres Freund http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-performance

Re: [PERFORM] Sudden crazy high CPU usage

the output using perf report without redirect into a file, you'll get an interactive UI. Greetings, Andres Freund -- Andres Freund http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-performance mailing list (pgsql

Re: [PERFORM] Parallel Select query performance and shared buffers

cached. From what I've seen so far the bigger problem than contention in the lwlocks itself, is the spinlock protecting the lwlocks... Greetings, Andres Freund -- Andres Freund http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent

Re: [PERFORM] Parallel Select query performance and shared buffers

, and release the spinlock again. In lots of workloads that internal spinlock is the contention point, not the lenght over which the lwlock is held. Especially when they are mostly held in shared mode. Makes sense? Greetings, Andres Freund -- Andres Freund http://www

Re: [PERFORM] [HACKERS] Parallel Select query performance and shared buffers

is often transportable to the virtual world. Greetings, Andres Freund -- Andres Freund http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your

Re: [PERFORM] [HACKERS] Parallel Select query performance and shared buffers

. Alternatively it's scheduler overhead, due to superflous context switches around the buffer mapping locks. I'd strongly suggest doing a perf record -g -a wait a bit, ctrl-c; perf report run to check what's eating up the time. Greetings, Andres Freund -- Andres Freund http

Re: [PERFORM] [HACKERS] Parallel Select query performance and shared buffers

] [k] hypercall_page + 6.80% postgres [kernel.kallsyms] [k] xen_set_pte_at All that time is spent in your virtualization solution. One thing to try is to look on the host system, sometimes profiles there can be more meaningful. Greetings, Andres Freund -- Andres Freund

Re: [PERFORM] [HACKERS] Parallel Select query performance and shared buffers

On 2013-12-04 16:00:40 -0200, Claudio Freire wrote: On Wed, Dec 4, 2013 at 1:54 PM, Andres Freund and...@2ndquadrant.com wrote: All that time is spent in your virtualization solution. One thing to try is to look on the host system, sometimes profiles there can be more meaningful. You

Re: [PERFORM] [HACKERS] Parallel Select query performance and shared buffers

improvement patches - for some workloads here, the improvements have been rather noticeable. Which version are you testing? Greetings, Andres Freund -- Andres Freund http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql

Re: [PERFORM] Planner performance extremely affected by an hanging transaction (20-30 times)?

- this was just about the additional ProcArrayLock contention. I don't think it would change anything dramatical in your case. Greetings, Andres Freund -- Andres Freund http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql

Re: [PERFORM] Cpu usage 100% on slave. s_lock problem.

On 2013-08-27 12:17:55 -0500, Merlin Moncure wrote: On Tue, Aug 27, 2013 at 10:55 AM, Andres Freund and...@2ndquadrant.com wrote: On 2013-08-27 09:57:38 -0500, Merlin Moncure wrote: + bool + RecoveryMightBeInProgress(void) + { + /* + * We check shared state each time only

Re: [PERFORM] Planner performance extremely affected by an hanging transaction (20-30 times)?

the situation. Greetings, Andres Freund -- Andres Freund http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http

Re: [PERFORM] Planner performance extremely affected by an hanging transaction (20-30 times)?

On 2013-09-25 11:17:51 -0700, Jeff Janes wrote: On Wed, Sep 25, 2013 at 10:53 AM, Andres Freund and...@2ndquadrant.comwrote: On 2013-09-25 00:06:06 -0700, Jeff Janes wrote: On 09/20/2013 03:01 PM, Jeff Janes wrote: 3) Even worse, asking if a given transaction has finished yet can

Re: [PERFORM] Cpu usage 100% on slave. s_lock problem.

is normally an indication that you have too many longrunning transactions around preventing hot pruning from working. Greetings, Andres Freund -- Andres Freund http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-performance

Re: [PERFORM] Cpu usage 100% on slave. s_lock problem.

On 2013-09-17 08:18:54 -0500, Merlin Moncure wrote: On Tue, Sep 17, 2013 at 6:59 AM, Andres Freund and...@2ndquadrant.com wrote: Hi, On 2013-09-17 17:55:01 +0600, Дмитрий Дегтярёв wrote: We have not been able to reproduce this problem on a test servers. Use this patch to production

Re: [PERFORM] Cpu usage 100% on slave. s_lock problem.

On 2013-09-17 08:32:30 -0500, Merlin Moncure wrote: On Tue, Sep 17, 2013 at 8:24 AM, Andres Freund and...@2ndquadrant.com wrote: On 2013-09-17 08:18:54 -0500, Merlin Moncure wrote: Do you think it's worth submitting the lock avoidance patch for formal review? You mean the bufmgr.c

Re: [PERFORM] Cpu usage 100% on slave. s_lock problem.

. The THP issues should be very clearly diagnosable because a good part of the time will be spent in the kernel. Lots of spinlocking there, but the function names are easily discernible from pg's code. Greetings, Andres Freund -- Andres Freund http://www.2ndQuadrant.com

Re: [PERFORM] Intermittent hangs with 9.2

perf -g. The performance price of that usually is below 1% for postgres. Greetings, Andres Freund -- Andres Freund http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-performance mailing list (pgsql-performance

Re: [PERFORM] Performance bug in prepared statement binding in 9.2?

to papering over actual issues with applications leaving transactions open. I don't really see a valid reason for an application needing cancelling of long idle transactions. Greetings, Andres Freund -- Andres Freund http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7

Re: [PERFORM] Performance bug in prepared statement binding in 9.2?

introduced a planner feature. Funnily you seem to have been the trigger for it's introduction ;) I'm also confused as to why this would affect BIND time rather than EXECUTE time. Because we're doing the histogram checks during planning and not during execution. Greetings, Andres Freund -- Andres

Re: [PERFORM] Performance bug in prepared statement binding in 9.2?

that you'll see get_actual_variable_range() as the entry point here. Which would explain why you're seing this during PARSE. But there still is the question why we never actually seem to prune... Greetings, Andres Freund -- Andres Freund http://www.2ndQuadrant.com/ PostgreSQL

Re: [PERFORM] Performance bug in prepared statement binding in 9.2?

... Greetings, Andres Freund -- Andres Freund http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org

Re: [PERFORM] Performance bug in prepared statement binding in 9.2?

On 2013-09-10 08:45:33 -0400, Andrew Dunstan wrote: On 09/10/2013 08:20 AM, Andres Freund wrote: A backtrace for this would be useful. Alternatively you could recompile postgres using -fno-omit-frame-pointer in CFLAGS and use perf record -g. It's using a custom build, so this should

Re: [PERFORM] Performance bug in prepared statement binding in 9.2?

On 2013-09-10 15:21:33 +0200, Andres Freund wrote: If I interpret things correctly you're using serializable? I guess there is no chance to use repeatable read instead? Err, that wouldn't help much. Read committed. That lets PGXACT-xmin advance these days and thus might help to reduce

Re: [PERFORM] Cpu usage 100% on slave. s_lock problem.

, Andres Freund -- Andres Freund http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql

Re: [PERFORM] Partitions not Working as Expected

something here? Greetings, Andres Freund -- Andres Freund http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http

Re: [PERFORM] Setting vacuum_freeze_min_age really low

On 2013-05-13 13:21:54 -0400, Robert Haas wrote: On Sun, May 12, 2013 at 8:50 AM, Andres Freund and...@2ndquadrant.com wrote: [ a response that I entirely agree with ] +1 to all that. It's maybe worth noting that it's probably fairly uncommon for vacuum to read a page and not dirty

Re: [PERFORM] Setting vacuum_freeze_min_age really low

Freund -- Andres Freund http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql

Re: [PERFORM] Setting vacuum_freeze_min_age really low

buffers than before, even if they are actively used). Makes sense? Greetings, Andres Freund -- Andres Freund http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org

Re: [PERFORM] Setting vacuum_freeze_min_age really low

for debugging problems. Most problems don't get noticed within minutes so loosing evidence that fast is bad. Greetings, Andres Freund -- Andres Freund http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-performance

Re: [PERFORM] 9.2.1 index-only scans : abnormal heap fetches after VACUUM FULL

vacuum without any benefits... Greetings, Andres -- Andres Freund http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http

Re: [PERFORM] 9.2.1 index-only scans : abnormal heap fetches after VACUUM FULL

On 2012-11-29 17:59:39 +0530, Pavan Deolasee wrote: On Thu, Nov 29, 2012 at 5:42 PM, Andres Freund and...@2ndquadrant.comwrote: On 2012-11-29 17:20:01 +0530, Pavan Deolasee wrote: Now can CLUSTER or VACUUM FULL recreate the visibility map with all bits set to visible, thats an entirely

Re: [PERFORM] Poor performance using CTE

apps where they have used them for that purpose. And I continue to think that spelling it OFFSET 0 is horribly obscure. +1 WITH foo AS (SELECT ...) (barrier=on|off)? 9.3 introduces the syntax, defaulting to on 9.4 switches the default to off. Greetings, Andres Freund -- Sent via pgsql

Re: [PERFORM] Poor performance using CTE

On 2012-11-21 13:16:25 -0300, Claudio Freire wrote: On Wed, Nov 21, 2012 at 12:32 PM, Andres Freund and...@anarazel.de wrote: +1 WITH foo AS (SELECT ...) (barrier=on|off)? 9.3 introduces the syntax, defaulting to on 9.4 switches the default to off. Why syntax? What about a guc

Re: [PERFORM] Poor performance using CTE

On 2012-11-21 13:32:45 -0300, Claudio Freire wrote: On Wed, Nov 21, 2012 at 1:24 PM, Andres Freund and...@2ndquadrant.com wrote: On 2012-11-21 13:16:25 -0300, Claudio Freire wrote: On Wed, Nov 21, 2012 at 12:32 PM, Andres Freund and...@anarazel.de wrote: +1 WITH foo AS (SELECT

Re: [PERFORM] Increasing work_mem and shared_buffers on Postgres 9.2 significantly slows down queries

. As you restrict on 'pi', the rightmost table in a chain of left joins, there is no point in all those left joins. I would guess the overall plan is better if use straight joins. Greetings, Andres Freund -- Andres Freund http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7

Re: [PERFORM] how to avoid deadlock on masive update with multiples delete

BY for DML. Andres -- Andres Freund http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql

Re: [PERFORM] how to avoid deadlock on masive update with multiples delete

On Friday, October 05, 2012 05:46:05 PM Tom Lane wrote: Andres Freund and...@2ndquadrant.com writes: On Friday, October 05, 2012 05:31:43 PM Tom Lane wrote: There's no guarantee that the planner won't re-sort the rows coming from the sub-select, unfortunately. More often than not you

Re: [PERFORM] Postgres becoming slow, only full vacuum fixes it

On Monday, September 24, 2012 02:21:09 PM Julien Cigar wrote: 5) synchronous_commit = off should only be used if you have a battery-backed write cache. Huh? Are you possibly confusing this with full_page_writes? Greetings, Andres -- Andres Freund http://www

Re: [PERFORM] Postgres becoming slow, only full vacuum fixes it

On Monday, September 24, 2012 02:53:59 PM Julien Cigar wrote: On 09/24/2012 14:34, Andres Freund wrote: On Monday, September 24, 2012 02:21:09 PM Julien Cigar wrote: 5) synchronous_commit = off should only be used if you have a battery-backed write cache. Huh? Are you possibly

Re: [PERFORM] transactions start time

backend connection. Aleksei confirmed that they use pgbouncer in that configuration, so that might be it. Andres -- Andres Freund http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-performance mailing list (pgsql-performance

Re: [PERFORM] Massive I/O spikes during checkpoint

which haven't been written to for dirty_expire_centisecs. But yes, adjusting dirty_* is definitely a good idea. Andres -- Andres Freund http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-performance mailing list (pgsql

Re: [PERFORM] Massive I/O spikes during checkpoint

On Tuesday, July 10, 2012 03:36:35 PM Jeff Janes wrote: On Tue, Jul 10, 2012 at 5:44 AM, Andres Freund and...@2ndquadrant.com wrote: On Tuesday, July 10, 2012 08:14:00 AM Maxim Boguk wrote: So kernel doesn't start write any pages out in background before it has at least 13Gb dirty pages

Re: [PERFORM] PostgreSQL db, 30 tables with number of rows 100 (not huge) - the fastest way to clean each non-empty table and reset unique identifier column of empty ones.

. Otherwise rollback would be pretty hard to implement. I guess the biggest cost in a bigger cluster is the dropping the buffers that were formerly mapped to that relation (DropRelFileNodeBuffers). Andres -- Andres Freund http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7

Re: [PERFORM] Maximum number of sequences that can be created

On Tuesday, May 15, 2012 08:29:11 AM Віталій Тимчишин wrote: 2012/5/13 Robert Klemme shortcut...@googlemail.com On Sun, May 13, 2012 at 10:12 AM, Віталій Тимчишин tiv...@gmail.com wrote: 2012/5/11 Robert Klemme shortcut...@googlemail.com On the contrary: what would be the

Re: [PERFORM] Fwd: [HACKERS] client performance v.s. server statistics

Hi, On Wednesday, February 15, 2012 11:19:00 AM Zhou Han wrote: I have tried unix domain socket and the performance is similar with TCP socket. It is MIPS architecture so memory copy to/from kernel can occupy much time, and apparently using unit domain socket has no difference than TCP in

Re: [PERFORM] Fwd: [HACKERS] client performance v.s. server statistics

On Wednesday, February 15, 2012 12:33:13 PM Han Zhou wrote: Hi, To be more specific, I list my calculation here: The timing shown in psql may include: plan + execution + copying to result set in backend (does this step exist?) + transferring data to client via socket. Correct. Then I want

Re: [PERFORM] STRICT SQL functions never inline

On Tuesday, November 08, 2011 15:29:03 Josh Berkus wrote: Folks, After having some production issues, I did some testing and it seems that any SQL function declared STRICT will never inline. As a result, it won't work with either indexes (on the underlying predicate) or partitioning.

Re: [PERFORM] Guide to PG's capabilities for inlining, predicate hoisting, flattening, etc?

On Wednesday 02 Nov 2011 16:13:09 Robert Haas wrote: On Wed, Nov 2, 2011 at 10:38 AM, Tom Lane t...@sss.pgh.pa.us wrote: Jay Levitt jay.lev...@gmail.com writes: So you can see where I'm going. I know if I break everything into elegant, composable functions, it'll continue to perform

Re: [PERFORM] Slow cursor

Hi, On Wednesday 26 Oct 2011 14:43:08 Cezariusz Marek wrote: Is there any known problem with slow cursors in PostgreSQL 8.4.5? I have a following query, which is slow (on my database it takes 11 seconds to execute), probably should be rewritten, but it doesn't matter here. The problem is,

Re: [PERFORM] Rather large LA

On Monday 05 Sep 2011 22:23:32 Scott Marlowe wrote: On Mon, Sep 5, 2011 at 11:24 AM, Andres Freund and...@anarazel.de wrote: On Monday, September 05, 2011 14:57:43 Richard Shaw wrote: Autovacuum has been disabled and set to run manually via cron during a quiet period and fsync has recently

Re: [PERFORM] Rather large LA

On Monday, September 05, 2011 14:57:43 Richard Shaw wrote: Autovacuum has been disabled and set to run manually via cron during a quiet period and fsync has recently been turned off to gauge any real world performance increase, there is battery backup on the raid card providing some level of

Re: [PERFORM] setting configuration values inside a stored proc

Hi, On Friday, May 13, 2011 01:10:19 AM Samuel Gendler wrote: I've got a stored proc that constructs some aggregation queries as strings and then executes them. I'd like to be able to increase work_mem before running those queries. If I set a new value for work_mem within the stored proc

  1   2   >