Re: [PERFORM] Any experience using "shake" defragmenter?
* Mark Felder: > Why do you feel the need to defrag your *nix box? Some file systems (such as XFS) read the whole extent list into RAM when a file is opened. When the extend list is long due to fragmentation, this can take a *long* time (in the order of minutes with multi-gigabyte Oracle Berkeley DB files). This phenomenon is less pronounced with PostgreSQL because it splits large relations into one-gigabyte chunks, and it writes the files sequentally. But a small effect is probably still there. -- Florian Weimer BFK edv-consulting GmbH http://www.bfk.de/ Kriegsstraße 100 tel: +49-721-96201-1 D-76133 Karlsruhe fax: +49-721-96201-99 -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Any experience using "shake" defragmenter?
* Mladen Gogala: > Did anyone try using "shake" while the cluster is active? As far as I can tell, it's totally unsafe. -- Florian Weimer BFK edv-consulting GmbH http://www.bfk.de/ Kriegsstraße 100 tel: +49-721-96201-1 D-76133 Karlsruhe fax: +49-721-96201-99 -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Bloat issue on 8.3; autovac ignores HOT new pages?
Robert, Mark, I have not been able to reproduce this issue in a clean test on 9.0. As a result, I now think that it was related to the FSM being too small on the user's 8.3 instance, and will consider it resolved. -- -- Josh Berkus PostgreSQL Experts Inc. http://www.pgexperts.com -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Migrating to Postgresql and new hardware
Interesting. Would have been nice if the test was with a raid-10 setup as raid-5 is not very good for writes... Would you get much of a performance increase with a write-cached ssd even if you got a raid controller with (battery-backed) cache? /Lars -Ursprungligt meddelande- Från: Greg Smith [mailto:g...@2ndquadrant.com] Skickat: den 29 januari 2011 01:27 Till: Lars Kopia: mark; pgsql-performance@postgresql.org Ämne: Re: [PERFORM] Migrating to Postgresql and new hardware Lars wrote: > Below is a quote from the Pliant datasheet: > "No Write Cache: > Pliant EFDs deliver outstanding > write performance > without any dependence on > write cache and thus does > not use battery/supercap." > I liked the article The Register wrote about them, with the headline "Pliant's SSDs are awesome, says Pliant". Of course they do. Check out the write benchmark figures in the information review at http://oliveraaltonen.com/2010/09/29/preliminary-benchmark-results-of-the-pliant-ssd-drives/ to see how badly performance suffers on their design from those decisions. The Fusion I/O devices get nearly an order of magnitude more write IOPS in those tests. As far as I've been able to tell, what Pliant does is just push writes out all the time without waiting for them to be aligned with block sizes, followed by cleaning up the wreckage later via their internal automatic maintenance ASICs (it's sort of an always on TRIM implementation if I'm guessing right). That has significant limitations both in regards to total write speed as well as device longevity. For a database, I'd much rather have a supercap and get ultimate write performance without those downsides. Depends on the read/write ratio though; I could see a heavily read-biased system work well with their approach. Of course, a heavily read-based system would be better served by having a ton of RAM instead in most cases. Could be worst though--they could be misleading about the whole topic of write durability like Intel is. I consider claiming high performance when you don't always really have it, what Pliant is doing here, to be a much lesser sin than losing data at random and not being clear about when that can happen. I'd like FusionIO to put a big "expect your server to be down for many minutes after a power interruption" warning on their drives, too, while I'm wishing for complete vendor transparency here. -- Greg Smith 2ndQuadrant USg...@2ndquadrant.com Baltimore, MD PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.us "PostgreSQL 9.0 High Performance": http://www.2ndQuadrant.com/books -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Any experience using "shake" defragmenter?
On 01/30/2011 11:38 PM, Mladen Gogala wrote: Mark Felder wrote: Why do you feel the need to defrag your *nix box? Let's stick to the original question and leave my motivation for some other time. Have you used the product? If you have, I'd be happy to hear about your experience with it. That seems a little harsh. You post to a discussion group but want to suppress discussion? Maybe that works with paid tech-support staff, but here ... -- Lew Ceci n'est pas une fenêtre. .___. |###] | [###| |##/ | *\##| |#/ * | \#| |#|#| ||| * || |o * |o| |_|_| |===| -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Bloat issue on 8.3; autovac ignores HOT new pages?
On 01/02/11 07:27, Josh Berkus wrote: Robert, Mark, I have not been able to reproduce this issue in a clean test on 9.0. As a result, I now think that it was related to the FSM being too small on the user's 8.3 instance, and will consider it resolved. Right - it might be interesting to see if you can reproduce on 8.4. I would hazard a guess that you will not (on disk FSM + visibility map vacuum improvements seem to make this whole area way better). Cheers Mark
Re: [PERFORM] Bloat issue on 8.3; autovac ignores HOT new pages?
On Mon, Jan 31, 2011 at 11:27 AM, Josh Berkus wrote: > Robert, Mark, > > I have not been able to reproduce this issue in a clean test on 9.0. As > a result, I now think that it was related to the FSM being too small on > the user's 8.3 instance, and will consider it resolved. I used to try and size free space map to be a little bigger than it needed to be. I now size 4 or 5 times what it needs to be. shared memory is cheap. So is going to 8.4, but on legacy systems that you can't upgrade, 8.3 with a huge FSM works well enough (with suitably aggressive autovac). -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
[PERFORM] Configuration for a new server.
Scott, I don't know if you received my private email, but just in case you did not I am posting the infomration here. I have a new set of servers coming in - Dual Xeon E5620's, 96GB RAM, 18 spindles (1 RAID1 for OS - SATA, 12 disk RAID10 for data - SAS, RAID-1 for logs - SAS, 2 hot spares SAS). They are replacing a single Dual Xeon E5406 with 16GB RAM and 2x RAID1 - one for OS/Data, one for Logs. Current server is using 3840MB of shared buffers. It will be running FreeBSD 8.1 x64, PG 9.0.2, running streaming replication to a like server. I have read the performance tuning book written by Greg Smith, and am using it as a guide to configure it for performance. The main questions which I have are the following: Is the 25% RAM for shared memory still a good number to go with for this size server? There are approximately 50 tables which get updated with almost 100% records updated every 5 minutes - what is a good number of autovacuum processes to have on these? The current server I am replacing only has 3 of them but I think I may gain a benefit from having more. Currently I have what I believe to be an aggressive bgwriter setting as follows: bgwriter_delay = 200ms # 10-1ms between rounds bgwriter_lru_maxpages = 1000# 0-1000 max buffers written/round bgwriter_lru_multiplier = 10# 0-10.0 multipler on buffers scanned/round Does this look right? I have the following settings: work_mem = 64MB # min 64kB maintenance_work_mem = 128MB# min 1MB And, of course, some of the most critical ones - the WAL settings. Right now, in order to give the best performance to the end users due to the size of the current box, I have a very unoptimal setting in my opinion fsync = off # turns forced synchronization on or off #synchronous_commit = on# immediate fsync at commit #wal_sync_method = fsync# the default is the first option # supported by the operating system: # open_datasync # fdatasync # fsync # fsync_writethrough # open_sync full_page_writes = on # recover from partial page writes wal_buffers = 16MB #wal_buffers = 1024KB # min 32kB # (change requires restart) # wal_writer_delay = 100ms # 1-1 milliseconds #commit_delay = 0 # range 0-10, in microseconds #commit_siblings = 5# range 1-1000 # - Checkpoints - #checkpoint_segments = 128 # in logfile segments, min 1, 16MB each checkpoint_segments = 1024 checkpoint_timeout = 60min # range 30s-1h #checkpoint_completion_target = 0.5 # checkpoint target duration, 0.0 - 1.0 checkpoint_completion_target = 0.1 checkpoint_warning = 45min # 0 disables These are values which I arrived to by playing with them to make sure that the end user performance did not suffer. The checkpoints are taking about 8 minutes to complete, but between checkpoints the disk i/o on the data partition is very minimal - when I had lower segments running a 15 minute timeout with a .9 completion target, the platform was fairly slow vis-à-vis the end user. The above configuration is using PG 8.4. Thanks in advance for any insight.
Re: [PERFORM] Questions on query planner, join types, and work_mem
Robert Haas wrote: > On Wed, Aug 11, 2010 at 9:42 PM, Bruce Momjian wrote: > > This confused me. ?If we are assuing the data is in > > effective_cache_size, why are we adding sequential/random page cost to > > the query cost routines? > > See the comments for index_pages_fetched(). We basically assume that > all data starts uncached at the beginning of each query - in fact, > each plan node. effective_cache_size only measures the chances that > if we hit the same block again later in the execution of something > like a nested-loop-with-inner-indexscan, it'll still be in cache. > > It's an extremely weak knob, and unless you have tables or indices > that are larger than RAM, the only mistake you can make is setting it > too low. The attached patch documents that there is no assumption that data remains in the disk cache between queries. I thought this information might be helpful. -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml index 77cacdd..520170b 100644 *** a/doc/src/sgml/config.sgml --- b/doc/src/sgml/config.sgml *** SET ENABLE_SEQSCAN TO OFF; *** 2424,2430 space. This parameter has no effect on the size of shared memory allocated by PostgreSQL, nor does it reserve kernel disk cache; it is used only for estimation ! purposes. The default is 128 megabytes (128MB). --- 2424,2432 space. This parameter has no effect on the size of shared memory allocated by PostgreSQL, nor does it reserve kernel disk cache; it is used only for estimation ! purposes. The system also does not assume data remains in ! the disk cache between queries. The default is 128 megabytes ! (128MB). -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
