[odb-users] how to use ODB for hight performance

[Boris Kolpackov]

Hi Herzl,

Herzl Shmuelian / Brilltech Int. <hshmuelian at brilltech.com> writes:

> I try to use it but I have Performance problem, and when I try to check the
> performance by simple program like below:
> 
> odb::mysql::database *pdb = new odb::mysql::database (
>   "odb_test","","odb_test","127.0.0.1");
> time_t t1;
> time_t t2;
> t1= time(0);
> transaction t(pdb->begin());
> person jane ("222", "Doe", 32);
> for(unsigned int i = 0 ; i < 10000000 ; i++){
>          pdb->persist (jane);
>   }
>                  t.comit();
> t2= time(0);
> cout<<"time:"t<<2-t1<<endl;

One sure way to improve the performance of the above transaction is to
split it into multiple batches. Persisting 1M of objects in a single
transaction will definitely be slow.

Here is an example (taken from the manual):

transaction t(pdb->begin());
person jane ("222", "Doe", 32);

for(unsigned int i = 0 ; i < 10000000 ; i++)
{
  if (i % 100 == 0)
  {
    t.commit ();
    t.reset (pdb->begin ());    
  }

  pdb->persist (jane);
}

t.commit();

Exactly how many objects to persist in each batch depends on your
particular situation so it is a good idea to experiment with
different sizes.


> 1) open several connection to mysql

ODB will do that if you request several connections at the same time.
So if you can perform your persist() calls from multiple threads, then
it may be worth a try. Before that, however, you may want to figure out
what the bottleneck in your case is. For example, if the CPU utilization
of your MySQL server is 100% then running persists from multiple threads
won't do you any good.


> 2) send several command together

Are you referring to bulk INSERT here? If so, then MySQL doesn't support
this feature. But if you use a database that supports it (e.g., Oracle or
MS SQL), then, yes, that would definitely help. We plan to add ODB support
for bulk INSERT for these databases in near future.


> 3) unsync send commnad to mysql

I maybe wrong, but I don't remember seeing any asynchronous statement
execution support in the MySQL C API.

Another option would be to switch to a different database. For example,
I would expect SQLite, being an in-process database, to perform much
better in this scenario.

Boris