On Mon, 2014-05-19 at 13:51 -0400, Peter Eisentraut wrote:
> On 5/18/14, 3:52 AM, Pavel Stehule wrote:
> > I am looking on --analyze-in-stages option. If I understand well,
> > motivation for this option is a get some minimal statistic for databases
> > in minimal time. But when I tested, I found so iterations are per
> > databases, not per stages - some first database get a maximum statistics
> > and second has zero statistics. Isn't it unpractical?
>
> Yes. Let me see if I can fix that.
At long last, here is a patch.
If somebody has an idea how to code some of that less confusingly, let
me know.
diff --git a/src/bin/scripts/t/102_vacuumdb_stages.pl b/src/bin/scripts/t/102_vacuumdb_stages.pl
index 4b032d3..18d596e 100644
--- a/src/bin/scripts/t/102_vacuumdb_stages.pl
+++ b/src/bin/scripts/t/102_vacuumdb_stages.pl
@@ -1,7 +1,7 @@
use strict;
use warnings;
use TestLib;
-use Test::More tests => 1;
+use Test::More tests => 2;
my $tempdir = tempdir;
start_test_server $tempdir;
@@ -15,3 +15,20 @@
.*statement:\ RESET\ default_statistics_target;
.*statement:\ ANALYZE/sx,
'analyze three times');
+
+
+issues_sql_like(
+ [ 'vacuumdb', '--analyze-in-stages', '--all' ],
+ qr/.*statement:\ SET\ default_statistics_target=1;\ SET\ vacuum_cost_delay=0;
+ .*statement:\ ANALYZE.*
+ .*statement:\ SET\ default_statistics_target=1;\ SET\ vacuum_cost_delay=0;
+ .*statement:\ ANALYZE.*
+ .*statement:\ SET\ default_statistics_target=10;\ RESET\ vacuum_cost_delay;
+ .*statement:\ ANALYZE.*
+ .*statement:\ SET\ default_statistics_target=10;\ RESET\ vacuum_cost_delay;
+ .*statement:\ ANALYZE.*
+ .*statement:\ RESET\ default_statistics_target;
+ .*statement:\ ANALYZE.*
+ .*statement:\ RESET\ default_statistics_target;
+ .*statement:\ ANALYZE/sx,
+ 'analyze more than one database in stages');
diff --git a/src/bin/scripts/vacuumdb.c b/src/bin/scripts/vacuumdb.c
index 0cfe5b0..5987869 100644
--- a/src/bin/scripts/vacuumdb.c
+++ b/src/bin/scripts/vacuumdb.c
@@ -16,7 +16,7 @@
static void vacuum_one_database(const char *dbname, bool full, bool verbose,
- bool and_analyze, bool analyze_only, bool analyze_in_stages, bool freeze,
+ bool and_analyze, bool analyze_only, bool analyze_in_stages, int stage, bool freeze,
const char *table, const char *host, const char *port,
const char *username, enum trivalue prompt_password,
const char *progname, bool echo);
@@ -217,7 +217,7 @@ main(int argc, char *argv[])
for (cell = tables.head; cell; cell = cell->next)
{
vacuum_one_database(dbname, full, verbose, and_analyze,
- analyze_only, analyze_in_stages,
+ analyze_only, analyze_in_stages, -1,
freeze, cell->val,
host, port, username, prompt_password,
progname, echo);
@@ -225,7 +225,7 @@ main(int argc, char *argv[])
}
else
vacuum_one_database(dbname, full, verbose, and_analyze,
- analyze_only, analyze_in_stages,
+ analyze_only, analyze_in_stages, -1,
freeze, NULL,
host, port, username, prompt_password,
progname, echo);
@@ -254,7 +254,7 @@ run_vacuum_command(PGconn *conn, const char *sql, bool echo, const char *dbname,
static void
vacuum_one_database(const char *dbname, bool full, bool verbose, bool and_analyze,
- bool analyze_only, bool analyze_in_stages, bool freeze, const char *table,
+ bool analyze_only, bool analyze_in_stages, int stage, bool freeze, const char *table,
const char *host, const char *port,
const char *username, enum trivalue prompt_password,
const char *progname, bool echo)
@@ -336,7 +336,9 @@ vacuum_one_database(const char *dbname, bool full, bool verbose, bool and_analyz
};
int i;
- for (i = 0; i < 3; i++)
+ /* If stage is -1, then run all stages. Otherwise, we got a stage
+ * from vacuum_all_databases(), so just run that one. */
+ for (i = (stage == -1 ? 0 : stage); i < (stage == -1 ? 3 : stage + 1); i++)
{
puts(gettext(stage_messages[i]));
executeCommand(conn, stage_commands[i], progname, echo);
@@ -361,12 +363,20 @@ vacuum_all_databases(bool full, bool verbose, bool and_analyze, bool analyze_onl
PGconn *conn;
PGresult *result;
int i;
+ int stage;
conn = connectMaintenanceDatabase(maintenance_db, host, port,
username, prompt_password, progname);
result = executeQuery(conn, "SELECT datname FROM pg_database WHERE datallowconn ORDER BY 1;", progname, echo);
PQfinish(conn);
+ /* If analyzing in stages, then run through all stages. Otherwise just
+ * run once, passing -1 as the stage. */
+ for (stage = (analyze_in_stages ? 0 : -1);
+ stage < (analyze_in_stages ? 3 : 0);
+ stage++)
+ {
+
for (i = 0; i < PQntuples(result); i++)
{
char *dbname = PQgetvalue(result, i, 0);
@@ -378,11 +388,13 @@ vacuum_all_databases(bool full, bool verbose, bool and_analyze, bool analyze_onl
}
vacuum_one_database(dbname, full, verbose, and_analyze, analyze_only,
- analyze_in_stages,
+ analyze_in_stages, stage,
freeze, NULL, host, port, username, prompt_password,
progname, echo);
}
+ }
+
PQclear(result);
}
--
Sent via pgsql-hackers mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
