X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id 2A8DC9DC817 for ; Thu, 26 Jan 2006 04:57:36 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 21645-09 for ; Thu, 26 Jan 2006 04:57:36 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey- Received: from fs3.ft.fo (mail-gw.ft.fo [212.55.40.2]) by postgresql.org (Postfix) with ESMTP id F20189DC844 for ; Thu, 26 Jan 2006 04:57:31 -0400 (AST) Received: from FT_DOM-MTA by fs3.ft.fo with Novell_GroupWise; Thu, 26 Jan 2006 08:57:32 +0000 Message-Id: X-Mailer: Novell GroupWise Internet Agent 6.5.3 Date: Thu, 26 Jan 2006 08:57:08 +0000 From: "John Jensen" To: , Subject: Re: Where is my bottleneck? Mime-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.12 required=5 tests=[AWL=0.120] X-Spam-Score: 0.12 X-Spam-Level: X-Archive-Number: 200601/294 X-Sequence-Number: 20382 Hi Arnau, >> - Is all the memory used by postgres ? > I'm not sure how to look at that (how could I do it?).=20 In TOP you can see how much memory is used by postmaster process'es. > SD22-SINER5:~# top > top - 15:09:50 up 453 days, 11:47, 3 users, load average: 4.08, 3.90, = 2.64 > Tasks: 70 total, 3 running, 67 sleeping, 0 stopped, 0 zombie > Cpu(s): 6.3% user, 37.1% system, 0.0% nice, 56.6% idle > Mem: 3748956k total, 3623988k used, 124968k free, 82976k buffers > Swap: 2097136k total, 13896k used, 2083240k free, 3283128k cached > The parameters related with memory usage of postgresql.conf all are = the=20 > default values (I haven't changed any value in the postgresql.conf file) > # - Memory - > #shared_buffers =3D 1000 > #sort_mem =3D 1024 > #vacuum_mem =3D 8192 > # - Free Space Map - > #max_fsm_pages =3D 20000 > #max_fsm_relations =3D 1000 > # - Kernel Resource Usage - > #max_files_per_process =3D 1000 > #preload_libraries =3D '' Take the suggestions from Scott Marlowe's mail, bump up the shared buffer memory usage. Apart from that there is a lot of really great info on the Varlena page he refers to. Go read it. >> - Run vmstat 1 and post the first 50 lines of data procs -----------memory---------- ---swap-- -----io---- --system--=20 ----cpu---- r b swpd free buff cache si so bi bo in cs us=20 sy id wa 1 0 13900 118920 43584 3333540 0 0 1 0 0 0 6=20 37 57 0 1 4 13900 120592 42976 3330616 0 0 8096 5296 506 16905 55=20 20 24 0 <> NOW this is interesting. Your I/O is dominated by write operations. Do you make a lot of inserts ? What is the database used for ? What is the application in front of it ? >> - How large is your database (disk usage under postgres-x.x.x/data/base >> ) >SD22-SINER5:/var/lib/postgres# du --max-depth=3D1 -h >17G ./data >360K ./dumpall >17G . All this indicates a usage pattern where data is pumbed in at say 10Gigs = an hour and is deleted after roughly two hours. Only 1/3 to =BD of the data is = ever queried. Is this correct ? If this is the case then you may have to throw hardware at the problem. Raid 10 instead of raid 5, split the base over multiple raid devices and = multiple controllers. > My settings are, do I should change anything else? > # - Query/Index Statistics Collector - > stats_start_collector =3D true > stats_command_string =3D true > #stats_block_level =3D false > stats_row_level =3D true > #stats_reset_on_server_start =3D true I run with: stats_command_string =3D on stats_block_level =3D on stats_row_level =3D on stats_reset_on_server_start =3D on That definatel gives you what you need. /John