Mysql

From DesigningPatterns

Jump to: navigation, search

Contents

Web Links

Notes

  • It is often worthwhile to prepare Mysql commands in a file and then execute them via mysql in batch mode (rather than using interactive mysql). This allows one to keep a record of the commands executed. 
File: mysql_commands
use test;
show tables \G;  //The \G specifies to <code>mysql</code> to display the results w/ one table column per line instead of tab delimited)

shell> mysql < mysql_commands;

Features

Storage Engines

Related mysql commands: 

mysql> show storage tables; // check out the storage engines shipped with your version of mysql
mysql> show table status; //check out the storage engine used for the tables in the current database

The main storage engines are:

  • MyISAM
    • Table level locking only.
    • Writes block reads.
    • No foreign keys.
    • Does not support transactions.
    • Only storage engine to support the FULLTEXT index.
    • Only storage engine to support GIS (geographical and spatial) data using R-Tree indexes.
  • InnoDB
    • Fully ACID compliant.
    • Row level locking.
    • Writes don't block reads!
    • Supports transactions.
    • Journals transactions as well as checkpoints for quick recovery.
    • Orders row data on disk by the primary key (unlike MyISAM). This results in much faster reads of sequential data in sequential primary key order.
    • Tables take ~ 3 times as much space as their MyISAM equivalent.
  • BerkeleyDB
    • Each row is a single key value pair.
    • Supports transactions
    • Journals transactions as well as checkpoints for quick recovery.
    • Sequential table scans are slow due to the way data is stored.
    • Page-level locking for writes.
  • Memory
    • Keeps all table data in memory and doesn't persist it to disk (on a Mysql server restart, Memory tables will be empty). Schemas will be persisted.
    • Use table-level locks but are usually fast, as writes/reads to/from memory are so fast.
    • Supports hash and B-tree indexes.
    • This storage engine can be used as a cache. It is not as fast / allows a smaller dataset than other caching software (eg. memcached), but allows you to use the SQL interface, and may be worthwhile if your programs are already using Mysql.

Backups

Mysql Replication

Mysql replication involves a master mysql server replicating across any number of slaves asynchronously. In this situation, application servers (ie web servers) must send write commands to the master, and mysql read commands to any mysql machines (master and slaves) (see Mysql Scaleout System Architecture Diagram). This requires code abstraction around the mysql calls within the application.

Inside Mysql's implementation of replication, the master mysql server writes all mysql commands (involving writes to the database) to a binary log. The master runs one thread (BinLogDump) for each slave. This thread simply reads the binary log and sends it to the associated slave. The slave has two slave related threads. One thread reads the data from its open connection with the master, and writes the results to a relay log. The other thread reads the relay log and executes the commands on the local database.

Mysql replication is useful in a number of situations, including

  • scaling out a system (in which the number of reads far outweigh the number of writes). Examples include Wikipedia, or a retail site, where browsing occurs far more frequently than purchasing.
  • Running analytics. Analytics can be run without affecting the performance of the master).
  • Running backups. A slave can halt updates to the database while the backup is being performed.

Mysql commands are provided to change the master (see Switching Masters During Failover). However, Mysql does not automatically execute a master switch when the master goes down. This is the responsibility of the administrator to have scripts in place to handle this. Applications can stay informed of the master mysql server by either having a dynamic dns entry for the master, or setting up a virtual ip address for the master.

Mysql Cluster

In Mysql cluster, writes are replicated synchronously. Each node in a cluster is a "master" for a given partition, or data set. Each node is part of a node group. Inside the node group, each node must store a copy of the partitions mastered by each of the other nodes in the node group.

The target customers are companies w/ high volumes of transactions (aka lots of writes). These often are telecoms and financial companies. The Mysql Cluster FAQ includes interesting stats on Mysql clustering.

Additional Links

Retrieved from "http://wiki.designingpatterns.com/index.php/Mysql"
Personal tools