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

Commit442b89d

Browse files
author
Alena Rybakina
committed
Add smart statement timeout for learning AQO.
AQO evaluates from the current aqo.statement_timeout value whether it has enough time to train.If not, then it increases this time exponentially.We can evaluate whether there was enough time to train the AQO by the average integral error.If the internal error has not practically changed compared to the previous iteration, then the training time increases.If the user needs an upper limit on query execution time, they can set a vanilla statement timeout.The aqo.statement_timeout value will be stored in shmem, next to the training data. Initially it = 0 (default value).The user has the ability to specify the GUC aqo.statement_timeout and terminates the request by analogy with statement timeout.
1 parent8a99337 commit442b89d

File tree

9 files changed

+269
-23
lines changed

9 files changed

+269
-23
lines changed

‎Makefile‎

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -25,7 +25,8 @@ REGRESS =aqo_disabled \
2525
plancache\
2626
statement_timeout\
2727
temp_tables\
28-
top_queries
28+
top_queries\
29+
smart_statement_timeout
2930

3031
fdw_srcdir =$(top_srcdir)/contrib/postgres_fdw
3132
stat_srcdir =$(top_srcdir)/contrib/pg_stat_statements

‎aqo--1.3--1.4.sql‎

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -4,6 +4,8 @@
44
\echo Use"ALTER EXTENSION aqo UPDATE TO '1.4'" to load this file. \quit
55

66
ALTERTABLEpublic.aqo_data ADD COLUMN reliabilitydouble precision [];
7+
ALTERTABLEpublic.aqo_queries ADD COLUMN flex_timeoutbigint;
8+
ALTERTABLEpublic.aqo_queries ADD COLUMN count_increase_timeoutbigint;
79

810
DROPFUNCTIONpublic.top_error_queries(int);
911

‎aqo.c‎

Lines changed: 13 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -36,6 +36,7 @@ intaqo_mode;
3636
boolaqo_enabled= false;/* Signals that CREATE EXTENSION have executed and
3737
all extension tables is ready for use. */
3838
boolforce_collect_stat;
39+
intaqo_statement_timeout;
3940

4041
/*
4142
* Show special info in EXPLAIN mode.
@@ -49,7 +50,7 @@ boolforce_collect_stat;
4950
*/
5051
boolaqo_show_hash;
5152
boolaqo_show_details;
52-
53+
boolchange_flex_timeout;
5354
/* GUC variables */
5455
staticconststructconfig_enum_entryformat_options[]= {
5556
{"intelligent",AQO_MODE_INTELLIGENT, false},
@@ -75,7 +76,6 @@ intauto_tuning_infinite_loop = 8;
7576
/* The number of nearest neighbors which will be chosen for ML-operations */
7677
intaqo_k=3;
7778
doublelog_selectivity_lower_bound=-30;
78-
7979
/*
8080
* Currently we use it only to store query_text string which is initialized
8181
* after a query parsing and is used during the query planning.
@@ -210,6 +210,17 @@ _PG_init(void)
210210
NULL,
211211
NULL,
212212
NULL);
213+
DefineCustomIntVariable("aqo.statement_timeout",
214+
"Time limit on learning.",
215+
NULL,
216+
&aqo_statement_timeout,
217+
0,
218+
0,INT_MAX,
219+
PGC_USERSET,
220+
0,
221+
NULL,
222+
NULL,
223+
NULL);
213224

214225
prev_shmem_startup_hook=shmem_startup_hook;
215226
shmem_startup_hook=aqo_init_shmem;

‎aqo.h‎

Lines changed: 15 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -224,8 +224,17 @@ typedef struct QueryContextData
224224

225225
instr_timestart_execution_time;
226226
doubleplanning_time;
227+
228+
int64flex_timeout;
229+
int64count_increase_timeout;
227230
}QueryContextData;
228231

232+
/*
233+
* Indicator for understading update or not
234+
* flexible statement timeout for query
235+
*/
236+
externboolchange_flex_timeout;
237+
229238
externdoublepredicted_ppi_rows;
230239
externdoublefss_ppi_hash;
231240

@@ -245,6 +254,7 @@ extern double log_selectivity_lower_bound;
245254
/* Parameters for current query */
246255
externQueryContextDataquery_context;
247256
externintnjoins;
257+
externintaqo_statement_timeout;
248258

249259
/* Memory context for long-live data */
250260
externMemoryContextAQOMemoryContext;
@@ -278,6 +288,7 @@ int get_clause_hash(Expr *clause, int nargs, int *args_hash, int *eclass_hash);
278288

279289
/* Storage interaction */
280290
externboolfind_query(uint64qhash,QueryContextData*ctx);
291+
externboolupdate_query_timeout(uint64qhash,int64flex_timeout,int64count_increase_timeout);
281292
externboolupdate_query(uint64qhash,uint64fhash,
282293
boollearn_aqo,booluse_aqo,boolauto_tuning);
283294
externbooladd_query_text(uint64query_hash,constchar*query_string);
@@ -313,6 +324,7 @@ extern double predict_for_relation(List *restrict_clauses, List *selectivities,
313324
voidaqo_ExecutorStart(QueryDesc*queryDesc,inteflags);
314325
voidaqo_ExecutorRun(QueryDesc*queryDesc,ScanDirectiondirection,
315326
uint64count,boolexecute_once);
327+
voidincrease_flex_timeout(uint64query_hash,int64flex_timeout_fin_time);
316328
voidaqo_ExecutorEnd(QueryDesc*queryDesc);
317329

318330
/* Automatic query tuning */
@@ -338,6 +350,8 @@ extern void selectivity_cache_clear(void);
338350
externOidget_aqo_schema(void);
339351
externvoidinit_lock_tag(LOCKTAG*tag,uint64key1,int32key2);
340352
externboolIsQueryDisabled(void);
341-
353+
externdoubleget_mean(double*elems,intnelems);
342354
externList*cur_classes;
355+
externboolget_flex_timeout(uint64qhash,QueryContextData*query_context);
356+
343357
#endif

‎auto_tuning.c‎

Lines changed: 1 addition & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -25,12 +25,10 @@
2525
*/
2626
doubleauto_tuning_convergence_error=0.01;
2727

28-
staticdoubleget_mean(double*elems,intnelems);
2928
staticdoubleget_estimation(double*elems,intnelems);
3029
staticboolis_stable(double*elems,intnelems);
31-
staticboolconverged_cq(double*elems,intnelems);
3230
staticboolis_in_infinite_loop_cq(double*elems,intnelems);
33-
31+
staticboolconverged_cq(double*elems,intnelems);
3432

3533
/*
3634
* Returns mean value of the array of doubles.

‎expected/smart_statement_timeout.out‎

Whitespace-only changes.

‎postprocessing.c‎

Lines changed: 50 additions & 11 deletions
Original file line numberDiff line numberDiff line change
@@ -81,6 +81,8 @@ static void update_query_stat_row(double *et, int *et_size,
8181
staticvoidStoreToQueryEnv(QueryDesc*queryDesc);
8282
staticvoidStorePlanInternals(QueryDesc*queryDesc);
8383
staticboolExtractFromQueryEnv(QueryDesc*queryDesc);
84+
staticint64max_timeout_value;
85+
staticint64growth_rate=3;
8486

8587

8688
/*
@@ -692,14 +694,44 @@ aqo_timeout_handler(void)
692694
ctx.learn=query_context.learn_aqo;
693695
ctx.isTimedOut= true;
694696

695-
elog(NOTICE,"[AQO] Time limit for execution of the statement was expired. AQO tried to learn on partial data.");
697+
elog(NOTICE,"[AQO] Time limit for execution of the statement was expired. AQO tried to learn on partial data. Timeout is %d",(int)get_timeout_finish_time(timeoutCtl.id));
696698
learnOnPlanState(timeoutCtl.queryDesc->planstate, (void*)&ctx);
697699
}
698700

701+
/*
702+
* Function to get the value of a variable with exponential growth
703+
*/
704+
staticint64
705+
get_increment()
706+
{
707+
returnpow(1+growth_rate,query_context.count_increase_timeout);
708+
}
709+
710+
/*
711+
* Function to update flexible timeout
712+
* with value of STETAMENT_TIMEOUT - 1 nano sec
713+
* or lower
714+
*
715+
*/
716+
void
717+
increase_flex_timeout(uint64query_hash,int64flex_timeout_fin_time)
718+
{
719+
LOCKTAGtag;
720+
flex_timeout_fin_time= (flex_timeout_fin_time+1)*get_increment();
721+
722+
init_lock_tag(&tag,query_context.query_hash,0);
723+
LockAcquire(&tag,ExclusiveLock, false, false);
724+
if (!update_query_timeout(query_hash,flex_timeout_fin_time,query_context.count_increase_timeout+1))
725+
elog(NOTICE,"timeout is not updated");
726+
LockRelease(&tag,ExclusiveLock, false);
727+
}
728+
699729
staticbool
700730
set_timeout_if_need(QueryDesc*queryDesc)
701731
{
702-
TimestampTzfin_time;
732+
max_timeout_value=Min((int64)get_timeout_finish_time(STATEMENT_TIMEOUT)-1, (int64)aqo_statement_timeout);
733+
if (max_timeout_value==0)
734+
max_timeout_value=Min((int64)get_timeout_finish_time(STATEMENT_TIMEOUT)-1,query_context.flex_timeout);
703735

704736
if (!get_timeout_active(STATEMENT_TIMEOUT)|| !aqo_learn_statement_timeout)
705737
return false;
@@ -711,6 +743,8 @@ set_timeout_if_need(QueryDesc *queryDesc)
711743
!(query_context.use_aqo||query_context.learn_aqo))
712744
return false;
713745

746+
get_flex_timeout(query_context.query_hash,&query_context);
747+
714748
/*
715749
* Statement timeout exists. AQO should create user timeout right before the
716750
* timeout.
@@ -722,8 +756,8 @@ set_timeout_if_need(QueryDesc *queryDesc)
722756
else
723757
Assert(!get_timeout_active(timeoutCtl.id));
724758

725-
fin_time=get_timeout_finish_time(STATEMENT_TIMEOUT);
726-
enable_timeout_at(timeoutCtl.id,fin_time-1);
759+
760+
enable_timeout_at(timeoutCtl.id,(TimestampTz)max_timeout_value);
727761

728762
/* Save pointer to queryDesc to use at learning after a timeout interruption. */
729763
timeoutCtl.queryDesc=queryDesc;
@@ -822,20 +856,25 @@ aqo_ExecutorEnd(QueryDesc *queryDesc)
822856
list_free(ctx.selectivities);
823857
}
824858

825-
if (query_context.collect_stat)
826-
stat=get_aqo_stat(query_context.query_hash);
859+
stat=get_aqo_stat(query_context.query_hash);
860+
if (cardinality_num_objects>0)
861+
cardinality_error=cardinality_sum_errors /cardinality_num_objects;
862+
else
863+
cardinality_error=-1;
827864

865+
if (stat&&aqo_learn_statement_timeout&&
866+
stat->cardinality_error_with_aqo[stat->cardinality_error_with_aqo_size-1]-cardinality_sum_errors/cardinality_num_objects <=0.1)
867+
{
868+
increase_flex_timeout(query_context.query_hash, (int64)get_timeout_finish_time(timeoutCtl.id));
869+
}
870+
871+
if (query_context.collect_stat)
828872
{
829873
/* Calculate execution time. */
830874
INSTR_TIME_SET_CURRENT(endtime);
831875
INSTR_TIME_SUBTRACT(endtime,query_context.start_execution_time);
832876
execution_time=INSTR_TIME_GET_DOUBLE(endtime);
833877

834-
if (cardinality_num_objects>0)
835-
cardinality_error=cardinality_sum_errors /cardinality_num_objects;
836-
else
837-
cardinality_error=-1;
838-
839878
/* Prevent concurrent updates. */
840879
init_lock_tag(&tag,query_context.query_hash,query_context.fspace_hash);
841880
LockAcquire(&tag,ExclusiveLock, false, false);

‎sql/smart_statement_timeout.sql‎

Lines changed: 43 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,43 @@
1+
-- The function just copied from stats_ext.sql
2+
createfunctioncheck_estimated_rows(text) returns table (estimatedint, actualint)
3+
language plpgsqlas
4+
$$
5+
declare
6+
lntext;
7+
tmptext[];
8+
first_row bool := true;
9+
begin
10+
for lnin
11+
execute format('explain analyze %s', $1)
12+
loop
13+
if first_row then
14+
first_row := false;
15+
tmp := regexp_match(ln,'rows=(\d*) .* rows=(\d*)');
16+
return queryselect tmp[1]::int, tmp[2]::int;
17+
end if;
18+
end loop;
19+
end;
20+
$$;
21+
22+
SET statement_timeout=5000;-- [0.8s]
23+
24+
DROPTABLE IF EXISTS a,b CASCADE;
25+
CREATETABLEa (xint);
26+
INSERT INTO a (x)SELECT mod(ival,10)FROM generate_series(1,1000)As ival;
27+
28+
CREATETABLEb (yint);
29+
INSERT INTO b (y)SELECT mod(ival+1,10)FROM generate_series(1,1000)As ival;
30+
31+
CREATE EXTENSION IF NOT EXISTS aqo;
32+
SETaqo.join_threshold=0;
33+
SETaqo.mode='learn';
34+
SETaqo.show_details='off';
35+
SETaqo.learn_statement_timeout='on';
36+
SETaqo.statement_timeout=4;-- [0.8s]
37+
SELECT check_estimated_rows('SELECT x FROM A,B where x < 10 and y > 10 group by(x);');-- haven't any partial data
38+
select flex_timeout, count_increase_timeoutfrom aqo_querieswhere query_hash<>0;
39+
SELECT check_estimated_rows('SELECT x FROM A,B where x < 10 and y > 10 group by(x);');-- haven't any partial data
40+
select flex_timeout, count_increase_timeoutfrom aqo_querieswhere query_hash<>0;
41+
SELECT check_estimated_rows('SELECT x FROM A,B where x < 10 and y > 10 group by(x);');-- haven't any partial data
42+
select flex_timeout, count_increase_timeoutfrom aqo_querieswhere query_hash<>0;
43+
SELECT check_estimated_rows('SELECT x FROM A,B where x < 10 and y > 10 group by(x);');-- haven't any partial data

0 commit comments

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