Home  /  RSS  /  RSS Comments  /  Enter

Berkeley DB Out, MySQL In...

Tuesday, February 26, 2008, by artyom ; Posted in: Storage, Berkeley DB; 7 comments

After long period of tests and thoughts I had finally decided to move from Berkeley DB to traditional data base that is used for web development.

I had choosen soci library as a backend that provides a universal C++ driver that gives access to all popular data bases:

  1. MySQL
  2. PostgreSQL
  3. Sqlite3
  4. Firebird
  5. Oracle
  6. MS SQL via odbc.

About SOCI

I had used latest CVS version of this library that goes towards "boostification". I had implemented several patches to this library in order make it more useful for this project. I had send them to developers and they are waiting to be merged to CVS tree.

It gives very friendly interface to access DB for example:

sql<<"INSERT INTO posts (id,content) VALUES(:id,:text)",
        use(id),use(sometext);

This is not only good syntactic sugar it is also provides automatic escaping to "sometext" etc. So, even it is still not in perfect shape it was well designed and gives really easy access to DB.

For example: STL like access to query results:

rowset<> rs=(sql.prepare<<"SELECT id,name "
                          "FROM users "
                          "ORDER BY id DESC "
                          "WHERE group=:gr "
                          "LIMIT 5",
                          use(group));
rowset<>::const_iterator r;
for(r=rs.begin();r!=rs.end();r++){
   *r>>id>>name;
   cout<<format("User #%1% has name %2%\n") % id %name;
}

At this point, I had removed any DB support from the framework because SOCI gives all tools to final application developer, so such support is not mandatory like it was in case of Berkeley DB.

This article was written on CMS that already uses MySQL backend. I must admit that, both PostgreSQL and Sqlite3 are supported for this blog as well.

Comments

elcuco, at 2/27/08 10:02 AM

How about performance? Is "bad" is SQLite3 compared to MySQL for this blog? How about PgSQL? can you provide some benchmarks for this system (I know you did....)

artyom, at 2/27/08 2:29 PM

Ok, I still hadn't run exact benchmarks because I had issues with PgSQL backend that reduced performace to 3 pages per second (WTF) because of incorrect use of prepared statements. Also I had issues with incorrect indexes (or to be more exact different beackends used different indexes).

Thus I had not time to show the final result. I still need to do correct measurements.

First impression is following:

  • MySQL had given 10-20% performance reduction.
  • Sqlite3 reduced performance in 20-30%.
  • PostgreSQL had about 50% reduction.

However I didn't run the final exact benchmarks... I'll publish them when I'll get exact results

joe, at 2/28/08 8:23 PM

i posted a message yesterday and it got lost

artyom, at 2/28/08 8:39 PM

Hello Joe.

Are you sure? Is it this one:

Hi, Just a comment about your feed...

Or not? If so, that may be a bug... (it is still pre-beta version)

What exactly happen, when?

artyom, at 2/28/08 8:40 PM

Do you have java script enabled? I have some spam protection based on JS

artyom, at 2/28/08 8:48 PM

As I can see, from the web server log all "POSTs" to the comments url has apropriate records in data base - so it doesn't seems that something was lost.

Maybe you posted it to this post -- you have a one comment there

joe, at 2/29/08 7:47 PM

yes, i got it wrong thanks

Add Comment:

 
 the email would not displayed
 

You can write your messages using Markdown syntax.

You must enable JavaScript in order to post comments.

Pages

Categories