Shulou(Shulou.com)06/01 Report--

1.1. Download the installation package

Official address: www.mysql.com

Download version: Mysql Community Server, platform Linux-Generic x86-64bit.

Download the installation package mysql 5.6mysql5.7

Download and verify the installation package through md5: md5sum filename

2018.11.3 the latest version of the official website:

Mysql 5.7.24

Mysql 5.6.42

Differences in version installation:

Prior to mysql 5.7, the / usr/local/mysql/scripts/mysql_install_db command was needed during database initialization, but mysql_install_db was abandoned after mysql 5.7.

1.2. Pre-installation system environment detection

Since there is no operating system platform in the book, centos7.4_x64 is chosen here.

[root@localhost ~] # cat / etc/centos-release

CentOS Linux release 7.4.1708 (Core)

The ip address is set to 192.0.2.10

1.2.1. Uninstall MariaDB from the original system

CentOS7 installs MariaDB instead of MySQL by default, and MySQL-related packages have been removed from the yum server. Because MariaDB and MySQL may conflict, uninstall MariaDB first.

Remember to uninstall first, or there will be package conflicts.

Need to uninstall the mariadb-lib that comes with the system

# rpm-qa | grep-I mariadb

Mariadb-5.5.56-2.el7.x86_64

Mariadb-server-5.5.56-2.el7.x86_64

Mariadb-libs-5.5.56-2.el7.x86_64

[root@localhost] # rpm-e-nodeps mariadb

[root@localhost] # rpm-e-nodeps mariadb-server

[root@localhost] # rpm-e-nodeps mariadb-libs

1.2.2. Close selinux, iptables, firewall

Close selinux

/ etc/sysconfig/selinux

Close iptables

Chkconfig-- list | grep iptables

Chkconfig iptables off

Turn off the firewall

The firewall in Centos7 has become firewall, so never use iptable to turn it off again.

1 View firewall status

# firewall-cmd-state

Running

2 is really running, so I want to disabled firewall service.

# systemctl mask firewalld

Ln-s'/ dev/null''/ etc/systemd/system/firewalld.service'

3 and then turn off the firewall

# systemctl stop firewalld

4 finally check the firewall service and find that it has been turned off

# firewall-cmd-state

Not running

1.2.3. Modification of IO scheduling system mode

Set the IO scheduling system to deadline mode

View by this system

[root@localhost ~] # cat / sys/block/sda/queue/scheduler

Noop [deadline] cfq

The method of changing IO system scheduling is not tested this time, depending on the system.

About IO scheduling

Specific reference: http://blog.itpub.net/29519108/viewspace-2218623/

Summary:

1. The focus of CFQ and DEADLINE consideration is on satisfying sporadic IO requests. Sequential IO requests, such as sequential reads, are not optimized. In order to meet the mixed scenarios of random IO and sequential IO, Linux also supports ANTICIPATORY scheduling algorithm. On the basis of DEADLINE, ANTICIPATORY sets the wait time window of 6ms for each read IO. If the OS receives a read IO request from an adjacent location in this 6ms, it can be satisfied immediately.

The choice of IO scheduler algorithm depends not only on the hardware characteristics, but also on the application scenario.

On the traditional SAS disk, CFQ, DEADLINE, ANTICIPATORY are all good choices; for the dedicated database server, DEADLINE has good throughput and response time.

However, on emerging SSDs such as SSD and Fusion IO, the simplest NOOP may be the best algorithm, because the optimization of the other three algorithms is based on shortening seek time, while SSDs have no so-called seek time and IO response time is very short.

2. For database applications, Anticipatory Scheduler's performance is the worst. Deadline performs a little better than cfq in a DSS environment, while cfq performs better overall. No wonder RHEL's default IO scheduler is set to cfq.

1.2.4. Swap Partition Settings

The swapiness value is between 0 and 100, with 0 for maximum anger using physical memory, and then using SWAP partitions. Actively use the swap partition to move the in-memory database to the swap partition in time.

View this system

[root@localhost ~] # cat / proc/sys/vm/swappiness

thirty

To change it, edit / etc/sysctl.conf and add the value of vm.swappiness.

1.2.5. File system

It is recommended to use the XFS file system, which is more convenient to manage, support dynamic expansion and delete files than ext4.

Specific reference: http://blog.itpub.net/29519108/viewspace-2218624/

1.2.6. Operating system restriction

Unlimit-a view parameters

[root@localhost] # ulimit-a

Core file size (blocks,-c) 0

Data seg size (kbytes,-d) unlimited

Scheduling priority (- e) 0

File size (blocks,-f) unlimited

Pending signals (- I) 7165

Max locked memory (kbytes,-l) 64

Max memory size (kbytes,-m) unlimited

Open files (- n) 1024

Pipe size (512 bytes,-p) 8

POSIX message queues (bytes,-Q) 819200

Real-time priority (- r) 0

Stack size (kbytes,-s) 8192

Cpu time (seconds,-t) unlimited

Max user processes (- u) 7165

Virtual memory (kbytes,-v) unlimited

File locks (- x) unlimited

Open_files defaults to 1024, which means that a single process can access up to 1024 file handles. If there are too many links or tables in the system, it will not open or cannot be opened within the scope.

Max user processes defaults to 7185, the number of connections.

Modify / etc/security/limits.conf, add the relevant content of the restriction, and restart the system.

* hard nproc 65535

* soft nproc 65535

* soft nofile 65535

* hard nofile 65535

1.2.7. Numa needs to be shut down

Specific reference: http://blog.itpub.net/29519108/viewspace-2218625/

Turn off the numa function, you can better allocate memory, do not need to use a seap way to obtain memory.

The shutdown mode is either in BIOS, in the operating system, or in the database startup process.

Numa-interleave=all / usr/loal/mysql/bin/mysqld_safe-defaults-file=/etc/my.cnf &

Non-uniform storage access structure (NUMA: Non-Uniform Memory Access) is also the latest memory management technology. It corresponds to the symmetric multiprocessor architecture (SMP: Symmetric Multi-Processor).

For a stand-alone instance of MySQL, it is recommended to disable NUMA. There are three ways to close it:

1. Hardware layer, setting shutdown in BIOS

2.OS kernel, setting numa=off at startup

3. You can change the memory allocation policy to interleave with the numactl command.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.