X-Original-To: pgsql-performance@postgresql.org Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 0E8BB475B8D for ; Fri, 14 Feb 2003 12:36:57 -0500 (EST) Received: from davinci.ethosmedia.com (unknown [209.10.40.251]) by postgresql.org (Postfix) with ESMTP id 43A1B475D12 for ; Fri, 14 Feb 2003 12:36:26 -0500 (EST) Received: from [63.195.55.98] (HELO spooky) by davinci.ethosmedia.com (CommuniGate Pro SMTP 4.0.2) with ESMTP id 2840189; Fri, 14 Feb 2003 09:36:38 -0800 Content-Type: text/plain; charset="iso-8859-1" From: Josh Berkus Organization: Aglio Database Solutions To: Kevin Brown , pgsql-performance@postgresql.org Subject: Re: Tuning scenarios (was Changing the default configuration) Date: Fri, 14 Feb 2003 09:36:02 -0800 User-Agent: KMail/1.4.3 References: <303E00EBDD07B943924382E153890E5434A909@cuthbert.rcsinc.local> <4590.1045202674@sss.pgh.pa.us> <20030214114843.GB1847@filer> In-Reply-To: <20030214114843.GB1847@filer> MIME-Version: 1.0 Message-Id: <200302140936.02011.josh@agliodbs.com> Content-Transfer-Encoding: quoted-printable X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200302/85 X-Sequence-Number: 1141 Kevin, > I think we might be able to come up with some reasonable answers to > that, as long as we don't expect too much out of the resulting > benchmark. The right people to ask are probably the people who are > actually running production databases. > > Anyone wanna chime in here with some opinions and perspectives? I thought you'd *never* ask. (for background: I'm a consultant, and I administrate 6 postgresql database= s=20 for 5 different clients) First off, one can't do a useful performance test on the sort of random dat= a=20 which can be generated by a script. The only really useful tests come fro= m=20 testing on a copy of the user's own database(s), or on a real database of= =20 some sort.=20=20 For new installations, we'd need to make a copy of a public domain or OSS= =20 database as a part of our performance testing tool. This database would ne= ed=20 at least 10 tables, some of them quite large, with FK relationships. Second, there are five kinds of query tests relevant to performmance: A) Rapid-fire simple select queries. B) Large complex select queries, combining at least 2 of: aggregates,=20 sub-selects, unions, unindexed text searches, and outer joins. C) Rapid-fire small (<10 rows) update/insert/delete queries. D) Large update queries (> 10,000 rows, possibly in more than one table) E) Long-running PL procedures. Testing on these five types of operations give an all-around test of server= =20 performance. Fortunately, for many installations, not all tests are=20 relevant; in fact, for many, only 2 of the 5 above are relevant. For=20 example, for a PHP-Nuke installation, you'd only need to test on A and C. = As=20 another example, an OLAP reporting server would only need to test on B. Unfortunately, for any real production server, you need to test all the=20 different operations concurrently at the appropriate multi-user level.=20= =20 Meaning that for one of my servers (a company-wide calendaring tool) I'd ne= ed=20 to run tests on A, B, C, and E all simultaneously ... for that matter, A an= d=20 C by themselves would require multiple connections. So, once again, if we're talking about a testing database, we would need=20 twenty examples of A and C, ten of each of B and D, and at least 3 of E tha= t=20 we could run. For real production databases, the user could supply "pools"= =20 of the 5 types of operations from their real query base. Thirdly, we're up against the problem that there are several factors which = can=20 have a much more profound effect on database performance than *any* amount = of=20 tuning postgresql.conf, even given a particular hardware platform. In my= =20 experience, these factors include (in no particular order):=20 1) Location of the pg_xlog for heavy-update databases. 2) Location of other files on multi-disk systems 3) Choice of RAID and controller for RAID systems. 4) Filesystem choice and parameters 5) VACUUM/FULL/ANALYZE/REINDEX frequency and strategy 6) Database schema design 7) Indexing Thus the user would have to be somehow informed that they need to examine a= ll=20 of the above, possibly before running the tuning utility. Therefore, any tuning utility would have to: 1) Inform the user about the other factors affecting performance and notify= =20 them that they have to deal with these. 2) Ask the user for all of the following data: a) How much RAM does your system have? b) How many concurrent users, typically? c) How often do you run VACUUM/FULL/ANALYZE? d) Which of the Five Basic Operations does your database perform frequentl= y? (this question could be reduced to "what kind of database do you have= ?" web site database =3D A and C reporting database =3D A and B transaction processing =3D A, C, D and possibly E etc.) e) For each of the 5 operations, how many times per minute is it run? f) Do you care about crash recovery? (if not, we can turn off fsync) g) (for users testing on their own database) Please make a copy of your=20 database, and provide 5 pools of operation examples. 3) The the script would need to draw random operations from the pool, with= =20 operation type randomly drawn weighted by relative frequency for that type = of=20 operation. Each operation would be timed and scores kept per type of=20 operation. 4) Based on the above scores, the tuning tool could adjust the following=20 parameters: For A) shared_buffers For B) shared_buffers and sort_mem (and Tom's new JOIN COLLAPSE settings) For C) and D) wal settings and FSM settings For E) shared_buffers, wal, and FSM 5) Then run 3) again. The problem is that the above process becomes insurmountably complex when w= e=20 are testing for several types of operations simultaneously. For example, i= f=20 operation D is slow, we might dramatically increase FSM, but that could tak= e=20 away memory needed for op. B, making op. B run slower. So if we're runnin= g=20 concurrently, we could could find the adjustments made for each type of=20 operation contradictory, and the script would be more likely to end up in a= n=20 endless loop than at a balance. If we don't run the different types of=20 operations simultaneously, then it's not a good test; the optimal settings= =20 for op. B, for example, may make ops. A and C slow down and vice-versa. So we'd actually need to run an optimization for each type of desired=20 operation seperately, and then compare settings, adjust to a balance=20 (weighted according to the expected relative frequency), and re-test=20 concurrently. Aieee! Personally, I think this is a project in and of itself. GBorg, anyone? --=20 Josh Berkus Aglio Database Solutions San Francisco