Niels Nes wrote:

On Tue, Apr 07, 2009 at 02:00:36PM -0700, Yue Sheng wrote:

...

Hi, we installed MonetDB on a Linux box, and did some comparison between MySQL and MonetDB. The result seems off by a large magnitude. Has anyone encountered similar problem please? Thanks.

For MonetDB:

ql>select count(ins_key) from bigtable where ins_key = 15012; +---------+ | L1 | +=========+ | 1931541 | +---------+ 1 tuple Timer 260771.478 msec 1 rows

For MySQL:

select count(ins_key) from bigtable where ins_key=15012; +----------------+ | count(ins_key) | +----------------+ | 1931548 | +----------------+

I see 2 problems one the performance diff, but the second (more important) is the difference in result. I agree

Could you send the schema and size of the table?

The performance difference is easily explained if you look at the size of the underlying base table, if it is a cold or hot run, non-indexed, etc.. Even assuming the table was already memory resident, then automatic construction of a (hash-) index to support this query will become visible. All techniques a DBA and database application builder should be familiar with when he embarks upon building his first sizeable database and selects the engine geared best towards his task. In case a phenonemon is repetative in a lab-setting and can not be explained using the schema, database properties, and the query trace then it is worth investigating. Comparison of systems or even understanding the performance of an application on a single system can not be replaced by a simple call like this to the community. For those interested in performance evaluation and comparison of systems my find the following tutorial on this challenging task helpful http://old-www.cwi.nl/htbin/ins1/publications?request=abstract&key=MaMa:EDBT:09 Martin Kersten

Niels

...

real 0m0.989s user 0m0.002s sys 0m0.003s

Indices: MonetDB: None MySQL: index on ins_key

Thanks.

...

------------------------------------------------------------------------------ This SF.net email is sponsored by: High Quality Requirements in a Collaborative Environment. Download a free trial of Rational Requirements Composer Now! http://p.sf.net/sfu/www-ibm-com _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users

Here is the schema for bigtable. Would it be possible, rough ball-park, give in your opinion the breakdown of factors that contribute to the factor of ~260 difference vs. MySQL? Thanks. CREATE TABLE "sys"."bigtable" ( "flag" smallint, "ins_key" int, "col3" double, "col4" int, "col5" int, "col6" double, "col7" int, "col8" int, "col9" double, "col10" int, "col11" int, "col12" int, "col13" double, "col14" varchar(2) ); On Wed, Apr 8, 2009 at 12:49 AM, Yue Sheng wrote:

The difference in counts is due to simply few less records inserted for MonetDB.

Understood re: cold vs hot run. Could this explain the factor >200 magnitude difference?

Does hot run here mean running same or similar query?

I'll load the schema next time I'm in front of the DB machine.

Thanks for all the useful comments. They are very helpful.

On Wed, Apr 8, 2009 at 12:06 AM, Martin Kersten wrote:

...

Niels Nes wrote:

...

On Tue, Apr 07, 2009 at 02:00:36PM -0700, Yue Sheng wrote:

...

Hi, we installed MonetDB on a Linux box, and did some comparison between MySQL and MonetDB. The result seems off by a large magnitude. Has anyone encountered similar problem please? Thanks.

For MonetDB:

ql>select count(ins_key) from bigtable where ins_key = 15012; +---------+ | L1 | +=========+ | 1931541 | +---------+ 1 tuple Timer 260771.478 msec 1 rows

For MySQL:

select count(ins_key) from bigtable where ins_key=15012; +----------------+ | count(ins_key) | +----------------+ | 1931548 | +----------------+

I see 2 problems one the performance diff, but the second (more important) is the difference in result. I agree

Could you send the schema and size of the table?

The performance difference is easily explained if you look at the size of the underlying base table, if it is a cold or hot run, non-indexed, etc.. Even assuming the table was already memory resident, then automatic construction of a (hash-) index to support this query will become visible. All techniques a DBA and database application builder should be familiar with when he embarks upon building his first sizeable database and selects the engine geared best towards his task.

In case a phenonemon is repetative in a lab-setting and can not be explained using the schema, database properties, and the query trace then it is worth investigating. Comparison of systems or even understanding the performance of an application on a single system can not be replaced by a simple call like this to the community.

For those interested in performance evaluation and comparison of systems my find the following tutorial on this challenging task helpful

http://old-www.cwi.nl/htbin/ins1/publications?request=abstract&key=MaMa:EDBT:09

Martin Kersten

...

Niels

...

real 0m0.989s user 0m0.002s sys 0m0.003s

Indices: MonetDB: None MySQL: index on ins_key

Thanks.

...

------------------------------------------------------------------------------

...

...

This SF.net email is sponsored by: High Quality Requirements in a Collaborative Environment. Download a free trial of Rational Requirements Composer Now! http://p.sf.net/sfu/www-ibm-com _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users

------------------------------------------------------------------------------ This SF.net email is sponsored by: High Quality Requirements in a Collaborative Environment. Download a free trial of Rational Requirements Composer Now! http://p.sf.net/sfu/www-ibm-com _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users