How to Optimized MySQL Percona XtraDB Cluster 5.7 Performance for 1GB RAM VPS - Part 3

SQL database optimization is the extensive strategy of reducing the response time for queries in the system. Note, the database you use for your business offers you all the critical data stored in a related and hierarchical structure. This permits you to get the content and organize it easily.

Database tuning, or performance tuning, involves optimizing and homogenizing the design of database files and of the database’s environment.  This can make data access easier than you imagined. 

I am using three(03) node where server RAM is 1GB each. To optimize MySQL Percona node which is installed on 1GB RAM VPS you need to add  this configs in  /etc/mysql/my.cnf under [mysqld] :

# vi /etc/mysql/my.cnf

[mysqld]

bind-address = 10.10.3.230 ( Use DB Node IP for another Node )

collation-server = utf8mb4_unicode_ci
init-connect='SET NAMES utf8mb4'
character-set-server = utf8mb4

skip-name-resolve
skip_external_locking

tmp_table_size = 128M
join_buffer_size = 128M

innodb_doublewrite = off

innodb_flush_log_at_timeout = 3
innodb_read_io_threads = 32
innodb_write_io_threads = 16

max_allowed_packet = 256M
max_connections=1000
max_user_connections=500

!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/percona-xtradb-cluster.conf.d/
 

Follow the above steps on another two nodes. Don't forget add SWAP memory. Here we already added SWAP memory in our previous tutorial. 

Install some another packages (Optional)

sudo apt install apt-transport-https ca-certificates curl software-properties-common -y

Optional: 

To avoid the software from being upgrade from distribution repositories we will pin the package, to do so we need to create a new file /etc/apt/preferences.d/00percona.pref and save the following content in it:

Package: *
Pin: release o=Percona Development Team
Pin-Priority: 1001 

Important Command: 

/etc/init.d/mysql bootstrap-pxc
/etc/init.d/mysql start | stop | restart | status

tail -f /var/log/mysqld.log
tail -f /var/log/syslog

cat /var/lib/mysql/grastate.dat
ps -aux | grep mysql 

Reference:

Linux OS Tuning for MySQL Database Performance

MySQL 5.7 Performance Tuning After Installation

Best Practices for Configuring Optimal MySQL Memory Usage

Choosing innodb_buffer_pool_size