2

I'm using Django to write to a Postgres DB on my server. The server receives about 800 insert requests every 15 minutes. Recently when I run ps aux on the server this is the number of postgres processes I see:

postgres  5721  0.0  0.6 102380 27372 ?        Ss   Jan27   0:04 postgres: writer process                                                      
postgres  5722  0.0  0.0 102280  1684 ?        Ss   Jan27   0:01 postgres: wal writer process                                                  
postgres  5723  0.0  0.0 102416  1800 ?        Ss   Jan27   0:03 postgres: autovacuum launcher process                                         
postgres  5724  0.0  0.0  73828  1480 ?        Ss   Jan27   0:12 postgres: stats collector process                                             
root      8621  0.0  0.2 107912  8736 ?        Ss   Jan27   0:03 /usr/sbin/apache2 -k start
www-data  8628  0.0  0.0 104444  3096 ?        S    Jan27   0:00 /usr/sbin/apache2 -k start
www-data  8629  0.0  0.0 104444  2908 ?        S    Jan27   0:00 /usr/sbin/fcgi-pm -k start
www-data  8630  0.0  0.0 105620  2976 ?        S    Jan27   0:00 /usr/sbin/apache2 -k start
www-data 29332  1.2  1.1 471932 47432 ?        Sl   18:45   0:08 /usr/sbin/apache2 -k start
www-data 29350  1.0  1.2 468712 49372 ?        Sl   18:45   0:07 /usr/sbin/apache2 -k start
www-data 29351  1.2  1.2 541820 51836 ?        Sl   18:45   0:08 /usr/sbin/apache2 -k start
postgres 29386  0.1  0.7 104932 31680 ?        Ss   18:45   0:00 postgres: abs ap 127.0.0.1(48450) INSERT                 
postgres 29401  0.1  0.7 104932 29784 ?        Ss   18:45   0:00 postgres: abs ap 127.0.0.1(48463) INSERT                 
postgres 29407  0.1  0.7 105060 29888 ?        Ss   18:46   0:00 postgres: abs ap 127.0.0.1(48467) COMMIT                 
postgres 29408  0.1  0.7 104932 31852 ?        Ss   18:46   0:00 postgres: abs ap 127.0.0.1(48468) INSERT                 
www-data 29409  1.2  1.0 467728 42204 ?        Sl   18:46   0:07 /usr/sbin/apache2 -k start
postgres 29428  0.1  0.7 105060 31972 ?        Ss   18:46   0:00 postgres: abs ap 127.0.0.1(48470) COMMIT                 
postgres 29433  0.1  0.7 104932 31788 ?        Ss   18:46   0:00 postgres: abs ap 127.0.0.1(48474) INSERT                 
postgres 29434  0.1  0.7 104932 31908 ?        Ss   18:46   0:00 postgres: abs ap 127.0.0.1(48475) COMMIT                 
postgres 29435  0.1  0.7 104932 31836 ?        Ss   18:46   0:00 postgres: abs ap 127.0.0.1(48476) COMMIT                 
postgres 29439  0.1  0.7 104932 31736 ?        Ss   18:46   0:00 postgres: abs ap 127.0.0.1(48480) COMMIT                 
postgres 29440  0.1  0.7 105060 31352 ?        Ss   18:46   0:00 postgres: abs ap 127.0.0.1(48481) COMMIT                 
postgres 29441  0.1  0.7 105056 31960 ?        Ss   18:46   0:00 postgres: abs ap 127.0.0.1(48482) COMMIT                 
postgres 29443  0.1  0.7 104932 31248 ?        Ss   18:46   0:00 postgres: abs ap 127.0.0.1(48484) COMMIT                 
postgres 29444  0.1  0.7 104932 31976 ?        Ss   18:46   0:00 postgres: abs ap 127.0.0.1(48485) COMMIT                 
www-data 29445  1.0  1.0 470256 44348 ?        Sl   18:46   0:06 /usr/sbin/apache2 -k start
postgres 29487  0.1  0.7 104932 32012 ?        Ss   18:46   0:00 postgres: abs ap 127.0.0.1(48487) COMMIT                 
postgres 29489  0.1  0.7 105060 31480 ?        Ss   18:47   0:00 postgres: abs ap 127.0.0.1(48489) INSERT                 
postgres 29491  0.1  0.7 104932 31788 ?        Ss   18:47   0:00 postgres: abs ap 127.0.0.1(48490) INSERT                 
postgres 29492  0.1  0.7 104932 31944 ?        Ss   18:47   0:00 postgres: abs ap 127.0.0.1(48491) INSERT                 
postgres 29496  0.1  0.7 104932 31260 ?        Ss   18:47   0:00 postgres: abs ap 127.0.0.1(48493) COMMIT                 
postgres 29499  0.1  0.7 105056 32088 ?        Ss   18:47   0:00 postgres: abs ap 127.0.0.1(48495) COMMIT                 
postgres 29502  0.1  0.7 105060 31936 ?        Ss   18:47   0:00 postgres: abs ap 127.0.0.1(48497) INSERT                 
postgres 29506  0.1  0.7 104932 31916 ?        Ss   18:47   0:00 postgres: abs ap 127.0.0.1(48501) INSERT                 
postgres 29518  0.1  0.7 104932 31736 ?        Ss   18:48   0:00 postgres: abs ap 127.0.0.1(48511) COMMIT                 
postgres 29523  0.1  0.7 104932 31524 ?        Ss   18:48   0:00 postgres: abs ap 127.0.0.1(48512) INSERT                 
postgres 29525  0.1  0.6 104932 27776 ?        Ss   18:48   0:00 postgres: abs ap 127.0.0.1(48514) COMMIT                 
postgres 29526  0.1  0.7 104932 30992 ?        Ss   18:48   0:00 postgres: abs ap 127.0.0.1(48515) COMMIT                 
postgres 29531  0.1  0.7 104932 28992 ?        Ss   18:48   0:00 postgres: abs ap 127.0.0.1(48516) INSERT                 
postgres 29532  0.1  0.7 104932 29792 ?        Ss   18:48   0:00 postgres: abs ap 127.0.0.1(48517) COMMIT                 
postgres 29533  0.1  0.7 104932 30396 ?        Ss   18:48   0:00 postgres: abs ap 127.0.0.1(48518) INSERT                 
postgres 29536  0.1  0.7 104932 31028 ?        Ss   18:48   0:00 postgres: abs ap 127.0.0.1(48520) INSERT                 
postgres 29537  0.1  0.7 104932 29264 ?        Ss   18:48   0:00 postgres: abs ap 127.0.0.1(48521) INSERT                 
postgres 29542  0.1  0.7 104932 29596 ?        Ss   18:48   0:00 postgres: abs ap 127.0.0.1(48525) COMMIT                 
postgres 29543  0.1  0.7 104932 30560 ?        Ss   18:48   0:00 postgres: abs ap 127.0.0.1(48526) COMMIT                 
postgres 29545  0.1  0.7 104932 30864 ?        Ss   18:48   0:00 postgres: abs ap 127.0.0.1(48528) INSERT                 
postgres 29550  0.1  0.7 104932 30964 ?        Ss   18:48   0:00 postgres: abs ap 127.0.0.1(48529) COMMIT                 
postgres 29552  0.1  0.7 104932 31012 ?        Ss   18:48   0:00 postgres: abs ap 127.0.0.1(48530) COMMIT                 
postgres 29553  0.1  0.7 104932 31260 ?        Ss   18:48   0:00 postgres: abs ap 127.0.0.1(48531) SELECT                 
postgres 29554  0.1  0.7 104932 30976 ?        Ss   18:49   0:00 postgres: abs ap 127.0.0.1(48532) INSERT                 
postgres 29556  0.1  0.7 104932 30084 ?        Ss   18:49   0:00 postgres: abs ap 127.0.0.1(48534) INSERT                 
postgres 29557  0.1  0.7 104932 30644 ?        Ss   18:49   0:00 postgres: abs ap 127.0.0.1(48535) INSERT                 
postgres 29558  0.1  0.7 104932 30408 ?        Ss   18:49   0:00 postgres: abs ap 127.0.0.1(48536) INSERT                 
postgres 29559  0.1  0.7 104932 30048 ?        Ss   18:49   0:00 postgres: abs ap 127.0.0.1(48537) INSERT                 
postgres 29560  0.1  0.7 104932 30488 ?        Ss   18:49   0:00 postgres: abs ap 127.0.0.1(48538) COMMIT                 
postgres 29561  0.1  0.7 104932 29840 ?        Ss   18:49   0:00 postgres: abs ap 127.0.0.1(48539) INSERT                 
postgres 29562  0.1  0.7 104932 30616 ?        Ss   18:49   0:00 postgres: abs ap 127.0.0.1(48540) COMMIT                 
postgres 29564  0.1  0.7 104932 30456 ?        Ss   18:49   0:00 postgres: abs ap 127.0.0.1(48541) INSERT                 
postgres 29565  0.2  0.7 104932 30612 ?        Ss   18:49   0:00 postgres: abs ap 127.0.0.1(48542) COMMIT                 
postgres 29566  0.2  0.7 104932 30512 ?        Ss   18:49   0:00 postgres: abs ap 127.0.0.1(48543) INSERT                 
postgres 29567  0.2  0.7 104932 30964 ?        Ss   18:49   0:00 postgres: abs ap 127.0.0.1(48544) INSERT                 
postgres 29568  0.2  0.7 104932 30460 ?        Ss   18:49   0:00 postgres: abs ap 127.0.0.1(48545) INSERT                 
postgres 29569  0.2  0.7 104932 30684 ?        Ss   18:49   0:00 postgres: abs ap 127.0.0.1(48546) INSERT                 
postgres 29571  0.2  0.7 104932 31188 ?        Ss   18:49   0:00 postgres: abs ap 127.0.0.1(48548) COMMIT                 
postgres 29572  0.2  0.7 104932 31280 ?        Ss   18:49   0:00 postgres: abs ap 127.0.0.1(48549) INSERT                 
postgres 29573  0.2  0.7 104932 30632 ?        Ss   18:49   0:00 postgres: abs ap 127.0.0.1(48550) COMMIT                 
postgres 29574  0.2  0.7 104932 30784 ?        Ss   18:49   0:00 postgres: abs ap 127.0.0.1(48551) INSERT                 
postgres 29575  0.2  0.7 104932 31060 ?        Ss   18:49   0:00 postgres: abs ap 127.0.0.1(48552) INSERT                 
postgres 29576  0.2  0.7 104932 31064 ?        Ss   18:49   0:00 postgres: abs ap 127.0.0.1(48553) COMMIT                 
postgres 29577  0.2  0.7 105008 31184 ?        Ss   18:49   0:00 postgres: abs ap 127.0.0.1(48554) INSERT                 
postgres 29578  0.2  0.7 105016 31168 ?        Ss   18:50   0:00 postgres: abs ap 127.0.0.1(48555) COMMIT                 
postgres 29579  0.2  0.7 105064 30632 ?        Ss   18:50   0:00 postgres: abs ap 127.0.0.1(48556) INSERT                 
postgres 29580  0.2  0.7 105016 31208 ?        Ss   18:50   0:00 postgres: abs ap 127.0.0.1(48557) COMMIT                 
postgres 29582  0.2  0.7 104932 31268 ?        Ss   18:50   0:00 postgres: abs ap 127.0.0.1(48559) COMMIT                 
postgres 29583  0.2  0.7 104936 31348 ?        Ss   18:50   0:00 postgres: abs ap 127.0.0.1(48560) COMMIT                 
postgres 29585  0.2  0.7 104932 31280 ?        Ss   18:50   0:00 postgres: abs ap 127.0.0.1(48561) COMMIT                 
postgres 29586  0.2  0.7 104936 31376 ?        Ds   18:50   0:00 postgres: abs ap 127.0.0.1(48562) COMMIT                 
www-data 29587  1.5  0.8 462216 35916 ?        Sl   18:50   0:05 /usr/sbin/apache2 -k start
postgres 29775  0.2  0.7 104932 30528 ?        Ss   18:51   0:00 postgres: abs ap 127.0.0.1(48722) INSERT                 
postgres 29776  0.2  0.7 104936 31180 ?        Ss   18:51   0:00 postgres: abs ap 127.0.0.1(48723) COMMIT                 
postgres 29777  0.2  0.7 104932 31292 ?        Ss   18:51   0:00 postgres: abs ap 127.0.0.1(48724) INSERT                 
postgres 29944  0.2  0.6 105016 28424 ?        Ss   18:51   0:00 postgres: abs ap 127.0.0.1(48890) COMMIT                 
postgres 30128  0.3  0.7 104936 29836 ?        Ss   18:52   0:00 postgres: abs ap 127.0.0.1(49070) INSERT                 
postgres 30129  0.3  0.7 104932 29444 ?        Ss   18:52   0:00 postgres: abs ap 127.0.0.1(49071) INSERT                 
postgres 30225  0.3  0.7 104932 30020 ?        Ss   18:52   0:00 postgres: abs ap 127.0.0.1(49167) INSERT                 
postgres 30301  0.2  0.7 104932 28884 ?        Ss   18:53   0:00 postgres: abs ap 127.0.0.1(49242) COMMIT                 
postgres 30302  0.2  0.6 104932 28136 ?        Ss   18:53   0:00 postgres: abs ap 127.0.0.1(49243) COMMIT                 
postgres 30305  0.2  0.6 104936 28116 ?        Ss   18:53   0:00 postgres: abs ap 127.0.0.1(49245) COMMIT                 
postgres 30307  0.2  0.7 104936 29852 ?        Ss   18:53   0:00 postgres: abs ap 127.0.0.1(49247) COMMIT                 
postgres 30308  0.4  0.7 104932 30512 ?        Ss   18:53   0:00 postgres: abs ap 127.0.0.1(49248) INSERT                 
postgres 30309  0.2  0.6 104936 28060 ?        Ss   18:53   0:00 postgres: abs ap 127.0.0.1(49249) COMMIT                 
postgres 30312  0.3  0.7 104932 28440 ?        Ss   18:53   0:00 postgres: abs ap 127.0.0.1(49252) COMMIT                 
postgres 30327  0.3  0.7 104932 29324 ?        Ss   18:53   0:00 postgres: abs ap 127.0.0.1(49267) COMMIT                 
postgres 30328  0.3  0.6 104932 27984 ?        Ss   18:53   0:00 postgres: abs ap 127.0.0.1(49268) COMMIT                 
postgres 30375  0.3  0.7 104936 28912 ?        Ss   18:53   0:00 postgres: abs ap 127.0.0.1(49314) COMMIT                 
postgres 30378  0.3  0.6 104936 27128 ?        Ss   18:53   0:00 postgres: abs ap 127.0.0.1(49317) COMMIT                 
postgres 30381  0.3  0.6 104936 26372 ?        Ss   18:53   0:00 postgres: abs ap 127.0.0.1(49320) COMMIT                 
postgres 30384  0.3  0.6 104932 27720 ?        Ss   18:54   0:00 postgres: abs ap 127.0.0.1(49323) COMMIT                 
postgres 30385  0.2  0.6 104416 26980 ?        Ss   18:54   0:00 postgres: abs ap 127.0.0.1(49324) COMMIT                 
postgres 30386  0.2  0.6 104416 27104 ?        Ss   18:54   0:00 postgres: abs ap 127.0.0.1(49325) INSERT                 
postgres 30387  0.2  0.6 104364 28160 ?        Ss   18:54   0:00 postgres: abs ap 127.0.0.1(49326) COMMIT                 
postgres 30389  0.2  0.6 104416 27496 ?        Ss   18:54   0:00 postgres: abs ap 127.0.0.1(49327) INSERT                 
postgres 30391  0.2  0.6 104396 27688 ?        Ss   18:54   0:00 postgres: abs ap 127.0.0.1(49329) COMMIT                 
postgres 30392  0.2  0.6 104416 28252 ?        Ss   18:54   0:00 postgres: abs ap 127.0.0.1(49330) COMMIT                 
postgres 30393  0.2  0.6 104420 28072 ?        Ss   18:54   0:00 postgres: abs ap 127.0.0.1(49331) COMMIT                 
postgres 30394  0.2  0.6 104416 28156 ?        Ss   18:54   0:00 postgres: abs ap 127.0.0.1(49332) COMMIT                 
postgres 30439  0.2  0.7 104420 28504 ?        Ss   18:54   0:00 postgres: abs ap 127.0.0.1(49377) INSERT                 
postgres 30658  0.0  0.2 104416  8316 ?        Ss   18:55   0:00 postgres: abs ap 127.0.0.1(49596) COMMIT                 
postgres 30896  0.0  0.1 104336  7688 ?        Ss   18:55   0:00 postgres: abs ap 127.0.0.1(49830) COMMIT                 
postgres 30946  0.0  0.1 104220  7272 ?        Ss   18:56   0:00 postgres: abs ap 127.0.0.1(49880) COMMIT                 
postgres 31073  2.5  0.6 104268 25816 ?        Ss   18:56   0:00 postgres: abs ap 127.0.0.1(50006) SELECT                 
1000     31104  0.2  0.0  19296  2068 pts/0    Ss   18:56   0:00 -bash
1000     31134  0.0  0.0  16332  1136 pts/0    R+   18:56   0:00 ps aux
postgres 31135  0.0  0.1 103960  4380 ?        Rs   18:56   0:00 postgres: abs ap 127.0.0.1(50057) startup

It seems like I'm losing a lot of inserts because that many processes are running. Very often I see the message:

psql: FATAL: connection limit exceeded for non-superusers

Any ideas on what might be going wrong? Why are the processes active for so long?

Improve this question
2
  • Do you open a new connection for each and every new INSERT statement? Commented Jan 28, 2013 at 20:04
  • Yes. Can you maybe post a link on how I can group several INSERTs into one transaction using Django? Commented Jan 29, 2013 at 5:31

3 Answers 3

Reset to default
1

It looks like your storage can't process that many transactions. A postgres process with PID 29586 is currently waiting for a device to complete a request (it is in a "D" state, which means "uninterruptable sleep (usually IO)") and is probably blocking other requests. Is it a virtual machine — for example a VPS? They often have not very responsive IO — especially for a lot of fsync requests (every commit generates a fsync in default Postgres configuration).

If you can afford to loose some confirmed transactions in case of a crash then you can make it much faster and less dependent on IO latency by turning off synchronous_commit in postgresql.conf or just for these transactions.

Also this can be made much faster if you can make your writes to run in one transaction instead of many.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

1

Could you put the inserts inside a bulk create?

to_insert = []

for item in items:
    to_insert.append(ObjectModel(item=item))

ObjectModel.objects.bulk_create(to_insert)

It would help if you are creating new connections as mentioned by @cyroxx.

Improve this answer

Comments

0

The Error message

FATAL: connection limit exceeded for non-superusers

is an unambiguous pointer to the setting max_connections

Set a higher limit in postgresql.conf (and restart the postgres server process). Something like:

max_connections = 200

Or what ever you need to satisfy the requests. Be advised that needs some resources. More info in the manual.

Ideally, you would not need a setting that high and spread out those INSERT requests or bundle them into a single (or few) connections. Possibly even into a single INSERT command.

Might also be worth to check if you have autocommit on in Django:

DATABASE_OPTIONS = {
    "autocommit": True,
}

If not, you need to send COMMIT at the end of a transaction.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.