Nguyen Tan Vy v0112358

Note: You may wish to convert MyISAM tables to InnoDB tables before you proceed.

innodb_file_per_table is by default ON Mysql 5.6.6 and onwards. There is plenty of stuff on Google about pros & cons ofinnodb_file_per_table.

This post details how to enable innodb_file_per_table on an existing database. Because innodb_file_per_table affects new tables only, created after innodb_file_per_table is enabled, we need to recreate old databases to force innodb_file_per_table on old tables and reclaim some disk space.

Server info

Percona Server 5.5.
innodb_file_per_table was not enable.
innodb_data_file_path = ibdata1:12G:autoextend
Size of ibdata1 is 25GB. It's too big and we can lose our data when the server crash.

	- Topology: Client ==> Reverse proxy (Nginx is listened at port 443) ======> Backend (Apache is listened at port 80).

	- Nginx configure
	proxy_set_header X-Forwarded-Host $host;
	proxy_set_header X-Forwarded-Server $host;
	proxy_set_header X-Forwarded-Proto $scheme;
	proxy_set_header X-Forwarded-For $proxy_add_x_forwarded_for;

	- Wordpress configure:
	cat wp-config.php

	////// Example: connect to Redis and store an array
	<?php
	$redis = new Redis;
	$connect = $redis->connect( "172.18.0.1", "6379" );
	$redis->setOption(Redis::OPT_SERIALIZER, Redis::SERIALIZER_PHP);
	$array = array(
	'message' => "My message",
	'to_send' => 2,
	'from_send' => 1,
	'create_time' => date('Y-m-d H:i:s')

	###### Result on physical server DDR3-1066 ######################
	[root@cp redis-4.0.11]# redis-benchmark -h 172.18.0.1 -d 1024 -n 10000 -r 10000 -c 100 -q
	PING_INLINE: 35842.29 requests per second
	PING_BULK: 39370.08 requests per second
	SET: 39215.69 requests per second
	GET: 38022.81 requests per second
	INCR: 39840.64 requests per second
	LPUSH: 40160.64 requests per second
	RPUSH: 39840.64 requests per second
	LPOP: 38910.51 requests per second

	#!/usr/bin/env bash
	# Use this script to test if a given TCP host/port are available

	cmdname=$(basename $0)

	echoerr() { if [[ $QUIET -ne 1 ]]; then echo "$@" 1>&2; fi }

	usage()
	{
	cat << USAGE >&2

	##### Hardware info
	- CPU: 08 core 2.3GHz
	- RAM: 32GB
	- HDD: 500GB HDD

	##### Raw data from https://github.com/Percona-Lab/ontime-airline-performance/blob/master/download.sh

	##### Data on disk
	- CSV raw data: 21GB
	- Innodb (no compression/original): 7.2GB.

	EC2 Reachability Test => http://ec2-reachability.amazonaws.com/

	AWS Inter-Region Latency => https://www.cloudping.co/

	wget https://cmake.org/files/v3.11/cmake-3.11.0.tar.gz
	tar zxvf cmake-3.11.0.tar.gz && cd cmake-3.11.0
	export LD_LIBRARY_PATH=/usr/local/lib:/usr/lib:/usr/local/lib64:/usr/lib64
	./bootstrap
	make -j$(nproc)
	make install
	cmake --version

	wget https://ftp.gnu.org/gnu/gcc/gcc-5.5.0/gcc-5.5.0.tar.bz2
	tar xjvf gcc-5.4.0.tar.bz2
	yum install -y gmp-devel mpfr-devel libmpc-devel
	mkdir gcc-5.5.0-build
	cd gcc-5.5.0-build
	../gcc-5.5.0/configure --enable-languages=c,c++ --disable-multilib
	make -j$(nproc) && make install

	wget https://dl.bintray.com/boostorg/release/1.68.0/source/boost_1_68_0.tar.gz
	tar xzvf boost_1_68_0.tar.gz
	cd boost_1_68_0
	./bootstrap.sh --prefix=/usr/local/
	export CPLUS_INCLUDE_PATH="$CPLUS_INCLUDE_PATH:/usr/include/python3.6m"
	./b2 -j$(nproc) -d0 install --with=all