Wednesday, August 26, 2009

Accessing MySQL tables from UDF + storage engine API

A couple of weeks ago, at a MySQL study group in Tokyo I presented about various kinds of MySQL hacking techniques, such as debugging with MS Visual Studio or gdb, tracing with DTrace, writing information schema plugins, accessing tables from UDF and storage engine API, and extending MySQL server code itself. This 90-minute session went successful. Nearly 100 people attended, including famous MySQL developers such as Kazuho Oku-san and Kentoku Shiba-san, having productive discussions with attendees and quite a lot of people seemed interested in MySQL hacking. The slides are written in Japanese, but sample codes can be understandable and can be downloaded here.
What audiences were most interested in was accessing MySQL tables directly from Plugins(currently UDFs) and storage engine API. I showed a sample UDF code, based on what Kazuho-san released over one year ago, which is over 10 times faster than SQL or stored procedures for complex operations. Excerpted codes are as follows(skipping error handling).


#define MYSQL_SERVER 1
#include
...
/* initializing tables, fields and indexes */
THD *thd = current_thd;
TABLE_LIST tables;
TABLE *table;
tables.init_one_table("test","message", TL_READ);
simple_open_n_lock_tables(thd, &tables);
table= tables.table;
message_id_fld = get_field(table, "id");
message_user_id_fld = get_field(table, "user_id");
message_key = index_init(table, "user_id", true);

/* accessing table. Equivalent query is
SELECT id FROM message WHERE user_id=? ORDER BY id DESC LIMIT 1; */
uchar* key_buff= (uchar*) thd->alloc(message_key->key_length);
bzero(key_buff, message_key->key_length);
int null_offset= message_user_id_fld->null_bit;
int4store(key_buff + null_offset, user_id);
err= table->file->index_read_last_map(table->record[0], key_buff, 1);
fprintf(stderr, "id=%lld\n", message_id_fld->val_int());

/* closing objects */
table->file->ha_index_end();
close_thread_tables(thd);


By defining MYSQL_SERVER to 1, you can access to MySQL internal structures.
You can build the sample UDF on development environment as follows. MySQL source code is required to build when accessing to internal structures.

g++ -g -DSAFE_MUTEX -DSAFEMALLOC \
-Wall -fno-rtti -fno-exceptions -fPIC -shared \
-I/debug-built-mysql-src/include \
-I/debug-built-mysql-src/regex \
-I/debug-built-mysql-src/sql \
-o udf_sample.so udf_sample.cc


On production environment, skip using -DSAFE_MUTEX -DSAFEMALLOC and include MySQL source built with configurations for production. When using some macros(i.e. current_thd) that behave differently between debug and prod configurations, you need to include appropriate source directly.

g++ -g \
-Wall -fno-rtti -fno-exceptions -fPIC -shared \
-I/prod-built-mysql-src/include \
-I/prod-built-mysql-src/regex \
-I/prod-built-mysql-src/sql \
-o udf_sample.so udf_sample.cc


This snippet skips error handling etc for good legibility, but you need to carefully handle errors, otherwise mysqld crashes. Please check bench_udf.cc, build_udf_debug.sh, and build_udf_prod.shin my sample code.

MySQL is fast, but it still has many expensive CPU operations such as parsing SQL statements, making execution plans every time (MySQL doesn't have optimizer-plan caching mechanism), a lot of memcpy/malloc operations, etc. If you write a UDF which directly accesses tables through storage engine API, these expensive operations can be skipped so it can be much faster than SQL statements in many cases.
The above code looks like using key value store APIs (i.e. TokyoCabinet APIs). Most KVSs are faster than MySQL, and one of the reasons is they don't need to do extra operations such as making execution plans etc. But it is not impossible to use MySQL like fast KVS as above. You can not only get benefits about better performance, but also utilize traditional RDBMS implementations such as B+Tree (range scan), perfect durability, schema awareness. Data is stored in one place, and you can get the same data regardless of accessing methods. For example, you can use UDF for some special parts (i.e. picking up all friends' latest messages, which is difficult for MySQL to fetch quickly), and use normal SQL statements for others. I assume such two-way client interfaces (fast API and traditional SQL) can be a common implementation for next-generation RDBMS. Third, this is currently not common hacks so there is almost no document. For example, handling two or more tables requires additional codes, but this is not documented (I am still fully not understand to safely handle them).
Of course there are many disadvantages about using UDFs and storage engine APIs directly. First, it highly depends on MySQL versions. Storage engine APIs are standardized but client side operations such as calling table open/lock functions are different from MySQL versions. Second, mysqld easily crashes if UDF has bugs.
But there are a lot of possibilities here and this certainly attracts developers. For example, Kazuho-san yesterday published a very interesting UDF: mycached (memcached server udf). It listens memcached protocol, then directly accessing a table through storage engine API (primary key lookup). According to his benchmark this is twice as fast as SELECT with primary key lookup (if records are cached in buffer pool). I assume this approach will work very well as a durable key-value store, for example storing session data, last access timestamp etc.

74 comments:

Roland Bouman said...

Hi!

hey, very interesting! I wasn't aware you actually could access internals through a UDF - probably due to my general lack of C/C++ knowledge and insight in the code base.

Just curious, can you describe the minimal recipe that allows you to access internals through a UDF? I mean which headers, compilation options etc?

TIA,

Roland

atcurtis said...

Code snippet as presented is likely to crash the server if the UDF is called in a SELECT statement when another table is already opened.
You should check the return of open_and_lock().
But yes, it naturally would be faster. ;)
A way to "get around" the open_and_lock() limitation would be to create a THD object for the purpose. There is a risk of deadlock but those can be broken.

Yoshinori Matsunobu said...

Hi Roland,

I added more detail in my post. Define MYSQL_SERVER to 1, including include/regex/sql MySQL source directries.

Yoshinori Matsunobu said...

Hi atcurtis,

Yes, thanks. My sample code (url is published in my post) checks simple_open_n_lock_tables() errors as below. You're right, mysqld crashes if skipping to check errors.
if (simple_open_n_lock_tables(thd, &tables))
{
close_thread_tables(thd);
DBUG_RETURN(-1);
}

jessika.mk said...

Thank you . To all participant, please have a look at our updated contest post.
more templates easy to download

1025 said...

嫉妒能使人得到短暫的快感,但也能使不幸更辛酸。 ..................................................

請吃飯 said...

能付出愛心就是福;能消除煩惱就是慧。........................................

與發 said...

與其爭取不可能得到的東西,不如善自珍惜運用自己所擁有的........................................

佩昭彥怡 said...

女優大奶性交性愛淫蕩淫慾淫亂淫婦淫妹淫叫淫水淫女情慾情色做愛限制級波霸口交18禁貼圖寫真視訊援交露點爆乳潮吹裸體裸照裸女愛愛無碼尋夢視訊聊天a漫

智琳 said...

IS VERY GOOD..............................

Anonymous said...

cool

Sildenafil Citrate said...

I have a friend that has taught me some of MySQL hacking techniques debugging with MS Visual Studio or gdb, tracing with DTrace and I did not know about this, thank you very much!

Sinus headache said...

Your post really grabbed my attention and interest for the reason that the content is not just informative but also simple yet meaningful.

Natural laxative foods Stomach gas Hard stool treatment Health benefits of almonds White spots on skin
Cure ear infection Under eye wrinkles Baby ear infection Prevent prostate cancer Prevent prostate cancer Lemon tea healh benefits Lemon tea healh benefits Prevent candida yeast infection Prevent candida yeast infection Health benefits of papaya Health benefits of papaya Benefits of peppermint oil Benefits of peppermint oil Stop hiccups Stop hiccups Water and weight loss Water and weight loss Wart Removal Wart Removal Health benefits of oatmeal

android tablet said...

Good post. Very impressive. Thanks for sharing.

Getting rid of cellulite said...

Your post is really good . White patches on skin Thanks. Constipation during pregnancy

WOW Gold said...

A simple example of how networks can be this easy example of a woman who used to love writing articles.
buy eden gold
eden gold
cheap eden gold

hermes birkin said...

Thank you for another essential article. Where else could anyone get that kind of information in such a complete way of writing? I have a presentation incoming week, and I am on the lookout for such information.
Hermes replica
hermes birkin replica

Marissarachelle said...

Here is a professional Tera gold online store which is dealing with cheap Tera gold as well as buy eden gold sevice. We have lots of suppliers and plenty of in stock for all servers, and will deliver you the gold in very short time. If you have any questions about buying Tera gold cheap, you can contact our Live Chat for assistance.

Anonymous said...

A number of the Patek Philippe replica Watches watches worn by NASA astronauts are

displayed in various places around the world. best replicas rolex For instance, the watches

worn by Neil Armstrong and Mike Collins are currently at the National Air and Space Museum in Washington D.C. Other burberry outlets

places that various watches hermes hello kitty bags are located at include the Museum

of Science and Industry in Chicago, The Replica Chanel Watches Museum in Switzerland and the U.S. Naval Academy in Annapolis.

film art said...

Very well written. You got every point explained. Loved the whole article! Many thanks!

get wife give blowjob said...

It is very informative. Keep up your excellent work.

vidi said...

It is very informative. Loved the whole article! Thanks for sharing.
Grow taller diet
How to grow taller free

tourist playground said...

I want to thank you for this informative read. Loved the whole article! Thanks for sharing.
How to grow taller

arabic letters said...

I'm so excited with this post. I really appreciate sharing this great post. Keep up your excellent work.
Does milk make you taller

cunnilingus said...

Very well written. You got every point explained. Loved the whole article! Many thanks!
cervical mucus
ovulation symptoms
cunnilingus video
fertilecm
mittleschmerz

oranum said...

I'm really enjoyed reading this article and whole website, thanks a lot for this informations!
gledanje u solju simboli

iphone 4 maske said...

I'm so excited with this post. I really appreciate sharing this great post. Keep up your excellent work.
ulepsavanje
ljubavni stihovi

recepti said...

I don’t know what to say except that I have enjoyed reading. Nice blog, I will keep visiting this blog very often.
krumpir salata
sataraš
lazanje s mesom

Accommodation Croatia said...

I'm really enjoyed reading this great article and whole website, thanks a lot for this informations!
sanovnik
dnevni horoskop

Sagging skin said...

Interesting article . Thanks for sharing. Constipation during pregnancy Find online career Facelift surgery Forehead wrinkles Age spots White patches on skin Skin to Skin Contact Wrinkles around the Mouth Tips for Job Search Symptoms of Allergic Reaction Tips for Job Search and Career Success Prevent Wrinkles On Neck Get Beautiful Skin Tips for preventing gas and flatulence

Bamboo Flooring Reviews said...

Great post!
Bamboo Flooring Review

Bamboo Flooring Review

erotske price said...

It is very informative. I will bookmark this site and visit again. Keep up your excellent work.

grupnjaci
matorke
lezbo
erotske slike
prvi put

trbusnjaci said...

Very well written post. You got every point explained. Loved the whole article! Many thanks!
vezbe za trbusnjake
vezbe za mrsavljenje
aerobik vezbe

how to get a blowjob said...

I want to thank you for this informative read. Loved the whole article! Thanks for sharing.
jacks blowjob persuasion
how to get a girl to suck your dick
get girlfriend to deepthroat
convince girlfriend facial
how to get more blow jobs
i need a blowjob
wife wont suck my cock
make swallow cum
jacks blowjob persuasion review

seagrass baskets said...

This is a wonderful content. I will bookmark this site and visit again. It is very informative. Thanks for sharing.
restorani u beogradu

restorani u beogradu said...

Excellent post is written here. I want to thank you for this informative read. I will bookmark this site and visit again...
horoskop

GuildWars2Items said...

If you lose your way, your forever friend guides you and cheers you on Diablo iii Gold, if you turn and walk away, your forever friend follows Buy Diablo iii Gold, your forever friend gets you through the hard times, the sad times, and the confused times Cheap Diablo iii Gold.

GuildWars2Items said...

Life is not measured by the number guild wars 2 gold of breaths we take, but by the moments that take our breath away guild wars 2 gold, guild wars 2 gold, don't go around saying the world owes you a living. The world owes you nothing. It was here first..

GuildWars2Items said...

Life is not measured by the number of breaths we take scarlet blade gold, but by the moments that take our breath away scarlet blade gold. I have a simple philosophy: Fill what's empty scarlet blade gold.

GuildWars2Items said...

The consequences of today are determined by the actions of the past rs gold. To change your future, alter your decisions today Runescape gp, Experience is a hard teacher because she gives the test first, the lesson afterwards rs gp, but it takes character to keep you there.
.

adeoe said...

Of course there are many disadvantages about using UDFs and storage engine APIs directly. First, it highly depends swtor credits on MySQL versions. Storage engine APIs are standardized but client side operations such as calling table open/lock functions are different from MySQL versions. Second, mysqld easily crashes if UDF has wizardry gold bugs.

Cahaya Mandiri said...

Great Post. I have not been visiting the site recently. Took a visit again and there were some great comments on the site. Excellent post. Keep up the good work.
tips cara agar cepat hamil l CARA BELAJAR BAHASA INGGRIS l the best acne treatment l how to lose weight fast easy
margahayuland l BISNIS ONLINE l tips cepat hamil l how to get rid of acne home remedies l
home remedies for acne l how to cure acne fast l
baju batik modern l toko sepatu online l grosir jam tangan online l
jual jam tangan l toko jam tangan murah peluang usaha online l is acne no more for you l how to get rid of acne naturally
how to clear acne l cure acne naturally
best natural remedies for acne l acne no more l tempat belajar bisnis online
peluang usaha rumahan l cara mendapatkan uang dari internet
makanan sehat agar cepat hamil l penyebab tidak bisa hamil lcara agar cepat hamil


shahbaz said...

I have been teaching a class and we are looking at this subject in the next week. I will be directing my student to look at your post for good information.Yacon Syrup

shahbaz said...

I mean it’s not my mom that loves Paula, I love Paula! It makes me sick even now just thinking about it.Products

shahbaz said...

I had really like it very much satisfied by the info in this blog. I am really like it very much impression in this blog. Thanks a lot for sharing the nice info in this blog. Hotel Towels in Bulk

Florence Jessica said...

I really like it very much satisfied by the info in this blog.I will be directing my student to look at your post for good information. buy coursework

sadia sulaman said...

Excellent read, Positive site, where did u come up with the information on this posting?I have read a few of the articles on your website now, and I really like your style.cafe-vn |

shoers |

abortionstatistics |

kellyllorennacd |

matthewsawyer |

chris-burke |

restaurant123 |

londonspeaker |

rioscoffee |

bikesandboards |

shahbaz said...

Just made this tonight for dinner, and it was absolutely delicious! Tastes just like my favorite restaurant's jambalaya pasta. Thanks for the recipe plano tx dentist

Marcellina Helen said...

I'm really enjoyed reading this great article and whole website.I will be directing my student to look at your post for good information. assignments by essaymall.co.uk , coursework writing service

shahbaz said...

Very Useful information, this is both good reading for, have quite a few good key points and I learn some new stuff from it too. click for wedding invites in san diego

shahbaz said...

The only sad part about moving away from Maryland recently, was having to leave ProFIT. Now in my search for a new Zumba class, I am very nervous about what I will find. Alex set the bar very high and now I am expecting, and hoping to find, the same crazy fun she brings to her classes.will living trust

shahbaz said...

I did a survey about Jolanda's Cafe, and so far, most of them are positive. So maybe they only need improvement, and not major changes. Web Design Company

shahbaz said...

Really liked your blog very informative and interesting facts and figures you have discussed on your blog even the comments are very helpful in enhancing the knowledge regarding this topic business for sale listings

sadia sulaman said...

Thank you for this blog. That's all i can say..useful information shared... i am very happy to read this article.. thanks for giving us nice info. fantastic walk-through.princetonhome |

nesaahome |

masonryhomes |

auntannshomecare |

betterathomedc |

dornhomes |

homefixcorporation |

trianglehomestour |

life-flexhome |

richmondhomevalues |

Best economical Dymo Labels said...

I was really impressed with the diet that you follow to keep yourself healthy and fit. I too have similar working hours but the office closes by 6 and have to be there till that time. I feel so tired after the hectic work and then again the cycle continues. London team building

John Dudley said...

Facelift Surgery Delhi, face lift procedure has helped enhance the lives of many. You will love the way you look and you will love the way you are treated.

shahbaz said...

Excellent read, I just passed this onto a colleague who was also looking for it, And he actually bought me lunch because I found it for him. So I should thank you for the free lunch I got.Seaside FL real estate

steve7876 said...

It is a great article, as well as I want more information for those who have any kind of. We are attracted to this kind of living trust cost

shahbaz said...

hope you will keep posting nice stuff on different important issues, and will share all your nice stuff with us, and all your experience to give more knowledge to people China tour packages

shahbaz said...

I really thank you for the valuable info on this great subject and look forward to more great posts. Thanks a lot for enjoying this beauty article with me. I am appreciating it very much! Looking forward to another great article. Good luck to the author! All the best! family dentistry complete new patient exam

steve7876 said...

Someday....someday....I will make it to France. It's so romantic and beautiful! Your photos are just amazing!!dentist plano

panca-samudera said...

The article posted was very informative and useful
thanks for sharing.
jaring futsal , jaring futsal murah , jual jaring futsal , toko jaring futsal , agen jaring futsal , jaring golf , jual jaring golf , jaring golf murah ,
jaring polynet , tangga darurat , jaring truk , jaring cargo , jaring outbound , jual rumput futsal murah , jual rumput sintetis murah ,
tali tambang , cargo net , jaring tanaman , jaring kassa , jaring proyek , jaring bangunan , jaring gedung , jaring pengaman proyek ,
jaring pengaman bangunan , jaring pengaman gedung , jaring peneduh , jaring waring , kasa hijau , tangga darurat , jaring gawang futsal , jual jaring gawang futsal murah , jaring peneduh , jaring truk , tali tambang nylon

jhoney said...

Thank you for this post. Thats all I are able to say. You most absolutely have built this blog website into something speciel. You Car Accident Lawyers Vaughan
clearly know what you are working on, youve insured so many corners.thanks

steve7876 said...

I really thank you for the valuable info on this great subject and look forward to more great posts. Thanks a lot for enjoying this beauty article with me. I am appreciating it very much! Looking forward to another great article. custom website design orlando

shahbaz said...

Thank you, I appreciate it! People like it when people come together and discuss opinions.GNC coupons

joy said...

We are all like flowers that blooms or withered in the garden depending on how we take good care of our self. So do not let negativity pass through. Enjoy and be confident. Have a good day and visit my site for more information.

n8fan.net

www.n8fan.net

shahbaz said...

I know some great informative websites and now after reading this my bookmarked websites list increased.expert tree service rochester ny

fikri ilham said...

Jual obat herbal de nature asli untuk berbagai penyakit kelamin dan sebagainya
Obat Wasir Herbal
Obat Wasir Herbal Mujarab
Obat Wasir Herbal Ampuh
Obat Wasir Herbal Pada Pria
Obat Wasir Herbal Di Apotik
Cara Mengobati Wasir Stadium 4
Cara Mengobati Wasir Stadium 4 Tanpa Operasi
Cara Mengatasi Wasir Stadium 4
Cara Penyembuhan Wasir Stadium 4
Cara Mengobati Ambeien Stadium 4 Secara Alami
Cara Pengobatan Wasir Stadium 4
Cara Pengobatan Ambeien Stadium 4
Cara Mengobati Wasir Stadium 3 Dan 4
Cara Mengobati Wasir Stadium 3
Cara Mengobati Wasir Stadium 3 Secara Alami
Obat Wasir Herbal Apotik
Obat Wasir Herbal Untuk Ibu Hamil
Obat Wasir Herbal Terdaftar Bpom
Obat Wasir Herbal Untuk Ibu Menyusui
Obat Wasir Herbal Bandung
Cara Mengobati Wasir Stadium 3 Dan 4
Cara Mengatasi Wasir Stadium 3
Cara Mengobati Ambeien Stadium 3 Tanpa Operasi
Cara Menyembuhkan Wasir Stadium 3
Cara Mengatasi Ambeien Stadium 3
Cara Alami Mengobati Wasir Stadium 3
Cara Mengobati Ambeien Stadium 3 Secara Alami
Obat Wasir Herbal Manjur
Obat Wasir Herbal Semarang
Obat Wasir Herbalife
Obat Wasir Herbal Jogja
Obat Herbal Wasir Berdarah
Obat Herbal Wasir Dalam
Obat Herbal Wasir Akut
Obat Herbal Wasir Stadium 4
Obat Herbal Wasir Terpercaya
Obat Herbal Wasir Stadium 3
Obat Herbal Wasir Internal
Obat Wasir Herbal Alami

shahbaz said...

I stumbled upon your own site perfect on your own would like. Their particular details attributes wonderful along with things. We now have examine most of them along with bought a large bin off from their site. Regards on the details post. Certainly cya! Fantastic. Regards on the find out.CWDM Mux/DeMux Module

shahbaz said...

Create this eleemosynary of buddys you attention about precisely what consequently heartbreak-favored along ruminates to most of these draws which are truth produced.traveling nursing agencies

shahbaz said...

OPY World School is promoted and managed by Rao Mohar Singh Education Society with the mission to provide quality education at school level.lawyers in West Palm Beach florida

lina sexshop said...

Berikut kami akan membahas tentang bagai mana cara memperbesar penis dengan hasil cepat dan aman tanpa operasi
jika anda ingin memperbesar penis dengan hasil cepat,aman tanpa operasi itu mudah anda bisa pilih cara memperbesar penis dengan obat pembesar penis herbal
seperti Pembesar Penis Obat Good Man

jika anda riskan konsumsi obat pembesar penis anda bisa pilih jalan lain seperti metode trapi ( pemakaian luar ) kami juga punya solusinya anda harus punya Alat Pembesar Penis sejenis Pro Extender atau Vimax Extender

Berikut penjelasan kami :

Apa Obat Good Man itu : Obat Good Man
Apa Vimax Extender itu : Vimax Extender Asli
Apa Pro Extender itu : Pro Extender Asli

Sekarang anda bisa tau rahasia cara memperbesar penis dengan aman tanpa operasi.selamat mencoba.

Thank you for the information , I wait for more information and I ask for a return visit our new website
>> Obat Pembesar penis <<

Regina Hilary said...

The game controls are shown just under . Movement mechanisms primarily include acceleration and tilting controls.
five nights at freddy's 2 | five nights at freddy's 4 |
fireboy and watergirl 2 | 2048 online | zombies plants | red ball 6 | age of war

shahbaz said...

An interesting share, one that is expressive as well as providing a founding statement. We found it interesting above all else.digital signature

Post a Comment