【總結】日常遇到的一些問題相關知識

cell_liu發表於2017-03-10
RAID卡:  
bbu  force wb
核心：
1. io排程  deadline   noop
2. vm 管理 vm.swappiness = 0
檔案系統  mount  nobarrier data=ordered writeback
1. 加大佇列  nr_requests
2. 關閉numa
CPU:
innodb_read_io_threads =  cpu核心數/2
innodb_write_io_threads = cpu核心數/2
read + write  = cpu核心數
可以根據讀寫比例調整上述值
磁碟：
innodb_io_capacity = 硬碟的iops
髒頁重新整理：
當超過innodb_max_dirty_pages_pct的設定值 ， 會將innodb_buffer_pool重新整理到磁碟
重做日誌ib_logfile寫滿後
機器空閒的時候
當寫操作很頻繁的時候，重做日誌ib_logfile切換次數就會很頻繁，只要有一個寫滿了，就會將髒頁大量的刷入磁碟，這會對系統系統有整體的影響。為了避免過大的磁碟io ， innodb_adaptive_flushing會自適應重新整理。引數預設開啟。
innodb_buffer_pool:
1. innodb_buffer_poll有2個區域，sublist of new blocks（熱資料） sublist of old blocks（不經常訪問的資料）
使用者訪問資料，如果緩衝區中有相應的資料，則返回，如果沒有，從磁碟空間讀入sublist of old blocks區域，然後再移動到2. sublist of new blocks 區域，然後在根據LRU演算法將最近最少使用的資料移除舊資料頁
3. 但是這其中也有一些問題，假如有些語句做統計用全表掃描，或者做一次mysqldump，這時就會進入sublist of new blocks區域，把一些真正的熱資料提走，這樣會造成緩衝區的進進出出，導致磁碟io繁忙
4. innodb_old_block_pec引數可以控制進入緩衝區sublist of old blocks的數量，預設37，佔整個緩衝區的3/8。當全表掃描一個大表或者做mysqldump可以將innodb_old_block_pec設定的小些使資料塊少量的進入緩衝區，從而讓更多的熱資料不被踢出。
Old database pages 23600  在舊區域存放多少頁
pages made young 411111, not young 2987612 移動到新區域多少頁  /   沒有移動的有多少頁
1274 youngs/s  16521 non-youngs/s   
如果你沒有全表掃描，發現youngs/s值很小，那麼就該增大innodb_old_blocks_pec或者減少innodb_old_blocks_time 。如果進行了全表掃描，發現non-youngs/s的值很小，那麼就應該增大innodb_old_blocks_time
innodb資料恢復時間：
依賴於 buffer poll ，髒頁面比例，tps等因素
innodb_buffer_pool_instances = 10  innodb_buffer_poll例項的個數
最多支援64 。 innodb_buffer_pool_size  必須大於1G
innodb_buffer_pool_dump_at_shutdown = 1
關閉mysql時，把熱資料dump到本地硬碟
innodb_buffer_pool_dump_now = 1
採用手工方式把資料dump到磁碟
innodb_buffer_pool_load_at_startup = 1
在啟動時把熱資料load到記憶體
innodb_buffer_pool_load_now = 1
採用手工方式把熱資料load到記憶體
慢日誌：
當資料庫連線數很高時，此時需要注意，可以通過cacti監控軟體觀察時間點，然後把這一段時間點的慢日誌擷取出來
sed -n `/#Time:110720 16:17:20/,/end/p` mysql.slow > slow.log
然後用mysqldumpslow 命令取出最耗時的最長的10條慢sql進行分析
mysqldumpslow -s t -t 10 slow.log
主從同步 ：
slave_exec_mode引數可自動處理同步複製錯誤
set global slave_exec_mode=`IDEMPOTENT`
預設值是 STRICT 嚴格模式
設定完畢後，當出現1023（記錄沒有找到） ， 1026（主鍵複製）時，會自動跳過錯誤，並且記錄到錯誤日誌中。
如果從庫某幾張表的資料和主庫不一致，解決方法：
1. 停止slave
2. 在主庫上匯出這幾張表，並記錄下同步的binlog時間點和pos點
mysqldump -uroot -pxxx -q –single-transation –master-data=2 yourdb table1 table2 > xxxx.sql
3. 匯入sql檔案到從庫。找出備份的sql檔案中的binlog和pos點
4. change master
直到sql_thread執行緒為NO，這期間同步報錯可以一律跳過
stop slave ; set global sql_slave_skip_counter=1 ; start slave;
如何幹淨的清除slave的同步資訊
stop slave ; reset slave 不行。 要使用 reset slave all；
主從切換：
事件排程器在主從切換出現的問題:
set global event_scheduler = 1;
在主從架構中 event只能在主上觸發，在slave上不能被觸發，如果再slave上觸發了，同步就會壞掉。
show event 
status ：slaveside_disabled
需要執行 alter event `insert` enable
人工誤刪除innodb ibdata 資料檔案，如何恢復：
先找到mysqld程式的pid   netstat -nlp | grep mysqld   這裡是30246
執行 ll /proc/30426/fd | grep `ib_|ibdata`
關閉前端業務 ，執行 flush tables with read lock
set global innodb_max_dirty_pages_pct=0; 讓髒頁儘快重新整理到磁碟 
show engine innodb statusG;檢視innodb狀態資訊，確保髒頁已經重新整理到磁碟 。
複製檔案到mysql的資料目錄 。重啟mysql
where 從句中的in 子查詢 ：
select * from tables1 where filed in (select filed from table2 where cacheld=`xxxx`)
工作原理：mysql先要為內層的查詢語句查詢結果建立一個臨時表，然後外層查詢語句才能在臨時表中查詢記錄，查詢完成後，還需要撤銷這些臨時表。
sql優化案例分析 ：
1. not in 子查詢優化
在生產環境中 ，儘量避免子查詢，可以用表連線取代。 （select update delete ）
2. 模糊匹配 like `%xxx%`優化
不要使用select *  ， select * 無法覆蓋索引。
select 欄位必須存在索引。
3. limit 分頁優化
select xx,xx,xx from logdb order by id limit 9999,10;
select xx,xx,xx from logdb where id >=10000 order by id limit 10 
4. count(*) 統計如何加快速度
count(*)如果資料量大一般都是比較慢的
select count(*) from L_user;
走聚集索引，innodb聚集索引就是資料，需要全表掃描統計。
select count(*) from L_user where Sid >=0;
如果加條件，走where後的Sid輔助索引，輔助索引不存放資料，而是由一個指標指向對應的資料塊。統計時消耗的資源少，速度也更快。
雖然上述方法加快了速度，類似統計的sql語句 select count(*) select sum() 。這不建議在主庫執行，會導致鎖表。不走索引，表鎖。
5. or 條件優化
select * from USER where name=`bob` or age=`30`;
這條語句是不會走索引的。全表掃描。
select * from USER where name=`bob` union all select * from USER where age=`30`
##############################################################################
6. 不必要的排序
select count(1) as rs_count from 
(
select a.id,a.title,a.content,b.log_time,b.name
from a b
where a.content like `rc_%` and a.id=b.id
order by a.title desc
)
as rs_table;
select count(1) as rs_count from
(
select a.id from a join b
on a.id = b.id and a.content like `rc_%`
)
as rs_table
7. 不必要的巢狀 select 子查詢
select count(1) as rs_count from 
(
select a.id,a.title,a.content,b.log_time,b.name
from a b
where a.content like `rc_%` and a.id=b.id
order by a.title desc
)
as rs_table;
select a.id,a.title,a.content,b.log_time,b.name
from a join b
on a.id = b.id and a.content like `rc_%`
order by a.title
#########################################################################################################
10.用where代替having
這個簡單，就不舉例子了


相關文章