Notes on MySQL performance optimization and Architecture Design

Log Files
Error Log
BINLOG mysql-bin.index
Update log
Query log
Slow query log
InnoDB online redo log

Architecture

Two Layers

SQL Layer

Storage engine Layer

MySQL tools

MySQL

Mysqladmin

Mysqldump

Mysqlimport

Mysqlbinlog

Mysqlcheck

Mysqlhotcopy

Storage Engine

MyISAM

Three indexes are supported.

B-tree

R-tree

Full-text

Only table-locking

InnoDB

Features:

Transaction Security

Multi-version Data Reading

Supports Foreign keys

The locking mechanism is improved, and row-locking is supported.

Better crash recovery

NDB Cluster

High Availability design ideas and solutions

Use replication to implement high-availability architecture

Advantage: simple deployment, easy implementation, and easy maintenance

Disadvantage: if the master topic hardware fails and cannot be recovered, some data that is not transmitted to the slave may be lost.

Use MySQL cluster to achieve overall high availability

Advantage: high availability and good performance. Real-time synchronization of redundant data copies

Disadvantages: complicated maintenance, fast product updates, and bugs

Use drdb to ensure high data security and reliability

Advantage: drdb is powerful and supports data mirroring across physical hosts at the underlying block device level. Io operation sequence is saved to ensure consistency

Disadvantage: the non-distributed file system environment cannot support simultaneous visibility of image data. The performance and reliability are both anchored at a high maintenance cost.

Common Open Source Monitoring Software

Nagios

MRTG

Cacti