1
00:00:00,000 --> 00:00:08,469
foreign

2
00:00:00,500 --> 00:00:08,469
[Music]

3
00:00:11,660 --> 00:00:17,039
welcome back to a smorgasbord of

4
00:00:14,880 --> 00:00:19,859
delicious new Maria DB

5
00:00:17,039 --> 00:00:22,020
Daniel works at the Maria DB Foundation

6
00:00:19,859 --> 00:00:24,840
contributing full-time and supporting

7
00:00:22,020 --> 00:00:27,599
related aspects of the Maria DB software

8
00:00:24,840 --> 00:00:30,119
ecosystem with the next long-term Maria

9
00:00:27,599 --> 00:00:32,579
DB version having been released Daniel

10
00:00:30,119 --> 00:00:36,059
will outline the very delicious database

11
00:00:32,579 --> 00:00:38,399
changes of Maria DB contributed by Maria

12
00:00:36,059 --> 00:00:39,800
DB and Community developers please

13
00:00:38,399 --> 00:00:43,559
welcome Daniel

14
00:00:39,800 --> 00:00:45,780
[Applause]

15
00:00:43,559 --> 00:00:47,879
thank you Fiona

16
00:00:45,780 --> 00:00:50,879
I begin today by acknowledging the

17
00:00:47,879 --> 00:00:52,100
dundry people warring people of the

18
00:00:50,879 --> 00:00:54,239
cooler Nation

19
00:00:52,100 --> 00:00:57,000
custodians of the land on which We

20
00:00:54,239 --> 00:00:59,039
Gather today and pay my respects to

21
00:00:57,000 --> 00:01:02,160
their Elders past and present

22
00:00:59,039 --> 00:01:05,040
I extend this respect and welcome to the

23
00:01:02,160 --> 00:01:07,519
Aborigines and tolerstrade Islanders

24
00:01:05,040 --> 00:01:07,519
here today

25
00:01:07,640 --> 00:01:13,200
welcome everyone I hope you can build up

26
00:01:10,979 --> 00:01:15,119
your appetite before lunch

27
00:01:13,200 --> 00:01:16,979
um I'm running actually in a browser in

28
00:01:15,119 --> 00:01:19,979
a binder session if you want to actually

29
00:01:16,979 --> 00:01:22,259
follow along and play with your own test

30
00:01:19,979 --> 00:01:25,439
versions of the same samples please do

31
00:01:22,259 --> 00:01:27,060
so and it won't bother me whatsoever

32
00:01:25,439 --> 00:01:30,600
because you know I'm running a separate

33
00:01:27,060 --> 00:01:33,560
instance thanks to the kind guys and

34
00:01:30,600 --> 00:01:33,560
girls at binder

35
00:01:33,720 --> 00:01:38,340
so when I say new features what I'm

36
00:01:36,780 --> 00:01:42,240
going to be talking today about

37
00:01:38,340 --> 00:01:44,119
everything that came after 10-6 so that

38
00:01:42,240 --> 00:01:46,880
the 10 same are Beyond

39
00:01:44,119 --> 00:01:49,200
10.6 was the last GA

40
00:01:46,880 --> 00:01:52,500
as of

41
00:01:49,200 --> 00:01:55,380
almost two years ago so what's happened

42
00:01:52,500 --> 00:01:58,439
since 106 came out is we've done a

43
00:01:55,380 --> 00:02:04,259
number of releases on a one-year support

44
00:01:58,439 --> 00:02:07,259
cycle on 10 7 10 8 10 9 and now 10 in to

45
00:02:04,259 --> 00:02:09,739
10.11 is the long term support release

46
00:02:07,259 --> 00:02:12,420
that's going until next

47
00:02:09,739 --> 00:02:15,300
Debian and Ubuntu

48
00:02:12,420 --> 00:02:17,879
so since some of you may not have been

49
00:02:15,300 --> 00:02:20,340
exposed to 10 7 and Beyond I'm going to

50
00:02:17,879 --> 00:02:22,800
actually start there and after some

51
00:02:20,340 --> 00:02:24,360
questions uh if we want to backtrack a

52
00:02:22,800 --> 00:02:26,459
little bit to you know what actually

53
00:02:24,360 --> 00:02:29,840
happened in 10-6 and before I can

54
00:02:26,459 --> 00:02:32,640
actually go into some of those there

55
00:02:29,840 --> 00:02:36,360
so the first thing I'm going to point

56
00:02:32,640 --> 00:02:39,239
out here a feature is that uuid is now

57
00:02:36,360 --> 00:02:40,860
data type for those using postgres

58
00:02:39,239 --> 00:02:43,080
probably had this already for a number

59
00:02:40,860 --> 00:02:44,580
of years but I'll say we're slowly

60
00:02:43,080 --> 00:02:48,260
catching up

61
00:02:44,580 --> 00:02:51,599
um we're catching up in in a fast way

62
00:02:48,260 --> 00:02:55,440
when we added some other dud types later

63
00:02:51,599 --> 00:02:59,540
on today there's actually a plug-in API

64
00:02:55,440 --> 00:03:02,160
in the Marie DB for adding data types

65
00:02:59,540 --> 00:03:04,220
that will hopefully mature at the time

66
00:03:02,160 --> 00:03:08,720
but for the moment

67
00:03:04,220 --> 00:03:11,959
we have a database we can use test

68
00:03:08,720 --> 00:03:16,159
and then we're hitting the right button

69
00:03:11,959 --> 00:03:19,019
yep and you know we insert insert it

70
00:03:16,159 --> 00:03:21,920
this is what happens here

71
00:03:19,019 --> 00:03:21,920
repeat things

72
00:03:23,420 --> 00:03:29,700
and that syntax been there for a while

73
00:03:25,920 --> 00:03:32,480
so uuids look like text treated like

74
00:03:29,700 --> 00:03:35,400
text push in as texts come out as text

75
00:03:32,480 --> 00:03:38,400
but they're actually stored in the the

76
00:03:35,400 --> 00:03:42,360
compressed eight by format

77
00:03:38,400 --> 00:03:45,239
can also convert you know 32 byte

78
00:03:42,360 --> 00:03:47,280
binaries as that and they go there as a

79
00:03:45,239 --> 00:03:50,519
unique value

80
00:03:47,280 --> 00:03:52,200
um and then if we go through you know

81
00:03:50,519 --> 00:03:53,159
all the different ways to represent they

82
00:03:52,200 --> 00:03:55,799
they

83
00:03:53,159 --> 00:03:58,560
look like a type

84
00:03:55,799 --> 00:04:00,420
like any type there are invalid values

85
00:03:58,560 --> 00:04:01,799
like Long Live that bear

86
00:04:00,420 --> 00:04:04,260
sorry bear

87
00:04:01,799 --> 00:04:07,200
um you're not a value value and you get

88
00:04:04,260 --> 00:04:09,860
incorrect types as you may expect

89
00:04:07,200 --> 00:04:09,860
on that

90
00:04:10,260 --> 00:04:18,600
so next up we have a inet 4 data type

91
00:04:14,939 --> 00:04:20,639
and you go whoa what about inet inet 6 I

92
00:04:18,600 --> 00:04:22,580
was like actually inep 6 was added way

93
00:04:20,639 --> 00:04:25,800
back in 10.5

94
00:04:22,580 --> 00:04:30,180
and so the the quick little refresher on

95
00:04:25,800 --> 00:04:31,740
onet 6 is that you know it's a data type

96
00:04:30,180 --> 00:04:35,880
and this is when we first introduced

97
00:04:31,740 --> 00:04:38,040
data types and in that you can do um IP

98
00:04:35,880 --> 00:04:39,000
version for math addresses and ipvision

99
00:04:38,040 --> 00:04:42,240
six

100
00:04:39,000 --> 00:04:44,340
as you expect with the data type no

101
00:04:42,240 --> 00:04:48,180
matter if you use the compressed form of

102
00:04:44,340 --> 00:04:51,060
inet version 6 or the extended version

103
00:04:48,180 --> 00:04:53,240
um they are exactly the same so we

104
00:04:51,060 --> 00:04:56,400
return a result that way

105
00:04:53,240 --> 00:04:58,560
now back to inet version four

106
00:04:56,400 --> 00:05:01,020
um strangely enough it looks like I know

107
00:04:58,560 --> 00:05:03,240
version four you put ipv4 with types and

108
00:05:01,020 --> 00:05:06,120
you receive IP version four types out

109
00:05:03,240 --> 00:05:08,880
and I guess I call data types what we

110
00:05:06,120 --> 00:05:11,040
try to do in the databases to store them

111
00:05:08,880 --> 00:05:13,460
as small as possible so you can fit as

112
00:05:11,040 --> 00:05:13,460
much in

113
00:05:13,919 --> 00:05:21,000
a big jump that sort of happened in

114
00:05:16,979 --> 00:05:23,720
Marie DB 10.10 was that we have actually

115
00:05:21,000 --> 00:05:26,699
updated the UCA standard

116
00:05:23,720 --> 00:05:31,139
collations or added explicitly at the

117
00:05:26,699 --> 00:05:33,300
UCA standards of you say a11 which came

118
00:05:31,139 --> 00:05:36,120
out last year

119
00:05:33,300 --> 00:05:38,639
I said the year before she's losing

120
00:05:36,120 --> 00:05:41,240
track what year is it again maybe I knew

121
00:05:38,639 --> 00:05:41,240
that year type

122
00:05:42,620 --> 00:05:47,100
oh

123
00:05:44,300 --> 00:05:50,160
he's trying to confuse me

124
00:05:47,100 --> 00:05:52,400
um so what we get in the correlations is

125
00:05:50,160 --> 00:05:55,320
a huge bunch of things

126
00:05:52,400 --> 00:05:58,259
on there so if we just look at the

127
00:05:55,320 --> 00:06:01,639
initial ones so far what you may

128
00:05:58,259 --> 00:06:05,160
recognize if you've done collations in

129
00:06:01,639 --> 00:06:07,259
Marie DB before she got a case instead

130
00:06:05,160 --> 00:06:10,020
of in a case insensitive

131
00:06:07,259 --> 00:06:13,259
what the lighter standards of the USAA

132
00:06:10,020 --> 00:06:15,080
ad is an accent insensitive and an

133
00:06:13,259 --> 00:06:19,020
accent sensitive

134
00:06:15,080 --> 00:06:20,639
type as well for those where it matters

135
00:06:19,020 --> 00:06:23,400
on that

136
00:06:20,639 --> 00:06:25,500
and like before we've got a notepad

137
00:06:23,400 --> 00:06:28,800
options and this will

138
00:06:25,500 --> 00:06:31,380
um the collations is the order of a

139
00:06:28,800 --> 00:06:33,720
character set and you know what makes

140
00:06:31,380 --> 00:06:36,240
things equivalent and the accent

141
00:06:33,720 --> 00:06:39,240
incenses of the case insensitive

142
00:06:36,240 --> 00:06:41,460
um reduce the equivalence down and

143
00:06:39,240 --> 00:06:43,139
there's also things like contractions

144
00:06:41,460 --> 00:06:44,639
and various other things that I

145
00:06:43,139 --> 00:06:47,940
encourage you not to ask me about

146
00:06:44,639 --> 00:06:49,740
because it's almost standard and you can

147
00:06:47,940 --> 00:06:51,120
read those and if you do hopefully

148
00:06:49,740 --> 00:06:53,460
someone else in the audience knows

149
00:06:51,120 --> 00:06:56,340
better than me about that

150
00:06:53,460 --> 00:06:58,259
uh like the the previous standards

151
00:06:56,340 --> 00:07:00,720
there's ordering for you know everything

152
00:06:58,259 --> 00:07:04,020
from Icelandic Latvian Romanian

153
00:07:00,720 --> 00:07:06,360
celebrity and polish Estonian Spanish

154
00:07:04,020 --> 00:07:08,819
blah blah blah blah blah blah blah blah

155
00:07:06,360 --> 00:07:10,560
there's a lot of them never trust a

156
00:07:08,819 --> 00:07:11,639
standards body to you know go on the

157
00:07:10,560 --> 00:07:13,680
brevity

158
00:07:11,639 --> 00:07:16,340
I'm sure they're paid by the uh the

159
00:07:13,680 --> 00:07:16,340
paperweight

160
00:07:16,979 --> 00:07:21,840
um collations

161
00:07:18,539 --> 00:07:24,840
um go back to like a utf-8 character set

162
00:07:21,840 --> 00:07:30,259
um it's just like you'd expect and they

163
00:07:24,840 --> 00:07:30,259
go with any of them so yeah that's them

164
00:07:31,080 --> 00:07:35,759
other things we've been improving are

165
00:07:33,180 --> 00:07:38,340
things like the the Json support so

166
00:07:35,759 --> 00:07:41,900
we've got like a Jason equals

167
00:07:38,340 --> 00:07:41,900
um obviously that

168
00:07:42,960 --> 00:07:48,139
um you know uh the spaces don't matter

169
00:07:44,880 --> 00:07:51,240
and Jason as to where they are however

170
00:07:48,139 --> 00:07:53,099
what does matter is the order of a raise

171
00:07:51,240 --> 00:07:55,500
if we swapped around they're no longer

172
00:07:53,099 --> 00:07:59,360
the same Json object

173
00:07:55,500 --> 00:08:02,819
um so that's useful there

174
00:07:59,360 --> 00:08:05,099
sometimes you may want to

175
00:08:02,819 --> 00:08:08,699
um normalize them all to the same thing

176
00:08:05,099 --> 00:08:11,400
so I've developed an algorithm here that

177
00:08:08,699 --> 00:08:12,860
normalizes all Json objects into the

178
00:08:11,400 --> 00:08:18,440
same representation

179
00:08:12,860 --> 00:08:21,180
and what that means is that we can

180
00:08:18,440 --> 00:08:22,800
define a generated column as the

181
00:08:21,180 --> 00:08:23,940
function of the normalized version of

182
00:08:22,800 --> 00:08:27,000
the value

183
00:08:23,940 --> 00:08:31,800
add a unique key on the output of that

184
00:08:27,000 --> 00:08:33,479
function and now what we've got is such

185
00:08:31,800 --> 00:08:34,140
that

186
00:08:33,479 --> 00:08:38,339
um

187
00:08:34,140 --> 00:08:40,800
you can't insert duplicates

188
00:08:38,339 --> 00:08:43,570
so here that you know the order of

189
00:08:40,800 --> 00:08:43,979
things in an object is

190
00:08:43,570 --> 00:08:44,820
[Music]

191
00:08:43,979 --> 00:08:47,160
um

192
00:08:44,820 --> 00:08:49,200
the equivalence so here you've got a

193
00:08:47,160 --> 00:08:50,820
uniqueness value that you can apply on

194
00:08:49,200 --> 00:08:53,100
it

195
00:08:50,820 --> 00:08:55,500
uh Jason pretty

196
00:08:53,100 --> 00:08:57,779
um was pretty much an alias of Json

197
00:08:55,500 --> 00:09:00,480
details what we already had I've done

198
00:08:57,779 --> 00:09:02,100
this for MySQL compatibility and this

199
00:09:00,480 --> 00:09:04,860
was something actually was contributed

200
00:09:02,100 --> 00:09:08,040
by our user base or user community in

201
00:09:04,860 --> 00:09:09,240
general so it just outputs it in a

202
00:09:08,040 --> 00:09:12,860
pretty way

203
00:09:09,240 --> 00:09:16,800
and it was really simple to implement

204
00:09:12,860 --> 00:09:19,380
CSC 32c I know it's probably not the uh

205
00:09:16,800 --> 00:09:20,779
the most useful thing but honestly it

206
00:09:19,380 --> 00:09:23,760
was added because

207
00:09:20,779 --> 00:09:25,620
Marco in ADB developer wanted to write a

208
00:09:23,760 --> 00:09:27,899
test case because it uses it underneath

209
00:09:25,620 --> 00:09:30,420
and they say well we may as well expose

210
00:09:27,899 --> 00:09:31,980
it as a function we've got all the

211
00:09:30,420 --> 00:09:35,160
implementation underneath you know

212
00:09:31,980 --> 00:09:38,820
what's a few more lines of code

213
00:09:35,160 --> 00:09:40,680
in a you know large code base and give

214
00:09:38,820 --> 00:09:43,160
the users the opposite if they have a

215
00:09:40,680 --> 00:09:43,160
use for it

216
00:09:43,320 --> 00:09:48,420
um random bites also use a contribution

217
00:09:45,920 --> 00:09:52,100
works the same as most curl stress

218
00:09:48,420 --> 00:09:52,100
enough returns a random number of bites

219
00:09:54,839 --> 00:10:01,019
prng

220
00:09:56,940 --> 00:10:03,600
sorry how secure is it and the random I

221
00:10:01,019 --> 00:10:05,040
think it's from memories

222
00:10:03,600 --> 00:10:08,760
prng

223
00:10:05,040 --> 00:10:11,160
um it's used the open SSL underneath

224
00:10:08,760 --> 00:10:13,320
um it might actually be crypted to

225
00:10:11,160 --> 00:10:15,120
graphically secure let me check let me

226
00:10:13,320 --> 00:10:18,120
check that one later and I'll get back

227
00:10:15,120 --> 00:10:23,820
to you on that

228
00:10:18,120 --> 00:10:26,420
uh next up Jason histograms uh so it

229
00:10:23,820 --> 00:10:31,140
doesn't mean much on its own in Marie DB

230
00:10:26,420 --> 00:10:35,399
10.0 a bunch of histograms were there to

231
00:10:31,140 --> 00:10:37,320
describe the format and distribution of

232
00:10:35,399 --> 00:10:39,779
values in a table and this was used by

233
00:10:37,320 --> 00:10:43,500
the query planners to work out you know

234
00:10:39,779 --> 00:10:45,660
it is Jane common or Julie common if

235
00:10:43,500 --> 00:10:49,160
someone might use that index or another

236
00:10:45,660 --> 00:10:53,160
one depending on how common it is

237
00:10:49,160 --> 00:10:56,120
uh as you probably know data sets are

238
00:10:53,160 --> 00:11:00,360
not always you know these beautiful

239
00:10:56,120 --> 00:11:03,300
normalized graphs of values that they

240
00:11:00,360 --> 00:11:05,339
have biases and that kind of thing and

241
00:11:03,300 --> 00:11:08,640
what we've done with Jason histograms

242
00:11:05,339 --> 00:11:11,579
we've put a Json format as the output

243
00:11:08,640 --> 00:11:14,220
and what that means is we can adapt the

244
00:11:11,579 --> 00:11:17,279
bin size of least common less common

245
00:11:14,220 --> 00:11:20,459
values to be a bigger bin and have a

246
00:11:17,279 --> 00:11:22,019
finer granularity on

247
00:11:20,459 --> 00:11:25,800
the other ones

248
00:11:22,019 --> 00:11:28,200
so if you look this uh just throw some

249
00:11:25,800 --> 00:11:31,800
dummy data into it

250
00:11:28,200 --> 00:11:35,040
um like like we did with

251
00:11:31,800 --> 00:11:36,800
uh to generate the statistics on a table

252
00:11:35,040 --> 00:11:39,720
use analyze table

253
00:11:36,800 --> 00:11:41,279
we add the persistent for all and that

254
00:11:39,720 --> 00:11:44,820
means it's actually a persistent

255
00:11:41,279 --> 00:11:47,760
statistic that's stored rather than a

256
00:11:44,820 --> 00:11:51,920
random sampling a query time

257
00:11:47,760 --> 00:11:55,100
and what this looks like in the tables

258
00:11:51,920 --> 00:11:56,720
all the way at the end I can't quite see

259
00:11:55,100 --> 00:12:01,079
is

260
00:11:56,720 --> 00:12:02,120
a Json object that describes the kind of

261
00:12:01,079 --> 00:12:04,380
bins

262
00:12:02,120 --> 00:12:08,220
available and you know what the

263
00:12:04,380 --> 00:12:10,860
likelihood is there if I wanted to place

264
00:12:08,220 --> 00:12:12,779
far too much data until this you'd come

265
00:12:10,860 --> 00:12:17,760
out with a lot more data and a lot more

266
00:12:12,779 --> 00:12:19,680
bins as to describe the data better

267
00:12:17,760 --> 00:12:22,279
and that just helps with your query

268
00:12:19,680 --> 00:12:22,279
execution

269
00:12:22,860 --> 00:12:28,320
uh natural sort key

270
00:12:25,940 --> 00:12:30,839
similar useful thing I mean normally

271
00:12:28,320 --> 00:12:33,420
when you sort by column if it's just got

272
00:12:30,839 --> 00:12:36,839
characters you expect like an alpha

273
00:12:33,420 --> 00:12:39,060
numeric order and what natural sort key

274
00:12:36,839 --> 00:12:40,980
does is treats the alphas there's like

275
00:12:39,060 --> 00:12:42,440
Alphas it treats the numbers like

276
00:12:40,980 --> 00:12:46,260
numbers

277
00:12:42,440 --> 00:12:50,100
and so let's add some sample data

278
00:12:46,260 --> 00:12:52,079
uh so what you get here is what you see

279
00:12:50,100 --> 00:12:54,300
is a normal ordering as to what you

280
00:12:52,079 --> 00:12:57,540
expect on the things

281
00:12:54,300 --> 00:13:00,660
um on those so you get you know things

282
00:12:57,540 --> 00:13:04,500
like a11 before A2

283
00:13:00,660 --> 00:13:07,620
now when you uh use an order by the

284
00:13:04,500 --> 00:13:10,800
natural sort key of that

285
00:13:07,620 --> 00:13:13,260
what you end up with is you know what a

286
00:13:10,800 --> 00:13:17,220
is before B's ones before twos two

287
00:13:13,260 --> 00:13:20,880
before elevens and all the way through

288
00:13:17,220 --> 00:13:25,740
so other you know see me not immediately

289
00:13:20,880 --> 00:13:29,339
obvious applications are things is

290
00:13:25,740 --> 00:13:32,399
things like IP yeah version five yeah

291
00:13:29,339 --> 00:13:34,820
obviously it's got 400 so it's not four

292
00:13:32,399 --> 00:13:38,220
or six

293
00:13:34,820 --> 00:13:41,339
so typing version five addressing and

294
00:13:38,220 --> 00:13:43,440
now we sort of sort of those in um in

295
00:13:41,339 --> 00:13:47,279
what actually looks like a more natural

296
00:13:43,440 --> 00:13:49,560
order for people to actually see them in

297
00:13:47,279 --> 00:13:53,940
um and you know it's stored as text but

298
00:13:49,560 --> 00:13:56,940
you know it comes out on top that way

299
00:13:53,940 --> 00:13:59,339
for those who've ever done SQL and have

300
00:13:56,940 --> 00:14:02,160
you know big large concatenate kind of

301
00:13:59,339 --> 00:14:04,860
SQL statements or little bits of

302
00:14:02,160 --> 00:14:06,839
formatting in we added this function and

303
00:14:04,860 --> 00:14:08,060
this is actually another Google summer

304
00:14:06,839 --> 00:14:12,300
code

305
00:14:08,060 --> 00:14:14,459
a contribution from our user base that

306
00:14:12,300 --> 00:14:16,100
sort of uses the the python kind of

307
00:14:14,459 --> 00:14:21,420
formats

308
00:14:16,100 --> 00:14:26,579
to in there as a function and can

309
00:14:21,420 --> 00:14:29,760
quickly do what python-like formats do

310
00:14:26,579 --> 00:14:34,019
and yeah just format the output and this

311
00:14:29,760 --> 00:14:35,760
is using the lib format algorithm lib

312
00:14:34,019 --> 00:14:39,120
format Library underneath so it actually

313
00:14:35,760 --> 00:14:41,760
can support a lot more you know variants

314
00:14:39,120 --> 00:14:44,339
than just these uh simple substitutions

315
00:14:41,760 --> 00:14:46,880
but even if these simple substitutions

316
00:14:44,339 --> 00:14:49,860
you've got added readability

317
00:14:46,880 --> 00:14:51,959
and easier to write and you know less

318
00:14:49,860 --> 00:14:54,800
counting of braces

319
00:14:51,959 --> 00:14:54,800
I should

320
00:14:57,480 --> 00:15:00,680
I'm sorry okay

321
00:15:00,720 --> 00:15:03,199
good

322
00:15:04,680 --> 00:15:09,480
cool

323
00:15:06,240 --> 00:15:11,639
uh descending in the indexes

324
00:15:09,480 --> 00:15:14,040
um it's sort of been in the Syntax for a

325
00:15:11,639 --> 00:15:15,720
while and it was pretty much ignored it

326
00:15:14,040 --> 00:15:18,959
sort of passed it for compatibility

327
00:15:15,720 --> 00:15:20,220
versions and then just assorted um

328
00:15:18,959 --> 00:15:23,820
ascending

329
00:15:20,220 --> 00:15:26,220
so now we've actually got true ascending

330
00:15:23,820 --> 00:15:29,579
and descending indexes and that really

331
00:15:26,220 --> 00:15:31,320
plays into account when you actually do

332
00:15:29,579 --> 00:15:34,019
ordering by

333
00:15:31,320 --> 00:15:36,959
one index in one order and and another

334
00:15:34,019 --> 00:15:39,079
index another part of the compound index

335
00:15:36,959 --> 00:15:42,440
in the other order

336
00:15:39,079 --> 00:15:47,760
and what that means

337
00:15:42,440 --> 00:15:51,120
is that in the query you go data we're

338
00:15:47,760 --> 00:15:53,779
using that index r and we're using the

339
00:15:51,120 --> 00:15:57,480
full length of it and going through

340
00:15:53,779 --> 00:16:00,060
for those sort of far more astute you

341
00:15:57,480 --> 00:16:01,560
may notice I did a force index in

342
00:16:00,060 --> 00:16:02,760
writing this talk I realized that

343
00:16:01,560 --> 00:16:05,579
there's actually a bug it doesn't

344
00:16:02,760 --> 00:16:08,959
actually choose that automatically so

345
00:16:05,579 --> 00:16:11,820
in the queue to be fixed

346
00:16:08,959 --> 00:16:12,560
always trust to talk to you know show up

347
00:16:11,820 --> 00:16:16,560
um

348
00:16:12,560 --> 00:16:21,180
almost obvious bugs similar things

349
00:16:16,560 --> 00:16:24,839
um yeah and same if we reverse the order

350
00:16:21,180 --> 00:16:29,540
so a was a descending index and B was

351
00:16:24,839 --> 00:16:31,980
ascending if we do it the other way a

352
00:16:29,540 --> 00:16:33,480
ascending Beauty sending

353
00:16:31,980 --> 00:16:35,519
um means we're still going to use the

354
00:16:33,480 --> 00:16:37,380
same index we just got to transverse it

355
00:16:35,519 --> 00:16:39,899
the other direction

356
00:16:37,380 --> 00:16:42,000
and that's exactly where to do

357
00:16:39,899 --> 00:16:43,680
interesting enough doesn't actually show

358
00:16:42,000 --> 00:16:46,160
the direction maybe it doesn't matter

359
00:16:43,680 --> 00:16:46,160
that much

360
00:16:47,940 --> 00:16:52,019
uh for those that have actually played

361
00:16:50,699 --> 00:16:54,680
with petitions who's played with

362
00:16:52,019 --> 00:16:54,680
petitions before

363
00:16:55,339 --> 00:17:02,160
anyone actually like them

364
00:16:58,860 --> 00:17:05,160
yeah it's a bit like that

365
00:17:02,160 --> 00:17:09,480
so this is what you previously had to do

366
00:17:05,160 --> 00:17:12,240
to okay replace it position so we've got

367
00:17:09,480 --> 00:17:16,500
a bit of data loading and then we create

368
00:17:12,240 --> 00:17:19,199
a table like the petition table we

369
00:17:16,500 --> 00:17:21,600
remove the petitioning on it and then we

370
00:17:19,199 --> 00:17:23,819
swap

371
00:17:21,600 --> 00:17:26,040
one with the other on the petitions and

372
00:17:23,819 --> 00:17:28,140
this is just all very verbose to

373
00:17:26,040 --> 00:17:31,640
actually do the same thing that we

374
00:17:28,140 --> 00:17:36,299
needed actually for SQL statements to

375
00:17:31,640 --> 00:17:39,440
remove a exchange of petition in the

376
00:17:36,299 --> 00:17:39,440
table to out of the table

377
00:17:39,480 --> 00:17:45,720
so it was simplified at that

378
00:17:41,840 --> 00:17:48,780
now we do uh

379
00:17:45,720 --> 00:17:54,000
just one statement so we had our table

380
00:17:48,780 --> 00:17:55,559
we've got datas in that and had a

381
00:17:54,000 --> 00:17:58,620
petition

382
00:17:55,559 --> 00:18:01,080
and exchange a position oh sorry that

383
00:17:58,620 --> 00:18:02,400
was the the previous one extended way to

384
00:18:01,080 --> 00:18:05,160
remove it

385
00:18:02,400 --> 00:18:07,080
it's not surprising it's confusing uh

386
00:18:05,160 --> 00:18:09,960
okay and now we've got like convert

387
00:18:07,080 --> 00:18:13,760
partitions so we convert petition one to

388
00:18:09,960 --> 00:18:17,160
a normal table one line of SQL to

389
00:18:13,760 --> 00:18:18,539
move one of your petitions out to a

390
00:18:17,160 --> 00:18:21,960
separate table

391
00:18:18,539 --> 00:18:25,559
or vice versa

392
00:18:21,960 --> 00:18:27,539
oops problems when you do it and you can

393
00:18:25,559 --> 00:18:30,000
also do it the version other way you can

394
00:18:27,539 --> 00:18:34,860
grab a table that's out and

395
00:18:30,000 --> 00:18:36,960
import it into a petition table with a

396
00:18:34,860 --> 00:18:39,900
criteria of what it actually represents

397
00:18:36,960 --> 00:18:42,380
on that petition

398
00:18:39,900 --> 00:18:45,360
so you know little syntax

399
00:18:42,380 --> 00:18:47,400
improvements that save a lot of jumbling

400
00:18:45,360 --> 00:18:51,380
around or you know copying and pasting

401
00:18:47,400 --> 00:18:54,780
other things on the internet

402
00:18:51,380 --> 00:18:57,740
system version tables anyone come across

403
00:18:54,780 --> 00:18:57,740
or use them before

404
00:18:58,340 --> 00:19:04,020
Sunset later in this example you like to

405
00:19:01,980 --> 00:19:08,539
see what it means a bit

406
00:19:04,020 --> 00:19:14,100
so system versioning was I believe a

407
00:19:08,539 --> 00:19:16,940
2011 SQL standard extension so we've got

408
00:19:14,100 --> 00:19:20,640
system versioning a date

409
00:19:16,940 --> 00:19:24,960
in Marie DP I didn't list it's probably

410
00:19:20,640 --> 00:19:26,840
um 10.10 or 10.11 we added a bit that

411
00:19:24,960 --> 00:19:31,260
allows you to insert the history

412
00:19:26,840 --> 00:19:33,120
the SQL standard is rather strict on it

413
00:19:31,260 --> 00:19:36,320
it sort of says well whatever is on the

414
00:19:33,120 --> 00:19:39,299
table this must be in the table

415
00:19:36,320 --> 00:19:42,900
but you know you have to load these

416
00:19:39,299 --> 00:19:45,360
sometimes and so what this does it gives

417
00:19:42,900 --> 00:19:49,140
the ability to load it so we've got a

418
00:19:45,360 --> 00:19:51,419
table it's just got one X primary key

419
00:19:49,140 --> 00:19:54,179
we load it in

420
00:19:51,419 --> 00:19:56,640
but we specify the row start and the row

421
00:19:54,179 --> 00:19:59,760
10 end time and those are fixed time

422
00:19:56,640 --> 00:20:01,980
values there and we say

423
00:19:59,760 --> 00:20:04,799
for this table you know between the

424
00:20:01,980 --> 00:20:08,039
times of the beginning of 1980 and

425
00:20:04,799 --> 00:20:10,340
beginning of 1980 and

426
00:20:08,039 --> 00:20:13,980
20 hours and one second

427
00:20:10,340 --> 00:20:17,400
this value existed on the table

428
00:20:13,980 --> 00:20:18,500
so the good thing about systems that are

429
00:20:17,400 --> 00:20:23,100
run before

430
00:20:18,500 --> 00:20:25,559
the system timed is that you can query

431
00:20:23,100 --> 00:20:27,120
what the table looks like at a

432
00:20:25,559 --> 00:20:30,000
particular time

433
00:20:27,120 --> 00:20:32,160
so this the system version tables

434
00:20:30,000 --> 00:20:35,280
maintains a view of what is the current

435
00:20:32,160 --> 00:20:37,799
data and what was there before

436
00:20:35,280 --> 00:20:41,160
and so this is really good for anyone

437
00:20:37,799 --> 00:20:43,260
doing like audit logs or that kind of

438
00:20:41,160 --> 00:20:45,480
thing or just resource allocation is

439
00:20:43,260 --> 00:20:49,500
like what did the resource allocation

440
00:20:45,480 --> 00:20:52,160
look like so long it go you've got it in

441
00:20:49,500 --> 00:20:52,160
one query now

442
00:20:54,900 --> 00:20:58,620
um

443
00:20:55,860 --> 00:21:00,480
usual questions you know if you query of

444
00:20:58,620 --> 00:21:04,919
you know too late it's an empty set if

445
00:21:00,480 --> 00:21:07,020
you query it too early it's an empty set

446
00:21:04,919 --> 00:21:09,480
and that's just what the table looked

447
00:21:07,020 --> 00:21:12,299
like at the time

448
00:21:09,480 --> 00:21:14,160
I see Williams not in the room to harass

449
00:21:12,299 --> 00:21:17,160
me about you know passwords but for

450
00:21:14,160 --> 00:21:20,400
those that you know obliged to use it

451
00:21:17,160 --> 00:21:21,020
you know we've got a password reset

452
00:21:20,400 --> 00:21:23,940
um

453
00:21:21,020 --> 00:21:27,660
Outreach functionalities I create a user

454
00:21:23,940 --> 00:21:29,460
set the password set it again and you

455
00:21:27,660 --> 00:21:32,100
know there's a limit number of times you

456
00:21:29,460 --> 00:21:33,720
can for use it the exact number is

457
00:21:32,100 --> 00:21:38,659
actually just an option on the plugin

458
00:21:33,720 --> 00:21:38,659
and so this is a pre-built plugin

459
00:21:40,440 --> 00:21:43,919
um like you know many of these kind of

460
00:21:42,240 --> 00:21:45,860
you know plugins There's an opportunity

461
00:21:43,919 --> 00:21:49,320
to actually you know write your own

462
00:21:45,860 --> 00:21:51,360
plugin on password requirements if you

463
00:21:49,320 --> 00:21:53,780
want

464
00:21:51,360 --> 00:21:57,179
uh something I got reminded last night

465
00:21:53,780 --> 00:21:59,820
that since Marie to be

466
00:21:57,179 --> 00:22:03,120
now stretching my memory it was at least

467
00:21:59,820 --> 00:22:06,000
10.2 or 10.3 there's been a data risk

468
00:22:03,120 --> 00:22:08,120
encryption out there and there's been a

469
00:22:06,000 --> 00:22:10,380
number of Key Management plugins

470
00:22:08,120 --> 00:22:14,220
there's been

471
00:22:10,380 --> 00:22:16,440
very slowly development developing a a I

472
00:22:14,220 --> 00:22:18,539
admit but you know the number and I

473
00:22:16,440 --> 00:22:21,840
guess hashcorp producer Key Management

474
00:22:18,539 --> 00:22:25,520
plugin it stores things and now there's

475
00:22:21,840 --> 00:22:25,520
a plugin that interacts with it

476
00:22:26,960 --> 00:22:31,620
get Diagnostics um I actually got to

477
00:22:30,240 --> 00:22:34,919
admit I didn't actually know there's a

478
00:22:31,620 --> 00:22:37,020
feature existed until last year and this

479
00:22:34,919 --> 00:22:41,159
is an information about getting

480
00:22:37,020 --> 00:22:42,179
information of the current state of the

481
00:22:41,159 --> 00:22:43,799
machine

482
00:22:42,179 --> 00:22:46,980
so we've just got a table with the

483
00:22:43,799 --> 00:22:49,980
primary key and we insert a value one

484
00:22:46,980 --> 00:22:52,260
and now we've got insert a value a set

485
00:22:49,980 --> 00:22:54,480
of values and one is there again and

486
00:22:52,260 --> 00:22:56,340
it's obviously a duplicate

487
00:22:54,480 --> 00:22:59,039
so the question is you know from a

488
00:22:56,340 --> 00:23:01,740
program perspective how do we determine

489
00:22:59,039 --> 00:23:03,299
you know which one was the duplicate I

490
00:23:01,740 --> 00:23:06,240
guess we could look at the values but

491
00:23:03,299 --> 00:23:08,280
you know if for more complicated queries

492
00:23:06,240 --> 00:23:10,380
it may not actually be obvious from the

493
00:23:08,280 --> 00:23:12,260
values as to to which one is actually

494
00:23:10,380 --> 00:23:15,480
causing the error

495
00:23:12,260 --> 00:23:16,140
circuit Diagnostics now includes the row

496
00:23:15,480 --> 00:23:18,360
number

497
00:23:16,140 --> 00:23:20,039
that you can get into a user variable

498
00:23:18,360 --> 00:23:22,559
and you can select the user variable and

499
00:23:20,039 --> 00:23:24,600
it goes number two and that means the

500
00:23:22,559 --> 00:23:27,539
second item in there is the one that

501
00:23:24,600 --> 00:23:30,240
actually caused the the warning

502
00:23:27,539 --> 00:23:32,760
so this is the extension there's a bunch

503
00:23:30,240 --> 00:23:36,140
of other information that you can return

504
00:23:32,760 --> 00:23:36,140
with get diagnostic

505
00:23:39,240 --> 00:23:44,900
a another this I think was

506
00:23:42,539 --> 00:23:46,520
the Google summer code as well

507
00:23:44,900 --> 00:23:51,000
contribution

508
00:23:46,520 --> 00:23:53,520
that it we can mark the in and out and

509
00:23:51,000 --> 00:23:55,679
in our attributes of the parameters of

510
00:23:53,520 --> 00:23:58,919
function with an attribute to describe

511
00:23:55,679 --> 00:23:59,700
which way they actually go and what they

512
00:23:58,919 --> 00:24:02,840
used

513
00:23:59,700 --> 00:24:05,340
and these are attributes just to help

514
00:24:02,840 --> 00:24:07,260
coders you know make sure they're using

515
00:24:05,340 --> 00:24:11,400
the things the right way

516
00:24:07,260 --> 00:24:14,100
a minor limitation maybe it's more more

517
00:24:11,400 --> 00:24:18,059
than minor is that it can be used in

518
00:24:14,100 --> 00:24:20,520
like set queries like that so set result

519
00:24:18,059 --> 00:24:22,200
equals the function

520
00:24:20,520 --> 00:24:25,080
um I think it's due to something

521
00:24:22,200 --> 00:24:26,659
internally that select queries you can't

522
00:24:25,080 --> 00:24:31,620
actually have

523
00:24:26,659 --> 00:24:33,419
out values in them in some ways I'm not

524
00:24:31,620 --> 00:24:37,080
sure why that is it's probably just the

525
00:24:33,419 --> 00:24:40,220
way user functions user variables are

526
00:24:37,080 --> 00:24:43,620
actually processed in SQL that it

527
00:24:40,220 --> 00:24:47,159
it's just a limitation that's there

528
00:24:43,620 --> 00:24:50,539
the important to know before you see

529
00:24:47,159 --> 00:24:55,020
something that's almost working and not

530
00:24:50,539 --> 00:24:57,440
for those that do replication there's an

531
00:24:55,020 --> 00:25:00,720
ALT of a two-phase

532
00:24:57,440 --> 00:25:02,460
replication statement on alter table so

533
00:25:00,720 --> 00:25:05,340
for those who be you know one after the

534
00:25:02,460 --> 00:25:08,100
other other the other in in Chains

535
00:25:05,340 --> 00:25:10,980
so in this case when this

536
00:25:08,100 --> 00:25:14,100
system variable is set

537
00:25:10,980 --> 00:25:17,220
that's the loadable I'm good let's move

538
00:25:14,100 --> 00:25:18,980
Timber what happens is the altar

539
00:25:17,220 --> 00:25:22,200
statement is actually

540
00:25:18,980 --> 00:25:24,559
pushed down to the replication it starts

541
00:25:22,200 --> 00:25:27,659
executing on the replica immediately

542
00:25:24,559 --> 00:25:30,720
when it finishes on the master there's a

543
00:25:27,659 --> 00:25:33,840
commit message that goes into the binary

544
00:25:30,720 --> 00:25:36,240
log as well and when the replicas

545
00:25:33,840 --> 00:25:38,220
actually receive that they'll swap it

546
00:25:36,240 --> 00:25:41,940
over and this means she rolled a table

547
00:25:38,220 --> 00:25:43,799
and your master is keeps up assuming

548
00:25:41,940 --> 00:25:45,440
similar Hardware compatibilities and

549
00:25:43,799 --> 00:25:49,080
other things

550
00:25:45,440 --> 00:25:51,919
your replica keeps up with your primary

551
00:25:49,080 --> 00:25:51,919
in the same way

552
00:25:53,059 --> 00:25:58,220
Duty ID's support was added in Marie

553
00:25:56,220 --> 00:26:02,220
would be

554
00:25:58,220 --> 00:26:05,700
10.1 possibly uh we finally actually got

555
00:26:02,220 --> 00:26:07,440
around to introducing the GTI positions

556
00:26:05,700 --> 00:26:10,200
into

557
00:26:07,440 --> 00:26:13,679
um you know the client utilities and

558
00:26:10,200 --> 00:26:15,779
meridi Bin log from unistar position to

559
00:26:13,679 --> 00:26:20,159
stop position to a bunch of filtering

560
00:26:15,779 --> 00:26:22,080
commands so that's all there now yeah

561
00:26:20,159 --> 00:26:26,240
like it should have been five years ago

562
00:26:22,080 --> 00:26:26,240
um but anyway better late than never

563
00:26:28,440 --> 00:26:33,659
uh for those who've actually played

564
00:26:31,020 --> 00:26:34,530
around with GT IDs and it probably is

565
00:26:33,659 --> 00:26:34,980
almost the same

566
00:26:34,530 --> 00:26:36,380
[Music]

567
00:26:34,980 --> 00:26:39,299
um

568
00:26:36,380 --> 00:26:42,720
tell me if there's a decent you know

569
00:26:39,299 --> 00:26:44,460
um confusion flea implementation out but

570
00:26:42,720 --> 00:26:47,940
um hopefully we've done a bit to do it

571
00:26:44,460 --> 00:26:51,179
but working out where to actually start

572
00:26:47,940 --> 00:26:54,179
and stop replication from especially

573
00:26:51,179 --> 00:26:55,860
that's your switching Masters to Slaves

574
00:26:54,179 --> 00:26:58,980
or slaves to Masters and primaries and

575
00:26:55,860 --> 00:27:00,840
secondaries it's not always immediate to

576
00:26:58,980 --> 00:27:02,640
which one you're doing it especially if

577
00:27:00,840 --> 00:27:05,580
you're trying to do it in a in a

578
00:27:02,640 --> 00:27:07,980
failover the nasty situation and yeah

579
00:27:05,580 --> 00:27:11,039
it's a easy to get wrong

580
00:27:07,980 --> 00:27:13,500
so what we did is improve the syntax so

581
00:27:11,039 --> 00:27:16,380
you just need to change master to make

582
00:27:13,500 --> 00:27:18,179
your slave and that will know which

583
00:27:16,380 --> 00:27:21,140
position it's at and where it has to

584
00:27:18,179 --> 00:27:21,140
continue from

585
00:27:24,500 --> 00:27:31,279
on grants and the privilege system uh

586
00:27:28,080 --> 00:27:35,520
there's now a granted public

587
00:27:31,279 --> 00:27:37,380
is applies to any user that has access

588
00:27:35,520 --> 00:27:40,260
on the system so you're effectively

589
00:27:37,380 --> 00:27:43,200
giving a grant to everyone who's got

590
00:27:40,260 --> 00:27:47,419
access it's not the same as an anonymous

591
00:27:43,200 --> 00:27:47,419
user Anonymous user can

592
00:27:47,820 --> 00:27:52,500
uh if you'd say granted public it

593
00:27:50,700 --> 00:27:55,080
doesn't mean that that becomes Anonymous

594
00:27:52,500 --> 00:27:57,120
a user exists it it had it's a it's a

595
00:27:55,080 --> 00:28:01,020
separate concept on that one

596
00:27:57,120 --> 00:28:04,679
so here we Grant select on the entire

597
00:28:01,020 --> 00:28:06,840
test base to anyone who's got a user on

598
00:28:04,679 --> 00:28:09,960
the system

599
00:28:06,840 --> 00:28:12,659
um and you can obviously yeah extend

600
00:28:09,960 --> 00:28:15,380
that to any other thing uh in the the

601
00:28:12,659 --> 00:28:15,380
grant syntax

602
00:28:16,820 --> 00:28:22,580
previously on moving on to read only

603
00:28:20,240 --> 00:28:27,620
that previously

604
00:28:22,580 --> 00:28:31,260
a super user on Marie DB was able to

605
00:28:27,620 --> 00:28:33,659
write even if the um

606
00:28:31,260 --> 00:28:35,179
the server was actually set into a

607
00:28:33,659 --> 00:28:39,299
read-only mode

608
00:28:35,179 --> 00:28:42,480
there's situations where this isn't

609
00:28:39,299 --> 00:28:45,179
um really appropriate or or you know can

610
00:28:42,480 --> 00:28:49,140
easily Overlook the fact that you know

611
00:28:45,179 --> 00:28:50,760
far too many things log in as a router

612
00:28:49,140 --> 00:28:54,600
or a super user

613
00:28:50,760 --> 00:28:57,679
so over I think since about 10.5 onwards

614
00:28:54,600 --> 00:29:01,020
was daily broken the super user

615
00:28:57,679 --> 00:29:04,740
privilege up into a number of more

616
00:29:01,020 --> 00:29:06,840
distinct privileges from in now we've

617
00:29:04,740 --> 00:29:10,500
got like a read-only admin that can

618
00:29:06,840 --> 00:29:14,220
change that we've also done binary

619
00:29:10,500 --> 00:29:16,500
uh log admins replication admins and I'm

620
00:29:14,220 --> 00:29:19,080
trying to remember all of the heads so

621
00:29:16,500 --> 00:29:20,880
um but yeah there's a large number of

622
00:29:19,080 --> 00:29:23,039
finely grained

623
00:29:20,880 --> 00:29:26,640
um privileges that can be granted rather

624
00:29:23,039 --> 00:29:29,640
than uh you know one privilege to rule

625
00:29:26,640 --> 00:29:32,279
them all so there's very few things that

626
00:29:29,640 --> 00:29:35,899
actually are left under that um you know

627
00:29:32,279 --> 00:29:35,899
notional super privilege

628
00:29:36,320 --> 00:29:42,419
okay so your show grants

629
00:29:39,299 --> 00:29:44,539
we revert the radar and the admin from

630
00:29:42,419 --> 00:29:47,700
the root user

631
00:29:44,539 --> 00:29:49,440
and like all actually revokes they

632
00:29:47,700 --> 00:29:51,120
actually start on the the next session

633
00:29:49,440 --> 00:29:53,179
so in this case we're still going to set

634
00:29:51,120 --> 00:29:56,279
read only but if you're logged in again

635
00:29:53,179 --> 00:30:00,799
or a different connection you wouldn't

636
00:29:56,279 --> 00:30:00,799
actually be able to change the read only

637
00:30:02,580 --> 00:30:08,100
in the nodb space what we've done is to

638
00:30:06,059 --> 00:30:11,480
increase the the number of people that

639
00:30:08,100 --> 00:30:14,640
do you know bulk inserts to load up data

640
00:30:11,480 --> 00:30:17,100
this is under a condition that the the

641
00:30:14,640 --> 00:30:20,460
front key checks and the unique checks

642
00:30:17,100 --> 00:30:23,039
are off and this is hopefully just a

643
00:30:20,460 --> 00:30:26,340
temporary limitation that we need to

644
00:30:23,039 --> 00:30:28,440
apply there was some complexities around

645
00:30:26,340 --> 00:30:32,159
someone

646
00:30:28,440 --> 00:30:33,600
added like unique blobs that turned out

647
00:30:32,159 --> 00:30:35,700
to be um

648
00:30:33,600 --> 00:30:39,240
a rather complicated thing with the

649
00:30:35,700 --> 00:30:41,580
large number of edge cases and for the

650
00:30:39,240 --> 00:30:45,240
moment that setting those use of

651
00:30:41,580 --> 00:30:49,399
variables is required to make this a

652
00:30:45,240 --> 00:30:53,760
feature fully performant so whether we

653
00:30:49,399 --> 00:30:57,659
insert create a table and do a very

654
00:30:53,760 --> 00:31:00,899
large select statement or a load data or

655
00:30:57,659 --> 00:31:02,299
if you do an insert data with a huge

656
00:31:00,899 --> 00:31:05,640
list of values

657
00:31:02,299 --> 00:31:08,340
this will be quicker because it doesn't

658
00:31:05,640 --> 00:31:10,559
actually need to write all the rollback

659
00:31:08,340 --> 00:31:12,720
things it knows that you know if

660
00:31:10,559 --> 00:31:14,360
something fails here it rolls back to an

661
00:31:12,720 --> 00:31:18,000
empty table

662
00:31:14,360 --> 00:31:23,059
so there's a lot of writing and overhead

663
00:31:18,000 --> 00:31:23,059
in the CPU that has been reduced by this

664
00:31:24,120 --> 00:31:30,299
been a number of resize segments in the

665
00:31:27,179 --> 00:31:32,059
table spaces that can be changed if you

666
00:31:30,299 --> 00:31:36,080
stop and start

667
00:31:32,059 --> 00:31:38,940
it's not actually dynamic in the um

668
00:31:36,080 --> 00:31:41,760
and the user of interface yes however

669
00:31:38,940 --> 00:31:43,140
what is is the inadably log size is

670
00:31:41,760 --> 00:31:45,720
recyclable

671
00:31:43,140 --> 00:31:47,520
so if you've got you know a default size

672
00:31:45,720 --> 00:31:50,399
there for some reason that's rather

673
00:31:47,520 --> 00:31:52,740
small you end up doing a a lot a lot of

674
00:31:50,399 --> 00:31:57,360
bulk inserts on things you can actually

675
00:31:52,740 --> 00:32:00,919
resize that dynamically at runtime

676
00:31:57,360 --> 00:32:00,919
or down in fact

677
00:32:02,779 --> 00:32:09,080
what has happened if I probably didn't

678
00:32:05,700 --> 00:32:11,340
spell deprecated right yeah that's good

679
00:32:09,080 --> 00:32:13,980
it was late

680
00:32:11,340 --> 00:32:17,299
um so the change buffer in in Adobe has

681
00:32:13,980 --> 00:32:21,720
actually been removed

682
00:32:17,299 --> 00:32:23,640
why we did this was there's been a huge

683
00:32:21,720 --> 00:32:26,580
number of bugs over the years that have

684
00:32:23,640 --> 00:32:29,880
sort of been impossible to really track

685
00:32:26,580 --> 00:32:33,179
down or get in a a test case for us so

686
00:32:29,880 --> 00:32:35,580
this uh feature of you know change

687
00:32:33,179 --> 00:32:38,700
buffer that sort of been in an ADB for a

688
00:32:35,580 --> 00:32:41,039
while has been removed so that in theory

689
00:32:38,700 --> 00:32:45,960
should mean that a whole class of bugs

690
00:32:41,039 --> 00:32:48,480
is just gone much to Marco's relief so

691
00:32:45,960 --> 00:32:51,840
it was a kind of deprecated and ignored

692
00:32:48,480 --> 00:32:53,340
in in 10.9 on onwards honestly the

693
00:32:51,840 --> 00:32:55,919
code's still there but you can't

694
00:32:53,340 --> 00:32:58,679
actually access it so it's been removed

695
00:32:55,919 --> 00:33:01,399
now in 11 0 Plus

696
00:32:58,679 --> 00:33:01,399
this

697
00:33:02,600 --> 00:33:06,480
apostrophe's in full text

698
00:33:04,679 --> 00:33:08,580
um fairly obvious that you know

699
00:33:06,480 --> 00:33:11,000
strangely enough apostrophes do actually

700
00:33:08,580 --> 00:33:15,720
form a meaningful part of text

701
00:33:11,000 --> 00:33:18,059
so yeah let's you know run it and insert

702
00:33:15,720 --> 00:33:22,460
some data and guess what you actually

703
00:33:18,059 --> 00:33:24,720
get things with apostrophes there

704
00:33:22,460 --> 00:33:27,840
no you know there was

705
00:33:24,720 --> 00:33:31,140
um yeah and there are you know absolute

706
00:33:27,840 --> 00:33:32,820
fantastic text searches in the open

707
00:33:31,140 --> 00:33:34,320
source data space but you know if you

708
00:33:32,820 --> 00:33:36,779
want something simple at least you've

709
00:33:34,320 --> 00:33:38,880
got something simple that does some

710
00:33:36,779 --> 00:33:41,779
fairly basic things right like

711
00:33:38,880 --> 00:33:41,779
apostrophes

712
00:33:42,000 --> 00:33:46,940
and the same applies to matches there

713
00:33:47,519 --> 00:33:53,940
in ADB software commit maybe I was wrong

714
00:33:50,820 --> 00:33:58,440
to actually show the code here but what

715
00:33:53,940 --> 00:34:03,179
happens is the main bit is when we

716
00:33:58,440 --> 00:34:06,720
free memory in the nadb what happened is

717
00:34:03,179 --> 00:34:09,659
that we uh till the operating system

718
00:34:06,720 --> 00:34:12,240
we're not using this anymore and so

719
00:34:09,659 --> 00:34:14,520
there's no it may look still allocated

720
00:34:12,240 --> 00:34:18,359
as the server it may still even be

721
00:34:14,520 --> 00:34:20,580
resonant but at the point the the

722
00:34:18,359 --> 00:34:22,740
someone runs up far too many PHP

723
00:34:20,580 --> 00:34:26,399
processes so that the operating system

724
00:34:22,740 --> 00:34:29,639
is still free to reclaim that memory

725
00:34:26,399 --> 00:34:31,980
from that and this is like a first step

726
00:34:29,639 --> 00:34:34,740
in a number of things we want to try to

727
00:34:31,980 --> 00:34:37,500
get done to make Marie to be not this

728
00:34:34,740 --> 00:34:40,020
you know big memory hog of the entire

729
00:34:37,500 --> 00:34:41,599
system that it can actually be a little

730
00:34:40,020 --> 00:34:44,099
bit more adaptive

731
00:34:41,599 --> 00:34:46,200
further work in the works that I haven't

732
00:34:44,099 --> 00:34:48,839
actually done is looking at like memory

733
00:34:46,200 --> 00:34:50,700
pressures from the operating system and

734
00:34:48,839 --> 00:34:53,220
seeing well okay if that's memory

735
00:34:50,700 --> 00:34:56,220
pressure maybe we don't need a 10 year

736
00:34:53,220 --> 00:34:58,460
old a 10 minute old cased version of a

737
00:34:56,220 --> 00:34:58,460
page

738
00:34:58,500 --> 00:35:02,400
okay quick stop looking at code it's

739
00:35:01,380 --> 00:35:04,920
gone

740
00:35:02,400 --> 00:35:07,740
um I apologies for inflicting on you but

741
00:35:04,920 --> 00:35:10,619
yes it does exist that way

742
00:35:07,740 --> 00:35:14,520
uh system versioning gives us the

743
00:35:10,619 --> 00:35:17,820
ability to Auto size the petitions for

744
00:35:14,520 --> 00:35:20,160
those doing that and this sort of helps

745
00:35:17,820 --> 00:35:22,500
you kind of realize that you know if

746
00:35:20,160 --> 00:35:24,839
you're keeping an audit log for you know

747
00:35:22,500 --> 00:35:26,900
several months as to what's that you may

748
00:35:24,839 --> 00:35:30,000
eventually may want to swap it out

749
00:35:26,900 --> 00:35:33,020
petitions are a really nice way to swap

750
00:35:30,000 --> 00:35:36,000
out data and remove them quickly

751
00:35:33,020 --> 00:35:38,660
so let's make it actually Auto created

752
00:35:36,000 --> 00:35:38,660
as well

753
00:35:39,540 --> 00:35:46,619
and depth of those

754
00:35:42,660 --> 00:35:49,500
a user contribution was Marie DB dump

755
00:35:46,619 --> 00:35:52,339
order by size so this dumps out the the

756
00:35:49,500 --> 00:35:55,859
small tables first

757
00:35:52,339 --> 00:35:58,099
unlike some other databases that Marie

758
00:35:55,859 --> 00:36:01,920
DB's

759
00:35:58,099 --> 00:36:03,660
table changes aren't actually Atomic yet

760
00:36:01,920 --> 00:36:05,060
so you could actually while you're

761
00:36:03,660 --> 00:36:07,800
dumping out

762
00:36:05,060 --> 00:36:10,619
a database dump someone changes the

763
00:36:07,800 --> 00:36:13,859
structure of a table the tables that

764
00:36:10,619 --> 00:36:15,920
normally changes of the small ones so a

765
00:36:13,859 --> 00:36:19,380
let's do those first B they're faster

766
00:36:15,920 --> 00:36:21,599
and what this means is you're more

767
00:36:19,380 --> 00:36:25,200
likely to have a reliable dump

768
00:36:21,599 --> 00:36:27,660
particularly on systems that where users

769
00:36:25,200 --> 00:36:28,980
add or create or change small tables

770
00:36:27,660 --> 00:36:33,000
first

771
00:36:28,980 --> 00:36:36,599
that was the content anyway

772
00:36:33,000 --> 00:36:39,560
use the contribution we now got a full

773
00:36:36,599 --> 00:36:44,460
set of Chinese error messages

774
00:36:39,560 --> 00:36:47,579
in the server from about 10.4 up so

775
00:36:44,460 --> 00:36:50,960
they're all translated through and we're

776
00:36:47,579 --> 00:36:55,200
experimenting with translation

777
00:36:50,960 --> 00:36:58,920
automated and of various languages

778
00:36:55,200 --> 00:37:01,339
um recently also got pushed into 1011

779
00:36:58,920 --> 00:37:04,560
it's not in the latest release but

780
00:37:01,339 --> 00:37:07,320
a first-time GitHub contributor did a

781
00:37:04,560 --> 00:37:09,839
whole heap of Georgian translations of

782
00:37:07,320 --> 00:37:11,460
all their messages as well and sort of

783
00:37:09,839 --> 00:37:13,980
said you know pinga is like what about

784
00:37:11,460 --> 00:37:16,020
the local here's the data structure

785
00:37:13,980 --> 00:37:18,300
here's what you change and they did that

786
00:37:16,020 --> 00:37:20,880
as well so we

787
00:37:18,300 --> 00:37:23,700
getting a lot of improvements from our

788
00:37:20,880 --> 00:37:25,380
user base that way

789
00:37:23,700 --> 00:37:28,079
so that's all I wanted to actually

790
00:37:25,380 --> 00:37:30,320
present to you today but do you have any

791
00:37:28,079 --> 00:37:30,320
questions

792
00:37:33,500 --> 00:37:39,960
do you have any questions that you know

793
00:37:37,099 --> 00:37:41,960
that you're depriving or waiting people

794
00:37:39,960 --> 00:37:45,980
to have lunch of

795
00:37:41,960 --> 00:37:45,980
okay so there are many questions

796
00:37:46,520 --> 00:37:50,180
are there questions

797
00:37:52,859 --> 00:37:56,760
I think you should

798
00:37:54,420 --> 00:37:57,680
have it well have one so one of the

799
00:37:56,760 --> 00:38:00,720
things

800
00:37:57,680 --> 00:38:03,300
is there was many of them the last one

801
00:38:00,720 --> 00:38:06,180
was my sequel dump right yeah I hate

802
00:38:03,300 --> 00:38:07,800
that no why do you encourage people to

803
00:38:06,180 --> 00:38:11,839
use that you can't restore it have you

804
00:38:07,800 --> 00:38:13,980
ever tried to restore myself

805
00:38:11,839 --> 00:38:17,820
you can only restore it to the same

806
00:38:13,980 --> 00:38:20,640
version yes and and it takes forever

807
00:38:17,820 --> 00:38:22,320
yeah it's a single threaded uh thing so

808
00:38:20,640 --> 00:38:23,700
it's so sorry what I was getting at that

809
00:38:22,320 --> 00:38:25,260
was that was a statement I'm sorry sure

810
00:38:23,700 --> 00:38:26,220
I'll phrase my question in the form of

811
00:38:25,260 --> 00:38:29,099
the question

812
00:38:26,220 --> 00:38:32,099
have you got any better ways of doing it

813
00:38:29,099 --> 00:38:33,660
on on the single thread a bit um what

814
00:38:32,099 --> 00:38:36,420
we've been doing actually has been

815
00:38:33,660 --> 00:38:37,619
working with like the the my damper uh

816
00:38:36,420 --> 00:38:39,780
Community

817
00:38:37,619 --> 00:38:42,900
um to ensure that they've got the

818
00:38:39,780 --> 00:38:45,480
ability to dump all the meridi syntax

819
00:38:42,900 --> 00:38:47,280
and the sequences and uh the other

820
00:38:45,480 --> 00:38:49,980
little extensions that we've added over

821
00:38:47,280 --> 00:38:54,060
the years so that's a way to actually do

822
00:38:49,980 --> 00:38:56,579
parallel dumping and get a logical SQL

823
00:38:54,060 --> 00:38:59,160
output that they've been maintained

824
00:38:56,579 --> 00:39:01,859
there for years and we're just working

825
00:38:59,160 --> 00:39:02,820
with the community that way to bring it

826
00:39:01,859 --> 00:39:07,220
up to

827
00:39:02,820 --> 00:39:07,220
the standard of all the features we need

828
00:39:07,400 --> 00:39:12,720
try something harder

829
00:39:10,800 --> 00:39:15,560
oh

830
00:39:12,720 --> 00:39:15,560
there's a question

831
00:39:19,020 --> 00:39:22,260
you don't think by adding one CRC

832
00:39:21,119 --> 00:39:24,420
function you might get a whole other

833
00:39:22,260 --> 00:39:27,420
people asking for all the other crcs

834
00:39:24,420 --> 00:39:27,420
possibly

835
00:39:27,660 --> 00:39:33,660
um yes there's only uh

836
00:39:30,900 --> 00:39:35,760
two Hardware accelerated crcs in the in

837
00:39:33,660 --> 00:39:37,920
the in the server and that's the the

838
00:39:35,760 --> 00:39:41,640
IEEE and the

839
00:39:37,920 --> 00:39:41,640
um uh Castillo

840
00:39:41,760 --> 00:39:44,880
um polynomial

841
00:39:45,660 --> 00:39:52,140
so um there might be but um another

842
00:39:49,560 --> 00:39:54,480
thing I want to actually working on is

843
00:39:52,140 --> 00:39:57,240
working on Liz

844
00:39:54,480 --> 00:39:58,619
a guy in the US started doing rust

845
00:39:57,240 --> 00:40:02,040
plugins

846
00:39:58,619 --> 00:40:04,740
um there so what's actually implemented

847
00:40:02,040 --> 00:40:08,040
out there's a Marie to be rust

848
00:40:04,740 --> 00:40:10,440
UDF crate so you can actually write UDF

849
00:40:08,040 --> 00:40:13,440
functions in Rust and you can write

850
00:40:10,440 --> 00:40:16,320
whatever polynomial you want in there or

851
00:40:13,440 --> 00:40:19,320
whatever other function you want in

852
00:40:16,320 --> 00:40:20,760
there and it'll come up in standard

853
00:40:19,320 --> 00:40:23,220
syntax so

854
00:40:20,760 --> 00:40:25,640
yeah write it in Rust and don't crash

855
00:40:23,220 --> 00:40:25,640
the server

856
00:40:28,640 --> 00:40:33,320
huh

857
00:40:30,359 --> 00:40:33,320
thank you all for coming

858
00:40:34,740 --> 00:40:36,859
thank you