Re: H2 Database Size Increased Dramatically

[Philipp Cornelius]

So, I need two things now:

- How can I fix the database size (the content should make the database only around 46GB large)?
- How can I prevent this from happening again (i.e., what has happened)?

On Saturday, 29 December 2012 11:20:28 UTC+1, Philipp Cornelius wrote:

Hi

I have a H2 database which saves around 760MB/day. Yesterday, database size was around 45,542,743.00 KB (approximated).
Then, while performing the following query, the database literally exploded:

"SELECT a, b FROM t1 WHERE a NOT IN ((SELECT a FROM t2) UNION (SELECT a FROM t3 WHERE c = ?)) ORDER BY b ASC LIMIT ?;"

(There are unique and non-unique inidces on a in every table and an index on c in t3. I am pretty sure this is not the most efficient query, but it was good enough for our purposes ... until now).

While performing the above query, the database increased in size with approx. 20MB/s until the disks were full. Currently, that is 89GB and 130GB respectively for the main and the backup server (the same thing happened on the redundant backup server at the same time).

I have analyzed the database content, but I cannot see where the 20MB/s of data came from?

[Thomas Mueller]

Hi,

The reason for the large increase might be that the subquery creates a temporary table.

I wonder if the query could be simplified so that no temporary table is necessary?

Regards,

Thomas

--
You received this message because you are subscribed to the Google Groups "H2 Database" group.

To view this discussion on the web visit https://groups.google.com/d/msg/h2-database/-/O4oXTPShYcIJ.

To post to this group, send email to h2-da...@googlegroups.com.
To unsubscribe from this group, send email to h2-database...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/h2-database?hl=en.