Re: [PERFORM] Tuning one Recurcive CTE

På onsdag 09. november 2016 kl. 15:30:20, skrev Henrik Ekenberg < hen...@ekenberg.pw >: Hi, I will need to anonymized before sending it. Do you know if there is any tuning documents related to CTE scans You might want to read this: http://blog.2ndquadrant.com/postgres

Re: [PERFORM] Tuning one Recurcive CTE

Hi, I will need to anonymized before sending it. Do you know if there is any tuning documents related to CTE scans //H På onsdag 09. november 2016 kl. 14:05:55, skrev Henrik Ekenberg : Hi, I try to tune one Recursive CTE. Explain Plan can be found here https://explain.depesz.com/s/yLVd An

Re: [PERFORM] Tuning one Recurcive CTE

På onsdag 09. november 2016 kl. 14:05:55, skrev Henrik Ekenberg < hen...@ekenberg.pw >: Hi, I try to tune one Recursive CTE. Explain Plan can be found here https://explain.depesz.com/s/yLVd Anyone can give me direction to check? //H.   Rule number one; Always pr

Re: [PERFORM] Tuning Checkpoints

On 10/31/2016 08:19 PM, Andre Henry wrote: My PG 9.4.5 server runs on Amazon RDS some times of the day we have a lot of checkpoints really close (less than 1 minute apart, see logs below) and we are trying to tune the DB to minimize the impact of the checkpoint or reduce the number of checkpoints

Re: [PERFORM] Tuning guidelines for server with 256GB of RAM and SSDs?

On 08/07/16 02:09, Wes Vaske (wvaske) wrote: ?The Crucial drive does not have power loss protection. The Samsung drive does. (The Crucial M550 has capacitors to protect data that's already been written to the device but not the entire cache. For instance, if data is read from the device during

Re: [PERFORM] Tuning guidelines for server with 256GB of RAM and SSDs?

> Regarding write back cache: > Disabling the write back cache won't have a real large impact on the > endurance of the drive unless it reduces the total number of bytes written > (which it won't). I've seen drives that perform better with it disabled and > drives that perform better with it enable

Re: [PERFORM] Tuning guidelines for server with 256GB of RAM and SSDs?

On Thu, Jul 7, 2016 at 10:27 AM, Merlin Moncure wrote: > On Wed, Jul 6, 2016 at 4:48 PM, Scott Marlowe wrote: >> On Wed, Jul 6, 2016 at 12:13 PM, Merlin Moncure wrote: >>> Disabling write back cache for write heavy database loads will will >>> destroy it in short order due to write amplication a

Re: [PERFORM] Tuning guidelines for server with 256GB of RAM and SSDs?

On Wed, Jul 6, 2016 at 4:48 PM, Scott Marlowe wrote: > On Wed, Jul 6, 2016 at 12:13 PM, Merlin Moncure wrote: >> Disabling write back cache for write heavy database loads will will >> destroy it in short order due to write amplication and will generally >> cause it to underperform hard drives in

Re: [PERFORM] Tuning guidelines for server with 256GB of RAM and SSDs?

ow...@postgresql.org on behalf of Kaixi Luo Sent: Thursday, July 7, 2016 2:49 AM To: Mark Kirkwood Cc: pgsql-performance@postgresql.org Subject: Re: [PERFORM] Tuning guidelines for server with 256GB of RAM and SSDs? It's a Crucial CT250MX200SSD1 and a Samsung MZ7LM480HCHP-3. Regard

Re: [PERFORM] Tuning guidelines for server with 256GB of RAM and SSDs?

It's a Crucial CT250MX200SSD1 and a Samsung MZ7LM480HCHP-3. Regards, Kaixi On Thu, Jul 7, 2016 at 6:59 AM, Mark Kirkwood wrote: > On 06/07/16 07:17, Mkrtchyan, Tigran wrote: > >> Hi, >> >> We had a similar situation and the best performance was with 64MB >> background_bytes and 512 MB dir

Re: [PERFORM] Tuning guidelines for server with 256GB of RAM and SSDs?

On 06/07/16 07:17, Mkrtchyan, Tigran wrote: Hi, We had a similar situation and the best performance was with 64MB background_bytes and 512 MB dirty_bytes. Tigran. On Jul 5, 2016 16:51, Kaixi Luo wrote: Here are my server specs: RAID1 - 2x480GB Samsung SSD with power loss protecti

Re: [PERFORM] Tuning guidelines for server with 256GB of RAM and SSDs?

rg on behalf of Merlin Moncure Sent: Wednesday, July 6, 2016 1:13 PM To: Kaixi Luo Cc: postgres performance list Subject: Re: [PERFORM] Tuning guidelines for server with 256GB of RAM and SSDs? On Tue, Jul 5, 2016 at 9:50 AM, Kaixi Luo wrote: > Hello, > > I've been reading Mr. Greg

Re: [PERFORM] Tuning guidelines for server with 256GB of RAM and SSDs?

On Wed, Jul 6, 2016 at 12:13 PM, Merlin Moncure wrote: > Disabling write back cache for write heavy database loads will will > destroy it in short order due to write amplication and will generally > cause it to underperform hard drives in my experience. Interesting. We found our best performance

Re: [PERFORM] Tuning guidelines for server with 256GB of RAM and SSDs?

On Tue, Jul 5, 2016 at 9:50 AM, Kaixi Luo wrote: > Hello, > > I've been reading Mr. Greg Smith's "Postgres 9.0 - High Performance" book > and I have some questions regarding the guidelines I found in the book, > because I suspect some of them can't be followed blindly to the letter on a > server w

Re: [PERFORM] Tuning guidelines for server with 256GB of RAM and SSDs?

Hi, We had a similar situation and the best performance was with 64MB background_bytes and 512 MB dirty_bytes. Tigran. On Jul 5, 2016 16:51, Kaixi Luo wrote:Hello,I've been reading Mr. Greg Smith's "Postgres 9.0 - High Performance" book and I have some questions regarding the guidelines I found

Re: [PERFORM] Tuning the configuration

> On 16 Dec 2014, at 14:51, Graeme B. Bell wrote: > >> >> I don't understand the logic behind using drives, >> which are best for random io, for sequent io workloads. > > Because they are also best for sequential IO. I get 1.3-1.4GB/second from 4 > SSDs in RAID or >500MB/s for single disk sy

Re: [PERFORM] Tuning the configuration

> > I don't understand the logic behind using drives, > which are best for random io, for sequent io workloads. Because they are also best for sequential IO. I get 1.3-1.4GB/second from 4 SSDs in RAID or >500MB/s for single disk systems, even with cheap models. Are you getting more than that f

Re: [PERFORM] Tuning the configuration

> Very much agree with this. Because SSD is fast doesn't make it suited for > certain things, and a streaming sequential 100% write workload is one of > them. I've worked with everything from local disk to high-end SAN and even > at the high end we've always put any DB logs on spinning disk.

Re: [PERFORM] Tuning the configuration

: [PERFORM] Tuning the configuration On 11 Dec 2014, at 15:02, Andrea Suisani wrote: On 12/10/2014 11:44 AM, Maila Fatticcioni wrote: 2- I would like to use the two SDD to store the wal file. Do you think it is useful or how should I use them? I definitely would give it a try. I don&#

Re: [PERFORM] Tuning the configuration

From: pgsql-performance-ow...@postgresql.org on behalf of Evgeniy Shishkin Sent: Thursday, December 11, 2014 7:11 AM To: Andrea Suisani Cc: mfatticci...@mbigroup.it; pgsql-performance@postgresql.org Subject: Re: [PERFORM] Tuning the configuration >

Re: [PERFORM] Tuning the configuration

On 12/11/2014 01:11 PM, Evgeniy Shishkin wrote: On 11 Dec 2014, at 15:02, Andrea Suisani wrote: On 12/10/2014 11:44 AM, Maila Fatticcioni wrote: 2- I would like to use the two SDD to store the wal file. Do you think it is useful or how should I use them? I definitely would give it a try.

Re: [PERFORM] Tuning the configuration

Would you mind to explain me better why you do suggest me to use the sas raid for wal please? SSDs are known to shine when they have to deal with random access pattern rather than sequential, on the other hand 10/15K rpm SAS disk is known to be better for sequential io workloads (in general "r

Re: [PERFORM] Tuning the configuration

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 12/11/2014 01:11 PM, Evgeniy Shishkin wrote: > >> On 11 Dec 2014, at 15:02, Andrea Suisani >> wrote: >> >> On 12/10/2014 11:44 AM, Maila Fatticcioni wrote: >>> 2- I would like to use the two SDD to store the wal file. Do >>> you think it is usefu

Re: [PERFORM] Tuning the configuration

> On 11 Dec 2014, at 15:02, Andrea Suisani wrote: > > On 12/10/2014 11:44 AM, Maila Fatticcioni wrote: >> 2- I would like to use the two SDD to store the wal file. Do you think >> it is useful or how should I use them? > > I definitely would give it a try. > I don't understand the logic behi

Re: [PERFORM] Tuning the configuration

On 12/10/2014 11:44 AM, Maila Fatticcioni wrote: -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hello. I need to tune a postgres installation I've just made to get a better performance. I use two identical servers with a hot replication configuration. The two servers have the following hardware:

Re: [PERFORM] Tuning the configuration

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 12/10/2014 06:47 PM, Patrick Krecker wrote: > On Wed, Dec 10, 2014 at 2:44 AM, Maila Fatticcioni > wrote: Hello. I need to tune a postgres > installation I've just made to get a better performance. I use two > identical servers with a hot replicat

Re: [PERFORM] Tuning the configuration

On Wed, Dec 10, 2014 at 2:44 AM, Maila Fatticcioni wrote: > -BEGIN PGP SIGNED MESSAGE- > Hash: SHA1 > > Hello. > I need to tune a postgres installation I've just made to get a better > performance. I use two identical servers with a hot replication > configuration. The two servers have the

Re: [PERFORM] tuning postgresql 9.3.5 and multiple cores

On 26/08/14 06:47, Jeison Bedoya Delgado wrote: hi, recently i change the hardware of my database 32 cores up to 64 cores and 128GB Ram, but the performance is the same. Perhaps i have to change any parameter in the postgresql.conf?. In addition to the points that others have made, even if yo

Re: [PERFORM] tuning postgresql 9.3.5 and multiple cores

Changing to a higher rate CPU would be more helpful if you run less than 32 queries at a time. On Tue, Aug 26, 2014 at 8:51 AM, Jeff Janes wrote: > On Monday, August 25, 2014, Jeison Bedoya Delgado < > jeis...@audifarma.com.co> wrote: > >> hi, recently i change the hardware of my database 32 co

Re: [PERFORM] tuning postgresql 9.3.5 and multiple cores

On Monday, August 25, 2014, Jeison Bedoya Delgado wrote: > hi, recently i change the hardware of my database 32 cores up to 64 cores > and 128GB Ram, but the performance is the same. Perhaps i have to change > any parameter in the postgresql.conf?. > PostgreSQL does not (yet) automatically par

Re: [PERFORM] Tuning Postgres for Single connection use

Jeff Janes writes: > On Tue, Apr 15, 2014 at 9:12 AM, Nick Eubank wrote: >> Quick followup Jeff: it seems that I can't set work_mem above about 1gb >> (can't get to 2gb. When I update config, the values just don't change in >> "SHOW ALL" -- integer constraint?). Is there a work around, or should

Re: [PERFORM] Tuning Postgres for Single connection use

From: pgsql-performance-ow...@postgresql.org [mailto:pgsql-performance-ow...@postgresql.org] On Behalf Of Nick Eubank Sent: Tuesday, April 15, 2014 11:12 AM To: Jeff Janes Cc: pgsql-performance@postgresql.org Subject: Re: [PERFORM] Tuning Postgres for Single connection use On Mon, Apr 14

Re: [PERFORM] Tuning Postgres for Single connection use

On Tue, Apr 15, 2014 at 9:12 AM, Nick Eubank wrote: > On Mon, Apr 14, 2014 at 5:19 PM, Jeff Janes wrote: > >> >> I'd go with a small shared_buffers, like 128MB, and let the OS cache as >> much as possible. This minimizes the amount of double buffering. >> >> And set work_mem to about 6GB, then

Re: [PERFORM] Tuning Postgres for Single connection use

On Mon, Apr 14, 2014 at 5:19 PM, Jeff Janes wrote: > On Mon, Apr 14, 2014 at 2:46 PM, Nick Eubank wrote: > >> Any rules of thumb for work_mem, maintenance_work_mem, shared_buffer, >> etc. for a database that DOESN'T anticipate concurrent connections and that >> is doing lots of aggregate functio

Re: [PERFORM] Tuning Postgres for Single connection use

Terrific -- thanks Gavin and Jeff! That's incredibly helpful for a n00b like me! On Mon, Apr 14, 2014 at 5:29 PM, Gavin Flower wrote: > In this list, please bottom post! > > I've added potentially useful advice below. > > > On 15/04/14 11:39, Nick Eubank wrote: > > Thanks Gavin -- would LOVE t

Re: [PERFORM] Tuning Postgres for Single connection use

In this list, please bottom post! I've added potentially useful advice below. On 15/04/14 11:39, Nick Eubank wrote: Thanks Gavin -- would LOVE to. Sadly I'm in a weird situation where my hardware is not under my control, so I'm stuck making the best of what I have. Next time though! :) On Mo

Re: [PERFORM] Tuning Postgres for Single connection use

On Mon, Apr 14, 2014 at 2:46 PM, Nick Eubank wrote: > Any rules of thumb for work_mem, maintenance_work_mem, shared_buffer, > etc. for a database that DOESN'T anticipate concurrent connections and that > is doing lots of aggregate functions on large tables? All the advice I > can find online on t

Re: [PERFORM] Tuning Postgres for Single connection use

On 04/14/2014 05:46 PM, Nick Eubank wrote: Any rules of thumb for |work_mem|, |maintenance_work_mem|, |shared_buffer|, etc. for a database that DOESN'T anticipate concurrent connections and that is doing lots of aggregate functions on large tables? All the advice I can find online on tuning

Re: [PERFORM] Tuning Postgres for Single connection use

Thanks Gavin -- would LOVE to. Sadly I'm in a weird situation where my hardware is not under my control, so I'm stuck making the best of what I have. Next time though! :) On Monday, April 14, 2014, Gavin Flower wrote: > On 15/04/14 09:46, Nick Eubank wrote: > > Any rules of thumb for work_mem,

Re: [PERFORM] Tuning Postgres for Single connection use

On 15/04/14 09:46, Nick Eubank wrote: Any rules of thumb for |work_mem|, |maintenance_work_mem|, |shared_buffer|, etc. for a database that DOESN'T anticipate concurrent connections and that is doing lots of aggregate functions on large tables? All the advice I can find online on tuning (this

Re: [PERFORM] Tuning Postgres 9.1 on Windows

@postgresql.org [mailto:pgsql-performance-ow...@postgresql.org] On Behalf Of Walker, James Les Sent: Tuesday, May 01, 2012 3:14 PM To: 'Thomas Kellerer'; pgsql-performance@postgresql.org Subject: Re: [PERFORM] Tuning Postgres 9.1 on Windows Yes. I didn't know the proper vernacular :-) It i

Re: [PERFORM] Tuning Postgres 9.1 on Windows

Of Thomas Kellerer Sent: Tuesday, May 01, 2012 1:00 PM To: pgsql-performance@postgresql.org Subject: Re: [PERFORM] Tuning Postgres 9.1 on Windows Walker, James Les wrote on 01.05.2012 16:44: > I installed the enterprisedb distribution and immediately saw a 400% > performance increase. What

Re: [PERFORM] Tuning Postgres 9.1 on Windows

Walker, James Les wrote on 01.05.2012 16:44: I installed the enterprisedb distribution and immediately saw a 400% performance increase. What exactly is "the enterprisedb distribution"? Are you talking about the the Advanced Server? I would be very surprised if the code base would differ so mu

Re: [PERFORM] Tuning Postgres 9.1 on Windows

On Tue, May 1, 2012 at 9:44 AM, Walker, James Les wrote: > I installed the enterprisedb distribution and immediately saw a 400% > performance increase. Turning off fsck made it an order of magnitude better. > I'm now peaking at over 400 commits per second. Does that sound right? yeah -- well it

Re: [PERFORM] Tuning Postgres 9.1 on Windows

Les Cc: Thomas Kellerer; pgsql-performance@postgresql.org Subject: Re: [PERFORM] Tuning Postgres 9.1 on Windows On Tue, May 1, 2012 at 8:14 AM, Walker, James Les wrote: > SSD is OCZ-VERTEX3 MI. Controller is LSI SAS2 2008 Falcon. I'm working on > installing EDB. Then I can give you some I

Re: [PERFORM] Tuning Postgres 9.1 on Windows

On Tue, May 1, 2012 at 8:14 AM, Walker, James Les wrote: > SSD is OCZ-VERTEX3 MI. Controller is LSI SAS2 2008 Falcon. I'm working on > installing EDB. Then I can give you some I/O numbers. It looks like the ssd doesn't have a nv cache and the raid card is a simple sas hba (which likely isn't doi

Re: [PERFORM] Tuning Postgres 9.1 on Windows

On 5/1/2012 8:06 AM, Merlin Moncure wrote: On Tue, May 1, 2012 at 7:51 AM, Walker, James Les wrote: Exactly, if turning off fsync gives me 100 commits/sec then I know where my bottleneck is and I can attack it. Keep in mind though that I already turned off synchronous commit -- *really* dange

Re: [PERFORM] Tuning Postgres 9.1 on Windows

lerer; pgsql-performance@postgresql.org Subject: Re: [PERFORM] Tuning Postgres 9.1 on Windows On Tue, May 1, 2012 at 7:51 AM, Walker, James Les wrote: > Exactly, if turning off fsync gives me 100 commits/sec then I know where my > bottleneck is and I can attack it. Keep in mind though that I alr

Re: [PERFORM] Tuning Postgres 9.1 on Windows

On Tue, May 1, 2012 at 7:51 AM, Walker, James Les wrote: > Exactly, if turning off fsync gives me 100 commits/sec then I know where my > bottleneck is and I can attack it. Keep in mind though that I already turned > off synchronous commit -- *really* dangerous -- and it didn't have any effect.

Re: [PERFORM] Tuning Postgres 9.1 on Windows

ce-ow...@postgresql.org [mailto:pgsql-performance-ow...@postgresql.org] On Behalf Of Merlin Moncure Sent: Monday, April 30, 2012 6:04 PM To: Thomas Kellerer Cc: pgsql-performance@postgresql.org Subject: Re: [PERFORM] Tuning Postgres 9.1 on Windows On Mon, Apr 30, 2012 at 5:00 PM, Thomas Kellerer

Re: [PERFORM] Tuning Postgres 9.1 on Windows

On Mon, Apr 30, 2012 at 5:00 PM, Thomas Kellerer wrote: > Merlin Moncure wrote on 30.04.2012 23:43: > >> Trying turning off fsync in postgrsql.conf to be sure. > > > This is a dangerous advise. > Turning off fsync can potentially corrupt the database in case of a system > failure (e.g. power outag

Re: [PERFORM] Tuning Postgres 9.1 on Windows

Merlin Moncure wrote on 30.04.2012 23:43: Trying turning off fsync in postgrsql.conf to be sure. This is a dangerous advise. Turning off fsync can potentially corrupt the database in case of a system failure (e.g. power outage). -- Sent via pgsql-performance mailing list (pgsql-performan

Re: [PERFORM] Tuning Postgres 9.1 on Windows

On Mon, Apr 30, 2012 at 8:49 AM, Walker, James Les wrote: > I’m trying to benchmark Postgres vs. several other databases on my > workstation. My workstation is running 64 bit Windows 7. It has 12 gb of RAM > and a W3550 @ 3 Ghz. I installed Postgres 9.1 using the windows installer. > The data dire

Re: [PERFORM] Tuning Postgres 9.1 on Windows

On 4/30/2012 8:49 AM, Walker, James Les wrote: I’m trying to benchmark Postgres vs. several other databases on my workstation. My workstation is running 64 bit Windows 7. It has 12 gb of RAM and a W3550 @ 3 Ghz. I installed Postgres 9.1 using the windows installer. The data directory is on a 6Gb/

Re: [PERFORM] Tuning wizard

On Mon, Mar 12, 2012 at 10:07 PM, Scott Marlowe wrote: > On Mon, Mar 12, 2012 at 10:03 AM, Tomas Vondra wrote: > > On 9 Březen 2012, 16:07, Michael Kopljan wrote: > >> I just upgraded to 9.1 and downloaded EnterpriseDB Tuning wizard, but > the > >> list of servers in ComboBox support only 8.2, 8.

Re: [PERFORM] Tuning wizard

On Mon, Mar 12, 2012 at 10:03 AM, Tomas Vondra wrote: > On 9 Březen 2012, 16:07, Michael Kopljan wrote: >> I just upgraded to 9.1 and downloaded EnterpriseDB Tuning wizard, but the >> list of servers in ComboBox support only 8.2, 8.3 i 8.4 version and x86 >> build. How can I  tune 9.1, 64 bit vers

Re: [PERFORM] Tuning wizard

On 9 Březen 2012, 16:07, Michael Kopljan wrote: > I just upgraded to 9.1 and downloaded EnterpriseDB Tuning wizard, but the > list of servers in ComboBox support only 8.2, 8.3 i 8.4 version and x86 > build. How can I tune 9.1, 64 bit version? > > Is there any workaround, other version for download

Re: [PERFORM] Tuning Tips for a new Server

On 17 Srpen 2011, 21:22, Ogden wrote: >> This is a very important point. I've found on most machines with >> hardware caching RAID and 8 or fewer 15k SCSI drives it's just as >> fast to put it all on one big RAID-10 and if necessary partition it to >> put the pg_xlog on its own file system. Afte

Re: [PERFORM] Tuning Tips for a new Server

On Aug 17, 2011, at 2:14 PM, Scott Marlowe wrote: > On Wed, Aug 17, 2011 at 12:56 PM, Tomas Vondra wrote: >> >> I think you've mentioned the database is on 6 drives, while the other >> volume is on 2 drives, right? That makes the OS drive about 3x slower >> (just a rough estimate). But if the d

Re: [PERFORM] Tuning Tips for a new Server

On Wed, Aug 17, 2011 at 12:56 PM, Tomas Vondra wrote: > > I think you've mentioned the database is on 6 drives, while the other > volume is on 2 drives, right? That makes the OS drive about 3x slower > (just a rough estimate). But if the database drive is used heavily, it > might help to move the

Re: [PERFORM] Tuning Tips for a new Server

On Aug 17, 2011, at 1:56 PM, Tomas Vondra wrote: > On 17 Srpen 2011, 18:39, Ogden wrote: >>> Yes, but it greatly depends on the amount of WAL and your workload. If >>> you >>> need to write a lot of WAL data (e.g. during bulk loading), this may >>> significantly improve performance. It may also h

Re: [PERFORM] Tuning Tips for a new Server

On 17 Srpen 2011, 18:39, Ogden wrote: >> Yes, but it greatly depends on the amount of WAL and your workload. If >> you >> need to write a lot of WAL data (e.g. during bulk loading), this may >> significantly improve performance. It may also help when you have a >> write-heavy workload (a lot of cli

Re: [PERFORM] Tuning Tips for a new Server

On Aug 17, 2011, at 9:44 AM, Tomas Vondra wrote: > On 17 Srpen 2011, 3:35, Ogden wrote: >> Hope all is well. I have received tremendous help from this list prior and >> therefore wanted some more advice. >> >> I bought some new servers and instead of RAID 5 (which I think greatly >> hindered our

Re: [PERFORM] Tuning Tips for a new Server

On 17 Srpen 2011, 16:28, Ogden wrote: > I was wondering what the best parameters would be with my new setup. The > work_mem obviously will increase as will everything else as it's a 64Gb > machine as opposed to a 16Gb machine. The configuration I posted was for > a 16Gb machine but this new one is

Re: [PERFORM] Tuning Tips for a new Server

On 17 Srpen 2011, 3:35, Ogden wrote: > Hope all is well. I have received tremendous help from this list prior and > therefore wanted some more advice. > > I bought some new servers and instead of RAID 5 (which I think greatly > hindered our writing performance), I configured 6 SCSI 15K drives with

Re: [PERFORM] Tuning Tips for a new Server

On Aug 17, 2011, at 8:41 AM, Andy Colson wrote: > On 8/16/2011 8:35 PM, Ogden wrote: >> Hope all is well. I have received tremendous help from this list prior and >> therefore wanted some more advice. >> >> I bought some new servers and instead of RAID 5 (which I think greatly >> hindered our

Re: [PERFORM] Tuning Tips for a new Server

On 8/16/2011 8:35 PM, Ogden wrote: Hope all is well. I have received tremendous help from this list prior and therefore wanted some more advice. I bought some new servers and instead of RAID 5 (which I think greatly hindered our writing performance), I configured 6 SCSI 15K drives with RAID 10

Re: [PERFORM] tuning on ec2

> Sounds like a reasonable starting point. You could certainly fiddle > around a bit - especially with shared_buffers - to see if some other > setting works better, but that should be in the ballpark. I tend to set it a bit higher on EC2 to discourage the VM from overcommitting memory I need. S

Re: [PERFORM] tuning on ec2

On Tue, Apr 26, 2011 at 11:15 AM, Joel Reymont wrote: > I'm running pgsql on an m1.large EC2 instance with 7.5gb available memory. > > The free command shows 7gb of free+cached. My understand from the docs is > that I should dedicate 1.75gb to shared_buffers (25%) and set > effective_cache_size

Re: [PERFORM] Tuning massive UPDATES and GROUP BY's?

> Bulk data imports of this size I've done with minimal pain by simply > breaking the raw data into chunks (10M records becomes 10 files of > 1M records), on a separate spindle from the database, and performing > multiple COPY commands but no more than 1 COPY per server core. > I

Re: [PERFORM] Tuning massive UPDATES and GROUP BY's?

On Mon, Mar 14, 2011 at 4:17 AM, Marti Raudsepp wrote: > On Sun, Mar 13, 2011 at 18:36, runner wrote: > > Other than being very inefficient, and consuming > > more time than necessary, is there any other down side to importing > > into an indexed table? > > Doing so will result in somewhat large

Re: [PERFORM] Tuning massive UPDATES and GROUP BY's?

On Sun, Mar 13, 2011 at 18:36, runner wrote: > Tried removing the indexes and other constraints just for > the import but for a noob like me, this was too much to ask. Maybe > when I get more experience. pgAdmin should make it pretty easy. Choose each index and constraint, save the code from the

Re: [PERFORM] Tuning massive UPDATES and GROUP BY's?

>Don't insert data into an indexed table. A very important point with >bulk-loading is that you should load all the data first, then create >the indexes. Running multiple (different) CREATE INDEX queries in >parallel can additionally save a lot of time. Also don't move data >back and forth betwee

Re: [PERFORM] Tuning massive UPDATES and GROUP BY's?

On Fri, Mar 11, 2011 at 21:06, fork wrote: > Like the following?  Will it rebuild the indexes in a sensical way? Don't insert data into an indexed table. A very important point with bulk-loading is that you should load all the data first, then create the indexes. Running multiple (different) CREA

Re: [PERFORM] Tuning massive UPDATES and GROUP BY's?

Marti Raudsepp juffo.org> writes: > If you don't mind long recovery times in case of a crash, set > checkpoint_segments to ~100 and checkpoint_completion_target=0.9; this > will improve write throughput significantly. Sounds good. > Also, if you don't mind CORRUPTing your database after a crash

Re: [PERFORM] Tuning massive UPDATES and GROUP BY's?

On Thu, Mar 10, 2011 at 17:40, fork wrote: > The data is not particularly sensitive; if something happened and it rolled > back, that wouldnt be the end of the world.  So I don't know if I can use > "dangerous" setting for WAL checkpoints etc.   There are also aren't a lot of > concurrent hits on

Re: [PERFORM] Tuning massive UPDATES and GROUP BY's?

Merlin Moncure gmail.com> writes: > > I am loathe to create a new table from a select, since the indexes > > themselves > > take a really long time to build. > > you are aware that updating the field for the entire table, especially > if there is an index on it (or any field being updated), wil

Re: [PERFORM] Tuning massive UPDATES and GROUP BY's?

On Thu, Mar 10, 2011 at 9:40 AM, fork wrote: > Given that doing a massive UPDATE SET foo = bar || ' ' || baz; on a 12 million > row table (with about 100 columns -- the US Census PUMS for the 2005-2009 ACS) > is never going to be that fast, what should one do to make it faster? > > I set work_mem

Re: [PERFORM] Tuning Postgresql on Windows XP Pro 32 bit [was on HACKERS list]

>>> On Tue, Jan 15, 2008 at 11:05 AM, in message <[EMAIL PROTECTED]>, Doug Knight <[EMAIL PROTECTED]> wrote: > We tried reducing the memory footprint of the postgres processes, via > shared_buffers (from 3 on Linux to 3000 on Windows), I would never go below 1. 2 to 3 is a goo

Re: [PERFORM] tuning for TPC-C benchmark

[EMAIL PROTECTED] wrote: Hello all, I'm doing tests on various Database and in particular I'm running a comparison between Oracle 10g and Postgres 8.1 on a dedicated server with 2 processors Dual-Core AMD Opteron 2218 2.6 GHz, 4GB of memory and Debian GNU / Linux version 2.6.18-5. Performance

Re: [PERFORM] tuning for TPC-C benchmark

On Nov 22, 2007 10:45 AM, Kevin Grittner <[EMAIL PROTECTED]> wrote: > I suggest testing with some form of connection pooling. Yeah, that's one of the reasons I suggested DBT-2. It pools connections and is the most mature TPC-C-like test for Postgres. -- Jonah H. Harris, Sr. Software Architect |

Re: [PERFORM] tuning for TPC-C benchmark

>>> "[EMAIL PROTECTED]" <[EMAIL PROTECTED]> 11/22/07 8:09 AM >>> > Performance is very similar up > to 30 users, but from 40 onwards with Postgres fall quickly. I suggest testing with some form of connection pooling. Many database products will queue requests in those situations; with PostgreSQL

Re: [PERFORM] tuning for TPC-C benchmark

On Nov 22, 2007 9:09 AM, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: > I'm doing tests on various Database and in particular I'm running a > comparison between Oracle 10g and Postgres 8.1 on a dedicated server As Bill said, do not publish any part of the Oracle result anywhere. > with 2 processo

Re: [PERFORM] tuning for TPC-C benchmark

"[EMAIL PROTECTED]" <[EMAIL PROTECTED]> wrote: > > Hello all, > I'm doing tests on various Database and in particular I'm running a > comparison between Oracle 10g and Postgres 8.1 on a dedicated server > with 2 processors Dual-Core AMD Opteron 2218 2.6 GHz, 4GB of memory > and Debian GNU / Lin

Re: [PERFORM] Tuning Help - What did I do wrong?

On Thu, 04 Oct 2007 14:03:07 -0500 "Kevin Grittner" <[EMAIL PROTECTED]> wrote: > It's kind of silly to tell PostgreSQL that its total cache space is > 1 pages when you've got more than that in shared buffers plus > all that OS cache space. Try something around 285000 pages for > effective_cac

Re: [PERFORM] Tuning Help - What did I do wrong?

>>> On Thu, Oct 4, 2007 at 10:28 AM, in message <[EMAIL PROTECTED]>, Josh Trutwin <[EMAIL PROTECTED]> wrote: > running postgres 8.1.4 > # cat /proc/meminfo > total:used:free: shared: buffers: cached: > Mem: 3704217600 3592069120 1121484800 39460864 2316271616 > share

Re: [PERFORM] Tuning Help - What did I do wrong?

On 10/4/07, Josh Trutwin <[EMAIL PROTECTED]> wrote: > On Thu, 4 Oct 2007 11:19:22 -0500 > "Scott Marlowe" <[EMAIL PROTECTED]> wrote: > > > We need to see examples of what's slow, including explain analyze > > output for slow queries. Also a brief explanation of the type of > > load your database s

Re: [PERFORM] Tuning Help - What did I do wrong?

On Thu, 4 Oct 2007 11:19:22 -0500 "Scott Marlowe" <[EMAIL PROTECTED]> wrote: > We need to see examples of what's slow, including explain analyze > output for slow queries. Also a brief explanation of the type of > load your database server is seeing. I.e. is it a lot of little > transactions, mo

Re: [PERFORM] Tuning Help - What did I do wrong?

Oh, and in addition to my previous message, you should use tools like vmstat, iostat and top to get an idea of what your server is doing. What kind of drive subsystem do you have? What kind of raid controller? etc... ---(end of broadcast)--- TIP

Re: [PERFORM] Tuning Help - What did I do wrong?

On 10/4/07, Josh Trutwin <[EMAIL PROTECTED]> wrote: > We have a pretty busy linux server running postgres 8.1.4, waiting to > upgrade until 8.3 to avoid dump/restoring twice. You should immediate update your version to 8.1.whateverislatest. That requires no dump / restore and it is a bug fix updat

Re: [PERFORM] Tuning for warm standby

On 9/27/07, Kevin Kempter <[EMAIL PROTECTED]> wrote: > Hi All; > > I'm preparing to fire up WAL archiving on 8 production servers We will follow > up with implementing a warm standby scenariio. > > Does anyone have any thoughts per how to maximize performance, yet minimize > the potential for data

Re: [PERFORM] tuning a function to insert/retrieve values from a reference table

"Marc Mamin" <[EMAIL PROTECTED]> writes: > Can I optimize this function with: > a) remove the EXCEPTION clause (Is there an underlying lock that prevent > concurrent inserts ?) No. > b) declare the function being IMMUTABLE ? Certainly not --- it's got side-effects. rega

Re: [PERFORM] Tuning

Tuners, allways be aware that results on Windows may be totally different! My main customer is running PostgreSQL 8.1 on MINIMUM shared buffers max_connections = 100# shared_buffers = 200# min 16 or max_connections*2, 8KB each I changed it to this value from the very low de

Re: [PERFORM] Tuning

> -Original Message- > From: [EMAIL PROTECTED] [mailto:pgsql-performance- > [EMAIL PROTECTED] On Behalf Of John Parnefjord > Sent: Tuesday, January 30, 2007 2:05 AM > Subject: Re: [PERFORM] Tuning > EnterpriseDB advocates: 250 KB + 8.2 KB * shared_buffers + 14.2 kB * &g

Re: [PERFORM] Tuning

> What are Oracle and EnterpriseDB recommending for shmmax these days? According to Oracle "set to a value half the size of physical memory". [http://www.oracle.com/technology/tech/linux/validated-configurations/ht ml/vc_dell6850-rhel4-cx500-1_1.html] I've been talking to an Oracle DBA and he sa

Re: [PERFORM] Tuning

At 06:24 PM 1/28/2007, Josh Berkus wrote: John, > -work_mem Depends on the number of concurrent queries you expect to run and what size sorts you expect them to do. EXPLAIN ANALYZE is your friend. It will tell you how much data each query is manipulating and therefore how much memory each que

Re: [PERFORM] Tuning

Josh Berkus writes: >> -checkpoint_segments - this is crucial as one of the server is >> transaction heavy > Well, it only helps you to raise this if you have a dedicated disk resource > for the xlog. Otherwise having more segments doesn't help you much. Au contraire, large checkpoint_segment

Re: [PERFORM] Tuning

John, > -work_mem Depends on the number of concurrent queries you expect to run and what size sorts you expect them to do. > -maintenance_work_mem - 50% of the largest table? Actually, in current code I've found that anything over 256mb doesn't actually get used. > -shared_buffers - max valu

Re: [PERFORM] Tuning

Hello ! Am Freitag 26 Januar 2007 12:28 schrieb John Parnefjord: > Hi! > > I'm planning to move from mysql to postgresql as I believe the latter > performs better when it comes to complex queries. The mysql database > that I'm running is about 150 GB in size, with 300 million rows in the > largest

Re: [PERFORM] Tuning

On 26-Jan-07, at 6:28 AM, John Parnefjord wrote: Hi! I'm planning to move from mysql to postgresql as I believe the latter performs better when it comes to complex queries. The mysql database that I'm running is about 150 GB in size, with 300 million rows in the largest table. We do quite a l

  1   2   3   >