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
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
|
drop table if exists t1;
#
# Test simple create of s3 table
#
create or replace table t1 (a int, b int, c varchar(1000), key (a), key(c)) engine=aria;
Warnings:
Note 1071 Specified key was too long; max key length is 2300 bytes
insert into t1 select seq, seq+10, repeat(char(65+ mod(seq, 20)),mod(seq,1000)) from seq_1_to_10000;
alter table t1 engine=s3;
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`a` int(11) DEFAULT NULL,
`b` int(11) DEFAULT NULL,
`c` varchar(1000) DEFAULT NULL,
KEY `a` (`a`),
KEY `c` (`c`(575))
) ENGINE=S3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci PAGE_CHECKSUM=1
select * from information_schema.tables where table_schema="database" and table_name="t1";;
TABLE_CATALOG TABLE_SCHEMA TABLE_NAME TABLE_TYPE ENGINE VERSION ROW_FORMAT TABLE_ROWS AVG_ROW_LENGTH DATA_LENGTH MAX_DATA_LENGTH INDEX_LENGTH DATA_FREE AUTO_INCREMENT CREATE_TIME UPDATE_TIME CHECK_TIME TABLE_COLLATION CHECKSUM CREATE_OPTIONS TABLE_COMMENT MAX_INDEX_LENGTH TEMPORARY
def # t1 BASE TABLE S3 10 Page 10000 567 5677056 # 385024 0 NULL # # # utf8mb4_uca1400_ai_ci NULL page_checksum=1 2305843009213685760 #
show table status like "t1";
Name Engine Version Row_format Rows Avg_row_length Data_length Max_data_length Index_length Data_free Auto_increment Create_time Update_time Check_time Collation Checksum Create_options Comment Max_index_length Temporary
t1 S3 10 Page 10000 567 5677056 # 385024 0 NULL # # # utf8mb4_uca1400_ai_ci NULL page_checksum=1 # N
select a,b from t1 limit 10;
a b
1 11
2 12
3 13
4 14
5 15
6 16
7 17
8 18
9 19
10 20
select count(*) from t1;
count(*)
10000
select a,b from t1 where a between 10 and 20;
a b
10 20
11 21
12 22
13 23
14 24
15 25
16 26
17 27
18 28
19 29
20 30
explain select * from t1 where a between 10 and 20;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 range a a 5 NULL # Using index condition
insert into t1 values (1,1);
ERROR HY000: Table 't1' is read only
update t1 set b=100 where a=1;
ERROR HY000: Table 't1' is read only
delete from t1 where a>10;
ERROR HY000: Table 't1' is read only
#
# Analyze, repair, optimize and check table
#
set @@use_stat_tables='never';
truncate mysql.table_stats;
check table t1 fast;
Table Op Msg_type Msg_text
database.t1 check status Table is already up to date
check table t1 quick;
Table Op Msg_type Msg_text
database.t1 check status OK
check table t1 extended;
Table Op Msg_type Msg_text
database.t1 check status OK
analyze table t1;
Table Op Msg_type Msg_text
database.t1 analyze status Table 'database.t1' is read only
analyze table t1 persistent for all;
Table Op Msg_type Msg_text
database.t1 analyze status Table 'database.t1' is read only
database.t1 analyze status Engine-independent statistics collected
database.t1 analyze status OK
repair table t1;
Table Op Msg_type Msg_text
database.t1 repair Error Table 't1' is read only
database.t1 repair status Operation failed
optimize table t1;
Table Op Msg_type Msg_text
database.t1 optimize Error Table 't1' is read only
database.t1 optimize status Operation failed
select * from mysql.table_stats;
db_name table_name cardinality
database t1 10000
#
# Converting table back to Aria
#
alter table t1 engine=aria;
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`a` int(11) DEFAULT NULL,
`b` int(11) DEFAULT NULL,
`c` varchar(1000) DEFAULT NULL,
KEY `a` (`a`),
KEY `c` (`c`(575))
) ENGINE=Aria DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci PAGE_CHECKSUM=1
select a,b from t1 limit 10;
a b
1 11
2 12
3 13
4 14
5 15
6 16
7 17
8 18
9 19
10 20
select count(*) from t1;
count(*)
10000
delete from t1 where a=1;
drop table t1;
#
# status
#
show variables like "s3%";
Variable_name Value
s3_access_key X
s3_block_size X
s3_bucket X
s3_debug X
s3_host_name X
s3_no_content_type X
s3_pagecache_age_threshold X
s3_pagecache_buffer_size X
s3_pagecache_division_limit X
s3_pagecache_file_hash_size X
s3_port X
s3_protocol_version X
s3_provider X
s3_region X
s3_replicate_alter_as_create_select X
s3_secret_key X
s3_slave_ignore_updates X
s3_ssl_no_verify X
s3_use_http X
show variables like "s3_slave%";
Variable_name Value
s3_slave_ignore_updates OFF
show variables like "s3_replicate%";
Variable_name Value
s3_replicate_alter_as_create_select ON
show status like "s3%";
Variable_name Value
S3_pagecache_blocks_not_flushed X
S3_pagecache_blocks_unused X
S3_pagecache_blocks_used X
S3_pagecache_read_requests X
S3_pagecache_reads X
|
