CentOS MySQL in the third session MySQL object MySQL architecture mysql log data type

CentOS MySQL in the third session MySQL object MySQL architecture mysql log data type

Last two lessons
1. Installation of MySQL
2. mysql startup mode

The object in MySQL

The first one today: MySQL objects

See which databases are currently available:
show databases;

drop database test;
TRUNCATE TABLE mysql.db;

MySQL view is treated as table, there is no separate command to back up the view, only to back up the table

Information_schema
Dictionary Library

Performance_schema
Performance-Related dictionary libraries

MySQL is a storage-engine-oriented solution for DB
Show engines;

There are some show engines, no engines listed.
Tokudb
INFORBRIGHT/INFINIDB OLAP, column-storage engine
MARIADB Dblink:connect Engine

The pit of the memory engine
Memory Engine No Persistence
For memory tables, the system automatically initiates a TRUNCATE table MEM_TB when the database restarts
For memory tables, it is best to ignore him in the copy

Mysql.server, mysqld_safe-> mysqld

MySQL Architecture

Access Layer
SQL interface, parsing, optimization, Cache/buffer
Storage Engine
File system

Access Layer:
Authentication, threading
Every connection in MySQL is a thread

The degree of concurrency of a system is limited
The number of threads is too many, but the system's processing power drops
As the number of connections rises, the performance of the counter decreases
64 Connections below <5.1
5.5 128
<128 a connection
5.6 < 200 connections
5.7 <200, 300 connections

Case

Wu Bingxi (82565387) 20:55:20
LVS hangs 5 web, back end: 1 Groups of caches, three DB
Wu Bingxi (82565387) 20:56:06
10 units
More than 6,000 connections on a single set of DB

Workaround: Introduce middleware
Fee: Kingshard
Free: Thread-pool (Percona, mariadb belongs to free of charge)

Core processing layer of the server layer

SQL interface; SQL interface

Struct
Analytical
Get the path
Cache & Buffer

Enterprise Interface Management Tools

Another layer down is the storage engine.

Innodb

Store file system above
Wu Bingxi (82565387) 21:04:52
Run MySQL XFS as standard

MySQL Log

Error log
Generl Log
Binary log
Slow log

When troubleshooting db, I want to know what logs are in MySQL
Error log errors
Wu Bingxi (82565387) 21:09:47
Just look [ERROR] and [WARNING]
[Note] Ignore

Wu Bingxi (82565387) 21:1
Database error, it will definitely report the wrong log
1. Take this error log to search the source, on the Linux machine
Google
Take a look at the notes near the source code

2. Encountering error logs
http://bugs.mysql.com/
a230-Pengshusen-Shenzhen (10031145) 21:19:13
When you search for a problem, you go in.

General log regular logs, only suitable for development environment open
All interactions with MySQL will log, default is not open
Global level
Set global general_log = on;
Session level development test environment best with session level
Set general_log = on;
General log is used to analyze mysqldump principle mydumper principle Innbackupex principle
General Log Location

Binary log
All writes to the database will be recorded in binary log.
Copy of the data library is also used by binary log
Extract Binlog count the number of insert,update per table

Slow log
Execute_time > Long-query-time will be recorded
Long_query_time = 10s
suggested change to 1s
Attention:
Dml:insert, UPDATE, delete does not record the time the lock waits in processing
The time the Select lock waits is recorded
MySQL Code comment Yes

Data type
To make the database run faster, let's choose a suitable data type

What is the essence of doing?
Save IO
Database SQL tuning
99% are both base-saving IO

SAS disk
Wu Bingxi (82565387) 21:50:02
15000 rpm, revolutions per minute 250 rpm per second

IOPS per second
Random io IOPS
Wu Bingxi (82565387) 21:54:49
Actual, probably only: 150 ioPS

PCIe Card 10-45w IOPS

MySQL's data page is 16k
Block 4k in the Linux file system
Sector: 512 bytes
Wu Bingxi (82565387) 22:00:26
Iostat ioPS is read RAID card cache at work
If your raid has a cache, don't save 10 dollars of RAID card battery

RAID Card 512M Price 1k-2k
RAID Card 1G Price 3-4k

Special attention to type overflow
tinyint

Wu Bingxi (82565387) 22:06:50
tinyint
128
0-256
256-300
Wu Bingxi (82565387) 22:07:28
Brush integral
5.1, 5.5
Wu Bingxi (82565387) 22:08:34
How many bytes each type occupies
Wu Bingxi (82565387) 22:09:14
If we need to calculate the president in the future, we need

Int (n) What does this n mean?
Zerofile display width, if not zerofile, then add (N) meaningless
int 4,200,000,010 digits up to int (10), even if int (20) can only display 10 bits

Wu Bingxi (82565387) 22:16:12
Can a single table support 4.2 billion data?
Bigint

(product) [Email protected] [test]> INSERT into T1 (ID, name) VALUES (POW (2,31)-1, ' wubx ');
Query OK, 1 row affected (0.01 sec)

(product) [Email protected] [test]> select * from T1;
+------------+------+
| ID | name |
+------------+------+
| 2147483647 | WUBX | No use unsigned.
+------------+------+
1 row in Set (0.00 sec)

(product) [Email protected] [test]> INSERT into T1 (ID, name) VALUES (POW (2,32), ' wubx ');
ERROR 1264 (22003): Out of Range value to column ' ID ' at row 1

Wu Bingxi (82565387) 22:22:28
Fixed decimal point type
Wu Bingxi (82565387) 22:22:37
Decimal (decimal is rounded)
Salary Decimal (8,2)
Decimal (M,n) M represents the maximum length, also refers to the byte
n denotes a decimal point
M Maximum Value 65
N<m
Solution: The bank to save all is divided into units stored, there will be no decimal point

Wu Bingxi (82565387) 22:28:22
Decimal (10,0)
Wu Bingxi (82565387) 22:28:47
10 bytes
Any alternatives?
Scheme: Int (10) Two integer type

F

CentOS MySQL in the third session MySQL object MySQL architecture mysql log data type