user1302478
Reputation: 21
Too many PostgreSQL processes running (using Django to write to DB)
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?
Upvotes: 2
Views: 738
Answers (3)
Erwin Brandstetter
Reputation: 658472
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.
Upvotes: 0
Nathaniel
Reputation: 696
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.
Upvotes: 1
Tometzky
Reputation: 23910
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.
Upvotes: 1
Related Questions
- Django exceeds maximum Postgres connections
- Django Postgres memory leak
- Django+Postgres FATAL: sorry, too many clients already
- Django multiprocessing database concurrency
- Django PSYCOPG2 db connection
- A lot of requests in PostgreSQL
- PostgreSQL Database Server Unresponsive
- Django/Postgresql memory leak and persistent connections
- Django app freezing with a few concurrent requests
- django long running process database connection