MySQL Advanced
Download
1 / 27

MySQL Advanced - PowerPoint PPT Presentation


  • 87 Views
  • Uploaded on

MySQL Advanced. MySQL Replication MySQL Cluster MySQL Partitioning. António Amorim, Carlos Jesus. CU1 - DBWorkshop 9-10 /Feb/09. MySQL Advanced. MySQL Replication. António Amorim, Carlos Jesus. CU1 - DBWorkshop 9-10 /Feb/09. MySQL Replication Concepts.

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about ' MySQL Advanced' - ady


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.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript

MySQL Advanced

MySQL Replication

MySQL Cluster

MySQL Partitioning

António Amorim, Carlos Jesus. CU1 - DBWorkshop 9-10 /Feb/09


MySQL Advanced

MySQL Replication

António Amorim, Carlos Jesus. CU1 - DBWorkshop 9-10 /Feb/09


MySQL Replication

Concepts

MySQL replication tracks updates performed in a MySQL master logs and updates one or more slave servers. The mechanism involved in MySQL replication requires binary logging to be active.

-Asynchronous replication, a slave doesn't need to be always active.

-MySQL replication can be used to scale-out queries among different servers.

-MySQL replication is a good solution for performing live backups.

- Long distance data distribution.

António Amorim, Carlos Jesus. CU1 - DBWorkshop 9-10 /Feb/09


MySQL Replication

Architecture

MySQL replication architecture:

- Each update performed in a database is replicated from the master to the slaves.

- Only slaves keep track of their replication status.

- One may set at what point the replication begins.

António Amorim, Carlos Jesus. CU1 - DBWorkshop 9-10 /Feb/09


MySQL Replication

setup

  • MySQL Master

  • Turn on binary log and set the server id restart mysql server:

  • server-id =1

  • log_bin =/var/log/mysql/mysql-bin.log

  • binlog_do_db = gaia_test

  • Create a user for replication:

  • GRANT REPLICATION SLAVE ON *.* TO 'rep_slave'@'slave_ip' IDENTIFIED BY 'password';

MySQL slave

Set the master properties in /etc/mysql/my.cnf:

server-id=2

master-host = master_ip

master-user = rep_slave

master-password = password

replicate-do-db={db_name}

António Amorim, Carlos Jesus. CU1 - DBWorkshop 9-10 /Feb/09


MySQL Replication

setup

  • MySQL Master

  • Show master log position:

  • mysql > show master status \G;

  • File: mysql-bin.000011

  • Position: 784

  • Binlog_Do_DB: gaia_test

  • MySQL slave

  • Change log position:

  • CHANGE MASTER TO

  • MASTER_HOST=server_IP,

  • MASTER_LOG_FILE=mysql-bin,

  • MASTER_LOG_POS=784;

  • Start Slave:

  • mysql > slave start;

  • See slave status and notice the SLAVE_* variables for errors (No = bad) :

  • Show slave status\G;

António Amorim, Carlos Jesus. CU1 - DBWorkshop 9-10 /Feb/09


MySQL Advanced

MySQL Cluster

António Amorim, Carlos Jesus. CU1 - DBWorkshop 9-10 /Feb/09


MySQL Cluster

MySQL cluster is designed to provide near perfect availability through a shared nothing architecture, in turn this means that all the nodes in a MySQL cluster can be replicated for failover.

MySQL cluster is a in memory database, which means that all data must be located in RAM, since version 5.1.6 a mechanism is available to store data on disk although Indexes must still fit in ram.

All data in MySQL is horizontally partitioned, across different storage nodes, which means that all table data is split across the storage nodes.

António Amorim, Carlos Jesus. CU1 - DBWorkshop 9-10 /Feb/09


MySQL Cluster

MySQL cluster architecture consists of three types nodes:

Storage nodes: All data is stored in this nodes.

SQL nodes: Standard MySQL nodes, used for connection.

Management nodes: Used to change the setup of the cluster.

António Amorim, Carlos Jesus. CU1 - DBWorkshop 9-10 /Feb/09


MySQL Cluster

Setup (Debian)‏

To setup a 3 node (API + engine + management) MySQL Cluster you must update 2 files:

Edit /etc/mysql/my.cnf and add:

[mysqld]

ndbcluster

[MYSQL_CLUSTER]

ndb-connectstring=127.0.0.1

António Amorim, Carlos Jesus. CU1 - DBWorkshop 9-10 /Feb/09


MySQL Cluster

Setup (debian)

Create a new configuration file in /etc/mysql/ndb_mgmd.cnf:

[ndbd default]

NoOfReplicas=1

DataMemory=80M

IndexMemory=18M

[ndb_mgmd]

Id=1

hostname=localhost

datadir=/var/lib/mysql-cluster

[mysqld]

António Amorim, Carlos Jesus. CU1 - DBWorkshop 9-10 /Feb/09


MySQL Cluster

Startup order:

#1 Management nodes:

invoke-rc.d mysql-ndb-mgm start (start-initial)‏

#2 Data nodes

invoke-rc.d mysql-ndb start

#3 SQL nodes

invoke-rc.d mysql start

António Amorim, Carlos Jesus. CU1 - DBWorkshop 9-10 /Feb/09


MySQL Cluster

Testing the installation:

bash$ ndb-mgm -e show

Connected to Management Server at: 127.0.0.1:1186

Cluster Configuration

---------------------

[ndbd(NDB)] 1 node(s)‏

id=2 @127.0.0.1 (Version: 5.1.22, Nodegroup: 0, Master)‏

[ndb_mgmd(MGM)] 1 node(s)‏

id=1 @127.0.0.1 (Version: 5.1.22)‏

[mysqld(API)] 1 node(s)‏

id=3 @127.0.0.1 (Version: 5.1.22)‏

António Amorim, Carlos Jesus. CU1 - DBWorkshop 9-10 /Feb/09


MySQL Cluster

Testing the installation:

mysql> create table person (id int, name varchar(32)) engine = ndb;

Query OK, 0 rows affected (2,34 sec)‏

mysql> insert into person (id, name ) values ('11','carlos');

Query OK, 1 row affected (0,00 sec)‏

mysql> select * from person;

+------+--------+

| id | name |

+------+--------+

| 11 | carlos |

+------+--------+

1 row in set (0,00 sec)‏

António Amorim, Carlos Jesus. CU1 - DBWorkshop 9-10 /Feb/09


MySQL Advanced

MySQL Partitioning

António Amorim, Carlos Jesus. CU1 - DBWorkshop 9-10 /Feb/09


MySQL Partitioningconcepts

Typical SQL doesn't provide guidance to underling physical storage layer, Partitioning allows you to split a table across multiple files, through the usage of a special partitioning rule called partitioning function.

Partitioning can only take place in engines that support partitioning, engines like MyISAM or InnoDB and not like CSV or BlackHole.

The big performance boost in database partition is that partitions that do not satisfy a certain rule are not scanned, this is called partition pruning.

António Amorim, Carlos Jesus. CU1 - DBWorkshop 9-10 /Feb/09


MySQL PartitioningPartition types

  • RANGE partitioning: Assigns rows to partitions based on column values falling within a given range.

  • • LIST partitioning: Similar to partitioning by range, except that the partition is selected based on columns matching one of a

  • Partitioning set of discrete values.

  • • HASH partitioning: A partition is selected based on the value returned by a user-defined expression that operates on column values in rows to be inserted into the table. The function may consist of any expression valid in MySQL that yields a non-negative integer value.

  • • KEY partitioning: Similar to partitioning by hash, except that only one or more columns to be evaluated are supplied, and the MySQL server provides its own hashing function.

António Amorim, Carlos Jesus. CU1 - DBWorkshop 9-10 /Feb/09


MySQL Partitioningexamples

Range partition:

CREATE TABLE t1 (

r_name VARCHAR(50) NOT NULL,

region_code TINYINT UNSIGNED NOT NULL

)‏

PARTITION BY RANGE( region_code ) (

PARTITION p0 VALUES LESS THAN (64),

PARTITION p1 VALUES LESS THAN (128),

PARTITION p3 VALUES LESS THAN MAXVALUE

);

António Amorim, Carlos Jesus. CU1 - DBWorkshop 9-10 /Feb/09


MySQL Partitioningexamples

List Partition:

CREATE TABLE employees (

id INT NOT NULL,

fname VARCHAR(30),

lname VARCHAR(30),

hired DATE NOT NULL DEFAULT '1970-01-01',

separated DATE NOT NULL DEFAULT '9999-12-31',

job_code INT,

store_id INT )‏

PARTITION BY LIST(store_id) (

PARTITION pNorth VALUES IN (16,15,3,5,6,9,17),

PARTITION pEast VALUES IN (1,2,8,10,11,19,20),

PARTITION pWest VALUES IN (7, 4,12,13,14,18) );

António Amorim, Carlos Jesus. CU1 - DBWorkshop 9-10 /Feb/09


MySQL Partitioningexamples

Hash Partition:

CREATE TABLE employees (

id INT NOT NULL,

fname VARCHAR(30),

lname VARCHAR(30),

hired DATE NOT NULL DEFAULT '1970-01-01',

separated DATE NOT NULL DEFAULT '9999-12-31',

job_code INT,

store_id INT )‏

PARTITION BY HASH(store_id)‏

PARTITIONS 5;

António Amorim, Carlos Jesus. CU1 - DBWorkshop 9-10 /Feb/09


MySQL Partitioningexamples

Key Partition:

CREATE TABLE k1 (

id INT NOT NULL PRIMARY KEY,

name VARCHAR(20))‏

PARTITION BY KEY()‏

PARTITIONS 4;

CREATE TABLE k1 (

id INT NOT NULL,

name VARCHAR(20),

UNIQUE KEY (id) )‏

PARTITION BY KEY()‏

PARTITIONS 4;

António Amorim, Carlos Jesus. CU1 - DBWorkshop 9-10 /Feb/09


Q&A

  • For more information:More on MySQL-cluster and other studies:SIM Studies at the GAIA WIKIThe technical note:GAIA-C1-TN-SIM-CDJ-001-1.pdf

  • GAIA-C1-TN-SIM-AAB-001-01.pdf

António Amorim, Carlos Jesus. CU1 - First Database Testing Meeting, 20/Nov/08


MySQL Benchmarks

MySQL Single server

MySQL Cluster

Oracle

António Amorim, Carlos Jesus. CU1 - DBWorkshop 9-10 /Feb/09


Test objectives

  • Past studies evaluated the architecture and performance of MySQL Cluster and PostgreSQL solutions for the Gaia Databases.

  • MySQL Cluster:

    • MySQL-Cluster

      • Poor performance

      • Redundancy benefits

    • PostgreSQL

      • Sends 2x more data than MySQL

      • Poor Bytea performance

Network load(400K objects)‏

PostgreSQL: 2072M

MYSQL: 1050 Mb

ORACLE-XE: 520 Mb

António Amorim, Carlos Jesus. CU1 - First Database Testing Meeting, 20/Nov/08


Single MySQL Server

In order to pinpoint bottlenecks associated with MySQL, a series of basic tests were performed, these new tests made use of the available MDBExtractorIngestor package.

Control and Monitor:

Available at the local infrastructure was the SMS (Supervisor Monitor Scheduler) batch system, we deployed in one node the database and in another the Ingestor.

At the local infrastructure was available the SMS (Supervisor Monitor Scheduler) batch system.

The tests basic 2 node client server model the nodes where as following:

Hardware setup

António Amorim, Carlos Jesus. CU1 - First Database Testing Meeting, 20/Nov/08


Tests For

MDBExtractorIngestor

JdbcObjectUpdater.java

MySQL Ingestor, Old and new JdbcObjectUpdater, 4.5Gb of data.

António Amorim, Carlos Jesus. CU1 - First Database Testing Meeting, 20/Nov/08


Tests For

DbBenchmark

JdbcObjectUpdater.java

MySQL Ingestor, Old and new JdbcObjectUpdater, 1Gb of data.

Oracle datapump (local), 1Gb of data.

António Amorim, Carlos Jesus. CU1 - First Database Testing Meeting, 20/Nov/08


ad