Jcrespo has uploaded a new change for review. (
https://gerrit.wikimedia.org/r/375349 )
Change subject: mariadb: Add script to generate watchlist_count table on labs
......................................................................
mariadb: Add script to generate watchlist_count table on labs
This needs to be puppetized so the watchlist_count table is
available on labs (watchlist cannot be there because it contrains
private user preferences, so this is a summary-like table as an
alternative).
Bug: T59617
Change-Id: Id2ade25848991702ccb1ccf4acffa2aadb84155c
---
A modules/role/files/mariadb/generate_watchlist_count.sh
1 file changed, 36 insertions(+), 0 deletions(-)
git pull ssh://gerrit.wikimedia.org:29418/operations/puppet
refs/changes/49/375349/1
diff --git a/modules/role/files/mariadb/generate_watchlist_count.sh
b/modules/role/files/mariadb/generate_watchlist_count.sh
new file mode 100755
index 0000000..fa44c56
--- /dev/null
+++ b/modules/role/files/mariadb/generate_watchlist_count.sh
@@ -0,0 +1,36 @@
+#!/bin/bash
+vslow_host=dbstore1002.eqiad.wmnet
+vslow_port=3306
+target_host=db1069.eqiad.wmnet
+target_port=3311
+query="SELECT count(*) as watchers, wl_namespace, wl_title FROM watchlist
GROUP BY wl_namespace, wl_title HAVING watchers > 29"
+table=watchlist_count
+lock_dir=/tmp/lock-generate-labs-table
+tmp_dir=/home/jynus
+
+if mkdir $lock_dir; then
+ echo "Locking succeeded" >&2
+else
+ echo "Lock failed - exit" >&2
+ exit 1
+fi
+
+for shard in 1 2 3 4 5 6 7; do
+ sort /srv/mediawiki/dblists/private.dblist \
+/srv/mediawiki/dblists/private.dblist /srv/mediawiki/dblists/s${shard}.dblist \
+| uniq -u | \
+ while read db; do
+# echo "Creating table $db.$table..."
+ mysql -h $target_host -P 331${shard} $db -e "CREATE TABLE IF NOT
EXISTS watchlist_count (watchers bigint NOT NULL DEFAULT '0', wl_namespace int
NOT NULL DEFAULT '0', wl_title varbinary(255) NOT NULL DEFAULT '', PRIMARY KEY
(wl_namespace, wl_title), KEY watchers (watchers))"
+ echo "Generating table $db.$table..."
+ mysql -BN -A -h $vslow_host -P $vslow_port $db -e "$query" \
+> ${tmp_dir}/${db}.${table}.txt
+ echo "Saving table to $target_host..."
+ mysql -h $target_host -P 331${shard} $db -e "TRUNCATE TABLE ${table}"
&&
+ mysql -h $target_host -P 331${shard} $db --local-infile -e \
+"LOAD DATA LOCAL INFILE '${tmp_dir}/${db}.${table}.txt' INTO TABLE ${table}" &&
+ rm ${tmp_dir}/${db}.${table}.txt
+ done
+done
+
+rmdir ${lock_dir}
--
To view, visit https://gerrit.wikimedia.org/r/375349
To unsubscribe, visit https://gerrit.wikimedia.org/r/settings
Gerrit-MessageType: newchange
Gerrit-Change-Id: Id2ade25848991702ccb1ccf4acffa2aadb84155c
Gerrit-PatchSet: 1
Gerrit-Project: operations/puppet
Gerrit-Branch: production
Gerrit-Owner: Jcrespo <[email protected]>
_______________________________________________
MediaWiki-commits mailing list
[email protected]
https://lists.wikimedia.org/mailman/listinfo/mediawiki-commits
