Percona博客學習總結

1. Upgrading to MySQL 5.7, focusing on temporal types

    在MySQL 5.6.4中，對TIME, TIMESTAMP and DATETIME三種時間類型進行了擴充，支持了微秒，並且DATETIME的存儲由之前的8個位元組縮小到5個位元組。

MySQL 5.6.4在Server層新增三種時間類型MYSQL_TYPE_TIME2，MYSQL_TYPE_DATETIME2，MYSQL_TYPE_TIMESTAMP2，併在InnoDB層以二進位的格式存儲，用這種方式來實現時間類型支持小數精度並優化存儲節省空間。

    如果從MySQL 5.5升級到5.6，使用mysql_upgrade，mysql_upgrade並不會顯示的將之前的類型轉換成新類型，只是新建的表會使用新類型。

    但如果是升級到5.7，則mysql_upgrade會將舊類型轉換為新類型，而這可能導致升級的過程比較漫長。

    對於上述情況，如果避免呢？

    1. mysql_upgrade支持upgrade-system-tables選項，在升級時可指該參數，只升級系統表，而不升級普通表

    2. 升級完畢後，再使用alter table或者pt-online-schema-schange修改表的數據類型。

    可通過以下命令查看升級完畢後，各表中上述三個欄位的新舊情況。   

SELECT CASE isc.mtype
           WHEN '6' THEN 'OLD'
           WHEN '3' THEN 'NEW'
       END FORMAT,
       t.schema_name,
       t.table_name
FROM information_schema.tables AS t
INNER JOIN information_schema.columns AS c ON c.table_schema = t.table_schema
AND c.table_name = t.table_name
LEFT OUTER JOIN information_schema.innodb_sys_tables AS ist ON ist.name = concat(t.table_schema,'/',t.table_name)
LEFT OUTER JOIN information_schema.innodb_sys_columns AS isc ON isc.table_id = ist.table_id
AND isc.name = c.column_name
WHERE c.column_type IN ('time','timestamp','datetime')
    AND t.table_schema NOT IN ('mysql','information_schema','performance_schema')
    AND t.table_type = 'base table'
    AND (t.engine = 'innodb');

      註意：5.6中t.shema_name應為t.TABLE_SCHEMA。

      具體可參考：https://www.percona.com/blog/2016/04/27/upgrading-to-mysql-5-7-focusing-on-temporal-types/

2. Best Practices for Configuring Optimal MySQL Memory Usage

    1> The first rule of configuring MySQL memory usage is you never want your MySQL to cause the operating system to swap.Even minor swapping activity can dramatically reduce MySQL performance.

        並不是有了swap就一定是壞事，有可能這些swap是在MySQL啟動之前產生的。重點需要關註的是MySQL運行過程中的SWAP情況。

        這個可以很容易根據vmstat命令中的si和so列看出來。

    2> MySQL的記憶體分配很複雜，There are global buffers, per-connection buffers (which depend on the workload), and some uncontrolled memory allocations (i.e., inside Stored Procedures), all contributing to difficulties in computing how much memory MySQL will really use for your workload.

        比較合理的是查看MySQL使用的virtual memory size (VSZ)，這個可通過top命令或者ps aux | grep mysqld查看，單位是KB。

$ ps aux |grep mysqld
bdp      16569  0.0  0.0 103240   868 pts/0    S+   14:35   0:00 grep mysqld
bdp      25883  0.0  0.0 106220  1360 ?        S    Mar25   0:00 /bin/sh ./bin/mysqld_safe
bdp      26716 12.7  2.4 16884956 9652760 ?    Sl   Mar25 7684:17 /home/bdp/mysql/bin/mysqld --basedir=/home/bdp/mysql/ --datadir=/home/bdp/mysql/data/ --plugin-dir=/home/bdp/mysql//lib/plugin --log-error=/home/bdp/mysql/logs/mysqld.log --pid-file=/home/bdp/mysql/run/mysqld/mysqld.pid --socket=/home/bdp/mysql/tmp/mysql.sock
-bash-4.1$ bash

       上圖中，VSZ16884956，大概16.1G    

       VSZ可能會隨著時間發生變化，所以要及時監控這一項，Don’t allow the mysqld process VSZ exceed 90% of the system memory，如果伺服器上還運行了其它的進程，閥值更小，一旦超過這個閥值，要及時發出告警（這點，可通過腳本實現）。

    3> 在大多數情況下，你不能將90%的記憶體分配給MySQL。有以下幾點需要考慮：

        1. 有其它的重要進程運行在同一伺服器上，無論是長期的還是周期性的，或者批量任務。

        2. 對於Innodb，推薦innodb_flush_method=O_DIRECT，這樣就不會使用Operating System File Cache。但是對於MyISAM或者TokuDB，需要操作系統緩存來緩存數據。

        3. 對於MyISAM on disk temporary tables, sort files, some other temporary files，操作系統緩存還是必需的。

    4> 對於一個16G記憶體的伺服器，innodb_buffer_pool_size分配多少比較合適呢？

        Let’s do some math for a specific example. Assume you have a system (physical or virtual) with 16GB of memory. We are only running MySQL on this system, with an InnoDB storage engine and use innodb_flush_method=O_DIRECT, so we can allocate 90% (or 14.4GB) of memory to MySQL. For our workload, we assume connection handling and other MySQL connection-based overhead will take up 1GB (leaving 13.4GB). 0.4GB is likely to be consumed by various other global buffers (innodb_log_buffer_size, Table Caches, other miscellaneous needs, etc.), which now leaves 13GB. Considering the 5-7% overhead that the InnodB Buffer Pool has, a sensible setting is innodb_buffer_pool_size=12G – what we very commonly see working well for systems with 16GB of memory.

    5> 配置完MySQL記憶體，下麵來看看操作系統層面的配置。

        主要涉及以下幾個方面：

        1. SWAP需要開啟麽？

            需要開啟，爭取最小4G，但不能超過伺服器記憶體的25%。有以下兩點原因：

            1> 操作系統可以將記憶體中沒有使用的部分通過swap方式釋放出去，而不是強制它們放到記憶體中。

            2> 如果你的MySQL配置出現錯誤，或者某些進程的記憶體消耗超過預期，會導致MySQL因記憶體溢出（OOM）直接被kill掉，如果有swap，最多是性能下降。

            可通過設置echo 1 >  /proc/sys/vm/swappiness降低系統對swap的使用，從而避免不必要的swap對性能造成的影響。這個參數定義了系統對swap的使用傾向，預設值為60，值越大表示越傾向於使用swap。

         2. 配置Out Of Memory killer

            echo '-800' > /proc/$(pidof mysqld)/oom_score_adj

            通過設置此值，可使Linux內核優先kill其它進程而不是MySQL。

         3. 在多核伺服器中，關於NUMA的設置。

            在5.7.9中，引入了一個新的系統參數，innodb_numa_interleave，將其設置為1，Enables the NUMA interleave memory policy for allocation of the InnoDB buffer pool

            在之前，可通過numactl --interleave=all進行設置。

            具體可參考：https://www.percona.com/blog/2016/05/03/best-practices-for-configuring-optimal-mysql-memory-usage/