Getting Started with libmysqld - Overview and Implementation Guide

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