BDB or not BDB - that is the question...
At the beginning of the project I had several options for president storage for CppCMS:
- Use standard SQL databases like MySQL, PostgreSQL.
- Use embedded SQL data base like Sqlite3
- Use Berkeley DB
- Implement my own storage model.
At the beginning I wanted to use MySQL. At certain point I decided to switch to Berkeley DB, and now I come back to the original question: "what data storage to use?"
In order to make a proper design I had run lots of micro benchmarks and finally I had chosen Berkeley DB. However I never used queries from real DB. And now, when I had implemented a simple blog I decided to run benchmarks on the situation that is closer to real one.
I had reimplemented an operation of fetching single post using MySQL and imported the database that included 5000 articles with 10 comments each one and tested.
First of all I had found that there is almost no performance difference in number of pages per second between MySQL and Berkeley DB driven web site. However it was clear for me that an amount of the time that the system had spend in kernel mode was significantly higher with MySQL then with BDB. It was obvious that I had done some something wrong.
So I prepared a simple program that had queried Berkeley DB and MySQL in exact way the application does. And I had found that MySQL and BDB runs in almost same speed, when MySQL application spends about 40% of time in kernel mode (IPC between server and client).
After looking deeply I had found my big design mistake I done with planning my DB. In order to simplify and BDB interface I had separated table that contains texts from the tables that contains meta data. Thus each query had required additional join on another table that had added about 40% of execution time. I hadn't craated such separated table In MySQL, but about same time was wasted by MySQL client on IPC to the server.
I had changed the structure of my BDB tables and run tests once more:
===========================================
Database | runtime for | pages per
| 15,000 queries | second
==========================================
MySQL MyISAM | 2.75 | 5,450
MySQL InnoDB | 2.77 | 5,420
BDB Old Tables | 2.36 | 6,340
BDB New Tables | 1.67 | 8,980
==========================================
Berkeley DB is about 65% faster then MySQL because it does not waste time on IPC. On the other hand, it is not so much faster. Also I hadn't used any kind of optimizations for MySQL as prepared statements etc.
Many questions and no answers...
Berkeley DB is indeed faster, but isn't the price for that 65% of performance too high?
When I work with BDB, I do miss things like simple queries for development purposes, I still have no idea how to upgrade my DB if I add a new field and much more.
On the other hand, I want to work with my DB as I work with STL and not to use some fancy staff like ORM or embed SQL to my C++ programs.
And usability? How many developers know what Berkeley DB is and how many know what SQL is? The answer is obvious. On the other hand, how many web developers know to design indexes correctly and write good queries? The answer is obvious as well... (BTW, learning BDB had helped me a lot in writing better SQL)
So... Many questions, many points to think about.
Comments
One big difference between MySQL and BDB is that MySQL can easily be tiered, you can put it on another machine, cluster it when needed, backup it easily and peoples already know how to do all these things.
Going with what is common would be a better choice for you than going with what is faster. You are already doing one uncommon thing, having too much of it would drive people away. IMNSHO.
Yes, the point you are talking about is very important.
That is actually one of the most important reasons that I reconsider (My/Postgre/XYZ)SQL as primary data storage.
MySQL have few features that can really help you understanding what is it that you are doing wrong such as: "describe select tbl_comment.id, comment, user, email, site, from tbl_comments, tbl_article where article_id=tbl_article"
Then you will know what it takes for MySQL to execute such query...
Another thing is to use cache on the tables. The reason is simple... you extract/read more data then you write.
And you also require to use the correct database engine in MySQL for better results. For example MyISAM is faster when you require to add a lot of data, but InnoDB is better in "select" queries.
Also you should see the queries you are writing ... for example nested queries takes longer then joins. "select *", takes longer then select with the names of the fields etc...
And last, you should see if you created the indexes correctly. that is, if you do not select things by "id", then you might not require it at all, or you do not need to make it an index at all.
Some times writing a better SQL query can make a huge difference in the speed of the result. And the more data you have, the more changes you should take in the database, because it will effect the way the database is working. So your benchmarks should tests difference sizes of data population to know where/what is the limits of the current design.
First of all about the tests I had run, all of them used indexes, actually they where SQL translations of my BDB requests. And in BDB you just can do anithing without indexes. So I'm sure that my queries where good and indexing was correct. However I'll test them with "describe", this is good point to check.
I did these benchmarks after I considered all performance issues.
The major question of using InnoDB or MyISAM is to chose between tansactional and non transactional DB. It is quite obvious that MyISAM will be faster because it does not support transactions, no the other hand sometimes transactiosn are important -- an ability to roll back your commit or complete it in atomic way.
I think that is definatly good point. Maybe I need to create several DB of several sizes and test the behavior of the system.
Efficient work with DB is a not simple, that is why there is people like DBA around.
I'm not sure if you have already decided, but why not allow for multiple databases? That way they user can decide? Django, Ruby, etc do this and it works pretty well.
Yes, I can do this easily for all SQL based one -- MySQL, PostgreSQL, SQLite3, etc.. However I can't do this for Berkeley DB because it is not based on SQL, but rather on direct C++ API, something closer to STL then to SQL queries.
Anyway, user will be able to choose what he wants. The question is an integration with CppCMS.
Actually I think I'll go on SQL solution.
I don't like approach of "lets wait to someone that knows the subject"... So looking for a DBA is not a good idea IMHO...
The more database based programs you are developing, I would expect you to improve your use SQL and/or table structure...
But that's just my point of view :)
It wasn't about to give this job to someone else, it is about to tell that knowlege of SQL is an important profession.
And defenatly I am -- the developer -- should know to write good SQL queries, to create a correct DB structures and use correct API for all these purposes.
The discussion was -- should I stik to non SQL engine of Berkeley DB with all its pros and cons or to "go back" to SQL based one.
A small nitpick about your rss on the blog - you should end the rss with 'more...' and a link or 'more after the break' so people would know that the rss isn't the whole article. This time it was obvious due to the context, but there were other times where one couldn't know for sure.
Thanks.
Thanks. Fixed.
Ok...
I think that finally I desided -- I will not use Berkeley DB as my primary data storage:
Why?
I had written a comment to this blog yestoday. The Server was reboot due to accidentual power down... The last comment is not in the DB.
The question is why? I can understand if this happens if the data was update few minutes before the crash. But after almost 24 houes?
Hi,
Just a comment about your feed. You should use text/xml as MIME type instead of text/html, so that aggregators con pick the feed
I thought using BDB was an interesting and different approach as everybody uses SQL but it looks like it's not so easy after all ... thanks
Hi Joe,
Yes, you right, I'm aware of this problem, just I had not time to fix it - because mimetime is set in external library I need to learn its API little bit more.
Finaly... I got a desigion -- I'll use SQL backend.
Add Comment:
You must enable JavaScript in order to post comments.