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:

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

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

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

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

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

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

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

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

gresql.org <pgsql-performance-ow...@postgresql.org> on behalf of Kaixi Luo <kaixi...@gmail.com> 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

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

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

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

ostgresql.org> on behalf of Merlin Moncure <mmonc...@gmail.com> 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 <kaixi...@gmail.com> w

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

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

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

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 from

Re: [PERFORM] Tuning the configuration

On 16 Dec 2014, at 14:51, Graeme B. Bell g...@skogoglandskap.no 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

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

-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 mfatticci...@mbigroup.it wrote: Hello. I need to tune a postgres installation I've just made to get a better performance. I use two identical

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

On 11 Dec 2014, at 15:02, Andrea Suisani sick...@opinioni.net 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

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 sick...@opinioni.net 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

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

Re: [PERFORM] Tuning the configuration

On 12/11/2014 01:11 PM, Evgeniy Shishkin wrote: On 11 Dec 2014, at 15:02, Andrea Suisani sick...@opinioni.net 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

Re: [PERFORM] Tuning the configuration

@postgresql.org Subject: Re: [PERFORM] Tuning the configuration On 11 Dec 2014, at 15:02, Andrea Suisani sick...@opinioni.net 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

Re: [PERFORM] Tuning the configuration

...@mbigroup.it; pgsql-performance@postgresql.org Subject: Re: [PERFORM] Tuning the configuration On 11 Dec 2014, at 15:02, Andrea Suisani sick...@opinioni.net 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

Re: [PERFORM] Tuning the configuration

On Wed, Dec 10, 2014 at 2:44 AM, Maila Fatticcioni mfatticci...@mbigroup.it 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

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 jeff.ja...@gmail.com wrote: On Monday, August 25, 2014, Jeison Bedoya Delgado jeis...@audifarma.com.co wrote: hi, recently i change the hardware of my

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

Re: [PERFORM] tuning postgresql 9.3.5 and multiple cores

On Monday, August 25, 2014, Jeison Bedoya Delgado jeis...@audifarma.com.co 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

Re: [PERFORM] Tuning Postgres for Single connection use

On Mon, Apr 14, 2014 at 5:19 PM, Jeff Janes jeff.ja...@gmail.com wrote: On Mon, Apr 14, 2014 at 2:46 PM, Nick Eubank nickeub...@gmail.com 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

Re: [PERFORM] Tuning Postgres for Single connection use

On Tue, Apr 15, 2014 at 9:12 AM, Nick Eubank nickeub...@gmail.com wrote: On Mon, Apr 14, 2014 at 5:19 PM, Jeff Janes jeff.ja...@gmail.com 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

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

Jeff Janes jeff.ja...@gmail.com writes: On Tue, Apr 15, 2014 at 9:12 AM, Nick Eubank nickeub...@gmail.com 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

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 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 gavinflo...@archidevsys.co.nz wrote: On 15/04/14 09:46, Nick Eubank wrote: Any

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

On Mon, Apr 14, 2014 at 2:46 PM, Nick Eubank nickeub...@gmail.com 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

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

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 gavinflo...@archidevsys.co.nz wrote: In this list, please bottom post! I've added potentially useful advice below. On 15/04/14 11:39, Nick Eubank wrote:

Re: [PERFORM] Tuning Postgres 9.1 on Windows

...@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 spam_ea

Re: [PERFORM] Tuning Postgres 9.1 on Windows

On Tue, May 1, 2012 at 7:51 AM, Walker, James Les jawal...@cantor.com 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

Re: [PERFORM] Tuning Postgres 9.1 on Windows

; 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 jawal...@cantor.com 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

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 Lesjawal...@cantor.com 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

Re: [PERFORM] Tuning Postgres 9.1 on Windows

On Tue, May 1, 2012 at 8:14 AM, Walker, James Les jawal...@cantor.com 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

Re: [PERFORM] Tuning Postgres 9.1 on Windows

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 jawal...@cantor.com 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

Re: [PERFORM] Tuning Postgres 9.1 on Windows

On Tue, May 1, 2012 at 9:44 AM, Walker, James Les jawal...@cantor.com 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?

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

Re: [PERFORM] Tuning Postgres 9.1 on Windows

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 exactly

Re: [PERFORM] Tuning Postgres 9.1 on Windows

[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 is very likely that the default

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

Re: [PERFORM] Tuning Postgres 9.1 on Windows

On Mon, Apr 30, 2012 at 8:49 AM, Walker, James Les jawal...@cantor.com 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

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

Re: [PERFORM] Tuning Postgres 9.1 on Windows

On Mon, Apr 30, 2012 at 5:00 PM, Thomas Kellerer spam_ea...@gmx.net 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.

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 wizard

On Mon, Mar 12, 2012 at 10:03 AM, Tomas Vondra t...@fuzzy.cz 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

Re: [PERFORM] Tuning wizard

On Mon, Mar 12, 2012 at 10:07 PM, Scott Marlowe scott.marl...@gmail.comwrote: On Mon, Mar 12, 2012 at 10:03 AM, Tomas Vondra t...@fuzzy.cz 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

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

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 writing

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 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 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 writing

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 clients

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 help when you

Re: [PERFORM] Tuning Tips for a new Server

On Wed, Aug 17, 2011 at 12:56 PM, Tomas Vondra t...@fuzzy.cz 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

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 t...@fuzzy.cz 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

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. After that

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. So,

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

On Sun, Mar 13, 2011 at 18:36, runner run...@winning.com 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

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

On Mon, Mar 14, 2011 at 4:17 AM, Marti Raudsepp ma...@juffo.org wrote: On Sun, Mar 13, 2011 at 18:36, runner run...@winning.com 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

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 between

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

On Fri, Mar 11, 2011 at 21:06, fork forkandw...@gmail.com 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

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

Marti Raudsepp marti at 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

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

On Thu, Mar 10, 2011 at 9:40 AM, fork forkandw...@gmail.com 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?

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

Merlin Moncure mmoncure at 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),

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

On Thu, Mar 10, 2011 at 17:40, fork forkandw...@gmail.com 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

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 good start.

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 / Linux

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] 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.

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 update.

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 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, mostly

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 server is

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 shared_buffers =

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

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 loss

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. regards,

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

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 said

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

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 value

Re: [PERFORM] Tuning

Josh Berkus josh@agliodbs.com 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

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

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 New Server (slow function)

Ron St-Pierre [EMAIL PROTECTED] writes: We just purchased a new Dell PowerEdge 2800 (dual xeon, 8GB RAM, raid 4, RHEL, postgres 8.1) and ported our old database over to it (single cpu, 2GB RAM, no raid, postgres 7.4). Our apps perform great on it, however some queries are super slow. One

Re: [PERFORM] Tuning New Server (slow function)

On Jun 21, 2006, at 5:53 PM, Ron St-Pierre wrote: Jim C. Nasby wrote: On Wed, Jun 21, 2006 at 02:27:41PM -0700, Ron St-Pierre wrote: We just purchased a new Dell PowerEdge 2800 (dual xeon, 8GB RAM, raid 4, RHEL, postgres 8.1) and ported our old database over to it (single cpu, RAID *4*?

Re: [PERFORM] Tuning New Server (slow function)

On Wed, Jun 21, 2006 at 02:27:41PM -0700, Ron St-Pierre wrote: We just purchased a new Dell PowerEdge 2800 (dual xeon, 8GB RAM, raid 4, RHEL, postgres 8.1) and ported our old database over to it (single cpu, RAID *4*? If you do any kind of updating at all, you're likely to be real unhappy

Re: [PERFORM] Tuning New Server (slow function)

Jim C. Nasby wrote: On Wed, Jun 21, 2006 at 02:27:41PM -0700, Ron St-Pierre wrote: We just purchased a new Dell PowerEdge 2800 (dual xeon, 8GB RAM, raid 4, RHEL, postgres 8.1) and ported our old database over to it (single cpu, RAID *4*? oops, raid 5 (but we are getting good io

Re: [PERFORM] tuning seqscan costs

On Oct 19, 2005, at 9:51 AM, Katherine Stoovs wrote: I want to correlate two index rows of different tables to find an offset so that table1.value = table2.value AND table1.id = table2.id + offset is true for a maximum number of rows. To achieve this, I have the two tables and a table with

  1   2   >