X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 2A707D77CC for ; Wed, 24 Aug 2005 17:10:48 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 05573-01 for ; Wed, 24 Aug 2005 20:10:44 +0000 (GMT) Received: from ausimss.pervasive.com (ausimss.pervasive.com [66.45.103.246]) by svr1.postgresql.org (Postfix) with ESMTP id 7C761D76B7 for ; Wed, 24 Aug 2005 17:10:39 -0300 (ADT) Received: from ausbayes2.aus.pervasive.com ([172.16.8.8]) by ausimss.pervasive.com with InterScan Messaging Security Suite; Wed, 24 Aug 2005 15:10:38 -0500 Received: from ausbayes1.aus.pervasive.com ([172.16.8.6]) by ausbayes2.aus.pervasive.com with Microsoft SMTPSVC(5.0.2195.6713); Wed, 24 Aug 2005 15:10:38 -0500 Received: from ausmailowa.aus.pervasive.com ([172.16.4.8]) by ausbayes1.aus.pervasive.com with Microsoft SMTPSVC(5.0.2195.6713); Wed, 24 Aug 2005 15:10:38 -0500 Received: from ausmail2k2.aus.pervasive.com ([172.16.4.9]) by ausmailowa.aus.pervasive.com with Microsoft SMTPSVC(6.0.3790.1830); Wed, 24 Aug 2005 15:10:37 -0500 X-MimeOLE: Produced By Microsoft Exchange V6.0.6249.0 content-class: urn:content-classes:message MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Subject: Re: Read/Write block sizes Date: Wed, 24 Aug 2005 15:10:37 -0500 Message-ID: <072BDB2B234F3840B0AC03411084C9AF869911@ausmail2k2.aus.pervasive.com> X-MS-Has-Attach: X-MS-TNEF-Correlator: Thread-Topic: Read/Write block sizes Thread-Index: AcWo5+SGCG6LxAh9SkKyyvvry40Dug== From: "Lance Obermeyer" To: "Bruce Momjian" , "Jignesh K. Shah" Cc: "Jim Nasby" , "Chris Browne" , X-OriginalArrivalTime: 24 Aug 2005 20:10:37.0777 (UTC) FILETIME=[E4B2EC10:01C5A8E7] X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0 required=5 tests=[none] X-Spam-Level: X-Archive-Number: 200508/404 X-Sequence-Number: 14151 Since Bruce referred to the "corporate software world" I'll chime in... It has been a while since adding knobs and dials has been considered a = good idea. Customers are almost always bad at tuning their systems, = which decreases customer satisfaction. While many people assume the = corporate types don't care, that is actually far from the truth. Well = run commercial software companies regularly commission (expensive) = customer satisfaction surveys. These numbers are the second most = important numbers in all of the enterprise, trailing only revenue in = importance. Results are sliced and diced in every way imaginable. The commercial world is trying to auto-tune their systems just as much. = Examples are the work that many of the big boys are doing towards = "autonomic" computing. While it is driven by naked self interest of = wanting to sell version upgrades, those efforts increase customer = satisfaction and decrease support costs. Works well for everyone... -----Original Message----- From: Bruce Momjian [mailto:pgman@candle.pha.pa.us] Sent: Wednesday, August 24, 2005 8:52 AM To: Jignesh K. Shah Cc: Jim Nasby; Chris Browne; pgsql-performance@postgresql.org Subject: Re: Read/Write block sizes This thread covers several performance ideas. First is the idea that more parameters should be configurable. While this seems like a noble goal, we try to make parameters auto-tuning, or if users have to configure it, the parameter should be useful for a significant number of users. In the commercial software world, if you can convince your boss that a feature/knob is useful, it usually gets into the product.=20 Unfortunately, this leads to the golden doorknob on a shack, where some features are out of sync with the rest of the product in terms of usefulness and utility. With open source, if a feature can not be auto-tuned, or has significant overhead, the features has to be implemented and then proven to be a benefit. In terms of adding async I/O, threading, and other things, it might make sense to explore how these could be implemented in a way that fits the above criteria. -------------------------------------------------------------------------= -- Jignesh K. Shah wrote: > Hi Jim, >=20 > | How many of these things are currently easy to change with a = recompile? > | I should be able to start testing some of these ideas in the near > | future, if they only require minor code or configure changes. >=20 >=20 > The following > * Data File Size 1GB > * WAL File Size of 16MB > * Block Size of 8K >=20 > Are very easy to change with a recompile.. A Tunable will be greatly=20 > prefered as it will allow one binary for different tunings >=20 > * MultiBlock read/write >=20 > Is not available but will greatly help in reducing the number of = system=20 > calls which will only increase as the size of the database increases = if=20 > something is not done about i. >=20 > * Pregrown files... maybe not important at this point since TABLESPACE = > can currently work around it a bit (Just need to create a different = file=20 > system for each tablespace >=20 > But if you really think hardware & OS is the answer for all small=20 > things...... I think we should now start to look on how to make = Postgres=20 > Multi-threaded or multi-processed for each connection. With the influx = > of "Dual-Core" or "Multi-Core" being the fad.... Postgres can have = the=20 > cutting edge if somehow exploiting cores is designed. >=20 > Somebody mentioned that adding CPU to Postgres workload halved the=20 > average CPU usage... > YEAH... PostgreSQL uses only 1 CPU per connection (assuming 100%=20 > usage) so if you add another CPU it is idle anyway and the system = will=20 > report only 50% :-) BUT the importing to measure is.. whether the = query=20 > time was cut down or not? ( No flames I am sure you were talking about = > multi-connection multi-user environment :-) ) But my point is then = this=20 > approach is worth the ROI and the time and effort spent to solve this=20 > problem. >=20 > I actually vote for a multi-threaded solution for each connection = while=20 > still maintaining seperate process for each connections... This way = the=20 > fundamental architecture of Postgres doesn't change, however a=20 > multi-threaded connection can then start to exploit different cores..=20 > (Maybe have tunables for number of threads to read data files who=20 > knows.. If somebody is interested in actually working a design ..=20 > contact me and I will be interested in assisting this work. >=20 > Regards, > Jignesh >=20 >=20 > Jim C. Nasby wrote: >=20 > >On Tue, Aug 23, 2005 at 06:09:09PM -0400, Chris Browne wrote: > > =20 > > > >>J.K.Shah@Sun.COM (Jignesh Shah) writes: > >> =20 > >> > >>>>Does that include increasing the size of read/write blocks? I've > >>>>noticedthat with a large enough table it takes a while to do a > >>>>sequential scan, even if it's cached; I wonder if the fact that it > >>>>takes a million read(2) calls to get through an 8G table is part = of > >>>>that. > >>>> =20 > >>>> > >>>Actually some of that readaheads,etc the OS does already if it does > >>>some sort of throttling/clubbing of reads/writes. But its not = enough > >>>for such types of workloads. > >>> > >>>Here is what I think will help: > >>> > >>>* Support for different Blocksize TABLESPACE without recompiling = the > >>>code.. (Atlease support for a different Blocksize for the whole > >>>database without recompiling the code) > >>> > >>>* Support for bigger sizes of WAL files instead of 16MB files > >>>WITHOUT recompiling the code.. Should be a tuneable if you ask me > >>>(with checkpoint_segments at 256.. you have too many 16MB files in > >>>the log directory) (This will help OLTP benchmarks more since now > >>>they don't spend time rotating log files) > >>> > >>>* Introduce a multiblock or extent tunable variable where you can > >>>define a multiple of 8K (or BlockSize tuneable) to read a bigger > >>>chunk and store it in the bufferpool.. (Maybe writes too) (Most > >>>devices now support upto 1MB chunks for reads and writes) > >>> > >>>*There should be a way to preallocate files for TABLES in > >>>TABLESPACES otherwise with multiple table writes in the same > >>>filesystem ends with fragmented files which causes poor "READS" = from > >>>the files. > >>> > >>>* With 64bit 1GB file chunks is also moot.. Maybe it should be > >>>tuneable too like 100GB without recompiling the code. > >>> > >>>Why recompiling is bad? Most companies that will support Postgres > >>>will support their own binaries and they won't prefer different > >>>versions of binaries for different blocksizes, different WAL file > >>>sizes, etc... and hence more function using the same set of = binaries > >>>is more desirable in enterprise environments > >>> =20 > >>> > >>Every single one of these still begs the question of whether the > >>changes will have a *material* impact on performance. > >> =20 > >> >=20 >=20 > ---------------------------(end of = broadcast)--------------------------- > TIP 3: Have you checked our extensive FAQ? >=20 > http://www.postgresql.org/docs/faq >=20 --=20 Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania = 19073