Android Question RDC : How realible it is to handle large data insert?

[incendio]

Hi guys,

Is any of you has experience to insert data with RDC, about 10000 - 15000 records, almost at the same time?

If you have, I hope you can share your experience about its reliability and performance.

I am planning to build app on and android device. The app will be installed on about 300 devices.
Each device will be send about 30-50 records at almost the same time.

I read, somewhere in this forum, that there are connection polling problem on RDC with Access

I am not using Access, but Firebird. Firebird is a very stable and mature database, but, I am very grateful if someone could share hints before face any problems.

Thanks in advance & Regards.

 

[Erel]

The issue with Access is not relevant to real databases.

You can see some rough numbers here: http://www.b4x.com/android/forum/threads/server-performance-measures.37502/#content

The tests were created with a B4J server. However the performance should be similar to RDC server.

 

[incendio]

Performance is ok, but how about reability?
Is there any change that some commands failed to carried on by RDC?

I am still confused about how it is works.

When there are a numbers of commands (insert/select/update/delete) received by RDC, these commands are temporary stored/polled by RDC, RDC doing some parse, pass it to database via JDBC, and finally released the poll, is this how RDS works ?

 

[Erel]

RDC is reliable. Each set of commands is executed as a single transaction. This means that the request will either succeed or fail and rollback (assuming that the database engine is working correctly). Your code should be prepared to handle errors.

RDC uses a connection pool. When a command (or set of commands) arrive it takes a connection from the pool, issues the command and returns the connection to the pool. The connection pool can be configured with the 3dp0.properties file.

 

[incendio]

It is not about errors when running sql statements.

I assume, RDC pooled all sql commands before pass it to database server via jdbc, and then emptied the poll when this sql command pass to database server.
Poll is always emptied after execute sql command and not depend whether the command is success or fail

What I concern is, during a high traffic data, could it be the pool is full so next sql command will lost? and where is the file 3dp0.properties? I don't see it on RDC's file.

There is a file named c3p0.properties, is this the same file?
The contents are :
c3p0.maxStatements=150
c3p0.maxIdleTime=1800
c3p0.idleConnectionTestPeriod=600
c3p0.checkoutTimeout=20000

The statement on that file : c3p0.maxStatements=150,
seem to me is that this is the setting for max numbers of pool, is that right?
If that is right, what happen when sql command exceed 150 statements? Is statement no 151 will be lost?

 

[keirS]

There is a file named c3p0.properties, is this the same file?
The contents are :
c3p0.maxStatements=150
c3p0.maxIdleTime=1800
c3p0.idleConnectionTestPeriod=600
c3p0.checkoutTimeout=20000

The statement on that file : c3p0.maxStatements=150,
seem to me is that this is the setting for max numbers of pool, is that right?
If that is right, what happen when sql command exceed 150 statements? Is statement no 151 will be lost?

maxStatements is to do with statement pooling. c3p0 uses connection pooling and statement pooling. Statement pooling is caching prepared SQL statements so they are quicker to execute. It has very little to do with connection pooling.

 

[Erel]

The file name is c3p0.properties.

The limit in most cases will be the database server itself.
You can configure the maximum pool size with maxPoolSize: http://www.mchange.com/projects/c3p0/#managing_pool_size

If a connection is not available then the request will wait until a connection is available or until the set timeout (which can also be configured).
Your app should be able to gracefully handle errors. Errors will happen. For example if the network connection itself is broken.

 

[incendio]

Glad that this connection pooling has so much configuration option.

So, if not mistaken, in order to make sure that all request handle properly, I should play with minPoolSize, maxPoolSize, & checkoutTimeout in this c3p0.properties, is this right?

Default configuration on RDC :
- maxPoolSize = 15
- timeout = 20 secs

I have about 300 devices that send command to RDC, will try to increase maxPoolSize = 150, maxStatements = 1500 and will post the result, in case someone else need this info.

 

[Erel]

Can your database server manage 150 open connections? Seems a bit high to me. It is not needed for 300 devices. Most commands take a few milliseconds to run. Nothing bad happens if a command is queued for a few seconds.

 

[incendio]

Is there a way to know how many open connection from RDC can be managed by my database server?

On LAN, on windows os, this database server can manage about 60 connection without problem.
I have a small monitoring program, database server caprocessed 200 sql commands per sec on a peak network traffic.

 

[Erel]

Don't confuse commands per seconds with open connections.

As I wrote, most commands take a few milliseconds.

Is there a way to know how many open connection from RDC can be managed by my database server?

It really depends on your sever configuration and actual performance. For example in MySQL the default value is 100 or 150.

Eventually you will need to do some tests and see whether you encounter any performance issues or not.

 

[incendio]

Sorry for misunderstood connection with commands.

There are a 3 model server configuration in firebird. Different configuration will have different connection setting. I never take a notice about this connection setting, all leave in its default values.

Also, 32bit an 64bit OS has different capability. Manual says on 32bit OS, Superserver setting on Firebird can handle up to 400 connections, 64bit OS can handle more connections.

I am using 64bit and have enough RAM, so I guess, it won't be a problem and will do some test to raise maxPoolSize step by step.