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.
Advertising
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
[EMAIL PROTECTED]
http://www.gotfrag.com
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
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
