Re: Out of Memory
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? Thanks, Greig -- Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html
Re: Out of Memory
greigwise writes: > If I have nearly 8 GB of memory left, why am I getting out of memory errors? Probably the postmaster is running under restrictive ulimit settings. regards, tom lane
Re: Postgresql
You might find this comparision useful: https://en.wikipedia.org/wiki/Comparison_of_relational_database_management_systems -- Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html
Re: Out of Memory
Tom Lane-2 wrote > greigwise < > greigwise@ > > writes: >> If I have nearly 8 GB of memory left, why am I getting out of memory >> errors? > > Probably the postmaster is running under restrictive ulimit settings. > > regards, tom lane If I login as the user which runs postmaster, I get this: -bash-4.2$ ulimit -a core file size (blocks, -c) 0 data seg size (kbytes, -d) unlimited scheduling priority (-e) 0 file size (blocks, -f) unlimited pending signals (-i) 59341 max locked memory (kbytes, -l) 64 max memory size (kbytes, -m) unlimited open files (-n) 1024 pipe size(512 bytes, -p) 8 POSIX message queues (bytes, -q) 819200 real-time priority (-r) 0 stack size (kbytes, -s) 8192 cpu time (seconds, -t) unlimited max user processes (-u) 4096 virtual memory (kbytes, -v) unlimited file locks (-x) unlimited Max memory size is unlimited as is virtual memory... is there something else there I should be concerned with in regard to out of memory? Thanks again. Greig -- Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html
Re: Out of Memory
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
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
Re: Weird procedure question
digimer writes: > Hi all, > > I've got an interesting use case that I am stuck on. It's a bit of a > complicated environment, but I'll try to keep it simple. > > In short; I have a history schema that has tables that match the > public schema, plus one 'history_id' column that has a simple sequential > bigserial value. Then I have a procedure and trigger that, on UPDATE or > INSERT, copies the data to history. Example use case is that I can > UPDATE a sensor value in the public table and it's also INSERTs the data > into history. So public shows just the most recent values, but I can see > changes over time in the history schema. > > I have built my system to support writing to one or more DBs. I keep > a list of connected DBs and send INSERT/UPDATE calls to a method that > then runs the UPDATE/INSERT against all connected databases, as a form > of redundancy. This all works fine. > > The problem I've hit is that the 'history_id' differs between the > various databases. So I want to switch this to 'history_uuid' and use > UUIDs instead of bigserial. > > Now the question; > > Can I tell a produce to use a specific UUID? > > The idea is to generate a UUID for 'history_uuid' so that I have > consistency across databases. Of course, if an UPDATE will change > multiple rows, then I'll need to predefine multiple UUIDs. This is where > things start to get really complicated I think... Maybe I could pass an > array of UUIDs? I don't care if I find out which UUID was used for which > record, just that the same UUID was used for the same record when the > procedure is (re)run on other DBs. > > The databases are not clustered, on purpose. I've been trying to > handle all the HA stuff in my application for various reasons. > > If it helps, here is an example pair of tables, the procedure and the > trigger I currently use; > > > CREATE TABLE host_variable ( > host_variable_uuid uuid not null primary key, > host_variable_host_uuid uuid not null, > host_variable_name text not null, > host_variable_value text not null, > modified_date timestamp with time zone not null > ); > ALTER TABLE host_variable OWNER TO admin; > > CREATE TABLE history.host_variable ( > history_id bigserial, > host_variable_uuid uuid, > host_variable_host_uuid uuid, > host_variable_name text, > host_variable_value text, > modified_date timestamp with time zone not null > ); > ALTER TABLE history.host_variable OWNER TO admin; > > CREATE FUNCTION history_host_variable() RETURNS trigger > AS $$ > DECLARE > history_host_variable RECORD; > BEGIN > SELECT INTO history_host_variable * FROM host_variable WHERE > host_uuid = new.host_uuid; > INSERT INTO history.host_variable > (host_variable_uuid, > host_variable_host_uuid, > host_variable_name, > host_variable_value, > modified_date) > VALUES > (history_host_variable.host_variable_uuid, > history_host_variable.host_variable_host_uuid, > history_host_variable.host_variable_name, > history_host_variable.host_variable_value, > history_host_variable.modified_date); > RETURN NULL; > END; > $$ > LANGUAGE plpgsql; > ALTER FUNCTION history_host_variable() OWNER TO admin; > > CREATE TRIGGER trigger_host_variable > AFTER INSERT OR UPDATE ON host_variable > FOR EACH ROW EXECUTE PROCEDURE history_host_variable(); > > > I know this might sound odd, but I didn't want to complicate things > with how my system works. However, if it would help solve the problem, > I'm happy to dig into more detail. > > Thanks! I think James has probably given you the input you need - basically, don't allow the system to automatically set the modified time - make that parameter to your function or set that value before the copy to the history tables - content would then be the same, so uuid v3 should work. However, I do think you have another big problem lurking in the shadows. What happens if any of your connected databases are unavailable or unreachable for a period of time? I suspect your going to run into update anomalies and depending on your setup/environment, possibly even partitioning problems (depending on number of clients and typology etc). These are well known problems in distributed or replication systems. You appear to be implementing a 'poor mans' replication system. There are lots of complex issues to deal with and I wonder why you want to take them on when PG has already got well tested and robust solutions for this that would simplify your architecture and avoid the need to re-implement functionality which already exists? regards, Tim -- Tim Cross
Re: Weird procedure question
On 2018-09-25 6:22 p.m., Tim Cross wrote: digimer writes: Hi all, I've got an interesting use case that I am stuck on. It's a bit of a complicated environment, but I'll try to keep it simple. In short; I have a history schema that has tables that match the public schema, plus one 'history_id' column that has a simple sequential bigserial value. Then I have a procedure and trigger that, on UPDATE or INSERT, copies the data to history. Example use case is that I can UPDATE a sensor value in the public table and it's also INSERTs the data into history. So public shows just the most recent values, but I can see changes over time in the history schema. I have built my system to support writing to one or more DBs. I keep a list of connected DBs and send INSERT/UPDATE calls to a method that then runs the UPDATE/INSERT against all connected databases, as a form of redundancy. This all works fine. The problem I've hit is that the 'history_id' differs between the various databases. So I want to switch this to 'history_uuid' and use UUIDs instead of bigserial. Now the question; Can I tell a produce to use a specific UUID? The idea is to generate a UUID for 'history_uuid' so that I have consistency across databases. Of course, if an UPDATE will change multiple rows, then I'll need to predefine multiple UUIDs. This is where things start to get really complicated I think... Maybe I could pass an array of UUIDs? I don't care if I find out which UUID was used for which record, just that the same UUID was used for the same record when the procedure is (re)run on other DBs. The databases are not clustered, on purpose. I've been trying to handle all the HA stuff in my application for various reasons. If it helps, here is an example pair of tables, the procedure and the trigger I currently use; CREATE TABLE host_variable ( host_variable_uuid uuid not null primary key, host_variable_host_uuid uuid not null, host_variable_name text not null, host_variable_value text not null, modified_date timestamp with time zone not null ); ALTER TABLE host_variable OWNER TO admin; CREATE TABLE history.host_variable ( history_id bigserial, host_variable_uuid uuid, host_variable_host_uuid uuid, host_variable_name text, host_variable_value text, modified_date timestamp with time zone not null ); ALTER TABLE history.host_variable OWNER TO admin; CREATE FUNCTION history_host_variable() RETURNS trigger AS $$ DECLARE history_host_variable RECORD; BEGIN SELECT INTO history_host_variable * FROM host_variable WHERE host_uuid = new.host_uuid; INSERT INTO history.host_variable (host_variable_uuid, host_variable_host_uuid, host_variable_name, host_variable_value, modified_date) VALUES (history_host_variable.host_variable_uuid, history_host_variable.host_variable_host_uuid, history_host_variable.host_variable_name, history_host_variable.host_variable_value, history_host_variable.modified_date); RETURN NULL; END; $$ LANGUAGE plpgsql; ALTER FUNCTION history_host_variable() OWNER TO admin; CREATE TRIGGER trigger_host_variable AFTER INSERT OR UPDATE ON host_variable FOR EACH ROW EXECUTE PROCEDURE history_host_variable(); I know this might sound odd, but I didn't want to complicate things with how my system works. However, if it would help solve the problem, I'm happy to dig into more detail. Thanks! I think James has probably given you the input you need - basically, don't allow the system to automatically set the modified time - make that parameter to your function or set that value before the copy to the history tables - content would then be the same, so uuid v3 should work. However, I do think you have another big problem lurking in the shadows. What happens if any of your connected databases are unavailable or unreachable for a period of time? I suspect your going to run into update anomalies and depending on your setup/environment, possibly even partitioning problems (depending on number of clients and typology etc). These are well known problems in distributed or replication systems. You appear to be implementing a 'poor mans' replication system. There are lots of complex issues to deal with and I wonder why you want to take them on when PG has already got well tested and robust solutions for this that would simplify your architecture and avoid the need to re-implement functionality which already exists? regards, Tim Hi Tim, Last I checked, pgsql couldn't handle this; Two DBs up, getting data. DB1 goes down, DB2 continues to collect data. DB2 goes down DB1 comes back up, starts collecting data. DB2 comes back up, now I need to move data in both directions (DB1 has data 2 doesn't and vice-versa). I've created a way to resolve this in my application and it's worked for some time (obviously, in my application only. It's not a general purpose system nor is it intended to be). For the record, I
Re: Weird procedure question
On 2018-09-25 6:41 p.m., digimer wrote: On 2018-09-25 6:22 p.m., Tim Cross wrote: digimer writes: Hi all, I've got an interesting use case that I am stuck on. It's a bit of a complicated environment, but I'll try to keep it simple. In short; I have a history schema that has tables that match the public schema, plus one 'history_id' column that has a simple sequential bigserial value. Then I have a procedure and trigger that, on UPDATE or INSERT, copies the data to history. Example use case is that I can UPDATE a sensor value in the public table and it's also INSERTs the data into history. So public shows just the most recent values, but I can see changes over time in the history schema. I have built my system to support writing to one or more DBs. I keep a list of connected DBs and send INSERT/UPDATE calls to a method that then runs the UPDATE/INSERT against all connected databases, as a form of redundancy. This all works fine. The problem I've hit is that the 'history_id' differs between the various databases. So I want to switch this to 'history_uuid' and use UUIDs instead of bigserial. Now the question; Can I tell a produce to use a specific UUID? The idea is to generate a UUID for 'history_uuid' so that I have consistency across databases. Of course, if an UPDATE will change multiple rows, then I'll need to predefine multiple UUIDs. This is where things start to get really complicated I think... Maybe I could pass an array of UUIDs? I don't care if I find out which UUID was used for which record, just that the same UUID was used for the same record when the procedure is (re)run on other DBs. The databases are not clustered, on purpose. I've been trying to handle all the HA stuff in my application for various reasons. If it helps, here is an example pair of tables, the procedure and the trigger I currently use; CREATE TABLE host_variable ( host_variable_uuid uuid not null primary key, host_variable_host_uuid uuid not null, host_variable_name text not null, host_variable_value text not null, modified_date timestamp with time zone not null ); ALTER TABLE host_variable OWNER TO admin; CREATE TABLE history.host_variable ( history_id bigserial, host_variable_uuid uuid, host_variable_host_uuid uuid, host_variable_name text, host_variable_value text, modified_date timestamp with time zone not null ); ALTER TABLE history.host_variable OWNER TO admin; CREATE FUNCTION history_host_variable() RETURNS trigger AS $$ DECLARE history_host_variable RECORD; BEGIN SELECT INTO history_host_variable * FROM host_variable WHERE host_uuid = new.host_uuid; INSERT INTO history.host_variable (host_variable_uuid, host_variable_host_uuid, host_variable_name, host_variable_value, modified_date) VALUES (history_host_variable.host_variable_uuid, history_host_variable.host_variable_host_uuid, history_host_variable.host_variable_name, history_host_variable.host_variable_value, history_host_variable.modified_date); RETURN NULL; END; $$ LANGUAGE plpgsql; ALTER FUNCTION history_host_variable() OWNER TO admin; CREATE TRIGGER trigger_host_variable AFTER INSERT OR UPDATE ON host_variable FOR EACH ROW EXECUTE PROCEDURE history_host_variable(); I know this might sound odd, but I didn't want to complicate things with how my system works. However, if it would help solve the problem, I'm happy to dig into more detail. Thanks! I think James has probably given you the input you need - basically, don't allow the system to automatically set the modified time - make that parameter to your function or set that value before the copy to the history tables - content would then be the same, so uuid v3 should work. However, I do think you have another big problem lurking in the shadows. What happens if any of your connected databases are unavailable or unreachable for a period of time? I suspect your going to run into update anomalies and depending on your setup/environment, possibly even partitioning problems (depending on number of clients and typology etc). These are well known problems in distributed or replication systems. You appear to be implementing a 'poor mans' replication system. There are lots of complex issues to deal with and I wonder why you want to take them on when PG has already got well tested and robust solutions for this that would simplify your architecture and avoid the need to re-implement functionality which already exists? regards, Tim Hi Tim, Last I checked, pgsql couldn't handle this; Two DBs up, getting data. DB1 goes down, DB2 continues to collect data. DB2 goes down DB1 comes back up, starts collecting data. DB2 comes back up, now I need to move data in both directions (DB1 has data 2 doesn't and vice-versa). I've created a way to resolve this in my application and it's worked for some time (obviously, in my application only. It's not a general purpose sys
Re: Weird procedure question
On Tue, Sep 25, 2018, 2:19 AM digimer wrote: > Oh, this is a very interesting approach! I didn't realize any UUIDs > could be created in a predictable way. Thank you, this might be what I need. > Yep, DCE defined about 5 different versions of UUIDs, each with somewhat differing characteristics. https://en.m.wikipedia.org/wiki/Universally_unique_identifier Versions 3 and 5 generate repeatable values, which is possibly what you are after. 1 and 2 used timestamp info plus node. At one time MAC addresses were used as node info, but that seems to have fallen into disrepute. (I think because it leaks network identifier info) It's worth struggling through understanding them; the variations certainly are useful. I think I have a function around to generate time-based UUIDs based on giving the timestamp as parameter; I'll see if I can dig that out. That would allow writing up old history with UUIDs that look old. Bug me off list if that sounds interesting.
Strange behavior of the random() function
Hello, Can anybody explain me that strange behavior? select random() as "rnd", random() as "rnd1", random() as "rnd2" from generate_series( 1, 5 ); --- 0.948556384071708 0.769186236895621 0.381390900816768 0.684488877654076 0.594888080842793 0.21123208523 0.668408528901637 0.452859566546977 0.924952184315771 All of the values are different. select random() as "rnd", random() as "rnd1", random() as "rnd2" from generate_series( 1, 3 ) order by random(); --- 0.166236794553697 0.166236794553697 0.166236794553697 0.629051928408444 0.629051928408444 0.629051928408444 0.698161978274584 0.698161978274584 0.698161978274584 All values in any row are the same. select random() as "rnd", random() as "rnd1", random() as "rnd2" from generate_series( 1, 3 ) order by random() || 'test'; --- 0.569052047561854 0.531697370111942 0.338135569822043 0.880468992516398 0.172520748339593 0.0296505615115166 0.862012444529682 0.119546371512115 0.21626165881753 All of the values are different again. Is it a bug?
Re: Strange behavior of the random() function
r.zhar...@postgrespro.ru writes: > Can anybody explain me that strange behavior? It's a squishiness in the SQL language, if you ask me. Consider this simplified query: select random() from generate_series(1, 3) order by random(); Would you expect the output of this query to appear ordered, or not? There's an argument to be made that the two instances of random() ought to be executed independently for each row, so that the output would (probably) seem unordered. But practical usage generally expects that we unify the two textually-identical expressions, so that the behavior is the same as select random() from generate_series(1, 3) order by 1; > select random() as "rnd", random() as "rnd1", random() as "rnd2" > from generate_series( 1, 3 ) > order by random(); > All values in any row are the same. Here, we've unified *all* the appearances of the ORDER BY expression in the SELECT-list. Maybe that's wrong, but it's hard to make a principled argument why we shouldn't do it. If only one of them should be unified with the ORDER BY expression, then which one? > select random() as "rnd", random() as "rnd1", random() as "rnd2" > from generate_series( 1, 3 ) > order by random() || 'test'; Here, the ORDER BY expression is not a match to any SELECT-list expression, so they don't get unified with it. If you'd like more control over this sort of thing, I'd recommend using a sub-select, ie select x,y,z from (select random() as x, random() as y, random() as z from generate_series(1,3)) ss order by x; which makes it clear what your intention is. There's some interesting related behaviors: compare the outputs of select random() as x, random() as y, random() as z from generate_series(1,3) order by 1; select random() as x, random() as y, random() as z from generate_series(1,3) order by 1,2; select random() as x, random() as y, random() as z from generate_series(1,3) order by 1,2,3; I could buy the idea that there's a bug involved in that; but again the question is exactly which textually identical expressions should get unified and why. regards, tom lane
Re:Re: how to know whether query data from memory after pg_prewarm
But I use windows server 2012R.
pgfincore can not run on the windows.
Is there some replacements in windows system?
At 2018-09-19 15:44:06, "Cédric Villemain" wrote:
>Le 19/09/2018 à 05:29, Thomas Munro a écrit :
>> On Wed, Sep 19, 2018 at 1:35 PM jimmy wrote:
>>> I use select pg_prewarm('table1','read','main') to load data of table1
>>> into the memory.
>>> when I use select count(1) from table1 group by aa to query data.
>>> I find the speed of query is not fast, I wonder whether it query data from
>>> memory.
>>> And it is slower than Oracle, both of Oracle and Postgresql has same table
>>> and count of data.
>>> when pg_prewarm use 'read' mode, the data is put into the OS cache, how to
>>> examine the table which is pg_prewarmed into the OS cache .
>>> I know pg_buffercache ,but it just examine the table in the shared buffer
>>> of Postgresql, not the table in the OS cache.
>>
>> This is a quick and dirty hack, but it might do what you want:
>>
>> https://github.com/macdice/pgdata_mincore
>>
>> Tested on FreeBSD, not sure how well it'll travel.
>
>You can use pgfincore extension for that purpose, and more.
>
>https://github.com/klando/pgfincore/blob/master/README.md
>
>
>--
>Cédric Villemain +33 (0)6 20 30 22 52
>http://2ndQuadrant.fr/
>PostgreSQL: Support 24x7 - Développement, Expertise et Formation
how to clean the cache from databases and operating system
1、When I execute the firse sql query, like below:
select * from tablename;
there are some datas that will be loaded into the database cache.
How to clean the data from cache.
2、When I execute second sql query like below:
SELECT pg_prewarm('tablename', 'buffer') . Data will be loaded into the
database cache.
How to clean the data from cache.
3、When I execute the third sql query like below:
SELECT pg_prewarm('tablename', 'main') . Data will be loaded into the os
cache.
How to clean the data from cache.
