[PERFORM] BUG: endless lseek(.., SEEK_END) from select queries on x64 builds

[Markus Schulz]

hello,

i have a strange and reproducible bug with some select queries and 64bit 
postgresql builds (works fine on 32bit builds).
The postgres process will run with 100% cpu-load (no io-wait) and strace 
will show endless lseek(..., SEEK_END) calls on one table for minutes.
lseek(28, 0, SEEK_END)  = 26697728
lseek(28, 0, SEEK_END)  = 26697728
lseek(28, 0, SEEK_END)  = 26697728
...
the file-descriptor 28 points to the file for the webapps_base.Acquisition 
table (see query/plan below).

Now the details:

The Query:
select count(ac.ID) as col_0_0_ from 
webapps_base.Acquisition ac, 
webapps_base.SalesPartnerStructure struc
where 
struc.fk_SalesPartner_child=ac.fk_SalesPartner_ID 
and struc.fk_SalesPartner_parent=200
and (ac.CreationDate between '2012-02-01' and '2013-01-31') 
and ac.acquisitiondepot='STANDARD' 
and ('2013-01-31' between struc.ValidFrom 
and coalesce(struc.ValidTo, '2013-01-31'))

The Plan:
Aggregate  (cost=32617.11..32617.12 rows=1 width=8) (actual 
time=204.180..204.180 rows=1 loops=1)
  -  Merge Join  (cost=32232.01..32598.26 rows=7543 width=8) (actual 
time=172.882..202.218 rows=2 loops=1)
Merge Cond: (ac.fk_salespartner_id = struc.fk_salespartner_child)
-  Sort  (cost=5582.60..5635.69 rows=21235 width=16) (actual 
time=28.920..31.121 rows=21204 loops=1)
  Sort Key: ac.fk_salespartner_id
  Sort Method: quicksort  Memory: 1763kB
  -  Bitmap Heap Scan on acquisition ac  (cost=395.26..4056.43 
rows=21235 width=16) (actual time=3.064..15.868 rows=21223 loops=1)
Recheck Cond: ((creationdate = '2012-02-01'::date) AND 
(creationdate = '2013-01-31'::date))
Filter: ((acquisitiondepot)::text = 'STANDARD'::text)
-  Bitmap Index Scan on index_acquistion_creationdate  
(cost=0.00..389.95 rows=21267 width=0) (actual time=2.890..2.890 rows=21514 
loops=1)
  Index Cond: ((creationdate = '2012-02-01'::date) 
AND (creationdate = '2013-01-31'::date))
-  Sort  (cost=26648.60..26742.61 rows=37606 width=8) (actual 
time=143.952..152.808 rows=131713 loops=1)
  Sort Key: struc.fk_salespartner_child
  Sort Method: quicksort  Memory: 8452kB
  -  Bitmap Heap Scan on salespartnerstructure struc  
(cost=3976.80..23790.79 rows=37606 width=8) (actual time=13.279..64.681 
rows=114772 loops=1)
Recheck Cond: (fk_salespartner_parent = 200)
Filter: (('2013-01-31'::date = validfrom) AND 
('2013-01-31'::date = COALESCE(validto, '2013-01-31'::date)))
-  Bitmap Index Scan on index_parent_salespartner  
(cost=0.00..3967.39 rows=114514 width=0) (actual time=13.065..13.065 
rows=116479 loops=1)
  Index Cond: (fk_salespartner_parent = 200)
Total runtime: 205.397 ms

as you can see the query runs fine. 
I can run this query from a bash-psql-while-loop/jdbc-cli-tool 
endless without any problems. 
so far so good.

But now i run the same query from:

JBoss EAP 5.1.2 with connection pooling and xa-datasource/two-phase-commits 
(transactions on multiple datasources needed)
*and* *prepared-statement-cache-size1000/prepared-statement-cache-size*

i can run the query four times with good performance and after that postgresql 
starts with the strange lseek() behavior. 
The query needs more then a minute to complete and during execution the 
postgres process runs at 100% cpu load with lseek calls (straced).
If i flush the connection pool (close all open connections from the jboss 
jmx-console) it works again for four calls.
These problem applies only to 64bit builds. If i run a 32bit postgresql 
server it works fine.

We have tested the following environments:

- Debian Squeeze 64bit with Postgresql 9.1.[5,6,7] - Bad behavior
- Debian Wheezy 64bit with Postgresql 9.1.8 64bit - Bad behavior
- Ubuntu 12.04 LTS 64bit with Postgresql 9.1.8 64bit - Bad behavior
- Windows 7 x64 with Postgresql 9.1.8 64bit - Bad behavior
- Debian Wheezy 64bit with EnterpriseDB 9.2 64bit - Bad behavior

- Debian Wheezy 64bit with Postgresql 9.1.8 32bit - Good behavior
- Debian Wheezy 32bit with Postgresql 9.1.8 32bit - Good behavior

as you can see all 64bit builds of postgresql are affected (independent from 
os-arch).

If i disable the prepared-statement-cache-size (remove it from -ds.xml) 
it works on 64bit build too.

regards,
msc


-- 
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] BUG: endless lseek(.., SEEK_END) from select queries on x64 builds

[Markus Schulz]

Am Freitag, 22. Februar 2013, 14:35:25 schrieb Heikki Linnakangas:
 On 22.02.2013 10:25, Markus Schulz wrote:
  i can run the query four times with good performance and after that
  postgresql starts with the strange lseek() behavior.
 
 By default, the JDBC driver re-plans the prepared statement for the
 first 4 invocations of the query. On the fifth invocation, it switches
 to using a generic plan, which will be reused on subsequent invocations.

that sounds really interesting and i would try to change my java-jdbc-test-cli 
program according to that, but ...

 See http://jdbc.postgresql.org/documentation/head/server-prepare.html.
 The generic plan seems to perform much worse in this case. You can
 disable that mechanism and force re-planning the query every time by
 setting the prepareThreshold=0 parameter on the data source.

it wouldn't explain why the same jboss runs fine with a 32bit postgresql 
server (i switched only the datasource to another server with exactly the same 
database).

 You could check what the generic plan looks like by taking the query
 used in the java program, with the parameter markers, and running
 EXPLAIN on that.

how can i do this?
I've tried the following in my ejb-test-function to:

String query = ...
entitymanager.createNativeQuery(query)...;
entitymanager.createNativeQuery(EXPLAIN ANALYZE  + query)...;

but the second createNativeQuery call runs fast every time and will show the 
same plan and the first hangs after the fourth call to this function.

 PostgreSQL version 9.2 might work better in this case. It has some
 smarts in the server to generate parameter-specific plans even when
 prepared statements are used, if the planner thinks a specific plan will
 be faster.

this wouldn't help:
 - Debian Wheezy 64bit with EnterpriseDB 9.2 64bit - Bad behavior

we tried postgresql 9.2 too

 - Heikki

regards,
msc


-- 
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] problem with from_collapse_limit and joined views

[Markus Schulz]

Am Freitag, 3. Dezember 2010 schrieb Kevin Grittner:
 Markus Schulz m...@antzsystem.de wrote:
  i have a big performance problem
  
  [joining two complex views for reporting]
 
 What version of PostgreSQL is this?  How is PostgreSQL configured?
 (The postgresql.conf file with all comments removed is good.)

Production System is 8.4 (config attached).
But i've tried 9.0 with same result.

Regards 
msc
data_directory = '/var/lib/postgresql/8.4/main' # use data in another 
directory
hba_file = '/etc/postgresql/8.4/main/pg_hba.conf'   # host-based 
authentication file
ident_file = '/etc/postgresql/8.4/main/pg_ident.conf'   # ident configuration 
file
external_pid_file = '/var/run/postgresql/8.4-main.pid'  # write an 
extra PID file
listen_addresses = '*'  # what IP address(es) to listen on;
port = 5432 # (change requires restart)
max_connections = 3100  # (change requires restart)
unix_socket_directory = '/var/run/postgresql'   # (change requires 
restart)
ssl = true  # (change requires restart)
shared_buffers = 1500MB # min 128kB
max_prepared_transactions = 20  # zero disables the feature
work_mem = 16MB # min 64kB
checkpoint_segments = 8 # in logfile segments, min 1, 16MB each
random_page_cost = 3.0  # same scale as above
effective_cache_size = 20GB
from_collapse_limit = 8
join_collapse_limit = 8 # 1 disables collapsing of explicit 
log_destination = 'stderr'  # Valid values are combinations of
logging_collector = on  # Enable capturing of stderr and csvlog
log_directory = '/var/log/pgsql'# directory where log files are 
written,
log_filename = 'postgresql-%Y-%m-%d_%H.log' # log file name pattern,
log_rotation_age = 120  # Automatic rotation of logfiles will
log_duration = on
log_line_prefix = '%t [%d] [%x][%p]: [%l-1] '   # special 
values:
log_statement = 'none'  # none, ddl, mod, all
track_functions = all   # none, pl, all
track_activity_query_size = 16384
log_statement_stats = off
autovacuum_max_workers = 10 # max number of autovacuum subprocesses
datestyle = 'iso, dmy'
lc_messages = 'C'   # locale for system error message
lc_monetary = 'de_DE.utf8'  # locale for monetary formatting
lc_numeric = 'de_DE.utf8'   # locale for number formatting
lc_time = 'de_DE.utf8'  # locale for time formatting
default_text_search_config = 'pg_catalog.german'

-- 
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] problem with from_collapse_limit and joined views

[Markus Schulz]

Am Samstag 04 Dezember 2010 schrieb Kevin Grittner:
 One option would be to create a different user for running queries
 which read from complex views such as this.
 
 postgres=# create user bob;
 CREATE ROLE
 postgres=# alter user bob set from_collapse_limit = 40;
 ALTER ROLE
 postgres=# alter user bob set join_collapse_limit = 40;
 ALTER ROLE
 
 Log in as bob, and your queries should run fine.

thanks, that was really an option for us to use a different user for 
creating the reports.

 Nothing leapt out at me as an issue in your postgresql.conf except:
 
 max_prepared_transactions = 20
 
 Do you actually use prepared transactions?  (Sometimes people confuse
 this with prepared statements, which are a completely different
 feature.)

yes, they are needed for our JPA-based j2ee application.

regards
msc

-- 
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] problem with from_collapse_limit and joined views

[Markus Schulz]

Am Samstag 04 Dezember 2010 schrieb Tom Lane:
 Kevin Grittner kevin.gritt...@wicourts.gov writes:
...
  One option would be to create a different user for running queries
  which read from complex views such as this.
 
 If you don't want to change the collapse limits, the only other
 option is to restructure this specific query so that its syntactic
 structure is closer to the ideal join order.  Look at the plan you
 get in the good-performing case and re-order the join syntax to look
 like that.

no that's not working in this case.
view1 and view2 are written with explicit joins and no better join was 
possible. Each view works perfect standalone. 
In my above example i have rewritten view1 without explicit joins only 
for testing purpose. Without explicit joins i can gather the optimal 
query plan from a slightly higher from_collapse_limit (see workaround 2 
from my initial posting). 
If both views using explicit joins the from_collapse_limit is useless 
(only join_collapse_limit usable).

The problem exists only for view1 JOIN view2 and that pgsql don't 
see that an element of view2 contains an index-access for reducing the 
data from view1. Only if he can break the complete join of both views 
into one query-plan he can see this. But for this i must raise the 
limits.

Looks like some improvement to the geco optimizer was needed here ;)


regards
msc

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

[PERFORM] problem with from_collapse_limit and joined views

[Markus Schulz]

hello,

i have a big performance problem with some views which would joined 
(from the third party tool crystal reports) to print a document.

view1:

SELECT ...
FROM 
  personen.kunde kunde, 
  personen.natuerliche_person person, 
  viewakteur akteur, 
  personen.anschrift adresse, 
  personen.kontaktdaten kontakt, 
  konten.bankverbindung konto, 
  personen.berufsdaten beruf
WHERE person.objid = kunde.objid AND akteur.objid = kunde.objid AND 
person.adresse = adresse.objid AND person.kontaktdaten = kontakt.objid 
AND person.bankverbindung = konto.objid AND person.berufsdaten = 
beruf.objid

view2: 

SELECT ...
FROM vertraege.vertrag basisvertrag 
  JOIN ..
 .. twelve more inner joins ..

Each view works alone very fast for objid-access.(no sequence scans)
The final query build by crystal reports was like:

SELECT ...
FROM view2 INNER JOIN  view1 ON view2.kunde_objid = view1.objid
WHERE view2.objid = 

as you can see the search-key for view1 comes from view2.

if i set from_collapse_limit  (to merge the views) and 
join_collapse_limit (to explode the explicit joins) high enough(approx 
32), all is fine (good performance). But other queries are really slow 
in our environment (therefore it's no option to raise the 
join_collapse_limit to a higher value)

With defaults (8) for both, the performance is ugly because pgsql can't 
explode the views to build a better join-table with view1. 
(basisvertrag.kunde_objid from view2 is the key for kunde.objid from 
view1).

As workaround nr.1 i can do the following:

SELECT ...
FROM view2 INNER JOIN  view1 ON view2.kunde_objid = view1.objid 
WHERE view2.objid =  AND view1.objid = 

 (redundant information) is the same value as view2.kunde_objid. 
This instructs pgsql to minimize the result of view1 (one entry). 
But for this solution i must change hundreds of crystal report files.


For workaround nr.2 i need to instruct crystal report to generate a 
cross-join:
SELECT ...
FROM view2 , view1 
WHERE view2.VNID = view1.ID  AND view2.ID =  

Then i can slightly increase the from_collapse_limit (9) to enforce 
pgsql to explode the view1 and build a better join-plan. But i don't 
find a way to enforce crystal reports to using cross joins.

Workaround nr.3:
build one big view which contains all parts of view1 and view2. 
Really ugly (view1 and view2 are used in many more places).


What are the other options?

Regards,
msc

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