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
Comments
Post a Comment
You are always welcome to comment here, but your remarks should be relevant to the conversation. To keep the exchanges focused and engaging, we reserve the right to remove off-topic comments, or self-promoting URLs and vacuous messages.
We will try to reply to your queries as soon as time allows.
Regards,
Admin