PL19DB

by perconalab
52 deployments · 52 still active · last rev. 1 month 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_size=128M
innodb_buffer_pool_instances=1
innodb_log_file_size=16M
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=512M
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 

sudo percona-release disable all
sudo percona-release enable original experimental
apt-get update
apt-get install pmm2-client

# Configure Add MySQL to PMM 
# BUG -  Should use pmm-agent and avoid machine-id workaround

pmm-agent setup --force --server-insecure-tls --server-address=$PMMSERVER:443  --machine-id=$HOSTNAME

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

# Looks like we have a bug and we need to wait a bit 
# BUG - We should not require here - scripting with add directly after config must work
sleep 5

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

# 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 all 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


# 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"