X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 7CC99D1B3B6 for ; Tue, 11 May 2004 19:10:51 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 55619-01 for ; Tue, 11 May 2004 19:10:32 -0300 (ADT) Received: from mail.gotfrag.com (unknown [66.208.110.11]) by svr1.postgresql.org (Postfix) with ESMTP id C5438D1D15E for ; Tue, 11 May 2004 19:10:28 -0300 (ADT) Received: from jcoene2 (roc-66-66-149-175.rochester.rr.com [66.66.149.175]) by mail.gotfrag.com (8.12.10/8.12.8) with ESMTP id i4BMASam015217; Tue, 11 May 2004 18:10:29 -0400 (EDT) (envelope-from jcoene@gotfrag.com) Message-Id: <200405112210.i4BMASam015217@mail.gotfrag.com> From: "Jason Coene" To: Subject: Intermittent slowdowns, connection delays Date: Tue, 11 May 2004 18:10:30 -0400 MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="----=_NextPart_000_002C_01C43783.3EB43F20" X-Mailer: Microsoft Office Outlook, Build 11.0.5510 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165 Thread-Index: AcQ3pL1mEU+HR8E0RYKJWeQoZ6oQ2A== X-Virus-Scanned: by amavisd-new at postgresql.org X-Spam-Status: No, hits=0.1 tagged_above=0.0 required=5.0 tests=RCVD_IN_SORBS X-Spam-Level: X-Archive-Number: 200405/95 X-Sequence-Number: 6926 This is a multi-part message in MIME format. ------=_NextPart_000_002C_01C43783.3EB43F20 Content-Type: text/plain; charset="US-ASCII" Content-Transfer-Encoding: 7bit Hi All, We have a Postgres 7.4.1 server running on FreeBSD 5.2. Hardware is a Dual Xeon 2.6 (HT enabled), 2 GB Memory, 3Ware SATA RAID-5 w/ 4 7200 RPM Seagate disks and gigabit Intel Server Ethernet. The server is dedicated to serving data to our web-based CMS. We have a few web servers load balanced, and we do around 1M page impressions per day. Our website is highly personalized, and we've optimized it to limit the number of queries, but we still see between 2 and 3 SELECT's (with JOIN's) and 1 UPDATE per page load, selectively more - a fair volume. The single UPDATE per page load is updating a timestamp in a small table (about 150,000 rows) with only 1 index (on the 1 field that needs to be matched). We're seeing some intermittent spikes in query time as actual connection time. I.e., during these seemingly random spikes, our debug output looks like this (times from start of HTTP request): SQL CONNECTION CREATING 'gf' 0.0015 - ESTABLISHING CONNECTION 1.7113 - CONNECTION OK SQL QUERY ID 1 COST 0.8155 ROWS 1 SQL QUERY ID 2 COST 0.5607 ROWS 14 .. etc.. (all queries taking more time than normal, see below) Refresh the page 2 seconds later, and we'll get: SQL CONNECTION CREATING 'gf' 0.0017 - ESTABLISHING CONNECTION 0.0086 - CONNECTION OK SQL QUERY ID 1 COST 0.0128 ROWS 1 SQL QUERY ID 2 COST 0.0033 ROWS 14 .. etc.. (with same queries) Indeed, during these types, it takes a moment for "psql" to connect on the command line (from the same machine using a local file socket), so it's not a network issue or a web-server issue. During these spurts, there's nothing too out of the ordinary in vmstat, systat or top. These programs show that we're not using much CPU (usually 60-80% idle), and disks usage is virtually nil. I've attached 60 seconds of "vmstat 5". Memory usage looks like this (constantly): Mem: 110M Active, 1470M Inact, 206M Wired, 61M Cache, 112M Buf, 26M Free I've cleaned up and tested query after query, and nothing is a "hog". On an idle server, every query will execute in < 0.05 sec. Perhaps some of you veterans have ideas? Thanks, Jason Coene Gotfrag eSports 585-598-6621 Phone 585-598-6633 Fax jcoene@gotfrag.com http://www.gotfrag.com ------=_NextPart_000_002C_01C43783.3EB43F20 Content-Type: text/plain; name="vmstat51min.txt" Content-Transfer-Encoding: quoted-printable Content-Disposition: attachment; filename="vmstat51min.txt" d01.gotfrag.com> vmstat 5 procs memory page disks faults cpu r b w avm fre flt re pi po fr sr tw0 fd0 in sy cs us sy = id 0 9 5 335952 103108 625 0 0 0 319 4 0 0 584 0 437 3 5 = 92 0 4 5 350772 90140 24534 0 0 0 2533 0 8 0 1448 0 45969 8= 22 71 0 0 0 321016 112884 10603 0 0 0 2840 0 3 0 2030 0 26562 6= 12 82 0 0 0 341428 99548 10823 0 0 0 1014 0 4 0 687 0 4891 4 = 5 91 0 0 0 352064 91748 13041 0 0 0 1979 0 6 0 743 0 4950 6 = 6 88 0 0 0 346236 96024 7562 0 0 0 2070 0 2 0 736 0 2057 4 = 3 93 0 1 0 366876 82184 10081 0 0 0 1502 0 50 0 828 0 2607 5 = 5 90 0 0 0 321600 112344 9724 0 0 0 3984 0 1 0 885 0 3440 5 = 5 90 2 0 0 321200 112716 24244 0 0 0 2571 0 8 0 794 0 33756 8= 17 75 0 0 1 329016 107352 16676 0 0 0 2834 0 10 0 922 0 44430 9= 20 71 0 0 0 328620 107328 13862 0 0 0 1713 0 2 0 616 0 8500 4 = 7 90 0 0 0 317376 114780 3798 0 0 0 1321 0 0 0 514 0 1137 2 = 2 97 0 5 0 334724 102396 12999 0 0 0 1106 0 39 0 672 0 24891 5= 13 82 0 3 3 336904 102068 12886 0 0 0 2527 0 29 0 879 0 18817 6= 10 84 2 0 0 324008 110416 14625 0 0 0 2378 0 4 0 745 0 28433 7= 14 79 0 0 4 333692 104400 15440 0 0 0 1154 0 7 0 645 0 31156 4= 16 80 4 12 0 352328 91884 19349 0 0 0 1095 0 5 0 623 0 46283 = 9 21 70 5 5 0 345796 95412 15790 0 0 0 1896 0 2 0 727 0 50062 10= 20 70 4 1 0 331440 105316 16178 0 0 0 2909 0 5 0 1728 0 48194 9= 20 71 0 0 0 326664 108364 11869 0 0 0 1533 0 61 0 640 0 11855 5= 9 85 0 0 2 322980 110452 5970 0 0 0 1520 0 0 0 594 0 1614 3 = 3 95 0 10 6 343108 97884 17571 0 0 0 1409 0 14 0 643 0 33528 = 6 18 76 ------=_NextPart_000_002C_01C43783.3EB43F20--