Inserts become slow after a while

[Navjot Singh]

Hi,

I read some posts on the 'slow insert performance' reported by others
but couldnt seem to figure out an approach to improve on this. Just
wanted to share my problem and pick some brains about how others
solved their problem and may offer some tips.

In my use case, there is a ever running process which does some
analytics and keep generating the output data. This output data we
save in H2 (version 1.3.157). We are using mixed mode as we got 2
processes writing to same database.
The database file is lying on the shared network drive but fast r/w
otherwise. Cache is 64MB. We use connection pool and a db close delay
of 5s and rest all is default.

I have noticed that upto ~80k rows, the insert perf is fast, hardly
touches a ms but then gradually becomes slow. After that,
approximately every 5th insert operation (or one about every half a
sec) seems to be taking on an avg of 300ms while others keep inserting
in about a 1 ms or less time. So with my shallow knowledge of H2, it
seems that inserts shall be okay, there is something else happening
within H2 at regular interval which is causing this extra time.

What could be the possible cause? Please help me understand this. Is
there some file operation that H2 is doing which can cause them to be
slow as I read about some sync() being slow.

I have profiled our application which doesnt seem to be contributing
to this behavior.

regards
Navjot Singh

[Navjot Singh]

Hi,

Can anyone explain a bit more about this option - what it exactly
achieves?

SET WRITE_DELAY

regards
Navjot Singh

[Noel Grandin]

See here:
http://h2database.com/html/advanced.html
the discussion on durability.

WRITE_DELAY sets the delay between writes being committed to disk.

You could set it to longer, and you will gain more performance on
average, but risk losing more data if you have a power failure.

> --
> You received this message because you are subscribed to the Google Groups "H2 Database" group.
> 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.
>
>

[Navjot Singh]

Thanks a lot, Noel. Seems like I could gain something out of it.

regards
Navjot Singh

> > For more options, visit this group athttp://groups.google.com/group/h2-database?hl=en.- Hide quoted text -
>
> - Show quoted text -

[Navjot Singh]

Hi,

I tried setting up WRITE_DELAY to 30000 but I still get those slow
inserts approx every 500ms.

Is there something I am missing completely?

regards
Navjot Singh

On Sep 6, 5:44 pm, Noel Grandin <noelgran...@gmail.com> wrote:

[Steve McLeod]

Slow inserts after a while? Can you create some test code to
demonstrate this?

H2 does perform an automatic ANALYZE on a table periodically following
an insert, which causes a measurable delay. But this does not happen
for every 5th row.

> > > For more options, visit this group athttp://groups.google.com/group/h2-database?hl=en.-Hide quoted text -

[Mark Addleman]

This is interesting.  I have a pattern of many thousands of inserts while periodically issuing a DELETE of around ~100,000 rows.  Right now, I run an ANALYZE after each DELETE operation.  Is the ANALYZE superfluous?

[Noel Grandin]

Some things to look at:
(1) Is this reproducable when running on a local drive?
(2) turn on -verbosegc and see if the pauses are related to GC events

[Steve McLeod]

Mark,

The ANALYZE is probably superfluous. By default, an ANALYZE is
automatically performed after a total of 2000 rows are either added or
removed. Then, subsequently after 4000 adds/removes, 8000, 16,000,
etc... always doubling.

[Mark Addleman]

Just a clarification:  Is the doubling function applied to the number of INSERT / DELETE operations or to the number of rows in the table?

[Chris Schanck]

Try increasing the PAGE_SIZE parm in the connection properties; the
default is (I think) 4k, and I have found PAGE_SIZE=16384 to be vastly
more useful. Must be applied when the db is created. FWIW.

Chris

On Fri, Sep 9, 2011 at 7:55 PM, Mark Addleman <markad...@gmail.com> wrote:
> Just a clarification:  Is the doubling function applied to the number of
> INSERT / DELETE operations or to the number of rows in the table?
>

> --
> 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/-/T3Er4Tfl3_kJ.

> 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.
>

--
C. Schanck

[Mark Addleman]

Thanks!

[Chris Schanck]

Did it help? Sure helped me.

On Mon, Sep 12, 2011 at 12:26 AM, Mark Addleman <markad...@gmail.com> wrote:
> Thanks!
>

--
C. Schanck

[Steve McLeod]

Mark,

From reading the code, it seems to me that it is the actual number of
rows added/removed and NOT insert/delete operations. So, a single
DELETE might remove 100 rows - and that is 100 steps closer to the
next ANALYZE. A single counter is incremented after each row is added
and after each row is removed.

[Mark Addleman]

I threw in the new page size and, based on casual observation, I don't notice any improvement.  I haven't done any formal testing yet.  My rows are pretty small, probably around 300 bytes I may not see an advantage in larger page sizes.

[Mark Addleman]

Thanks!