Every once in a while, I read something that's so obvious after the fact that I wonder why I didn't think of it myself. A recent thread on pgsql-performance had that effect. The PostgreSQL documentation gives some rough guidelines for tuning shared_buffers, which recommends (on UNIX-like systems) 25% of system memory up to a maximum of 8GB. There are similar guidelines on the PostgreSQL wiki page, in the article on Tuning Your PostgreSQL Server.
What became obvious to me in reading the thread - and probably should have been obvious to me all along - is that this advice only makes sense if you're running a 64-bit build of PostgreSQL, because, on a 32-bit build, each process is limited to 4GB of address space, of which (at least on Linux) 1GB is reserved for the kernel. That means that no matter how much physical memory the machine has, each PostgreSQL backend will be able to address at most 3GB of data. That includes (most significantly) shared_buffers, but also the process executable text, stack, and heap, including backend-local memory allocations for sorting and hashing, various internal caches that each backend process uses, local buffers for accessing temporary relations, and so on. And 3GB is really a theoretical upper limit, if everything is packed optimally into the available address space: I'm not sure how close you can get to that limit in practice before things start breaking.
So, if you're running a 32-bit PostgreSQL on UNIX-like operating system, you probably need to limit shared_buffers to at most 2GB or 2.5GB. If that is less than about 25% of your system memory, you should seriously consider an upgrade to a 64-bit PostgreSQL.
There's also the option of running a 32-bit userspace with a 64-bit kernel, which at least lets you get close to the full 4GB limit, and is much less inconvenient for an existing system than a full 32 to 64 bit upgrade.
ReplyDeleteHow does postgresql will run on a kernel with pae enabled and with more than 4 G of RAM ? What are the limits of postgresql for this cenario ?
ReplyDeleteI assumed most 32-bit machines had at most 4GB of RAM so would never use more than 25% or 1GB for shared buffers.
ReplyDeleteBruce - I think the most likely scenario where there could be a problem is a 32-bit build of PostgreSQL on a 64-bit OS.
ReplyDeleteMarcos - The extra RAM can be used for OS-level caching, but each process address space will still be limited to 4GB, so the caveats described herein still apply.
ReplyDeleteI have a 32gB, 48 CPU HP Intel Xeon systemm running raid 10 adm.
ReplyDeleteI have installed postgresql-12 on Centos 8. however, querying a 42 column,205m rows take almost 120sec, just "select count(indexed_columns) from allcalls_tables"
i use my postgresql with default settings.
please advise
This seems a little off-topic for this blog post. You might want to try asking your question on one of the mailing lists, such as pgsql-general. Be sure to include relevant information such as the size of the table on disk.
Delete