MYSQL

Data Lake

设计目标 存取(入库和分析)高效 节省存储空间 评估单台设备基于采集评率的每年存储成本 http://mysql.rjweb.org/doc.php/datawarehouse

Spring Oauth2

http://localhost:8080/oauth/token curl -u eagleeye:thisissecret -i -H ‘Accept:application/json’ -d “grant_type=password&scope=webclient&username=will&password=pass” -H “Content-Type: application/x-www-form-urlencoded” -X POST http://localhost:8080/oauth/token access protected resource

Mysql Tuning on Many Tables

mysqladmin status MySQL is multithreaded, so there may be many clients issuing queries for a given table simultaneously. To minimize the problem with multiple client sessions having different states on the same table, the table is opened independently by each concurrent session. This uses additional memory but normally increases performance The table_open_cache and max_connections system variables affect the maximum number of files the server keeps open. If you increase one or both of these values, you may run up against a limit imposed by your operating system on the per-process number of open file descriptors.

Mysql Tuning on Query

innodb-memcached-multiple-get-range-query native partitioning in-place APIs

Redis Install Ubuntu

install curl -O http://download.redis.io/redis-stable.tar.gz tar xzvf redis-stable.tar.gz cd redis-stable make make test sudo make install config sudo mkdir /etc/redis sudo cp redis-stable/redis.conf /etc/redis sudo adduser --system --group --no-create-home redis sudo mkdir /var/lib/redis sudo chown redis:redis /var/lib/redis sudo chmod 770 /var/lib/redis /etc/redis/redis.conf supervised systemd dir /var/lib/redis # bind localhost start redis-server /etc/redis/redis.conf shutdown redis-cli shutdown redis-cli 127.0.0.1:6379> shutdown run in docker docker run --name redis -network host -v /var/lib/redis:/data /etc/redis/redis.

Mysql Slave

[mysqld] server-id = 2 relay-log-index = slave-relay-bin.index relay-log = slave-relay-bin mysql>CHANGE MASTER TO MASTER_HOST = 'db2',MASTER_PORT = 3306, MASTER_USER = 'repl_user', MASTER_PASSWORD = 'xyzzy'; Connecting the Master mysql> START SLAVE;

Mysql Tuning on OS

Server and Operating System Kernel – vm.swappiness Disables swapping completely while 1 causes the kernel to perform the minimum amount of swapping # Set the swappiness value as root echo 1 > /proc/sys/vm/swappiness # Alternatively, using sysctl sysctl -w vm.swappiness=1 # Verify the change cat /proc/sys/vm/swappiness 1 # Alternatively, using sysctl sysctl vm.swappiness vm.swappiness = 1 Filesystems – XFS/ext4/ZFS FILE SIZE mount option EXT4 16TB noatime,data=writeback,barrier=0,nobh,errors=remount-ro XFS 8EiB defaults,nobarrier Disk Subsystem – I/O scheduler Most modern Linux distributions come with noop or deadline I/O schedulers by default, both providing better performance than the cfq and anticipatory ones

Mysql 5.7 InnoDB

index buffer Depends on Storage Engine MyISAM (Caches Index Pages From .MYI files) SELECT FLOOR(SUM(index_length)/POWER(1024,2)) IndexSizesMB FROM information_schema.tables WHERE engine='MyISAM' AND table_schema NOT IN ('information_schema','performance_schema','mysql'); Subtract that from key_buffer_size. InnoDB (Caches Data and Index Pages) SELECT FLOOR(SUM(data_length+index_length)/POWER(1024,2)) InnoDBSizeMB FROM information_schema.tables WHERE engine='InnoDB'; Subtract that from innodb_buffer_pool_size. convert all tables from InnoDB into MyISAM SET @DATABASE_NAME = 'guowang'; SELECT CONCAT('ALTER TABLE `', table_name, '` ENGINE=MyISAM;') AS sql_statements FROM information_schema.

Mysql5.7

mysql install ubuntu 16.04 install mysql 5.7 at default sudo apt-get update sudo apt-get install mysql-server Enable root remote connection mysql -u root -p mysql>GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY '<password>' WITH GRANT OPTION; mysql>FLUSH PRIVILEGES; SHOW current setting mysql> SHOW VARIABLES WHERE Variable_name LIKE 'innodb%'; MySQL 5.7 has significantly better default values. the following variables are set by default: [mysqld] innodb_buffer_pool_instances=8 innodb_flush_method=O_DIRECT setting of mysql 5.

Mysql Partition

whether MySQL Server supports partitioning mysql -u root -p123456 -e "SHOW PLUGINS;" |grep partition

Mysql 5.7 SQL MODE

Disable ONLY_FULL_GROUP_BY SHOW VARIABLES WHERE Variable_name LIKE 'sql_mode'; +---------------+-------------------------------------------------------------------------------------------------------------------------------------------+ | Variable_name | Value | +---------------+-------------------------------------------------------------------------------------------------------------------------------------------+ | sql_mode | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION | [mysqld] sql_mode = "STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION" mysql> SHOW CREATE TABLE <tablename>;

Mysql Master

[mysqld] log-bin = master-bin log-bin-index = master-bin.index server-id = 1 Grant the user to retrieve the binary log from the master mysql>CREATE USER repl_user; GRANT REPLICATION SLAVE ON *.* TO repl_user IDENTIFIED BY 'xyzzy';

Mysql 5.7 MyISAM

key_buffer_size the size of the index buffers held in memory, which affects the speed of index reads recommend: 25% or more of the available server memory A good way to determine whether to adjust the value is to compare the key_read_requests value, which is the total value of requests to read an index, and the key_reads values, the total number of requests that had to be read from disk.