Posts Tagged ‘MySQL’

MySQL’s BLACKHOLE Storage Engine

        The BLACKHOLE storage engine is named as “black hole” because it accepts data and does not store it. If you wiil insert any record it will prompt you that tables are updated nut when you will hit a select query on same table, you will be return an empty result:

Below is the example,

       The BLACKHOLE storage engine comes by default with  in MySQL. If you build MySQL from source, then parse the –with-blackhole-storage-engine option.

      When you create a BLACKHOLE table, only the .frm file is created, because it does not reuire to store data so no .MYD and .MYI file needed. You can check whether the BLACKHOLE storage engine is available by running below MySQL statement on mysql prompt.

      Inserts into a BLACKHOLE table do not store any data, but if the binary log is enabled, the SQL statements are logged and replicated to slave servers.

      If you run in a high volume production system where you may have one or more master databases for writes/updates/deletes and a whole bunch of slaves reading the log from that master which in short increase the network traffic, disk I/O, CPU power and others, this affect the performance of a Master Server.

      So to solve this problem you can take a help of MySQL’s BLACKHOLE storage engine as shown on below example.

      What you then have is the Master only replicating to one database, increasing the Master’s capacity to process  transactions. The slaves consume the log file from the Blackhole server. It acts as a proxy at this point, with the benefit of freeing up resources on the Master’s server.

      The master writes to its binary log. The “Blackhole” server acts as a proxy slave server, it does not actually store any data, so there is little processing overhead incurred and other SLAVE server will read a binary log from this server.

Other possible uses for the BLACKHOLE storage engine include:

* Verification of dump file syntax.
* Measurement of the overhead from binary logging, by comparing
  performance using BLACKHOLE with and without binary logging  

MySQL storage engine Sphinx

=> Sphinx Overview

Sphinx is MySQL storage engine which can be compiled with MySQL server 5.X version. It you want to compile with MySQL 5.0 series then you must have MySQL 5.0.22 or higher version, or MySQL 5.1.12 or higher in 5.1.x version.

Sphinx does not actually store any data itself. It is a built-in client which allows MySQL server to talk to searchd, run search queries, and obtain search results. All indexing and searching happen outside the MySQL.

=> Pre-Compile Package Requirements

You will need to install below package before compiling Sphinx

* autoconf
* automake
* libtool
* bison

=> Downloading, Installing and Compiling Sphinx

If you have already have an MySQL on you server then, there is no need to install it again just follow the “Installing Sphinx Add-On for already Installed MySQL”. But if you want to install a fresh copy of MySQl with Sphinx then follow the below stpes.

++  For MySQL 5.1 Series

1. Download the latest stable version of MySQL from “” snd Sphinx from “”

2. Untar the downloaded package.

3. In MySQL sources directory, create storage/sphinx directory and copy all files from Sphinx’s  mysqlse directory to MySQL’s storage/sphinx folder. E.g,

4. In MySQL sources directory, run below command

5. Now configure MySQL with Sphinx engine on “/usr/local/mysql-5.1” path.

6. Build and Install MySQL with Sphinx

++  For MySQL 5.0 Series

1. Download the latest stable version of MySQL from “” snd Sphinx from “”

2. Untar the downloaded package.

3. For MySQL 5.0 series first you wiil have to apply an patach which will be found on path “/usr/local/src/sphinx-”.

4. In MySQL sources directory, run below command

5. IN MySQL sources directory, create sql/sphinx directory in and copy all files in mysqlse directory from Sphinx sources there.

6. Now configure MySQL with Sphinx engine on “/usr/local/mysql-5.0” path.

7. Build and Install MySQL with Sphinx

++ Installing Sphinx Add-On for already Installed MySQL

    *  For MySQL 5.1 and MySQL 5.0 series skip 1st and 2nd steps, but make sure you have latest copy of Sphinx.

=>  Checking Sphinx installation

To check whether Sphinx has been succesfully compiled into MySQL, run SHOW ENGINES query on MySQL SHELL. You should see a Sphinx to be present and “Support” column should contain “YES”.

MySQL Inernals

By April of 2000, with some encouragement and sponsorship from Slashdot, master-slave replication capability was added.The old nontransactional storage engine,ISAM, was reworked and released as MyISAM.

By early 2002 the MySQL/InnoDB combo was stable and instantly took MySQL to another level. Version 4.0 was finally declared production stable in March 2003.

One can identify the following modules in the server:

• Server Initialization Module
• Connection Manager
• Thread Manager
• Connection Thread
• User Authentication Module
• Access Control Module
• Parser
• Command Dispatcher
• Query Cache Module
• Optimizer
• Table Manager
• Table Modification Modules
• Table Maintenance Module
• Status Reporting Module
• Abstracted Storage Engine Interface (Table Handler)
• Storage Engine Implementations (MyISAM, InnoDB, MEMORY, Berkeley DB)
• Logging Module
• Replication Master Module
• Replication Slave Module
• Client/Server Protocol API
• Low-Level Network I/O API
• Core API

=> Interaction of the Core Modules

When the server is started on the command line, the Initialization Module takes control. It parses the configuration file and the command-line arguments, allocates global memory buffers, initializes global variables and structures, loads the access control tables, and performs a number of other initialization tasks. Once the initialization job is complete, the initialization Module passes control to the Connection Manager, which starts listening for connections from clients.

When a client connects to the database server, the Connection Manager performs a number of low-level network protocol tasks and then passes control to the Thread Manager, which in turn supplies a thread to handle the connection. Once the Connection Thread receives control, it first invokes the User Authentication Module. The credentials of the connecting user are verified, and the client may now issue requests.

The Connection Thread passes the request data to the Command Dispatcher. In MySQL server terminology, there are two types of client requests: a query and a command. A query is anything that has to go through the parser. A command is a request that can be executed without the need to invoke the parser.

The Command Dispatcher forwards queries to the Parser through the Query Cache Module. The Query Cache Module checks whether the query is of the type that can be cached, and if it is already exist ,then a previously computed cached result that is still valid will be displayed. If the Query Cache Module reports a miss, the query goes to the Parser, which will make a decision on how to transfer control based on the query type.

Select queries are forwarded to the Optimizer; updates, inserts, deletes, and table-creation and schema-altering queries go to the respective Table Modification Modules; queries that check, repair, update key statistics, or defragment the table go to the Table Maintenance module; queries related to replication go to the Replication Module; and status requests go to the Status Reporting Module.

At this point, each of the modules that will receive control from the Parser passes the list of tables involved in the query to the Access Control Module and then, upon success, to the Table Manager, which opens the tables and acquires the necessary locks. Now the table operation module is ready to proceed with its specific task and will issue a number of requests to the Abstracted Storage Engine Module for low-level operations such as inserting or updating a record, retrieving the records based on a key value, or performing an operation on the table level, such as repairing it or updating the index statistics.

If the low-level module has made a modification to the data in some way and if the binary update logging is enabled, the module will be responsible for asking the Logging Module to log the update event to the binary update log, sometimes known as the replication log, or, among MySQL developers and power users, the binlog.

Once the task is completed, the execution flow returns to the Connection Thread, which performs the necessary clean-up and waits for another query or command from the client. The session continues until the client issues the Quit command.

=> Replication

In addition to interacting with regular clients, a server may receive a command from a replication slave to continuously read its binary update log. This command will be handled by the Replication Master Module.

If the server is configured as a replication slave, the Initialization Module will call the Replication Slave Module, which in turn will start two threads, called the SQL Thread and the I/O thread. They take care of propagating updates that happened on the master to the slave. It is possible for the same server to be configured as both a master and a slave.


=> Server Initialization Module

• init_common_variables( )
• init_thread_environment( )
• init_server_components( )
• grant_init( ) in sql/
• init_slave( ) in sql/
• get_options( )

=> Connection Manager

• handle_connections_sockets( )

=> Thread Manager

• create_new_thread( )
• start_cached_thread( )

=> Connection Thread

• handle_one_connection( )

=> User Authentication Module

• acl_check_host( )
• create_random_string( )
• check_user( )
• acl_getroot( )

=> Access Control Module

• check_grant( )
• check_table_access( )
• check_grant_column( )
• acl_get( )

=> Parser

• mysql_parse( )
• yyparse( )

=> Command Dispatcher

• do_command( )
• dispatch_command( )

=> Query Cache Module

• Query_cache::store_query( )
• Query_cache::send_result_to_client( )

=> Optimizer

• mysql_select( )
• JOIN::prepare( )
• JOIN::optimize( )
• JOIN::exec( )
• make_join_statistics( )
• find_best_combination( )
• optimize_cond( )

=> Table Manager

• openfrm( )
• mysql_create_frm( )
• open_table( )
• open_tables( )
• open_ltable( )
• mysql_lock_table( )

=> Table Modification Modules

• mysql_update( )
• mysql_multi_update( )
• mysql_insert( )
• mysql_create_table( )
• mysql_alter_table( )
• mysql_rm_table( )
• mysql_delete( )

=> Table Maintenance Module

• mysql_check_table( )
• mysql_repair_table( )
• mysql_backup_table( )
• mysql_restore_table( )
• mysql_optimize_table( )
• mysql_analyze_table( )

=> Status Reporting Module

• mysqld_list_processes( )
• mysqld_show( )
• mysqld_show_create( )
• mysqld_show_fields( )
• mysqld_show_open_tables( )
• mysqld_show_warnings( )
• show_master_info( )
• show_binlog_info( )

=> Storage Engine Implementations (MyISAM, InnoDB, MEMORY/HEAP, Berkeley DB, NDB)

=> Replication Master Module

• mysql_binlog_send( )

=> Replication Slave Module

• handle_slave_io( )
• handle_slave_sql( )