You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-user@db.apache.org by Ashwin Jayaprakash <as...@rediffmail.com> on 2006/05/16 18:05:07 UTC
High throughput, min durability - tuning
Hello,
I'm posting several questions about Perf tuning. I'd be grateful if people can provide me with answers.
I'm using Derby as an Embedded Database for a prototype application, where Derby is used to run queries on a small set of Rows, very frequently. Rows are inserted into the Tables at very high rates. The Queries are run on these new Rows and so they are hot, in the Cache. Once the Query is executed on those Rows, they can be discarded. In a sense, I want Derby to function as an in-memory DB, like Oracle TimesTen.
I've tuned all the documented parameters like PageCacheSize, PageSize and Durability=test. Are there any other ways I can drastically reduce the Commits to the Disk and thereby eliminate Disk IO? Perhaps rewrite a key Class and add that to the "Pre-Classpath" so that the default behaviour can be over-ridden?
Are there any other parameters (undocumented) to increase the scalability of the DB? In the Embedded mode, I see that Derby only creates 2 Threads - antigc and something else. Does this mean that if my application can scale well, Derby's scalability will also scale?
The application works like this: Large number of Inserts, using a monotonically increasing Id, which is the Primary key and is indexed. Updates are practically absent. Once Inserted, they are used in a Select Query immediately. There's like, one such Table for each Producer-Consumer Thread pair. There will be several such Tables and Producer-Consumer Thread pairs. Contention is only between the 2 Threads for each Table. The rows will never be used again and so durability is NOT a requirement.
Any Hacks or Tuning Tips would be appreciated, for the scenario described above.
Thanks,
Ashwin.
Re: High throughput, min durability - tuning
Posted by Oystein Grovlen - Sun Norway <Oy...@Sun.COM>.
Ashwin Jayaprakash wrote:
> Hello,
> I'm posting several questions about Perf tuning. I'd be grateful if
> people can provide me with answers.
>
> I'm using Derby as an Embedded Database for a prototype application,
> where Derby is used to run queries on a small set of Rows, very
> frequently. Rows are inserted into the Tables at very high rates. The
> Queries are run on these new Rows and so they are hot, in the Cache.
> Once the Query is executed on those Rows, they can be discarded. In a
> sense, I want Derby to function as an in-memory DB, like Oracle TimesTen.
> I've tuned all the documented parameters like PageCacheSize, PageSize
> and Durability=test. Are there any other ways I can drastically reduce
> the Commits to the Disk and thereby eliminate Disk IO? Perhaps rewrite a
> key Class and add that to the "Pre-Classpath" so that the default
> behaviour can be over-ridden?
I guess your problem may really be that you are not able to reuse the
pages used by old rows for new rows after the old rows have been
deleted. (I assume you delete your old rows.) If you could reuse
existing pages for new data, you would never have to replace pages in
the cache, and hence you would not be dependent on disk IO for
performance. (Assuming you combine this with durability=test).
--
Øystein
Re: High throughput, min durability - tuning
Posted by Oystein Grovlen - Sun Norway <Oy...@Sun.COM>.
Ashwin Jayaprakash wrote:
> Hello,
> I'm posting several questions about Perf tuning. I'd be grateful if
> people can provide me with answers.
>
> I'm using Derby as an Embedded Database for a prototype application,
> where Derby is used to run queries on a small set of Rows, very
> frequently. Rows are inserted into the Tables at very high rates. The
> Queries are run on these new Rows and so they are hot, in the Cache.
> Once the Query is executed on those Rows, they can be discarded. In a
> sense, I want Derby to function as an in-memory DB, like Oracle TimesTen.
> I've tuned all the documented parameters like PageCacheSize, PageSize
> and Durability=test. Are there any other ways I can drastically reduce
> the Commits to the Disk and thereby eliminate Disk IO? Perhaps rewrite a
> key Class and add that to the "Pre-Classpath" so that the default
> behaviour can be over-ridden?
I guess your problem may really be that you are not able to reuse the
pages used by old rows for new rows after the old rows have been
deleted. (I assume you delete your old rows.) If you could reuse
existing pages for new data, you would never have to replace pages in
the cache, and hence you would not be dependent on disk IO for
performance. (Assuming you combine this with durability=test).
--
Øystein
Re: High throughput, min durability - tuning
Posted by David Van Couvering <Da...@Sun.COM>.
Well, I have a couple of thoughts:
- Depending upon your OS, often the /tmp directory is actually mapped to
memory, not disk. You might try putting your database in /tmp
- There has been a proposal and I think some work on implementing an
in-memory implementation of the store interface. I would highly
recommend you think about building this yourself. There are experts on
the team who would be more than glad to guide you in this effort. If
nothing else you would learn some of the itnernals really well which
could only help you in your efforts to tune the system.
David
Ashwin Jayaprakash wrote:
> Hello,
> I'm posting several questions about Perf tuning. I'd be grateful if
> people can provide me with answers.
>
> I'm using Derby as an Embedded Database for a prototype application,
> where Derby is used to run queries on a small set of Rows, very
> frequently. Rows are inserted into the Tables at very high rates. The
> Queries are run on these new Rows and so they are hot, in the Cache.
> Once the Query is executed on those Rows, they can be discarded. In a
> sense, I want Derby to function as an in-memory DB, like Oracle TimesTen.
>
> I've tuned all the documented parameters like PageCacheSize, PageSize
> and Durability=test. Are there any other ways I can drastically reduce
> the Commits to the Disk and thereby eliminate Disk IO? Perhaps rewrite a
> key Class and add that to the "Pre-Classpath" so that the default
> behaviour can be over-ridden?
>
> Are there any other parameters (undocumented) to increase the
> scalability of the DB? In the Embedded mode, I see that Derby only
> creates 2 Threads - antigc and something else. Does this mean that if my
> application can scale well, Derby's scalability will also scale?
>
> The application works like this: Large number of Inserts, using a
> monotonically increasing Id, which is the Primary key and is indexed.
> Updates are practically absent. Once Inserted, they are used in a Select
> Query immediately. There's like, one such Table for each
> Producer-Consumer Thread pair. There will be several such Tables and
> Producer-Consumer Thread pairs. Contention is only between the 2 Threads
> for each Table. The rows will never be used again and so durability is
> NOT a requirement.
>
> Any Hacks or Tuning Tips would be appreciated, for the scenario
> described above.
>
> Thanks,
> Ashwin.
>
>
>
>
>
>
>
>
>
>
>
>
> <http://adworks.rediff.com/cgi-bin/AdWorks/sigclick.cgi/www.rediff.com/signature-home.htm/1507191490@Middle5?PARTNER=3>
>
Re: High throughput, min durability - tuning
Posted by David Van Couvering <Da...@Sun.COM>.
Well, I have a couple of thoughts:
- Depending upon your OS, often the /tmp directory is actually mapped to
memory, not disk. You might try putting your database in /tmp
- There has been a proposal and I think some work on implementing an
in-memory implementation of the store interface. I would highly
recommend you think about building this yourself. There are experts on
the team who would be more than glad to guide you in this effort. If
nothing else you would learn some of the itnernals really well which
could only help you in your efforts to tune the system.
David
Ashwin Jayaprakash wrote:
> Hello,
> I'm posting several questions about Perf tuning. I'd be grateful if
> people can provide me with answers.
>
> I'm using Derby as an Embedded Database for a prototype application,
> where Derby is used to run queries on a small set of Rows, very
> frequently. Rows are inserted into the Tables at very high rates. The
> Queries are run on these new Rows and so they are hot, in the Cache.
> Once the Query is executed on those Rows, they can be discarded. In a
> sense, I want Derby to function as an in-memory DB, like Oracle TimesTen.
>
> I've tuned all the documented parameters like PageCacheSize, PageSize
> and Durability=test. Are there any other ways I can drastically reduce
> the Commits to the Disk and thereby eliminate Disk IO? Perhaps rewrite a
> key Class and add that to the "Pre-Classpath" so that the default
> behaviour can be over-ridden?
>
> Are there any other parameters (undocumented) to increase the
> scalability of the DB? In the Embedded mode, I see that Derby only
> creates 2 Threads - antigc and something else. Does this mean that if my
> application can scale well, Derby's scalability will also scale?
>
> The application works like this: Large number of Inserts, using a
> monotonically increasing Id, which is the Primary key and is indexed.
> Updates are practically absent. Once Inserted, they are used in a Select
> Query immediately. There's like, one such Table for each
> Producer-Consumer Thread pair. There will be several such Tables and
> Producer-Consumer Thread pairs. Contention is only between the 2 Threads
> for each Table. The rows will never be used again and so durability is
> NOT a requirement.
>
> Any Hacks or Tuning Tips would be appreciated, for the scenario
> described above.
>
> Thanks,
> Ashwin.
>
>
>
>
>
>
>
>
>
>
>
>
> <http://adworks.rediff.com/cgi-bin/AdWorks/sigclick.cgi/www.rediff.com/signature-home.htm/1507191490@Middle5?PARTNER=3>
>