Use BoneCP as the datasource manager

[tandraschko]

Would it be possible to allow pluggable ConnectionPools in the future?
As we use BoneCP in all projects (and we are very happy with it!), this would be a great feature.

[hceylan]

Yes definitely...

I will investigate that try to implement it as soon as possible.

[hceylan]

Dear Thomas,

Actually I am not happy with the built-in connection pooling right now.

If you suggest, I'll try to embed BoneCP into Batoo JPA as the default connection pool implementation.
They do assert that they have the best pooling,however their website is currently down and I cannot find the resources on the internet.

If you have chosen BoneCP mostly based on its performance, I wil go ahead on that.

Please advise...

[aruld]

+1 for BoneCP.

[tandraschko]

I only use it because of performance, it think its the best pool available :)

[hceylan]

Ok. Looks great. This is the first issue to handle tomorrow.

[hceylan]

Woaw, not so fast...

BEFORE
Prvdr | Total Time | JPA Time | DB Time | Name Of The Test

---

BATOO | 0000030125 | 0000000831 | 0000029293 | Criteria Test
BATOO | 0000025201 | 0000001045 | 0000024156 | Find Test
BATOO | 0000029701 | 0000000933 | 0000028768 | Jpql Test
BATOO | 0000027287 | 0000000934 | 0000026353 | Persist Test
BATOO | 0000015317 | 0000000266 | 0000015050 | Remove Test
BATOO | 0000007623 | 0000000132 | 0000007490 | Update Test

AFTER
Prvdr | Total Time | JPA Time | DB Time | Name Of The Test

---

BATOO | 0000032657 | 0000001382 | 0000031275 | Criteria Test
BATOO | 0000027103 | 0000001919 | 0000025183 | Find Test
BATOO | 0000032265 | 0000001513 | 0000030752 | Jpql Test
BATOO | 0000028828 | 0000001371 | 0000027456 | Persist Test
BATOO | 0000016102 | 0000000463 | 0000015638 | Remove Test
BATOO | 0000008046 | 0000000548 | 0000007497 | Update Test

[hceylan]

Batoo Datasource 23.68x -> Vanilla BoneCP 14.96x -> Volatile Fix 17.13x.

After Converting the BoneCPDataSource.pool from volatile to standard

Prvdr | Total Time | JPA Time | DB Time | Name Of The Test

---

BATOO | 0000031802 | 0000001238 | 0000030563 | Criteria Test
BATOO | 0000026286 | 0000001655 | 0000024630 | Find Test
BATOO | 0000031354 | 0000001338 | 0000030016 | Jpql Test
BATOO | 0000027811 | 0000001192 | 0000026619 | Persist Test
BATOO | 0000015390 | 0000000435 | 0000014954 | Remove Test
BATOO | 0000007647 | 0000000543 | 0000007103 | Update Test

I think I should fork and study BoneCP before using it as the default Datasource.

Thomas & Arul,

Please try Batoo JPA with the builtin datasource. If it doesn't suit your needs, then I'll give this priority. Otherwise, I will put this off for some time (Not long though meaning a few days).

Meanwhile I committed the BoneCP branchi if you need to take a look. You can change BenchmarkTest.SUMMARIZE to false if you would like to study the cost of BoneCP line-by-line.

What do you think?

[tandraschko]

Did you also tried to the partition settings and disabled statistics etc.?

[hceylan]

Yes stats is off. Looks like jsr166y.LinkedTransferQueue is expensive.

[nileshmali]

I think you have not applied Partitioning in BoneCP settings

[hceylan]

OK, Seems like the releaseHelperThreads cost more to make relese operations asynchronous then it helps.
Setting the releaseHelperThreads to 0 did the trick.

Prvdr | Total Time | JPA Time | DB Time | Name Of The Test

---

BATOO | 0000029667 | 0000000477 | 0000029190 | Criteria Test
BATOO | 0000024687 | 0000000812 | 0000023875 | Find Test
BATOO | 0000029258 | 0000000546 | 0000028711 | Jpql Test
BATOO | 0000026889 | 0000000916 | 0000025973 | Persist Test
BATOO | 0000015090 | 0000000288 | 0000014802 | Remove Test
BATOO | 0000007223 | 0000000206 | 0000007017 | Update Test

it is now even faster - 40x.

I am renaming this issue and will open a new issue for pluggable connections as this issue's content has become all BoneCP specific.

Changes has been merged to the master branch.

[hceylan]

Thomas, Arul and Nilesh,

Please advice partitioning.

Current set up is:

• volatile pool has been converted to double synchronized block
• releaseThreads set to 0.

[hceylan]

The "Implement Pluggable Datasource Inlets" issue has been recreated with a suggested structure . Please review and feedback as positive or negative.

[hceylan]

OK, I have taken a look into and definitely not fan of that. If one still wants that then they can plug BoneCP back as an external datasource as per the related issue.

4 most important settings have been incorporated.

public interface BJPASettings {

    (...)

    /**
     * The default for {@link #MAX_CONNECTIONS} that is 50.
     */
    final Integer DEFAULT_MAX_CONNECTIONS = 50;

    /**
     * The default for {@link #MIN_CONNECTIONS} that is 10.
     */
    final Integer DEFAULT_MIN_CONNECTIONS = 1;

    /**
     * The default for {@link #MIN_CONNECTIONS} that is 10.
     */
    final Integer DEFAULT_AUTO_INCREMENT = 2;

    /**
     * The default for {@link #STATEMENT_CACHE_SIZE} that is 50.
     */
    final Integer DEFAULT_STATEMENT_CACHE_SIZE = 50;

    /**
     * The size of the datasource statement cache size
     */
    final String STATEMENT_CACHE_SIZE = "org.batoo.jdbc.statement_cache_size";

    /**
     * The max size of the connection pool.
     */
    final String MAX_CONNECTIONS = "org.batoo.jdbc.max_connections";

    /**
     * The min size of the connection pool.
     */
    final String MIN_CONNECTIONS = "org.batoo.jdbc.min_connections";

    /**
     * The number of connection to increment on expanding the datasource.
     */
    final String AUTO_INCREMENT = "org.batoo.jdbc.auto_increment";

    (...)
}

I am closing the issue now. Fell free to open it in case you need further improvement.