On Thu, Jan 17, 2008 at 07:35:25PM +0300, Grigorio V. Moshkin wrote:
Hi,
Our company is searching for database storage back-end to our application that provides navigation upon legal data on Russian laws and justice practice. So we will attempt test MonetDB/SQL performance amoung other database candidates. Note, read access is much more often than write access in our business model.
1. Build notes --------------
First, I tried to build MonetDB from source code provided, but it seems too difficult for me to understand instructions. My advice, if it were possible, make ready-to-build Microsoft Visual Studio projects (*.sln, *.vcproj) to compile them immediately under Windows, and include them to Windows distribution. Because it is too hard to install and configure all build tools, such as nmake, autoconfig, etc., under Windows.
So, thanks to Stefan Manegold, I have just downloaded binary libs from monetdb.cwi.nl nighty build area. It is very easy to do that using open source program WinHTTrack, I marked the following urls for (recursively) download: ================= http://monetdb.cwi.nl/testing/projects/monetdb/Current/MonetDB5/.Int.32.32.d... http://monetdb.cwi.nl/testing/projects/monetdb/Current/MonetDB5/.Int.32.32.d... http://monetdb.cwi.nl/testing/projects/monetdb/Current/MonetDB5/.Int.32.32.d... http://monetdb.cwi.nl/testing/projects/monetdb/Current/MonetDB5/.Int.32.32.d... http://monetdb.cwi.nl/testing/projects/monetdb/Current/MonetDB5/.Int.32.32.d... http://monetdb.cwi.nl/testing/projects/monetdb/Current/sql/.Int.32.32.d-Wind... http://monetdb.cwi.nl/testing/projects/monetdb/Current/sql/.Int.32.32.d-Wind... http://monetdb.cwi.nl/testing/projects/monetdb/Current/sql/.Int.32.32.d-Wind... http://monetdb.cwi.nl/testing/projects/monetdb/Current/MonetDB/.Int.32.32.d-... http://monetdb.cwi.nl/testing/projects/monetdb/Current/clients/.Int.32.32.d-... =================
Then I have linked my program executable against: ================= libembeddedsql libMapi libmutils libbat libembeddedmal ================= and copied them (*.dll, *.lib) to the directory C:\monetdb5 (it is arbitrary named directory), and placed all the rest *.dll, *.lib, *.mal files to the subdirectory C:\monetdb5\libmonetdb5, and pointed "prefix", "exec_prefix" config option to C:\monetdb5, and pointed "monet_mod_path" config option to C:\monetdb5\libmonetdb5.
See my C++ code fragment in attach for all option set and call to embedded_sql.
I found, the call mapi_setAutocommit (dbh, 1) is significant, because data won't store to database, if I replace that call with mapi_setAutocommit (dbh, 0): there was no error indicated while saving data, Well with mapi_setAutocommit 0 you disable the autocommit. This is good for performance. So first attempt to increase your performance is to do
a mapi_setAutocommit(dbh, 0); mapi_query(dbh, "START TRANSACTION;"); /* now your queries */ mapi_query(dbh, "COMMIT;"); Niels
but then, data can't be loaded, if it were not saved to database.
After all, MonetDB/SQL embedded works under Windows!
The question is - performance.
I use the following Mapi calls to load/save data into database table: mapi_query, mapi_fetch_row, mapi_fetch_field, mapi_close_handle, as I found them in examles at MonetDB site.
Test for sequential create of 1000 rows with 1st field BIGINT and 2nd field CHAR (32), and 1000 rows with 1st field BIGINT and 2nd field CHAR (64) got 21 seconds. Test for random load of all 2000 the previously created objects got 0.17 seconds.
It is not the best performance amoung other candidates (such as Firebird, Empress, BerkeleyDB, etc.).
So the question is - How One Could Improve MonetDB/SQL Performance? I suppose, first, to try to use mapi_prepare once / mapi_execute many times, instead of mapy_query many times. What else?
void Database_i::init_embedded (const char* caller) { //#UC START# *478E12A603CD* LOG_D (("MONETDB Database_i::init_embedded (): caller = %s", caller)); if (s_dbh != 0) { LOG_D (("MONETDB Database_i::init_embedded (): s_dbh already initialized")); return; } opt* set = 0; int setlen = 0; if (!(setlen = mo_builtin_settings (&set))) { LOG_E (("MONETDB Database_i::init_embedded (): caller = %s: mo_builtin_settings error", caller)); throw SEA::DatabaseAPI::IOError (); } std::string monetdb_prefix = ::Core::ParamManager::instance ()->get_string ("-monetdb_prefix", "c:\\monetdb"); LOG_D (("monetdb_prefix = %s", monetdb_prefix.c_str ())); // needed to prevent the MonetDB config file from being used setlen = mo_add_option (&set, setlen, opt_config, "prefix", monetdb_prefix.c_str ()); setlen = mo_add_option (&set, setlen, opt_config, "exec_prefix", monetdb_prefix.c_str ()); //setlen = mo_add_option (&set, setlen, opt_config, "config", "W:\\build\\bin\\Purify_Static\\monetdb5.conf"); setlen = mo_add_option (&set, setlen, opt_config, "gdk_dbinit", "include sql;"); setlen = mo_add_option (&set, setlen, opt_config, "gdk_dbfarm", m_root_path.c_str ()); setlen = mo_add_option (&set, setlen, opt_config, "gdk_dbname", m_config.name); setlen = mo_add_option (&set, setlen, opt_config, "gdk_mem_bigsize", "262144"); setlen = mo_add_option (&set, setlen, opt_config, "gdk_vm_minsize", "137438953472"); setlen = mo_add_option (&set, setlen, opt_config, "gdk_alloc_map", "yes"); setlen = mo_add_option (&set, setlen, opt_config, "gdk_embedded", "yes"); setlen = mo_add_option (&set, setlen, opt_config, "gdk_debug", "0"); std::string monet_mod_lib = monetdb_prefix + "\\" + "libmonetdb5"; std::string monet_mod_path = monet_mod_lib + ";" + monetdb_prefix; setlen = mo_add_option (&set, setlen, opt_config, "monet_mod_path", monet_mod_path.c_str ()); setlen = mo_add_option (&set, setlen, opt_config, "monet_daemon", "yes"); setlen = mo_add_option (&set, setlen, opt_config, "monet_welcome", "yes"); std::string mero_msglog = m_root_path + "\\" + "merovingian.log"; setlen = mo_add_option (&set, setlen, opt_config, "mero_msglog", mero_msglog.c_str ()); setlen = mo_add_option (&set, setlen, opt_config, "mero_errlog", mero_msglog.c_str ()); setlen = mo_add_option (&set, setlen, opt_config, "mero_timeinterval", "600"); setlen = mo_add_option (&set, setlen, opt_config, "mero_exittimeout", "7"); std::string mal_init = monet_mod_lib + "\\" + "mal_init.mal"; setlen = mo_add_option (&set, setlen, opt_config, "mal_init", mal_init.c_str ()); setlen = mo_add_option (&set, setlen, opt_config, "mal_listing", "15"); setlen = mo_add_option (&set, setlen, opt_config, "checkpoint_dir", m_root_path.c_str ()); setlen = mo_add_option (&set, setlen, opt_config, "mapi_port", "0"); setlen = mo_add_option (&set, setlen, opt_config, "mapi_open", "false"); setlen = mo_add_option (&set, setlen, opt_config, "sql_debug", "0"); setlen = mo_add_option (&set, setlen, opt_config, "sql_logdir", m_root_path.c_str ()); std::string sql_init = monet_mod_lib + "\\" + "sql.mal"; setlen = mo_add_option (&set, setlen, opt_config, "sql_init", sql_init.c_str ()); setlen = mo_system_config (&set, setlen); LOG_D (("ACE_OS::getenv (PATH) = %s", ACE_OS::getenv ("PATH"))); s_dbh = embedded_sql (set, setlen); MapiMsg msg = mapi_error (s_dbh); if ((s_dbh == 0) || (msg != 0)) { LOG_E (("MONETDB Database_i::init_embedded (): caller = %s: mapi_error = %d", caller, (int) msg)); throw SEA::DatabaseAPI::IOError (); } MapiMsg msg1 = mapi_setAutocommit (s_dbh, 1); // significant MapiMsg msg2 = mapi_error (s_dbh); if ((msg1 != MOK) || (msg2 != 0)) { LOG_E (("MONETDB Database_i::init_embedded (): caller = %s: mapi_error msg1, msg2 = %d, %d", caller, (int) msg1, (int) msg2)); throw SEA::DatabaseAPI::IOError (); } //#UC END# *478E12A603CD* }
------------------------------------------------------------------------- This SF.net email is sponsored by: Microsoft Defy all challenges. Microsoft(R) Visual Studio 2008. http://clk.atdmt.com/MRT/go/vse0120000070mrt/direct/01/ _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users
-- Niels Nes, Centre for Mathematics and Computer Science (CWI) Kruislaan 413, 1098 SJ Amsterdam, The Netherlands room C0.02, phone ++31 20 592-4098, fax ++31 20 592-4312 url: http://www.cwi.nl/~niels e-mail: Niels.Nes@cwi.nl