Thread

1. can't handle large number of INSERT/UPDATEs

   Anjan Dave <adave@vantage.com> — 2004-10-25T20:53:23Z

   Hi,
   
    
   
   I am dealing with an app here that uses pg to handle a few thousand
   concurrent web users. It seems that under heavy load, the INSERT and
   UPDATE statements to one or two specific tables keep queuing up, to the
   count of 150+ (one table has about 432K rows, other has about 2.6Million
   rows), resulting in 'wait's for other queries, and then everything piles
   up, with the load average shooting up to 10+. 
   
    
   
   We (development) have gone through the queries/explain analyzes and made
   sure the appropriate indexes exist among other efforts put in.
   
    
   
   I would like to know if there is anything that can be changed for better
   from the systems perspective. Here's what I have done and some recent
   changes from the system side:
   
    
   
   -Upgraded from 7.4.0 to 7.4.1 sometime ago
   
   -Upgraded from RH8 to RHEL 3.0
   
   -The settings from postgresql.conf (carried over, basically) are:
   
               shared_buffers = 10240 (80MB)
   
               max_connections = 400
   
               sort_memory = 1024
   
               effective_cache_size = 262144 (2GB)
   
               checkpoint_segments = 15
   
   stats_start_collector = true
   
   stats_command_string = true 
   
   Rest everything is at default
   
    
   
   In /etc/sysctl.conf (512MB shared mem)
   
   kernel.shmall = 536870912
   
   kernel.shmmax = 536870912
   
    
   
   -This is a new Dell 6650 (quad XEON 2.2GHz, 8GB RAM, Internal HW
   RAID10), RHEL 3.0 (2.4.21-20.ELsmp), PG 7.4.1
   
   -Vaccum Full run everyday
   
   -contrib/Reindex run everyday
   
   -Disabled HT in BIOS
   
    
   
   I would greatly appreciate any helpful ideas.
   
    
   
   Thanks in advance,
   
    
   
   Anjan
   
   

2. Re: can't handle large number of INSERT/UPDATEs

   Rod Taylor <pg@rbt.ca> — 2004-10-25T21:19:14Z

   On Mon, 2004-10-25 at 16:53, Anjan Dave wrote:
   > Hi,
   > 
   >  
   > 
   > I am dealing with an app here that uses pg to handle a few thousand
   > concurrent web users. It seems that under heavy load, the INSERT and
   > UPDATE statements to one or two specific tables keep queuing up, to
   > the count of 150+ (one table has about 432K rows, other has about
   > 2.6Million rows), resulting in ‘wait’s for other queries, and then
   
   This isn't an index issue, it's a locking issue. Sounds like you have a
   bunch of inserts and updates hitting the same rows over and over again.
   
   Eliminate that contention point, and you will have solved your problem.
   
   Free free to describe the processes involved, and we can help you do
   that.
   
   
   
   

3. Re: can't handle large number of INSERT/UPDATEs

   Dustin Sallings <dustin@spy.net> — 2004-10-26T02:29:19Z

   On Oct 25, 2004, at 13:53, Anjan Dave wrote:
   
   > I am dealing with an app here that uses pg to handle a few thousand 
   > concurrent web users. It seems that under heavy load, the INSERT and 
   > UPDATE statements to one or two specific tables keep queuing up, to 
   > the count of 150+ (one table has about 432K rows, other has about 
   > 2.6Million rows), resulting in ‘wait’s for other queries, and then 
   > everything piles up, with the load average shooting up to 10+.
   
   	Depending on your requirements and all that, but I had a similar issue 
   in one of my applications and made the problem disappear entirely by 
   serializing the transactions into a separate thread (actually, a thread 
   pool) responsible for performing these transactions.  This reduced the 
   load on both the application server and the DB server.
   
   	Not a direct answer to your question, but I've found that a lot of 
   times when someone has trouble scaling a database application, much of 
   the performance win can be in trying to be a little smarter about how 
   and when the database is accessed.
   
   --
   SPY                      My girlfriend asked me which one I like better.
   pub  1024/3CAE01D5 1994/11/03 Dustin Sallings <dustin@spy.net>
   |    Key fingerprint =  87 02 57 08 02 D0 DA D6  C8 0F 3E 65 51 98 D8 BE
   L_______________________ I hope the answer won't upset her. ____________
   
   
   

4. Re: can't handle large number of INSERT/UPDATEs

   Andrew McMillan <andrew@catalyst.net.nz> — 2004-10-26T20:50:50Z

   On Mon, 2004-10-25 at 16:53 -0400, Anjan Dave wrote:
   > Hi,
   > 
   >  
   > 
   > I am dealing with an app here that uses pg to handle a few thousand
   > concurrent web users. It seems that under heavy load, the INSERT and
   > UPDATE statements to one or two specific tables keep queuing up, to
   > the count of 150+ (one table has about 432K rows, other has about
   > 2.6Million rows), resulting in ‘wait’s for other queries, and then
   > everything piles up, with the load average shooting up to 10+. 
   
   Hi,
   
   We saw a similar problem here that was related to the locking that can
   happen against referred tables for referential integrity.
   
   In our case we had referred tables with very few rows (i.e. < 10) which
   caused the insert and update on the large tables to be effectively
   serialised due to the high contention on the referred tables.
   
   We changed our app to implement those referential integrity checks
   differently and performance was hugely boosted.
   
   Regards,
   					Andrew.
   -------------------------------------------------------------------------
   Andrew @ Catalyst .Net .NZ  Ltd,  PO Box 11-053, Manners St,  Wellington
   WEB: http://catalyst.net.nz/            PHYS: Level 2, 150-154 Willis St
   DDI: +64(4)803-2201      MOB: +64(272)DEBIAN      OFFICE: +64(4)499-2267
                           Chicken Little was right.
   -------------------------------------------------------------------------