From owner-freebsd-questions@FreeBSD.ORG Thu Dec 9 22:39:04 2010 Return-Path: Delivered-To: questions@freebsd.org Received: from mx1.freebsd.org (mx1.freebsd.org [IPv6:2001:4f8:fff6::34]) by hub.freebsd.org (Postfix) with ESMTP id 497861065674 for ; Thu, 9 Dec 2010 22:39:04 +0000 (UTC) (envelope-from gandalf@shopzeus.com) Received: from fep19.mx.upcmail.net (fep19.mx.upcmail.net [62.179.121.39]) by mx1.freebsd.org (Postfix) with ESMTP id B48E78FC15 for ; Thu, 9 Dec 2010 22:39:03 +0000 (UTC) Received: from edge04.upcmail.net ([192.168.13.239]) by viefep19-int.chello.at (InterMail vM.8.01.02.02 201-2260-120-106-20100312) with ESMTP id <20101209223902.MUSN1944.viefep19-int.chello.at@edge04.upcmail.net>; Thu, 9 Dec 2010 23:39:02 +0100 Received: from [192.168.0.101] ([89.132.77.158]) by edge04.upcmail.net with edge id hAf01f03D3QvkXM04Af1BH; Thu, 09 Dec 2010 23:39:02 +0100 X-SourceIP: 89.132.77.158 Message-ID: <4D015A82.5040605@shopzeus.com> Date: Thu, 09 Dec 2010 23:38:58 +0100 From: Laszlo Nagy User-Agent: Mozilla/5.0 (Windows; U; Windows NT 6.1; hu; rv:1.9.2.12) Gecko/20101027 Thunderbird/3.1.6 MIME-Version: 1.0 To: Bill Moran References: <4D00BDF8.6020206@shopzeus.com> <20101209083818.e622a146.wmoran@potentialtech.com> In-Reply-To: <20101209083818.e622a146.wmoran@potentialtech.com> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Cloudmark-Analysis: v=1.1 cv=Nww7yNiXF4C1XGF+VcigPkOcTpD8wJaI1KQuZlH5eEk= c=1 sm=0 a=Xb4xUz3OdD0A:10 a=8nJEP1OIZ-IA:10 a=2frQhE18BXCGe4mAu24A:9 a=RySAvreLgEhyWe5TQmUA:7 a=NHVwDOYaibr1vqmw-RDooQ_mBVwA:4 a=wPNLvfGTeEIA:10 a=HpAAvcLHHh0Zw7uRqdWCyQ==:117 Cc: questions@freebsd.org, danieleff@gmail.com Subject: Re: What is loading my server so much? X-BeenThere: freebsd-questions@freebsd.org X-Mailman-Version: 2.1.5 Precedence: list List-Id: User questions List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , X-List-Received-Date: Thu, 09 Dec 2010 22:39:04 -0000 > Step 1, get them to define "server" and "too slow": > > If you log in and do shell ops, is the system slow to respond? Based on > what you've reported, I'd be willing to bet that shell ops are pretty > responsive. I can't be 100% sure without more information, but I'm > willing to be that what your users are complaining about is your web > application being slow. Since you don't say what that application is, > I can only provide general advice. Shell response is fast. The application may be slow, but we should see why (high CPU load or high I/O?) > I'm guessing that PostgreSQL is the bottleneck. I'm going to first make > a few general suggestions, then provide suggestions on how to isolate the > problem more specifically. > > First off, you have 24G of RAM available and PostgreSQL only seems to > have access to 400M of it. Bump shared_buffers up to 2 or 3 G at least, > and bump up work_mem to at least a few hundred meg, and > maintenance_work_mem up to at 1/2G or so. Good point. Changed shared_buffers to 4G, work_mem to 512M and maintenance_work_mem to 2G > If the top and gstat outputs are typical, it looks like PostgreSQL is > doing mostly writes, but is not significantly blocked on writes. It looks > like individual PostgreSQL processes are simply taking a long time to do > their work. > > What's in your PostgreSQL log files? If there's nothing, then bump up > the logging information in your postgresql.conf. I particularly like > log_min_duration_statement at 500 ... any query that takes longer than > 1/2 second to execute is suspect in the types applications I work with > most frequently. Thanks for that hint also. We have some programs making huge queries (once in a day or so) so I set this to 3000 for now. > If your application is developed in-house, I'd be willing to bet a paycheck > that there are LOTS of indexes missing and that PostgreSQL is doing lots > of seq scans where it could run lots faster if it had indexes. > > Check also your autovacuum settings and ensure that tables are not bloating > out of control due to insufficient vacuuming. You may have to vacuum full/ > reindex the entire database to get things back under control, which can take > a long time if it's badly bloated. Well, we have tables with 5M+ rows (table size over 4G) and we have 500+ tables. This is probably something that is out of scope - probably you cannot help with that without knowing the structure of the database and how it is used, and it would take a long time to understand. But anyway, if there is a problem with the database (not having indexes), we see heavy I/O or CPU load of the postgresql processes right? I mean, if the bottleneck is postgresql, then we should be able to see it at the OS level. > Your application may also be suffering from lock contention if there are > lots of table locks used. Looking at the pg_locks table while things are > slow can quickly identify if this is the case, and looking at > pg_stat_activity in conjunction with that table will usually narrow down > the problem pretty quickly. We do not issue "lock" statements directly, but we use database transactions. We have a tool for checking the state of those. Most of the time, only a few open database transactions are opened, and usually we don't have locks that are not granted. > Finally, if you find that PostgreSQL is the bottleneck and you can't > narrow it down enough to fix, join the PostgreSQL general questions > mailing list and ask for help with the same level of detail you did > here. You'll find that they're an equally helpful community. I'll do that, but first let me test the new settings. Yes, thank you very much for you help. Laszlo