[
https://issues.apache.org/jira/browse/JENA-28?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12989824#comment-12989824
]
Gavin Carothers commented on JENA-28:
-------------------------------------
The speed up is HUGE. With even moderate (transactional, not bulk) insert loads
it's at least one order of magnitude. The issue is really that SDB never cleans
up the temporary tables at all. They just sit around forever and with MVCC in
Postgres their data sits around as well being written to disk every checkpoint,
forever.
One cross database method for detecting the table would be to do a test select
from the temporary table and create the table if it fails? It adds an extra
select statement per chunk but that doesn't seem horrible. I don't think that
would need any new SQL code in the per database bits of SDB?
> Drop Temporary Tables in Postgres (PATCH)
> -----------------------------------------
>
> Key: JENA-28
> URL: https://issues.apache.org/jira/browse/JENA-28
> Project: Jena
> Issue Type: Improvement
> Components: SDB
> Environment: Effects SDB PostgreSQL
> Reporter: Gavin Carothers
> Assignee: Damian Steer
>
> Current behaviour to truncate the temporary tables leaves the table objects,
> and thanks to MVCC in Postgres the data in those tables around for far longer
> then expected. This causes HUGE write loads on the database when loading a
> large number of quads or triples transactionally.
> Fix is VERY simple:
> Index: src/com/hp/hpl/jena/sdb/layout2/hash/TupleLoaderHashPGSQL.java
> ===================================================================
> --- src/com/hp/hpl/jena/sdb/layout2/hash/TupleLoaderHashPGSQL.java
> (revision 8420)
> +++ src/com/hp/hpl/jena/sdb/layout2/hash/TupleLoaderHashPGSQL.java
> (working copy)
> @@ -26,7 +26,7 @@
> }
>
> public String[] getCreateTempTable() {
> - return new String[] { "CREATE TEMPORARY TABLE" , "ON COMMIT
> DELETE ROWS" };
> + return new String[] { "CREATE TEMPORARY TABLE" , "ON COMMIT
> DROP" };
> }
--
This message is automatically generated by JIRA.
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
