Re: Out of Memory
On Tue, Sep 25, 2018 at 2:05 PM PT wrote: > On Tue, 25 Sep 2018 11:34:19 -0700 (MST) > greigwise wrote: > > > Well, I've been unsuccessful so far on creating a standalone test. > > > > I have put some scripting in place to capture some additional > information on > > the server with the out of memory issues. I have a script which just > > periodically dumps the output of free -m to a text file. > > > > So, the output of free -m immediately before and after the out of memory > > error looks like this: > > > > Just before: > > totalusedfree shared buff/cache > > available > > Mem: 148772978 1323553 11766 > > > 7943 > > Swap: 0 0 0 > > > > Just after: > > totalusedfree shared buff/cache > > available > > Mem: 148772946 6493548 11280 > > > 7982 > > Swap: 0 0 0 > > > > If I have nearly 8 GB of memory left, why am I getting out of memory > errors? > > Doesn't the default NUMA setup mean that it can't actually allocate all > the available memory to a single NUMA zone (or whatever it's called)? > > Or am I talking ancient Linux history with that? > > -- > Bill Moran > > By the way we have similar issues running in our smaller stage environment. 9.5.6 on CentOS 7.2 Only ever my stage environment on smaller AWS t2 boxen. Memory looks fine but Postgres says it has no access to any. And my settings are really low. Not sure if it's the same issue, but we run into it a few times a year in my stage environment. Also running postgres in unlimited mode for ulimit. Tory
pg_upgrade custom table locations. Move table locations during upgrade?
Many decades ago (small fib). There was a write up of someone stopping the postgres upgrade at a certain point, editing a file with the table locations and then restarting the upgrade process. "Now, what are you trying to do?" I have version specific directories (good for you), but... /pgsql/9.5/tablespaces I am updating to 12 and thus /pgsql/12/tablespaces (is where I would like them). Using the -link, it simples creates the PG12, files under /pgsql/9.5/tablespace I'd like to , even when using link, to say don't do that, but instead please link the Ver 12 tables to /pgsql/12/tablespaces. The information is in the file pg_upgrade_dump_globals.sql:CREATE TABLESPACE "torquespace" OWNER "cls" LOCATION '/pgsql/9.5/torque'; but while it's there, I'd like it not to be there (and it's possible that I'm running into an initial design flaw and the table spaces should really be under /pgsql and not /pgsql/$VERSION/ However is there a way to say create the new links under /pgsql/12/ vs /pgsql/9.5/ using the pg_upgrade process? Thanks Tory
Re: pg_upgrade custom table locations. Move table locations during upgrade?
Finally found it http://bajis-postgres.blogspot.com/2014/04/anyone-wants-to-change-tablespaces.html Thanks Tory On Thu, Feb 27, 2020 at 12:40 PM Tory M Blue wrote: > Many decades ago (small fib). > > There was a write up of someone stopping the postgres upgrade at a certain > point, editing a file with the table locations and then restarting the > upgrade process. > > "Now, what are you trying to do?" > > I have version specific directories (good for you), but... > > /pgsql/9.5/tablespaces > > I am updating to 12 and thus > > /pgsql/12/tablespaces (is where I would like them). > > Using the -link, it simples creates the PG12, files under > /pgsql/9.5/tablespace > > I'd like to , even when using link, to say don't do that, but instead > please link the Ver 12 tables to /pgsql/12/tablespaces. > > The information is in the file > > pg_upgrade_dump_globals.sql:CREATE TABLESPACE "torquespace" OWNER "cls" > LOCATION '/pgsql/9.5/torque'; > > but while it's there, I'd like it not to be there (and it's possible that > I'm running into an initial design flaw and the table spaces should really > be under /pgsql and not /pgsql/$VERSION/ > > However is there a way to say create the new links under /pgsql/12/ vs > /pgsql/9.5/ using the pg_upgrade process? > > Thanks > Tory >
Odd postgres12 upgrade is changing or munging a password?
Going from 9.5 to 12 and 2 times now, I've had a password either go missing or munged. I've had to add an alter statement at the end of the upgrade. The DB is functioning fine, shut it down, do the upgrade and the password is munged. Seems like an odd occurrence, we have not noted any other weird issues. Anyone else see or hear of this? Thanks Tory
Re: Odd postgres12 upgrade is changing or munging a password?
Yes same password, I'm using a basic alter command to put the right password back. I'm doing another upgrade in an hour, and will do some more checks to see if it's trying to use another password or what. I obviously can't read the password from the file , so knowing if it's munged or other, I'm not sure is possible. Upgrade command i'm running time /usr/pgsql-12/bin/pg_upgrade --old-bindir /usr/pgsql-9.5/bin/ --new-bindir /usr/pgsql-12/bin/ --old-datadir /pgsql/9.5/data --new-datadir /pgsql/12/data --link So it's very odd. and I've not experienced this in other environments, it's just this one. Now it's a bigger data set, but very odd. I'm also not seeing any other data issues, just seems to be this one password. Thanks, If there are commands I can run on the data before I do an alter, to give someone more info, let me know Tory On Thu, May 7, 2020 at 12:08 PM Adrian Klaver wrote: > On 5/7/20 11:55 AM, Tory M Blue wrote: > > Going from 9.5 to 12 and 2 times now, I've had a password either go > > missing or munged. I've had to add an alter statement at the end of the > > upgrade. > > What are the commands you are using? > > Is it the same password? > > > > > The DB is functioning fine, shut it down, do the upgrade and the > > password is munged. Seems like an odd occurrence, we have not noted any > > other weird issues. > > > > Anyone else see or hear of this? > > > > Thanks > > Tory > > > -- > Adrian Klaver > adrian.kla...@aklaver.com >
Memory footprint diff between 9.5 and 12
I hadn't noticed this until today, but a running 9.5 system with buffers at 10GB starts and has been running years without issues. (15GB available) Postgres 12 will not start with that configuration, complaining about memory availability. So Postgres12 won't start until shared buffers is 6GB, but even with that, my DB servers , postgres queries started complaining about being unable to allocate memory "unable to allocate". So dropping them to 4GB (on a 15GB system), may help, but did I miss a huge note about significant memory changes between 9.5 to 12? Is there something else I'm missing that on busy systems is important, something introduced in 10 or 11 as again I'm not seeing anything noted in 12. Thanks Tory
Re: Odd postgres12 upgrade is changing or munging a password?
On Thu, May 7, 2020 at 12:32 PM Adrian Klaver wrote: > On 5/7/20 12:24 PM, Tory M Blue wrote: > > Yes same password, I'm using a basic alter command to put the right > > password back. > > > > I'm doing another upgrade in an hour, and will do some more checks to > > see if it's trying to use another password or what. I obviously can't > > read the password from the file , so knowing if it's munged or other, > > I'm not sure is possible. > > > > Upgrade command i'm running > > > > time /usr/pgsql-12/bin/pg_upgrade --old-bindir /usr/pgsql-9.5/bin/ > > --new-bindir /usr/pgsql-12/bin/ --old-datadir /pgsql/9.5/data > > --new-datadir /pgsql/12/data --link > > > > So it's very odd. and I've not experienced this in other environments, > > it's just this one. Now it's a bigger data set, but very odd. > > Anything different about this environment e.g. locale? > > What is the encoding/character set for the database? > > > > > I'm also not seeing any other data issues, just seems to be this one > > password. > > I'm assuming you have super user access so you could look at the > password in: > > https://www.postgresql.org/docs/12/view-pg-shadow.html > > on the old server and then on the new server. > > > It absolutely did change the password. Only 1 password out of 4 accounts, but it changed it. The MD5 is different so this is verified. But why, how? Tory
Re: Odd postgres12 upgrade is changing or munging a password?
On Thu, May 7, 2020 at 11:41 PM Laurenz Albe wrote: > On Thu, 2020-05-07 at 11:55 -0700, Tory M Blue wrote: > > Going from 9.5 to 12 and 2 times now, I've had a password either go > missing or munged. > > I've had to add an alter statement at the end of the upgrade. > > > > The DB is functioning fine, shut it down, do the upgrade and the > password is munged. > > Seems like an odd occurrence, we have not noted any other weird issues. > > > > Anyone else see or hear of this? > > The only explanation I can come up with is that "password_encryption" is > set to > "scram-sha-256" on the v12 server. > > Yours, > Laurenz Albe > -- > Cybertec | https://www.cybertec-postgresql.com > > If that was the case, wouldn't it change the others as well? Why a single password. Super strange indeed :) Tory
Re: Memory footprint diff between 9.5 and 12
On Thu, May 7, 2020 at 11:39 PM Laurenz Albe wrote: > On Thu, 2020-05-07 at 13:33 -0700, Tory M Blue wrote: > > I hadn't noticed this until today, but a running 9.5 system with buffers > at 10GB starts > > and has been running years without issues. (15GB available) > > > > Postgres 12 will not start with that configuration, complaining about > memory availability. > > So Postgres12 won't start until shared buffers is 6GB, but even with > that, my DB servers , > > postgres queries started complaining about being unable to allocate > memory "unable to allocate". > > > > So dropping them to 4GB (on a 15GB system), may help, but did I miss a > huge note about > > significant memory changes between 9.5 to 12? > > > > Is there something else I'm missing that on busy systems is important, > something introduced > > in 10 or 11 as again I'm not seeing anything noted in 12. > > There must be something else running on the machine that allocates memory. > > Did you perchance run the 9.5 and the v12 server on the same machine? > > Yours, > Laurenz Albe > -- > > I guess the one thing I can come up with, is that my older config has commands that are no longer valid or have been changed. or the defaults that I'm not overwriting have changed significantly. I'm using the stock postgresql.conf file (not edited, other than to add the include at the bottom for my config file). the include file is our local config and same one I've been using between 9.5 and 12, and 9.5 will start with shared buffers of 10GB but 12 will not. This box has 15GB of available memory. listen_addresses = '*' # max_connections = 300 # log_destination 'stderr' # log_directory = '/pgsql/logs' # logging_collector = on # log_filename = 'pgsql-%m-%d.log' # log file name pattern, # log_min_duration_statement = 80ms # -1 is disabled, 0 logs all statements # log_lock_waits = on # log lock waits >= deadlock_timeout # log_timezone = 'US/Pacific' # autovacuum_max_workers = 3 # max number of autovacuum subprocesses # autovacuum_vacuum_threshold = 1 # min number of row updates before # autovacuum_analyze_threshold = 3000 # min number of row updates before # timezone = 'US/Pacific' # deadlock_timeout = 2s # autovacuum_work_mem = -1# min 1MB, or -1 to use # max_stack_depth = 2MB # min 100kB # dynamic_shared_memory_type = posix # the default is the first option # shared_buffers = 5GB # effective_cache_size = 10GB # work_mem = 256MB # maintenance_work_mem = 256MB # # min_wal_size = 100MB # # max_wal_size = 2GB # checkpoint_completion_target = 0.9 # wal_buffers = 16MB # default_statistics_target = 100 # default_text_search_config = 'pg_catalog.simple' # synchronous_commit = off # log_line_prefix = '< %m %h >'
Is there a significant difference in Memory settings between 9.5 and 12
Upgraded from 9.5 to 12 and 12 would not start with the current configured Shared Buffers. Same hardware, same config file. Which tells me something has changed, are there new default settings in the 12 postgresql.conf file that are not being called out in my 9.5 config file that could be the cause or? 9.5 shared_buffers 10GB 12 shared_buffers 5GB 12 will not start at 10GB, even though it's the same hardware, same config file, same physical box, same everything, just version 12 vs 9.5 So this tells me that maybe i'm missing a new memory setting in 12 , that is not being overwritten in my local config file (I run an include and my own settings), this worked fine in 9.5 but I'm guessing there is something considerably different between 9.5 and 12 (I'm just not seeing it). Anyone with insight into what memory settings may have been added in 10/11/12 that are significantly different than 9.5? Thanks Tory CentOS 7 Postgresql 9.5.x Postgresql 12.2
Re: Is there a significant difference in Memory settings between 9.5 and 12
On Mon, May 11, 2020 at 1:36 PM Tom Lane wrote: > Tory M Blue writes: > > 12 will not start at 10GB, even though it's the same hardware, same > config > > file, same physical box, same everything, just version 12 vs 9.5 > > For me, using all-default settings (in particular, shared_buffers = > 128MB), the shared memory block is about 141.6MB using 9.5 and 142.1MB > using 12. So there's half a meg or so of additional data in v12, but > certainly not gigabytes worth. > > Are you trying to start both postmasters concurrently? Maybe you're > hitting some kernel limit on the total amount of shared memory in the > system. > > regards, tom lane > Hey Tom Nope, just a single one that is why i'm flummoxed :) I've even rebooted, but I can't start Postgres 12 with my current setting of 10GB, I can start 9.5 with 10GB configured. I've tried, shutting down 9.5 and rebooting so nothing is running and attempting to start 12 and nada, it won't unless I drop the Shared Buffers down to 5GB (half).. But these are dedicated postgresql servers. And in fact my latest migrations, don't even have 9.5 binaries anymore and 12 will not start with my 9.5 configuration of 10GB buffers. So something feels really different. It's very possible that there are new defaults , new memory settings that I'm not finding in the default postgresql 12 .conf file, and my include is not overwriting it. But really I just can't fathom what that could be.. Buffers, work mem, effective cache, what would they have added? I am going to pull the settings from postgres itself and compare 12 and 9.5 to see if there is something glaring. Thanks! :) Tory
Re: Is there a significant difference in Memory settings between 9.5 and 12
On Mon, May 11, 2020 at 1:36 PM Tom Lane wrote: > Tory M Blue writes: > > 12 will not start at 10GB, even though it's the same hardware, same > config > > file, same physical box, same everything, just version 12 vs 9.5 > > For me, using all-default settings (in particular, shared_buffers = > 128MB), the shared memory block is about 141.6MB using 9.5 and 142.1MB > using 12. So there's half a meg or so of additional data in v12, but > certainly not gigabytes worth. > > Are you trying to start both postmasters concurrently? Maybe you're > hitting some kernel limit on the total amount of shared memory in the > system. > > regards, tom lane > Okay the one difference I see in settings is this little gem in 12.. shared_memory_type mmap Otherwise i'm not seeing a ton of other settings not common between them,. This is the only major difference I'm seeing, as it's really not an option in 9.5. Appears 9.5 was using shared_memory_type (enum) Specifies the shared memory implementation that the server should use for the main shared memory region that holds PostgreSQL's shared buffers and other shared data. Possible values are mmap (for anonymous shared memory allocated using mmap), sysv (for System V shared memory allocated via shmget) and windows (for Windows shared memory). Not all values are supported on all platforms; the first supported option is the default for that platform. The use of the sysv option, which is not the default on any platform, is generally discouraged because it typically requires non-default kernel settings to allow for large allocations (see Section 18.4.1 <https://www.postgresql.org/docs/12/kernel-resources.html#SYSVIPC>).
Re: Is there a significant difference in Memory settings between 9.5 and 12
On Mon, May 11, 2020 at 2:08 PM Tory M Blue wrote: > > > On Mon, May 11, 2020 at 1:36 PM Tom Lane wrote: > >> Tory M Blue writes: >> > 12 will not start at 10GB, even though it's the same hardware, same >> config >> > file, same physical box, same everything, just version 12 vs 9.5 >> >> For me, using all-default settings (in particular, shared_buffers = >> 128MB), the shared memory block is about 141.6MB using 9.5 and 142.1MB >> using 12. So there's half a meg or so of additional data in v12, but >> certainly not gigabytes worth. >> >> Are you trying to start both postmasters concurrently? Maybe you're >> hitting some kernel limit on the total amount of shared memory in the >> system. >> >> regards, tom lane >> > > Okay the one difference I see in settings is this little gem in 12.. > > shared_memory_type mmap > > Otherwise i'm not seeing a ton of other settings not common between them,. > > This is the only major difference I'm seeing, as it's really not an option > in 9.5. Appears 9.5 was using > > shared_memory_type (enum) > > Specifies the shared memory implementation that the server should use for > the main shared memory region that holds PostgreSQL's shared buffers and > other shared data. Possible values are mmap (for anonymous shared memory > allocated using mmap), sysv (for System V shared memory allocated via > shmget) and windows (for Windows shared memory). Not all values are > supported on all platforms; the first supported option is the default for > that platform. The use of the sysv option, which is not the default on > any platform, is generally discouraged because it typically requires > non-default kernel settings to allow for large allocations (see > Section 18.4.1 > <https://www.postgresql.org/docs/12/kernel-resources.html#SYSVIPC>). > > That didn't help. ay 11 19:46:13 qdb03.prod.ca. postmaster[31048]: < 2020-05-11 19:46:13.026 PDT >FATAL: could not create shared memory segment: Cannot allocate memory May 11 19:46:13 qdb03.prod.ca postmaster[31048]: < 2020-05-11 19:46:13.026 PDT >DETAIL: Failed system call was shmget(key=5432001, size=11026235392, 03600). May 11 19:46:13 qdb03.prod.ca postmaster[31048]: < 2020-05-11 19:46:13.026 PDT >HINT: This error usually means that PostgreSQL's request for a shared memory segm May 11 19:46:13 qdb03.prod.ca postmaster[31048]: The PostgreSQL documentation contains more information about shared memory configuration. Attempted to change ; #shared_memory_type = 'sysv' It took the change but didn't help. So 10GB of shared_buffers in 12 is still a no go. I'm down to 5GB and it works, but this is the same hardware, the same exact 9.5 configuration. So I'm missing something. WE have not had to mess with kernel memory settings since 9.4, so this is an odd one. I'll keep digging, but i'm hesitant to do my multiple TB db's with half of their shared buffer configs, until I understand what 12 is doing differently than 9.5 Thanks again for the ideas Tory
Re: Is there a significant difference in Memory settings between 9.5 and 12
On Mon, May 11, 2020 at 7:57 PM David G. Johnston < david.g.johns...@gmail.com> wrote: > On Monday, May 11, 2020, Tory M Blue wrote: > >> I'll keep digging, but i'm hesitant to do my multiple TB db's with half >>>> of their shared buffer configs, until I understand what 12 is doing >>>> differently than 9.5 >>> >>> > Maybe run your test suite on 9.6, 10, and 11 to see if it is indeed new to > 12 or at least appears on other versions? > > David J. > That may be the next step in the lab, but was hoping someone knew of a significant difference. Thanks Tory
Re: Is there a significant difference in Memory settings between 9.5 and 12
On Mon, May 11, 2020 at 9:57 PM Tom Lane wrote: > Tory M Blue writes: > > That may be the next step in the lab, but was hoping someone knew of a > > significant difference. > > I think we've made it perfectly clear that we don't. There's something > odd about your situation. > > regards, tom lane > totally, and i'll try to provide some more data tomorrow. Thanks everyone. Tory
Re: Is there a significant difference in Memory settings between 9.5 and 12
On Mon, May 11, 2020 at 9:01 PM Thomas Munro wrote:
> On Tue, May 12, 2020 at 2:52 PM Tory M Blue wrote:
> > It took the change but didn't help. So 10GB of shared_buffers in 12 is
> still a no go. I'm down to 5GB and it works, but this is the same hardware,
> the same exact 9.5 configuration. So I'm missing something. WE have not had
> to mess with kernel memory settings since 9.4, so this is an odd one.
> >
> > I'll keep digging, but i'm hesitant to do my multiple TB db's with half
> of their shared buffer configs, until I understand what 12 is doing
> differently than 9.5
>
> Which exact version of 9.5.x are you coming from? What's the exact
> error message on 12 (you showed the shared_memory_type=sysv error, but
> with the default value (mmap) how does it look)? What's your
> huge_pages setting?
>
9.5-20
postgresql95-9.5.20-2PGDG.rhel7.x86_64
postgresql95-contrib-9.5.20-2PGDG.rhel7.x86_64
postgresql95-libs-9.5.20-2PGDG.rhel7.x86_64
postgresql95-server-9.5.20-2PGDG.rhel7.x86_64
I don't use huge_pages
And this error is actually from the default mmap
May 08 12:33:58 qdb01.prod.ca postmaster[8790]: < 2020-05-08 12:33:58.324
PDT >HINT: This error usually means that PostgreSQL's request for a
shared memory segment exceeded available memory, swap space, or huge pages.
To reduce the request size (currently 11026235392 bytes), reduce
PostgreSQL's shared memory usage, perhaps by reducing shared_buffers or
max_connections.
The above error is with 12 trying to start with shared_buffers = 10GB...
9.5 starts fine with the same configuration file. That kind of started me
down this path.
And just to repeat. Same exact hardware, same kernel, nothing more than
installing the latest postgres12, copying my config files from 9.5 to 12
and running the pg_upgrade.
9.5 has been running for years with the same configuration file, so
something changed somewhere along the line that is preventing 12 to start
with the same config file. And the allocation error is with either the
sysv or mman on 12. (will start with 5GB allocated, but not 10GB, on a 15GB
box (dedicated postgres server).
> Can you reproduce the problem with a freshly created test cluster? As
> a regular user, assuming regular RHEL packaging, something like
> /usr/pgsql-12/bin/initdb -D test_pgdata, and then
> /usr/pgsql-12/bin/postgres -D test_pgdata -c shared_buffers=10GB (then
> ^C to stop it). If that fails to start in the same way, it'd be
> interesting to see the output of the second command with strace in
> front of it, in the part where it allocates shared memory. And
> perhaps it'd be interesting to see the same output with
> /usr/pgsql-9.5/bin/XXX (if you still have the packages). For example,
> on my random dev laptop that looks like:
>
> openat(AT_FDCWD, "/proc/meminfo", O_RDONLY) = 6
> fstat(6, {st_mode=S_IFREG|0444, st_size=0, ...}) = 0
> read(6, "MemTotal: 16178852 kB\nMemF"..., 1024) = 1024
> read(6, ":903168 kB\nShmemHugePages: "..., 1024) = 311
> close(6)= 0
> mmap(NULL, 11016339456, PROT_READ|PROT_WRITE,
> MAP_SHARED|MAP_ANONYMOUS|MAP_HUGETLB, -1, 0) = -1 ENOMEM (Cannot
> allocate memory)
> mmap(NULL, 11016003584, PROT_READ|PROT_WRITE,
> MAP_SHARED|MAP_ANONYMOUS, -1, 0) = 0x7ff74e579000
> shmget(0x52e2c1, 56, IPC_CREAT|IPC_EXCL|0600) = 3244038
> shmat(3244038, NULL, 0) = 0x7ff9df5ad000
>
> The output is about the same on REL9_5_STABLE and REL_12_STABLE for
> me, only slightly different sizes. If that doesn't fail in the same
> way on your system with 12, perhaps there are some more settings from
> your real clusters required to make it fail. You could add them one
> by one with -c foo=bar or in the throw away
> test_pgdata/postgresql.conf, and perhaps that process might shed some
> light?
>
> I was going to ask if it might be a preloaded extension that is asking
> for gobs of extra memory in 12, but we can see from your "Failed
> system call was shmget(key=5432001, size=11026235392, 03600)" that
> it's in the same ballpark as my total above for shared_buffers=10GB.
>
Be more than happy to test this out. I'll see what I can pull tomorrow and
provide some dataz :) I know it's not ideal to use the same config file,
I know that various things are added or changed (usually added) but the
defaults are typically safe. But after sometime dialing in the settings for
our use case, I've just kind of kept moving them forward.
But let me do some more testing tomorrow (since I'm trying to get to the
bottom of this, before I attempt my big DB upgrades). So I'll spend some
time testing and see if I can't get similar "failures/challenges"? and go
from there.
Appreciate the ideas!
Tory
Re: Is there a significant difference in Memory settings between 9.5 and 12
On Mon, May 11, 2020 at 11:09 PM David G. Johnston < david.g.johns...@gmail.com> wrote: > On Monday, May 11, 2020, David G. Johnston > wrote: > >> Repost, edited subject by mistake... >> >> On Monday, May 11, 2020, Tory M Blue wrote: >>> >>> And just to repeat. Same exact hardware, same kernel, nothing more than >>> installing the latest postgres12, copying my config files from 9.5 to 12 >>> and running the pg_upgrade. >>> >> >> You’ll want to remove the pg_upgrade from the equation and try v12 >> > > Sorry...if you copied the config to v12 before the upgrade and the upgrade > worked that suggests that v12 booted up at some point with the > configuration, no? Does pg_upgrade do something special? > > David J > Not entirely sure I follow, and it may be that I confused the issue. 9.5 running for years, run the upgrade, and migrate my config files. 12 won't start without bumping the shared_buffers down. 12 won't start with "my" original config files. I'm going to do native 9.5 and 12 installs on the same piece of hardware, no data migration, no pg_upgrade just to see if I can get 12 to start with my current configuration. I'll try your suggestions from last night as well, see if setting via command line will give us more dataz. Tory > >
Re: Is there a significant difference in Memory settings between 9.5 and 12
On Mon, May 11, 2020 at 10:55 PM Tory M Blue wrote:
>
>
> On Mon, May 11, 2020 at 9:01 PM Thomas Munro
> wrote:
>
>> On Tue, May 12, 2020 at 2:52 PM Tory M Blue wrote:
>> > It took the change but didn't help. So 10GB of shared_buffers in 12 is
>> still a no go. I'm down to 5GB and it works, but this is the same hardware,
>> the same exact 9.5 configuration. So I'm missing something. WE have not had
>> to mess with kernel memory settings since 9.4, so this is an odd one.
>> >
>> > I'll keep digging, but i'm hesitant to do my multiple TB db's with half
>> of their shared buffer configs, until I understand what 12 is doing
>> differently than 9.5
>>
>> Which exact version of 9.5.x are you coming from? What's the exact
>> error message on 12 (you showed the shared_memory_type=sysv error, but
>> with the default value (mmap) how does it look)? What's your
>> huge_pages setting?
>>
>
> 9.5-20
> postgresql95-9.5.20-2PGDG.rhel7.x86_64
> postgresql95-contrib-9.5.20-2PGDG.rhel7.x86_64
> postgresql95-libs-9.5.20-2PGDG.rhel7.x86_64
> postgresql95-server-9.5.20-2PGDG.rhel7.x86_64
>
> I don't use huge_pages
>
> And this error is actually from the default mmap
>
> May 08 12:33:58 qdb01.prod.ca postmaster[8790]: < 2020-05-08 12:33:58.324
> PDT >HINT: This error usually means that PostgreSQL's request for a
> shared memory segment exceeded available memory, swap space, or huge pages.
> To reduce the request size (currently 11026235392 bytes), reduce
> PostgreSQL's shared memory usage, perhaps by reducing shared_buffers or
> max_connections.
>
> The above error is with 12 trying to start with shared_buffers = 10GB...
>
> 9.5 starts fine with the same configuration file. That kind of started
> me down this path.
>
> And just to repeat. Same exact hardware, same kernel, nothing more than
> installing the latest postgres12, copying my config files from 9.5 to 12
> and running the pg_upgrade.
>
> 9.5 has been running for years with the same configuration file, so
> something changed somewhere along the line that is preventing 12 to start
> with the same config file. And the allocation error is with either the
> sysv or mman on 12. (will start with 5GB allocated, but not 10GB, on a 15GB
> box (dedicated postgres server).
>
>
>> Can you reproduce the problem with a freshly created test cluster? As
>> a regular user, assuming regular RHEL packaging, something like
>> /usr/pgsql-12/bin/initdb -D test_pgdata, and then
>> /usr/pgsql-12/bin/postgres -D test_pgdata -c shared_buffers=10GB (then
>> ^C to stop it). If that fails to start in the same way, it'd be
>> interesting to see the output of the second command with strace in
>> front of it, in the part where it allocates shared memory. And
>> perhaps it'd be interesting to see the same output with
>> /usr/pgsql-9.5/bin/XXX (if you still have the packages). For example,
>> on my random dev laptop that looks like:
>>
>> openat(AT_FDCWD, "/proc/meminfo", O_RDONLY) = 6
>> fstat(6, {st_mode=S_IFREG|0444, st_size=0, ...}) = 0
>> read(6, "MemTotal: 16178852 kB\nMemF"..., 1024) = 1024
>> read(6, ":903168 kB\nShmemHugePages: "..., 1024) = 311
>> close(6)= 0
>> mmap(NULL, 11016339456, PROT_READ|PROT_WRITE,
>> MAP_SHARED|MAP_ANONYMOUS|MAP_HUGETLB, -1, 0) = -1 ENOMEM (Cannot
>> allocate memory)
>> mmap(NULL, 11016003584, PROT_READ|PROT_WRITE,
>> MAP_SHARED|MAP_ANONYMOUS, -1, 0) = 0x7ff74e579000
>> shmget(0x52e2c1, 56, IPC_CREAT|IPC_EXCL|0600) = 3244038
>> shmat(3244038, NULL, 0) = 0x7ff9df5ad000
>>
>> The output is about the same on REL9_5_STABLE and REL_12_STABLE for
>> me, only slightly different sizes. If that doesn't fail in the same
>> way on your system with 12, perhaps there are some more settings from
>> your real clusters required to make it fail. You could add them one
>> by one with -c foo=bar or in the throw away
>> test_pgdata/postgresql.conf, and perhaps that process might shed some
>> light?
>>
>> I was going to ask if it might be a preloaded extension that is asking
>> for gobs of extra memory in 12, but we can see from your "Failed
>> system call was shmget(key=5432001, size=11026235392, 03600)" that
>> it's in the same ballpark as my total above for shared_buffers=10GB.
>>
>
> Be more than happy to test this out. I'll see what I can pull tomorrow and
> provide some dataz :) I know it's not ideal to use the same config file,
> I
Huge tables, trying to delete OID's taking 6+hours per table
The db is only 2TB, these tables are about 50% of the DB. Just wondering what I can boost to give me some elevated temp performance for what I would think would be a fairly quick operation. Give this query more work_mem? Mine are set pretty low, based on previous performance notes, this is a 9.5 server. Thanks Tory
Re: Huge tables, trying to delete OID's taking 6+hours per table
On Tue, May 19, 2020 at 12:17 AM Tory M Blue wrote: > > The db is only 2TB, these tables are about 50% of the DB. > > > Just wondering what I can boost to give me some elevated temp performance > for what I would think would be a fairly quick operation. > > Give this query more work_mem? Mine are set pretty low, based on previous > performance notes, this is a 9.5 server. > > Thanks > Tory > The command i'm using is ALTER TABLE tablename SET WITHOUT OIDS; Would a drop column oid be better? Tory
Re: Huge tables, trying to delete OID's taking 6+hours per table
On Tue, May 19, 2020 at 6:40 AM Tom Lane wrote: > Tory M Blue writes: > > The command i'm using is > > ALTER TABLE tablename SET WITHOUT OIDS; > > Would a drop column oid be better? > > Unfortunately, you're kind of stuck. OIDs are not like regular columns > (at least before v12) --- they are integrated into the tuple header in > a hackish way, and so there's no way to get rid of them without a table > rewrite. > > regards, tom lane > Poop :) kind of figured that, so it's just painful. But I guess if it's doing a table rewrite, is there any configuration params I could boost to help it? Shared_buffers, give it more, work mem, maintenance mem, temp buffers anything you can think of? Thanks again Tom Tory
Re: Huge tables, trying to delete OID's taking 6+hours per table
On Tue, May 19, 2020 at 10:06 AM Ron wrote: > On 5/19/20 11:51 AM, Tory M Blue wrote: > > > > On Tue, May 19, 2020 at 6:40 AM Tom Lane wrote: > >> Tory M Blue writes: >> > The command i'm using is >> > ALTER TABLE tablename SET WITHOUT OIDS; >> > Would a drop column oid be better? >> >> Unfortunately, you're kind of stuck. OIDs are not like regular columns >> (at least before v12) --- they are integrated into the tuple header in >> a hackish way, and so there's no way to get rid of them without a table >> rewrite. >> >> regards, tom lane >> > > Poop :) kind of figured that, so it's just painful. > > But I guess if it's doing a table rewrite, is there any configuration > params I could boost to help it? Shared_buffers, give it more, work mem, > maintenance mem, temp buffers anything you can think of? > > > There's an alternative if this is a "transaction table" (named, in this > example, FOO) which never gets updated (only inserted into and selected > from). > > Create a new, partitioned, oid-free copy of the table (named NEW_FOO) > that's populated with *most* of the records (all except the most > recent). When ready to cut over, you'd stop the applications, copy over > the most current records from FOO to NEW_FOO and then rename FOO to OLD_FOO > and FOO to OLD_FOO. > > Then you can drop OLD_FOO. > > -- > Angular momentum makes the world go 'round. > Thanks Ron, Looked into this but we have large indexes that take 8-12 hours to create. So my gut says this would not buy us anytime. Also this has been running for 16 hours now and still not done. I think it's forcing index creation regardless. Really a crappy situation!!! Tory
Re: AWS Aurora and PG 10
> On Dec 19, 2017, at 08:15, Rakesh Kumar wrote: > > Has AWS Aurora started supporting PG 10. Their website still talks about 9.6. > > Thanks > Actually in us-west-1 9.x is not even available Tory
