I'd like to get some feedback on my setup to see if I can optimize my
database performance. My application has two separate applications:
The first application connects to websites and records the statistics in the
database. Websites are monitored every 5 or 10 minutes (depends on client),
there are 900 monitors which comes out to 7,800 monitorings per hour. The
monitor table has columns "nextdate" and "status" which are updated with
every monitoring, and also a row is inserted into the status table and the
status item table. For my performance testing (we're just about to go live)
I've loaded the database with a month of data (we don't plan to keep data
longer than 1 month). So my status table has 6 million records and my
status item table has 6 million records as well. One key is that the system
is multithreaded so up to 32 processes are accessing the database at the
same time, updating the "nextdate" before the monitoring and inserting the
status and status item records after. There is a serious performance
constraint here because unlike a webserver, this application cannot slow
down. If it slows down, we won't be able to monitor our sites at 5 minute
intervals which will make our customers unhappy.
The second application is a web app (tomcat) which lets customers check
their status. Both of these applications are deployed on the same server, a
4 CPU (Xeon) with 1.5 gigs of RAM. The OS (RedHat Linux 7.3) and servers
are running on 18gig 10,000 RPM SCSI disk that is mirrored to a 2nd disk.
The database data directory is on a separate 36 gig 10,000 RPM SCSI disk
(we're trying to buy a 2nd disk to mirror it). I'm using Postgres 7.3.2.
Issue #1 - Vacuum => Overall the system runs pretty well and seems stable.
Last night I did a "vacuum full analyze" and then ran my app overnight and
first thing in the morning I did a "vacuum analyze", which took 35 minutes.
I'm not sure if this is normal for a database this size (there are 15,000
updates per hour). During the vacuum my application does slow down quite a
bit and afterwards is slow speeds back up. I've attached the vacuum output
to this mail. I'm using Java Data Objects (JDO) so if table/column names
look weird it's because the schema is automatically generated.
Issue #2 - postgres.conf => I'd love to get some feedback on these settings.
I've read the archives and no one seems to agree I know, but with the above
description of my app I hope someone can at least point me in the right
direction:
max_connections = 200
#
# Shared Memory Size
#
shared_buffers = 3072 # min max_connections*2 or 16, 8KB each
#max_fsm_relations = 1000 # min 10, fsm is free space map, ~40 bytes
#max_fsm_pages = 10000 # min 1000, fsm is free space map, ~6 bytes
#max_locks_per_transaction = 64 # min 10
#wal_buffers = 8 # min 4, typically 8KB each
#
# Non-shared Memory Sizes
#
sort_mem = 8192 # min 64, size in KB
vacuum_mem = 24576 # min 1024, size in KB
The rest are left uncommented (using the defaults).
Issue #3 - server hardware =>
- Is there anything I can do with the hardware to increase performance?
- Should I increase the ram to 2 gigs? top shows that it is using the swap
a bit (about 100k only).
- I have at my disposal one other server which has 2 Xeons, 10,000 RPM SCSI
drive. Would it make sense to put Postgres on it and leave my apps running
on the more powerful 4 CPU server?
- Would a RAID setup make the disk faster? Because top rarely shows the
CPUs above 50%, I suspect maybe the disk is the bottleneck.
I'm thrilled to be able to use Postgres instead of a commercial database and
I'm looking forward to putting this into production. Any help with the
above questions would be greatly appreciated.
Michael Mattox
veriguard=# vacuum verbose analyze;
INFO: --Relation pg_catalog.pg_description--
INFO: Pages 12: Changed 0, Empty 0; Tup 1390: Vac 0, Keep 0, UnUsed 1.
Total CPU 0.01s/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: Analyzing pg_catalog.pg_description
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: Analyzing pg_catalog.pg_group
INFO: --Relation pg_catalog.pg_proc--
INFO: Pages 58: Changed 0, Empty 0; Tup 1492: Vac 0, Keep 0, UnUsed 165.
Total CPU 0.00s/0.00u sec elapsed 0.01 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: Analyzing pg_catalog.pg_proc
INFO: --Relation pg_catalog.pg_rewrite--
INFO: Pages 4: Changed 0, Empty 0; Tup 29: 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: Analyzing pg_catalog.pg_rewrite
INFO: --Relation pg_catalog.pg_type--
INFO: Pages 4: Changed 0, Empty 0; Tup 195: Vac 0, Keep 0, UnUsed 0.
Total CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: Analyzing pg_catalog.pg_type
INFO: --Relation pg_catalog.pg_attribute--
INFO: Pages 19: Changed 0, Empty 0; Tup 1131: Vac 0, Keep 0, UnUsed 2.
Total CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: Analyzing pg_catalog.pg_attribute
INFO: --Relation pg_catalog.pg_class--
INFO: Pages 4: Changed 0, Empty 0; Tup 181: Vac 0, Keep 0, UnUsed 31.
Total CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: Analyzing pg_catalog.pg_class
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: Analyzing pg_catalog.pg_inherits
INFO: --Relation pg_catalog.pg_index--
INFO: Pages 3: Changed 0, Empty 0; Tup 95: Vac 0, Keep 0, UnUsed 2.
Total CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: Analyzing pg_catalog.pg_index
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: Analyzing pg_catalog.pg_operator
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: Analyzing pg_catalog.pg_opclass
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: Analyzing pg_catalog.pg_am
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: Analyzing pg_catalog.pg_amop
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: Analyzing pg_catalog.pg_amproc
INFO: --Relation pg_catalog.pg_language--
INFO: Pages 1: Changed 0, Empty 0; Tup 3: Vac 0, Keep 0, UnUsed 3.
Total CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: Analyzing pg_catalog.pg_language
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: Analyzing pg_catalog.pg_largeobject
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: Analyzing pg_catalog.pg_aggregate
INFO: --Relation pg_catalog.pg_trigger--
INFO: Pages 1: Changed 0, Empty 0; Tup 2: Vac 0, Keep 0, UnUsed 0.
Total CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: Analyzing pg_catalog.pg_trigger
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: Analyzing pg_catalog.pg_listener
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: Analyzing pg_catalog.pg_cast
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: Analyzing pg_catalog.pg_namespace
INFO: --Relation pg_catalog.pg_shadow--
INFO: Pages 1: Changed 0, Empty 0; Tup 1: Vac 0, Keep 0, UnUsed 1.
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: Analyzing pg_catalog.pg_shadow
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: Analyzing pg_catalog.pg_conversion
INFO: --Relation pg_catalog.pg_depend--
INFO: Pages 21: Changed 0, Empty 0; Tup 2905: Vac 0, Keep 0, UnUsed 2.
Total CPU 0.00s/0.00u sec elapsed 0.01 sec.
INFO: Analyzing pg_catalog.pg_depend
INFO: --Relation pg_catalog.pg_attrdef--
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_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: Analyzing pg_catalog.pg_attrdef
INFO: --Relation pg_catalog.pg_constraint--
INFO: Pages 1: Changed 0, Empty 0; Tup 10: 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: Analyzing pg_catalog.pg_constraint
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: Analyzing pg_catalog.pg_database
INFO: --Relation public.companycontactx--
INFO: Pages 14: Changed 0, Empty 0; Tup 586: Vac 0, Keep 0, UnUsed 1.
Total CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: --Relation pg_toast.pg_toast_17030--
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: Analyzing public.companycontactx
INFO: --Relation public.jdo_sequencex--
INFO: Index jdo_sequencex_pkey: Pages 1027; Tuples 1: Deleted 5124.
CPU 0.01s/0.03u sec elapsed 0.04 sec.
INFO: Removed 5124 tuples in 28 pages.
CPU 0.00s/0.01u sec elapsed 0.00 sec.
INFO: Pages 28: Changed 1, Empty 0; Tup 1: Vac 5124, Keep 0, UnUsed 0.
Total CPU 0.01s/0.04u sec elapsed 0.04 sec.
INFO: Analyzing public.jdo_sequencex
INFO: --Relation public.monitorstatusx--
INFO: Index monitorstatusx_pkey: Pages 24449; Tuples 6398745: Deleted 148.
CPU 1.75s/1.92u sec elapsed 98.39 sec.
INFO: Index monitorstatusxmonitori: Pages 22378; Tuples 6399034: Deleted 148.
CPU 3.63s/3.09u sec elapsed 206.76 sec.
INFO: Removed 148 tuples in 109 pages.
CPU 0.02s/0.02u sec elapsed 1.39 sec.
INFO: Pages 136706: Changed 151, Empty 0; Tup 6398610: Vac 148, Keep 0, UnUsed 6.
Total CPU 12.60s/6.74u sec elapsed 382.70 sec.
INFO: --Relation pg_toast.pg_toast_16995--
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: Analyzing public.monitorstatusx
INFO: --Relation public.monitorgroup_monitorsx--
INFO: Pages 10: Changed 0, Empty 0; Tup 1489: Vac 0, Keep 0, UnUsed 61.
Total CPU 0.01s/0.00u sec elapsed 0.03 sec.
INFO: Analyzing public.monitorgroup_monitorsx
INFO: --Relation pg_catalog.pg_statistic--
INFO: Index pg_statistic_relid_att_index: Pages 4; Tuples 254: Deleted 395.
CPU 0.01s/0.01u sec elapsed 0.12 sec.
INFO: Removed 395 tuples in 15 pages.
CPU 0.00s/0.00u sec elapsed 0.15 sec.
INFO: Pages 22: Changed 20, Empty 0; Tup 254: Vac 395, Keep 13, UnUsed 107.
Total CPU 0.01s/0.01u sec elapsed 0.37 sec.
INFO: --Relation pg_toast.pg_toast_16408--
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.monitorx--
INFO: Index monitorx_pkey: Pages 1194; Tuples 2213: Deleted 170055.
CPU 1.16s/2.86u sec elapsed 58.16 sec.
INFO: Index monitorxstatusi: Pages 3845; Tuples 2862: Deleted 170055.
CPU 1.53s/3.49u sec elapsed 78.64 sec.
INFO: Index monitorx_id_index: Pages 4187; Tuples 3070: Deleted 170055.
CPU 1.59s/3.11u sec elapsed 72.21 sec.
INFO: Index monitorx_nextdate_enabled_index: Pages 1070; Tuples 3720: Deleted 170055.
CPU 1.27s/2.79u sec elapsed 68.05 sec.
INFO: Removed 170055 tuples in 6036 pages.
CPU 0.52s/0.81u sec elapsed 206.26 sec.
INFO: Pages 6076: Changed 0, Empty 0; Tup 2057: Vac 170055, Keep 568, UnUsed 356.
Total CPU 6.28s/13.23u sec elapsed 486.07 sec.
INFO: --Relation pg_toast.pg_toast_17006--
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: Analyzing public.monitorx
INFO: --Relation public.monitor_monitorlocationsx--
INFO: Pages 14: Changed 0, Empty 0; Tup 2073: Vac 0, Keep 0, UnUsed 2.
Total CPU 0.00s/0.01u sec elapsed 0.00 sec.
INFO: Analyzing public.monitor_monitorlocationsx
INFO: --Relation public.monitorgroupx--
INFO: Pages 14: Changed 0, Empty 0; Tup 586: Vac 0, Keep 0, UnUsed 21.
Total CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: Analyzing public.monitorgroupx
INFO: --Relation public.customeraccountx--
INFO: Pages 14: Changed 0, Empty 0; Tup 586: Vac 0, Keep 0, UnUsed 0.
Total CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: --Relation pg_toast.pg_toast_17044--
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: Analyzing public.customeraccountx
INFO: --Relation public.monitorstatusitemx--
INFO: Index monitorstatusitemx_pkey: Pages 24459; Tuples 6401221: Deleted 165.
CPU 1.69s/1.97u sec elapsed 63.83 sec.
INFO: Removed 165 tuples in 125 pages.
CPU 0.05s/0.05u sec elapsed 1.34 sec.
INFO: Pages 145514: Changed 2021, Empty 0; Tup 6401160: Vac 165, Keep 0, UnUsed 6.
Total CPU 7.98s/3.23u sec elapsed 153.89 sec.
INFO: --Relation pg_toast.pg_toast_17037--
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: Analyzing public.monitorstatusitemx
INFO: --Relation public.addressx--
INFO: Pages 10: Changed 0, Empty 0; Tup 586: Vac 0, Keep 0, UnUsed 0.
Total CPU 0.01s/0.00u sec elapsed 0.09 sec.
INFO: --Relation pg_toast.pg_toast_17014--
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: Analyzing public.addressx
INFO: --Relation public.monitorstatusitemlistx--
INFO: Index monitorstatusitemlistx_pkey: Pages 24464; Tuples 6402415: Deleted 181.
CPU 1.62s/2.11u sec elapsed 93.29 sec.
INFO: Removed 181 tuples in 129 pages.
CPU 0.01s/0.03u sec elapsed 1.86 sec.
INFO: Pages 101654: Changed 1442, Empty 0; Tup 6402153: Vac 181, Keep 0, UnUsed 5.
Total CPU 9.87s/4.87u sec elapsed 291.93 sec.
INFO: Analyzing public.monitorstatusitemlistx
INFO: --Relation public.companyx--
INFO: Pages 10: Changed 0, Empty 0; Tup 586: Vac 0, Keep 0, UnUsed 0.
Total CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: Analyzing public.companyx
INFO: --Relation public.monitorstatusitemlistd8ea58a5x--
INFO: Index monitorstatusitejdoid7db0befci: Pages 24470; Tuples 6403200: Deleted 175.
CPU 1.96s/2.07u sec elapsed 97.15 sec.
INFO: Index monitorstatusitemlist6fe47957i: Pages 24470; Tuples 6403333: Deleted 175.
CPU 1.51s/1.77u sec elapsed 92.20 sec.
INFO: Removed 175 tuples in 100 pages.
CPU 0.04s/0.04u sec elapsed 2.25 sec.
INFO: Pages 37703: Changed 570, Empty 0; Tup 6403027: Vac 175, Keep 0, UnUsed 8.
Total CPU 6.58s/5.15u sec elapsed 262.83 sec.
INFO: Analyzing public.monitorstatusitemlistd8ea58a5x
INFO: --Relation public.monitorstatus_statusitemsx--
INFO: Index monitorstatus_stjdoidb742c9b3i: Pages 24475; Tuples 6403913: Deleted 175.
CPU 1.80s/2.12u sec elapsed 109.54 sec.
INFO: Index monitorstatus_stitemsa2bb7ff1i: Pages 24475; Tuples 6404057: Deleted 175.
CPU 1.72s/1.95u sec elapsed 107.31 sec.
INFO: Removed 175 tuples in 107 pages.
CPU 0.01s/0.02u sec elapsed 1.82 sec.
INFO: Pages 47124: Changed 701, Empty 0; Tup 6403754: Vac 175, Keep 0, UnUsed 6.
Total CPU 6.85s/5.14u sec elapsed 298.35 sec.
INFO: Analyzing public.monitorstatus_statusitemsx
INFO: --Relation public.companycontact_rncies9688bceax--
INFO: Pages 1: Changed 0, Empty 0; Tup 4: Vac 0, Keep 0, UnUsed 0.
Total CPU 0.00s/0.00u sec elapsed 0.04 sec.
INFO: Analyzing public.companycontact_rncies9688bceax
VACUUM
veriguard=#
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
