PL19DB

by perconalab
83 deployments · 83 still active · last rev. 5 days ago

DB Deployment for PL19 Demo

Compatible with: Ubuntu 18.04 LTS
						#!/bin/bash
# This block defines the variables the user of the script needs to input
# when deploying using this script.
#
#
#<UDF name="hostname" label="The hostname for the new Linode.">
# HOSTNAME=
#<UDF name="pmmserver" label="The IP/Host Name of PMM Server to use.">
# PMMSERVER=
#

# Added logging for debug purposes
exec >  >(tee -a /root/stackscript.log)
exec 2> >(tee -a /root/stackscript.log >&2)

# Kernel Tune
echo 1 > /proc/sys/vm/swappiness

fallocate -l 1G /swapfile
dd if=/dev/zero of=/swapfile bs=1024 count=1048576
chmod 600 /swapfile
mkswap /swapfile
swapon /swapfile



# This section sets the hostname.
echo $HOSTNAME > /etc/hostname
hostname -F /etc/hostname

#Percona 
cd /tmp/
wget https://repo.percona.com/apt/percona-release_latest.$(lsb_release -sc)_all.deb
dpkg -i percona-release_latest.$(lsb_release -sc)_all.deb
percona-release setup ps80
DEBIAN_FRONTEND=noninteractive apt-get -y install percona-server-server sysbench sysbench-tpcc bc screen 

# "mysql2" db should have bad configuration

if [ "$HOSTNAME" == "mysql2" ]; then

cat > /etc/mysql/my.cnf << EOF
[mysqld]
#MYSQL2
innodb_buffer_pool_chunk_size=32M
innodb_buffer_pool_size=32M
innodb_buffer_pool_instances=1
innodb_log_file_size=5M
innodb_flush_method=O_DIRECT
innodb_numa_interleave=1
innodb_flush_neighbors=0
log_bin
server_id=1
binlog_expire_logs_seconds=600
log_output=file
slow_query_log=ON
long_query_time=0
log_slow_rate_limit=1
log_slow_rate_type=query
log_slow_verbosity=full
log_slow_admin_statements=ON
log_slow_slave_statements=ON
slow_query_log_always_write_time=1
slow_query_log_use_global_control=all
innodb_monitor_enable=all
userstat=1
EOF

else

cat > /etc/mysql/my.cnf << EOF
[mysqld]
#MYSQL NOT2
innodb_buffer_pool_size=256M
innodb_buffer_pool_instances=1
innodb_log_file_size=1G
innodb_flush_method=O_DIRECT
innodb_numa_interleave=1
innodb_flush_neighbors=0
log_bin
server_id=1
binlog_expire_logs_seconds=600
log_output=file
slow_query_log=ON
long_query_time=0
log_slow_rate_limit=1
log_slow_rate_type=query
log_slow_verbosity=full
log_slow_admin_statements=ON
log_slow_slave_statements=ON
slow_query_log_always_write_time=1
slow_query_log_use_global_control=all
innodb_monitor_enable=all
userstat=1
EOF
fi

service mysql restart 

#Install PMM-Client 

percona-release enable original
apt-get update
apt-get install pmm2-client

# Configure Add MySQL to PMM 


pmm-admin config --force --server-insecure-tls --server-url=https://admin:admin@$PMMSERVER --node-model=linode$LINODE_RAM --region=datacenter$LINODE_DATACENTERID --az=myaz    

mysql -e "create user pmm@localhost identified by \"pmm\""
mysql -e "grant all on *.* to pmm@localhost"


pmm-admin add mysql --use-slowlog --username=pmm --password=pmm --environment=mytest

# Set up poor man's log rotate
# BUG  - Log rotate should be built in 
# cat > /etc/cron.d/pmm-rotate  << EOF
# */11 * * * *  root  rm /var/lib/mysql/*-slow.log;  mysql -e "flush slow logs"
# EOF

# Create Users 

for i in `seq 1 5`;
do
  mysql -e "create user app$i@'%' identified by  'passwd$i'"
  mysql -e "grant select,insert,update,delete on *.* to app$i@'%'"
done



#Create Databases 

for i in `seq 1 5`;
do
  echo Preparing  $i
  mysqladmin create tpcc$i
  /usr/share/sysbench/tpcc.lua  --rate=1 --db-driver=mysql --mysql-user=root --mysql-db=tpcc$i --percentile=99 --time=0 --threads=1 --report-interval=10 --tables=1 --scale=1 --enable_purge=yes --use_fk=0 prepare   
done

mysqladmin create sbtest
/usr/share/sysbench/oltp_point_select.lua  --rate=0 --db-driver=mysql --mysql-user=root --mysql-db=sbtest --percentile=99 --time=0 --threads=1 --report-interval=10 --tables=1 --table_size=1000000 --rand-type=uniform prepare


if [ "$HOSTNAME" == "mysql4" ]; then
 echo "Dropping Indexes on TPCC1 at  $HOSTNAME"
 mysql -u root -e "alter table tpcc1.customer1 drop index idx_customer1"
 mysql -u root -e "alter table tpcc1.orders1 drop index idx_orders1"
 mysql -u root -e "alter table tpcc1.item1 drop primary key"

fi

# Install Periodic Database Rebuilder so long term runs are interesting 
cat > /root/rebuilddb.sh  << EOF
mysql -u root -e "set global read_only=1"
sleep 10

mysql -u root -e "drop database sbtest"


for i in \`seq 1 5\`;
do
  echo Preparing  \$i
  mysql -u root -e "drop database tpcc\$i"
  mysqladmin create tpcc\$i
  /usr/share/sysbench/tpcc.lua  --rate=1 --db-driver=mysql --mysql-user=root --mysql-db=tpcc\$i --percentile=99 --time=0 --threads=1  --report-interval=10 --tables=1 --scale=1 --enable_purge=yes --use_fk=0 prepare
done

mysqladmin create sbtest
/usr/share/sysbench/oltp_point_select.lua  --rate=0 --db-driver=mysql --mysql-user=root --mysql-db=sbtest --percentile=99 --time=0 --threads=1 --report-interval=10 --tables=1 --table_size=1000000 --rand-type=uniform prepare


if [ "\$HOSTNAME" == "mysql4" ]; then
 echo "Dropping Indexes on TPCC1 at  \$HOSTNAME"
 mysql -u root -e "alter table tpcc1.customer1 drop index idx_customer1"
 mysql -u root -e "alter table tpcc1.orders1 drop index idx_orders1"
 mysql -u root -e "alter table tpcc1.item1 drop primary key"

fi

sleep 10
mysql -u root -e "set global read_only=0"
EOF

cat > /etc/cron.d/db-rebuild  << EOF
35 20 * * *  root  bash /root/rebuilddb.sh >> /root/rebuilddb.log
EOF



# On MySQL3 We Start Stress

if [ "$HOSTNAME" == "mysql3" ]; then
 echo "Stressing $HOSTNAME"
 apt-get install stress
 stress   -c 1 -i 1 -m 1 --vm-bytes 128MB  &
fi

echo "Available Variables:  LINODE_ID: $LINODE_ID,  LINODE_LISHUSERNAME: $LINODE_LISHUSERNAME,  LINODE_RAM: $LINODE_RAM,  LINODE_DATACENTERID:$LINODE_DATACENTERID"



#pmm-admin annotate "$HOSTNAME monitoring started"