Received: from localhost (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id 65E9364FD46 for ; Wed, 10 Dec 2008 04:20:33 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 76614-05 for ; Wed, 10 Dec 2008 04:20:17 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from EXHUB018-3.exch018.msoutlookonline.net (exhub018-3.exch018.msoutlookonline.net [64.78.17.18]) by mail.postgresql.org (Postfix) with ESMTP id EA77364FD3D for ; Wed, 10 Dec 2008 04:20:16 -0400 (AST) Received: from EXVMBX018-1.exch018.msoutlookonline.net ([64.78.17.47]) by EXHUB018-3.exch018.msoutlookonline.net ([64.78.17.18]) with mapi; Wed, 10 Dec 2008 00:20:15 -0800 From: Scott Carey To: Jean-David Beyer , "pgsql-performance@postgresql.org" Date: Wed, 10 Dec 2008 00:20:12 -0800 Subject: Re: Need help with 8.4 Performance Testing Thread-Topic: [PERFORM] Need help with 8.4 Performance Testing Thread-Index: AclZ/3/tvA6ymVqUSg6rlWNzbGAmZAAE0j45ACNVjTI= Message-ID: In-Reply-To: Accept-Language: en-US Content-Language: en X-MS-Has-Attach: X-MS-TNEF-Correlator: acceptlanguage: en-US Content-Type: multipart/alternative; boundary="_000_C564BBBCFD2scottrichrelevancecom_" MIME-Version: 1.0 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.001 tagged_above=0 required=5 tests=HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 200812/111 X-Sequence-Number: 32147 --_000_C564BBBCFD2scottrichrelevancecom_ Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable I did some further tests, that alter some of my statements below. Mainly: * I can do select count(1) queries at closer to disk speeds than my older t= ests (on a different machine) indicated. I can get ~800MB/sec where the di= sks can do 1200MB/sec and other single process tasks can go 1100MB/sec. It= is mostly CPU bound during these tests. Accessing from within shared_buffers rather than from the OS cache is faste= r 20% ish, but it is hard to measure consistently (hard for me to be certai= n its in that cache). Slightly more complicated scan queries (simple group by aggregates, returni= ng < 1000 rows) slow down to ~1/3 disk speed, depending on various factors = that are not worth getting into. So, postgres is a less CPU bound currentl= y than I thought. * The linux caching 100% system CPU spin issue seems related to something p= inning memory on my machine. Not sure what it is yet, the only thing runni= ng on the machine is postgres, and the size of the pinned memory is roughly= equal to shared_buffers. * I have some tests that conflict with prior results, that seem to depend o= n whether postgres is on or off when I run the disk benchmark. This may be= related to the pinned memory above. Specifically, larger block size reads= reduce overall CPU usage more if there isn't anything else running on the = system than if there is - attributable to more time spent in kswapd when po= stgres is on (and mostly idle). Further investigation is needed here. On 12/9/08 8:37 AM, "Scott Carey" wrote: > ________________________________________ > From: pgsql-performance-owner@postgresql.org [pgsql-performance-owner@pos= tgresql.org] On Behalf Of > Jean-David Beyer [jeandavid8@verizon.net] > Sent: Tuesday, December 09, 2008 5:08 AM > To: pgsql-performance@postgresql.org > Subject: Re: [PERFORM] Need help with 8.4 Performance Testing > -----BEGIN PGP SIGNED MESSAGE----- > Hash: SHA1 > But one thing that can matter is whether you want to improve just the > performance of the dbms, or the performance of the entire system, on whic= h > the dbms runs. Because if you want to improve the whole system, you would > want as much of the caching to take place in the system's buffers so the = use > of the memory could be optimized over the entire workload, not just the l= oad > of the dbms itself. I suppose on a dedicated system with only one dbms > running with only one database open (at a time, anyway), this might be mo= ot, > but not otherwise. Yes, the OS is in better position to arbitrate between multiple things. Of= course, we aren't talking about the highest performance databases if we ar= e talking about mixed use systems though. Additionally, the OS can never really get it right, with a DB or other apps= . Any app can behave badly and grab too much RAM and access it regularly e= nough for it to not be 'idle' much but give the OS VM fits trying to figure= out if its important or not versus other processes. > Now I agree that it would be good to get the entire index (or at least th= e > working set of the index) into the memory of the computer. But does it > really matter if it is in the system's cache, or the postgres cache? Is i= t > any more likely to be in postgres's cache than in the system cache if the > system is hurting for memory? I would think the system would be equally > likely to page out "idle" pages no matter where they are unless they are > locked to memory, and I do not know if all operating systems can do this, > and even if they can, I do not know if postgres uses that ability. I doub= t > it, since I believe (at least in Linux) a process can do that only if run= as > root, which I imagine few (if any) users do. The problem, is when none of them are really 'idle'. When the OS has to de= cide which pages, all of which have been accessed recently, to evict. Most= OS's will make bad choices if the load is mixed random and sequential acce= ss, as they treat all pages equally with respect to freshness versus evicti= on. Another problem is that there IS a difference between being in postgres' ca= che and the OS cache. One is more expensive to retrieve than the other. S= ignificantly. Aaccessing buffers in shared_buffers, in process, uses a good chunk less CP= U (and data copy and shared buffer eviction overhead) than going over the s= ys call to the OS. And as far as I can tell, even after the 8.4 fadvise patch, all I/O is in b= lock_size chunks. (hopefully I am wrong) My system is now CPU bound, the I/O can do sequential reads of more than 1.= 2GB/sec but Postgres can't do a seqscan 30% as fast because it eats up CPU = like crazy just reading and identifying tuples. It does seqscans ~ 25% fas= ter if its from shared_buffers than from the OS's page cache though. Seqs= cans are between 250MB/sec and 400MB/sec peak, from mem or disk, typically = showing no more than 35% iostat utilization of the array if off disk -- so = we run a few concurrently where we can. In addition to the fadvise patch, postgres needs to merge adjacent I/O's in= to larger ones to reduce the overhead. It only really needs to merge up to= sizes of about 128k or 256k, and gain a 8x to 16x drop in syscall overhead= , and additionally potentially save code trips down the shared buffer manag= ement code paths. At lest, thats my guess I haven't looked at any code and= could be wrong. Additionally, the "If your operating system has any reasonable caching itse= lf" comment earlier in this conversation --- Linux (2.6.18, Centos 5.2) do= es NOT. I can easily make it spend 100% CPU in system time trying to figur= e out what to do with the system cache for an hour. Just do large seqscans= with memory pressure from work_mem or other forces that the OS will not de= em 'idle'. Once the requested memory is ~75% of the system total, it will = freak out. Linux simply will not give up that last 25% or so of the RAM fo= r anything but page cache, even though the disk subsustem is very fast and = most of the access is sequential, marginalizing the benefit of the cache. = Depending on how you tune it, it will either spin system cpu or swap storm,= but the system cpu spin times for the same work load are a lot shorter tha= n an equivalent swap storm. Mount the data drive in O_DIRECT and the problem vanishes. I've been told = that this problem may be gone in some of the latest kernels. I have seriou= sly considered bumping shared_buffers up a lot and mounting the thing direc= t -- but then we lose the useful scheduler and readahead algorithms. The o= ther way around (small shared_buffers, let the OS do it) hurts performance = overall quite a bit -- randomly accessed pages get pushed out to the OS cac= he more often, and the OS tosses thouse out when a big seqscan occurs, resu= lting in a lot more random access from disk and more disk bound periods of = time. Great wonder, this operating system caching, eh? In any event, don't hold up these OS page cache things as if they're the be= st thing in the world for a database, they have serious flaws themselves an= d typically are difficult or impossible to tune to be ideal for a database. Its one thing to propose that a database build its own file system (hard, a= nd why bother?) versus have a database manage its own page cache intelligen= tly and access the OS file system as optimally as it can. In both of the l= atter, the DB knows much more about what data is really important than the = OS (and could for example, prioritize cache versus work_mem intelligently w= hile the OS can get that one horribly wrong in my experience, and knows whe= n a huge seqscan occurs to make caching those results low priority). No ma= tter how you do it using the OS cache, you cache twice and copy twice. O_D= IRECT isn't usually an option for other reasons, the OS disk scheduler, rea= dahead, and other benefits of a file system are real and substantial. If y= ou are caching twice, you might as well have the "closer" copy of that data= be the larger, more efficient pool. As for tipping points and pg_bench -- It doesn't seem to reflect the kind o= f workload we use postgres for at all, though my workload does a lot of big= hashes and seqscans, and I'm curious how much improved those may be due to= the hash improvements. 32GB RAM and 3TB data (about 250GB scanned regular= ly) here. And yes, we are almost completely CPU bound now except for a few= tasks. Iostat only reports above 65% disk utilization for about 5% of the= workload duty-cycle, and is regularly < 20%. COPY doesn't get anywhere ne= ar platter speeds, on indexless bulk transfer. The highest disk usage spik= es occur when some of our radom-access data/indexes get shoved out of cache= . These aren't too large, but high enough seqscan load will cause postgres= and the OS to dump them from cache. If we put these on some SSD's the dis= k utilization % would drop a lot further. I feel confident in saying that in about a year, I could spec out a medium = sized budget for hardware ($25k) for almost any postgres setup and make it = almost pure CPU bound. SSDs and hybrid tech such as ZFS L2ARC make this possible with easy access = to 10k+ iops, and it it will take no more than 12 SATA drives in raid 10 ne= xt year (and a good controller or software raid) to get 1GB/sec sequential = reads. --_000_C564BBBCFD2scottrichrelevancecom_ Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Re: [PERFORM] Need help with 8.4 Performance Testing I did some further tests, that alter some of my statements below. &nb= sp;

Mainly:

* I can do select count(1) queries at closer to disk speeds than my older t= ests (on a different machine) indicated.  I can get ~800MB/sec where t= he disks can do 1200MB/sec and other single process tasks can go 1100MB/sec= .  It is mostly CPU bound during these tests.
Accessing from within shared_buffers rather than from the OS cache is faste= r 20% ish, but it is hard to measure consistently (hard for me to be certai= n its in that cache).
Slightly more complicated scan queries (simple group by aggregates, returni= ng < 1000 rows) slow down to ~1/3 disk speed, depending on various facto= rs that are not worth getting into.  So, postgres is a less CPU bound = currently than I thought.
* The linux caching 100% system CPU spin issue seems related to something p= inning memory on my machine.  Not sure what it is yet, the only thing = running on the machine is postgres, and the size of the pinned memory is ro= ughly equal to shared_buffers.
* I have some tests that conflict with prior results, that seem to depend o= n whether postgres is on or off when I run the disk benchmark.  This m= ay be related to the pinned memory above.  Specifically, larger block = size reads reduce overall CPU usage more if there isn’t anything else= running on the system than if there is — attributable to more time s= pent in kswapd when postgres is on (and mostly idle).  Further investi= gation is needed here.

On 12/9/08 8:37 AM, "Scott Carey" <scott@richrelevance.com> wrote:

> ______________________________________= __
> From: pgsql-perform= ance-owner@postgresql.org [pgsql-performance-owner@postgresql.org] On Behalf Of > Jean-D= avid Beyer [jeandavid8@verizon.net]<= BR> > Sent: Tuesday, December 09, 2008 5:08 AM
> To: pgsql-performance@pos= tgresql.org
> Subject: Re: [PERFORM] Need help with 8.4 Performance Testing
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1

> But one thing that can matter is whether you want to improve just the<= BR> > performance of the dbms, or the performance of the entire system, on w= hich
> the dbms runs. Because if you want to improve the whole system, you wo= uld
> want as much of the caching to take place in the system's buffers so t= he use
> of the memory could be optimized over the entire workload, not just th= e load
> of the dbms itself. I suppose on a dedicated system with only one dbms=
> running with only one database open (at a time, anyway), this might be= moot,
> but not otherwise.

Yes, the OS is in better position to arbitrate between multiple things. &nb= sp;Of course, we aren't talking about the highest performance databases if = we are talking about mixed use systems though.
Additionally, the OS can never really get it right, with a DB or other apps= .  Any app can behave badly and grab too much RAM and access it regula= rly enough for it to not be 'idle' much but give the OS VM fits trying to f= igure out if its important or not versus other processes.

> Now I agree that it would be good to get the entire index (or at least= the
> working set of the index) into the memory of the computer. But does it=
> really matter if it is in the system's cache, or the postgres cache? I= s it
> any more likely to be in postgres's cache than in the system cache if = the
> system is hurting for memory? I would think the system would be equall= y
> likely to page out "idle" pages no matter where they are unl= ess they are
> locked to memory, and I do not know if all operating systems can do th= is,
> and even if they can, I do not know if postgres uses that ability. I d= oubt
> it, since I believe (at least in Linux) a process can do that only if = run as
> root, which I imagine few (if any) users do.

The problem, is when none of them are really 'idle'.  When the OS has = to decide which pages, all of which have been accessed recently, to evict. =  Most OS's will make bad choices if the load is mixed random and seque= ntial access, as they treat all pages equally with respect to freshness ver= sus eviction.
Another problem is that there IS a difference between being in postgres' ca= che and the OS cache.  One is more expensive to retrieve than the othe= r.  Significantly.

Aaccessing buffers in shared_buffers, in process, uses a good chunk less CP= U (and data copy and shared buffer eviction overhead) than going over the s= ys call to the OS.

And as far as I can tell, even after the 8.4 fadvise patch, all I/O is in b= lock_size chunks.  (hopefully I am wrong)
My system is now CPU bound, the I/O can do sequential reads of more than 1.= 2GB/sec but Postgres can't do a seqscan 30% as fast because it eats up CPU = like crazy just reading and identifying tuples.  It does seqscans ~ 25= % faster if its from shared_buffers than from the OS's page cache though. &= nbsp; Seqscans are between 250MB/sec and 400MB/sec peak, from mem or d= isk, typically showing no more than 35% iostat utilization of the array if = off disk -- so we run a few concurrently where we can.

In addition to the fadvise patch, postgres needs to merge adjacent I/O's in= to larger ones to reduce the overhead.  It only really needs to merge = up to sizes of about 128k or 256k, and gain a 8x to 16x drop in syscall ove= rhead, and additionally potentially save code trips down the shared buffer = management code paths.  At lest, thats my guess I haven't looked at an= y code and could be wrong.


Additionally, the "If your operating system has any reasonable caching= itself" comment earlier in this conversation ---  Linux (2.6.18,= Centos 5.2) does NOT.  I can easily make it spend 100% CPU in system = time trying to figure out what to do with the system cache for an hour. &nb= sp;Just do large seqscans with memory pressure from work_mem or other force= s that the OS will not deem 'idle'.  Once the requested memory is ~75%= of the system total, it will freak out.  Linux simply will not give u= p that last 25% or so of the RAM for anything but page cache, even though t= he disk subsustem is very fast and most of the access is sequential, margin= alizing the benefit of the cache.  Depending on how you tune it, it wi= ll either spin system cpu or swap storm, but the system cpu spin times for = the same work load are a lot shorter than an equivalent swap storm.
Mount the data drive in O_DIRECT and the problem vanishes.  I've been = told that this problem may be gone in some of the latest kernels.  I h= ave seriously considered bumping shared_buffers up a lot and mounting the t= hing direct -- but then we lose the useful scheduler and readahead algorith= ms.  The other way around (small shared_buffers, let the OS do it) hur= ts performance overall quite a bit -- randomly accessed pages get pushed ou= t to the OS cache more often, and the OS tosses thouse out when a big seqsc= an occurs, resulting in a lot more random access from disk and more disk bo= und periods of time. Great wonder, this operating system caching, eh?

In any event, don't hold up these OS page cache things as if they're the be= st thing in the world for a database, they have serious flaws themselves an= d typically are difficult or impossible to tune to be ideal for a database.=

Its one thing to propose that a database build its own file system (hard, a= nd why bother?) versus have a database manage its own page cache intelligen= tly and access the OS file system as optimally as it can.  In both of = the latter, the DB knows much more about what data is really important than= the OS (and could for example, prioritize cache versus work_mem intelligen= tly while the OS can get that one horribly wrong in my experience, and know= s when a huge seqscan occurs to make caching those results low priority). &= nbsp;No matter how you do it using the OS cache, you cache twice and copy t= wice.  O_DIRECT isn't usually an option for other reasons, the OS disk= scheduler, readahead, and other benefits of a file system are real and sub= stantial.  If you are caching twice, you might as well have the "= closer" copy of that data be the larger, more efficient pool.

As for tipping points and pg_bench -- It doesn't seem to reflect the kind o= f workload we use postgres for at all, though my workload does a lot of big= hashes and seqscans, and I'm curious how much improved those may be due to= the hash improvements.  32GB RAM and 3TB data (about 250GB scanned re= gularly) here.  And yes, we are almost completely CPU bound now except= for a few tasks.  Iostat only reports above 65% disk utilization for = about 5% of the workload duty-cycle, and is regularly < 20%.  COPY = doesn't get anywhere near platter speeds, on indexless bulk transfer.  = ;The highest disk usage spikes occur when some of our radom-access data/ind= exes get shoved out of cache.  These aren't too large, but high enough= seqscan load will cause postgres and the OS to dump them from cache.  = ;If we put these on some SSD's the disk utilization % would drop a lot furt= her.

I feel confident in saying that in about a year, I could spec out a medium = sized budget for hardware ($25k) for almost any postgres setup and make it = almost pure CPU bound.
SSDs and hybrid tech such as ZFS L2ARC make this possible with easy access = to 10k+ iops, and it it will take no more than 12 SATA drives in raid 10 ne= xt year (and a good controller or software raid) to get 1GB/sec sequential = reads.

--_000_C564BBBCFD2scottrichrelevancecom_--