You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-dev@db.apache.org by "Peter Kovgan (JIRA)" <de...@db.apache.org> on 2005/07/17 10:16:13 UTC
[jira] Created: (DERBY-465) Embedded Derby-PointBase comparison
Embedded Derby-PointBase comparison
-----------------------------------
Key: DERBY-465
URL: http://issues.apache.org/jira/browse/DERBY-465
Project: Derby
Type: Wish
Components: Test
Versions: 10.0.2.1, 10.0.2.0
Environment: Windows Server 2003, 4 processors, summary CPU 3.00 Ghz, RAM 1 Gb
Reporter: Peter Kovgan
I have tested 4 major embedded DB.
I have found that major disadvantage of Derby is
1)low insert speed and
2)significant performance degradation in select, update, delete operation speed starting from some table size.
PointBase in comparison has not such degradation.
It will be better if you improve your product.
Good luck and thank you.
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
http://www.atlassian.com/software/jira
[jira] Commented: (DERBY-465) Embedded Derby-PointBase comparison
Posted by "Peter Kovgan (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-465?page=comments#action_12316021 ]
Peter Kovgan commented on DERBY-465:
------------------------------------
I will continue testing Derby trying to add possible optimizations, I'll publish results shortly.
> Embedded Derby-PointBase comparison
> -----------------------------------
>
> Key: DERBY-465
> URL: http://issues.apache.org/jira/browse/DERBY-465
> Project: Derby
> Type: Wish
> Components: Test
> Versions: 10.0.2.0, 10.0.2.1
> Environment: Windows Server 2003, 4 processors, summary CPU 3.00 Ghz, RAM 1 Gb
> Reporter: Peter Kovgan
> Attachments: Benchmarks_info_independent.doc
>
> I have tested 4 major embedded DB.
> I have found that major disadvantage of Derby is
> 1)low insert speed and
> 2)significant performance degradation in select, update, delete operation speed starting from some table size.
> PointBase in comparison has not such degradation.
> It will be better if you improve your product.
> Good luck and thank you.
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
http://www.atlassian.com/software/jira
[jira] Updated: (DERBY-465) Embedded Derby-PointBase comparison
Posted by "Peter Kovgan (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-465?page=all ]
Peter Kovgan updated DERBY-465:
-------------------------------
Attachment: derby-pb1.doc
Attached file (derby-pb1.doc) describes test environment.
Regarding right page size - I need to test it, but I'm in doubt that right page size may change situation when Large table is in processing.
> Embedded Derby-PointBase comparison
> -----------------------------------
>
> Key: DERBY-465
> URL: http://issues.apache.org/jira/browse/DERBY-465
> Project: Derby
> Type: Wish
> Components: Test
> Versions: 10.0.2.1, 10.0.2.0
> Environment: Windows Server 2003, 4 processors, summary CPU 3.00 Ghz, RAM 1 Gb
> Reporter: Peter Kovgan
> Attachments: Benchmarks_info_independent.doc, derby-optimization.doc, derby-pb1.doc
>
> I have tested 4 major embedded DB.
> I have found that major disadvantage of Derby is
> 1)low insert speed and
> 2)significant performance degradation in select, update, delete operation speed starting from some table size.
> PointBase in comparison has not such degradation.
> It will be better if you improve your product.
> Good luck and thank you.
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
http://www.atlassian.com/software/jira
[jira] Commented: (DERBY-465) Embedded Derby-PointBase comparison
Posted by "Peter Kovgan (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-465?page=comments#action_12316609 ]
Peter Kovgan commented on DERBY-465:
------------------------------------
Hi Mike!
Your comments and help are very important for me and it seems we are coming together to similar results.
Thank you.
1. Right now I discovered lock timeout errors on PB on multi-user-write test and I'm planning provide more precise tests of PB and recheck PB read and write.
2. Also Commit() probably must be done on every transaction to minimize lock time in multi-user test
3. May be I also will check several debugging options in both DB to be sure results are right.
After tests I'll send source code of multi-user test as well as description to clarify results.
> Embedded Derby-PointBase comparison
> -----------------------------------
>
> Key: DERBY-465
> URL: http://issues.apache.org/jira/browse/DERBY-465
> Project: Derby
> Type: Wish
> Components: Test
> Versions: 10.0.2.1, 10.0.2.0
> Environment: Windows Server 2003, 4 processors, summary CPU 3.00 Ghz, RAM 1 Gb
> Reporter: Peter Kovgan
> Attachments: Benchmarks_info_independent.doc, DBOperations.java, Multithreading-access read.doc, User.java, derby-optimization.doc, derby-pb1.doc
>
> I have tested 4 major embedded DB.
> I have found that major disadvantage of Derby is
> 1)low insert speed and
> 2)significant performance degradation in select, update, delete operation speed starting from some table size.
> PointBase in comparison has not such degradation.
> It will be better if you improve your product.
> Good luck and thank you.
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
http://www.atlassian.com/software/jira
[jira] Commented: (DERBY-465) Embedded Derby-PointBase comparison
Posted by "Mike Matrigali (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-465?page=comments#action_12316146 ]
Mike Matrigali commented on DERBY-465:
--------------------------------------
It is very interesting that the other embedded db's don't seem to need indexing, again without seeing the ddl
used for those db's it is hard to tell if somehow they may be automatically building an index. Is the natural
key of the table the 1st column in your table? Are the results the same for the other db's if you make it the
last column?
For your pointbase tests what value do you have for the pointbase log.forcewrite parameter. Here is their writeup
about the danger of not forcing log writes:
log.forcewrite false When log.forcewrite is false, a physical write to the log file on the disk system will NOT be forced after each commit. It is left to the operating system to flush the buffer onto the disk as per the operating system's schedule. If there is an abnormal termination of the program, there is a possibility that some committed transactions will be lost. In some cases the database may get corrupted. However, a forced write to the log file on the disk WILL occur when the database is closed.
With log.forcewrite set to true, whenever there is hardware failure/system reset, the last commit to the log file will be written before the failure. When log.forcewrite is true, every time a commit is made (by manually committing or auto commit = true), a physical write to the log file on the disk system will be FORCED.
My interpretation of this is that by default pointbase does not correctly support crash recovery, and probably should be marked so in your write up.
> Embedded Derby-PointBase comparison
> -----------------------------------
>
> Key: DERBY-465
> URL: http://issues.apache.org/jira/browse/DERBY-465
> Project: Derby
> Type: Wish
> Components: Test
> Versions: 10.0.2.1, 10.0.2.0
> Environment: Windows Server 2003, 4 processors, summary CPU 3.00 Ghz, RAM 1 Gb
> Reporter: Peter Kovgan
> Attachments: Benchmarks_info_independent.doc, derby-optimization.doc
>
> I have tested 4 major embedded DB.
> I have found that major disadvantage of Derby is
> 1)low insert speed and
> 2)significant performance degradation in select, update, delete operation speed starting from some table size.
> PointBase in comparison has not such degradation.
> It will be better if you improve your product.
> Good luck and thank you.
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
http://www.atlassian.com/software/jira
[jira] Commented: (DERBY-465) Embedded Derby-PointBase comparison
Posted by "Peter Kovgan (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-465?page=comments#action_12316236 ]
Peter Kovgan commented on DERBY-465:
------------------------------------
Multithreading test I did compromises Derby(at least I think so , but I may change my opinion...) and I need your suggestions how to improve this situation.
Thank you.
> Embedded Derby-PointBase comparison
> -----------------------------------
>
> Key: DERBY-465
> URL: http://issues.apache.org/jira/browse/DERBY-465
> Project: Derby
> Type: Wish
> Components: Test
> Versions: 10.0.2.1, 10.0.2.0
> Environment: Windows Server 2003, 4 processors, summary CPU 3.00 Ghz, RAM 1 Gb
> Reporter: Peter Kovgan
> Attachments: Benchmarks_info_independent.doc, DBOperations.java, Multithreading-access read.doc, User.java, derby-optimization.doc, derby-pb1.doc
>
> I have tested 4 major embedded DB.
> I have found that major disadvantage of Derby is
> 1)low insert speed and
> 2)significant performance degradation in select, update, delete operation speed starting from some table size.
> PointBase in comparison has not such degradation.
> It will be better if you improve your product.
> Good luck and thank you.
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
http://www.atlassian.com/software/jira
[jira] Closed: (DERBY-465) Embedded Derby-PointBase comparison
Posted by "Mike Matrigali (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-465?page=all ]
Mike Matrigali closed DERBY-465:
--------------------------------
Resolution: Fixed
Peter has completed his performance testing effort vs. pointbase and has attached his results to this issue.
Some of my observations:
o Derby's default configuration which guarantees transaction consistency, often results in users believing Derby
is 10 times slower than other db's like PointBase which default to not syncing their transactions. This is often
the first thing new users see when they write their first program which does inserts with autocommit true, thus
committing on every transaction. My belief is that most applications which use transactions actually expect to be
able to count on the commit, but there are a number of applications which don't.
o Many of the initial problems seen trying to run this benchmark was that there were no indexes. It was interesting
that for 100,000 rows that performance was actually somewhat ok for keyed lookup, even when there was no index.
Should a zero-admin database somehow create it's own indexes on the fly?
o The write portion of this benchmark was sort of unusual in that it updates and deletes in a concurrent fashion a large
% of the total data set. As the number of users was increased this caused deadlocks. The conclusion that only
a small number of updaters could be supported I believe is because of this particular application mix. I have seen
Derby support 100's of concurrent readers/writers in a more standard application mix where the number of rows
affected by each user transaction is smaller.
> Embedded Derby-PointBase comparison
> -----------------------------------
>
> Key: DERBY-465
> URL: http://issues.apache.org/jira/browse/DERBY-465
> Project: Derby
> Type: Wish
> Components: Test
> Versions: 10.0.2.0, 10.0.2.1
> Environment: Windows Server 2003, 4 processors, summary CPU 3.00 Ghz, RAM 1 Gb
> Reporter: Peter Kovgan
> Attachments: Benchmarks_info_independent.doc, DBBenchmarkProject.zip, DBOperations.java, Multithreading-access read.doc, User.java, dbbenchmark_multi_user_04_08_2005.zip, derby-optimization.doc, derby-pb1.doc
>
> I have tested 4 major embedded DB.
> I have found that major disadvantage of Derby is
> 1)low insert speed and
> 2)significant performance degradation in select, update, delete operation speed starting from some table size.
> PointBase in comparison has not such degradation.
> It will be better if you improve your product.
> Good luck and thank you.
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
http://www.atlassian.com/software/jira
[jira] Commented: (DERBY-465) Embedded Derby-PointBase comparison
Posted by "Peter Kovgan (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-465?page=comments#action_12316315 ]
Peter Kovgan commented on DERBY-465:
------------------------------------
Mike,
thank you for your comments.
They are all helpful.
1. Really : update is differ and this is my mistake and I'll take care. New tests will be with the same update in both DB.
2. My second mistake is in last document, describing threads: I tested thread in 100 cycles, not 10.
(but also PB was tested with 100 loops, so comparison is right for 100 cycles, because I have no mistake in select)
I also did not indexed PB. PB and Derby are in default configuration. Yes , PB does not write every transaction in log, but it also tested only for "READ" access in SELECT and here I shouldn't expect logs? right?
3. Yes, I extend DBOperations for Derby and for PB, next time I'll send all relevant files and updated results.
So, excuse me for my mistaken docs.
I hope shortly I'll update them.
> Embedded Derby-PointBase comparison
> -----------------------------------
>
> Key: DERBY-465
> URL: http://issues.apache.org/jira/browse/DERBY-465
> Project: Derby
> Type: Wish
> Components: Test
> Versions: 10.0.2.1, 10.0.2.0
> Environment: Windows Server 2003, 4 processors, summary CPU 3.00 Ghz, RAM 1 Gb
> Reporter: Peter Kovgan
> Attachments: Benchmarks_info_independent.doc, DBOperations.java, Multithreading-access read.doc, User.java, derby-optimization.doc, derby-pb1.doc
>
> I have tested 4 major embedded DB.
> I have found that major disadvantage of Derby is
> 1)low insert speed and
> 2)significant performance degradation in select, update, delete operation speed starting from some table size.
> PointBase in comparison has not such degradation.
> It will be better if you improve your product.
> Good luck and thank you.
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
http://www.atlassian.com/software/jira
[jira] Commented: (DERBY-465) Embedded Derby-PointBase comparison
Posted by "Sunitha Kambhampati (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-465?page=comments#action_12316384 ]
Sunitha Kambhampati commented on DERBY-465:
-------------------------------------------
Derby has some debug properties that can be enabled, to print the lock table information on a timeout. Please see the following link that explains how to do this.
http://incubator.apache.org/derby/faq.html#debug_lock_timeout
> Embedded Derby-PointBase comparison
> -----------------------------------
>
> Key: DERBY-465
> URL: http://issues.apache.org/jira/browse/DERBY-465
> Project: Derby
> Type: Wish
> Components: Test
> Versions: 10.0.2.1, 10.0.2.0
> Environment: Windows Server 2003, 4 processors, summary CPU 3.00 Ghz, RAM 1 Gb
> Reporter: Peter Kovgan
> Attachments: Benchmarks_info_independent.doc, DBOperations.java, Multithreading-access read.doc, User.java, derby-optimization.doc, derby-pb1.doc
>
> I have tested 4 major embedded DB.
> I have found that major disadvantage of Derby is
> 1)low insert speed and
> 2)significant performance degradation in select, update, delete operation speed starting from some table size.
> PointBase in comparison has not such degradation.
> It will be better if you improve your product.
> Good luck and thank you.
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
http://www.atlassian.com/software/jira
[jira] Commented: (DERBY-465) Embedded Derby-PointBase comparison
Posted by "Mike Matrigali (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-465?page=comments#action_12316262 ]
Mike Matrigali commented on DERBY-465:
--------------------------------------
note that the results of the select * tests posted at http://jamie.ideasasylum.com/notebook/index.php?id=4, show 50,000 row select of same table
you are using takes 375 ms. on a slightly slower machine. There is definitely something strange going on with your results. I have read over
the .java files you posted and nothing jumps out at me. I recommend debugging the single user derby select test, verify number of rows and
that you aren't missing any errors.
> Embedded Derby-PointBase comparison
> -----------------------------------
>
> Key: DERBY-465
> URL: http://issues.apache.org/jira/browse/DERBY-465
> Project: Derby
> Type: Wish
> Components: Test
> Versions: 10.0.2.1, 10.0.2.0
> Environment: Windows Server 2003, 4 processors, summary CPU 3.00 Ghz, RAM 1 Gb
> Reporter: Peter Kovgan
> Attachments: Benchmarks_info_independent.doc, DBOperations.java, Multithreading-access read.doc, User.java, derby-optimization.doc, derby-pb1.doc
>
> I have tested 4 major embedded DB.
> I have found that major disadvantage of Derby is
> 1)low insert speed and
> 2)significant performance degradation in select, update, delete operation speed starting from some table size.
> PointBase in comparison has not such degradation.
> It will be better if you improve your product.
> Good luck and thank you.
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
http://www.atlassian.com/software/jira
[jira] Updated: (DERBY-465) Embedded Derby-PointBase comparison
Posted by "Peter Kovgan (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-465?page=all ]
Peter Kovgan updated DERBY-465:
-------------------------------
Attachment: Benchmarks_info_independent.doc
Test description and results.
> Embedded Derby-PointBase comparison
> -----------------------------------
>
> Key: DERBY-465
> URL: http://issues.apache.org/jira/browse/DERBY-465
> Project: Derby
> Type: Wish
> Components: Test
> Versions: 10.0.2.1, 10.0.2.0
> Environment: Windows Server 2003, 4 processors, summary CPU 3.00 Ghz, RAM 1 Gb
> Reporter: Peter Kovgan
> Attachments: Benchmarks_info_independent.doc
>
> I have tested 4 major embedded DB.
> I have found that major disadvantage of Derby is
> 1)low insert speed and
> 2)significant performance degradation in select, update, delete operation speed starting from some table size.
> PointBase in comparison has not such degradation.
> It will be better if you improve your product.
> Good luck and thank you.
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
http://www.atlassian.com/software/jira
[jira] Commented: (DERBY-465) Embedded Derby-PointBase comparison
Posted by "Mike Matrigali (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-465?page=comments#action_12316139 ]
Mike Matrigali commented on DERBY-465:
--------------------------------------
It looks like indexing has resolved the unexpected performance problem as the db grows.
As you measured without indexes, derby as implemented has no option to perform updates/deletes
other than scanning the entire table. As you measured the time to this scan is as expected linearly
related to the size of the table.
As you measured with indexes the performance of theses updates/deletes is relatively stable
from 0 to 5 million rows.
The addition of each index is expected to slightly increase the cost of each insert. In derby each
index is implemented internally as another table, so a single insert into a table with one index
internally turns into an insert into the base table and an insert into an btree table. Again it is hard
to guess how much each should take without seeing the sample data. It looks like the table
structure is (integer, varchar, integer, date, bigint, float) - but I don't see what the size of the varchar
date is expected to be.
I am guessing that the variance that you describe in derby-optimization.doc at each level is again caused
by recompiling the query, subsequent to compiling this query once derby will then reuse the query plan
for subsequent executions of the same query. This compilation is necessary as it looks like the test is
dropping and recreating the table at each of these points. Once the table is dropped the derby dependency
system will automatically notice that the current query plan is invalid and will recompile the query even
if you recreate the table with the same name and same columns. This is the same effect you noted in
your original document.
Your results with the page cache are puzzling. There are definite problems is you make the cache too big for
the OS/JVM to support and paging happens but the sizes picked don't seem to be the case. Note that
your choices for pageSize are incorrect. There are only 4 valid page sizes :Defines the page size, in bytes, for on-disk database pages for tables or indexes used during table or index creation. Page size can only be one the following values: 4096, 8192, 16384, or 32768. Set this property prior to issuing the CREATE TABLE or CREATE INDEX statement. This value will be used for the lifetime of the newly created conglomerates.
http://incubator.apache.org/derby/docs/10.0/manuals/tuning/perf87.html#IDX561
Again without knowing the expected size of your rows, it is hard to recommend a page size change. For this test there
are probably 2 significant cache points:
1) fit whole table/index into memory - as you add rows this becomes unreasonable
2) fit index into memory - my guess is that index fits into memory up to somewhere between 3 and 4 million rows. Assuming integer
key I used about 8 bytes per index entry as an estimate.
On space usage, again it is hard to know what is appropriate without seeing the queries. But if your application does not expect
to update the data such that it grows in size, then overall space in the database can be saved by setting the reserved space option to 0,
see:
http://incubator.apache.org/derby/docs/10.0/manuals/tuning/perf86.html#HDRSII-PROPER-28026
I also suggest that you include the version of Derby that you are using in your documents.
> Embedded Derby-PointBase comparison
> -----------------------------------
>
> Key: DERBY-465
> URL: http://issues.apache.org/jira/browse/DERBY-465
> Project: Derby
> Type: Wish
> Components: Test
> Versions: 10.0.2.1, 10.0.2.0
> Environment: Windows Server 2003, 4 processors, summary CPU 3.00 Ghz, RAM 1 Gb
> Reporter: Peter Kovgan
> Attachments: Benchmarks_info_independent.doc, derby-optimization.doc
>
> I have tested 4 major embedded DB.
> I have found that major disadvantage of Derby is
> 1)low insert speed and
> 2)significant performance degradation in select, update, delete operation speed starting from some table size.
> PointBase in comparison has not such degradation.
> It will be better if you improve your product.
> Good luck and thank you.
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
http://www.atlassian.com/software/jira
[jira] Commented: (DERBY-465) Embedded Derby-PointBase comparison
Posted by "Mike Matrigali (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-465?page=comments#action_12316259 ]
Mike Matrigali commented on DERBY-465:
--------------------------------------
A question on the derby-pb1.doc. It looks like the update statement used by derby and the one for pb use 2 totally different fields in the
update statement. Is this a problem with the test, or maybe a typo?
For the set of queries described in derby-pb1.doc, derby will need an index created on the mvalue column, to get reasonable delete/update/select
performance for a large number of rows where the subset of rows determined by the where clause is a small set of rows. The original benchmark
you based your work on, did not have any where clause queries so did not need any indexes. There is automatically an index on the primary key
you create but none of your queries actually use the primary key.
So far I am at a loss to explain your newest test results. On similar tests of selecting all rows from from a table we see results in the
100,000 rows/second range, on much slower machines than you have.
Your single user result if I read the doc right is reporting 140 seconds for 10 cycles of 100,000 rows. I would expect something in the
5-10 second range. It looks like you must have a .java file with the actual queries that extends DBOperations - any chance not the same
test was run against pb and derby?
> Embedded Derby-PointBase comparison
> -----------------------------------
>
> Key: DERBY-465
> URL: http://issues.apache.org/jira/browse/DERBY-465
> Project: Derby
> Type: Wish
> Components: Test
> Versions: 10.0.2.1, 10.0.2.0
> Environment: Windows Server 2003, 4 processors, summary CPU 3.00 Ghz, RAM 1 Gb
> Reporter: Peter Kovgan
> Attachments: Benchmarks_info_independent.doc, DBOperations.java, Multithreading-access read.doc, User.java, derby-optimization.doc, derby-pb1.doc
>
> I have tested 4 major embedded DB.
> I have found that major disadvantage of Derby is
> 1)low insert speed and
> 2)significant performance degradation in select, update, delete operation speed starting from some table size.
> PointBase in comparison has not such degradation.
> It will be better if you improve your product.
> Good luck and thank you.
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
http://www.atlassian.com/software/jira
[jira] Updated: (DERBY-465) Embedded Derby-PointBase comparison
Posted by "Peter Kovgan (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-465?page=all ]
Peter Kovgan updated DERBY-465:
-------------------------------
Attachment: DBBenchmarkProject.zip
While I'm testing and checking I want to allow you see source code of test program.
You need compile it and change paths in scripts(.bat files) to run, also parameters in scripts may be wrong, but you in general can reproduce
Several situations: multi-user access, write mode, read mode, indexing on table, auto commit=true/false and other.
I'll send you results of tests later.
> Embedded Derby-PointBase comparison
> -----------------------------------
>
> Key: DERBY-465
> URL: http://issues.apache.org/jira/browse/DERBY-465
> Project: Derby
> Type: Wish
> Components: Test
> Versions: 10.0.2.1, 10.0.2.0
> Environment: Windows Server 2003, 4 processors, summary CPU 3.00 Ghz, RAM 1 Gb
> Reporter: Peter Kovgan
> Attachments: Benchmarks_info_independent.doc, DBBenchmarkProject.zip, DBOperations.java, Multithreading-access read.doc, User.java, derby-optimization.doc, derby-pb1.doc
>
> I have tested 4 major embedded DB.
> I have found that major disadvantage of Derby is
> 1)low insert speed and
> 2)significant performance degradation in select, update, delete operation speed starting from some table size.
> PointBase in comparison has not such degradation.
> It will be better if you improve your product.
> Good luck and thank you.
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
http://www.atlassian.com/software/jira
Re: [jira] Commented: (DERBY-465) Embedded Derby-PointBase comparison
Posted by Mike Matrigali <mi...@sbcglobal.net>.
Such recommendation depends on exactly what the test is doing, I think
you were still changing the test so it is hard to know what it doing
now until you update the descriptions and send out the source. Having
said this I will give advice based one what I saw previously.
1) assuming your benchmark still does deletes and updates of 5000 based
on where clause on a single field, I would suggest:
o bump the default lock escalation from 5000 to 10000
o make sure to have a key on the field you are using for the
update/delete
2) memory depends on cache size and number of threads. Changing the
cache size depends on expected size of the database. I would not expect
need to change default memory size for the number of threads you had
before (ie. 1 to 10). I am not going to guess at cache size without
knowing database table and index size.
3) batching the inserts in units of 1000 may help, definitely 100 is
better than 10.
4) not sure what application mix you are trying to model. I believe the
app you had before always had all threads inserting, deleting and
updating the same key value. If that is what you are looking to test,
then so be it. Usually multi-user tests assume some percentage of
different keys per thread, so that it can measure multi-thread
scalability which is not totally bound by each thread locking the other
out based on logical locks. Usually the same key is less than 20% of
the total update operations.
5) you may have to alter lock timeout default, depending on the above
changes and size of database. Especially if you don't add indexes on
the key'd deletes and updates and depending on total size of database.
Peter Kovgan (JIRA) wrote:
> [ http://issues.apache.org/jira/browse/DERBY-465?page=comments#action_12316752 ]
>
> Peter Kovgan commented on DERBY-465:
> ------------------------------------
>
> Mike and other Derby hackers!
>
> Could you suggest me best parameters to run "multithreading access write" test on Derby?
>
> What lock,memory and other parameters are optimal for such test?
>
>
> I have already some results, but I must complete all tests to show whole picture.
>
> Please, advice me how to improve Derby performance in "multithreading access write" mode.
>
> I mean 2 and more threads try to insert/update/delete simultaneously.
>
> Many thanks!
>
>
>
>
>
>>Embedded Derby-PointBase comparison
>>-----------------------------------
>>
>> Key: DERBY-465
>> URL: http://issues.apache.org/jira/browse/DERBY-465
>> Project: Derby
>> Type: Wish
>> Components: Test
>> Versions: 10.0.2.1, 10.0.2.0
>> Environment: Windows Server 2003, 4 processors, summary CPU 3.00 Ghz, RAM 1 Gb
>> Reporter: Peter Kovgan
>> Attachments: Benchmarks_info_independent.doc, DBOperations.java, Multithreading-access read.doc, User.java, derby-optimization.doc, derby-pb1.doc
>>
>>I have tested 4 major embedded DB.
>>I have found that major disadvantage of Derby is
>>1)low insert speed and
>>2)significant performance degradation in select, update, delete operation speed starting from some table size.
>>PointBase in comparison has not such degradation.
>>It will be better if you improve your product.
>>Good luck and thank you.
>
>
[jira] Commented: (DERBY-465) Embedded Derby-PointBase comparison
Posted by "Peter Kovgan (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-465?page=comments#action_12316752 ]
Peter Kovgan commented on DERBY-465:
------------------------------------
Mike and other Derby hackers!
Could you suggest me best parameters to run "multithreading access write" test on Derby?
What lock,memory and other parameters are optimal for such test?
I have already some results, but I must complete all tests to show whole picture.
Please, advice me how to improve Derby performance in "multithreading access write" mode.
I mean 2 and more threads try to insert/update/delete simultaneously.
Many thanks!
> Embedded Derby-PointBase comparison
> -----------------------------------
>
> Key: DERBY-465
> URL: http://issues.apache.org/jira/browse/DERBY-465
> Project: Derby
> Type: Wish
> Components: Test
> Versions: 10.0.2.1, 10.0.2.0
> Environment: Windows Server 2003, 4 processors, summary CPU 3.00 Ghz, RAM 1 Gb
> Reporter: Peter Kovgan
> Attachments: Benchmarks_info_independent.doc, DBOperations.java, Multithreading-access read.doc, User.java, derby-optimization.doc, derby-pb1.doc
>
> I have tested 4 major embedded DB.
> I have found that major disadvantage of Derby is
> 1)low insert speed and
> 2)significant performance degradation in select, update, delete operation speed starting from some table size.
> PointBase in comparison has not such degradation.
> It will be better if you improve your product.
> Good luck and thank you.
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
http://www.atlassian.com/software/jira
[jira] Commented: (DERBY-465) Embedded Derby-PointBase comparison
Posted by "Peter Kovgan (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-465?page=comments#action_12316222 ]
Peter Kovgan commented on DERBY-465:
------------------------------------
First of all:
Thank you for your answers and good suggestions, I'll try to provide all information I have to describe my benchmark.
Trust me I'm not PointBase advertiser and I have no intention to attack Derby, contrariwise I just want to resolve all these issues to find out better embedded DB for our project. Perhaps my benchmark based on wrong assumptions and may be my lack of knowledge leads me in wrong direction.
Due to corporate policy ( this benchmark is not my own ) I can't publish all my source code and all requirements I have to test.
But I can provide table structure, and clear test description.
And I'll do it ASAP together with clarification of log status on PointBase.
Thanks!
> Embedded Derby-PointBase comparison
> -----------------------------------
>
> Key: DERBY-465
> URL: http://issues.apache.org/jira/browse/DERBY-465
> Project: Derby
> Type: Wish
> Components: Test
> Versions: 10.0.2.1, 10.0.2.0
> Environment: Windows Server 2003, 4 processors, summary CPU 3.00 Ghz, RAM 1 Gb
> Reporter: Peter Kovgan
> Attachments: Benchmarks_info_independent.doc, derby-optimization.doc
>
> I have tested 4 major embedded DB.
> I have found that major disadvantage of Derby is
> 1)low insert speed and
> 2)significant performance degradation in select, update, delete operation speed starting from some table size.
> PointBase in comparison has not such degradation.
> It will be better if you improve your product.
> Good luck and thank you.
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
http://www.atlassian.com/software/jira
[jira] Commented: (DERBY-465) Embedded Derby-PointBase comparison
Posted by "Sunitha Kambhampati (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-465?page=comments#action_12316126 ]
Sunitha Kambhampati commented on DERBY-465:
-------------------------------------------
I am posting the comment that Mike Matrigali posted to derby-dev yesterday.
Mike Matrigali <quote>
I can't tell from your report, are you testing each operation as a
separate transaction? Any chance you can post your test programs?
If you can't do that, at least the DDL of the table and index
creates would help.
A major difference between derby and some other db's is that by
default it guarantees transaction consistency with each transaction
commit. This means that it does a synchronous I/O with every
transaction commit, and thus update benchmarks which do one operation
per commit single user are not very interesting as they simply
measure the I/O throughput of the disk.
I don't see in the document what kind of disk you are using. Also for
Derby it is important to list what JVM you used to test.
I would be interested in helping you configure the test as appropriate
for Derby, and am interested at looking at the large DB results.
</quote>
> Embedded Derby-PointBase comparison
> -----------------------------------
>
> Key: DERBY-465
> URL: http://issues.apache.org/jira/browse/DERBY-465
> Project: Derby
> Type: Wish
> Components: Test
> Versions: 10.0.2.1, 10.0.2.0
> Environment: Windows Server 2003, 4 processors, summary CPU 3.00 Ghz, RAM 1 Gb
> Reporter: Peter Kovgan
> Attachments: Benchmarks_info_independent.doc, derby-optimization.doc
>
> I have tested 4 major embedded DB.
> I have found that major disadvantage of Derby is
> 1)low insert speed and
> 2)significant performance degradation in select, update, delete operation speed starting from some table size.
> PointBase in comparison has not such degradation.
> It will be better if you improve your product.
> Good luck and thank you.
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
http://www.atlassian.com/software/jira
[jira] Commented: (DERBY-465) Embedded Derby-PointBase comparison
Posted by "Peter Kovgan (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-465?page=comments#action_12316316 ]
Peter Kovgan commented on DERBY-465:
------------------------------------
Mike,
thank you for your comments.
They are all helpful.
1. Really : update is differ and this is my mistake and I'll take care. New tests will be with the same update in both DB.
2. My second mistake is in last document, describing threads: I tested thread in 100 cycles, not 10.
(but also PB was tested with 100 loops, so comparison is right for 100 cycles, because I have no mistake in select)
I also did not indexed PB. PB and Derby are in default configuration. Yes , PB does not write every transaction in log, but it also tested only for "READ" access in SELECT and here I shouldn't expect logs? right?
3. Yes, I extend DBOperations for Derby and for PB, next time I'll send all relevant files and updated results.
So, excuse me for my mistaken docs.
I hope shortly I'll update them.
> Embedded Derby-PointBase comparison
> -----------------------------------
>
> Key: DERBY-465
> URL: http://issues.apache.org/jira/browse/DERBY-465
> Project: Derby
> Type: Wish
> Components: Test
> Versions: 10.0.2.1, 10.0.2.0
> Environment: Windows Server 2003, 4 processors, summary CPU 3.00 Ghz, RAM 1 Gb
> Reporter: Peter Kovgan
> Attachments: Benchmarks_info_independent.doc, DBOperations.java, Multithreading-access read.doc, User.java, derby-optimization.doc, derby-pb1.doc
>
> I have tested 4 major embedded DB.
> I have found that major disadvantage of Derby is
> 1)low insert speed and
> 2)significant performance degradation in select, update, delete operation speed starting from some table size.
> PointBase in comparison has not such degradation.
> It will be better if you improve your product.
> Good luck and thank you.
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
http://www.atlassian.com/software/jira
[jira] Updated: (DERBY-465) Embedded Derby-PointBase comparison
Posted by "Peter Kovgan (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-465?page=all ]
Peter Kovgan updated DERBY-465:
-------------------------------
Attachment: dbbenchmark_multi_user_04_08_2005.zip
Here are last results + updated source code.
According to results Derby is winner(in certain circumstances).
Good luck, folks!
> Embedded Derby-PointBase comparison
> -----------------------------------
>
> Key: DERBY-465
> URL: http://issues.apache.org/jira/browse/DERBY-465
> Project: Derby
> Type: Wish
> Components: Test
> Versions: 10.0.2.1, 10.0.2.0
> Environment: Windows Server 2003, 4 processors, summary CPU 3.00 Ghz, RAM 1 Gb
> Reporter: Peter Kovgan
> Attachments: Benchmarks_info_independent.doc, DBBenchmarkProject.zip, DBOperations.java, Multithreading-access read.doc, User.java, dbbenchmark_multi_user_04_08_2005.zip, derby-optimization.doc, derby-pb1.doc
>
> I have tested 4 major embedded DB.
> I have found that major disadvantage of Derby is
> 1)low insert speed and
> 2)significant performance degradation in select, update, delete operation speed starting from some table size.
> PointBase in comparison has not such degradation.
> It will be better if you improve your product.
> Good luck and thank you.
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
http://www.atlassian.com/software/jira
[jira] Commented: (DERBY-465) Embedded Derby-PointBase comparison
Posted by "Peter Kovgan (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-465?page=comments#action_12316317 ]
Peter Kovgan commented on DERBY-465:
------------------------------------
Also important: last SELECT-multithreading test was done with SELECT without WHERE clause.
Just SELECT * FROM table.
> Embedded Derby-PointBase comparison
> -----------------------------------
>
> Key: DERBY-465
> URL: http://issues.apache.org/jira/browse/DERBY-465
> Project: Derby
> Type: Wish
> Components: Test
> Versions: 10.0.2.1, 10.0.2.0
> Environment: Windows Server 2003, 4 processors, summary CPU 3.00 Ghz, RAM 1 Gb
> Reporter: Peter Kovgan
> Attachments: Benchmarks_info_independent.doc, DBOperations.java, Multithreading-access read.doc, User.java, derby-optimization.doc, derby-pb1.doc
>
> I have tested 4 major embedded DB.
> I have found that major disadvantage of Derby is
> 1)low insert speed and
> 2)significant performance degradation in select, update, delete operation speed starting from some table size.
> PointBase in comparison has not such degradation.
> It will be better if you improve your product.
> Good luck and thank you.
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
http://www.atlassian.com/software/jira
Re: [jira] Updated: (DERBY-465) Embedded Derby-PointBase comparison
Posted by Mike Matrigali <mi...@sbcglobal.net>.
I can't tell from your report, are you testing each operation as a
separate transaction? Any chance you can post your test programs?
If you can't do that, at least the DDL of the table and index
creates would help.
A major difference between derby and some other db's is that by
default it guarantees transaction consistency with each transaction
commit. This means that it does a synchronous I/O with every
transaction commit, and thus update benchmarks which do one operation
per commit single user are not very interesting as they simply
measure the I/O throughput of the disk.
I don't see in the document what kind of disk you are using. Also for
Derby it is important to list what JVM you used to test.
I would be interested in helping you configure the test as appropriate
for Derby, and am interested at looking at the large DB results.
Peter Kovgan (JIRA) wrote:
> [ http://issues.apache.org/jira/browse/DERBY-465?page=all ]
>
> Peter Kovgan updated DERBY-465:
> -------------------------------
>
> Attachment: Benchmarks_info_independent.doc
>
>
>>Embedded Derby-PointBase comparison
>>-----------------------------------
>>
>> Key: DERBY-465
>> URL: http://issues.apache.org/jira/browse/DERBY-465
>> Project: Derby
>> Type: Wish
>> Components: Test
>> Versions: 10.0.2.1, 10.0.2.0
>> Environment: Windows Server 2003, 4 processors, summary CPU 3.00 Ghz, RAM 1 Gb
>> Reporter: Peter Kovgan
>> Attachments: Benchmarks_info_independent.doc, Benchmarks_info_independent.doc
>>
>>I have tested 4 major embedded DB.
>>I have found that major disadvantage of Derby is
>>1)low insert speed and
>>2)significant performance degradation in select, update, delete operation speed starting from some table size.
>>PointBase in comparison has not such degradation.
>>It will be better if you improve your product.
>>Good luck and thank you.
>
>
[jira] Updated: (DERBY-465) Embedded Derby-PointBase comparison
Posted by "Peter Kovgan (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-465?page=all ]
Peter Kovgan updated DERBY-465:
-------------------------------
Attachment: Benchmarks_info_independent.doc
> Embedded Derby-PointBase comparison
> -----------------------------------
>
> Key: DERBY-465
> URL: http://issues.apache.org/jira/browse/DERBY-465
> Project: Derby
> Type: Wish
> Components: Test
> Versions: 10.0.2.1, 10.0.2.0
> Environment: Windows Server 2003, 4 processors, summary CPU 3.00 Ghz, RAM 1 Gb
> Reporter: Peter Kovgan
> Attachments: Benchmarks_info_independent.doc, Benchmarks_info_independent.doc
>
> I have tested 4 major embedded DB.
> I have found that major disadvantage of Derby is
> 1)low insert speed and
> 2)significant performance degradation in select, update, delete operation speed starting from some table size.
> PointBase in comparison has not such degradation.
> It will be better if you improve your product.
> Good luck and thank you.
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
http://www.atlassian.com/software/jira
[jira] Updated: (DERBY-465) Embedded Derby-PointBase comparison
Posted by "Peter Kovgan (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-465?page=all ]
Peter Kovgan updated DERBY-465:
-------------------------------
Attachment: derby-optimization.doc
I have found that Derby may be optimized by INDEXING.
Cache increment does not help.
results in attached file.
> Embedded Derby-PointBase comparison
> -----------------------------------
>
> Key: DERBY-465
> URL: http://issues.apache.org/jira/browse/DERBY-465
> Project: Derby
> Type: Wish
> Components: Test
> Versions: 10.0.2.1, 10.0.2.0
> Environment: Windows Server 2003, 4 processors, summary CPU 3.00 Ghz, RAM 1 Gb
> Reporter: Peter Kovgan
> Attachments: Benchmarks_info_independent.doc, derby-optimization.doc
>
> I have tested 4 major embedded DB.
> I have found that major disadvantage of Derby is
> 1)low insert speed and
> 2)significant performance degradation in select, update, delete operation speed starting from some table size.
> PointBase in comparison has not such degradation.
> It will be better if you improve your product.
> Good luck and thank you.
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
http://www.atlassian.com/software/jira
[jira] Commented: (DERBY-465) Embedded Derby-PointBase comparison
Posted by "Peter Kovgan (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-465?page=comments#action_12316319 ]
Peter Kovgan commented on DERBY-465:
------------------------------------
I have also errors in my new WRITE-multithreading test:
Description:
1 thread runs:
Thread Cycle:{
openConnection()
insert(5000 rows, with commit per 10 rows)
update(affects 5 000 rows)
delete(affects 5 000 rows)
closeConnection()
}
Error is on update and on delete, it seems that error may be because connection was not closed after insert:
ERROR 40XL1: A lock could not be obtained within the time requested
at org.apache.derby.iapi.error.StandardException.newException(StandardException.java)
at org.apache.derby.impl.services.locks.LockSet.lockObject(LockSet.java)
at org.apache.derby.impl.services.locks.SinglePool.lockAnObject(SinglePool.java)
at org.apache.derby.impl.services.locks.SinglePool.lockObject(SinglePool.java)
at org.apache.derby.impl.store.raw.xact.RowLocking2.lockRecordForRead(RowLocking2.java)
at org.apache.derby.impl.store.access.conglomerate.OpenConglomerate.lockPositionForRead(OpenConglomerate.java)
at org.apache.derby.impl.store.access.conglomerate.GenericScanController.fetchRows(GenericScanController.java)
at org.apache.derby.impl.store.access.heap.HeapScan.fetchNext(HeapScan.java)
at org.apache.derby.impl.sql.execute.TableScanResultSet.getNextRowCore(TableScanResultSet.java)
at org.apache.derby.impl.sql.execute.ProjectRestrictResultSet.getNextRowCore(ProjectRestrictResultSet.java)
at org.apache.derby.impl.sql.execute.NormalizeResultSet.getNextRowCore(NormalizeResultSet.java)
at org.apache.derby.impl.sql.execute.DMLWriteResultSet.getNextRowCore(DMLWriteResultSet.java)
at org.apache.derby.impl.sql.execute.UpdateResultSet.collectAffectedRows(UpdateResultSet.java)
at org.apache.derby.impl.sql.execute.UpdateResultSet.open(UpdateResultSet.java)
at org.apache.derby.impl.sql.GenericPreparedStatement.execute(GenericPreparedStatement.java)
at org.apache.derby.impl.jdbc.EmbedStatement.executeStatement(EmbedStatement.java)
at org.apache.derby.impl.jdbc.EmbedPreparedStatement.executeStatement(EmbedPreparedStatement.java)
at org.apache.derby.impl.jdbc.EmbedPreparedStatement.execute(EmbedPreparedStatement.java)
at com.bmc.dbtest.threads.DBOperations.testUpdate(Unknown Source)
at com.bmc.dbtest.threads.User.performUpdate(Unknown Source)
at com.bmc.dbtest.threads.User.runCycle(Unknown Source)
at com.bmc.dbtest.threads.User.run(Unknown Source)
ERROR 40XL1: A lock could not be obtained within the time requested
at org.apache.derby.iapi.error.StandardException.newException(StandardException.java)
at org.apache.derby.impl.services.locks.LockSet.lockObject(LockSet.java)
at org.apache.derby.impl.services.locks.SinglePool.lockAnObject(SinglePool.java)
at org.apache.derby.impl.services.locks.SinglePool.lockObject(SinglePool.java)
at org.apache.derby.impl.store.raw.xact.RowLocking2.lockRecordForRead(RowLocking2.java)
at org.apache.derby.impl.store.access.conglomerate.OpenConglomerate.lockPositionForRead(OpenConglomerate.java)
at org.apache.derby.impl.store.access.conglomerate.GenericScanController.fetchRows(GenericScanController.java)
at org.apache.derby.impl.store.access.heap.HeapScan.fetchNext(HeapScan.java)
at org.apache.derby.impl.sql.execute.TableScanResultSet.getNextRowCore(TableScanResultSet.java)
at org.apache.derby.impl.sql.execute.ProjectRestrictResultSet.getNextRowCore(ProjectRestrictResultSet.java)
at org.apache.derby.impl.sql.execute.DMLWriteResultSet.getNextRowCore(DMLWriteResultSet.java)
at org.apache.derby.impl.sql.execute.DeleteResultSet.setup(DeleteResultSet.java)
at org.apache.derby.impl.sql.execute.DeleteResultSet.open(DeleteResultSet.java)
at org.apache.derby.impl.sql.GenericPreparedStatement.execute(GenericPreparedStatement.java)
at org.apache.derby.impl.jdbc.EmbedStatement.executeStatement(EmbedStatement.java)
at org.apache.derby.impl.jdbc.EmbedPreparedStatement.executeStatement(EmbedPreparedStatement.java)
at org.apache.derby.impl.jdbc.EmbedPreparedStatement.execute(EmbedPreparedStatement.java)
at com.bmc.dbtest.threads.DBOperations.makeDelete(Unknown Source)
at com.bmc.dbtest.threads.User.performDelete(Unknown Source)
at com.bmc.dbtest.threads.User.runCycle(Unknown Source)
at com.bmc.dbtest.threads.User.run(Unknown Source)
> Embedded Derby-PointBase comparison
> -----------------------------------
>
> Key: DERBY-465
> URL: http://issues.apache.org/jira/browse/DERBY-465
> Project: Derby
> Type: Wish
> Components: Test
> Versions: 10.0.2.1, 10.0.2.0
> Environment: Windows Server 2003, 4 processors, summary CPU 3.00 Ghz, RAM 1 Gb
> Reporter: Peter Kovgan
> Attachments: Benchmarks_info_independent.doc, DBOperations.java, Multithreading-access read.doc, User.java, derby-optimization.doc, derby-pb1.doc
>
> I have tested 4 major embedded DB.
> I have found that major disadvantage of Derby is
> 1)low insert speed and
> 2)significant performance degradation in select, update, delete operation speed starting from some table size.
> PointBase in comparison has not such degradation.
> It will be better if you improve your product.
> Good luck and thank you.
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
http://www.atlassian.com/software/jira
[jira] Updated: (DERBY-465) Embedded Derby-PointBase comparison
Posted by "Peter Kovgan (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-465?page=all ]
Peter Kovgan updated DERBY-465:
-------------------------------
Attachment: User.java
DBOperations.java
Multithreading-access read.doc
A also tested multiuser access.
attached User.java file is - thread accessing DB.
DBOperations.java - it is how I call DB.
Multithreading-access read.doc - test description.
> Embedded Derby-PointBase comparison
> -----------------------------------
>
> Key: DERBY-465
> URL: http://issues.apache.org/jira/browse/DERBY-465
> Project: Derby
> Type: Wish
> Components: Test
> Versions: 10.0.2.1, 10.0.2.0
> Environment: Windows Server 2003, 4 processors, summary CPU 3.00 Ghz, RAM 1 Gb
> Reporter: Peter Kovgan
> Attachments: Benchmarks_info_independent.doc, DBOperations.java, Multithreading-access read.doc, User.java, derby-optimization.doc, derby-pb1.doc
>
> I have tested 4 major embedded DB.
> I have found that major disadvantage of Derby is
> 1)low insert speed and
> 2)significant performance degradation in select, update, delete operation speed starting from some table size.
> PointBase in comparison has not such degradation.
> It will be better if you improve your product.
> Good luck and thank you.
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
http://www.atlassian.com/software/jira
Re: [jira] Commented: (DERBY-465) Embedded Derby-PointBase comparison
Posted by Øystein Grøvlen <Oy...@Sun.COM>.
Peter Kovgan (JIRA) wrote:
> [ http://issues.apache.org/jira/browse/DERBY-465?page=comments#action_12316327 ]
>
> Peter Kovgan commented on DERBY-465:
> ------------------------------------
>
> previous error is result of low derby.locks.escalationThreshold
>
>
> But I don't understand the reason of this error....
> 5000 rows is default , if operation affects more then 5000 rows, then DB locks whole table.
> OK...
>
> but it seems that after whole table was locked, DB attempts to lock single row...
>
> Is it bug?
> Or I missed something?
>
Do you run just a single thread, or several threads in parallell?
--
Øystein
[jira] Commented: (DERBY-465) Embedded Derby-PointBase comparison
Posted by "Peter Kovgan (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-465?page=comments#action_12316327 ]
Peter Kovgan commented on DERBY-465:
------------------------------------
previous error is result of low derby.locks.escalationThreshold
But I don't understand the reason of this error....
5000 rows is default , if operation affects more then 5000 rows, then DB locks whole table.
OK...
but it seems that after whole table was locked, DB attempts to lock single row...
Is it bug?
Or I missed something?
> Embedded Derby-PointBase comparison
> -----------------------------------
>
> Key: DERBY-465
> URL: http://issues.apache.org/jira/browse/DERBY-465
> Project: Derby
> Type: Wish
> Components: Test
> Versions: 10.0.2.1, 10.0.2.0
> Environment: Windows Server 2003, 4 processors, summary CPU 3.00 Ghz, RAM 1 Gb
> Reporter: Peter Kovgan
> Attachments: Benchmarks_info_independent.doc, DBOperations.java, Multithreading-access read.doc, User.java, derby-optimization.doc, derby-pb1.doc
>
> I have tested 4 major embedded DB.
> I have found that major disadvantage of Derby is
> 1)low insert speed and
> 2)significant performance degradation in select, update, delete operation speed starting from some table size.
> PointBase in comparison has not such degradation.
> It will be better if you improve your product.
> Good luck and thank you.
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
http://www.atlassian.com/software/jira
[jira] Commented: (DERBY-465) Embedded Derby-PointBase comparison
Posted by "Peter Kovgan (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-465?page=comments#action_12316233 ]
Peter Kovgan commented on DERBY-465:
------------------------------------
Summary time (in seconds) - it is for multithreading results
> Embedded Derby-PointBase comparison
> -----------------------------------
>
> Key: DERBY-465
> URL: http://issues.apache.org/jira/browse/DERBY-465
> Project: Derby
> Type: Wish
> Components: Test
> Versions: 10.0.2.1, 10.0.2.0
> Environment: Windows Server 2003, 4 processors, summary CPU 3.00 Ghz, RAM 1 Gb
> Reporter: Peter Kovgan
> Attachments: Benchmarks_info_independent.doc, DBOperations.java, Multithreading-access read.doc, User.java, derby-optimization.doc, derby-pb1.doc
>
> I have tested 4 major embedded DB.
> I have found that major disadvantage of Derby is
> 1)low insert speed and
> 2)significant performance degradation in select, update, delete operation speed starting from some table size.
> PointBase in comparison has not such degradation.
> It will be better if you improve your product.
> Good luck and thank you.
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
http://www.atlassian.com/software/jira
[jira] Commented: (DERBY-465) Embedded Derby-PointBase comparison
Posted by "Mike Matrigali (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-465?page=comments#action_12316402 ]
Mike Matrigali commented on DERBY-465:
--------------------------------------
1) I look forward to your subsequent posting the files you are using get your results. It seems important when publishing
performance results to make it possible for others to reproduce your work.
This will also help me be able to understand the problems you are running into.
2) For multi-user read tests, given that the results are for 100 cyles, which means each thread is retrieving 100*100,000 rows, then the
numbers for Derby seem at least to be the right order of magnitude. The 10 user number is a little slower than I would have guessed,
I was hoping for somthing in the range of 3 times the 2 user number.
We see approximately 100,000 rows/second for a single thread. If you plan on doing
any more runs, here are the thread numbers I would use for a 4 processor machine: 1, 2, 3, 4, 8, 16. These numbers can show
better how the tested db is scaling across the available processors. Especially for the select run
I would expect Derby to have relatively flat elapsed time from 2,3,4 and then start to see a steady increase as more threads are
added.
Are you sure there were no problems with your PB runs? It definitely seems strange that the 1 user run is more than the 2, 5, and
10 user runs. And retrieving and processing 10,000,000 rows in 1 second seems unlikely. In my work in the past sometimes
I would get problems in my setup and get results like this, and not see them in the program especially if like in your tests there
is no sanity checking to see that the expected number of rows are processed and/or the right results are returned.
3) from description of tests or the included source code I could not tell the following:
o are multi-user tests auto commit or not in the delete/update phase.
o if they are not auto commit, when does the commit happen? I think the isAutoCommit flag is set in a module that you have not posted.
4) lock escalation/timeout issues
o I can answer better once I know answer to 3, but I am going to assume the test is not auto commit as you are committing
the insert every 10 rows, so am going to assume the update and delete happen in on transaction.
o As you found your test happened to exactly match the lock escalation threshhold. For best multi-user operation I suggest you
raise the lock escalation threshhold to something like 10000.
o In answer to your question, derby will attempt to escalate to table locking once it reaches the threshold, but if it can't get the
the table level lock then it will continue to get row locks - it was decided it was better to continue to use more memory rather
than fail the statement at this point. This process is time consuming so for better performance if you
expect an application to run concurrently and get more that 5000 locks it is recommended you raise the escalate threshhold.
It would seem in your concurrent application, that escalation other than the single user case is almost never possible (but is totally timing dependent).
So as expected the update or delete is waiting on a row lock that is blocked because another thread has the row locked
for longer than the lock timeout threshhold.
I am not sure if somehow your program is not committing as expected, or if without an appropriate index the update and delete operations
are taking too long, causing time outs in other threads waiting on them. To debug this issue I recommend using the above mentioned faq to
enable printing the lock table when the timeout occurs - but warn that it is probably going to be very large with 1000's of row locks.
5) To get a more direct comparison would you consider running pointbase with log.forcewrite=true. This is especially appropriate if your target
application depends on the recoverability of committed transactions.
> Embedded Derby-PointBase comparison
> -----------------------------------
>
> Key: DERBY-465
> URL: http://issues.apache.org/jira/browse/DERBY-465
> Project: Derby
> Type: Wish
> Components: Test
> Versions: 10.0.2.1, 10.0.2.0
> Environment: Windows Server 2003, 4 processors, summary CPU 3.00 Ghz, RAM 1 Gb
> Reporter: Peter Kovgan
> Attachments: Benchmarks_info_independent.doc, DBOperations.java, Multithreading-access read.doc, User.java, derby-optimization.doc, derby-pb1.doc
>
> I have tested 4 major embedded DB.
> I have found that major disadvantage of Derby is
> 1)low insert speed and
> 2)significant performance degradation in select, update, delete operation speed starting from some table size.
> PointBase in comparison has not such degradation.
> It will be better if you improve your product.
> Good luck and thank you.
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
http://www.atlassian.com/software/jira