大樹kingmax的隨手筆記

使用 Azure PowerShell 修改 AD User 密碼不過期

shtzeng Post in Research, 系統設定，Tags: ad user, azure, microsoft, PasswordNeverExpires, powershell, suck

11 10 月 2016

放一個國慶日，一早來就被 Azure 炸到
我們家的 jenkins 怎麼推不動 ansible 了

查了一陣子發現 ansible 用的 azure ad 帳號密碼過期需要重設
由於這是 api 用的帳號密碼
過於頻繁重設會讓管理者比較麻煩(要改很多job設定)
所以就去搜尋了一番
結論都是….要用 Azure PowerShell 去改設定
他媽的又是 Portal 又是 CLI 又是 PowerShell 的
三國鼎立不會很煩嗎
suck suck suck

還是要來說一下使用流程
第一是 windows 環境
然後下載 PowerShell 安裝檔安裝
教學網頁在這裡
 下載連結在這裡
這是一個漫長的等待流程
再來下在 AD Moudle for PowerShell 安裝
教學網頁在這裡
 下載連結在這裡
裝完後打開 AD Module 建立的捷徑
輸入 Connect-MsolService 後登入 AD 帳號，通常是 [email protected] 這個
檢查帳號設定 Get-MSOLUser -UserPrincipalName [email protected] | Select PasswordNeverExpires ，False 就是三個月密碼會過期，要換密碼喔～
設定密碼不過期 Set-MsolUser -UserPrincipalName [email protected] -PasswordNeverExpires $true
如果跟你說你沒權限，請登入 Classic 的網頁，將該使用者權限調大一點
教學網頁在這裡

整個做完之後還是覺得
suck suck suck

Azure 設定 reverse DNS

shtzeng Post in Research，Tags: azure, azure cli, azure login, azure reverse dns, reverse dns

29 9 月 2016

查了網路上的文章
其實不是很詳盡
所以自己來寫一份了 orz

PART I – 安裝 Azure Cli

這部份可以參考官網文章
基本上就是跑下面指令就可以了

sudo npm install azure-cli -g

裝完可以打 azure 指令測試看看

shtzeng@jump [~] [08:36:22] $ azure                                                                                                                                                                                                                                        [8/8]
info:             _    _____   _ ___ ___
info:            /_\  |_  / | | | _ \ __|
info:      _ ___/ _ \__/ /| |_| |   / _|___ _ _
info:    (___  /_/ \_\/___|\___/|_|_\___| _____)
info:       (_______ _ _)         _ ______ _)_ _
info:              (______________ _ )   (___ _ _)
info:
info:    Microsoft Azure: Microsoft's Cloud Platform
info:
info:    Tool version 0.10.5
help:
help:    Display help for a given command
help:      help [options] [command]
help:
help:    Log in to an Azure subscription using Active Directory or a Microsoft account identity.
help:      login [options]
help:
help:    Log out from Azure subscription using Active Directory. Currently, the user can log out only via Microsoft organizational account
help:      logout [options] [username]
help:
help:    Open the portal in a browser
help:      portal [options]
help:
help:    Manages the data collection preference.
help:      telemetry [options]
help:
help:    Commands:
help:      account          Commands to manage your account information and publish settings
help:      acs              Commands to manage your container service.
help:      ad               Commands to display Active Directory objects
help:      appserviceplan   Commands to manage your Azure appserviceplans
help:      availset         Commands to manage your availability sets.
help:      batch            Commands to manage your Batch objects
help:      cdn              Commands to manage Azure Content Delivery Network (CDN)
help:      config           Commands to manage your local settings
help:      datalake         Commands to manage your Data Lake objects
help:      feature          Commands to manage your features
help:      group            Commands to manage your resource groups
help:      hdinsight        Commands to manage HDInsight clusters and jobs
help:      insights         Commands related to monitoring Insights (events, alert rules, autoscale settings, metrics)
help:      iothub           Commands to manage your Azure IoT hubs
help:      keyvault         Commands to manage key vault instances in the Azure Key Vault service
help:      lab              Commands to manage your DevTest Labs
help:      location         Commands to get the available locations
help:      network          Commands to manage network resources
help:      policy           Commands to manage your policies on ARM Resources.
help:      powerbi          Commands to manage your Azure Power BI Embedded Workspace Collections
help:      provider         Commands to manage resource provider registrations
help:      quotas           Command to view your aggregated Azure quotas
help:      rediscache       Commands to manage your Azure Redis Cache(s)
help:      resource         Commands to manage your resources
help:      role             Commands to manage role definitions
help:      servermanagement Commands to manage Azure Server Managment resources
help:      servicefabric    Commands to manage your Azure Service Fabric
help:      storage          Commands to manage your Storage objects
help:      tag              Commands to manage your resource manager tags
help:      usage            Command to view your aggregated Azure usage data
help:      vm               Commands to manage your virtual machines
help:      vmss             Commands to manage your virtual machine scale sets.
help:      vmssvm           Commands to manage your virtual machine scale set vm.
help:      webapp           Commands to manage your Azure webapps
help:
help:    Options:
help:      -h, --help     output usage information
help:      -v, --version  output the application version
help:
help:    Current Mode: arm (Azure Resource Management)
shtzeng@jump [~] [08:36:26] $

建議啟用 TAB 自動完成

azure --completion >> ~/azure.completion.sh
echo 'source ~/azure.completion.sh' >> ~/.bash_profile

PART II – 登入 Azure

這部份可以參考官網文章
透過輸入 azure login 會得到一組 token，再到 http://aka.ms/devicelogin 輸入 token 後再輸入帳號密碼，然後跳到一頁說明可以關掉這頁面時，azure cli 工具應該就顯示登入成功了。

shtzeng@jump [~] [02:59:02] $ azure login
info:    Executing command login
|info:    To sign in, use a web browser to open the page https://aka.ms/devicelogin. Enter the code XXXX to authenticate.
-info:    Added subscription AAAA
info:    Added subscription BBBB
info:    Setting subscription "AAAA" as default
+
info:    login command OK
shtzeng@jump [~] [03:19:07] $

這邊可以看到我有兩個 subscription，然後預設是 AAAA。
這時候下 azure vm list 會發現空無一物，因為選錯 subscription 了，要自己切換 subscription。

shtzeng@jump [~] [03:31:04] $ azure vm list
info:    Executing command vm list
+ Getting virtual machines
info:    No VMs found
info:    vm list command OK
shtzeng@jump [~] [03:31:45] $ azure account list
info:    Executing command account list
data:    Name                         Id                                    Current  State
data:    ---------------------------  ------------------------------------  -------  -------
data:    AAAA                         xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx  true     Enabled
data:    BBBB                         yyyyyyyy-yyyy-yyyy-yyyy-yyyyyyyyyyyy  false    Enabled
info:    account list command OK
shtzeng@jump [~] [03:42:07] $ azure account set "BBBB"
info:    Executing command account set
info:    Setting subscription to "BBBB" with id "yyyyyyyy-yyyy-yyyy-yyyy-yyyyyyyyyyyy".
info:    Changes saved
info:    account set command OK
shtzeng@jump [~] [03:42:36] $

如此一來就可以查到東西了

shtzeng@jump [~] [03:42:45] $ azure vm list                                                                                                                                                                                                                            [236/826]
info:    Executing command vm list
+ Getting virtual machines
data:    ResourceGroupName  Name                              ProvisioningState  PowerState  Location    Size
data:    -----------------  --------------------------------  -----------------  ----------  ----------  -----------
data:    VA                 nat-virginia                      Succeeded          VM running  eastus2     Standard_A1
data:    NL                 nat-netherlands                   Succeeded          VM running  westeurope  Standard_A1
info:    vm list command OK
shtzeng@jump [~] [03:43:35]

PART III – 設定 reverse DNS

這邊就是其他文章沒寫清楚的地方了
可以參考官網文章
我這邊已經有了 Public IP 了，想為它設定反查，首先要查出來該 IP 的名稱

shtzeng@jump [~] [03:49:35] $ azure network public-ip list HK
info:    Executing command network public-ip list
+ Getting the public ip addresses
data:    Name                              Location  Resource group  Provisioning state  Allocation method  IP version  IP Address      Idle timeout, minutes  FQDN
data:    --------------------------------  --------  --------------  ------------------  -----------------  ----------  --------------  ---------------------  -----------------------------------------
data:    ip-vm-1                           eastasia  HK              Succeeded           Static             IPv4        257.257.257.257   4
info:    network public-ip list command OK
shtzeng@jump [~] [03:49:41] $

有了名稱才可以下指令

shtzeng@jump [~] [04:05:56] $ azure network public-ip set -n ip-vm-1 -g HK -f vm-1.xxx.yyy.zzz
info:    Executing command network public-ip set
+ Looking up the public ip "ip-vm-1"
+ Updating public ip address "ip-vm-1"
data:    Id                              : /subscriptions/yyyyyyyy-yyyy-yyyy-yyyy-yyyyyyyyyyyy/resourceGroups/HK/providers/Microsoft.Network/publicIPAddresses/ip-vm-1
data:    Name                            : ip-vm-1
data:    Type                            : Microsoft.Network/publicIPAddresses
data:    Location                        : eastasia
data:    Provisioning state              : Succeeded
data:    Allocation method               : Static
data:    IP version                      : IPv4
data:    Idle timeout in minutes         : 4
data:    IP Address                      : 257.257.257.257
data:    IP configuration id             : /subscriptions/yyyyyyyy-yyyy-yyyy-yyyy-yyyyyyyyyyyy/resourceGroups/HK/providers/Microsoft.Network/networkInterfaces/ip-vm-1234/ipConfigurations/ipconfig1
data:    Domain name label               : ip-vm-1
data:    FQDN                            : ip-vm-1.eastasia.cloudapp.azure.com
info:    network public-ip set command OK
shtzeng@jump [~] [04:06:49] $

這樣就完成 reverse DNS 設定了
以下是說明
-n 這裡指定哪個 IP 做設定，就是 azure network public-ip list HK 列出來的 Name 部分
-g 指定 resource group，我們是縮寫 HK 代表香港機器們
-f 是指設定這個 IP 的反解為何，這裡設定成 vm-1.xxx.yyy.zzz

MongoDB 建置及調教

shtzeng Post in Research，Tags: mongodb, tuning

31 8 月 2016

人很懶
做過的事情
參考過的資料
沒留下來就會煙飛雲滅 XD

這邊要補的是當初怎麼建的 MongoDB
第一部分是安裝 MongoDB，我是用 Percona 版本的
先把 Percona 的 key 加入

apt-key adv --keyserver keys.gnupg.net --recv-keys 1C4CBDCDCD2EFD2A

再來是加 source list 讓 apt 可以抓的到
在 /etc/apt/sources.list.d/percona.list 加上 (假設我是用 Ubuntu 14.04 LTS)

#
deb http://repo.percona.com/apt trusty main
deb-src http://repo.percona.com/apt trusty main

然後更新並安裝

apt-get update
apt-get install percona-server-mongodb-32

改設定檔，我是用 rocksdb engine

storage:
  dbPath: /var/lib/mongodb
  journal:
    enabled: true
  engine: rocksdb
systemLog:
  destination: file
  logAppend: true
  path: /var/log/mongodb/mongod.log
processManagement:
  fork: true
  pidFilePath: /var/run/mongod.pid
net:
  port: 27017
  bindIp: 0.0.0.0

到這邊就大功告成了， MongoDB 裝好了
安裝的部分可以參考各 blog
再來就是系統調教的部分了
首先要增加 /etc/init/mongo_vm_settings.conf ，讓 THP 預設關掉 (這是官方強烈建議關掉的)

# Ubuntu upstart file at /etc/init/mongod_vm_settings.conf

start on runlevel [2345]
stop on runlevel [!2345]

script
  echo "never" > /sys/kernel/mm/transparent_hugepage/enabled
  echo "never" > /sys/kernel/mm/transparent_hugepage/defrag
end script

再來是 /etc/sysctl.d/20-mongodb.conf，其中 vm.swappiness 建議是為 0 關掉，但因為存在一些 bug ，設定成超小的 1% 比較保險

vm.swappiness = 1
vm.dirty_ratio = 10
vm.dirty_background_ratio = 3
vm.dirty_expire_centisecs = 12000
net.core.somaxconn = 4096
net.ipv4.tcp_fin_timeout = 30
net.ipv4.tcp_keepalive_intvl = 30
net.ipv4.tcp_keepalive_time = 120
net.ipv4.tcp_max_syn_backlog = 4096

然後設定 /etc/security/limits.d/mongod.conf ，方便跑起來的時候不會檔案不夠開之類的

mongod       soft        nproc        64000
mongod       hard        nproc        64000
mongod       soft        nofile       64000
mongod       hard        nofile       64000

最後設定硬碟檔案區塊大小

# set deadline scheduler and 16kb read-ahead for /dev/xvda
ACTION=="add|change", KERNEL=="xvda", ATTR{queue/scheduler}="deadline", ATTR{bdi/read_ahead_kb}="16"

這樣就解決了XD
調教部分可以參考https://www.percona.com/blog/2016/08/12/tuning-linux-for-mongodb/
又把一個事情記完了 XD

Munin Plugin for MongoDB

shtzeng Post in Research，Tags: mongodb, munin, munin plugin

31 8 月 2016

所謂的凡走過必留下痕跡
不留痕跡很難找到當初設定的方法的

簡單來說，執行完下面的指令就加好 plugin 了，我的環境是 Ubuntu 14.04.5 LTS

sudo apt-get install python-pip build-essential python-dev git -y
sudo pip install pymongo
git clone https://github.com/comerford/mongo-munin.git /tmp/mongo-munin
sudo cp /tmp/mongo-munin/mongo_* /usr/share/munin/plugins
sudo ln -sf /usr/share/munin/plugins/mongo_btree /etc/munin/plugins/mongo_btree
sudo ln -sf /usr/share/munin/plugins/mongo_conn /etc/munin/plugins/mongo_conn
sudo ln -sf /usr/share/munin/plugins/mongo_lock /etc/munin/plugins/mongo_lock
sudo ln -sf /usr/share/munin/plugins/mongo_mem /etc/munin/plugins/mongo_mem
sudo ln -sf /usr/share/munin/plugins/mongo_ops /etc/munin/plugins/mongo_ops
sudo ln -sf /usr/share/munin/plugins/mongo_docs /etc/munin/plugins/mongo_docs
sudo chmod +x /usr/share/munin/plugins/mongo_*

來源參考https://github.com/comerford/mongo-munin的README

Modify Json file

shtzeng Post in Research，Tags: deep merge, json, merge

09 8 月 2016

最近在陪著弄 nodejs 的東西
有個任務就是隨著不同環境就 deploy 不同設定
這要是普通的檔案就簡單了
可是 nodejs 是 json 格式的
瞎攪和是不行的

我們有以下原始資料 origin.json

{
  "key_c": {
    "key_c_b": "value_c_b",
    "key_c_a": "value_c_a"
  },
  "key_b": {
    "key_b_a": "value_b_a"
  },
  "key_a": "value_a"
}

然後想要修改的資料 modify.json

{
  "key_d": "new_value_d",
  "key_c": {
    "key_c_c": "add_value_c_b",
    "key_c_a": "modify_value_c_a"
  },
  "key_b": {
    "key_b_b": "add_value_b_a"
  },
  "key_a": "modify_value_a"
}

一開始一直在琢磨 jq 的方法
摸來摸去最多也只做得到

jq -s add modify.json origin.json
{
  "key_d": "new_value_d",
  "key_c": {
    "key_c_b": "value_c_b",
    "key_c_a": "value_c_a"
  },
  "key_b": {
    "key_b_a": "value_b_a"
  },
  "key_a": "value_a"
}

有值的都無法覆蓋
好吧，其實上面的方法是錯的
應該要是指令反過來比較趨近我們想要的

jq -s add origin.json modify.json
{
  "key_d": "new_value_d",
  "key_c": {
    "key_c_c": "add_value_c_b",
    "key_c_a": "modify_value_c_a"
  },
  "key_b": {
    "key_b_b": "add_value_b_a"
  },
  "key_a": "modify_value_a"
}

這也不對啊，原本的值都不見了，而我只想修改某些項目
後來找到了這個方法，安裝 npm 的 json

npm install -g json

之後就可以用 json 來處理這個問題了

cat origin.json modify.json | json --deep-merge
{
  "key_a": "modify_value_a",
  "key_b": {
    "key_b_a": "value_b_a",
    "key_b_b": "add_value_b_a"
  },
  "key_c": {
    "key_c_a": "modify_value_c_a",
    "key_c_b": "value_c_b",
    "key_c_c": "add_value_c_b"
  },
  "key_d": "new_value_d"
}

看起來就比較對了
要修改的修改
要新增的新增

Json 真是博大精深超難搞的
我還是敬而遠之吧 XD

Jenkins Git Submodule Add Tag

shtzeng Post in 系統設定，Tags: add tag, git, jenkins, subnodule

15 6 月 2016

總是會有些需求

而需求就產生了程式碼
畢竟～Jenkins預設的外掛沒有對Submodule做新增標籤的功能
做法如下：

新增一隻 shell script 做以下事情處理 update submodule tag，放在最後確保是成功的狀態再新增標籤

#!/bin/bash

if [ -e ".gitmodules" ]; then
	git submodule foreach git tag -f -a tag_name -m "Autotag from tag_name building"
	git submodule foreach git push origin tag_name
fi

然後呢，這時候就炸了
啊，別人的 repo 怎麼辦？
於是就有下列的 workaround，只搜尋想要加標籤的 submodule 來處理

#!/bin/bash

if [ -e ".gitmodules" ]; then
	SUBMODULE_LISTS=`git config --file .gitmodules --get-regexp url | grep 逼哩八啦 | awk '{print $1}' | sed 's/^submodule\.//g' | sed 's/\.url$//g'`
    
    for SUBMODULE in $SUBMODULE_LISTS
    do
    	SUBMODULE_PATH=`git config --file .gitmodules --get-regexp path | grep $SUBMODULE | awk '{print $2}'`
        cd $SUBMODULE_PATH
        git tag -f -a tag_name -m "Autotag from tag_name building"
        git push origin tag_name
        cd $WORKSPACE
    done
fi

舒服舒服，惡搞的真舒服 Orz

AWS RDS MySQL Threads 太高

shtzeng Post in MySQL，

15 6 月 2016

一直很想搞一搞新東家的DB
最近終於開始有時間了
另一方面是
最近比較了解產品運作
所以去動資料庫就比較舒服

這邊遇到的情況是
只有三台或四台機器會進來DB處理事情
QPS其實也沒到多少，最多來個 100 好了 (可能平常 50 都不到)
可是呢平常 Threads_connected 就差不多是 200 了
看了一下一堆 Sleep 到天荒地老的

MySQL Threads_connected 越高會導致效率越差，不要粘著不放啊
這時候去 parameter group 找到這台機器用的 parameter 後
修改 wait_timeout = 60 大概就是個合理數值了

現在看著 Threads_connected 大概 50 就好舒服….

用 OpenSSL 產生 CSR

shtzeng Post in 系統設定，Tags: openssl; csr; Certificate signing request;

17 5 月 2016

人懶就會有程式的產出 Orz
我只是不想一直打 openssl 的指令
所以就寫了 build_csr.sh 出來了

#!/usr/bin/env bash

if [ $# -lt 1 ]; then
  echo 1>&2 "$0: Please use \"$0 aaa.domain.com\" or \"$0 *.domain.com\" to generate key and csr files."
  exit 2
fi

DOMAIN=${1//\*/star}

openssl req -new -newkey rsa:2048 -nodes -out "$DOMAIN".csr -keyout "$DOMAIN".key -subj "/C=US/ST=California/L=Sunnyvale/O=Yahoo Inc./OU=Information Technology/CN=$1"

其中 subj 部分是公司資訊
偷 Yahoo 的樣式給大家看
請參考這篇 Certificate signing request 維基百科

結果範例

$ ./build_csr.sh aaa.domain.com
Generating a 2048 bit RSA private key
...........................................+++
.....+++
writing new private key to 'aaa.domain.com.key'
-----
$ ./build_csr_y.sh *.domain.com
Generating a 2048 bit RSA private key
..................+++
................................+++
writing new private key to 'star.domain.com.key'
-----
ls
aaa.domain.com.csr      aaa.domain.com.key      build_csr.sh          star.domain.com.csr     star.domain.com.key
$

可以用 openssl req 指令來檢查 CSR 資訊

$ openssl req -in star.domain.com.csr -noout -text
Certificate Request:
    Data:
        Version: 0 (0x0)
        Subject: C=US, ST=California, L=Sunnyvale, O=Yahoo Inc., OU=Information Technology, CN=*.domain.com
        Subject Public Key Info:
            Public Key Algorithm: rsaEncryption
            RSA Public Key: (2048 bit)
                Modulus (2048 bit):
                    00:d0:46:97:78:c9:5f:b3:9a:2e:83:39:ed:f7:22:
                    44:ac:cc:e8:44:8b:9a:61:53:b8:39:46:8a:18:58:
                    79:51:65:c3:3a:68:a0:3e:93:71:d5:71:c8:a9:2f:
                    ba:74:f5:10:29:83:26:fa:50:cd:51:e9:0b:35:4d:
                    54:e0:25:70:96:b9:69:15:dc:12:df:51:be:65:34:
                    cc:de:00:44:7f:06:cc:78:a3:2e:6a:54:8a:49:8c:
                    12:4c:70:9d:8f:42:af:ab:87:c6:2e:4f:de:13:e4:
                    e5:0e:b7:4b:ec:ac:11:11:40:44:31:98:98:61:71:
                    83:52:0f:9e:36:e8:5c:1f:b5:ef:a4:35:fe:c7:7c:
                    2d:04:8b:fe:0d:77:b8:4a:e1:5d:04:70:b1:23:07:
                    26:4e:33:70:c3:ee:08:a7:b8:76:f6:a4:2f:17:a3:
                    b5:09:59:a4:33:2b:8c:87:e8:bb:48:f7:7c:5c:46:
                    3e:36:cb:95:c4:6b:ef:b0:e1:aa:97:b0:3d:b9:17:
                    3f:24:aa:e7:b1:a3:b8:35:26:1e:5d:4c:54:af:72:
                    62:ef:01:68:b3:81:f1:d0:f8:0b:a3:26:1e:04:ff:
                    de:9a:5b:61:33:68:45:00:14:33:20:4d:4e:e9:8c:
                    1e:02:a2:95:ab:b0:25:bb:de:10:c6:a5:37:f9:f2:
                    64:83
                Exponent: 65537 (0x10001)
        Attributes:
            a0:00
    Signature Algorithm: sha1WithRSAEncryption
        42:81:9b:1f:64:8a:07:61:89:2b:0d:9b:30:31:db:e7:62:b6:
        15:af:f1:b8:97:0d:cb:ad:db:76:d4:2d:43:ad:17:3e:dc:31:
        2f:40:08:a3:b3:d4:0d:9e:91:c8:33:d0:24:28:d6:ea:cb:af:
        54:b0:03:6c:d4:1c:e7:d2:7c:9d:93:02:77:79:64:1b:d3:b9:
        46:2d:ab:aa:c1:f7:b6:f7:e8:3f:e0:c7:61:ff:62:65:3f:38:
        8d:54:ea:8a:a4:17:56:e7:ea:20:7d:68:4b:9c:ce:37:b7:b5:
        06:1e:62:90:b7:7f:13:27:33:27:1d:b9:80:29:fb:c6:af:f9:
        cc:80:8c:3c:70:71:c5:07:29:55:51:d8:78:3b:0a:f0:35:5f:
        95:0f:75:d3:e6:5b:a9:5e:a0:81:51:5b:f1:38:a1:64:41:f9:
        a5:49:4d:b0:cc:9f:0a:4f:c4:4d:94:61:d7:e8:e4:e3:b7:04:
        af:07:02:29:52:d2:ce:bc:59:e2:7e:e1:da:60:e7:4a:ad:e7:
        57:05:90:6a:fb:b4:5d:cf:fa:b4:a8:7b:40:06:af:fe:c2:f1:
        00:f6:3e:d5:f6:3c:a0:68:00:24:de:80:84:c3:24:46:e4:4e:
        a3:a1:bf:d0:7e:4b:04:c0:51:77:8c:48:c9:d1:a1:89:41:98:
        d6:2a:58:d3
$

以上只是範例
請勿任意委造他人網域發佈憑證 :p

更新目錄下所有 repos

shtzeng Post in 系統設定，

16 5 月 2016

總是會有一個需求
每次要用某個 repository 的時候就要先更新一次
避免 merge 或 conflict 的發生
我記得上次也寫了一隻 update.sh
不過離職了就飛了 (我什麼都沒備份 xd)
這次再寫一次 xddd

環境：Mac

#!/usr/bin/env bash

lists=$(ls);

for list in $lists
do
    if [ $list != 'update.sh' ]; then
        echo ">> $list";
        cd $list;
        git pull;
        cd ..;
    fi
done

MySQL Redundant Indexes 問題與檢測

shtzeng Post in MySQL，Tags: mysql; pt-duplicate-key-checker; redundant indexes

09 5 月 2016

上班又用到了，所以乾脆寫下來 XD

參考 https://www.percona.com/blog/2006/08/17/duplicate-indexes-and-redundant-indexes/

I call redundant indexes BTREE indexes which are prefix of other index, for example KEY(A), KEY (A,B), KEY(A(10)). – First and last are redundant indexes because they are prefix of KEY(A,B)

意即現在有三個 INDEX
KEY(A)
KEY(A,B)
KEY(A(10))
因為 B+TREE 結構問題， KEY(A) 跟 KEY(A(10)) 其實都與 KEY(A,B) 部分相同
刪除 KEY(A) 及 KEY(A(10)) 並不會影響效能 (因為會轉而參考 KEY(A,B))
但這樣會造成空間上的浪費，所以要刪除掉這類的 INDEX

檢測方法可以透過 Percona Toolkit 下的 pt-duplicate-key-checker 來檢測
簡單寫個 shell script 列排程，若有找到重複的就通知

First Prev 1 2 3 4 5 Next Last