Re: [GENERAL] Linux vs FreeBSD

On Fri, Apr 4, 2014 at 9:33 AM, François Beausoleil wrote: > Hi all! > > Does PG perform that much better on FreeBSD? I have some performance > issues on a Ubuntu 12.04 which I'd like to resolve. iowait varies a lot, > between 5 and 50%. Does FreeBSD better schedule I/O, which could alleviate > so

Re: [GENERAL] Linux vs FreeBSD

On 4/3/2014 9:03 PM, François Beausoleil wrote: The host is a dedicated hardware machine at online.fr: 128 GB RAM, 2 x 3TB disk in RAID 1 configuration. just a passing comment... 3TB disks are 7200rpm and suitable for nearline bulk storage (or desktop use), not high performance database rand

[GENERAL] Linux vs FreeBSD

Hi all! Does PG perform that much better on FreeBSD? I have some performance issues on a Ubuntu 12.04 which I'd like to resolve. iowait varies a lot, between 5 and 50%. Does FreeBSD better schedule I/O, which could alleviate some of the issues, or not at all? I have no experience administering

Re: [GENERAL] Is it safe to stop postgres in between pg_start_backup and pg_stop_backup?

What we did in this kind of higher performance storage migration, setting up standby on that mounts and then executed a failover. On Thu, Apr 3, 2014 at 3:58 PM, Alan Hodgson wrote: > On Thursday, April 03, 2014 02:48:03 PM Steven Schlansker wrote: > > On Apr 2, 2014, at 3:08 PM, Jacob Scott w

Re: [GENERAL] Is it safe to stop postgres in between pg_start_backup and pg_stop_backup?

On Thursday, April 03, 2014 02:48:03 PM Steven Schlansker wrote: > On Apr 2, 2014, at 3:08 PM, Jacob Scott wrote: > • pg_start_backup > • Take a filesystem snapshot (of a volume containing postgres data but > not > pg_xlog) • pg_stop_backup > • pg_ctl stop > • Bring a new

Re: [GENERAL] SSD Drives

On Thu, Apr 3, 2014 at 1:44 PM, Brent Wood wrote: > > Hi David, > > Does the RAID 1 array give any performance benefits over a single drive? I'd > guess that writes may be slower, reads may be faster (if balanced) but data > security is improved. I did some testing on machines with 3xMLC Fusion

Re: [GENERAL] SSD Drives

On Thu, Apr 3, 2014 at 3:28 PM, Merlin Moncure wrote: > On Thu, Apr 3, 2014 at 2:53 PM, Scott Marlowe wrote: >> On a machine with 16 cores with HT (appears as 32 cores) and 8 of the >> 3700 series Intel SSDs in a RAID-10 under an LSI MegaRAID with BBU, I >> was able to get 6300 to 7500 tps on a d

Re: [GENERAL] Spring JDBC and the PostgreSQL JDBC driver

On 04/04/14 10:22, John R Pierce wrote: On 4/3/2014 1:31 PM, Matthew Chambers wrote: This removes all the boilerplate associated with old style JDBC. It also means you get great reuse of your SQL code since the transaction starts at the entry point. Most of my SQL related code is just 1 lin

Re: [GENERAL] Is it safe to stop postgres in between pg_start_backup and pg_stop_backup?

On Apr 2, 2014, at 3:08 PM, Jacob Scott wrote: > Hi, Hello there ;) > > > Does upgrading a a disk being used by postgres (9.1, on Ubuntu) with the > following process sound safe? > • pg_start_backup > • Take a filesystem snapshot (of a volume containing postgres data but > not

Re: [GENERAL] SSD Drives

Hi David, Does the RAID 1 array give any performance benefits over a single drive? I'd guess that writes may be slower, reads may be faster (if balanced) but data security is improved. Brent Wood Brent Wood Principal Technician - GIS and Spatial Data Management Programme Leader - Environmental

[GENERAL] Is it safe to stop postgres in between pg_start_backup and pg_stop_backup?

Hi, Does upgrading a a disk being used by postgres (9.1, on Ubuntu) with the following process sound safe? 1. pg_start_backup 2. Take a filesystem snapshot (of a volume containing postgres data but not pg_xlog) 3. Bring a new higher performing disk online from snapshot 4. pg_ctl s

[GENERAL] Need some help in postgres locking mechanism

Hi I have two problems to discuss.an you please guide me how to proceed on this. *problem 1:* I have table X('a' is prmiary key) and table Y('b' is primary key). Table Y has ''a''(X primary key) as foreign key.I have one job which runs once in one hour.I want to lock the rows in table Y.but this wi

Re: [GENERAL] SSD Drives

On Thu, Apr 3, 2014 at 2:53 PM, Scott Marlowe wrote: > On a machine with 16 cores with HT (appears as 32 cores) and 8 of the > 3700 series Intel SSDs in a RAID-10 under an LSI MegaRAID with BBU, I > was able to get 6300 to 7500 tps on a decent sized pgbench db > (-s1000). Did you happen to grab a

Re: [GENERAL] Spring JDBC and the PostgreSQL JDBC driver

On 4/3/2014 1:31 PM, Matthew Chambers wrote: This removes all the boilerplate associated with old style JDBC. It also means you get great reuse of your SQL code since the transaction starts at the entry point. Most of my SQL related code is just 1 liners using Springs JdbcTemplate class. I'v

Re: [GENERAL] Spring JDBC and the PostgreSQL JDBC driver

On 04/04/14 05:39, John R Pierce wrote: On 4/3/2014 9:10 AM, Miller, Michael W wrote: The issue I'm running into is the interaction between the Spring Framework JDBC functionality and the PostgreSQL JDBC driver. The specific issue is that I'm using SimpleJdbcCall.execute() to call the datab

Re: [GENERAL] SSD Drives

On 4/3/2014 2:00 PM, John R Pierce wrote: an important thing in getting decent wear leveling life with SSDs is to keep them under about 70% full. This depends on the drive : drives with higher specified write endurance already have significant overprovisioning, before the user sees the spa

Re: [GENERAL] New OpenSource columnstore from CitusData for Postgresql

I wonder how this approach: https://github.com/citusdata/cstore_fdw compares to this one: https://github.com/knizhnik/imcs From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Dorian Hoxha Sent: Thursday, April 3, 2014 12:58 PM To: PostgreSql-general

Re: [GENERAL] SSD Drives

On 4/3/2014 12:32 PM, David Rees wrote: So yeah, even the slower, cheaper S3500 SSDs are way fast. If your write workload isn't too high, the S3500 can work well. We'll see how the SMART drive lifetime numbers do once we get into production, but right now we estimate they should last at least 5 y

[GENERAL] New OpenSource columnstore from CitusData for Postgresql

Link to hackernews which also has some comments from the devs https://news.ycombinator.com/item?id=7523950 Very interesting: They use foreign data tables as an abstraction to separate the storage layer from the rest of the database.

Re: [GENERAL] SSD Drives

On Thu, 2014-04-03 at 12:32 -0700, David Rees wrote: > On Thu, Apr 3, 2014 at 12:13 PM, Merlin Moncure wrote: > > On Wed, Apr 2, 2014 at 2:37 PM, Bret Stern > > wrote: > >> Any opinions/comments on using SSD drives with postgresql? > > > > Here's a single S3700 smoking an array of 16 15k drives (

Re: [GENERAL] SSD Drives

On Thu, Apr 3, 2014 at 1:32 PM, David Rees wrote: > On Thu, Apr 3, 2014 at 12:13 PM, Merlin Moncure wrote: >> On Wed, Apr 2, 2014 at 2:37 PM, Bret Stern >> wrote: >>> Any opinions/comments on using SSD drives with postgresql? >> >> Here's a single S3700 smoking an array of 16 15k drives (poster

Re: [GENERAL] SSD Drives

On Thu, Apr 3, 2014 at 12:44 PM, Brent Wood wrote: > Does the RAID 1 array give any performance benefits over a single drive? I'd > guess > that writes may be slower, reads may be faster (if balanced) but data > security is improved. Unfortunately I didn't test a single drive as that's not a co

Re: [GENERAL] COPY v. java performance comparison

On 04/03/2014 01:28 PM, Jeff Janes wrote: On Thu, Apr 3, 2014 at 9:04 AM, Rob Sargent > wrote: I have to straighten out my environment, which I admit I was hoping to avoid. I reset checkpoint_segments to 12 and restarted my server. I kicked of the CO

Re: [GENERAL] SSD Drives

On Thu, Apr 3, 2014 at 12:13 PM, Merlin Moncure wrote: > On Wed, Apr 2, 2014 at 2:37 PM, Bret Stern > wrote: >> Any opinions/comments on using SSD drives with postgresql? > > Here's a single S3700 smoking an array of 16 15k drives (poster didn't > realize that; was to focused on synthetic numbers

Re: [GENERAL] COPY v. java performance comparison

On Thu, Apr 3, 2014 at 9:04 AM, Rob Sargent wrote: > I have to straighten out my environment, which I admit I was hoping to > avoid. I reset checkpoint_segments to 12 and restarted my server. > I kicked of the COPY at 19:00. That generated a couple of the "too > frequent" statements but 52 "WAR

Re: [GENERAL] SSD Drives

On Wed, Apr 2, 2014 at 2:37 PM, Bret Stern wrote: > Any opinions/comments on using SSD drives with postgresql? Here's a single S3700 smoking an array of 16 15k drives (poster didn't realize that; was to focused on synthetic numbers): http://dba.stackexchange.com/questions/45224/postgres-write-per

Re: [GENERAL] COPY v. java performance comparison

On Wed, Apr 2, 2014 at 5:11 PM, Rob Sargent wrote: > On 04/02/2014 04:36 PM, Jeff Janes wrote: > > On Wed, Apr 2, 2014 at 12:37 PM, Rob Sargent wrote: > >> I'm playing with various data models to compare performance and >> practicalities and not sure if I should be surprised by the numbers I'm

Re: [GENERAL] COPY v. java performance comparison

On Wed, Apr 2, 2014 at 3:46 PM, Rob Sargent wrote: > On 04/02/2014 04:36 PM, Jeff Janes wrote: > > Are you sure you actually dropped the indices? (And the primary key?) > > I get about 375,000 lines per second with no indexes, triggers, > constraints. > > perl -le 'my $x=""; fo

Re: [GENERAL] COPY v. java performance comparison

On 4/3/2014 12:57 PM, Andy Colson wrote: On 4/3/2014 12:15 PM, Rob Sargent wrote: Is the java app cpu bound? > Also watch "vmstat 3" for a minute or two. The last two numbers (wa > & id) (some vmstat's have a steal, ignore that) will tell you if you > are io bound. > > -Andy > > Durin

Re: [GENERAL] [ANNOUNCE] == PostgreSQL Weekly News - April 01 2014 ==

On Thu, Apr 3, 2014 at 11:52 PM, Andy Colson wrote: > > You realize its an April fools joke, right? > > I think I can hear David's evil laugh from here :-) > > -Andy > Ya, actually did realize the same time when it got posted as was online that time. ;) Regards...

Re: [GENERAL] SSD Drives

On Apr 3, 2014, at 12:47 PM, John R Pierce wrote: > On 4/3/2014 9:26 AM, Joe Van Dyk wrote: >> Related, anyone have any thoughts on using postgresql on Amazon's EC2 SSDs? >> Been looking at >> http://aws.amazon.com/about-aws/whats-new/2013/12/19/announcing-the-next-generation-of-amazon-ec2-hi

Re: [GENERAL] [ANNOUNCE] == PostgreSQL Weekly News - April 01 2014 ==

On 4/3/2014 1:14 PM, Dev Kumkar wrote: On Tue, Apr 1, 2014 at 7:10 PM, David Fetter mailto:da...@fetter.org>> wrote: == PostgreSQL Weekly News - April 01 2014 == PostgreSQL 10.0 Released. This release includes built-in, tradeoff-free multi-master replication, full integration wi

Re: [GENERAL] [ANNOUNCE] == PostgreSQL Weekly News - April 01 2014 ==

On Tue, Apr 1, 2014 at 7:10 PM, David Fetter wrote: > == PostgreSQL Weekly News - April 01 2014 == > > PostgreSQL 10.0 Released. > This release includes built-in, tradeoff-free multi-master > replication, full integration with all other data stores, and a broad > choice of SQL query dialects incl

Re: [GENERAL] COPY v. java performance comparison

On 4/3/2014 12:15 PM, Rob Sargent wrote: Is the java app cpu bound? > Also watch "vmstat 3" for a minute or two. The last two numbers (wa > & id) (some vmstat's have a steal, ignore that) will tell you if you > are io bound. > > -Andy > > During COPY, with autovaccume off (server resta

Re: [GENERAL] window function help

Andy Colson wrote > On 4/3/2014 11:09 AM, David Johnston wrote: >> Andy Colson wrote >>> On 4/3/2014 10:27 AM, Schnabel, Robert D. wrote: I'm trying to figure out how to count the number of rows within a fixed range of the current row value. My table looks like this: SELECT chr

Re: [GENERAL] COPY v. java performance comparison

Is the java app cpu bound? > Also watch "vmstat 3" for a minute or two. The last two numbers (wa > & id) (some vmstat's have a steal, ignore that) will tell you if you > are io bound. > > -Andy > > During COPY, with autovaccume off (server restarted, manual vacuum to get things going). Immed

Re: [GENERAL] window function help

On 4/3/2014 11:09 AM, David Johnston wrote: Andy Colson wrote On 4/3/2014 10:27 AM, Schnabel, Robert D. wrote: I'm trying to figure out how to count the number of rows within a fixed range of the current row value. My table looks like this: SELECT chr_pos FROM mutations_crosstab_9615_99 WHERE

Re: [GENERAL] SSD Drives

On 4/3/2014 9:26 AM, Joe Van Dyk wrote: Related, anyone have any thoughts on using postgresql on Amazon's EC2 SSDs? Been looking at http://aws.amazon.com/about-aws/whats-new/2013/12/19/announcing-the-next-generation-of-amazon-ec2-high-i/o-instance if your data isn't very important, by all m

Re: [GENERAL] Spring JDBC and the PostgreSQL JDBC driver

On 4/3/2014 9:10 AM, Miller, Michael W wrote: The issue I'm running into is the interaction between the Spring Framework JDBC functionality and the PostgreSQL JDBC driver. The specific issue is that I'm using SimpleJdbcCall.execute() to call the database and getting back a Jdbc4Array. When I

Re: [GENERAL] window function help

-Original Message- From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of David Johnston Sent: Thursday, April 03, 2014 11:09 AM To: pgsql-general@postgresql.org Subject: Re: [GENERAL] window function help Andy Colson wrote > On 4/3/2014 10:27 AM

Re: [GENERAL] Pagination count strategies

On 03/04/14 17:44, Leonardo M. Ramé wrote: > Nice!, do you know if this will work on 8.4?. > no way -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] SSD Drives

On Wed, Apr 2, 2014 at 12:37 PM, Bret Stern < bret_st...@machinemanagement.com> wrote: > Any opinions/comments on using SSD drives with postgresql? > Related, anyone have any thoughts on using postgresql on Amazon's EC2 SSDs? Been looking at http://aws.amazon.com/about-aws/whats-new/2013/12/19/a

Re: [GENERAL] Spring JDBC and the PostgreSQL JDBC driver

"Miller, Michael W" writes: > I apologize if this is not the correct place to post this question but who do > I talk to about modifying the functionality of the JDBC driver? pgsql-jdbc list would be the place for that. regards, tom lane -- Sent via pgsql-general maili

[GENERAL] Spring JDBC and the PostgreSQL JDBC driver

I apologize if this is not the correct place to post this question but who do I talk to about modifying the functionality of the JDBC driver? The issue I'm running into is the interaction between the Spring Framework JDBC functionality and the PostgreSQL JDBC driver. The specific issue is that I

Re: [GENERAL] COPY v. java performance comparison

On 04/03/2014 09:01 AM, Thomas Kellerer wrote: Rob Sargent, 02.04.2014 21:37: I loaded 37M+ records using jOOQ (batching every 1000 lines) in 12+ hours (800+ records/sec). Then I tried COPY and killed that after 11.25 hours when I realised that I had added on non-unque index on the name fields

Re: [GENERAL] window function help

Andy Colson wrote > On 4/3/2014 10:27 AM, Schnabel, Robert D. wrote: >> I'm trying to figure out how to count the number of rows within a fixed >> range of the current row value. My table looks like this: >> >> SELECT chr_pos >> FROM mutations_crosstab_9615_99 >> WHERE bta = 38 >> LIMIT 10 >> >> c

Re: [GENERAL] Pagination count strategies

On Thu, Apr 03, 2014 at 12:44:23PM -0300, Leonardo M. Ramé wrote: > Sounds nice, is it possible to modify my "count(*) over()" to what you > suggest?. I think the window_definition inside over() can contain a LIMIT, can't it? I didn't check just now, but I can't think any reason why not. (ISTR wh

Re: [GENERAL] window function help

On 4/3/2014 10:27 AM, Schnabel, Robert D. wrote: Hi, I'm trying to figure out how to count the number of rows within a fixed range of the current row value. My table looks like this: SELECT chr_pos FROM mutations_crosstab_9615_99 WHERE bta = 38 LIMIT 10 chr_pos 138 140 163 174 187 187 188 20

Re: [GENERAL] COPY v. java performance comparison

On 4/2/2014 7:30 PM, Rob Sargent wrote: Well things slow down over time, and lots of "too frequent"s: Have done 500 batches in 24219 ms Have done 1000 batches in 52362 ms Have done 1500 batches in 82256 ms Have done 2000 batches in 113754 ms Have done 2500 batches in 14

Re: [GENERAL] Pagination count strategies

On 2014-04-03 17:19:56 +0200, Torsten Förtsch wrote: > On 03/04/14 15:34, Leonardo M. Ramé wrote: > > Hi, in one of our systems, we added a kind of pagination feature, that > > shows N records of Total records. > > > > To do this, we added a "count(*) over() as Total" field in our queries > > in r

Re: [GENERAL] Pagination count strategies

On 2014-04-03 10:00:18 -0400, Andrew Sullivan wrote: > On Thu, Apr 03, 2014 at 10:34:32AM -0300, Leonardo M. Ramé wrote: > > > > What strategy for showing the total number of records returned do you > > recommend?. > > The best answer for this I've ever seen is to limit the number of rows > you'r

Re: [GENERAL] size of interval type

2014/04/03 23:53 "Tom Lane" : > It's a documentation bug; the size used to be 12 bytes, but that was quite > a long time ago. I see somebody fixed this in HEAD recently but failed to > back-patch it. I see. Thank you for your reply and quick fix! Best regards,

[GENERAL] window function help

Hi, I'm trying to figure out how to count the number of rows within a fixed range of the current row value. My table looks like this: SELECT chr_pos FROM mutations_crosstab_9615_99 WHERE bta = 38 LIMIT 10 chr_pos 138 140 163 174 187 187 188 208 210 213 chr_pos is integer and represents the ba

Re: [GENERAL] Pagination count strategies

On 03/04/14 15:34, Leonardo M. Ramé wrote: > Hi, in one of our systems, we added a kind of pagination feature, that > shows N records of Total records. > > To do this, we added a "count(*) over() as Total" field in our queries > in replacement of doing two queries, one for fetching the records, an

Re: [GENERAL] Any way to insert rows with ID used in another column

On 3 April 2014 16:41, Ben Hoyt wrote: > Hi folks, > I tried using currval() to see if that'd work, but it gave an error, I guess > because I was using it multiple times per session. currval() requires that nextval() was called before it (either automatically or explicitly) in the same transacti

Re: [GENERAL] Any way to insert rows with ID used in another column

( Forgot to hit reply all, so probably someone will get this twice, sorry ). Hi: On Thu, Apr 3, 2014 at 4:41 PM, Ben Hoyt wrote: . > Is there some way to do something like this: > INSERT INTO images (filename) VALUES > ('foo' || image_id_about_to_used_for_this_row::text || '.jpg') > ('bar' |

Re: [GENERAL] Any way to insert rows with ID used in another column

On 04/03/2014 07:41 AM, Ben Hoyt wrote: Hi folks, We have a table images in our db with id (serial primary key) and filename columns, where the filename is a unique text column that looks something like "pool-1234.jpg". The catch is that the "1234" in the filename is the image ID. We want the f

Re: [GENERAL] COPY v. java performance comparison

Rob Sargent, 02.04.2014 21:37: > I loaded 37M+ records using jOOQ (batching every 1000 lines) in 12+ > hours (800+ records/sec). Then I tried COPY and killed that after > 11.25 hours when I realised that I had added on non-unque index on > the name fields after the first load. By that point is was

Re: [GENERAL] COPY v. java performance comparison

On 04/02/2014 08:40 PM, Adrian Klaver wrote: On 04/02/2014 05:30 PM, Rob Sargent wrote: On 04/02/2014 06:06 PM, Adrian Klaver wrote: On 04/02/2014 02:27 PM, Rob Sargent wrote: On 04/02/2014 03:11 PM, Adrian Klaver wrote: On 04/02/2014 02:04 PM, Rob Sargent wrote: On 04/02/2014 02:36 PM, Adri

Re: [GENERAL] size of interval type

Kasahara Tatsuhito writes: > The document says the size of interval type is 12 bytes. > http://www.postgresql.org/docs/9.3/static/datatype-datetime.html > But ISTM the size is actually 16 bytes. > # I checked src/include/datatype/timestamp.h and pg_type system view. > Is it a document bug? Or am

[GENERAL] Any way to insert rows with ID used in another column

Hi folks, We have a table images in our db with id (serial primary key) and filename columns, where the filename is a unique text column that looks something like "pool-1234.jpg". The catch is that the "1234" in the filename is the image ID. We want the filename to include the image ID because it

[GENERAL] size of interval type

Hi, The document says the size of interval type is 12 bytes. http://www.postgresql.org/docs/9.3/static/datatype-datetime.html But ISTM the size is actually 16 bytes. # I checked src/include/datatype/timestamp.h and pg_type system view. Is it a document bug? Or am I missing something? Best rega

Re: [GENERAL] Pagination count strategies

Cache the total ? On Thu, Apr 3, 2014 at 3:34 PM, Leonardo M. Ramé wrote: > Hi, in one of our systems, we added a kind of pagination feature, that > shows N records of Total records. > > To do this, we added a "count(*) over() as Total" field in our queries > in replacement of doing two queries

Re: [GENERAL] Pagination count strategies

On 3 April 2014 15:34, Leonardo M. Ramé wrote: > Hi, in one of our systems, we added a kind of pagination feature, that > shows N records of Total records. > > To do this, we added a "count(*) over() as Total" field in our queries > in replacement of doing two queries, one for fetching the records

Re: [GENERAL] Pagination count strategies

On Thu, Apr 03, 2014 at 10:34:32AM -0300, Leonardo M. Ramé wrote: > > What strategy for showing the total number of records returned do you > recommend?. The best answer for this I've ever seen is to limit the number of rows you're counting (at least at first) to some reasonably small number -- s

[GENERAL] Pagination count strategies

Hi, in one of our systems, we added a kind of pagination feature, that shows N records of Total records. To do this, we added a "count(*) over() as Total" field in our queries in replacement of doing two queries, one for fetching the records, and other for getting the count. This improved the perf

Re: [GENERAL] Unattended Installation

Many thanks to Igor The hint with the "bitrock" installer log helped me to find the problem. It was an incorrect path setting (double backslash). The UI-Installer might correct these settings. Regards, Peter. Quoting Igor Neyman : -Original Message- From: pgsql-general-ow...@postgr