Devops Engineer

1. Use gbk code, gbk connect, data insert;  gbk/utf8 search result normal; 2. Use gbk code, utf8 connect, data insert;  gbk/utf8 search result error; 3. Use utf8 code, gbk connect, data insert;  gbk/utf8 search result error; 4. Use utf8 code, utf8 connect, data insert;  gbk/utf8 search result normal; Check character_set: show variables like '%char%'; Check table character_set: show create table <table name> \G; Change session character_set: set name <character_set>; database contains utf8 table and gbk table, use binary backup character_set change using logic backup and recover; database upgrade using logic backup and recover; mysql logic backup: mysqldump Character_set recommendation: using utf8

Security Manager 1. empty password or weak password: use random password instead; 2. text password: union management 3. privilege distribution: min privilege distribution 4. account management: use different privilege for different account 5. history operation: delete the operation history: cat /dev/null > ~/.mysql_history Mandatory Access Control (MAC): mysql system control the access Discretionary Access Control (DAC): client use username/password to login database Install MAC 1. Create system table: define the table privilege of user; 2. Modify the user authentication logic (sql_acl.cc) Security Audit Audit user operation; Audit operation context Check user privileges mysql>show grants for <username>@'127.0.0.1';

Mysql Tuning Methods 1. Parameter Tuning 2. System Tuning 3. SQL Tuning Parameter Tuning Innodb structure 1. Caching: Buffer Pool Pool 2. Transaction System: redo log buffer / redo log file 3. Storage: file per table / ibdata parameter 1: Innodb_buffer_pool_size 1. single instance:  Innodb_buffer_pool_size= 75% system memory 2. instance requirement: try to increase the memory hit ratio > 90% 3. instance standardlization parameter 2: innodb_buffer_pool_instances divide innodb buffer pool into several instances If the system memory size is big, use it.  parameter 3: Innodb_log_file_size redo log file size: more bigger more better to reduce I/O operation;  After crash, the recover time is much longer.  But normally mysql is master - slave structure. If master crash, system will connect to slave one.  parameter 4: Innodb_log_buffer_size redo log write into buffer; when buffer full or transaction finish, write int...

OLTP Database Test Model: TPC-C (Transaction Processing Performance Council) Example Testing Tool 1. fio A. Installation #tar xvf fio-2.1.10 #cd fio-2.1.10 #yum install libaio libaio-devel #./configure --prefix=/usr/local/fio #make B. Test Cases Disk IO    /dev/sdb1  Test: disk size 200GB, block size 16k, using 10 process and running 10 seconds. #./fio -filename=/dev/sdb1 -direct=1 -iodepth 1 -rw=read -ioengine=psync -bs=16k -size=200G -numjobs=10 -runtime=10 -group_reporting -name=mytest --output-format=json #./fio -filename=/dev/sdb1 -direct=1 -iodepth 1 -rw=read -ioengine=psync -bs=16k -size=200G -numjobs=10 -runtime=10 -group_reporting -name=mytest 2. tpcc-mysql A. Installation #unzip tpcc-mysql.zip #cd tpcc-mysql/src #export C_INCLUDE_PATH=<MYSQL Installation path>/include #export PATH=<MYSQL Installation path>/bin:$PATH #make B. Test preparation #cd tpcc-mysql #mysql -uro...

Installation require package: gcc/g++, cmake, bison, ncurses-devel Function require package: zlib, libxml, openssl, dtrace yum install gcc gcc-c++ cmake bison ncurses-devel zlib libxml openssl dtrace -y Install 5.5.37 #groupadd mysql #useradd -g mysql mysql #cd  #CFLAGS="-O3 -g -fno-exceptions -static-libgcc -fno-omit-frame-pointer -fno-strict-aliasing" #CXX=g++ #CXXFLAGS="-O3 -g -fno-exceptions -fno-rtti -static-libgcc -fno-omit-frame-pointer -fno-strict-aliasing" #export CFLAGS CXX CXXFLAGS #cmake .                                                  \   -DSYSCONFDIR:PATH=/usr/local/mysql                           \   -DCMAKE_INSTALL_PREFIX:PATH=/usr/local/mysql                  \   -DCMAKE_BUILD_TYPE:ST...

Business Request Analysis 1. response time (RT) 2. data amount 3. request number per second 4. read / write ratio 5. important level Request Transfer 1. TPS 2. IOPS 3. CPU 4. Memory 5. IO Example Business Requestments  1. Response Time: ms level 2. Data amount: 1T/year 3. Request number per second: 1w requests 4. Read / Write ratio: 4:1 5. Important level: P1 (if crash, client will lost money) 6. Others: only need recent 15days, data record is 1KB Step1: 1T/year --> 34KB/s Step2: 1w requests per second and Read / Write ratio 4:1 Read per second = 8000/s Write per second = 2000/s data record is 1KB -->  1 second: 34 insert, 1966 update / delete Step3: mysql page size 16KB  Write 16KB * 2000/s = 32MB/s  Read 16KB * 8000/s = 128MB/s plus 30% Disk bandwidth write 50MB/s  read 150MB/s  Step 4: only need recent 15 days' data 1T * 1024 / 365 * 15 = 42GB Step 5: response time ms lev...