Mysql 效能調優 一 3

 Mysql 效能調優 一   3

---

檢視mysql啟動檔案查詢順序：

[root@test1 ~]# mysql --help
Default options are read from the following files in the given order:
/etc/my.cnf /etc/mysql/my.cnf /usr/etc/my.cnf ~/.my.cnf


connect pool---&gtsql處理過程 ---&gt儲存引擎 ---&gt檔案系統


檢視系統引數：
show variables like '%key_buffer%';

檢視狀態引數：
show status like '%%';

修改全域性系統引數：
set global 引數名=值;

修改session引數：（預設）
set  引數名=值;

mysql> show variables like '%key_buffer%';
+-----------------+---------+
| Variable_name   | Value   |
+-----------------+---------+
| key_buffer_size | 8388608 |
+-----------------+---------+
1 row in set (0.00 sec)

mysql> show status like '%key_buffer%';
Empty set (0.00 sec)

mysql>

注意：
系統引數與狀態引數區別


key_buffer_size     query_cache_size
innodb_buffer_pool_size
table_cache

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++、
引數詳解：

系統引數：
max_connections      配置mysql最大連線數

狀態引數：
max_used_connections  從mysql開機開始，歷史最大連線數


mysql> show variables like '%max_connection%';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 5000  |
+-----------------+-------+
1 row in set (0.00 sec)

mysql> show variables like '%max_used_connections%';
Empty set (0.00 sec)

mysql> show status like '%max_used_connections%';
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| Max_used_connections | 1     |
+----------------------+-------+
1 row in set (0.00 sec)

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
系統引數：
thread_cache_size  執行緒

狀態引數：
Threads_created       執行緒建立

已建立執行緒處於一個相對穩定值，則thread_cache_size配置合適


mysql> show status like '%Threads_created%';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| Threads_created | 1     |
+-----------------+-------+
1 row in set (0.00 sec)

mysql> show variables like '%thread_cache_size%';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| thread_cache_size | 58    |
+-------------------+-------+
1 row in set (0.00 sec)

mysql>

mysqlslap -uroot -p123456 --query "select count(*) from mysql.user" -i 20 -c 300

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
系統引數：
table_open_cache

狀態引數：
open_tables     當前開啟的table數
opened_tables    歷史開啟的table數

flush tables

cashe與buffer的區別：


+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
系統引數：
open_files_limit

狀態引數：
open_files

opened_files

os系統方面調整: file-max
檔案控制程式碼

一般情況下，mysql資料庫，產生的資料檔案數都會多於oracle，所以對於開啟檔案數引數管理也相當重要

innodb_file_per_table 表空間
mysql的innodb的表空間，與oracle的表空間存在很大的區別：

要說表空間，Mysql的表空間管理遠遠說不上完善。換句話說，事實上Mysql根本沒有真正意義上的表空間管理。
Mysql的Innodb包含兩種表空間檔案模式，預設的共享表空間和每個表分離的獨立表空間。
只要在my.cnf裡面增加innodb_file_per_table=1就可以從共享表空間切換到獨立表空間。
當然對於已經存在的表，則需要執行alter table MY_TABLE engine=innodb命令遷移資料。


共享表空間方式
由於是預設的方式，就暫且理解為Mysql官方推薦的方式。相對而言所有的資料都在一個（或幾個）檔案中，比較利於管理，
而且在操作的時候只需要open這一個（或幾個）檔案即可，相對來說代價很低。
但問題是在資料達到以G為單位來計算的時候優劣逆轉。一個大小驚人的檔案很不利於管理，而且對於一個如此巨大的檔案來說，
讀寫它需要耗費的資源一樣巨大。更加令人費解的是，MySQL竟然將索引和資料儲存於同一個檔案中，索引和資料之間尚存在資源爭用，不利於效能的提升。
你當然可以透過innodb_data_file_path的配置規劃多個表空間檔案，但MySQL的邏輯是“用滿後增加”，僅僅是一個檔案的拆分而已，不能從根本上分離資料和索引。

之前曾經遭遇到700G以上的表空間檔案，而且更加讓人鬱悶的是對於如此大的檔案還在以每天數G的數量增加。
由於無法停機，即便是複製一下也要花費差不多一夜，只能眼睜睜看著它繼續增大而毫無保守可行的辦法。



獨立表空間方式

相對而言對立表空間每個表都有獨立的多個資料檔案，而且做到了索引和資料的分離。
多個小檔案之間很方便的完成跨資料庫甚至跨硬體的資料複製和遷移。相對來說靈活性很好。
這樣做同樣帶來另一個方面的問題。當資料庫中的表數量達到一定級別時，每次操作所涉及的檔案過多，如果按照預設Centos的ulimit -n = 1024的話，
僅僅只能保證同時開啟256個表以內，這在習慣上“拆庫拆表”的MySQL資料結構上很難達到要求。
尚且這種資料檔案的利用率不算很高，當大量“不高”的檔案集中起來，浪費的空間也很驚人，
更何況最後可能出現的狀況不是“一堆K級別的小檔案”而是“一堆G級別的大檔案”，
有點適得其反的意思。你自然可以聯想到分割槽表，又是一個“僅僅做檔案拆分而已”，
多個分割槽檔案缺一不可。



之前同樣遇到過這個問題，MySQL連線大的狀況下大量的timeout，但主機負載還算可以，
查了一圈才知道是open files限制的問題，限制一修改，負載變得驚人，但連線數卻又提升的不多。


總之，兩種方法各有所長，部分互補，但都不是解決問題的終極方案

innodb_data_file_path=ibdata1:12M.........
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++


                                                                   ;