dbTalk Databases Forums  

Re: On-line cardinality for MEMORY tables

Go Back dbTalk Databases Forums Databases mailing.database.mysql-internals Re: On-line cardinality for MEMORY tables

mailing.database.mysql-internals mailing.database.mysql-internals


Discuss Re: On-line cardinality for MEMORY tables in the mailing.database.mysql-internals forum.



Reply
 
Thread Tools Display Modes
  #1  
Old   
Lenz Grimmer
 
Posts: n/a

Default Re: On-line cardinality for MEMORY tables - 11-22-2010 , 08:47 AM





Hi Maxim,

On 11/07/10 23:47, Maxim Deviatov wrote:

Quote:
a few years ago I was playing with B-tree index cardinality
of MEMORY storage engine and come up with a patch (attached)
implementing on-line cardinality calculation.

According to my benchmarks, in certain cases MEMORY table scan
may be up to 45% faster than index scan. But since MEMORY engine
doesn't provide cardinality for B-tree indexes, index scan is
always preferred over table scan.

The idea of the patch is to provide accurate cardinality for
B-tree indexes at any time.

Max overhead should be 5-10% slowdown of DELETE/UPDATE queries.
Very cool, thanks for the contribution! I have asked our developers to get in
touch with you and to review and comment on your patch. Before we can
incorporate your patch into the code base, I have to ask you for one favour:
can you please fill out and sign our Contributor Agreement? The instructions
on how to do this are outlined on these Wiki pages:

http://forge.mysql.com/wiki/Contributing_Code
http://forge.mysql.com/wiki/Oracle_C...utor_Agreement

Once I've received your agreement, I will review and approve it ASAP. Then the
administrative side is out of the way and we can focus on the technical part.

Thank you!

Lenz

--
Lenz Grimmer | MySQL Community Relations Specialist EMEA
Phone: +49 40 41267308 | Mobile: +49 172 7106443

ORACLE Deutschland B.V. & Co. KG - http://oracle.com
Hauptverwaltung: Riesstr. 25, D-80992 München
Registergericht: Amtsgericht München, HRA 95603

Komplementärin: ORACLE Deutschland Verwaltung B.V.
Rijnzathe 6, 3454PV De Meern, Niederlande
Handelsregister der Handelskammer Midden-Niederlande, Nr. 30143697
Geschaftsführer: Jürgen Kunz, Marcel van de Molen, Alexander van der Ven

Oracle is committed to developing practices and products that help
protect the environment - http://www.oracle.com/commitment


-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.10 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/

iEYEARECAAYFAkzqgoQACgkQSVDhKrJykfJFzgCbBiGi7u7Kwg AZCET0Ghz1Co62
I9UAnjLJYVZwj1rMwjvQFN/PWFvNaQhi
=xF9V
-----END PGP SIGNATURE-----

Reply With Quote
  #2  
Old   
Sergey Vojtovich
 
Posts: n/a

Default Re: On-line cardinality for MEMORY tables - 11-26-2010 , 07:42 AM





Hi Maxim, Lenz,

JFYI: I created BUG#58520 - "Memory tables should provide cardinality
for btree indexes" to track this contribution.

Regards,
Sergey

On Mon, Nov 22, 2010 at 03:47:32PM +0100, Lenz Grimmer wrote:
Quote:
Hi Maxim,

On 11/07/10 23:47, Maxim Deviatov wrote:

a few years ago I was playing with B-tree index cardinality
of MEMORY storage engine and come up with a patch (attached)
implementing on-line cardinality calculation.

According to my benchmarks, in certain cases MEMORY table scan
may be up to 45% faster than index scan. But since MEMORY engine
doesn't provide cardinality for B-tree indexes, index scan is
always preferred over table scan.

The idea of the patch is to provide accurate cardinality for
B-tree indexes at any time.

Max overhead should be 5-10% slowdown of DELETE/UPDATE queries.

Very cool, thanks for the contribution! I have asked our developers to get in
touch with you and to review and comment on your patch. Before we can
incorporate your patch into the code base, I have to ask you for one favour:
can you please fill out and sign our Contributor Agreement? The instructions
on how to do this are outlined on these Wiki pages:

http://forge.mysql.com/wiki/Contributing_Code
http://forge.mysql.com/wiki/Oracle_C...utor_Agreement

Once I've received your agreement, I will review and approve it ASAP. Then the
administrative side is out of the way and we can focus on the technical part.

Thank you!

Lenz

--
Lenz Grimmer | MySQL Community Relations Specialist EMEA
Phone: +49 40 41267308 | Mobile: +49 172 7106443

ORACLE Deutschland B.V. & Co. KG - http://oracle.com
Hauptverwaltung: Riesstr. 25, D-80992 München
Registergericht: Amtsgericht München, HRA 95603

Komplementärin: ORACLE Deutschland Verwaltung B.V.
Rijnzathe 6, 3454PV De Meern, Niederlande
Handelsregister der Handelskammer Midden-Niederlande, Nr. 30143697
Geschaftsführer: Jürgen Kunz, Marcel van de Molen, Alexander van der Ven

Oracle is committed to developing practices and products that help
protect the environment - http://www.oracle.com/commitment


--
Sergey Vojtovich <svoj (AT) mysql (DOT) com>
MySQL AB, Software Engineer
Izhevsk, Russia, www.mysql.com

--
MySQL Internals Mailing List
For list archives: http://lists.mysql.com/internals
To unsubscribe: http://lists.mysql.com/internals?uns...ie.nctu.edu.tw

Reply With Quote
  #3  
Old   
Sergey Vojtovich
 
Posts: n/a

Default Re: On-line cardinality for MEMORY tables - 12-20-2010 , 05:34 AM


Hi Maxim,

thanks for this great patch! It looks good overall, but there
are a few things that need to be addressed before we can
incorporate it into our source trees.

1. There are tab characters in keys_compare(), please replace
with spaces. See also:
http://forge.mysql.com/wiki/MySQL_In..._and_ Spacing

2. It would be great if this feature is covered by tests. See also:
http://forge.mysql.com/wiki/How_to_Create_Good_Tests

3. The values you're using in scan_time() and read_time() seem to
be correct, but I want to double check with the optimizer team.

4. Since this feature has 5-10% overhead on delete, probably it is
a good idea let users turn it off when needed. It can be achieved
by adding new system variable. See also:
http://dev.mysql.com/doc/refman/5.1/...variables.html
...or other storage engines that expose system variables.

Thanks,
Sergey

On Mon, Nov 08, 2010 at 01:47:12AM +0300, Maxim Deviatov wrote:
Quote:
Hi there,

a few years ago I was playing with B-tree index cardinality
of MEMORY storage engine and come up with a patch (attached)
implementing on-line cardinality calculation.

According to my benchmarks, in certain cases MEMORY table scan
may be up to 45% faster than index scan. But since MEMORY engine
doesn't provide cardinality for B-tree indexes, index scan is
always preferred over table scan.

The idea of the patch is to provide accurate cardinality for
B-tree indexes at any time.

Max overhead should be 5-10% slowdown of DELETE/UPDATE queries.

Have fun,
=====================
Maxim N. Deviatov

Quote:
--
MySQL Internals Mailing List
For list archives: http://lists.mysql.com/internals
To unsubscribe: http://lists.mysql.com/internals?uns... sun (DOT) com

--
Sergey Vojtovich <svoj (AT) mysql (DOT) com>
MySQL AB, Software Engineer
Izhevsk, Russia, www.mysql.com

--
MySQL Internals Mailing List
For list archives: http://lists.mysql.com/internals
To unsubscribe: http://lists.mysql.com/internals?uns...ie.nctu.edu.tw

Reply With Quote
Reply

« Previous Thread | Next Thread »



Thread Tools
Display Modes
Linear Mode Linear Mode

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
vB code is On
Smilies are On
[IMG] code is On
HTML code is Off



Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2013, Jelsoft Enterprises Ltd.


Contact Us - dbTalk Databases Forums - Archive - Top