mysql版本不一致会导致uuid_MySQL性能优化和高可用架构建议
1.主從復制binlog_format要使用row,statement會導致主從數據不一致(基于語句,rand或uuid等函數沒法恢復)
2.如果使用binlog恢復數據,標準做法是用mysqlbinlog工具把binlog中內容解析出來,然后把解析結果發給mysql執行
3.innodb_buffer_pool_size是緩存用戶表及索引數據的最主要緩存空間,建議可以設置到50%到80%的內存大小,調整這個參數期間會阻塞用戶的請求,直到調整完畢,建議在低峰期調整
4.對于某些工作負載,如使用like和%的范圍查詢以及高并發的joins,不適合使用自適應哈希索引,維護哈希索引結構的額外開銷會帶來嚴重性能損耗,這種情況建議關掉,set global innodb_adaptive_hash_index=off/on命令
5.innodb_flush_log_at_trx_commit的參數取值
設為1:最安全,數據肯定一致,配合sync_binlog=1
設為2:適合數據一致性和完整性要求不高的
設為0:只要求性能,高并發日志服務
6.對于可能產生大量更新記錄的大事務,增加innodb_log_buffer_size的大小,可以避免在事務提交前就執行不必要的日志寫入磁盤操作,提高事務處理性能
7.關于query_cache,任何更新操作都會導致其失效,并發高的時候也有影響,還有bug,所有大部分情況下只是雞肋,建議全面禁用,5.7中默認關閉
8.當發生鎖等待情況時,可以通過語句select * from sys.innodb_lock_waits \G來在線查看,最主要是看waiting_pid等待事務的線程pid、waiting_query等待鎖釋放的語句、blocking_pid阻塞事務的pid、blocking_query阻塞事務的SQL語句這4個參數,pid對應show full processlist命令里面輸出的線程id號;阻塞事務語句顯示可能為NULL,想要找到相應id對應的SQL可以使用select SQL_TEXT from performance_schema.events_statements_current where thread_id in (select thread_id from performance_schema.threads where processlist_id = 相應pid)
9.Innodb會自動檢測死鎖,并立即回滾其中某個事務,并且返回一個錯誤。偶然發生死鎖不必擔心,當死鎖頻繁出現是要引起注意,檢查代碼,調整SQL操作順序,或者縮短事務長度
10.避免死鎖建議:
1.)不同程序并發存取多個表或者涉及多行記錄時,盡量約定以相同的順序訪問表
2.)把大事務盡量分解成多個小事務,使所更快釋放
3.)在同一個事務中,盡可能做到一次鎖定所需要的所有資源
4.)盡量使用索引,不用索引會為每一行添加鎖
11.通過show full processlist是為了查看當前MySQL是否有壓力、都在跑什么語句、當前語句耗時多久了,從中可以看到總共有多少鏈接數、哪些線程有問題,然后把有問題的線程kill掉,臨時解決一些突發性問題
12.通過show engine innodb status命令來查看是否存在鎖表的情況
13.事務和鎖信息記錄在information_schema數據庫中,主要涉及innodb_trx、innodb_locks、innodb_lock_waits,可以幫我們方便地監控當前的事務并分析可能存在的鎖問題
innodb_trx:查看事務情況
trx_id:唯一的事務id號
trx_state:當前事務的狀態
trx_wait_started:事務開始等待的時間
trx_mysql_thread_id:線程id與show full processlist相對應
trx_query:事務運行的SQL語句
trx_operation_state:事務運行的狀態
innodb_locks:查詢事務鎖情況,包括事務正在申請加的鎖和事務加上的鎖
innodb_lock_waits查看鎖阻塞情況
requesting_trx_id:請求鎖的事務ID(等待方)
blocking_trx_id:阻塞該鎖的事務ID(當前持有方,待釋放)
14.查詢語句避免使用select *且加上limit限制
15.使用索引盡量避免事后才想起添加索引
16.開啟ICP(Index Condition Pushdown)可以過濾掉大量的數據,減少IO,提高查詢語句性能,開啟方法:set optimizer_switch="index_condition_pushdown=on"
17.開啟MRR(Multi-Range Read Optimization),優化器將隨機IO轉化為順序IO,目的是減少磁盤的隨機訪問,降低查詢過程中的IO開銷,對IO-bound類型的SQL語句性能帶來極大的提升;開啟方式set optimizer_switch="mrr=on,mrr_cost_based=on",cost base=on表示當發現優化后的代價高時就不會使用該優化項
18.開啟BKA(batched key access),提高表join性能,開啟方式set optimizer_switcher="mrr=on,mrr_cost_based=off,batched_key_access=on",BKA必須使用MRR,所以mrr_cost_based必須關閉
19.慢SQL優化思路:開啟慢日志,設置超過幾秒為慢SQL語句,抓取慢SQL語句,通過explain查看執行計劃,對慢SQL語句分析,創建索引并調整語句,再查看執行計劃,對比調優結果
1.抓取慢SQL:可以使用slow_query_log_file,可以使用mysqldumpslow進行分析,命令為mysqldumpslow -t 10 xxx/xxx/xxx.log,顯示出日志中最慢的10條SQL
2.通過explain分析語句,其中type字段有ALL(代表全表掃描,沒有用到index)、index、range(優化目標至少到range級別)、ref、eq_ref、const、system、NULL(從左到右,性能從差到好);key字段出現NULL,代表沒有使用索引;rows字段值越大意味著需要掃描的行數越多,相應耗時越長;extra字段出現Using filesort,一般是因為order by后的條件導致索引失效,最好進行優化;extra字段出現Using join buffer,應該注意,根據查詢的具體情況可能需要添加索引來改進;extra列要避免出現Using filesort或Using temporary,很影響性能
3.show profiles也有助于分析慢SQL
20.索引使用原則
1.)表一定要有主鍵,顯示定義主鍵且采用與業務無關的列以避免修改,建議采用自增列來使數據順序插入
2.)經常被查詢的列、經常用于表連接的列、經常排序分組的列,需要創建索引
3.)組合索引區分度高的放在最左邊,字段數不建議超過5個,如果5個字段還不能極大地縮小row范圍,那么設計肯定有問題
4.) 單張表的索引數量建議控制在5個以內
5.)注意隱式類型轉換會導致索引失效
21.通過設置innodb_buffer_pool_dump_pct的值可以在重啟時自動預熱,提高訪問性能(尤其是在業務高峰時)
22.innodb_undo_log_tablespaces建議設置為3,避免在自動清除undo log時系統處于不可用狀態,2也可以,但3更安全
23.硬件建議:
1.使用SSD或者PCIe SSD設備,至少獲得百倍甚至萬倍的IOPS提升
2.購置陣列卡同時配備Cache及BBU模塊,有陣列卡時設置陣列寫策略為WB,甚至FORCE WB,嚴禁使用WT策略
3.盡可能選用RAID10,而非RAID 5
3.建議在BIOS層面關閉NUMA,CPU設置為最大性能模式,選擇performance per watt optimized來充分發揮CPU的最大功耗性能,同時建議關閉節能選項
24.Linux操作系統層面優化
1.防火墻(iptables)和SElinux需要關閉
2.關于IO調度,建議使用deadline或者noop模式,不要使用cfg模式,會影響數據庫性能
3.設置內核參數vm.swappiness=1,盡量避免使用swap(交換)分區
4.推薦使用xfs文件系統,其次選擇ext4文件系統,放棄ext3;CentOS 7將xfs作為默認的文件系統
25.MySQL配置參數優化
query_cache_type=0,query_cache_size=0 關閉查詢緩存
innodb_buffer_pool_size一般設置為物理內存的50%-80%
innodb_io_capacity與innodb_io_capacity_max:取決于硬盤IOPS,即每秒的輸入輸出量(或讀寫次數),capacity一般建議設置如下:SAS:200,SSD:3000,PCI-E:10000-50000,capacity_max為capacity的2倍,也可以通過sysbench或其他基準工具來測試磁盤的吞吐量
innodb_log_file_size:redo日志大小,通常應用是頻繁寫入的,可以設置為2G
innodb_flush_method:默認是fdatasync,當服務器硬件有SSD硬盤、RAID控制器、斷電保護、采取write-back緩存機制的時候,可以改為0_DIRECT,避免了buffer pool和系統buffer的雙緩存,效率更高
innodb_max_dirty_pages_pct:臟頁占buffer pool的比例,建議調整為50,表示當臟塊達到innodb_buffer_pool_size的50%時觸發檢查點,寫磁盤
binlog_format建議使用row模式,數據更加安全可靠,在主從復制過程中不會丟失數據
26.設計規范建議
1)字符集統一使用utf8mb4,降低亂碼風險,初始化時就設置好,修改后只會對修改后創建的表生效
2)小數字段推薦使用decimal類型,float/double精度不夠
3)避免使用text/blob來存儲大段文本、二進制數據、圖片、文件等內容,應該保存成磁盤文件,數據庫中保存其索引即可
4)不使用外鍵,在應用層實現外鍵邏輯,外鍵會降低性能,容易產生死鎖
5)字段盡量定義為NOT NULL并加上默認值,NULL會給SQL開發帶來很多問題,從而導致用不了索引,對NULL計算時只能用IS NULL和IS NOT NULL來判斷
6)不要使用存儲過程、觸發器、視圖、函數等高級功能,移植性和可擴展性較差
7.)varchar是可變長字符串,不預先分配存儲空間,長度不超過5000,如果大于此值,定義為text類型,獨立出來一張表,用主鍵來對應
27.SQL規范建議
1.)禁止使用insert into t values(xx),必須顯示指定插入的列屬性,避免表結構變動導致數據出錯
2.)禁止使用select *,必須指定查詢字段
3.)join時必須讓小表做驅動表,join列必須字符集一致并且都建有索引
28.主從復制建議
1.)在從庫上master_info_repository="TABLE"和relay_log_info_repository=“TABLE”和relay_log_recovery=1確保在slave上和復制相關的元數據表也采用innodb引擎,受到innodb事務安全的保護,并開啟relay-log自動修復機制
2.)主庫上innodb_flush_log_at_trx_commit=1且sync_binlog=1確保數據高安全
3.)從節點上授權只讀模式set global read_only=1避免人為誤操作在從庫中修改數據,導致主從不一致,對應擁有super權限用戶使用set global super_read_only=ON
4.)建議使用和主庫規格一樣好的硬件設備作為slave,存儲采用PCIE-SSD才是王道
5.)建議使用GTID復制模式(待定)
6.)工具使用perconna-toolkit,可以跳過錯誤pt-slave-restart、主從一致性檢查pt-table-checksum、修復不一致數據pt-table-sync
7.)主從延遲排查show slave status 看Seconds_Behind_Master參數的值來判斷,該值為0表示主從復制良好,如果是正值表示已出現延遲,數字越大延遲越嚴重或者用percona-toolkit的pt-heartbeat來監測延遲情況
8.)主從延遲解決建議
a.)硬件配置和主庫一致,強烈建議使用SSD硬盤,并且修改配置參數innodb_flush_method為0_DIRECT提升寫入性能
b.)適當增大從庫innodb_buffer_pool_size,減少IO壓力
c.)從庫不需要太高的數據安全,可以將sync_binlog設置為0或者500,且innodb_flushlog_at_trx_commit設置成2,減少磁盤IO壓力
d.)開啟并行復制MTS,最少升級到5.7.19版本,修復了很多bug
29.高可用方案
MGR 單寫 HAProxy+自定義腳本 proxySQL 秒級切換
MHA
PXC
keepalived+雙主復制 云服務器上keepalived只能設置單播,通常不支持浮動IP,往往需要用戶提請工單來開通高可用虛擬IP
30.分庫分表
數據遷移:先讀出歷史數據,然后按照分片規則寫入,計算需要多少分片,建議單分片單表容量不超過1000w,數據盡可能均勻分布到各個節點
總結
以上是生活随笔為你收集整理的mysql版本不一致会导致uuid_MySQL性能优化和高可用架构建议的全部內容,希望文章能夠幫你解決所遇到的問題。
- 上一篇: 将输入字符串t中从第m个字符开始的全部字
- 下一篇: php slaveok_ZipArchi