-
Notifications
You must be signed in to change notification settings - Fork 0
/
HP.mysql.txt
76 lines (67 loc) · 1.62 KB
/
HP.mysql.txt
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
architecture:
connect/auth
query cache/parser/optimizer
storage engine
lock:
granularity:
table lock
row lock
implemented in storage engine
cmd:
lock tables xxx read|write;
unlock tables;
sql isolation: within transaction
read uncommitted
read committed
repeatable read(mysql default)
serializable #lock every row
autocommit:
show variables like 'autocommit';
set autocommit=1;
Multiversion Concurrency Control(MVCC):
row level
applied to:
read committed
repeatable read
InnoDB:
good at many transaction with less rollback
default transactional storage engine for mysql
use MVCC
default repeatable read isolation
clustered index table
optimization:
predictable read-ahead
adaptive hash index
insert buffer
...
MyISAM engine:
good at:
select and insert, fast logging
drawback:
data lost when crash
default storage engine, ver >5.1
store table in 2 files:
data file
index file
features:
lock entire table
delayed_key_write
compressed:
use myisampack
read-only
faster
Table conversion:
alter table
mysqldump + mysqlimport
create and select
create table pet2 like pet;
alter table pet2 engine=MyISAM;
insert into pet2 select * from pet;
benchmark strategies:
full-stack
single component
benchmark measure:
throughput
response time or latency
concurrency
scalability