5

InnoDB provides an innodb_thread_concurrency option to limit the number of concurrent storage engine operations MySQL can perform at a time. Seems like InnoDB controls (schedules) execution of its thread.

Does PostgreSQL provide any configuration that allow us to limit the number of concurrent operations performed at a time? Does postgresql controls execution of its child processes or does it delegate this task to the OS?

4

"Does postgresql controls execution of its child processes...?" Sure it does. The postmaster process is the father of all other processes and spawns many of them depending on the needs:

  • archiver;
  • autovacuum launcher;
  • autovacuum worker;
  • checkpointer;
  • logger;
  • postgres (here are the users connections);
  • stats collector;
  • wal writer;
  • wal sender;
  • writer.

and periodicaly checks their status. If some of them got killed and the postmaster thinks it could lead to disk/memory corruption (for example), it could even shut down itself in order to enter recovery mode and take good care of your data.

As far as I know, there is no hard limit on the number of concurrent processes you can have like in MySQL, but this could be tuned at a finer level, for example, you could adjust:

  • max_wal_senders
  • autovacuum_max_workers
  • number of connections: per cluster, per database, per user, ...

When PostgreSQL needs to do some inner work - like autovacuuming - it makes sure it has as little impact on the users as possible. This is configurable of course on many, many levels (even if very few of such constants are hard-coded).

4

PostgreSQL doesn't control execution of its child processes explicitly. If I borrow your words, PostgreSQL delegates that task to the OS.

PostgreSQL allows multiple clients to connect simultaneously. Each client connects a child process (referred to as a backend process), and each running backend process handles all queries issued by the connected client. It is a very simple and classical server-client type multi-process architecture.

(I add more information.)

PostgreSQL can control the maximum number of connected clients by the configuration parameter max_connections (in the same way as MySQL), but does not control the activities of the running backend processes depending on the server activity.

In addition, some helper processes, such as autovacuum related processes and checkpointer, can be controlled their activities by themselves depending on the server activity.

2
  • Thank you. So if there is too many backend process (max_connections >1000) and Each backend process process doing heavy write and heavy read , Does it too slow(hang) system, because of too many heavy process ? So i should be more cautious to increase max_connections in PostgreSQL.
    – sudalai
    Feb 22 '16 at 7:05
  • It depends on the situation: server activity, query type, OS, hardware, etc. (Usually I don't like to say any solutions without any reasons, but) I think over 1000 connections are too large, generally speaking.
    – shx
    Feb 22 '16 at 7:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.