[PERFORM] Postgres 8.x on Windows Server in production
Hi all, What are your experiences with Postgres 8.x in production use on Windows Server 2003/2008? Are there any limitations, trade-offs or quirks? My client is accustomed to Windows Server environment, but it seems hard to google good information about these types of installations. Regards, Ognjen -- 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] Nested query performance issue
Glenn Maynard writes: > http://www.postgresql.org/docs/8.3/static/xfunc-sql.html says this is > deprecated (though no deprecation warning is being generated): >> Currently, functions returning sets can also be called in the select list of >> a query. For each row that the query generates by itself, the function >> returning set is invoked, and an output row is generated for each element of >> the function's result set. Note, however, that this capability is deprecated >> and might be removed in future releases. The way to parse that is "we don't like this and we will get rid of it if we can ever figure out a good substitute". Right now there is no 100% substitute, so it stays. (In fact, 8.4 will extend the feature so it works in cases that don't work today, like for PL functions.) There are, however, good reasons not to like it, such as the rather questionable behavior if there's more than one SRF in the same select list. Don't complain if you run into that wart. regards, tom lane -- 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] Shouldn't the planner have a higher cost for reverse index scans?
Josh Berkus writes: > Now, what that says to me is that for this system reverse sequential > reads are 1/4 the speed of forwards reads. And from my testing > elsewhere, that seems fairly typical of disk systems in general. Well, that's because filesystems try to lay out files so that logically successive sectors are about as far apart as needed to support the disk's maximum transfer rate. If you fetch them in reverse order, then instead of optimizing the rotational latency you find you are pessimizing it. This has got approximately nothing to do with indexscans, either forward or reverse, because then we aren't fetching blocks in a pre-optimized order. > Now, while index scans (for indexes on disk) aren't 100% sequential > reads, it seems like we should be increasing (substantially) the > estimated cost of reverse index scans if the index is likely to be on > disk. No? AFAICS this is already folded into random_page_cost. regards, tom lane -- 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] Using IOZone to simulate DB access patterns
On Fri, 2009-04-03 at 17:09 -0700, Josh Berkus wrote: > On 4/3/09 4:12 PM, Josh Berkus wrote: > > All, > > > > I've been using Bonnie++ for ages to do filesystem testing of new DB > > servers. But Josh Drake recently turned me on to IOZone. > > Related to this: is IOZone really multi-threaded? I'm doing a test run > right now, and only one CPU is actually active. While there are 6 > IOZone processes, most of them are idle. In order to test real interactivity (AFAIK) with iozone you have to launch multiple iozone instances. You also need to do them from separate directories, otherwise it all starts writing the same file. The work I did here: http://www.commandprompt.com/blogs/joshua_drake/2008/04/is_that_performance_i_smell_ext2_vs_ext3_on_50_spindles_testing_for_postgresql/ Was actually with multiple bash scripts firing separate instances. The interesting thing here is the -s 1000m and -r8k. Those options are basically use a 1000 meg file (like our data files) with 8k chunks (like our pages). Based on your partitioning scheme, what is the break out? Can you reasonably expect all partitions to be used equally? Sincerely, Joshua D. Drake > > --Josh > > -- PostgreSQL - XMPP: jdr...@jabber.postgresql.org Consulting, Development, Support, Training 503-667-4564 - http://www.commandprompt.com/ The PostgreSQL Company, serving since 1997 -- 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] Shouldn't the planner have a higher cost for reverse index scans?
Tom, Now, while index scans (for indexes on disk) aren't 100% sequential reads, it seems like we should be increasing (substantially) the estimated cost of reverse index scans if the index is likely to be on disk. No? AFAICS this is already folded into random_page_cost. Not as far as I can tell. It looks to me like the planner is assuming that a forwards index scan and a reverse index scan will have the same cost. -- Josh Berkus PostgreSQL Experts Inc. 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] Using IOZone to simulate DB access patterns
On 4/9/09 11:26 PM, Mark Kirkwood wrote: Josh Berkus wrote: All, Wow, am I really the only person here who's used IOZone? No - I used to use it exclusively, but everyone else tended to demand I redo stuff with bonnie before taking any finding seriously... so I've kinda 'submitted to the Borg' as it were Bonnie++ has its own issues with concurrency; it's using some kind of ad-hoc threading implementation, which results in not getting real parallelism. I just did a test with -c 8 on Bonnie++ 1.95, and the program only ever used 3 cores. -- Josh Berkus PostgreSQL Experts Inc. 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] Using IOZone to simulate DB access patterns
I've switched to using FIO. Bonnie in my experience produces poor results and is better suited to testing desktop/workstation type load. Most of its tests don't apply to how postgres writes/reads anyway. IOZone is a bit more troublesome to get it to work on the file(s) you want under concurrency and is also hard to get it to avoid the OS file cache. On systems with lots of RAM, it takes too long as a result. I personally like it better than bonnnie by far, but its not flexible enough for me and is often used by hardware providers to 'show' theier RAID cards are doing fine (PERC 6 doing 4GB /sec file access -- see! Its fine!) but the thing is just testing in memory cached reads for most of the test or all if not configured right... FIO with profiles such as the below samples are easy to set up, and they can be mix/matched to test what happens with mixed read/write seq/rand -- with surprising and useful tuning results. Forcing a cache flush or sync before or after a run is trivial. Changing to asynchronous I/O, direct I/O, or other forms is trivial. The output result formatting is very useful as well. I got into using FIO when I needed to test a matrix of about 400 different tuning combinations. This would have taken a month with Iozone, but I could create my profiles with FIO, force the OS cache to flush, and constrain the time appropriately for each test, and run the batch overnight. # [read-rand] rw=randread ; this will be total of all individual files per process size=1g directory=/data/test fadvise_hint=0 blocksize=8k direct=0 ioengine=sync iodepth=1 numjobs=32 ; this is number of files total per process nrfiles=1 group_reporting=1 runtime=1m exec_prerun=echo 3 > /proc/sys/vm/drop_caches # [read] rw=read ; this will be total of all individual files per process size=512m directory=/data/test fadvise_hint=0 blocksize=8k direct=0 ioengine=sync iodepth=1 numjobs=8 ; this is number of files total per process nrfiles=1 runtime=30s group_reporting=1 exec_prerun=echo 3 > /proc/sys/vm/drop_caches #-- [write] rw=write ; this will be total of all individual files per process size=4g directory=/data/test fadvise_hint=0 blocksize=8k direct=0 ioengine=sync iodepth=1 numjobs=1 ;rate=1 ; this is number of files total per process nrfiles=1 runtime=48s group_reporting=1 end_fsync=1 exec_prerun=echo 3 >sync; /proc/sys/vm/drop_caches On 4/9/09 10:41 PM, "Josh Berkus" wrote: > All, > > Wow, am I really the only person here who's used IOZone? > > -- > Josh Berkus > PostgreSQL Experts Inc. > 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 > -- 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] Using IOZone to simulate DB access patterns
JD, In order to test real interactivity (AFAIK) with iozone you have to launch multiple iozone instances. You also need to do them from separate directories, otherwise it all starts writing the same file. The work I did here: Actually, current IOZone allows you to specify multiple files. For example, the command line I was using: iozone -R -i 0 -i 1 -i 2 -i 3 -i 4 -i 5 -i 8 -l 6 -u 6 -r 8k -s 4G -F f1 f2 f3 f4 f5 f6 And it does indeed launch 6 processes under that configuration. However, I found that for pretty much all of the write tests except for the first the processes blocked each other: F S UID PID PPID C PRI NI ADDR SZ WCHAN TTY TIME CMD 0 S26 6061 5825 0 80 0 - 11714 wait pts/300:00:00 iozone 1 D26 6238 6061 0 78 0 - 11714 sync_p pts/300:00:03 iozone 1 D26 6239 6061 0 78 0 - 11714 sync_p pts/300:00:03 iozone 1 D26 6240 6061 0 78 0 - 11714 sync_p pts/300:00:03 iozone 1 D26 6241 6061 0 78 0 - 11714 sync_p pts/300:00:03 iozone 1 D26 6242 6061 0 78 0 - 11714 stext pts/300:00:03 iozone 1 R26 6243 6061 0 78 0 - 11714 - pts/300:00:03 iozone Don Capps says that the IOZone code is perfect, and that pattern indicates a problem with my system, which is possible. Can someone else try concurrent IOZone on their system and see if they get the same pattern? I just don't have that many multi-core machines to test on. Also, WTF is the difference between "Children See" and "Parent Sees"? IOZone doesn't document this anywhere. -- Josh Berkus PostgreSQL Experts Inc. 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] Shouldn't the planner have a higher cost for reverse index scans?
Josh Berkus writes: > Not as far as I can tell. It looks to me like the planner is assuming > that a forwards index scan and a reverse index scan will have the same > cost. Right, because they do. If you think otherwise, demonstrate it. (bonnie tests approximating a reverse seqscan are not relevant to the performance of indexscans.) regards, tom lane -- 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] Using IOZone to simulate DB access patterns
Scott, FIO with profiles such as the below samples are easy to set up, and they can be mix/matched to test what happens with mixed read/write seq/rand -- with surprising and useful tuning results. Forcing a cache flush or sync before or after a run is trivial. Changing to asynchronous I/O, direct I/O, or other forms is trivial. The output result formatting is very useful as well. FIO? Link? -- Josh Berkus PostgreSQL Experts Inc. 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] Using IOZone to simulate DB access patterns
On 4/10/09 10:31 AM, "Josh Berkus" wrote: > Scott, > >> FIO with profiles such as the below samples are easy to set up, and they can >> be mix/matched to test what happens with mixed read/write seq/rand -- with >> surprising and useful tuning results. Forcing a cache flush or sync before >> or after a run is trivial. Changing to asynchronous I/O, direct I/O, or >> other forms is trivial. The output result formatting is very useful as >> well. > > FIO? Link? First google result: http://freshmeat.net/projects/fio/ Written by Jens Axobe, the Linux Kernel I/O block layer maintainer. He wrote the CFQ scheduler and Noop scheduler, and is the author of blktrace as well. " fio is an I/O tool meant to be used both for benchmark and stress/hardware verification. It has support for 13 different types of I/O engines (sync, mmap, libaio, posixaio, SG v3, splice, null, network, syslet, guasi, solarisaio, and more), I/O priorities (for newer Linux kernels), rate I/O, forked or threaded jobs, and much more. It can work on block devices as well as files. fio accepts job descriptions in a simple-to-understand text format. Several example job files are included. fio displays all sorts of I/O performance information. It supports Linux, FreeBSD, and OpenSolaris" > > > -- > Josh Berkus > PostgreSQL Experts Inc. > 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] Shouldn't the planner have a higher cost for reverse index scans?
Tom, Right, because they do. If you think otherwise, demonstrate it. (bonnie tests approximating a reverse seqscan are not relevant to the performance of indexscans.) Working on it. I *think* I've seen this issue in the field, which is why I brought it up in the first place, but getting a good test case is, of course, difficult. -- Josh Berkus PostgreSQL Experts Inc. 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] plpgsql arrays
Matthew Wakeling writes: > On Tue, 7 Apr 2009, Tom Lane wrote: >> Subsequent discussion showed that the problem was Matthew hadn't found >> that page. I guess that at least the DECLARE CURSOR reference page >> ought to have something like "if you are trying to use cursors in >> plpgsql, see ". Matthew, where *were* you looking exactly? > The DECLARE CURSOR page, and then guessing the INTO bit because that's how > SELECT works. I've added cross-references in the DECLARE and FETCH pages. I hope that's sufficient to catch the attention of anyone trying to use cursors ... regards, tom lane -- 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] Using IOZone to simulate DB access patterns
On Fri, 10 Apr 2009, Scott Carey wrote: FIO with profiles such as the below samples are easy to set up There are some more sample FIO profiles with results from various filesystems at http://wiki.postgresql.org/wiki/HP_ProLiant_DL380_G5_Tuning_Guide -- * Greg Smith gsm...@gregsmith.com http://www.gregsmith.com Baltimore, MD -- 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] Postgres 8.x on Windows Server in production
Ognjen, What are your experiences with Postgres 8.x in production use on Windows Server 2003/2008? Are there any limitations, trade-offs or quirks? First of all, you need to know that the first *two* digits of a PostgreSQL version are major version numbers. So 8.3 is not the same Postgres which 8.1 is. Here's the top level summary: PostgreSQL on Windows, compared to Linux, in general: -- is a bit slower -- is not as reliable, because the underlying FS and OS are not as reliable* -- some of the tools for Postgres which are available on Linux do not work on Windows (especially performance tools) -- is less secure, because the OS is less secure Yet 1000's of users are running PostgreSQL on Windows in production. It really depends on what kind of application you're running, and what its demands are. For a CMS or a contact manager or a personnel directory? No problem. For a central payroll system for 18,000 employees?I'd use Linux. -- Josh Berkus PostgreSQL Experts Inc. 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] Using IOZone to simulate DB access patterns
On 4/10/09 11:01 AM, "Greg Smith" wrote: > On Fri, 10 Apr 2009, Scott Carey wrote: > >> FIO with profiles such as the below samples are easy to set up > > There are some more sample FIO profiles with results from various > filesystems at > http://wiki.postgresql.org/wiki/HP_ProLiant_DL380_G5_Tuning_Guide I wish to thank Greg here as many of my profile variations came from the above as a starting point. Note in his results the XFS file system behavior on random writes is due to FIO doing 'sparse writes' (which Postgres does not do, and fio exposes some issues on xfs with) in the default random write mode. To properly simulate Postgres these should be random overwrites. Add 'overwrite=true' to the profile for random writes and the whole file will be allocated before randomly (over)writing to it. Here is the man page: http://linux.die.net/man/1/fio > > -- > * Greg Smith gsm...@gregsmith.com http://www.gregsmith.com Baltimore, MD > -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
[PERFORM] determining the locks that will be held by a query
I sent this out on 4/7 and either missed a response or didn't get one. If this is the wrong forum, I'd appreciate a redirect. I know that EXPLAIN will show the query plan. I know that pg_locks will show the locks currently held for activity transactions. Is there a way to determine what locks a query will hold when it is executed? Thanks, Brian -- 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] Using IOZone to simulate DB access patterns
On Fri, 10 Apr 2009, Scott Carey wrote: I wish to thank Greg here as many of my profile variations came from the above as a starting point. That page was mainly Mark Wong's work, I just remembered where it was. -- * Greg Smith gsm...@gregsmith.com http://www.gregsmith.com Baltimore, MD -- 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] determining the locks that will be held by a query
Brian Cox wrote: > I know that EXPLAIN will show the query plan. I know that pg_locks > will show the locks currently held for activity transactions. Is > there a way to determine what locks a query will hold when it is > executed? Only to read the docs regarding locking, and to desk-check your query, at least as far as I know. Keep in mind that some statements will only obtain locks if they find rows that are affected, which might vary from one run to the next. -Kevin -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
[PERFORM] 2.6.26 kernel and PostgreSQL
Hi chaps, Is anyone using 2.6.26 with postgres? I was thinking about shifting my home test machine up from 2.6.18, however I recall reading a post somewhere a while back about the scheduler in more recent versions being a bit cranky... I just thought I'd ask before I go ahead, I don't have too much time for testing etc at the moment. thanks Glyn -- 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] 2.6.26 kernel and PostgreSQL
Glyn Astill wrote: > I was thinking about shifting my home test machine up from 2.6.18, > however I recall reading a post somewhere a while back about the > scheduler in more recent versions being a bit cranky... A recent post on the topic: http://archives.postgresql.org/pgsql-performance/2009-04/msg00098.php -Kevin -- 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] Postgres 8.x on Windows Server in production
On Fri, Apr 10, 2009 at 7:07 PM, Josh Berkus wrote: > Yet 1000's of users are running PostgreSQL on Windows in production. It > really depends on what kind of application you're running, and what its > demands are. For a CMS or a contact manager or a personnel directory? No > problem. For a central payroll system for 18,000 employees? I'd use > Linux. Confirmed from my experience too. On top of that, I would like to add - that using it on windows first, might be a good step ahead. And installing linux on server isn't so hard anymore, and shouldn't be a problem, unlike 8 years ago :) Give it a try, and please tell us what sort of application you want to put on it. -- GJ -- 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] 2.6.26 kernel and PostgreSQL
--- On Fri, 10/4/09, Kevin Grittner wrote: > Glyn Astill wrote: > > I was thinking about shifting my home test machine up > from 2.6.18, > > however I recall reading a post somewhere a while back > about the > > scheduler in more recent versions being a bit > cranky... > > A recent post on the topic: > > http://archives.postgresql.org/pgsql-performance/2009-04/msg00098.php > > -Kevin > So it was only for connections over a unix socket, but wow; it's still an ongoing issue. Nice to see somebody is on top of it though. -- 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] Using IOZone to simulate DB access patterns
I've done quite a bit with IOzone, but if you're on Linux, you have lots of options. In particular, you can actually capture I/O patterns from a running application with blktrace, and then replay them with btrecord / btreplay. The documentation for this stuff is a bit hard to find. Some of the distros don't install it by default. But have a look at http://ow.ly/2zyW for some "Getting Started" info. -- M. Edward (Ed) Borasky http://www.linkedin.com/in/edborasky I've never met a happy clam. In fact, most of them were pretty steamed.
