From c1da556833f3e37a87f362ab6cd4e89b8ed5455c Mon Sep 17 00:00:00 2001 From: Boss Marco Date: Wed, 20 Apr 2022 16:07:13 +0200 Subject: add centos db initialization script --- experiment/scripts/database-centos.sh | 321 ++++++++++++++++++++++++++++++++++ experiment/scripts/run.sh | 6 +- notes.txt | 3 - 3 files changed, 326 insertions(+), 4 deletions(-) create mode 100755 experiment/scripts/database-centos.sh diff --git a/experiment/scripts/database-centos.sh b/experiment/scripts/database-centos.sh new file mode 100755 index 0000000..cd8e22a --- /dev/null +++ b/experiment/scripts/database-centos.sh @@ -0,0 +1,321 @@ +#!/bin/bash +INFO_MSG=" +Setup the database node (start postgresql) +" +OPT_MSG=" +init: + Initialize and start the taler database +" + +set -eux +source ~/scripts/helpers.sh + +# move to tmp to prevent change directory errors +cd /tmp + +function setup_disks() { + if [ -b /dev/disk1 ]; then + echo 'start=2048, type=83' | sfdisk /dev/disk1 || true + yes | mkfs.ext4 -m 0 /dev/disk1 || true + mkdir /mnt/disk || true + mount /dev/disk1 /mnt/disk || true + fi +} + +# Setup the postgresql configuration +function setup_config() { + + restart_rsyslog + + PGSETUP_INITDB_OPTIONS="-D /tmp/postgresql/13/data" + + if [ -d /mnt/disk ]; then + mkdir /mnt/disk/pg_wal + chown -R postgres:postgres /mnt/disk/pg_wal + PGSETUP_INITDB_OPTIONS="${PGSETUP_INITDB_OPTIONS} -X /mnt/disk/pg_wal" + fi + + if [ ! -d /tmp/postgresql/13/data ]; then + mkdir -p /tmp/postgresql/13/data + chown -R postgres:postgres /tmp/postgresql/ + fi + + postgresql-${POSTGRES_VERSION}-setup initdb + + # Get hardware info to tune in postgresql.conf + SHARED_MEM=$(($(awk '/MemTotal/ {print $2}' /proc/meminfo) / 3 )) + CACHE_SIZE=$(($(awk '/MemTotal/ {print $2}' /proc/meminfo) * 3/4)) + NUM_CPU=$(lscpu | grep "CPU(s)" | head -n 1 | awk '{print $2}') + + # Enable huge pages + # Size for huge_pages =~ shared_buffers * 1.25 so that there is enough + VM_PEAK=$((${SHARED_MEM} * 10/8)) + + HUGE_PAGES_SIZE=$(grep ^Hugepagesize /proc/meminfo | awk '{print $2}') + NUM_PAGES=$((${VM_PEAK} / ${HUGE_PAGES_SIZE})) + + if ! grep -q "vm.nr_hugepages'" /etc/sysctl.conf; then + echo "vm.nr_hugepages=${NUM_PAGES}" >> /etc/sysctl.conf + sysctl -p + fi + + # Allow longer lines to be logged by postgres + sed -i '1 i\$MaxMessageSize 4k' /etc/rsyslog.conf + + # Configure postgres with an additional file and include this + # in the main configuration + echo " + listen_addresses='*' + + log_destination=syslog + syslog_ident='taler-database' + + log_error_verbosity=terse + # log_min_messages=PANIC + # log_min_error_statement=PANIC + # client_min_messages=ERROR + + # For pgbadger + # log_checkpoints=on + # log_connections=on + # log_disconnections=on + # log_lock_waits=on + # log_temp_files=0 + # log_autovacuum_min_duration=0 + # log_error_verbosity=default + # log_duration=on + # log_statement=all + + # For explain.py + # syslog_split_messages=off + # log_statement=all + # log_error_verbosity=default + + log_min_duration_statement=${DB_MIN_DURATION_STATEMENT} + + auto_explain.log_min_duration='${DB_MIN_DURATION_STATEMENT}ms' + auto_explain.log_verbose=true + auto_explain.log_nested_statements=true + auto_explain.log_analyze=true + auto_explain.log_buffers=true + auto_explain.log_wal=true + + shared_preload_libraries='pg_stat_statements,auto_explain' + + # Should be set locally + join_collapse_limit=1 + + # Large tables perform bad with the default settings + # However, they could also be set on each table indiviudally + # (NOTE: on partitions!) + # ALTER TABLE known_coins_default + # SET (autovacuum_vacuum_scale_factor = 0.0, autovacuum_vacuum_threshold = 1000); + log_autovacuum_min_duration=0 + default_statistics_target=300 + autovacuum_vacuum_cost_limit=400 + autovacuum_vacuum_scale_factor=0.1 + autovacuum_vacuum_threshold=1000 + + # Default 50, 0.1 + autovacuum_analyze_threshold=50 + autovacuum_analyze_scale_factor=0.1 + + # use 25% of the available memory + # (https://www.postgresql.org/docs/13/runtime-config-resource.html) + # shared_buffers=${SHARED_MEM}kB + # effective_cache_size=${CACHE_SIZE}kB + shared_buffers=24551953kB + effective_cache_size=75491895kB + + huge_pages=on + + # (https://www.postgresql.org/docs/current/runtime-config-wal.html#GUC-MAX-WAL-SIZE) + min_wal_size=20GB + max_wal_size=200GB + wal_buffers=1GB + + checkpoint_completion_target=0.9 + checkpoint_timeout = 15min + checkpoint_flush_after = 2MB + random_page_cost=1.1 + + # Default 2kB + bgwriter_flush_after = 2MB + + # Default 0 + # https://www.cybertec-postgresql.com/en/the-mysterious-backend_flush_after-configuration-setting/ + backend_flush_after = 2MB + + # Too much results in CPU load + # https://www.postgresql.org/docs/13/runtime-config-resource.html#GUC-EFFECTIVE-IO-CONCURRENCY + effective_io_concurrency = 200 + + # Bad when turned off - Recovering db may not be possible + # https://www.postgresql.org/docs/13/runtime-config-wal.html#GUC-FSYNC + fsync = on + + # Not so bad as when turning off fsync, but single transactions might get lost on crash - but + # like they would have aborted cleanly + # https://www.postgresql.org/docs/13/runtime-config-wal.html#GUC-SYNCHRONOUS-COMMIT + # When having replication, this one can be changed (in local only on and off are of use) + # https://www.postgresql.org/docs/13/runtime-config-replication.html#GUC-SYNCHRONOUS-STANDBY-NAMES + # on causes us to get 100% IO load + synchronous_commit = off + + # Default off + wal_compression = off + + wal_sync_method = fsync + + # Bad to turn off, may lead to inconcistency + # https://www.postgresql.org/docs/13/runtime-config-wal.html#GUC-FULL-PAGE-WRITES + # Write full pages to WAL while partial are written to disk, helpful in case of crash, then + # partially written pages can be recovered. + # Can be disabled if FS does not support partial written pages (such as ZFS) + full_page_writes = on + + max_worker_processes=${NUM_CPU} + max_parallel_workers=${NUM_CPU} + max_parallel_workers_per_gather=10 + max_connections=500 + + max_parallel_maintenance_workers=12 + + # out of shared memory + max_locks_per_transaction=85 + + # (max used =~ work_mem * max_connections) + # NOTE: This formula is not completely correct + work_mem=2GB + maintenance_work_mem=4GB + # 1 min + idle_in_transaction_session_timeout=60000 + + # Disable sequential scans + # enable_seqscan=off + + " > /tmp/postgresql/${POSTGRES_VERSION}/data/exchange.conf + + cp /tmp/postgresql/${POSTGRES_VERSION}/data/exchange.conf ${LOG_DIR}/postgresql.conf + + if ! grep -q "include = 'exchange.conf'" \ + /tmp/postgresql/${POSTGRES_VERSION}/data/postgresql.conf; then + echo "include = 'exchange.conf'" >> \ + /tmp/postgresql/${POSTGRES_VERSION}/data/postgresql.conf + fi + + if ! grep -q "host all taler-exchange-httpd 127.16.0.0/12 trust" \ + /tmp/postgresql/${POSTGRES_VERSION}/data/pg_hba.conf; then + echo " + # we need this in centos to initialize the db from remote (taler not installed) + host all taler-exchange-httpd 172.16.0.0/12 trust + host all postgres 172.16.0.0/12 trust + " >> /tmp/postgresql/${POSTGRES_VERSION}/data/pg_hba.conf + fi + + echo " + [Service] + Environment=PGDATA=/tmp/postgresql/13/data + " >> /etc/systemd/system/postgresql-13.service.d/override.conf + systemctl daemon-reload + + systemctl restart postgresql-${POSTGRES_VERSION} +} + +function enable_remote_access() { + # Enable password for taler since this is the case in real world deployments + # For the postgres user do not enable authentication (used in metrics) + if ! grep -q "host all ${DB_USER} 127.16.0.0/12 md5" \ + /tmp/postgresql/${POSTGRES_VERSION}/data/pg_hba.conf; then + echo " + host all ${DB_USER} 172.16.0.0/12 md5 + " >> /tmp/postgresql/${POSTGRES_VERSION}/data/pg_hba.conf + fi +} + +function configure_shard_access() { + + for i in $(seq $NUM_SHARDS); do + su postgres << EOF +psql -d "${DB_NAME}" -tAc "CREATE USER MAPPING IF NOT EXISTS FOR \"${DB_USER}\" + SERVER \"${i}\" + OPTIONS (user '${DB_USER}', password '${DB_PASSWORD}');" +EOF + done + +} + +function remote_init_db() { + + sed -i "s\\postgresql://taler-exchange-httpd@db.${DNS_ZONE}:${DB_PORT}/${DB_NAME}\g" \ + /etc/taler/secrets/exchange-db.secret.conf + + sudo -u taler-exchange-httpd taler-exchange-dbinit -r || true + sudo -u taler-exchange-httpd taler-exchange-dbinit -s || true + + if [ "${SHARD_DB}" = "true" ]; then + sudo -u postgres taler-exchange-dbinit -F ${NUM_SHARDS} + elif [ "${PARTITION_DB}" = "true" ]; then + sudo -u taler-exchange-httpd taler-exchange-dbinit -P ${NUM_PARTITIONS} + else + sudo -u taler-exchange-httpd taler-exchange-dbinit + fi + +} + +# Initialize the database for taler exchange +function init_db() { + + # Create the role taler-exchange-httpd and the database + su postgres << EOF +psql postgres -tAc "DROP DATABASE IF EXISTS \"${DB_NAME}\";" +psql postgres -tAc "SELECT 1 FROM pg_roles WHERE rolname='taler-exchange-httpd'" | \ + grep -q 1 || \ + createuser taler-exchange-httpd +psql -tAc "SELECT 1 FROM pg_database WHERE datname='${DB_NAME}'" | \ + grep -q 1 || \ + createdb -O taler-exchange-httpd "${DB_NAME}" +psql -tAc "CREATE EXTENSION IF NOT EXISTS pg_stat_statements" +EOF + + ssh -o StrictHostKeyChecking=no monitor.${DNS_ZONE} './scripts/database-centos.sh remote-init' + + if [ "${SHARD_DB}" = "true" ]; then + configure_shard_access + fi + + # Create the remote user "$DB_USER" and load pg_stat_statements for metrics + su postgres << EOF +psql postgres -tAc "SELECT 1 FROM pg_roles WHERE rolname='${DB_USER}'" | \ + grep -q 1 || \ + psql << END + CREATE USER "${DB_USER}" with encrypted password '${DB_PASSWORD}'; +END +EOF + + # Grant access to the databse to the remote user + su taler-exchange-httpd -s /bin/bash << EOF +psql -d "${DB_NAME}" +GRANT SELECT,INSERT,UPDATE ON ALL TABLES IN SCHEMA public TO "${DB_USER}"; +GRANT USAGE ON ALL SEQUENCES IN SCHEMA public TO "${DB_USER}"; +EOF + + enable_remote_access + systemctl restart postgresql +} + +case ${1} in + init) + setup_disks + setup_config + restart_rsyslog + ;; + remote-init) + remote_init_db + ;; + *) + taler_perf_help $0 "$INFO_MSG" "$OPT_MSG" + ;; +esac + +exit 0 diff --git a/experiment/scripts/run.sh b/experiment/scripts/run.sh index 11d298b..8ea1adb 100644 --- a/experiment/scripts/run.sh +++ b/experiment/scripts/run.sh @@ -28,7 +28,11 @@ elif [[ "${HOSTNAME}" =~ ${DB_HOSTS} ]]; then PROMTAIL_LOG_PORT=1515 setup_log enable_logrotate - exec ~/scripts/database.sh init + if grep -q "Red Hat" /proc/version; then + exec ~/scripts/database-centos.sh init + else + exec ~/scripts/database.sh init + fi elif [[ "${HOSTNAME}" =~ ${SHARD_HOSTS} ]]; then setup_log enable_logrotate diff --git a/notes.txt b/notes.txt index 99dd902..e69de29 100644 --- a/notes.txt +++ b/notes.txt @@ -1,3 +0,0 @@ -dahu-26.grenoble.grid5000.fr|dahu-2.grenoble.grid5000.fr|dahu-24.grenoble.grid5000.fr|dahu-28.grenoble.grid5000.fr - -case ${HOSTNAME} in ${SHARD_HOSTS}) echo yes;; *) echo no;; esac -- cgit v1.2.3