X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id 8B1EF9DC87F for ; Mon, 16 Jan 2006 18:07:53 -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 48705-06 for ; Mon, 16 Jan 2006 18:07:52 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from uproxy.gmail.com (uproxy.gmail.com [66.249.92.201]) by postgresql.org (Postfix) with ESMTP id 84D709DC87C for ; Mon, 16 Jan 2006 18:07:48 -0400 (AST) Received: by uproxy.gmail.com with SMTP id s2so830228uge for ; Mon, 16 Jan 2006 14:07:50 -0800 (PST) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:user-agent:x-accept-language:mime-version:to:subject:content-type:content-transfer-encoding; b=NbRrMRw/AU2RHG+IZyVHUn/XEVy6PSWAiDSea93ahzbxwI0SvQYl2z7Tadi8IbnEOpTaGFfzVm8XgnP/zkM6vgmTy3aEnaPe4a6pQANGu5Wk2nK89NJF9YJ9ct9/b/MY2QEfr3iRuwiDwMbPd90uLWbCGddyYUWCO0AiP9j+jkc= Received: by 10.66.254.16 with SMTP id b16mr1800254ugi; Mon, 16 Jan 2006 14:07:50 -0800 (PST) Received: from ?192.168.1.10? ( [84.98.10.34]) by mx.gmail.com with ESMTP id o1sm910245uge.2006.01.16.14.07.49; Mon, 16 Jan 2006 14:07:49 -0800 (PST) Message-ID: <43CC1938.7020807@gmail.com> Date: Mon, 16 Jan 2006 23:07:52 +0100 From: Antoine User-Agent: Mozilla Thunderbird 1.0.7 (X11/20051022) X-Accept-Language: en-us, en MIME-Version: 1.0 To: pgsql-performance@postgresql.org Subject: new to postgres (and db management) and performance already a problem :-( Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=1.332 required=5 tests=[RCVD_IN_BL_SPAMCOP_NET=1.332] X-Spam-Score: 1.332 X-Spam-Level: * X-Archive-Number: 200601/209 X-Sequence-Number: 16687 Hi, We have a horribly designed postgres 8.1.0 database (not my fault!). I am pretty new to database design and management and have really no idea how to diagnose performance problems. The db has only 25-30 tables, and half of them are only there because our codebase needs them (long story, again not my fault!). Basically we have 10 tables that are being accessed, and only a couple of queries that join more than 3 tables. Most of the action takes place on two tables. One of the devs has done some truly atrocious coding and is using the db as his data access mechanism (instead of an in-memory array, and he only needs an array/collection). It is running on an p4 3000ish (desktop model) running early linux 2.6 (mdk 10.1) (512meg of ram) so that shouldn't be an issue, as we are talking only about 20000 inserts a day. It probably gets queried about 20000 times a day too (all vb6 via the pg odbc). So... seeing as I didn't really do any investigation as to setting default sizes for storage and the like - I am wondering whether our performance problems (a programme running 1.5x slower than two weeks ago) might not be coming from the db (or rather, my maintaining of it). I have turned on stats, so as to allow autovacuuming, but have no idea whether that could be related. Is it better to schedule a cron job to do it x times a day? I just left all the default values in postgres.conf... could I do some tweaking? Does anyone know of any practical resources that might guide me in sorting out these sorts of problems? Some stuff with pratical examples would be good so I could compare with what we have. Thanks Antoine ps. I had a look with top and it didn't look like it was going much over 15% cpu, with memory usage negligeable. There are usually about 10 open connections. I couldn't find an easy way to check for disk accessings. pps. The db is just one possible reason for our bottleneck so if you tell me it is very unlikely I will be most reassured!