ougls 2016: guided tour on the mysql source code

Confidential – Oracle Internal/Restricted/Highly RestrictedCopyright © 2016, Oracle and/or its affiliates. All rights reserved. |

Guided tour on the MySQL Source CodeGeorgi KodinovMySQL team lead

Copyright © 2016, Oracle and/or its affiliates. All rights reserved. | Confidential – Oracle Internal/Restricted/Highly Restricted 2

Safe Harbor StatementThe following is intended to outline our general product direction. It is intended for information purposes only, and may not be incorporated into any contract. It is not a commitment to deliver any material, code, or functionality, and should not be relied upon in making purchasing decisions. The development, release, and timing of any features or functionality described for Oracle’s products remains at the sole discretion of Oracle.


Agenda

The directory layout of the codebase

Session establishment and termination

Query execution

Add functionality to MySQL


Important directories in the codebase Directory Purpose

sql/ Most of the server code

storage/ Storage engines

plugin/ Non-storage-engine plugins

sql-common/ Code common between client and server

libmysql/ Client library code

libmysqld/ Embedded server client library glue code

mysql-test/ Integral tests

libservices/ The plugin services library, plugin part

mysys*/ Portability library, utilities

vio/ Network connection abstraction library

client/ Client tools


Session establishment and termination• Each transport has its own listening thread: *_conn_event_handler()– Runs Connection_acceptor::connection_event_loop()

• Communicates via a Protocol class sub-class. Protocol_classic for the native MySQL protocol. Work in progress !• Each session has a THD created. The THD is the session state. The THD is in

a OS thread attribute (current_thd()). There can be nested THDs.• Connection_handler_manager class drives the threading model


Session establishment and termination contd.• handle_connection() is the main worker thread execution function• check_connection() is where authentication happens• close_connection() is the function to close the connection. Called by both

the worker thread or KILL


Query execution at a glance• do_command() fetches and executes a command (RPC)• dispatch_command() has the command (RPC) id main switch()• The THD has the current command being executed• mysql_parse() parses and executes a single query. – Called in a loop for the COM_QUERY command

• mysql_execute_command() is the execution part of mysql_parse()– Used to be a switch() over the SQL command id and call different C functions– Now in the process of being transformed to calls to Sql_cmd::execute(). – Lots of sub-classes for Sql_cmd.


The parser: work in progress• In older releases the parser produced the execution plan directly– Item class subclasses

• Started adding a separate parse stage data structure– Parse_tree_node subclasses

• Extra pass– Sql_cmd *PT_Statement::make_cmd() – Still called by the parser


Query execution passes: handle_query() et al• “Open” all tables: open_tables_for_query ()• Preparation: SELECT_LEX::prepare() and friends– Name resolution: setup_fields() et al– Some transformations, like flattening subqueries etc

• Optimization: SELECT_LEX::optimize() and friends– Produces a JOIN object for each subquery level– Calls JOIN::optimize() to produce query execution plan

• Execution: JOIN::exec() or st_select_lex_unit::execute()– Pumps result into a Query_result sub-class.


Adding functionality to MySQL: Plugins


Plugins overview• A plugin is:– An API that the server calls– init() and deinit()– A bunch of system variables– A bunch of status variables

• A plugin does:– Initialize and de-initialize itself– Implement the API that the server will call– Call back into the server via the plugin services


Plugins: Pros and ConsPros Cons

Clean versioned API to implement Can only implement a fixed set of APIs defined by the server

Can be loaded and unloaded dynamically Can only interact with the server. Not other plugins

Can use services to call back into the server Acquiring and releasing plugin handles by the server is expensive

Can be built with the binary distro, no need for the server source

Very poor set of plugin services

Are versioned and can be reused across versions Linked with the server binary: can call into the server code without services

The tree cmake files detect “foreign” plugin directories and compile them

APIs cannot be related so APIs tend to be large (see e.g. the SE API : 200+ methods)


Most notable plugin types• Storage engines• INFORMATION_SCHEMA tables• Daemon• Authentication• Audit• Keyring


How to write a plugin ?• Take an existing one and alter it• Plenty of examples– Live plugins– Test plugins

• Put it in plugin/ or storage/ so it compiles


Adding functionality to MySQLPlugin Services


What is a plugin service ?• The reverse of a plugin API• Allows plugins to call into the server• Version controlled• In essence a set function pointers in each plugin that the server fills in at

INSTALL PLUGIN time• Header in include/mysql/service_*.h


Please use the plugin services !• Calling server code directly => a “dirty” plugin– No portability across versions– No guarantee to work with future versions

• Plugin services are the reverse of Plugin APIs– Inlcude/mysql/service_*.h

• We have some good ones in 5.7:– command_service_st : execute a RPC command–mysql_locking_service_st : interface to MDL locks– security_context_service_st : interface to the security context– srv_session_service_st : handle “sessions” inside the server


Adding functionality to MySQL: UDFs


What are the UDFs• Can reside in external binaries• Can be loaded and unloaded• Can share binaries with plugins or other UDFs• Executed as single result or aggregate SQL functions• Easy to write, lots of examples


UDFs: Pros and ConsPros Cons

Easy to write Terrible for the DBA to administer

Can share a binary with the plugins No global initialization and deinitialization

Can be version safe No access to plugin services

Somewhat limited data type range

Only SQL functions, not commands to execute

No THD context

ougls 2016: guided tour on the mysql source code

Software