Re: [GENERAL] pg_last_xact_replay_timestamp() sometimes reports unlikely, very large delays

- Original Message - > > On Mar 22, 2017, at 8:06 PM, Toby Corkindale > > wrote: > > > > My best guess for what is going on is: > > - There has been no activity for hours or days, and so the oldest replayed > > transaction on the slave is genuinely qui

[GENERAL] pg_last_xact_replay_timestamp() sometimes reports unlikely, very large delays

Hi all, I wondered if any experts can help me out? I currently monitor Postgresql's replication status by looking at two metrics. First I check to see if the current slave xlog replay is equal to the master -- if so, it's up to date. If it's not equal, then I look at pg_last_xact_replay_timestamp

Re: [GENERAL] Two-way encryption

- Original Message - > From: "Patrick Simcoe" > To: pgsql-general@postgresql.org > Sent: Wednesday, 2 July, 2014 1:42:04 AM > Subject: [GENERAL] Two-way encryption > > I have a question regarding two-way encryption data for specific columns. > > Does anyone have a technique or recommenda

Re: [GENERAL] pl/perl and recent perl versions - failing to load internal modules

EXT: PL/Perl anonymous code block Oddly, even if I add utf8 to plperl.oninit, I then get an error about being unable to load the 're' (regex) module. I don't understand how the example you gave does manage to work! - Original Message - > From: "Alex Hunsaker" >

Re: [GENERAL] pl/perl and recent perl versions - failing to load internal modules

Hi Alex, => do $$ "\N{U+263A}" =~ /[[:punct:]]/ $$ language plperl; ERROR: Unable to load utf8.pm into plperl at line 1. BEGIN failed--compilation aborted. CONTEXT: PL/Perl anonymous code block - Original Message - > From: "Alex Hunsaker" > To: "Toby

[GENERAL] pl/perl and recent perl versions - failing to load internal modules

Hi, I've been trying out PostgreSQL 9.3 with pl/perl built against Ubuntu 14.04 LTS' Perl 5.18 (Sourced from apt.postgresql.org) Maybe I'm doing something wrong, but it appears that plperl has become completely useless, as it can't load any new modules, but modern Perl versions have refactored

Re: [GENERAL] Many, many materialised views - Performance?

On 09/10/13 21:05, Alban Hertroys wrote: On Oct 9, 2013, at 4:08, Kevin Grittner wrote: Toby Corkindale wrote: In this instance, we have a lot of queries that build certain aggregate results, which are very slow. The queries were initially all implemented as views, but then we started

Re: [GENERAL] Many, many materialised views - Performance?

On 08/10/13 19:58, Alban Hertroys wrote: On Oct 8, 2013, at 9:36, Toby Corkindale wrote: Hi, I've discovered previously that Postgres doesn't perform so well in some areas once you have hundreds of thousands of small tables. I'm wondering if materialised views will fare bet

[GENERAL] Many, many materialised views - Performance?

Hi, I've discovered previously that Postgres doesn't perform so well in some areas once you have hundreds of thousands of small tables. I'm wondering if materialised views will fare better, or if they too create a lot of fluff in pg_catalog and many files on-disk? -Toby -- Sent via pgsql-g

Re: [GENERAL] PSA: If you are running Precise/12.04 upgrade your kernel.

Perhaps someone with a spare server floating around could install Ubuntu LTS and run some pg-bench benchmarks with the various kernel options? Like you, I'd have to stick to official updates for production systems. -Toby On 07/06/13 15:36, Nikhil G Daddikar wrote: Folks, This is bad news as

Re: [GENERAL] PSA: If you are running Precise/12.04 upgrade your kernel.

On 07/06/13 08:35, Joshua D. Drake wrote: Hello, I had the distinct displeasure of staying up entirely too late with a customer this week because they upgraded to 12.04 and immediately experienced a huge performance regression. In the process they also upgraded to PostgreSQL 9.1 from 8.4. There

Re: [GENERAL] Deploying PostgreSQL on CentOS with SSD and Hardware RAID

On 21/05/13 00:16, Merlin Moncure wrote: On Sun, May 19, 2013 at 8:07 PM, Toby Corkindale wrote: On 11/05/13 02:25, Merlin Moncure wrote: On Fri, May 10, 2013 at 11:11 AM, Evan D. Hoffman wrote: Not sure of your space requirements, but I'd think a RAID 10 of 8x or more Samsung 840 Pr

Re: [GENERAL] Deploying PostgreSQL on CentOS with SSD and Hardware RAID

On 20/05/13 15:12, David Boreham wrote: On 5/19/2013 7:19 PM, Toby Corkindale wrote: On 13/05/13 11:23, David Boreham wrote: btw we deploy on CentOS6. The only things we change from the default are: 1. add "relatime,discard" options to the mount (check whether the most recent Ce

Re: [GENERAL] Deploying PostgreSQL on CentOS with SSD and Hardware RAID

On 13/05/13 11:23, David Boreham wrote: btw we deploy on CentOS6. The only things we change from the default are: 1. add "relatime,discard" options to the mount (check whether the most recent CentOS6 does this itself -- it didn't back when we first deployed on 6.0). While it is important to l

Re: [GENERAL] Deploying PostgreSQL on CentOS with SSD and Hardware RAID

On 11/05/13 02:25, Merlin Moncure wrote: On Fri, May 10, 2013 at 11:11 AM, Evan D. Hoffman wrote: Not sure of your space requirements, but I'd think a RAID 10 of 8x or more Samsung 840 Pro 256/512 GB would be the best value. Using a simple mirror won't get you the reliability that you want sin

Re: [GENERAL] Perl function leading to out of memory error

On 19/02/13 22:05, Jan Strube wrote: we have a Java daemon that´s repeatedly calling a Perl function inside our database (version 9.1.8). The function is called about 200 times per second. While the Java program is running you can watch the memory usage of the postmaster grow continuously until a

Re: [GENERAL] SSDs - SandForce or not?

On 15/11/12 01:42, Shaun Thomas wrote: On 11/14/2012 01:11 AM, Toby Corkindale wrote: I'm wondering which type of SSDs would be better for use with PostgreSQL. Hi Shaun, thanks for your info. I should probably have made it clear that I was curious to know how the compression stuff aff

[GENERAL] SSDs - SandForce or not?

Hi, I'm wondering which type of SSDs would be better for use with PostgreSQL. Background: At the moment, SSD drives fall into two categories.. Those that use internal-compression on the SandForce controller, which gives very fast speeds for compressible data; and those that don't. In benchmark

Re: [GENERAL] COPY FROM with BYTEA fields - escaping doesn't work

4/10/12 12:58, Toby Corkindale wrote: Hi, I'm trying to use the COPY .. FROM system with some data which includes binary values. They aren't large, but they include invalid UTF8 bytes, so I'm storing them into a BYTEA field. However I get errors when I try to do this.. CREATE TAB

[GENERAL] COPY FROM with BYTEA fields - escaping doesn't work

Hi, I'm trying to use the COPY .. FROM system with some data which includes binary values. They aren't large, but they include invalid UTF8 bytes, so I'm storing them into a BYTEA field. However I get errors when I try to do this.. CREATE TABLE foo (id SERIAL PRIMARY KEY, bar BYTEA); COPY fo

Re: [GENERAL] Can I force a query plan to materialise part?

On 02/10/12 13:02, Ondrej Ivanič wrote: Hi, On 2 October 2012 12:33, Toby Corkindale wrote: I have a query that joins two views, and takes 28 seconds to run. However if I create temporary tables that contain the contents of each view, and then join them, the total time is 1.3 seconds. try

Re: [GENERAL] Can I force a query plan to materialise part?

On 02/10/12 12:44, David Johnston wrote: On Oct 1, 2012, at 22:33, Toby Corkindale wrote: Hi, Is there any way to force the query planner to do a materialisation stage? I have a query that joins two views, and takes 28 seconds to run. However if I create temporary tables that contain the

[GENERAL] Can I force a query plan to materialise part?

Hi, Is there any way to force the query planner to do a materialisation stage? I have a query that joins two views, and takes 28 seconds to run. However if I create temporary tables that contain the contents of each view, and then join them, the total time is 1.3 seconds. Is there a way to hin

Re: [GENERAL] success with postgresql on beaglebone

On 16/08/12 04:05, Tomas Hlavaty wrote: Hi all, I managed to compile postgresql on BeagleBone. [snip] Hi Tomas, If you install Ubuntu, you can just install postgresql with the package management software. $ apt-get install postgresql -Toby -- Sent via pgsql-general mailing list (pgsql-gen

Re: [GENERAL] Async replication: how to get an alert on failure

On 02/08/12 10:04, Edson Richter wrote: I have few PostgreSQL servers established with replication. Now, is there any way to receive an e-mail in case of error during synchronization? I mean, is there an script (cron?) I can run (maybe every 5 minutes) that will check if replication is stolen, an

Re: [GENERAL] Bug? Prepared queries continue to use search_path from their preparation time

On 13/07/12 02:59, Greg Sabino Mullane wrote: -BEGIN PGP SIGNED MESSAGE- Hash: RIPEMD160 This behavior is intentional. In that case, the error lies with the Perl libraries that automatically prepare and cache queries, but do not include the search_path in the cache keys. You have

Re: [GENERAL] Bug? Prepared queries continue to use search_path from their preparation time

On 12/07/12 00:07, Tom Lane wrote: Toby Corkindale writes: The issue occurs when you have server-side prepared queries enabled, and then change the search_path parameter after creating a prepared query. Future executions of that query still seem to be using the original search_path. This

[GENERAL] Bug? Prepared queries continue to use search_path from their preparation time

Hi, I've encountered something that might be a bug in DBD::Pg, or might be a feature of PostgreSQL itself. The issue occurs when you have server-side prepared queries enabled, and then change the search_path parameter after creating a prepared query. Future executions of that query still seem

Re: [GENERAL] Run external SQL file via Perl dbh

On 10/06/12 08:29, David Williams wrote: Hi There, I'd like to be able to run the contents of an external SQL file from Perl. Something akin to: $dbh->do( '\i /home/david/run_me.sql' ); However this fails, and I assume that is because the \i is a client command. Is there a way to run

Re: [GENERAL] Bug? Query plans / EXPLAIN using gigabytes of memory

On 30/04/12 11:26, Rob Sargentg wrote: On 04/29/2012 07:19 PM, Toby Corkindale wrote: On 27/04/12 09:33, Tom Lane wrote: Toby Corkindale writes: I've created a bit of a test case now. There's a Perl script here: http://dryft.net/postgres/ AFAICT, what is happening is that we&#x

Re: [GENERAL] Bug? Query plans / EXPLAIN using gigabytes of memory

On 27/04/12 09:33, Tom Lane wrote: Toby Corkindale writes: I've created a bit of a test case now. There's a Perl script here: http://dryft.net/postgres/ AFAICT, what is happening is that we're repeating the planning of that messy nest of views for each child table of foo.

Re: [GENERAL] Bug? Query plans / EXPLAIN using gigabytes of memory

On 26/04/12 13:11, Tom Lane wrote: Toby Corkindale writes: Just wondering if anyone else has thoughts on this? I'm still suspicious that this is a bug. Well, if you were to provide a reproducible test case, somebody might be motivated to look into it. There could be a memory leak i

Re: [GENERAL] Bug? Query plans / EXPLAIN using gigabytes of memory

On 26/04/12 17:16, Toby Corkindale wrote: On 26/04/12 16:58, Tom Lane wrote: Toby Corkindale writes: On 26/04/12 15:30, Tom Lane wrote: Hm, is the update target an inheritance tree? The target is the parent table of a bunch of partitions. How many would "a bunch" be, exactly?

Re: [GENERAL] Bug? Query plans / EXPLAIN using gigabytes of memory

On 26/04/12 16:58, Tom Lane wrote: Toby Corkindale writes: On 26/04/12 15:30, Tom Lane wrote: Hm, is the update target an inheritance tree? The target is the parent table of a bunch of partitions. How many would "a bunch" be, exactly? I'm fairly sure that the complex vi

Re: [GENERAL] Bug? Query plans / EXPLAIN using gigabytes of memory

On 26/04/12 15:30, Tom Lane wrote: Toby Corkindale writes: On 26/04/12 13:11, Tom Lane wrote: Well, if you were to provide a reproducible test case, somebody might be motivated to look into it. There could be a memory leak in the planner somewhere, but without a test case it's not

Re: [GENERAL] Bug? Query plans / EXPLAIN using gigabytes of memory

On 26/04/12 13:11, Tom Lane wrote: Toby Corkindale writes: Just wondering if anyone else has thoughts on this? I'm still suspicious that this is a bug. Well, if you were to provide a reproducible test case, somebody might be motivated to look into it. There could be a memory leak i

Re: [GENERAL] Bug? Query plans / EXPLAIN using gigabytes of memory

plan is still hundreds of lines long, but running it doesn't use much RAM. So I think that eliminates work_mem-related issues. It really does seem like it's purely the query plan itself that is consuming all the memory. Has anyone else seen this? Thanks, Toby On 25/04/12 16:18,

Re: [GENERAL] Bug? Query plans / EXPLAIN using gigabytes of memory

- Original Message - > From: "Willy-Bas Loos" > To: "Toby Corkindale" > Cc: "pgsql-general" > Sent: Wednesday, 25 April, 2012 7:16:50 PM > Subject: Re: [GENERAL] Bug? Query plans / EXPLAIN using gigabytes of memory > > would it be pos

Re: [GENERAL] Bug? Query plans / EXPLAIN using gigabytes of memory

The memory settings (shared buffers, effective cache) might seem to be set quite conservatively at the moment, given the memory available in the machine -- but since we can exhaust that memory with just a few connections, it seems fair. Cheers, Toby - Original Message ----- From: "Willy-Ba

[GENERAL] Bug? Query plans / EXPLAIN using gigabytes of memory

Hi, I'm hitting some peculiar behaviour. I'm currently on Pg 9.1.3 on a 64bit Debian system. I have a database which is moderately large - 20 GByte or so - and contains that data split up over dozens of tables, which are themselves partitioned. Queries are usually only run against fairly small,

Re: [GENERAL] How to tell if server is in backup mode?

On 29/03/12 19:50, Gabriele Bartolini wrote: Hi Toby, Il 29/03/12 10:46, Gabriele Bartolini ha scritto: Currently, there's no such a function. In general we simply check if a 'backup_label' file exists in PGDATA. However, it could be a good idea to add it as admin function (I will have a think

[GENERAL] How to tell if server is in backup mode?

Hi, I can start and stop backup mode with pg_start_backup() and pg_stop_backup(). Is there any function like pg_is_in_backup() to tell if the mode has been enabled? (I can't find anything in the 9.1 docs) Cheers, Toby -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To

[GENERAL] ANALYSE on top-level of partitioned tables - required?

Hi, Say you have a master table which has many partitioned tables, created like this: CREATE TABLE foo (various columns...); CREATE TABLE foo_1 () INHERITS foo; CREATE TABLE foo_2 () INHERITS foo; Now lets say you insert data directly into foo_X, eg with COPY foo_1 FROM... COPY foo_1 FROM...

Re: [GENERAL] Connection Pooling

On 07/10/11 06:57, Brandon Phelps wrote: Can anyone recommend a good solution for connection pooling? Here is our setup: 2 PostgreSQL 9.1 servers (1 master, 1 hot standby). 1 Apache 2.2.17 We have a pretty extensive web application running on the apache server that talks to both of the database

Re: [GENERAL] PostgreSQL benchmarked on XFS vs ZFS vs btrfs vs ext4

On 17/09/11 00:09, Vick Khera wrote: On Tue, Sep 13, 2011 at 9:15 PM, Toby Corkindale wrote: However we have a new contender - ZFS performed *extremely* well on the latest Ubuntu setup - achieving triple the performance of regular ext4! Did you do any tuning to ZFS? There are many tweaks

Re: [GENERAL] Upgrading from 9.0->9.1 Ubuntu Best Practices

On 16/09/11 12:43, adebarros wrote: Hi, all, first post on this list. I'm running Ubuntu 11.04 and have Postgres 9.0 and 8.4 both installed, although I'm only working with 9.0 at this time for some Django development. I have added this PPA to my Upgrade Manager: http://ppa.launchpad.net/pitti/p

Re: [GENERAL] Why is this query running slowly?

On 15/09/11 17:53, Yang Zhang wrote: I have a simple query that's been running for a while, which is fine, but it seems to be running very slowly, which is a problem: mydb=# explain select user_id from den where user_id not in (select duid from user_mappings) and timestamp between '2009-04-01' a

Re: [GENERAL] how to use all the options of EXPLAIN?

On 15/09/11 15:43, AI Rumman wrote: Could anyone please tell me how to use all the options of explain in Postgresql 9. http://www.postgresql.org/docs/9.0/static/sql-explain.html I can use EXPLAIN ANALYZE. explain (format yaml) select * from tab1; But explain (format yaml) analyze s

Re: [GENERAL] PostgreSQL benchmarked on XFS vs ZFS vs btrfs vs ext4

if you can suggest some. Unfortunately I can't easily bypass the LVM layer here, as the disks involved are fully committed to the volume group and I don't want to rebuild the machine. Cheers, Toby On Tue, Sep 13, 2011 at 10:56 PM, Andy Colson wrote: On 09/13/2011 08:15 PM, Toby Co

Re: [GENERAL] PostgreSQL benchmarked on XFS vs ZFS vs btrfs vs ext4

On 14/09/11 12:56, Andy Colson wrote: On 09/13/2011 08:15 PM, Toby Corkindale wrote: Hi, Some months ago, I ran some (probably naive) benchmarks looking at how pgbench performed on an identical system with differing filesystems. (on Linux). [snip] Did you test unplugging the power cable in

[GENERAL] PostgreSQL benchmarked on XFS vs ZFS vs btrfs vs ext4

Hi, Some months ago, I ran some (probably naive) benchmarks looking at how pgbench performed on an identical system with differing filesystems. (on Linux). Since then the kernel-level version of ZFS became usable, and there have been improvements to btrfs, and no doubt various updates in the

Re: [GENERAL] Need help with what I think is likely a simple query - for each distinct val, return only the record with the most recent date.

On 13/09/11 10:54, Reid Thompson wrote: Could someone point me in the right direction.. Thanks - reid Given the example data, how do I write a query that will give me the resultset: [snip] > I.E. for each distinct val, return the record with the most recent date. Isn't it something simple like

[GENERAL] Has Pg 9.1.0 been released today?

http://www.postgresql.org/docs/9.1/static/release-9-1.html contains the line: "Release Date: 2011-09-12" *bounces excitedly* Has the release candidate gone final today? -Toby -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.

Re: [GENERAL] (replication) Detecting if server a slave, or a master in recovery

On 12/09/11 17:27, Simon Riggs wrote: On Mon, Sep 12, 2011 at 8:19 AM, Toby Corkindale wrote: On 12/09/11 17:13, Simon Riggs wrote: On Mon, Sep 12, 2011 at 7:54 AM, Toby Corkindale What is the best method for determining whether you're connected to the master or slave database? S

Re: [GENERAL] (replication) Detecting if server a slave, or a master in recovery

On 12/09/11 17:13, Simon Riggs wrote: On Mon, Sep 12, 2011 at 7:54 AM, Toby Corkindale wrote: What is the correct way to tell what mode a replicated Pg server is currently in, via a database connection? I can take a guess if it's the master or a slave by using the pg_current_xlog_loc

Re: [GENERAL] (replication) Detecting if server a slave, or a master in recovery

On 12/09/11 16:54, Toby Corkindale wrote: Hi, What is the correct way to tell what mode a replicated Pg server is currently in, via a database connection? I can take a guess if it's the master or a slave by using the pg_current_xlog_location() and pg_last_xlog_replay_location() func

[GENERAL] (replication) Detecting if server a slave, or a master in recovery

Hi, What is the correct way to tell what mode a replicated Pg server is currently in, via a database connection? I can take a guess if it's the master or a slave by using the pg_current_xlog_location() and pg_last_xlog_replay_location() functions. However it occurs to me that a master server

Re: [GENERAL] Indexes on inheriting tables

On 25/08/11 12:10, Shoaib Mir wrote: On Thu, Aug 25, 2011 at 11:57 AM, Toby Corkindale mailto:toby.corkind...@strategicdata.com.au>> wrote: It seems messy to inherit the columns but not the indexes or checks upon them :( Yes it can be a bit annoying at timse but you can

Re: [GENERAL] Indexes on inheriting tables

On 25/08/11 11:34, Shoaib Mir wrote: On Thu, Aug 25, 2011 at 11:17 AM, Toby Corkindale mailto:toby.corkind...@strategicdata.com.au>> wrote: Do I need to make sure I re-create every index on every child table I create? That would be.. annoying, at best. Is there a way to

[GENERAL] Indexes on inheriting tables

Hi, I'm using Pg 9.0 and inheritance to do table partitioning. A simple example would be: CREATE TABLE foo ( id INTEGER PRIMARY KEY, thing VARCHAR(32) ); CREATE INDEX foo_thing_idx ON foo(thing); CREATE TABLE foo_1 () INHERITS (foo); I read that foreign key constraints wouldn't be inherited

Re: [GENERAL] repmgr problem with registering standby

On 02/08/11 01:05, Jaime Casanova wrote: On Wed, Jul 27, 2011 at 7:24 PM, Toby Corkindale wrote: On 28/07/11 03:47, Jaime Casanova wrote: On Wed, Jul 27, 2011 at 4:36 AM, Toby Corkindale wrote: So that looks good, but then I try this on the slave: # repmgr -f /etc/repmgr/validator

Re: [GENERAL] repmgr and archive_mode

- Original Message - > wrote: > > Hi, > > In the repmgr README, it suggests configuration should include: > > > > archive_mode = on > > archive_command = 'cd .' > > > > Could someone explain why we need archive_mode enabled? In my own > > testing of > > Pg's streaming replication, I've fou

[GENERAL] repmgr and archive_mode

Hi, In the repmgr README, it suggests configuration should include: archive_mode = on archive_command = 'cd .' Could someone explain why we need archive_mode enabled? In my own testing of Pg's streaming replication, I've found it to work without this enabled. Cheers, Toby -- Sent via pgsql-

Re: [GENERAL] repmgr problem with registering standby

On 28/07/11 03:47, Jaime Casanova wrote: On Wed, Jul 27, 2011 at 4:36 AM, Toby Corkindale wrote: So that looks good, but then I try this on the slave: # repmgr -f /etc/repmgr/validator/repmgr.conf \ --verbose standby register can you show the content of /etc/repmgr/validator/repmgr.conf

Re: [GENERAL] repmgr problem with registering standby

- Original Message - > On Wed, Jul 27, 2011 at 10:36 AM, Toby Corkindale wrote: > > Hi, > > I have repmgr working to some degree on a couple of servers, but am > > having > > trouble with the "register" part on the slave. > > > > On the mas

[GENERAL] repmgr problem with registering standby

Hi, I have repmgr working to some degree on a couple of servers, but am having trouble with the "register" part on the slave. On the master, I run: # repmgr -f /etc/repmgr/validator/repmgr.conf \ --verbose --force master register Opening configuration file: /etc/repmgr/validator/repmgr.conf

[GENERAL] practical Fail-over methods (was: streaming replication trigger file)

On 16/06/11 18:44, John R Pierce wrote: On 06/16/11 1:31 AM, AI Rumman wrote: When I manually create the C:\\pg\\stopreplication\\standby.txt' file, then it is working. That is, B is becoming the master. So, my question is, how this trigger file should be created so that B will become master aut

Re: [GENERAL] out of memory error

On 06/07/11 01:12, Geoffrey Myers wrote: Wanted to add more specifics. Here is the actual code that generated the error: my $result = $conn->exec($select); if ($result->resultStatus != PGRES_TUPLES_OK) { $error = $conn->errorMessage; die "Error: <$error> Failed: <$select>"; } That looks like

Re: [GENERAL] windows vista and windows 7

On 07/06/11 20:45, Peter Geoghegan wrote: On 6 June 2011 21:18, Heine Ferreira wrote: Hi Does Postgres run on the Starter Edition of Windows Vista and Windows 7? Starter edition can only run 3 programs at a time. I'm not sure how that's counted, and how it will roll with PG's multi-process a

Re: [GENERAL] windows vista and windows 7

On 07/06/11 09:00, Craig Ringer wrote: On 06/07/2011 04:18 AM, Heine Ferreira wrote: Hi Does Postgres run on the Starter Edition of Windows Vista and Windows 7? As far as I know PostgreSQL is not explicitly tested on Windows ... Starter Edition. Whether it will work depends on how crippled th

Re: [GENERAL] Question about configuration and SSD

On 02/06/11 18:53, Craig Ringer wrote: On 02/06/11 16:26, Szymon Guz wrote: Hi, do we need some special configuration for SSD drives, or is that enough to treat those drives normally? Make sure the SSDs have a supercapacitor or battery backup for their write cache. If they do not, then do not

Re: [GENERAL] Shared Buffer Size

On 30/05/11 20:41, Cédric Villemain wrote: 2011/5/30 Toby Corkindale: On 28/05/11 18:42, Carl von Clausewitz wrote: a few months ago, when I installed my first PostgreSQL, I have had the same problem. I've try to get any information about optimal memory config, and working, but there w

Re: [GENERAL] Shared Buffer Size

On 28/05/11 18:42, Carl von Clausewitz wrote: a few months ago, when I installed my first PostgreSQL, I have had the same problem. I've try to get any information about optimal memory config, and working, but there wasn't any "optimal memory setting calculator" on the internet, just some guide in

Re: Fwd: Re: [GENERAL] SSDD reliability

On 19/05/11 10:50, mark wrote: Note 1: I have seen an array that was powered on continuously for about six years, which killed half the disks when it was finally powered down, left to cool for a few hours, then started up again. Recently we rebooted about 6 machines that had uptimes of 950+ d

Re: Fwd: Re: [GENERAL] SSDD reliability

BTW, I saw a news article today about a brand of SSD that was claiming to have the price effectiveness of MLC-type chips, but with lifetime of 4TB/day over 5 years. http://www.storagereview.com/anobit_unveils_genesis_mlc_enterprise_ssds which also links to: http://www.storagereview.com/sandfor

Re: Fwd: Re: [GENERAL] SSDD reliability

On 05/05/11 18:36, Florian Weimer wrote: * Greg Smith: Intel claims their Annual Failure Rate (AFR) on their SSDs in IT deployments (not OEM ones) is 0.6%. Typical measured AFR rates for mechanical drives is around 2% during their first year, spiking to 5% afterwards. I suspect that Intel's n

[GENERAL] SMART attributes for SSD (was: SSDD reliability)

On 05/05/11 22:50, David Boreham wrote: On 5/4/2011 11:50 PM, Toby Corkindale wrote: In what way has the SMART read failed? (I get the relevant values out successfully myself, and have Munin graph them.) Mis-parse :) It was my _attempts_ to read SMART that failed. Specifically, I was able

Re: [GENERAL] SSDD reliability

On 05/05/11 03:31, David Boreham wrote: On 5/4/2011 11:15 AM, Scott Ribe wrote: Sigh... Step 2: paste link in ;-) To be honest, like the article author, I'd be happy with 300+ days to failure, IF the drives

Re: [GENERAL] SSDs with Postgresql?

On 29/04/11 16:35, Greg Smith wrote: On 04/26/2011 10:30 AM, Toby Corkindale wrote: I see Intel is/was claiming their SLC SSDs had a *minimum* lifetime of 2PB in writes for their 64GB disks; for your customer with a 50GB db and 20GB/day of WAL, that would work out at a minimum lifetime of a

Re: [GENERAL] SSDs with Postgresql?

On 22/04/11 01:33, Florian Weimer wrote: * Greg Smith: The fact that every row update can temporarily use more than 8K means that actual write throughput on the WAL can be shockingly large. The smallest customer I work with regularly has a 50GB database, yet they write 20GB of WAL every day.

Re: [GENERAL] tuning on ec2

On 27/04/11 01:34, Joel Reymont wrote: On Apr 26, 2011, at 4:31 PM, Scott Marlowe wrote: What's your work_mem and max_connections set to? I have the default settings, e.g. work_mem = 1MB and max_connections = 100. I'm looking to process 400 requests per second, though. What should I use for

Re: [GENERAL] Poor performance of btrfs with Postgresql

On 22/04/11 12:39, mark wrote: (Tested on Ubuntu Server - Maverick - Kernel 2.6.35-28) Don't take this the wrong way - I applaud you asking for feedback. BTW -> Have you seen Greg Smiths PG 9.0 high performance book ? it's got some chapters dedicated to benchmarking. I do have the book, actu

Re: [GENERAL] SSDs with Postgresql?

- Original Message - > From: "Greg Smith" > To: pgsql-general@postgresql.org > Sent: Friday, 22 April, 2011 12:49:28 AM > Subject: Re: [GENERAL] SSDs with Postgresql? > On 04/20/2011 01:50 AM, Toby Corkindale wrote: > > Also, the number of erase cycles y

Re: [GENERAL] Poor performance of btrfs with Postgresql

On 21/04/11 17:28, Merlin Moncure wrote: On Thu, Apr 21, 2011 at 2:22 AM, Toby Corkindale wrote: I've done some testing of PostgreSQL on different filesystems, and with different filesystem mount options. I found that xfs and ext4 both performed similarly, with ext4 just a few percent f

[GENERAL] Poor performance of btrfs with Postgresql

I've done some testing of PostgreSQL on different filesystems, and with different filesystem mount options. I found that xfs and ext4 both performed similarly, with ext4 just a few percent faster; and I found that adjusting the mount options only gave small improvements, except for the barrier

Re: [GENERAL] SSDs with Postgresql?

On 14/04/11 23:25, Vick Khera wrote: On Thu, Apr 14, 2011 at 12:19 AM, Benjamin Smith mailto:li...@benjamindsmith.com>> wrote: I was wondering if anybody here could comment on the benefits of SSD in similar, high-demand rich schema situations? For the last several months, I've been usi

Re: [GENERAL] SSDs with Postgresql?

On 20/04/11 04:28, Yeb Havinga wrote: On 2011-04-19 19:07, Benjamin Smith wrote: On Sunday, April 17, 2011 01:55:02 AM Henry C. wrote: > > Exactly. Be aware of the risks, plan for failure and reap the rewards. Just curious what your thoughts are with respect to buying SSDs and mirroring the

Re: [GENERAL] Utilities for managing streaming replication servers?

On 23/03/11 20:18, John R Pierce wrote: On 03/22/11 11:18 PM, Toby Corkindale wrote: Hi, I wondered if there were any software packages floating around to manage servers using streaming replication with Pg? ie. To handle failing over and adjusting the config to promote a slave; and performing

[GENERAL] Utilities for managing streaming replication servers?

Hi, I wondered if there were any software packages floating around to manage servers using streaming replication with Pg? ie. To handle failing over and adjusting the config to promote a slave; and performing the steps of syncing and restarting to bring up a new slave. Cheers, Toby -- Sent

Re: [GENERAL] pl/perl interpreter instance(s) - how long does it persist?

On 09/12/10 13:37, Tom Lane wrote: Toby Corkindale writes: I would like to know how long the pl/perl interpreter instances persist? Till end of session. Where session = connection (whether from psql, DBI, etc), yes? Initial testing seems to give me contrary results. In some cases

Re: [GENERAL] pl/perl object destruction (or lack thereof) at session end

On 09/12/10 13:00, Toby Corkindale wrote: Hi, Looking at this: http://www.postgresql.org/docs/9.0/interactive/plperl-under-the-hood.html Specifically, the "known limitations" part, I see it says: When a session ends normally, not due to a fatal error, any END blocks that have been d

[GENERAL] pl/perl interpreter instance(s) - how long does it persist?

Hi, Apologies in advance if this has been covered before, but I've searched extensively without finding anything so far. I would like to know how long the pl/perl interpreter instances persist? I ask because I'm doing some work where we have PL/Perlu code that loads some Perl modules in trigg

[GENERAL] pl/perl object destruction (or lack thereof) at session end

Hi, Looking at this: http://www.postgresql.org/docs/9.0/interactive/plperl-under-the-hood.html Specifically, the "known limitations" part, I see it says: When a session ends normally, not due to a fatal error, any END blocks that have been defined are executed. Currently no other actions ar