3 ms·

note that while the MySQL connection-pool equation is pretty different from that of Postgresql's, at least in my experience MySQL connections use less memory (s

by zzzeek 8y ago

note that while the MySQL connection-pool equation is pretty different from that of Postgresql's, at least in my experience MySQL connections use less memory (sorry, no measurements handy, feel free to correct me, but they generally are handled as a thread rather than an entire subprocess and we regularly see apps that spin up thousands of them fairly cheaply) and also have a lower startup time (also anecdotal, sorry), the MySQL world does finally have middleware connection pools now such as proxysql: https://proxysql.com/ https://proxysql.com/

mbell 8y ago
There isn't much practical difference. MySQL's memory cost is lower, largely due to using a thread per connection rather than a process, so you can have a larger number of connections before running out of memory. But this really doesn't matter since by the time you run out of memory with PG or MySQL you'll probably have run into a situation where performance has tanked due to the amount of context switching going on. In both cases you likely want something like `num_cpu_cores * 2..3` connections to the database for optimal performance.
- zzzeek 8y ago
  it is different, because if you have 100 processes all talking to the database, they can safely all have an application-local connection pool holding onto a dozen connections in a pool that are probably largely idle, but are ready in a flash. context switches aren't necessarily a problem because the database probably sees 5-10% of these connection receiving queries at a given moment. Also, mariadb has a thread-pool feature (https://mariadb.com/kb/en/library/thread-pool-in-mariadb/ https://mariadb.com/kb/en/library/thread-pool-in-mariadb/) that allows a significant reduction in threads for a given number of connections. Contrast to Postgresql, where none of the above is an option, every connection is a process and you don't want thousands of those piled up.
anarazel 8y ago
> MySQL connections use less memory [...] handled as a thread I've not measured recently, but from the angle of a PG dev I'd be very surprised if that weren't true. Note that one unfortunatelycannot just compare the memory usage of a individual postgres connection processes with a threaded connection model. Read-only things are mapped multiple times, and a lot of the read-write memory is copy-on-write and never modified after startup. And a good chunk of the required memory is in shared memory and thus not duplicated. I strongly suggest using huge pages (huge_pages=on, and configure the os to allocate them) if memory usage due to connection count is a problem when using PG. The page table is usually one of the biggest users of per-process memory.

by zzzeek 8y ago

mbell 8y ago

There isn't much practical difference. MySQL's memory cost is lower, largely due to using a thread per connection rather than a process, so you can have a larger number of connections before running out of memory. But this really doesn't matter since by the time you run out of memory with PG or MySQL you'll probably have run into a situation where performance has tanked due to the amount of context switching going on. In both cases you likely want something like `num_cpu_cores * 2..3` connections to the database for optimal performance.

zzzeek 8y ago
it is different, because if you have 100 processes all talking to the database, they can safely all have an application-local connection pool holding onto a dozen connections in a pool that are probably largely idle, but are ready in a flash. context switches aren't necessarily a problem because the database probably sees 5-10% of these connection receiving queries at a given moment. Also, mariadb has a thread-pool feature (https://mariadb.com/kb/en/library/thread-pool-in-mariadb/ https://mariadb.com/kb/en/library/thread-pool-in-mariadb/) that allows a significant reduction in threads for a given number of connections. Contrast to Postgresql, where none of the above is an option, every connection is a process and you don't want thousands of those piled up.

anarazel 8y ago

> MySQL connections use less memory [...] handled as a thread I've not measured recently, but from the angle of a PG dev I'd be very surprised if that weren't true. Note that one unfortunatelycannot just compare the memory usage of a individual postgres connection processes with a threaded connection model. Read-only things are mapped multiple times, and a lot of the read-write memory is copy-on-write and never modified after startup. And a good chunk of the required memory is in shared memory and thus not duplicated. I strongly suggest using huge pages (huge_pages=on, and configure the os to allocate them) if memory usage due to connection count is a problem when using PG. The page table is usually one of the biggest users of per-process memory.