8 ms·

How to get the most out of Postgres memory settings

272 points by samaysharma 2y ago

sgarland 2y ago
Aside from these (most of which have reasonably sane defaults with RDS / Aurora), the biggest thing you can do to help the existing RAM you have is to stop making stupid indexing choices. You probably don’t need (or want) an index on every column. You shouldn’t index a UUIDv4 column if you can ever help it (and by extension, you shouldn’t use them as a PK). Finally, you almost certainly need to REINDEX what you have periodically. I’ve seen indices shrink by 400%, giving back GB of RAM. You want a quick win for speed with zero cost? Gain back enough RAM to have your entire working set cached.
- VWWHFSfQ 2y ago
  Should never use uuidv4 (the builtin gen_random_uuid()) for identifiers anyway since it clusters horribly and complete randomness is the absolute enemy of anything you want to "look up". uuid_generate_v1mc() from uuid-ossp is a much better choice if you really want a uuid indentifier since it will at least cluster much better. And yes, absolutely _never_ make it your primary key. I've seen that mistake far too many times and it's always a disaster that is a nightmare to fix once there's a non-trivial amount of data and the database is miserable to work with.
  - robertlagrant 2y ago
    I like it as a primary key because you don't have to worry about clashes if you rejig data. But I don't work at scale with data, so performance hasn't been a blocker for this approach.
    - cuu508 2y ago
      What is rejigging data? Combining tables into one but keeping original PKs?
      - NortySpock 2y ago
        Yes. I found it often necessary at my last job, either due to loading client data from various datasets and staging tables (ideally in an idempotent way to prevent mistakes or double-entries) , or because someone asked "Hey if we already have the data in system a, can we just copy the data to system b?"
    - sgarland 2y ago
      If you're not working at scale, IMO this is also not a difficult problem to overcome with integer IDs. Dump the DB into a CSV and increment the ID column by N, then reload. Or copy the table to a new table, add a trigger to copy from the original to the new with an ID increment, then do `UPDATE <temp_table> SET id = id + N WHERE id <= $MAX_ID_ORIGINALLY_SEEN`.
      - NortySpock 2y ago
        Yeah, but that requires basically a special favor from the DBA to create the trigger for what feels like a basic, common-enough data-load task... What I normally see though is that the auto-incrementing sequence (at least in MS SQL Server or Oracle) isn't clever enough to say "wow, that id already exists on your table somehow? Here let me bump the sequence again and you try again with a higher ID..." Instead you get a 2am alarm because the generated sequence somehow ran into the block of IDs that you inserted into the table and crashed with a PK unique constraint violation. Hence UUIDv7 or ULIDs being easy to insert from the temp table into the main table.
        hot_gril 2y ago
        It's easier if you have a non-PK UUID column you can use as a crutch.
  - adamcharnock 2y ago
    I think that is putting it rather strongly. I use UUID7 (for its better clustering) for anything that both, 1) may end up having a lot of rows (1M+), and 2) where leaking the creation time in the UUID is acceptable. Otherwise I use UUID4.
    - sgarland 2y ago
      > where leaking the creation time in the UUID is acceptable. I've seen tons of people cite this as a problem for various reasons, but not a single person has ever given an actual example in the wild of it causing problems. This isn't war; the German Tank Problem is not nearly as applicable as people think. Worse (from a DB perspective), it's often used as an excuse for poor API design. "I have to have a random ID, because the API is /api/user/<user-id>" and the like. OK, so don't do that? Put the user ID into a JWT (encrypted first if you'd like). Or use an external ID that is random, and an internal ID that's a sequential ID which maps to the external. Use the internal ID as the PK.
      - NortySpock 2y ago
        One year later, in a News Article: "$person_name's lawyer could not be reached for comment, but it is noted that the userid mentioned in the lawsuit was created on 2022-12-03, the same day as the photos were uploaded, and the same day as the arrest warrant was issued..." Using the id directly as the pk admittedly just makes things a bit simpler, one less bit of information to track down. I know bridge tables are not hard, but it's just one extra step that the developer has to be aware of, plan ahead for (and possibly all your teams and customer support people need to be aware of when they are tracking down an issue with an account) UUIDv4 lets you move fast now and pay your performance-piper next year rather than now.
      - hot_gril 2y ago
        PK should be bigserial no matter what. "Use an external ID that is random" has the same clustering problem mentioned above since you will need an index on it, so it's not always the right answer, but I would default to it.
  - patmorgan23 2y ago
    >randomness is the absolute enemy of anything you want to "look up". Absolutely true that having a lot ordered on a column that's one of the search predicates makes look ups faster. But aren't there many situations where the PK is essentially random even if it's an automatically incrementing integer? Like in a customer's table, is the order someone became your customer ever really relevant for most operations? It's essentially a random id, especially if you're only looking up a single customer (or things related to that single customer). Insert performance I could see being an issue. Im not supper familiar with postgres performance tuning, but if the engine handles a last hot page better than insert spread across all pages better (which I think is the case due to how the WAL operates) I can see that being a compelling reason for going with a sequential PK.
    - sgarland 2y ago
      You're correct that for simple one-off cases, in a new-ish table, the performance difference is tiny if you can measure it at all. As tables and indices age, they'll bloat, which can cause additional latency. Again, you may not notice this for small queries, but it can start becoming obvious in more complex queries. The main issue I've seen is that since software and hardware is so absurdly fast, in the beginning none of this is noticed. It isn't until you've had to upsize the hardware multiple times that someone stops to think about examining schema / query performance, and at that point, they're often unwilling to do a large refactor. Then it becomes my problem as a DBRE, and I sigh and explain the technical reasons why UUIDs suck in a DB, and inevitably get told to make it work as best I can. > Insert performance I could see being an issue. And UPDATE, since Postgres doesn't actually do that (it does an INSERT + DELETE).
      - redeeman 2y ago
        https://www.postgresql.org/docs/current/storage-hot.html https://www.postgresql.org/docs/current/storage-hot.html
  - Vadim_samokhin 2y ago
    > And yes, absolutely _never_ make it your primary key. If I dont have lots of range queries, why not then? Only because of bloating (I think fragmentation is a more precise term here)?
    - blackenedgem 2y ago
      Because there's a good chance down the line you will need to do some sort of range query. Let's say you want to add and backill a column. Not too bad, you create a partial index where the column is null and use that for backfilling data. But at a certain scale that starts taking too long and a bigint column would be quicker. Or you decide you need to periodically scan the table in batches for some reason. Perhaps to export the contents to a data warehouse as part of an initial snapshot. You can skip enumerating these possibilities by having a bigint surrogate key from the get go. There's other advantages as well like better joins and temporal locality when the bigint index can be used rather than the uuid.
- djbusby 2y ago
  What's the problem using UUID as a PK? I really like 128bit numbers for PK, more than 64bit-int or anything text. I'm generally putting a ULID or UUIDV7/8 in there.
  - VWWHFSfQ 2y ago
    128-Bit numbers are absolutely humongous and just by virtue of being a primary key means they get duplicated for every single relation. So if you have your user ID, for instance, as a 128bit uuid, any table and column that references the user ID is now also a 128-bit uuid. It's not a problem if you have very small amounts of data. But once your tables start to grow it will become misery to work with. In my opinion, the best pattern is to have just a regular bigserial for primary keys to use for internal database relations since postgres is extremely good at generating and optimizing PK sequences (You're taking away all that optimization just because you want an enormous random number to be the primary key). Then just have a uuid field for application-level identifiers and natural keys. You'll save yourself a whole lot of headache with this pattern.
    - londons_explore 2y ago
      UUID's are for when an auto-incrementing ID can't be used, usually due to scale (if you will be inserting millions of records per second from millions of clients). However, postgres isn't suited to such scale (something like spanner IS). Therefore, you probably shouldn't be using UUID's with postgres. The other reason for UUID's is because the system is badly architected and , for example, the client gets to choose their own ID which are written into a DB. Such systems generally are best avoided.
      - bruce511 2y ago
        Also think about systems that are "beyond one database". Like data that lives in multiple places at the same time (on phone local storage, in database, consolidated and syncd either other databases etc.) In other words once you move beyond '1 sql database' they become important. (I'm not downvoting you, just disagreeing with you.)
- yeswecatan 2y ago
  How do you know when you need to reindex?
  - sgarland 2y ago
    If you have pgstattuple [0], you can check the bloat of indices. Otherwise, you can just make it a cron on a monthly / quarterly / semi-annually / whatever basis. Since PG12 you can do `REINDEX INDEX CONCURRENTLY` with zero downtime, so it really doesn't hurt to do it more often than necessary. Even before PG12, you can do an atomic version of it: `CREATE INDEX new_<index_name> CONCURRENTLY;` `RENAME INDEX <index_name> TO old_<index_name>;` `RENAME INDEX new_<index_name> TO <index_name>;` `DROP INDEX CONCURRENTLY old_<index_name>;` [0]: https://www.postgresql.org/docs/current/pgstattuple.html https://www.postgresql.org/docs/current/pgstattuple.html
    - blackenedgem 2y ago
      I find average leaf density to be the best metric of them all. Most btree indexes with default settings (fill factor 90%) will converge to 67.5% leaf density over time. So anything below that is bloated and a candidate for reindexing.
  - singron 2y ago
    You can measure "bloat" in the index. It's essentially the wasted space in pages. You can also have bloat in the heap for the same reasons. You may also want to cluster if your pg_stat.correlation is low since that indicates your heap isn't in the same order as your index anymore. pg_repack can do all of this without blocking, but you can reindex just an index concurrently on version >= 12. https://wiki.postgresql.org/wiki/Show_database_bloat https://wiki.postgresql.org/wiki/Show_database_bloat
- mnahkies 2y ago
  I've got some queries like this saved which are handy to workout when things are getting bloated and need some maintenance that I find pretty handy https://wiki.postgresql.org/wiki/Show_database_bloat https://wiki.postgresql.org/wiki/Show_database_bloat
  - lomereiter 2y ago
    This is even handier: https://github.com/NikolayS/postgres_dba https://github.com/NikolayS/postgres_dba
- bonesmoses 2y ago
  I'm not against using UUIDs, but it has to be done in a balanced manner. Normal numeric IDs for all surrogates, and then a UUID as an external lookup attribute. You push that externally for API calls, tokens, etc. so you don't have to worry about leaking sequential values, so it should be indexed. Otherwise, it's purely a lookup to key you into the JOIN chain for everything else. That said, Now that UUIDv7 is available, switching to that should be a priority.
rc_mob 2y ago
Well.. that was very detailed. Will bookmark for when I'm ready to optimize.
nextaccountic 2y ago
Why doesn't Postgres autotune those settings? Determining this experimentally seems to be very fragile
- drewcoo 2y ago
  There is a certain appeal for Javaheads. java -Xms$FOO -Xmx$BAR I've worked on things where we had to know the correct heap incantation to run a service.
  - tempest_ 2y ago
    The exact settings for those params is always more
    - Izkata 2y ago
      With a caveat: If you cross 32G heap size, the JVM will start using more and you effectively have less memory available until you get to 48G. https://confluence.atlassian.com/jirakb/do-not-use-heap-sizes-between-32-gb-and-47-gb-in-jira-compressed-oops-1167745277.html https://confluence.atlassian.com/jirakb/do-not-use-heap-size... We had this problem for a while in one of our (non-Atlassian) systems and were running it with Xmx at 31.5G
      - tempest_ 2y ago
        So what your saying is your problem would have been solved if you just increased the number...
limaoscarjuliet 2y ago
Do not forget huge pages configuration in kernel - Postgres will use them and it will save a lot of RAM for large databases, esp with many connections. One of the ways to configure is to continue adding nr_hugepages to kernel, restarting PG and running your workload until you see HugePages_Free growing. E.g.: sysctl.d/10-hugepages.conf:vm.nr_hugepages=20000 See how these are used: [root@potato ~]# cat /proc/meminfo | grep -i huge AnonHugePages: 4411392 kB ShmemHugePages: 0 kB FileHugePages: 0 kB HugePages_Total: 20000 HugePages_Free: 3103 HugePages_Rsvd: 143 HugePages_Surp: 0 Hugepagesize: 2048 kB Hugetlb: 40960000 kB
- tanelpoder 2y ago
  Also, for multiprocess database engines like Postgres (and Oracle), starting up and tearing down connections will be much faster with hugepages too, as the shared memory attach and detach system calls have less pagetable setup work to do in the kernel.
rtp4me 2y ago
For our production PGSQL databases, we use a combination of PGTuner[0] to help estimate RAM requirements and PGHero[1] to get a live view of the running DB. Furthermore, we use ZFS with the built-in compression to save disk space. Together, these three utilities help keep our DBs running very well. [0] https://pgtune.leopard.in.ua https://pgtune.leopard.in.ua [1] https://github.com/ankane/pghero https://github.com/ankane/pghero
- anotherhue 2y ago
  Is the old ZFS block size trick still necessary (since DBs internalise so much that OSs tend to provide)?
  - rtp4me 2y ago
    It is - depending on the read-vs-write workload. For our workload, we landed on a record size (blocksize) of 128K which gives us 3x-5x compression. Contrary to the 8KB/16KB suggestions on the internet, our testing indicated 128K was the best option. And, using compression allows us to run much smaller storage volume sizes in Azure (thus, saving money). We did an exhaustive test of our use-cases, and the best ZFS tuning options with Postgres we found (again, for our workload): * Enable ZFS on-disk compression * Disable ZFS in-memory compression (enabling this option costs us 30% perf penalty) * Enable primary caching * Limit read-ahead caching Edit: Forgot to add, here are the required PGSQL options when using ZFS: * full_page_writes = off * wal_compression = off Once the above options were set, we were getting close to EXT4 read/write speeds with the benefit of compression.
- candiddevmike 2y ago
  Why did you choose to run PG on ZFS? DBs on CoW FS aren't usually ideal.
  - rtp4me 2y ago
    We were running very large storage volumes in Azure (+2TB) and wanted to leverage ZFS compression to save money. After running some performance testing, we landed on a good balance of PGSQL and ZFS options that worked well for us.
mannyv 2y ago
One other sneaky way to do this is to get the parameters from an AWS RDS instance of Postgres that matches your server and use those. It's general-purpose, but it's a good starting point...and probably much better than the default Postgres settings.
tiffanyh 2y ago
I've always found that the config generator below gets you 90% of the optimizations you'll likely needed. https://pgtune.leopard.in.ua https://pgtune.leopard.in.ua
lazyant 2y ago
I find the "pg connections take a lot of memory" to be mostly a myth. The number floating around is 10 - 20MB per connection but it looks it's more like 2MB per connection https://blog.anarazel.de/2020/10/07/measuring-the-memory-overhead-of-a-postgres-connection/ https://blog.anarazel.de/2020/10/07/measuring-the-memory-ove...

272 points by samaysharma 2y ago

Aside from these (most of which have reasonably sane defaults with RDS / Aurora), the biggest thing you can do to help the existing RAM you have is to stop making stupid indexing choices. You probably don’t need (or want) an index on every column. You shouldn’t index a UUIDv4 column if you can ever help it (and by extension, you shouldn’t use them as a PK). Finally, you almost certainly need to REINDEX what you have periodically. I’ve seen indices shrink by 400%, giving back GB of RAM. You want a quick win for speed with zero cost? Gain back enough RAM to have your entire working set cached.

VWWHFSfQ 2y ago
Should never use uuidv4 (the builtin gen_random_uuid()) for identifiers anyway since it clusters horribly and complete randomness is the absolute enemy of anything you want to "look up". uuid_generate_v1mc() from uuid-ossp is a much better choice if you really want a uuid indentifier since it will at least cluster much better. And yes, absolutely _never_ make it your primary key. I've seen that mistake far too many times and it's always a disaster that is a nightmare to fix once there's a non-trivial amount of data and the database is miserable to work with.
- robertlagrant 2y ago
  I like it as a primary key because you don't have to worry about clashes if you rejig data. But I don't work at scale with data, so performance hasn't been a blocker for this approach.
  - cuu508 2y ago
    What is rejigging data? Combining tables into one but keeping original PKs?
    - NortySpock 2y ago
      Yes. I found it often necessary at my last job, either due to loading client data from various datasets and staging tables (ideally in an idempotent way to prevent mistakes or double-entries) , or because someone asked "Hey if we already have the data in system a, can we just copy the data to system b?"
  - sgarland 2y ago
    If you're not working at scale, IMO this is also not a difficult problem to overcome with integer IDs. Dump the DB into a CSV and increment the ID column by N, then reload. Or copy the table to a new table, add a trigger to copy from the original to the new with an ID increment, then do `UPDATE <temp_table> SET id = id + N WHERE id <= $MAX_ID_ORIGINALLY_SEEN`.
    - NortySpock 2y ago
      Yeah, but that requires basically a special favor from the DBA to create the trigger for what feels like a basic, common-enough data-load task... What I normally see though is that the auto-incrementing sequence (at least in MS SQL Server or Oracle) isn't clever enough to say "wow, that id already exists on your table somehow? Here let me bump the sequence again and you try again with a higher ID..." Instead you get a 2am alarm because the generated sequence somehow ran into the block of IDs that you inserted into the table and crashed with a PK unique constraint violation. Hence UUIDv7 or ULIDs being easy to insert from the temp table into the main table.
      - hot_gril 2y ago
        It's easier if you have a non-PK UUID column you can use as a crutch.
- adamcharnock 2y ago
  I think that is putting it rather strongly. I use UUID7 (for its better clustering) for anything that both, 1) may end up having a lot of rows (1M+), and 2) where leaking the creation time in the UUID is acceptable. Otherwise I use UUID4.
  - sgarland 2y ago
    > where leaking the creation time in the UUID is acceptable. I've seen tons of people cite this as a problem for various reasons, but not a single person has ever given an actual example in the wild of it causing problems. This isn't war; the German Tank Problem is not nearly as applicable as people think. Worse (from a DB perspective), it's often used as an excuse for poor API design. "I have to have a random ID, because the API is /api/user/<user-id>" and the like. OK, so don't do that? Put the user ID into a JWT (encrypted first if you'd like). Or use an external ID that is random, and an internal ID that's a sequential ID which maps to the external. Use the internal ID as the PK.
    - NortySpock 2y ago
      One year later, in a News Article: "$person_name's lawyer could not be reached for comment, but it is noted that the userid mentioned in the lawsuit was created on 2022-12-03, the same day as the photos were uploaded, and the same day as the arrest warrant was issued..." Using the id directly as the pk admittedly just makes things a bit simpler, one less bit of information to track down. I know bridge tables are not hard, but it's just one extra step that the developer has to be aware of, plan ahead for (and possibly all your teams and customer support people need to be aware of when they are tracking down an issue with an account) UUIDv4 lets you move fast now and pay your performance-piper next year rather than now.
    - hot_gril 2y ago
      PK should be bigserial no matter what. "Use an external ID that is random" has the same clustering problem mentioned above since you will need an index on it, so it's not always the right answer, but I would default to it.
- patmorgan23 2y ago
  >randomness is the absolute enemy of anything you want to "look up". Absolutely true that having a lot ordered on a column that's one of the search predicates makes look ups faster. But aren't there many situations where the PK is essentially random even if it's an automatically incrementing integer? Like in a customer's table, is the order someone became your customer ever really relevant for most operations? It's essentially a random id, especially if you're only looking up a single customer (or things related to that single customer). Insert performance I could see being an issue. Im not supper familiar with postgres performance tuning, but if the engine handles a last hot page better than insert spread across all pages better (which I think is the case due to how the WAL operates) I can see that being a compelling reason for going with a sequential PK.
  - sgarland 2y ago
    You're correct that for simple one-off cases, in a new-ish table, the performance difference is tiny if you can measure it at all. As tables and indices age, they'll bloat, which can cause additional latency. Again, you may not notice this for small queries, but it can start becoming obvious in more complex queries. The main issue I've seen is that since software and hardware is so absurdly fast, in the beginning none of this is noticed. It isn't until you've had to upsize the hardware multiple times that someone stops to think about examining schema / query performance, and at that point, they're often unwilling to do a large refactor. Then it becomes my problem as a DBRE, and I sigh and explain the technical reasons why UUIDs suck in a DB, and inevitably get told to make it work as best I can. > Insert performance I could see being an issue. And UPDATE, since Postgres doesn't actually do that (it does an INSERT + DELETE).
    - redeeman 2y ago
      https://www.postgresql.org/docs/current/storage-hot.html https://www.postgresql.org/docs/current/storage-hot.html
- Vadim_samokhin 2y ago
  > And yes, absolutely _never_ make it your primary key. If I dont have lots of range queries, why not then? Only because of bloating (I think fragmentation is a more precise term here)?
  - blackenedgem 2y ago
    Because there's a good chance down the line you will need to do some sort of range query. Let's say you want to add and backill a column. Not too bad, you create a partial index where the column is null and use that for backfilling data. But at a certain scale that starts taking too long and a bigint column would be quicker. Or you decide you need to periodically scan the table in batches for some reason. Perhaps to export the contents to a data warehouse as part of an initial snapshot. You can skip enumerating these possibilities by having a bigint surrogate key from the get go. There's other advantages as well like better joins and temporal locality when the bigint index can be used rather than the uuid.
djbusby 2y ago
What's the problem using UUID as a PK? I really like 128bit numbers for PK, more than 64bit-int or anything text. I'm generally putting a ULID or UUIDV7/8 in there.
- VWWHFSfQ 2y ago
  128-Bit numbers are absolutely humongous and just by virtue of being a primary key means they get duplicated for every single relation. So if you have your user ID, for instance, as a 128bit uuid, any table and column that references the user ID is now also a 128-bit uuid. It's not a problem if you have very small amounts of data. But once your tables start to grow it will become misery to work with. In my opinion, the best pattern is to have just a regular bigserial for primary keys to use for internal database relations since postgres is extremely good at generating and optimizing PK sequences (You're taking away all that optimization just because you want an enormous random number to be the primary key). Then just have a uuid field for application-level identifiers and natural keys. You'll save yourself a whole lot of headache with this pattern.
  - londons_explore 2y ago
    UUID's are for when an auto-incrementing ID can't be used, usually due to scale (if you will be inserting millions of records per second from millions of clients). However, postgres isn't suited to such scale (something like spanner IS). Therefore, you probably shouldn't be using UUID's with postgres. The other reason for UUID's is because the system is badly architected and , for example, the client gets to choose their own ID which are written into a DB. Such systems generally are best avoided.
    - bruce511 2y ago
      Also think about systems that are "beyond one database". Like data that lives in multiple places at the same time (on phone local storage, in database, consolidated and syncd either other databases etc.) In other words once you move beyond '1 sql database' they become important. (I'm not downvoting you, just disagreeing with you.)
yeswecatan 2y ago
How do you know when you need to reindex?
- sgarland 2y ago
  If you have pgstattuple [0], you can check the bloat of indices. Otherwise, you can just make it a cron on a monthly / quarterly / semi-annually / whatever basis. Since PG12 you can do `REINDEX INDEX CONCURRENTLY` with zero downtime, so it really doesn't hurt to do it more often than necessary. Even before PG12, you can do an atomic version of it: `CREATE INDEX new_<index_name> CONCURRENTLY;` `RENAME INDEX <index_name> TO old_<index_name>;` `RENAME INDEX new_<index_name> TO <index_name>;` `DROP INDEX CONCURRENTLY old_<index_name>;` [0]: https://www.postgresql.org/docs/current/pgstattuple.html https://www.postgresql.org/docs/current/pgstattuple.html
  - blackenedgem 2y ago
    I find average leaf density to be the best metric of them all. Most btree indexes with default settings (fill factor 90%) will converge to 67.5% leaf density over time. So anything below that is bloated and a candidate for reindexing.
- singron 2y ago
  You can measure "bloat" in the index. It's essentially the wasted space in pages. You can also have bloat in the heap for the same reasons. You may also want to cluster if your pg_stat.correlation is low since that indicates your heap isn't in the same order as your index anymore. pg_repack can do all of this without blocking, but you can reindex just an index concurrently on version >= 12. https://wiki.postgresql.org/wiki/Show_database_bloat https://wiki.postgresql.org/wiki/Show_database_bloat
mnahkies 2y ago
I've got some queries like this saved which are handy to workout when things are getting bloated and need some maintenance that I find pretty handy https://wiki.postgresql.org/wiki/Show_database_bloat https://wiki.postgresql.org/wiki/Show_database_bloat
- lomereiter 2y ago
  This is even handier: https://github.com/NikolayS/postgres_dba https://github.com/NikolayS/postgres_dba
bonesmoses 2y ago
I'm not against using UUIDs, but it has to be done in a balanced manner. Normal numeric IDs for all surrogates, and then a UUID as an external lookup attribute. You push that externally for API calls, tokens, etc. so you don't have to worry about leaking sequential values, so it should be indexed. Otherwise, it's purely a lookup to key you into the JOIN chain for everything else. That said, Now that UUIDv7 is available, switching to that should be a priority.

rc_mob 2y ago

Well.. that was very detailed. Will bookmark for when I'm ready to optimize.

nextaccountic 2y ago

Why doesn't Postgres autotune those settings? Determining this experimentally seems to be very fragile

drewcoo 2y ago
There is a certain appeal for Javaheads. java -Xms$FOO -Xmx$BAR I've worked on things where we had to know the correct heap incantation to run a service.
- tempest_ 2y ago
  The exact settings for those params is always more
  - Izkata 2y ago
    With a caveat: If you cross 32G heap size, the JVM will start using more and you effectively have less memory available until you get to 48G. https://confluence.atlassian.com/jirakb/do-not-use-heap-sizes-between-32-gb-and-47-gb-in-jira-compressed-oops-1167745277.html https://confluence.atlassian.com/jirakb/do-not-use-heap-size... We had this problem for a while in one of our (non-Atlassian) systems and were running it with Xmx at 31.5G
    - tempest_ 2y ago
      So what your saying is your problem would have been solved if you just increased the number...

limaoscarjuliet 2y ago

Do not forget huge pages configuration in kernel - Postgres will use them and it will save a lot of RAM for large databases, esp with many connections. One of the ways to configure is to continue adding nr_hugepages to kernel, restarting PG and running your workload until you see HugePages_Free growing. E.g.: sysctl.d/10-hugepages.conf:vm.nr_hugepages=20000 See how these are used: [root@potato ~]# cat /proc/meminfo | grep -i huge AnonHugePages: 4411392 kB ShmemHugePages: 0 kB FileHugePages: 0 kB HugePages_Total: 20000 HugePages_Free: 3103 HugePages_Rsvd: 143 HugePages_Surp: 0 Hugepagesize: 2048 kB Hugetlb: 40960000 kB

tanelpoder 2y ago
Also, for multiprocess database engines like Postgres (and Oracle), starting up and tearing down connections will be much faster with hugepages too, as the shared memory attach and detach system calls have less pagetable setup work to do in the kernel.

rtp4me 2y ago

For our production PGSQL databases, we use a combination of PGTuner[0] to help estimate RAM requirements and PGHero[1] to get a live view of the running DB. Furthermore, we use ZFS with the built-in compression to save disk space. Together, these three utilities help keep our DBs running very well. [0] https://pgtune.leopard.in.ua https://pgtune.leopard.in.ua [1] https://github.com/ankane/pghero https://github.com/ankane/pghero

anotherhue 2y ago
Is the old ZFS block size trick still necessary (since DBs internalise so much that OSs tend to provide)?
- rtp4me 2y ago
  It is - depending on the read-vs-write workload. For our workload, we landed on a record size (blocksize) of 128K which gives us 3x-5x compression. Contrary to the 8KB/16KB suggestions on the internet, our testing indicated 128K was the best option. And, using compression allows us to run much smaller storage volume sizes in Azure (thus, saving money). We did an exhaustive test of our use-cases, and the best ZFS tuning options with Postgres we found (again, for our workload): * Enable ZFS on-disk compression * Disable ZFS in-memory compression (enabling this option costs us 30% perf penalty) * Enable primary caching * Limit read-ahead caching Edit: Forgot to add, here are the required PGSQL options when using ZFS: * full_page_writes = off * wal_compression = off Once the above options were set, we were getting close to EXT4 read/write speeds with the benefit of compression.
candiddevmike 2y ago
Why did you choose to run PG on ZFS? DBs on CoW FS aren't usually ideal.
- rtp4me 2y ago
  We were running very large storage volumes in Azure (+2TB) and wanted to leverage ZFS compression to save money. After running some performance testing, we landed on a good balance of PGSQL and ZFS options that worked well for us.

mannyv 2y ago

One other sneaky way to do this is to get the parameters from an AWS RDS instance of Postgres that matches your server and use those. It's general-purpose, but it's a good starting point...and probably much better than the default Postgres settings.

tiffanyh 2y ago

I've always found that the config generator below gets you 90% of the optimizations you'll likely needed. https://pgtune.leopard.in.ua https://pgtune.leopard.in.ua

lazyant 2y ago

I find the "pg connections take a lot of memory" to be mostly a myth. The number floating around is 10 - 20MB per connection but it looks it's more like 2MB per connection https://blog.anarazel.de/2020/10/07/measuring-the-memory-overhead-of-a-postgres-connection/ https://blog.anarazel.de/2020/10/07/measuring-the-memory-ove...