linux->MySQL installation and performance test

-- Download directory
cd /.
mkdir study
cd study

-- download MySQL7 Version of
-- download MySQL8 Version of

-- decompression
tar -zvxf mysql-5.7.26-linux-glibc2.12-x86_64.tar.gz

-- /usr/local Create folder under directory
mkdir /usr/local/msyql
-- move
mv mysql-5.7.26-linux-glibc2.12-x86_64/* /usr/lcoal/mysql

-- add to mysql Group and mysql User:
-- add to mysql Group:
groupadd mysql

-- add to mysql User:
useradd -r -g mysql mysql

-- to configure MySQL
cd /usr/local/mysql

-- Master: slave 5.7.18 Initially not provided in binary package my-default.cnf file
-- get into MySQL of bin catalogue
-- see MySQL For the search path of the configuration file, the left first search
/bin/mysql --help | grep 'Default options' -A 1

-- create profile
touch /usr/local/mysql/etc/my.cnf

Add content

-- Set startup
-- Copy startup script to resource directory
cp ./support-files/mysql.server /etc/rc.d/init.d/mysql
-- increase mysqld Service control script execution permission
chmod +x /etc/rc.d/init.d/mysql
-- take mysqld Add service to system service
chkconfig --add mysql
-- inspect mysqld Is the service effective
chkconfig --list mysql

-- start-up MySQL
service mysql start

-- Environment variable configuration
vim /etc/profile

Add the following, about 52 lines

PATH = $PATH:/usr/local/mysql/bin
-- Refresh
source /etc/profile
-- testing
echo $PATH

-- Modify login password

mysql> usr mysql;
mysql> update user set authentication_string=PASSWORD('Your password') where User='root';
mysql> alter user 'root'@'localhost' identified by 'root';
mysql> flush privileges;
mysql> quit;

-- modify/usr/local/mysql/etc/my.cnf file
-- notes skip-grant-tables
-- restart MySQL
service mysql restart

-- test
mysql -u root -p

-- reconfigure remote access
-- Firewall port setting for remote access
firewall-cmd --zone=public --add-port=3306/tcp --permanent
firewall-cmd --reload
-- View port
firewall-cmd --list-ports

Start firewall service: systemctl ummask firewalld
 Start firewall: systemctl start firewalld

-- get into MySQL
mysql> grant all privileges  on *.* to root@'%' identified by "password";
mysql> flush privileges;

-- If it doesn't work, you can try restarting it MySQL

2. Factors affecting mysql performance

  1. System (money) CPU memory

  2. I/O,mysql process status, mysql server cycle change = > mysql version and computer processing

  3. business
    Database design = > six paradigms (three paradigms) = > join

    From the perspective of performance: not the higher the paradigm, the better. Select some fields appropriately and redundantly

    sql statement writing and index building in the project

    Improper business analysis and expected design performance of the project

    Forum article statistics = > total number of articles updated in real time

    select count(*) from forum articles; 100w

    Massive 2000w query time 12s

    Materialized view

    Analysis - > business


    Demand change = > function

    Business code

mysql optimization - > ideas

Database - design structure of data table - > sql statement optimization - > database parameter configuration (transaction log) - > source (source code perspective c + +) (expansion) - > Hardware (recharge)

Performance Optimization: tacky and stingy; Make rational use of available resources

3. How to think about performance (fishbone diagram - thinking method)

4. linux common methods and tools for analyzing system performance

ps,top,vmstat,sar,free command = > understand parameters

5. mysql performance test

show status

show processlist

show variables

mysql slap is a benchmark tool of mysql. It has the advantages of simple syntax, flexibility and ease of use The tool can simulate multiple clients sending query updates to the server at the same time, give the performance test data, and provide the performance comparison of multiple engines msqlslap provides an intuitive verification basis before and after mysql performance optimization. It is suggested that system operation and maintenance and DBA personnel should master some common stress testing tools in order to accurately grasp the user flow limit and pressure resistance supported by online database.

mysqlslap -h -u root -p -c 100 -i 1 –create-schema tipaskx -q "select count(*) from ask_users" -e innodb –number-of-queries=5000

6. Tools for third-party testing

ab official website

The ab command has low requirements for the computer that issues the load. It will not occupy a lot of CPU or memory, but it will cause a huge load on the target server. Therefore, it is a necessary medicine for some DDOS attacks, suitable for all ages. Be careful when using it yourself. Otherwise, too much load will cause the target server to crash directly due to memory consumption, and have to be hard restarted. The gain is not worth the loss.

JMeter is a java application that can load other applications and test their performance. Although it is designed to test Web applications, it can also be used to test other applications, such as FTP server or database query test through JDBC.

JMeter is much more complicated than ab. For example, it can more flexibly simulate the access of real users by controlling parameters such as preheating time. JMeter has a drawing interface. It can also record the test and replay the test results offline.

Note that you need to download the jdbc jar package of java

Keywords: MySQL

Added by amax on Thu, 03 Feb 2022 05:36:45 +0200