Re: [HACKERS] kqueue

[Rui DeSousa]

> On Apr 10, 2018, at 9:05 PM, Thomas Munro  
> wrote:
> 
> On Wed, Dec 6, 2017 at 12:53 AM, Thomas Munro
>  wrote:
>> On Thu, Jun 22, 2017 at 7:19 PM, Thomas Munro
>>  wrote:
>>> I don't plan to resubmit this patch myself, but I was doing some
>>> spring cleaning and rebasing today and I figured it might be worth
>>> quietly leaving a working patch here just in case anyone from the
>>> various BSD communities is interested in taking the idea further.
> 
> I heard through the grapevine of some people currently investigating
> performance problems on busy FreeBSD systems, possibly related to the
> postmaster pipe.  I suspect this patch might be a part of the solution
> (other patches probably needed to get maximum value out of this patch:
> reuse WaitEventSet objects in some key places, and get rid of high
> frequency PostmasterIsAlive() read() calls).  The autoconf-fu in the
> last version bit-rotted so it seemed like a good time to post a
> rebased patch.
> 
> -- 
> Thomas Munro
> http://www.enterprisedb.com
> 

Hi, 

I’m instrested in the kqueue patch and would like to know its current state and 
possible timeline for inclusion in the base code.  I have several large FreeBSD 
systems running PostgreSQL 11 that I believe currently displays this issue.  
The system has 88 vCPUs, 512GB Ram, and very active application with over 1000 
connections to the database.  The system exhibits high kernel CPU usage 
servicing poll() for connections that are idle.   

I’ve being testing pg_bouncer to reduce the number of connections and thus 
system CPU usage; however, not all connections can go through pg_bouncer. 

Thanks,
Rui.

Re: [HACKERS] kqueue

[Rui DeSousa]

Thanks Thomas,

Just a quick update.

I just deployed this patch into a lower environment yesterday running FreeBSD 
12.1 and PostgreSQL 11.6.  I see a significant reduction is CPU/system load 
from load highs of 500+ down to the low 20’s.  System CPU time has been reduced 
to practically nothing.  

I’m working with our support vendor in testing the patch and will continue to 
let it burn in.  Hopefully, we can get the patched committed.  Thanks.

> On Dec 19, 2019, at 7:26 PM, Thomas Munro  wrote:
> 
> It's still my intention to get this committed eventually, but I got a
> bit frazzled by conflicting reports on several operating systems.  For
> FreeBSD, performance was improved in many cases, but there were also
> some regressions that seemed to be related to ongoing work in the
> kernel that seemed worth waiting for.  I don't have the details
> swapped into my brain right now, but there was something about a big
> kernel lock for Unix domain sockets which possibly explained some
> local pgbench problems, and there was also a problem relating to
> wakeup priority with some test parameters, which I'd need to go and
> dig up.  If you want to test this and let us know how you get on,
> that'd be great!  Here's a rebase against PostgreSQL's master branch,
> and since you mentioned PostgreSQL 11, here's a rebased version for
> REL_11_STABLE in case that's easier for you to test/build via ports or
> whatever and test with your production workload (eg on a throwaway
> copy of your production system).  You can see it's working by looking
> in top: instead of state "select" (which is how poll() is reported)
> you see "kqread", which on its own isn't exciting enough to get this
> committed :-)
> 

archive_command

[Rui DeSousa]

Hi, 

I’ve been encouraged to submit this code as there has been talk in the past 
about a simple pgcopy command to use with the archive_command.  Currently there 
is really no good solution in most base systems without having to introduce a 
dedicated third party Postgres solution.  The best base system solution and 
most commonly used today is rsync which doesn’t fsync the file after completing 
the transfer leaving no good recommendations. 

I not sure what would need to be done to introduce this into a given 
commitfest.  Also, would need to know if the command interface is acceptable 
and/or other features should be added.  It currently is very simple as it just 
reads from standard input and saves data to the indicated file.

Please let me know how to proceed.

Thanks,
Rui   

 
Example of local compressed archive:

archive_command=“xz -c %p | fwrite /mnt/server/archivedir/%f”


Example of remote archive via a shell script:

#!/usr/bin/env bash

.
.
. 

# SSH Command and options
SSH_CMD="ssh -o ServerAliveInterval=20 $ARCH_SERVER"
STS=3

OUTPUT=$(cat $XLOGFILE | $SSH_CMD "(mkdir -p $ARCH_DIR && fwrite 
$ARCH_DIR/$WALFILE) 2>&1")

echo ${PIPESTATUS[@]} | grep -qE '^[0 ]+$'
if [ $? == 0 ]; then 
  STS=0
fi

exit $STS




fwrite code:


#include 
#include 
#include 
#include 
#include 
#include 
#include 
#include 


#define BUFSIZE 131072

int
main(int argc, char *argv[])
{
int fd, r, w;
char *buf;
char *name;
struct stat fstat;
  
if (argc != 2) {
fprintf(stderr, "usage: fwrite [file]\n");
exit(1);
}

if ((buf = malloc(BUFSIZE)) == NULL)
err(1, "malloc");

++argv;
if ((name = (char *) malloc(strlen(*argv) + 8)) == NULL)
err(1, "malloc");

strcat(strcpy(name, *argv), ".XX");

if ((fd = mkstemp(name)) < 0)
err(1, "mkstemp");

while ((r = read(STDIN_FILENO, buf, BUFSIZE)) > 0)
if ((w = write(fd, buf, r)) == -1) {
unlink(name);
err(1, "write");
}

if (r < 0)
err(1, "read");

if (lseek(fd, 0, SEEK_CUR) <= 0) {
unlink(name);
errx(1, "zero byte file!");
}

if (fsync(fd) != 0)
err(1, "fsync");

if (close(fd) != 0)
err(1, "close");

if (access(*argv, F_OK) < 0 && errno == ENOENT) {
if (rename(name, *argv) != 0)
err(1, "rename");
} else {
unlink(name);
errx(1, "file exists already!");
}
 
free(name);
exit(0);
}

Re: Possible optimisation: push down SORT and LIMIT nodes

[Rui DeSousa]

In the meantime you can force it with CTE.

with inv as (
  select id_asset
, inventory.date
, quantity
  from inventory
  order by inventory.date
  limit 100
)
select inv.date, asset.name, inv.quantity
from inv
join asset on id_asset = asset.id
order by inv.date, asset.name
;

> On Jun 1, 2018, at 11:12 AM, Steven Winfield 
>  wrote:
> 
> It does indeed!
>
> QUERY PLAN
> 
> Limit  (cost=398.50..398.50 rows=100 width=32) (actual time=10.359..10.378 
> rows=100 loops=1)
>Output: inventory.date, asset.name, inventory.quantity
>->  Incremental Sort  (cost=398.50..403.27 rows=5006001 width=32) (actual 
> time=10.357..10.365 rows=100 loops=1)
>  Output: inventory.date, asset.name, inventory.quantity
>  Sort Key: inventory.date, asset.name
>  Presorted Key: inventory.date
>  Sort Method: quicksort  Memory: 103kB
>  Sort Groups: 1
>  ->  Nested Loop Left Join  (cost=0.71..1702372.39 rows=5006001 
> width=32) (actual time=0.030..2.523 rows=1002 loops=1)
>Output: inventory.date, asset.name, inventory.quantity
>Inner Unique: true
>->  Index Scan using inventory_pkey on temp.inventory  
> (cost=0.43..238152.40 rows=5006001 width=12) (actual time=0.016..0.290 
> rows=1002 loops=1)
>  Output: inventory.date, inventory.id_asset, 
> inventory.quantity
>->  Index Scan using asset_pkey on temp.asset  
> (cost=0.28..0.29 rows=1 width=28) (actual time=0.002..0.002 rows=1 loops=1002)
>  Output: asset.id , asset.name
>  Index Cond: (asset.id  = 
> inventory.id_asset)
>  
> I’m guessing the feature-freeze for v11 means we won’t see this in the that 
> version, though, and the extra GUC it requires means it will be in v12 at the 
> earliest?
>  
> From: James Coleman [mailto:jtc...@gmail.com ] 
> Sent: 01 June 2018 13:50
> To: Christopher Wilson
> Cc: pgsql-hackers@lists.postgresql.org 
> ; Steven Winfield
> Subject: Re: FW: Possible optimisation: push down SORT and LIMIT nodes
>  
> The incremental sort patch seems to significantly improve performance for 
> your query: https://commitfest.postgresql.org/17/1124/ 
> 
>  
> On Fri, Jun 1, 2018 at 7:46 AM, Christopher Wilson 
> mailto:chris.wil...@cantabcapital.com>> 
> wrote:
> Dear Postgres developers,
>  
> I sent this query to the performance list a couple of days ago, but nobody 
> has come up with any suggestions. I was wondering if you’d like to consider 
> it?
>  
> If this is interesting but nobody has time to implement it, then I would 
> potentially be willing to implement and submit it myself, in my own time. I 
> am experienced with C and C++, but I have not modified Postgres before, and I 
> would need significant support (e.g. on IRC) to help me to find my way around 
> the codebase and finish the task in an acceptable amount of time.
>  
> Thanks, Chris.
>  
>  
> 
> From: Christopher Wilson 
> Sent: 30 May 2018 16:47
> To: 'pgsql-performa...@postgresql.org 
> '
> Cc: Steven Winfield (steven.winfi...@cantabcapital.com 
> )
> Subject: Possible optimisation: push down SORT and LIMIT nodes
>  
> Hi all,
>  
> We have a query which is rather slow (about 10 seconds), and it looks like 
> this:
>  
> select inventory.date, asset.name , inventory.quantity
> from temp.inventory 
> left outer join temp.asset on asset.id  = id_asset
> order by inventory.date, asset.name 
> limit 100
>  
> The inventory table has the quantity of each asset in the inventory on each 
> date (complete SQL to create and populate the tables with dummy data is 
> below). The query plan looks like this (the non-parallel version is similar):
>  
> 
>  
> Or in text form:
>  
> Limit  (cost=217591.77..217603.60 rows=100 width=32) (actual 
> time=9122.235..9122.535 rows=100 loops=1)
>Buffers: shared hit=6645, temp read=6363 written=6364
>->  Gather Merge  (cost=217591.77..790859.62 rows=4844517 width=32) 
> (actual time=9122.232..9122.518 rows=100 loops=1)
>  Workers Planned: 3
>  Workers Launched: 3
>  Buffers: shared hit=6645, temp read=6363 written=6364
>  ->  Sort  (cost=216591.73..220628.83 rows=1614839 width=32) (actual 
> time=8879.909..8880.030 rows=727 loops=4)
>Sort Key: inventory.date, asset.name 
>Sort Method: external merge  Disk: 50904kB
>Buffers: shared hit=27365, temp read=25943 wr

Re: Possible optimisation: push down SORT and LIMIT nodes

[Rui DeSousa]

True… but in that case it needs to be more expressive.

i.e.

with d as (
  select date
  from inventory
  order by date
  limit 10
), df as (
  select distinct date
  from d
)
select i.date, a.name, i.quantity
from inventory i
join asset a on a.id = i.id_asset
where i.date in (select date from df)
order by i.date, a.name
limit 10
;  


prod=>  with d as (
prod(>   select date
prod(>   from inventory
prod(>   order by date
prod(>   limit 10
prod(> ), df as (
prod(>   select distinct date
prod(>   from d
prod(> )
prod-> select i.date, a.name, i.quantity
prod-> from inventory i
prod-> join asset a on a.id = i.id_asset
prod-> where i.date in (select date from df)
prod-> order by i.date, a.name
prod-> limit 10
prod-> ; 
date|   name| quantity 
+---+--
 2004-09-22 | Thing 0.00122669106349349 |0
 2004-09-22 | Thing 0.00140673760324717 |0
 2004-09-22 | Thing 0.00180063676089048 |0
 2004-09-22 | Thing 0.00463481899350882 |1
 2004-09-22 | Thing 0.00622459733858705 |1
 2004-09-22 | Thing 0.00649207830429077 |0
 2004-09-22 | Thing 0.00823836214840412 |1
 2004-09-22 | Thing 0.0109024560078979  |1
 2004-09-22 | Thing 0.0109436474740505  |0
 2004-09-22 | Thing 0.0111544523388147  |0
(10 rows)

Time: 3.040 ms
prod=> select inventory.date, asset.name, inventory.quantity
prod-> from asset 
prod-> join inventory on id_asset = asset.id
prod-> order by inventory.date, asset.name
prod-> limit 10;
date|   name| quantity 
+---+--
 2004-09-22 | Thing 0.00122669106349349 |0
 2004-09-22 | Thing 0.00140673760324717 |0
 2004-09-22 | Thing 0.00180063676089048 |0
 2004-09-22 | Thing 0.00463481899350882 |1
 2004-09-22 | Thing 0.00622459733858705 |1
 2004-09-22 | Thing 0.00649207830429077 |0
 2004-09-22 | Thing 0.00823836214840412 |1
 2004-09-22 | Thing 0.0109024560078979  |1
 2004-09-22 | Thing 0.0109436474740505  |0
 2004-09-22 | Thing 0.0111544523388147  |0
(10 rows)

Time: 6733.775 ms (00:06.734)


> On Jun 1, 2018, at 12:46 PM, Chris Wilson  wrote:
> 
> Hi Rui,
> 
> Unfortunately sorting and limiting the CTE doesn't work properly, because 
> exactly which 100 rows are selected depends on values in the asset table, 
> which are not known at the time that the cte is evaluated.
> 
> I can work around it for our case by querying for the unique dates that make 
> it through the limit, and making the cte return all and only inventory 
> records matching those dates, but of course having this done automatically is 
> infinitely preferable. 
> 
> I'm really happy that this patch is actively being worked on and pushed 
> forward towards merging, and grateful to all involved in doing that. Thank 
> you for making Postgres even more awesome!
> 
> Thanks, Chris. 
> 
> Sent from my iPhone
> 
> On 1 Jun 2018, at 16:44, Rui DeSousa  <mailto:rui.deso...@icloud.com>> wrote:
> 
>> In the meantime you can force it with CTE.
>> 
>> with inv as (
>>   select id_asset
>> , inventory.date
>> , quantity
>>   from inventory
>>   order by inventory.date
>>   limit 100
>> )
>> select inv.date, asset.name, inv.quantity
>> from inv
>> join asset on id_asset = asset.id <http://asset.id/>
>> order by inv.date, asset.name
>> ;
>> 
>>> On Jun 1, 2018, at 11:12 AM, Steven Winfield 
>>> >> <mailto:steven.winfi...@cantabcapital.com>> wrote:
>>> 
>>> It does indeed!
>>>
>>> QUERY PLAN
>>> 
>>> Limit  (cost=398.50..398.50 rows=100 width=32) (actual time=10.359..10.378 
>>> rows=100 loops=1)
>>>Output: inventory.date, asset.name, inventory.quantity
>>>->  Incremental Sort  (cost=398.50..403.27 rows=5006001 width=32) 
>>> (actual time=10.357..10.365 rows=100 loops=1)
>>>  Output: inventory.date, asset.name, inventory.quantity
>>>  Sort Key: inventory.date, asset.name
>>>  Presorted Key: inventory.date
>>>  Sort Method: quicksort  Memory: 103kB
>>>  Sort Groups: 1
>>>  ->  Nested Loop Left Join  (cost=0.71..1702372.39 rows=5006001 
>>> width=32) (actual time=0.030..2.523 rows=1002 loops=1)
>>>Output: inventory.date,

Re: Remove mention in docs that foreign keys on partitioned tables are not supported

[Rui DeSousa]

> On Jun 14, 2018, at 9:19 AM, Robert Haas  wrote:
> 
>  anyone who wants a BEFORE trigger has a good reason
> for wanting it.

I have used before triggers to enforce the immutability of a column.

i.e. 

  if (new.member_key != old.member_key) then
raise exception 'Unable to change member_key, column is immutable';
  end if
  ; 

Re: New function pg_stat_statements_reset_query() to reset statistics of a specific query

[Rui DeSousa]

Why not just parameterize it with the three key fields; userid, dbid, and 
queryid?

i.e It would then allow it be limited to only records associated with a given 
user and/or database as well.

pg_stat_statements_reset(dbid oid, userid oid, queryid bigint)

pg_stat_statements_reset(null, null, 3569076157)— all for a given query
pg_stat_statements_reset(6384, null, 3569076157)  
pg_stat_statements_reset(null, 16429, 3569076157)
pg_stat_statements_reset(6384, 6384, 3569076157)
pg_stat_statements_reset(6384, null, null) — all for a given database.
.
.
.  

>> pg_stat_statements_reset()

> On Jun 22, 2018, at 11:06 AM, Robert Haas  wrote:
> 
> On Wed, Jun 20, 2018 at 10:19 AM, Euler Taveira  wrote:
>> 2018-06-20 4:30 GMT-03:00 Haribabu Kommi :
>>> Attached is a simple patch with implementation. Comments?
>>> 
>> Why don't you extend the existing function pg_stat_statements_reset()?
> 
> Well, the existing function doesn't take any arguments.  We could add
> an additional version of it that takes an argument, or we could
> replace the existing version with one that has an optional argument.
> But are either of those things any better than just adding a new
> function with a different name, like
> pg_stat_statements_reset_statement()?
> 
> I have not had such good experiences with function overloading, either
> in PostgreSQL or elsewhere, that I'm ready to say reusing the same
> name is definitely the right approach.  For example, suppose we
> eventually end up with a function that resets all the statements, a
> function that resets just one statement, a function that resets all
> statements for one user, and a function that resets all statements
> where the statement text matches a certain regexp.  If those all have
> separate names, everything is fine.  If they all have the same name,
> there's no way that's not confusing.
> 
> -- 
> Robert Haas
> EnterpriseDB: http://www.enterprisedb.com
> The Enterprise PostgreSQL Company
> 

Re: automatic restore point

[Rui DeSousa]

Why not use auto commit off in the session or .psqlrc file or begin and then 
use rollback?  \set AUTOCOMMIT off

What would be nice is if a syntax error didn’t abort the transaction when auto 
commit is off — being a bad typist.

Re: automatic restore point

[Rui DeSousa]

> On Jun 26, 2018, at 12:37 AM, Justin Pryzby  wrote:
> 
> I think you'll get that behavior with ON_ERROR_ROLLBACK.
> 

Awesome. Thanks!