vacuumdb --all --analyze-in-stages - wrong order?

Started by Pavel Stehuleover 11 years ago6 messages
#1Pavel Stehule
pavel.stehule@gmail.com

Hello

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?

Now:

DB a
stage 1, stage2, stage3
DB b
stage 1, stage2, stage3

should be:

Stage1
DB a, DB b ...
Stage 2
DB a, DB b ...
Stage 3
DB a, DB b, ..

Regards

Pavel

#2Peter Eisentraut
peter_e@gmx.net
In reply to: Pavel Stehule (#1)
Re: vacuumdb --all --analyze-in-stages - wrong order?

On 5/18/14, 3:52 AM, Pavel Stehule wrote:

Hello

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.

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

#3Peter Eisentraut
peter_e@gmx.net
In reply to: Peter Eisentraut (#2)
1 attachment(s)
Re: vacuumdb --all --analyze-in-stages - wrong order?

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.

Attachments:

fix-analyze-in-stages.patchtext/x-patch; charset=UTF-8; name=fix-analyze-in-stages.patchDownload
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);
 }
 
#4Pavel Stehule
pavel.stehule@gmail.com
In reply to: Peter Eisentraut (#3)
Re: vacuumdb --all --analyze-in-stages - wrong order?

2014-09-04 5:36 GMT+02:00 Peter Eisentraut <peter_e@gmx.net>:

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.

It is little bit hard to read.

/* 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);
run_vacuum_command(conn, sql.data, echo, dbname,
table, progname);
}

maybe better be more verbose - and it can be in alone function, because it
is "analyze only"

if (stage == -1)
{
for (i = 0; i < 3; i++)
{
puts(gettext(stage_messages[i]));
executeCommand(conn, stage_commands[i], progname, echo);
run_vacuum_command(conn, sql.data, echo, dbname, table, progname);
}
}
else
{
puts(gettext(stage_messages[stage]));
executeCommand(conn, stage_commands[stage], progname, echo);
run_vacuum_command(conn, sql.data, echo, dbname, table, progname);
}

Regards

Pavel

#5Peter Eisentraut
peter_e@gmx.net
In reply to: Pavel Stehule (#4)
Re: vacuumdb --all --analyze-in-stages - wrong order?

On 9/4/14 4:23 PM, Pavel Stehule wrote:

It is little bit hard to read.

maybe better be more verbose - and it can be in alone function, because
it is "analyze only"

if (stage == -1)
{
for (i = 0; i < 3; i++)
{
puts(gettext(stage_messages[i]));
executeCommand(conn, stage_commands[i], progname, echo);
run_vacuum_command(conn, sql.data, echo, dbname, table, progname);
}
}
else
{
puts(gettext(stage_messages[stage]));
executeCommand(conn, stage_commands[stage], progname, echo);
run_vacuum_command(conn, sql.data, echo, dbname, table, progname);
}

Done that way, thanks!

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

#6Pavel Stehule
pavel.stehule@gmail.com
In reply to: Peter Eisentraut (#5)
Re: vacuumdb --all --analyze-in-stages - wrong order?

2014-09-12 3:44 GMT+02:00 Peter Eisentraut <peter_e@gmx.net>:

On 9/4/14 4:23 PM, Pavel Stehule wrote:

It is little bit hard to read.

maybe better be more verbose - and it can be in alone function, because
it is "analyze only"

if (stage == -1)
{
for (i = 0; i < 3; i++)
{
puts(gettext(stage_messages[i]));
executeCommand(conn, stage_commands[i], progname, echo);
run_vacuum_command(conn, sql.data, echo, dbname, table,

progname);

}
}
else
{
puts(gettext(stage_messages[stage]));
executeCommand(conn, stage_commands[stage], progname, echo);
run_vacuum_command(conn, sql.data, echo, dbname, table, progname);
}

Done that way, thanks!

Thank you

It is interesting feature

Pavel