Re: [PERFORM] insert performance

[Jeff Janes]

On Sat, Jan 9, 2016 at 9:57 PM, Jinhua Luo  wrote:
>
> To make a clean test env, I clone a new table, removing the indexes (keeping
> the primary key) and triggers, and use pgbench to test insert statement
> purely.

Can you share the pgbench command line, and the sql file you feed to
it (and whatever is needed to set up the schema)?


Thanks,

Jeff


-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] Queries getting canceled inside a proc that seems to slow down randomly

[Skarsol]

On Fri, Nov 13, 2015 at 3:50 PM, Jim Nasby  wrote:

> On 11/11/15 11:57 AM, Skarsol wrote:
>
>> Are we doing anything weird with this procedure? Is there anything more
>> I can do to get more info as to why/how the cancellation is happening or
>> why the function would slow down seemingly randomly?
>>
>> ERROR:  canceling statement due to user request
>> CONTEXT:  PL/pgSQL function chooselast(character varying,character
>> varying) line 1 at IF
>>  SQL statement "INSERT INTO partition_2015 VALUES (NEW.*)"
>>  PL/pgSQL function table1_insert_trigger() line 4 at SQL statement
>> STATEMENT:  INSERT into table1 (create_time,cusid,last1) Values
>> ('NOW','8175','ROBERT'')
>>
>
> The error tells you what's causing this; it's a client-side interrupt.
> Actually, looking at the code, you might get the same request if someone
> sent a signal to the relevant backend, either at the OS level or via
> pg_cancel_backend(). You can test that and see what error you get. A
> statement_timeout expiration would give you a different error.
>
> As for the hang, maybe someone is ALTERing or replacing the function?
>
> BTW, you could write that function in the SQL language, which might allow
> the optimizer to inline it. Even if it couldn't, you'd probably still see a
> performance gain from not firing up plpgsql on every row. Though, if you
> didn't allow empty strings in last1, you could also just replace that whole
> function with coalesce(). I see the function is marked IMMUTABLE, which is
> good.
> --
> Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
> Experts in Analytics, Data Architecture and PostgreSQL
> Data in Trouble? Get it in Treble! http://BlueTreble.com
>

No one is ALTERing or replacing the function (I'm the only person that
would). Recently we had an automated process attempted to load a file
containing one record (which usually takes under a second) and it failed
because of this issue (the insert was cancelled due to user request at the
IF in the chooselast function).

Similarly, but unrelated to the functions, we had a PHP script running as a
headless daemon process get this error (ERROR:  canceling statement due to
user request) while running 'SELECT * FROM connection WHERE id=109'.
Everyone was at lunch, so there's no way a user could have cancelled it.
These scripts run for months at a time with no issues, so it's not timeout
related. It's not a long running or complicated query:

 QUERY PLAN
-
 Seq Scan on connection  (cost=0.00..7.83 rows=1 width=60) (actual
time=0.031..0.033 rows=1 loops=1)
   Filter: (id = 109)
   Rows Removed by Filter: 306
 Total runtime: 0.047 ms
(4 rows)

This is going through pgbouncer and the related log entries are:

postgres:
5018 2016-01-11 12:23:28.143 CST:ERROR:  canceling statement due to user
request
5018 2016-01-11 12:23:28.143 CST:STATEMENT:  SELECT * FROM connection WHERE
id=109

pgbouncer (S-0x17fd780 entries):
2016-01-11 12:21:46.600 32905 LOG S-0x17fd780: edi01/editor@127.0.0.1:6432
closing because: server idle timeout (age=612)
2016-01-11 12:23:28.142 32905 LOG S-0x17fd780: edi01/editor@127.0.0.1:6432
new connection to server
2016-01-11 12:23:28.143 32905 LOG S-0x17fd780: edi01/editor@127.0.0.1:6432
closing because: sent cancel req (age=0)
2016-01-11 12:26:31.510 32905 LOG S-0x17fd780: edi01/editor@127.0.0.1:6432
new connection to server

Re: [PERFORM] Queries getting canceled inside a proc that seems to slow down randomly

[Jim Nasby]

On 1/11/16 1:32 PM, Skarsol wrote:

pgbouncer (S-0x17fd780 entries):
2016-01-11 12:21:46.600 32905 LOG S-0x17fd780:
edi01/editor@127.0.0.1:6432  closing
because: server idle timeout (age=612)
2016-01-11 12:23:28.142 32905 LOG S-0x17fd780:
edi01/editor@127.0.0.1:6432  new
connection to server
2016-01-11 12:23:28.143 32905 LOG S-0x17fd780:
edi01/editor@127.0.0.1:6432  closing
because: sent cancel req (age=0)
2016-01-11 12:26:31.510 32905 LOG S-0x17fd780:
edi01/editor@127.0.0.1:6432  new
connection to server


This makes me think there's a race condition in pgBouncer, or that their 
logging is just confusing.

--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com


--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] Queries getting canceled inside a proc that seems to slow down randomly

[Skarsol]

On Mon, Jan 11, 2016 at 1:39 PM, Jim Nasby  wrote:

> On 1/11/16 1:32 PM, Skarsol wrote:
>
>> pgbouncer (S-0x17fd780 entries):
>> 2016-01-11 12:21:46.600 32905 LOG S-0x17fd780:
>> edi01/editor@127.0.0.1:6432  closing
>> because: server idle timeout (age=612)
>> 2016-01-11 12:23:28.142 32905 LOG S-0x17fd780:
>> edi01/editor@127.0.0.1:6432  new
>> connection to server
>> 2016-01-11 12:23:28.143 32905 LOG S-0x17fd780:
>> edi01/editor@127.0.0.1:6432  closing
>> because: sent cancel req (age=0)
>> 2016-01-11 12:26:31.510 32905 LOG S-0x17fd780:
>> edi01/editor@127.0.0.1:6432  new
>> connection to server
>>
>
> This makes me think there's a race condition in pgBouncer, or that their
> logging is just confusing.
>
> --
> Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
> Experts in Analytics, Data Architecture and PostgreSQL
> Data in Trouble? Get it in Treble! http://BlueTreble.com
>

Okay, I'll go visit the pgbouncer guys and see if they can shed any light.
Thanks.