X-Original-To: pgsql-performance@postgresql.org Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id AD3954758DC for ; Mon, 10 Feb 2003 12:36:15 -0500 (EST) Received: from davinci.ethosmedia.com (unknown [209.10.40.251]) by postgresql.org (Postfix) with ESMTP id 7747E4758BD for ; Mon, 10 Feb 2003 12:36:14 -0500 (EST) Received: from [63.195.55.98] (HELO spooky) by davinci.ethosmedia.com (CommuniGate Pro SMTP 4.0.2) with ESMTP id 2832316; Mon, 10 Feb 2003 09:36:14 -0800 Content-Type: text/plain; charset="iso-8859-1" From: Josh Berkus Organization: Aglio Database Solutions To: "philip johnson" , Subject: Re: how to configure my new server Date: Mon, 10 Feb 2003 09:35:14 -0800 User-Agent: KMail/1.4.3 References: In-Reply-To: MIME-Version: 1.0 Message-Id: <200302100935.14455.josh@agliodbs.com> Content-Transfer-Encoding: quoted-printable X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200302/48 X-Sequence-Number: 1104 Philip, > > someone could come back to first request ? > Insistent, aren't you? ;-) > > Yes no Raid, but will could change soon Adding RAID 1+0 could simplify your job enormously. It would prevent you= =20 from having to figure out what to put on each disk. If it were my machine,= =20 and I knew that the database was more important than the other services, I'= d=20 build it like this: Array 1: Disk 1: 18Go Ultra 3 (Ultra 160) SCSI 15 Ktpm Disk2 : 18Go Hot Plug Ultra 3 (Ultra 160) SCSI 15 Ktpm Contains: Linux, Apache, Swap Array 2:=20 Di:sk3 18Go Hot Plug Ultra 3 (Ultra 160) SCSI 15 Ktpm Disk4 18Go Hot Plug Ultra 3 (Ultra 160) SCSI 15 Ktpm Contains: PostgreSQL and databases Disk5 36Go Hot Plug Ultra 3 (Ultra 160) SCSI 15 Ktpm Contains: Postgresql log, backup partition. Alternately: Put all of the above on one *smart* RAID5 controller, with on-controller=20 memory and battery. Might give you better performance considering your di= sk=20 setup. > > how can I put indexes on a seperate disk ? Move the index object (use the oid2name package in /contrib to find the ind= ex)=20 to a different location, and symlink it back to its original location. Ma= ke=20 sure that you REINDEX at maintainence time, and don't drop and re-create th= e=20 index, as that will have the effect of moving it back to the original=20 location. > >>> linux values: > >>> kernel.shmmni =3D 4096 > >>> kernel.shmall =3D 32000000 > >>> kernel.shmmax =3D 256000000 > > I took a look a the performance archive, and it's not possible to find > > real info on how to set these 3 values. Yeah. Personally, I just raise them until I stop getting error messages fr= om=20 Postgres. Perhaps someone on the list could speak to the danger of settin= g=20 any of these values too high? > > I'm surprised that there's no spreadsheet to calculate those values. > > There are many threads, but it seems that no one is able to find a > > rule to define values. That's correct. There is no rule, because there are too many variables, an= d=20 the value of many of those variables is a matter of opinion. As an=20 *abbreviated* list: 1) Your processors and RAM; 2) Your drive setup and speed; 3) the frequenc= y=20 of data reads; 4) the frequency of data writes; 5) the average complexity= =20 of queries; 6) use of database procedures (functions) for DML; 7) your= =20 maintainence plan (e.g. how often can you run VACUUM FULL?); 8) the expect= ed=20 data population of tables (how many rows, how many tables); 9) your abilit= y=20 to program for indexed vs. non-indexed queries; 10) do you do mass data=20 loads? ; 11) is the server being used for any other hihg-memory/networked= =20 applications? ; 12) the expected number of concurrent users; 13) use of lar= ge=20 objects and/or large text fields; etc. As a result, a set of values that work really well for me might crash your= =20 database. It's an interactive process. Justin Clift started a project t= o=20 create an automated interactive postgresql.conf tuner, one that would=20 repeatedly test the speed of different queries against your database,=20 overnight while you sleep. However, he didn't get very far and I haven't= =20 had time to help. > >> 1. How many queries does my database handle per second or minute? > >> can't say now This has a big influence on your desired sort_mem and shared_buffer setting= s.=20=20=20 Make some estimates. > >> > >> 2. How big/complex are those queries? > > > > Not really complex and big as you can see OK, so nothing that would require you to really jack up your sort or shared= =20 memory beyond levels suggested by other factors. However, you don't say ho= w=20 many rows these queries usually return, which has a substantial effect on= =20 desired sort_mem. A good, if time-consuming, technique for setting sort_mem is to move it up = and=20 down (from, say 512 to 4096) seeing at what level your biggest meanest=20 queries slow down noticably ... and then set it to one level just above tha= t. > > There are more insert/update than read, because I'm doing table > > synchronization > > from an SQL Server database. Every 5 minutes I'm looking for change > > in SQL Server > > Database. > > I've made some stats, and I found that without user acces, and only > > with the replications > > I get 2 millions query per day In that case, making sure that your WAL files (the pg_xlog directory) is=20 located on a seperate drive which *does nothing else* during normal operati= on=20 is your paramount concern for performance. You'll also need to carefully= =20 prune your indexes down to only the ones you really need to avoid slowing= =20 your inserts and updates. > >> 4. What large tables in my database get queried > >> simultaneously/together? why this questions ? If you're not using RAID, it would affect whether you should even consider= =20 moving a particular table or index to a seperate drive. If you have two= =20 tables, each of which is 3 million records, and they are quried joined=20 together in 50% of data reads, then one of those tables is a good candidate= =20 for moving to another drive. Good luck! --=20 Josh Berkus Aglio Database Solutions San Francisco