1 / 27

Anders Karlsson Principal Sales Engineer, MySQL

MySQL Embedded - Getting started with libmysqld. Anders Karlsson Principal Sales Engineer, MySQL. Agenda. Who am I? (Good question!) Why libmysqld? What is libmysqld? What is MySQL Emmbedded? And is it different from libmysqld? Programming with libmysqld API, Samples etc.

Download Presentation

Anders Karlsson Principal Sales Engineer, MySQL

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript


  1. MySQL Embedded - Getting started with libmysqld Anders Karlsson Principal Sales Engineer, MySQL

  2. Agenda • Who am I? (Good question!) • Why libmysqld? • What is libmysqld? • What is MySQL Emmbedded? And is it different from libmysqld? • Programming with libmysqld • API, Samples etc. • Limitations of libmysqld • Questions? Answers?

  3. About Anders Karlsson • Sales Engineer with Sun / MySQL for 5+ years • I have been in the RDBMS business for 20+ years • I have worked for many of the major vendors and with most of the vendor products • I’ve been in roles as • Sales Engineer • Consultant • Porting engineer • Support engineer • Etc. • Outside MySQL I build websites (www.papablues.com), develop Open Source software (MyQuery, ndbtop etc), am a keen photographer and drives sub-standard cars, among other things. Also, I am pushing for ZFS to become GPL: http://www.makezfsgpl.com

  4. Why libmysqld? • Libmysqld is compact and fast • Libmysqld is easily integrated into an application • Application installation is MUCH easier • No separate server to install or run • No need to run as service or daemon • Low hardware requirements • Zero maintenance • MySQL hidden from end user

  5. And why NOT libmysqld? • Only ONE application can connect at the time! • No server means less flexibility • Application itself has larger footprint • Includes both the server and the application • Certain limitations exist • No Replication • No Client / Server connection • No authentication / security • More functions must be implemented in the application • Any mysql Client operations such as dump, restore, check etc.

  6. What is libmysqld and why is it different? • “Normal” MySQL is a classic client/server based RDBMS • The database server is a separate process from the client application • The client and the sever communicates using standard networking (typically sockets or TCP/IP) • The Server may, and again may not, run in a separate hardware environment from the client • The client talks to the server using a MySQL defined protocol, typically implemented in a client interface or “driver” or Connector

  7. What is libmysqld and why is it different? • MySQL in Client / Server configuration • MySQL Server runs on machine on the network • Clients communicate with Server using the MySQL Protocol • Many clients talk to the same server Clients using MySQL Protocol Disk Server

  8. What is libmysqld and why is it different? • Libmysqld is a SQL database layer tightly integrated with the application or “client” • The database and the application runs in the same process space • No special means of communication is needed as both the application and the database are in the same process, calling MySQL is similar to calling any other library function • The interface between the Application or client is different, but the exposed component, the API, is the same as with Client / Server MySQL • Although the API looks the same, the implementation is different!

  9. What is libmysqld and why is it different? • Libmysqld configuration • The Application IS the server! • There is just 1 process • Data is stored on the local disk • NOTE: The API for the application is the same, but the implementation is different! There is just one implementation: In C • All data is local • Only one “client” libmysqld Application process Client system

  10. So where do I find libmysqld? • Libmysqld is part of the usual MySQL distribution • Just download the MySQL Server and you find • On Windows: libmysqld is in the Embedded subdirectory, both debug and release versions are available, and in DLL and static builds • On Linux: libmysqld.a is in the lib subdirectory where MySQL is installed • Includefiles: • Use the normal MySQL C API includefiles

  11. Building libmysqld applications • Develop applications in C • Add calls to mysql_library_init() etc. • Compile as usual • Link with libmysqld • Can be linked dynamically or statically • A dynamic link is easier, just as a dynamic link with the MySQL C Client library is easier • Set up an option file • Ship application, possibly the libmysqld shared library and option file

  12. The libmysqld API • VERY similar to the MySQL C API • Does NOT implement the MySQL Protocol! • Only looks the same, implementation is different! • Key functions that are different • mysql_library_init() – Starts the internal “MySQL Server” • mysql_library_end() – Shuts down the internal “MySQL Server” gracefully • mysql_thread_init() / mysql_thread_end() • mysql_options() – Set MYSQL_OPT_USE_EMBEDDED_CONNECTION before connecting

  13. The mysql_library_init() function • Initializes the MySQL internal server • Passes parameters to the server in that process • Must be passed information on initialization files and sections in those • No server is already running! • This means all necessary server parameters needs to be specified! • This means that this call is mostly different from when NOT using libmysqld! • MUST be called before mysql_init()!

  14. The mysql_library_init() parameters • Argument count (argc) • Arguments (argv) – Like the parameters to mysqld, the first argument (as in argv[0] beging name of the program) being ignored. • A list of option file sections to read • If a --defaults-file or any other argument with an option file is passed, the config files section specifies whicg sections in this file to read • The last section name is NULL • If no option file is passed, this argument may be NULL.

  15. Initializing a libmysqld application static char *server_options[] = {"mysql_test","--datadir=C:/mydata", NULL}; static char *server_groups[] = { "libmysqld_server", NULL}; int main(argc, argv){MYSQL mysql; mysql_library_init(sizeof(server_options)/ sizeof(char *) – 1, server_options, (char **) server_gropus);mysql = mysql_init(NULL);mysql_options(mysql, MYSQL_OPT_USE_EMBEDDED_CONNECTION, NULL);mysql_real_connect(mysql, NULL, NULL, NULL, "test", 0, NULL, 0);

  16. Configuring a libmysqld application • Use a my.cnf file • Set server_options as per the previous slide, to include--defaults-file=my.cnf • Note that you must be careful with the path here! • Set up the my.cnf file • Or set all options in the application itself • Set all options in the server_options variable

  17. Dynamic linking of a libmysqld application • As a shared object / DLL • An import library is provided to be linked with your application • Your application will need to be distributed with the libmysqld shared object / DLL • Not available on all platforms Application Libmysqld Import Library Libmysqld shared library

  18. Static linking of a libmysqld application • As a static library • The libmysqld library is linked directly into the application • No additional files needs to be distributed with the application • The only option on some platforms • Has a slight performance benefit Application Libmysqld Library

  19. Linking a libmysqld application • Note that dynamic linking does NOT necessarily allow you to update libmysqld by just changing the DLL / Shared object • Typically, an application linked with MySQL version X needs libmysqld version X, even with dynamic linking • For Windows, I recommed dynamic linking • Windows static linking is complex, as the static library depends on a particular version of Visual C++ is used • For Linux, I recommend static linking • Not the least because that is the only option right now. But it is less problematic than on windows, and has a slight performance advantage

  20. Managing libmysqld data • Data format are the same for libmysqld as it is for the MySQL Server mysqld • Hence: Data that can be freely copied with mysqld can be freely copied with libmysqld • This relates to most storage engines, but in some cases with limitations • MyISAM may use compression (which is common) and other MyISAM variations • Starting a libmysqld application with InnoDB enabled (not using --skip-innodb) will create InnoDB data and transaction log files, as usual

  21. Managing libmysqld data • Offline tools for mysqld data may be used on libmysqld data • myisampack • myisamchk • etc • Online tools need to be implementedas part of the application • mysqldump • mysqlshow • etc.

  22. Libmysqld demo! • The following demo application is available • On sourceforge:http://sourceforge.net/projects/libmysqlddemo/ • On MySQL Forge:http://forge.mysql.com/projects/project.php?id=323 • The application is Windows dialog-based and written in C using the Win32 API • Should be self contained, except for libmysqld of course, and the language file

  23. Limitations of libmysqld • No replication • No events • InnoDB is single threaded • No authentication / authorization • Single application access • But that is the whole pointof libmysqld • Only C / C++

  24. libmysqld mysqld INSERT 6.82 s 20.91 s UPDATE 6.54 s 21.59 s SELECT 6.92 s 21.73 s Libmysqld performance 100.000 operations each, time in average after several runs Note: This is very far from a scientific benchmark! The operations executed are simple single-row operations, searching on the PRIMARY KEY

  25. Libmysqld performance • So does this mean that libmysqld is faster than mysqld? • No, it means that libmysqld has less overhead, that performance is better is a side-effect of that • So why is this useful data then • As it means that I need less OS and Hardwareresources to achieve the same levelof performance!

  26. Resources and contacts • Email me at:anders@mysql.com • Read my blog at:http://karlssonondatabases.blogspot.com/ • MySQL Forum for Embedded systems:http://forums.mysql.com/list.php?58 • Contribute to the community with samples and ideas, we need more of those for libmysqldhttp://forge.mysql.com

  27. Questions? anders@mysql.com

More Related