Can the script used in PT3 to cluster be published?

[Guest]

Josh, or others,

Since I am running my PostgreSQL database on a linux server I run a cron job every night doing a vacuum, cluster and analysis, and this works fine. However in the release post Josh mentioned that the cluster done by the PT3 software actually does something more than just the standard CLUSTER command.

Question to the developers: Can you please publish the commands used in the PT3 cluster script so that I can run these in a nightly job?

Thanks a bunch in advance.

[Josh]

Enable logging. Each statement is in the log file.

[Guest]

Thanks, will start figuring that out in the weekend :)

[driemekasten]

I enabled statement-logging in postgres and got the following. Please consider that my username to connect to the postgres-db is "poker". Change it to your needs (some of the ALTER TABLE statements):

Cluster:
LOG: statement: BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
LOG: statement: LOCK TABLE "holdem_hand_player_combinations", "holdem_hand_player_detail", "holdem_hand_player_statistics", "holdem_hand_summary", "holdem_table_session_summary", "player" IN ACCESS EXCLUSIVE MODE;
LOG: statement: CREATE TABLE "new_holdem_hand_player_combinations" AS SELECT * FROM "holdem_hand_player_combinations" ORDER BY id_player DESC, id_hand DESC;
LOG: statement: DROP TABLE "holdem_hand_player_combinations";
LOG: statement: ALTER TABLE "new_holdem_hand_player_combinations" RENAME TO "holdem_hand_player_combinations";
LOG: statement: CREATE INDEX "hhpc:idx1-id_hand" ON "holdem_hand_player_combinations" USING btree (id_hand);
LOG: statement: CREATE INDEX "hhpc:idx2-id_player" ON "holdem_hand_player_combinations" USING btree (id_player);
LOG: statement: ALTER TABLE "holdem_hand_player_combinations" ADD CONSTRAINT holdem_hand_player_combinations_primary_key PRIMARY KEY (id_hand, id_player), OWNER TO "poker";
NOTICE: ALTER TABLE / ADD PRIMARY KEY will create implicit index "holdem_hand_player_combinations_primary_key" for table "holdem_hand_player_combinations"
LOG: statement: CREATE TABLE "new_holdem_hand_player_detail" AS SELECT * FROM "holdem_hand_player_detail" ORDER BY id_player DESC, id_hand DESC;
LOG: statement: DROP TABLE "holdem_hand_player_detail";
LOG: statement: ALTER TABLE "new_holdem_hand_player_detail" RENAME TO "holdem_hand_player_detail";
LOG: statement: CREATE INDEX "hhpd:idx1-id_hand" ON "holdem_hand_player_detail" USING btree (id_hand);
LOG: statement: CREATE INDEX "hhpd:idx2-id_player" ON "holdem_hand_player_detail" USING btree (id_player);
LOG: statement: ALTER TABLE "holdem_hand_player_detail" ADD CONSTRAINT holdem_hand_player_detail_primary_key PRIMARY KEY (id_player, id_hand),OWNER TO "poker";
NOTICE: ALTER TABLE / ADD PRIMARY KEY will create implicit index "holdem_hand_player_detail_primary_key" for table "holdem_hand_player_detail"
LOG: statement: CREATE TABLE "new_holdem_hand_player_statistics" AS SELECT * FROM "holdem_hand_player_statistics" ORDER BY id_player DESC, id_session DESC, id_hand DESC;
LOG: statement: DROP TABLE "holdem_hand_player_statistics";
LOG: statement: ALTER TABLE "new_holdem_hand_player_statistics" RENAME TO "holdem_hand_player_statistics";
LOG: statement: CREATE INDEX "hhps:idx1-id_hand" ON "holdem_hand_player_statistics" USING btree (id_hand);
LOG: statement: CREATE INDEX "hhps:idx2-id_player" ON "holdem_hand_player_statistics" USING btree (id_player);
LOG: statement: CREATE INDEX "hhps:idx3-id_limit" ON "holdem_hand_player_statistics" USING btree (id_limit);
LOG: statement: CREATE INDEX "hhps:idx4-date_played" ON "holdem_hand_player_statistics" USING btree (date_played);
LOG: statement: CREATE INDEX "hhps:idx5-id_session" ON "holdem_hand_player_statistics" USING btree (id_session);
LOG: statement: CREATE INDEX "hhps:idx6-position" ON "holdem_hand_player_statistics" USING btree ("position");
LOG: statement: ALTER TABLE "holdem_hand_player_statistics" ADD CONSTRAINT holdem_hand_player_statistics_primary_key PRIMARY KEY (id_player, id_hand), OWNER TO "poker";
NOTICE: ALTER TABLE / ADD PRIMARY KEY will create implicit index "holdem_hand_player_statistics_primary_key" for table "holdem_hand_player_statistics"
LOG: statement: CREATE TABLE "new_holdem_hand_summary" AS SELECT * FROM "holdem_hand_summary" ORDER BY id_site, id_limit, id_table, id_hand DESC;
LOG: statement: DROP TABLE "holdem_hand_summary";
LOG: statement: ALTER TABLE "new_holdem_hand_summary" RENAME TO "holdem_hand_summary";
LOG: statement: CREATE INDEX "hhs:idx1-date_played" ON "holdem_hand_summary" USING btree (date_played);
LOG: statement: CREATE INDEX "hhs:idx2-id_limit" ON "holdem_hand_summary" USING btree (id_limit);
LOG: statement: CREATE INDEX "hhs:idx3-hand_no" ON "holdem_hand_summary" USING btree (hand_no);
LOG: statement: ALTER TABLE "holdem_hand_summary" ADD CONSTRAINT holdem_hand_summary_primary_key PRIMARY KEY (id_hand), ADD CONSTRAINT "hhs-unique_hand_no" UNIQUE (id_site, hand_no), OWNER TO "poker";
NOTICE: ALTER TABLE / ADD PRIMARY KEY will create implicit index "holdem_hand_summary_primary_key" for table "holdem_hand_summary"
NOTICE: ALTER TABLE / ADD UNIQUE will create implicit index "hhs-unique_hand_no" for table "holdem_hand_summary"
LOG: statement: CREATE TABLE "new_holdem_table_session_summary" AS SELECT * FROM "holdem_table_session_summary" ORDER BY id_player DESC, id_session DESC;
LOG: statement: DROP TABLE "holdem_table_session_summary";
LOG: statement: ALTER TABLE "new_holdem_table_session_summary" RENAME TO "holdem_table_session_summary";
LOG: statement: CREATE INDEX "htss:idx1-id_player" ON "holdem_table_session_summary" USING btree (id_player);
LOG: statement: CREATE INDEX "htss:idx2-id_table" ON "holdem_table_session_summary" USING btree (id_table);
LOG: statement: ALTER TABLE "holdem_table_session_summary" ADD CONSTRAINT holdem_table_session_summary_primary_key PRIMARY KEY (id_session), OWNER TO "poker";
NOTICE: ALTER TABLE / ADD PRIMARY KEY will create implicit index "holdem_table_session_summary_primary_key" for table "holdem_table_session_summary"
LOG: statement: CREATE TABLE "new_player" AS SELECT * FROM "player" ORDER BY id_site, id_player DESC;
LOG: statement: DROP TABLE "player";
LOG: statement: ALTER TABLE "new_player" RENAME TO "player";
LOG: statement: CREATE INDEX "idx1:player_name_search" ON "player" USING btree (player_name_search);
LOG: statement: ALTER TABLE "player" ADD CONSTRAINT player_primary_key PRIMARY KEY (id_player), OWNER TO "poker";
NOTICE: ALTER TABLE / ADD PRIMARY KEY will create implicit index "player_primary_key" for table "player"
LOG: statement: COMMIT;

Vacuum:
LOG: statement: VACUUM FULL;
LOG: statement: VACUUM holdem_table_session_summary;

Analyze:
LOG: statement: ANALYZE;

[driemekasten]

Okay.. Here's the shell script for cluster/vacuum/analyze:

Code: Select all

#!/bin/bash
#
# /usr/local/bin/pt3clusterdb.sh
#
# use at your own risk!!

OWNER=poker                             # username to connect to db and owner of the pt3-tables
DBNAMES="PT3DB1 PT3DB1OBS"              # database-names of pt3-databases to cluster
LOGFILE=/var/log/pt3clusterdb.log       # logfile to log results

for DBNAME in $DBNAMES
do
echo `date +"%h %m %H:%M:%S"` clustering DB $DBNAME >> $LOGFILE
cat <<-EOF | psql -a -U $OWNER $DBNAME >> $LOGFILE 2>&1

BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
LOCK TABLE "holdem_hand_player_combinations", "holdem_hand_player_detail", "holdem_hand_player_statistics", "holdem_hand_summary", "holdem_table_session_summary", "player" IN ACCESS EXCLUSIVE MODE;
CREATE TABLE "new_holdem_hand_player_combinations" AS SELECT * FROM "holdem_hand_player_combinations" ORDER BY id_player DESC, id_hand DESC;
DROP TABLE "holdem_hand_player_combinations";
ALTER TABLE "new_holdem_hand_player_combinations" RENAME TO "holdem_hand_player_combinations";
CREATE INDEX "hhpc:idx1-id_hand" ON "holdem_hand_player_combinations" USING btree (id_hand);
CREATE INDEX "hhpc:idx2-id_player" ON "holdem_hand_player_combinations" USING btree (id_player);
ALTER TABLE "holdem_hand_player_combinations" ADD CONSTRAINT holdem_hand_player_combinations_primary_key PRIMARY KEY (id_hand, id_player), OWNER TO $OWNER;
CREATE TABLE "new_holdem_hand_player_detail" AS SELECT * FROM "holdem_hand_player_detail" ORDER BY id_player DESC, id_hand DESC;
DROP TABLE "holdem_hand_player_detail";
ALTER TABLE "new_holdem_hand_player_detail" RENAME TO "holdem_hand_player_detail";
CREATE INDEX "hhpd:idx1-id_hand" ON "holdem_hand_player_detail" USING btree (id_hand);
CREATE INDEX "hhpd:idx2-id_player" ON "holdem_hand_player_detail" USING btree (id_player);
ALTER TABLE "holdem_hand_player_detail" ADD CONSTRAINT holdem_hand_player_detail_primary_key PRIMARY KEY (id_player, id_hand),OWNER TO $OWNER;
CREATE TABLE "new_holdem_hand_player_statistics" AS SELECT * FROM "holdem_hand_player_statistics" ORDER BY id_player DESC, id_session DESC, id_hand DESC;
DROP TABLE "holdem_hand_player_statistics";
ALTER TABLE "new_holdem_hand_player_statistics" RENAME TO "holdem_hand_player_statistics";
CREATE INDEX "hhps:idx1-id_hand" ON "holdem_hand_player_statistics" USING btree (id_hand);
CREATE INDEX "hhps:idx2-id_player" ON "holdem_hand_player_statistics" USING btree (id_player);
CREATE INDEX "hhps:idx3-id_limit" ON "holdem_hand_player_statistics" USING btree (id_limit);
CREATE INDEX "hhps:idx4-date_played" ON "holdem_hand_player_statistics" USING btree (date_played);
CREATE INDEX "hhps:idx5-id_session" ON "holdem_hand_player_statistics" USING btree (id_session);
CREATE INDEX "hhps:idx6-position" ON "holdem_hand_player_statistics" USING btree ("position");
ALTER TABLE "holdem_hand_player_statistics" ADD CONSTRAINT holdem_hand_player_statistics_primary_key PRIMARY KEY (id_player, id_hand), OWNER TO $OWNER;
CREATE TABLE "new_holdem_hand_summary" AS SELECT * FROM "holdem_hand_summary" ORDER BY id_site, id_limit, id_table, id_hand DESC;
DROP TABLE "holdem_hand_summary";
ALTER TABLE "new_holdem_hand_summary" RENAME TO "holdem_hand_summary";
CREATE INDEX "hhs:idx1-date_played" ON "holdem_hand_summary" USING btree (date_played);
CREATE INDEX "hhs:idx2-id_limit" ON "holdem_hand_summary" USING btree (id_limit);
CREATE INDEX "hhs:idx3-hand_no" ON "holdem_hand_summary" USING btree (hand_no);
ALTER TABLE "holdem_hand_summary" ADD CONSTRAINT holdem_hand_summary_primary_key PRIMARY KEY (id_hand), ADD CONSTRAINT "hhs-unique_hand_no" UNIQUE (id_site, hand_no), OWNER TO $OWNER;
CREATE TABLE "new_holdem_table_session_summary" AS SELECT * FROM "holdem_table_session_summary" ORDER BY id_player DESC, id_session DESC;
DROP TABLE "holdem_table_session_summary";
ALTER TABLE "new_holdem_table_session_summary" RENAME TO "holdem_table_session_summary";
CREATE INDEX "htss:idx1-id_player" ON "holdem_table_session_summary" USING btree (id_player);
CREATE INDEX "htss:idx2-id_table" ON "holdem_table_session_summary" USING btree (id_table);
ALTER TABLE "holdem_table_session_summary" ADD CONSTRAINT holdem_table_session_summary_primary_key PRIMARY KEY (id_session), OWNER TO $OWNER;
CREATE TABLE "new_player" AS SELECT * FROM "player" ORDER BY id_site, id_player DESC;
DROP TABLE "player";
ALTER TABLE "new_player" RENAME TO "player";
CREATE INDEX "idx1:player_name_search" ON "player" USING btree (player_name_search);
ALTER TABLE "player" ADD CONSTRAINT player_primary_key PRIMARY KEY (id_player), OWNER TO $OWNER;
COMMIT;

VACUUM FULL;
VACUUM holdem_table_session_summary;

ANALYZE;

\q
EOF

done


[Guest]

Yeah, I reached that same script using the logging.

However, what is the best strategy for vacuuming: Vacuum before running all the clustering commands, or vacuum after it? Anybody have a good idea about that?

[WhiteRider]

If you are running the PT3 cluster/vacuum/analyse you should do it in that order as vacuum does some extra work.

[driemekasten]

ATTENTION! Do NOT use this script with the new beta16. I didn't look at b16, therefore i don't know what will happen. I would appreciate it, if the developers publish the OFFICIAL WAY to do Database-Maintenance for those who run PostgreSQL on Linux.

[Guest]

And why should this script not be used with beta 16? There is no change in the database during the installation, so the script should work just fine with the new version. Or do I miss something here?

[WhiteRider]

I guess it's to do with the cache, and possibly the way the clustering etc is managed.