Performance advice - Mailing list pgsql-performance
From | Michael Mattox |
---|---|
Subject | Performance advice |
Date | |
Msg-id | CJEBLDCHAADCLAGIGCOOCEJECKAA.michael.mattox@verideon.com Whole thread Raw |
Responses |
Re: Performance advice
("Shridhar Daithankar" <shridhar_daithankar@persistent.co.in>)
Re: Performance advice (Richard Huxton <dev@archonet.com>) Re: Performance advice ("Michael Mattox" <michael.mattox@verideon.com>) Re: Performance advice (Manfred Koizar <mkoi-pg@aon.at>) |
List | pgsql-performance |
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
Attachment
pgsql-performance by date: