We have also done little to no performance tuning of Postgres' configuration. We do have indexes on all of the important columns and we have reindexed. Any pointers would be greatly appreciated.
Tuning will often double (if not more) the performance.
What are the specs of the box? Memory, CPU, Disk.
Dual PIII 1.13Ghz with 1GB of RAM and 18GB SCSI drives.
Are any of these short? Are you swapping? Pegged CPU?
The CPU gets pegged. Load average goes to 20+. Here is the memory usage from top:
Mem: 1033508K total, 939484K used, 94024K free, 35148K buffers Swap: 498004K total, 75556K used, 422448K free, 252984K cached
iostat reports heavy disk usage?
I don't know what that is. I don't have an iostat utility on the machine. This is a Debian Linux machine. Is there a package with that utility in it?
What else is running on it?
Resin, a Java web/application server. It usually consumes ~300MB.
How often does your data turn over? (Frequency of updates / inserts / deletes)
Maybe a couple hundred updates/inserts per day. Not very many deletes. Many thousand 'selects' per day though.
I assume you have vacuumed recently? Please attach output of VACUUM VERBOSE. Please attach your postgresql.conf file.
Both are attached.
Thanks, -M@
# vacuum verbose;
INFO: --Relation pg_catalog.pg_description--
INFO: Pages 12: Changed 0, Empty 0; Tup 1390: Vac 0, Keep 0, UnUsed 1.
Total CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: --Relation pg_toast.pg_toast_16416--
INFO: Pages 0: Changed 0, Empty 0; Tup 0: Vac 0, Keep 0, UnUsed 0.
Total CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: --Relation pg_catalog.pg_group--
INFO: Pages 0: Changed 0, Empty 0; Tup 0: Vac 0, Keep 0, UnUsed 0.
Total CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: --Relation pg_toast.pg_toast_1261--
INFO: Pages 0: Changed 0, Empty 0; Tup 0: Vac 0, Keep 0, UnUsed 0.
Total CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: --Relation pg_catalog.pg_proc--
INFO: Pages 58: Changed 0, Empty 0; Tup 1493: Vac 0, Keep 0, UnUsed 164.
Total CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: --Relation pg_toast.pg_toast_1255--
INFO: Pages 0: Changed 0, Empty 0; Tup 0: Vac 0, Keep 0, UnUsed 0.
Total CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: --Relation pg_catalog.pg_rewrite--
INFO: Pages 4: Changed 0, Empty 0; Tup 27: Vac 0, Keep 0, UnUsed 0.
Total CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: --Relation pg_toast.pg_toast_16410--
INFO: Pages 4: Changed 0, Empty 0; Tup 16: Vac 0, Keep 0, UnUsed 1.
Total CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: --Relation pg_catalog.pg_type--
INFO: Pages 5: Changed 0, Empty 0; Tup 239: Vac 0, Keep 0, UnUsed 0.
Total CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: --Relation pg_catalog.pg_attribute--
INFO: Pages 30: Changed 0, Empty 0; Tup 1809: Vac 0, Keep 0, UnUsed 1.
Total CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: --Relation pg_catalog.pg_class--
INFO: Pages 6: Changed 0, Empty 0; Tup 265: Vac 0, Keep 0, UnUsed 53.
Total CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: --Relation pg_catalog.pg_inherits--
INFO: Pages 0: Changed 0, Empty 0; Tup 0: Vac 0, Keep 0, UnUsed 0.
Total CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: --Relation pg_catalog.pg_index--
INFO: Pages 5: Changed 0, Empty 0; Tup 135: Vac 0, Keep 0, UnUsed 0.
Total CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: --Relation pg_catalog.pg_operator--
INFO: Pages 13: Changed 0, Empty 0; Tup 643: Vac 0, Keep 0, UnUsed 0.
Total CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: --Relation pg_catalog.pg_opclass--
INFO: Pages 1: Changed 0, Empty 0; Tup 51: Vac 0, Keep 0, UnUsed 0.
Total CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: --Relation pg_catalog.pg_am--
INFO: Pages 1: Changed 0, Empty 0; Tup 4: Vac 0, Keep 0, UnUsed 0.
Total CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: --Relation pg_catalog.pg_amop--
INFO: Pages 1: Changed 0, Empty 0; Tup 180: Vac 0, Keep 0, UnUsed 0.
Total CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: --Relation pg_catalog.pg_amproc--
INFO: Pages 1: Changed 0, Empty 0; Tup 57: Vac 0, Keep 0, UnUsed 0.
Total CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: --Relation pg_catalog.pg_language--
INFO: Pages 1: Changed 0, Empty 0; Tup 4: Vac 0, Keep 0, UnUsed 2.
Total CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: --Relation pg_catalog.pg_largeobject--
INFO: Pages 0: Changed 0, Empty 0; Tup 0: Vac 0, Keep 0, UnUsed 0.
Total CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: --Relation pg_catalog.pg_aggregate--
INFO: Pages 1: Changed 0, Empty 0; Tup 60: Vac 0, Keep 0, UnUsed 0.
Total CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: --Relation pg_catalog.pg_trigger--
INFO: Pages 1: Changed 0, Empty 0; Tup 8: Vac 0, Keep 0, UnUsed 0.
Total CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: --Relation pg_catalog.pg_listener--
INFO: Pages 0: Changed 0, Empty 0; Tup 0: Vac 0, Keep 0, UnUsed 0.
Total CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: --Relation pg_catalog.pg_cast--
INFO: Pages 2: Changed 0, Empty 0; Tup 174: Vac 0, Keep 0, UnUsed 0.
Total CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: --Relation pg_catalog.pg_namespace--
INFO: Pages 1: Changed 0, Empty 0; Tup 4: Vac 0, Keep 0, UnUsed 0.
Total CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: --Relation pg_catalog.pg_shadow--
INFO: Pages 1: Changed 0, Empty 0; Tup 1: Vac 0, Keep 0, UnUsed 0.
Total CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: --Relation pg_toast.pg_toast_1260--
INFO: Pages 0: Changed 0, Empty 0; Tup 0: Vac 0, Keep 0, UnUsed 0.
Total CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: --Relation pg_catalog.pg_conversion--
INFO: Pages 2: Changed 0, Empty 0; Tup 114: Vac 0, Keep 0, UnUsed 0.
Total CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: --Relation pg_catalog.pg_depend--
INFO: Pages 22: Changed 0, Empty 0; Tup 3104: Vac 0, Keep 0, UnUsed 0.
Total CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: --Relation pg_catalog.pg_attrdef--
INFO: Pages 2: Changed 0, Empty 0; Tup 41: Vac 0, Keep 0, UnUsed 0.
Total CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: --Relation pg_toast.pg_toast_16384--
INFO: Pages 0: Changed 0, Empty 0; Tup 0: Vac 0, Keep 0, UnUsed 0.
Total CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: --Relation pg_catalog.pg_constraint--
INFO: Pages 1: Changed 0, Empty 0; Tup 34: Vac 0, Keep 0, UnUsed 0.
Total CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: --Relation pg_toast.pg_toast_16386--
INFO: Pages 0: Changed 0, Empty 0; Tup 0: Vac 0, Keep 0, UnUsed 0.
Total CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: --Relation pg_catalog.pg_database--
INFO: Pages 1: Changed 0, Empty 0; Tup 3: Vac 0, Keep 0, UnUsed 4.
Total CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: --Relation pg_toast.pg_toast_1262--
INFO: Pages 0: Changed 0, Empty 0; Tup 0: Vac 0, Keep 0, UnUsed 0.
Total CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: --Relation public.v_phone_contracts--
INFO: Pages 11: Changed 0, Empty 0; Tup 1821: Vac 0, Keep 0, UnUsed 2.
Total CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: --Relation pg_catalog.pg_statistic--
INFO: Index pg_statistic_relid_att_index: Pages 6; Tuples 280: Deleted 130.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: Removed 130 tuples in 8 pages.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: Pages 17: Changed 12, Empty 0; Tup 280: Vac 130, Keep 0, UnUsed 32.
Total CPU 0.01s/0.00u sec elapsed 0.00 sec.
INFO: --Relation pg_toast.pg_toast_16408--
INFO: Index pg_toast_16408_index: Pages 2; Tuples 5: Deleted 5.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: Removed 5 tuples in 1 pages.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: Pages 2: Changed 2, Empty 0; Tup 5: Vac 5, Keep 0, UnUsed 0.
Total CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: --Relation public.v_categories--
INFO: Pages 1: Changed 0, Empty 0; Tup 122: Vac 0, Keep 0, UnUsed 0.
Total CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: --Relation public.v_media_types--
INFO: Pages 1: Changed 0, Empty 0; Tup 5: Vac 0, Keep 0, UnUsed 0.
Total CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: --Relation public.v_text_content--
INFO: Pages 89: Changed 0, Empty 0; Tup 2792: Vac 0, Keep 0, UnUsed 13.
Total CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: --Relation pg_toast.pg_toast_17098--
INFO: Pages 3: Changed 0, Empty 0; Tup 16: Vac 0, Keep 0, UnUsed 0.
Total CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: --Relation public.v_distrib_contracts--
INFO: Pages 0: Changed 0, Empty 0; Tup 0: Vac 0, Keep 0, UnUsed 0.
Total CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: --Relation public.v_partner_types--
INFO: Pages 1: Changed 0, Empty 0; Tup 3: Vac 0, Keep 0, UnUsed 0.
Total CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: --Relation public.ping--
INFO: Pages 0: Changed 0, Empty 0; Tup 0: Vac 0, Keep 0, UnUsed 0.
Total CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: --Relation public.v_image_content--
INFO: Pages 109: Changed 0, Empty 0; Tup 12710: Vac 0, Keep 0, UnUsed 686.
Total CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: --Relation public.v_ringtone_content--
INFO: Pages 31: Changed 0, Empty 0; Tup 2656: Vac 0, Keep 0, UnUsed 102.
Total CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: --Relation public.v_animation_content--
INFO: Pages 0: Changed 0, Empty 0; Tup 0: Vac 0, Keep 0, UnUsed 0.
Total CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: --Relation public.v_carrier_phone_link--
INFO: Pages 1: Changed 0, Empty 0; Tup 180: Vac 0, Keep 0, UnUsed 24.
Total CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: --Relation public.v_phones--
INFO: Pages 3: Changed 0, Empty 0; Tup 107: Vac 0, Keep 0, UnUsed 9.
Total CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: --Relation public.v_purchase_history_content--
INFO: Pages 0: Changed 0, Empty 0; Tup 0: Vac 0, Keep 0, UnUsed 0.
Total CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: --Relation public.v_j2me_content--
INFO: Pages 10: Changed 0, Empty 0; Tup 136: Vac 0, Keep 0, UnUsed 81.
Total CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: --Relation public.v_partners--
INFO: Pages 1: Changed 0, Empty 0; Tup 64: Vac 0, Keep 0, UnUsed 0.
Total CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: --Relation public.v_addresses--
INFO: Pages 1: Changed 0, Empty 0; Tup 5: Vac 0, Keep 0, UnUsed 0.
Total CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: --Relation public.v_contests--
INFO: Pages 0: Changed 0, Empty 0; Tup 0: Vac 0, Keep 0, UnUsed 0.
Total CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: --Relation pg_toast.pg_toast_17002--
INFO: Pages 0: Changed 0, Empty 0; Tup 0: Vac 0, Keep 0, UnUsed 0.
Total CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: --Relation public.v_entries--
INFO: Pages 0: Changed 0, Empty 0; Tup 0: Vac 0, Keep 0, UnUsed 0.
Total CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: --Relation public.v_files--
INFO: Pages 215: Changed 0, Empty 0; Tup 18519: Vac 0, Keep 0, UnUsed 46.
Total CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: --Relation public.v_content_types--
INFO: Pages 1: Changed 0, Empty 0; Tup 28: Vac 0, Keep 0, UnUsed 0.
Total CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: --Relation public.v_manufacturers--
INFO: Pages 1: Changed 0, Empty 0; Tup 17: Vac 0, Keep 0, UnUsed 0.
Total CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: --Relation public.v_usage--
INFO: Pages 46: Changed 0, Empty 0; Tup 3122: Vac 0, Keep 0, UnUsed 5.
Total CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: --Relation public.v_downloads--
INFO: Pages 0: Changed 0, Empty 0; Tup 0: Vac 0, Keep 0, UnUsed 0.
Total CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: --Relation public.v_content_category_link--
INFO: Pages 90: Changed 0, Empty 0; Tup 18324: Vac 0, Keep 0, UnUsed 36.
Total CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: --Relation public.v_contracts--
INFO: Pages 8: Changed 0, Empty 0; Tup 386: Vac 0, Keep 0, UnUsed 41.
Total CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: --Relation public.v_carts--
INFO: Index v_carts_pkey: Pages 1637; Tuples 433282: Deleted 5.
CPU 0.09s/0.13u sec elapsed 0.21 sec.
INFO: Index cart_cart_cookie: Pages 2916; Tuples 433282: Deleted 5.
CPU 0.22s/0.11u sec elapsed 15.52 sec.
INFO: Removed 5 tuples in 1 pages.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: Pages 3187: Changed 1, Empty 0; Tup 433282: Vac 5, Keep 0, UnUsed 0.
Total CPU 0.40s/0.29u sec elapsed 15.88 sec.
INFO: --Relation public.v_invoice_contents--
INFO: Pages 14: Changed 0, Empty 0; Tup 1998: Vac 0, Keep 0, UnUsed 3.
Total CPU 0.01s/0.00u sec elapsed 0.00 sec.
INFO: --Relation public.v_cart_contents--
INFO: Index v_cart_contents_pkey: Pages 45; Tuples 8932: Deleted 2.
CPU 0.00s/0.01u sec elapsed 0.00 sec.
INFO: Index cart_contents_cart_id: Pages 43; Tuples 8932: Deleted 2.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: Index cart_contents_content_id: Pages 34; Tuples 8932: Deleted 2.
CPU 0.00s/0.00u sec elapsed 0.29 sec.
INFO: Removed 2 tuples in 2 pages.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: Pages 57: Changed 0, Empty 0; Tup 8932: Vac 2, Keep 0, UnUsed 15.
Total CPU 0.00s/0.01u sec elapsed 0.31 sec.
INFO: --Relation public.v_invoices--
INFO: Pages 11: Changed 0, Empty 0; Tup 1317: Vac 0, Keep 0, UnUsed 80.
Total CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: --Relation public.v_countries--
INFO: Pages 2: Changed 0, Empty 0; Tup 239: Vac 0, Keep 0, UnUsed 0.
Total CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: --Relation public.v_subscription_downloads--
INFO: Pages 0: Changed 0, Empty 0; Tup 0: Vac 0, Keep 0, UnUsed 0.
Total CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: --Relation public.v_carriers--
INFO: Pages 1: Changed 0, Empty 0; Tup 11: Vac 0, Keep 0, UnUsed 112.
Total CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: --Relation public.v_content--
INFO: Pages 198: Changed 0, Empty 0; Tup 18294: Vac 0, Keep 0, UnUsed 887.
Total CPU 0.01s/0.00u sec elapsed 0.01 sec.
INFO: --Relation public.v_purchase_history--
INFO: Pages 0: Changed 0, Empty 0; Tup 0: Vac 0, Keep 0, UnUsed 0.
Total CPU 0.00s/0.00u sec elapsed 0.00 sec.
VACUUM
postgresql.conf
Description: Binary data
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
