Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commitef2aa43

Browse files
committed
Move aqo_query_stat table into file storage. Use shmem hash table and file.
Replace UI according to this change.
1 parent95de10e commitef2aa43

19 files changed

+573
-418
lines changed

‎aqo--1.4--1.5.sql‎

Lines changed: 37 additions & 15 deletions
Original file line numberDiff line numberDiff line change
@@ -33,17 +33,38 @@ CREATE TABLE aqo_query_texts (
3333
query_texttextNOT NULL
3434
);
3535

36-
CREATETABLEaqo_query_stat (
37-
query_hashbigintCONSTRAINT aqo_query_stat_idxPRIMARY KEYREFERENCES aqo_queriesON DELETE CASCADE,
38-
execution_time_with_aqodouble precision[],
39-
execution_time_without_aqodouble precision[],
40-
planning_time_with_aqodouble precision[],
41-
planning_time_without_aqodouble precision[],
42-
cardinality_error_with_aqodouble precision[],
43-
cardinality_error_without_aqodouble precision[],
44-
executions_with_aqobigint,
45-
executions_without_aqobigint
46-
);
36+
/* Now redefine*/
37+
CREATEFUNCTIONaqo_query_stat(
38+
OUT queryidbigint,
39+
OUT execution_time_with_aqodouble precision[],
40+
OUT execution_time_without_aqodouble precision[],
41+
OUT planning_time_with_aqodouble precision[],
42+
OUT planning_time_without_aqodouble precision[],
43+
OUT cardinality_error_with_aqodouble precision[],
44+
OUT cardinality_error_without_aqodouble precision[],
45+
OUT executions_with_aqobigint,
46+
OUT executions_without_aqobigint
47+
)
48+
RETURNS SETOF record
49+
AS'MODULE_PATHNAME','aqo_query_stat'
50+
LANGUAGE C STRICT VOLATILE PARALLEL SAFE;
51+
52+
CREATEVIEWaqo_query_statASSELECT*FROM aqo_query_stat();
53+
54+
--
55+
-- Remove all records in the AQO statistics.
56+
-- Return number of rows removed.
57+
--
58+
CREATEFUNCTIONaqo_stat_reset() RETURNSbigint
59+
AS'MODULE_PATHNAME'
60+
LANGUAGE C PARALLEL SAFE;
61+
62+
COMMENT ON FUNCTION aqo_stat_reset() IS
63+
'Reset query statistics gathered by AQO';
64+
65+
CREATEFUNCTIONaqo_stat_remove(fsbigint) RETURNS bool
66+
AS'MODULE_PATHNAME'
67+
LANGUAGE C STRICT PARALLEL SAFE;
4768

4869
--
4970
-- Re-create the aqo_data table.
@@ -97,7 +118,7 @@ IF (controlled) THEN
97118
execution_time_with_aqo[array_length(execution_time_with_aqo,1)]AS exectime,
98119
executions_with_aqoAS execs
99120
FROM aqo_queries aqJOIN aqo_query_stat aqs
100-
ONaq.query_hash=aqs.query_hash
121+
ONaq.query_hash=aqs.queryid
101122
WHERE TRUE= ANY (SELECT unnest(execution_time_with_aqo)IS NOT NULL)
102123
)AS q1
103124
ORDER BY nnASC;
@@ -116,7 +137,7 @@ ELSE
116137
(SELECTAVG(t)FROM unnest(execution_time_without_aqo) t)AS exectime,
117138
executions_without_aqoAS execs
118139
FROM aqo_queries aqJOIN aqo_query_stat aqs
119-
ONaq.query_hash=aqs.query_hash
140+
ONaq.query_hash=aqs.queryid
120141
WHERE TRUE= ANY (SELECT unnest(execution_time_without_aqo)IS NOT NULL)
121142
)AS q1
122143
ORDER BY (nn)ASC;
@@ -195,6 +216,7 @@ BEGIN
195216

196217
-- Remove ALL feature space if one of oids isn't exists
197218
DELETEFROM aqo_queriesWHERE fspace_hash= fs;
219+
PERFORM*FROM aqo_stat_remove(fs);
198220
END LOOP;
199221

200222
-- Calculate difference with previous state of knowledge base
@@ -235,7 +257,7 @@ IF (controlled) THEN
235257
cardinality_error_with_aqo[array_length(cardinality_error_with_aqo,1)]AS cerror,
236258
executions_with_aqoAS execs
237259
FROM aqo_queries aqJOIN aqo_query_stat aqs
238-
ONaq.query_hash=aqs.query_hash
260+
ONaq.query_hash=aqs.queryid
239261
WHERE TRUE= ANY (SELECT unnest(cardinality_error_with_aqo)IS NOT NULL)
240262
)AS q1
241263
ORDER BY nnASC;
@@ -251,7 +273,7 @@ ELSE
251273
(SELECTAVG(t)FROM unnest(cardinality_error_without_aqo) t)AS cerror,
252274
executions_without_aqoAS execs
253275
FROM aqo_queries aqJOIN aqo_query_stat aqs
254-
ONaq.query_hash=aqs.query_hash
276+
ONaq.query_hash=aqs.queryid
255277
WHERE TRUE= ANY (SELECT unnest(cardinality_error_without_aqo)IS NOT NULL)
256278
)AS q1
257279
ORDER BY (nn)ASC;

‎aqo.c‎

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -63,7 +63,7 @@ static const struct config_enum_entry format_options[] = {
6363
};
6464

6565
/* Parameters of autotuning */
66-
intaqo_stat_size=20;
66+
intaqo_stat_size=STAT_SAMPLE_SIZE;
6767
intauto_tuning_window_size=5;
6868
doubleauto_tuning_exploration=0.1;
6969
intauto_tuning_max_iterations=50;
@@ -205,7 +205,7 @@ _PG_init(void)
205205
"Used for smooth transition from table storage",
206206
NULL,
207207
&aqo_use_file_storage,
208-
false,
208+
true,
209209
PGC_USERSET,
210210
0,
211211
NULL,

‎aqo.h‎

Lines changed: 2 additions & 32 deletions
Original file line numberDiff line numberDiff line change
@@ -135,7 +135,6 @@
135135
#include"optimizer/cost.h"
136136
#include"parser/analyze.h"
137137
#include"parser/parsetree.h"
138-
#include"utils/array.h"
139138
#include"utils/builtins.h"
140139
#include"utils/guc.h"
141140
#include"utils/hsearch.h"
@@ -145,6 +144,7 @@
145144
#include"utils/snapmgr.h"
146145

147146
#include"machine_learning.h"
147+
#include"storage.h"
148148

149149
/* Check PostgreSQL version (9.6.0 contains important changes in planner) */
150150
#ifPG_VERSION_NUM<90600
@@ -175,32 +175,6 @@ extern bool aqo_show_hash;
175175
externboolaqo_show_details;
176176
externintaqo_join_threshold;
177177

178-
/*
179-
* It is mostly needed for auto tuning of query. with auto tuning mode aqo
180-
* checks stability of last executions of the query, bad influence of strong
181-
* cardinality estimation on query execution (planner bug?) and so on.
182-
* It can induce aqo to suppress machine learning for this query.
183-
*/
184-
typedefstruct
185-
{
186-
double*execution_time_with_aqo;
187-
double*execution_time_without_aqo;
188-
double*planning_time_with_aqo;
189-
double*planning_time_without_aqo;
190-
double*cardinality_error_with_aqo;
191-
double*cardinality_error_without_aqo;
192-
193-
intexecution_time_with_aqo_size;
194-
intexecution_time_without_aqo_size;
195-
intplanning_time_with_aqo_size;
196-
intplanning_time_without_aqo_size;
197-
intcardinality_error_with_aqo_size;
198-
intcardinality_error_without_aqo_size;
199-
200-
int64executions_with_aqo;
201-
int64executions_without_aqo;
202-
}QueryStat;
203-
204178
/* Parameters for current query */
205179
typedefstructQueryContextData
206180
{
@@ -288,8 +262,6 @@ extern bool load_fss(uint64 fs, int fss, OkNNrdata *data, List **reloids,
288262
externboolupdate_fss_ext(uint64fs,intfss,OkNNrdata*data,
289263
List*reloids,boolisTimedOut);
290264
externboolupdate_fss(uint64fs,intfss,OkNNrdata*data,List*reloids);
291-
QueryStat*get_aqo_stat(uint64query_hash);
292-
voidupdate_aqo_stat(uint64query_hash,QueryStat*stat);
293265
externboolmy_index_insert(RelationindexRelation,Datum*values,bool*isnull,
294266
ItemPointerheap_t_ctid,RelationheapRelation,
295267
IndexUniqueCheckcheckUnique);
@@ -317,7 +289,7 @@ void aqo_ExecutorRun(QueryDesc *queryDesc, ScanDirection direction,
317289
voidaqo_ExecutorEnd(QueryDesc*queryDesc);
318290

319291
/* Automatic query tuning */
320-
externvoidautomatical_query_tuning(uint64query_hash,QueryStat*stat);
292+
externvoidautomatical_query_tuning(uint64query_hash,StatEntry*stat);
321293

322294
/* Utilities */
323295
externintint64_compare(constvoid*a,constvoid*b);
@@ -326,8 +298,6 @@ extern int double_cmp(const void *a, const void *b);
326298
externint*argsort(void*a,intn,size_tes,
327299
int (*cmp) (constvoid*,constvoid*));
328300
externint*inverse_permutation(int*a,intn);
329-
externQueryStat*palloc_query_stat(void);
330-
externvoidpfree_query_stat(QueryStat*stat);
331301

332302
/* Selectivity cache for parametrized baserels */
333303
externvoidcache_selectivity(intclause_hash,intrelid,intglobal_relid,

‎aqo_shared.c‎

Lines changed: 36 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -4,9 +4,12 @@
44

55
#include"postgres.h"
66

7+
#include"lib/dshash.h"
8+
#include"miscadmin.h"
79
#include"storage/shmem.h"
810

911
#include"aqo_shared.h"
12+
#include"storage.h"
1013

1114

1215
typedefstruct
@@ -23,11 +26,13 @@ shmem_startup_hook_type prev_shmem_startup_hook = NULL;
2326
AQOSharedState*aqo_state=NULL;
2427
HTAB*fss_htab=NULL;
2528
staticintaqo_htab_max_items=1000;
29+
staticintfs_max_items=1000;/* Max number of different feature spaces in ML model */
2630
staticuint32temp_storage_size=1024*1024*10;/* Storage size, in bytes */
2731
staticdsm_segment*seg=NULL;
2832

2933

3034
staticvoidaqo_detach_shmem(intcode,Datumarg);
35+
staticvoidon_shmem_shutdown(intcode,Datumarg);
3136

3237

3338
void*
@@ -169,16 +174,23 @@ aqo_init_shmem(void)
169174
boolfound;
170175
HASHCTLinfo;
171176

177+
if (prev_shmem_startup_hook)
178+
prev_shmem_startup_hook();
179+
172180
aqo_state=NULL;
173181
fss_htab=NULL;
182+
stat_htab=NULL;
174183

175184
LWLockAcquire(AddinShmemInitLock,LW_EXCLUSIVE);
176185
aqo_state=ShmemInitStruct("aqo",sizeof(AQOSharedState),&found);
177186
if (!found)
178187
{
179188
/* First time through ... */
189+
180190
LWLockInitialize(&aqo_state->lock,LWLockNewTrancheId());
181191
aqo_state->dsm_handler=DSM_HANDLE_INVALID;
192+
193+
LWLockInitialize(&aqo_state->stat_lock,LWLockNewTrancheId());
182194
}
183195

184196
info.keysize=sizeof(htab_key);
@@ -188,8 +200,31 @@ aqo_init_shmem(void)
188200
&info,
189201
HASH_ELEM |HASH_BLOBS);
190202

203+
info.keysize=sizeof(((StatEntry*)0)->queryid);
204+
info.entrysize=sizeof(StatEntry);
205+
stat_htab=ShmemInitHash("aqo stat hash",
206+
fs_max_items,fs_max_items,
207+
&info,
208+
HASH_ELEM |HASH_BLOBS);
209+
191210
LWLockRelease(AddinShmemInitLock);
192211
LWLockRegisterTranche(aqo_state->lock.tranche,"aqo");
212+
LWLockRegisterTranche(aqo_state->stat_lock.tranche,"aqo stat storage");
213+
214+
if (!IsUnderPostmaster)
215+
{
216+
on_shmem_exit(on_shmem_shutdown, (Datum)0);
217+
aqo_stat_load();
218+
}
219+
}
220+
221+
/*
222+
* Main idea here is to store all ML data in temp files on postmaster shutdown.
223+
*/
224+
staticvoid
225+
on_shmem_shutdown(intcode,Datumarg)
226+
{
227+
aqo_stat_flush();
193228
}
194229

195230
Size
@@ -199,6 +234,7 @@ aqo_memsize(void)
199234

200235
size=MAXALIGN(sizeof(AQOSharedState));
201236
size=add_size(size,hash_estimate_size(aqo_htab_max_items,sizeof(htab_entry)));
237+
size=add_size(size,hash_estimate_size(fs_max_items,sizeof(AQOSharedState)));
202238

203239
returnsize;
204240
}

‎aqo_shared.h‎

Lines changed: 5 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -5,6 +5,8 @@
55
#include"storage/dsm.h"
66
#include"storage/ipc.h"
77
#include"storage/lwlock.h"
8+
#include"utils/dsa.h"
9+
#include"lib/dshash.h"
810

911
#defineAQO_SHARED_MAGIC0x053163
1012

@@ -25,6 +27,9 @@ typedef struct AQOSharedState
2527
{
2628
LWLocklock;/* mutual exclusion */
2729
dsm_handledsm_handler;
30+
31+
/* Storage fields */
32+
LWLockstat_lock;/* lock for access to stat storage */
2833
}AQOSharedState;
2934

3035

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp