====== MySQL/MariaDB 相關技巧彙整 ======
===== 1. 資料表效能優化 =====
* 參考 - https://www.thegeekstuff.com/2016/04/mysql-optimize-table/
- 找出需要優化的資料表 Exp. 資料庫 abc
use abc;
select table_name, round(data_length/1024/1024) as data_length_mb, round(data_free/1024/1024) as data_free_mb
from information_schema.tables
where round(data_free/1024/1024) > 500
order by data_free_mb;
結果顯示類似以下訊息
+----------------+----------------+--------------+
| table_name | data_length_mb | data_free_mb |
+----------------+----------------+--------------+
| dwg_files | 312 | 527 |
| pdm_to_erp_log | 24 | 1277 |
| user_use_log | 66 | 879 |
+----------------+----------------+--------------+
3 rows in set (0.006 sec)
- 針對呈現出來的 Table 進行優化
OPTIMIZE TABLE dwg_files, pdm_to_erp_log, user_use_log;
結果顯示如下訊息
+--------------------------+----------+----------+-------------------------------------------------------------------+
| Table | Op | Msg_type | Msg_text |
+--------------------------+----------+----------+-------------------------------------------------------------------+
| abc.dwg_files | optimize | note | Table does not support optimize, doing recreate + analyze instead |
| abc.dwg_files | optimize | status | OK |
| abc.pdm_to_erp_log | optimize | note | Table does not support optimize, doing recreate + analyze instead |
| abc.pdm_to_erp_log | optimize | status | OK |
| abc.user_use_log | optimize | note | Table does not support optimize, doing recreate + analyze instead |
| abc.user_use_log | optimize | status | OK |
+--------------------------+----------+----------+-------------------------------------------------------------------+
6 rows in set (31.340 sec)
- 查看 /var/lib/mysql/abc 內的資料檔在執行後會變小
===== 2. 設定與查詢週期定時執行程序 =====
* 參考 - https://noter.tw/2306/mysql%E6%8E%92%E7%A8%8Bevent-scheduler/
- 查看現有排程
show events;
Exp.
+-----------+-------------------------------------------------------------+----------------+-----------+-----------+------------+----------------+----------------+---------------------+---------------------+---------+------------+----------------------+----------------------+--------------------+
| Db | Name | Definer | Time zone | Type | Execute at | Interval value | Interval field | Starts | Ends | Status | Originator | character_set_client | collation_connection | Database Collation |
+-----------+-------------------------------------------------------------+----------------+-----------+-----------+------------+----------------+----------------+---------------------+---------------------+---------+------------+----------------------+----------------------+--------------------+
| abc | SE同步刪除1個月以上的紀錄 | root@localhost | SYSTEM | RECURRING | NULL | 1 | DAY | 2019-09-15 00:00:00 | 2030-09-30 00:00:00 | ENABLED | 1 | utf8mb4 | utf8mb4_unicode_ci | utf8_general_ci |
| abc| 刪除1個月以上的curl資料 | root@localhost | SYSTEM | RECURRING | NULL | 1 | DAY | 2019-10-08 00:00:00 | 2030-10-31 00:00:00 | ENABLED | 1 | utf8mb4 | utf8mb4_unicode_ci | utf8_general_ci |
:
- 顯示詳細排程內容 Exp. 刪除1個月以上的curl資料
SELECT * FROM INFORMATION_SCHEMA.EVENTS WHERE EVENT_NAME='刪除1個月以上的curl資料';
顯示結果:
+---------------+--------------+----------------------------------+----------------+-----------+------------+-------------------------------------------------------------------------------------+------------+------------+----------------+----------------+-------------------------------------------------------------------------------------------+---------------------+---------------------+---------+---------------+---------------------+---------------------+---------------------+---------------+------------+----------------------+----------------------+--------------------+
| EVENT_CATALOG | EVENT_SCHEMA | EVENT_NAME | DEFINER | TIME_ZONE | EVENT_BODY | EVENT_DEFINITION | EVENT_TYPE | EXECUTE_AT | INTERVAL_VALUE | INTERVAL_FIELD | SQL_MODE | STARTS | ENDS | STATUS | ON_COMPLETION | CREATED | LAST_ALTERED | LAST_EXECUTED | EVENT_COMMENT | ORIGINATOR | CHARACTER_SET_CLIENT | COLLATION_CONNECTION | DATABASE_COLLATION |
+---------------+--------------+----------------------------------+----------------+-----------+------------+-------------------------------------------------------------------------------------+------------+------------+----------------+----------------+-------------------------------------------------------------------------------------------+---------------------+---------------------+---------+---------------+---------------------+---------------------+---------------------+---------------+------------+----------------------+----------------------+--------------------+
| def | abc | 刪除1個月以上的curl資料 | root@localhost | SYSTEM | SQL | delete FROM abc_curl_log WHERE DATE_SUB(CURDATE(), INTERVAL 15 DAY) > DATE(dd) | RECURRING | NULL | 1 | DAY | STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION | 2019-10-08 00:00:00 | 2030-10-31 00:00:00 | ENABLED | PRESERVE | 2020-07-07 09:12:28 | 2020-07-07 09:12:28 | 2020-08-20 00:00:00 | | 1 | utf8mb4 | utf8mb4_unicode_ci | utf8_general_ci |
+---------------+--------------+----------------------------------+----------------+-----------+------------+-------------------------------------------------------------------------------------+------------+------------+----------------+----------------+-------------------------------------------------------------------------------------------+---------------------+---------------------+---------+---------------+---------------------+---------------------+---------------------+---------------+------------+----------------------+----------------------+--------------------+
1 row in set (0.001 sec)
- 建立新的排程 Exp. 針對特定 Table 每周日中午 12:07 進行優化
CREATE EVENT 優化LOG型資料表
ON SCHEDULE EVERY 7 DAY STARTS '2020-08-23 12:07'
DO
OPTIMIZE TABLE erptools_to_sunlikeerp_log, erptools_curl_log, se_synsetting_log ;
如果沒有錯誤訊息, 就可以透過
SHOW events;
顯示出來如下:
+-----------+-------------------------------------------------------------+----------------+-----------+-----------+------------+----------------+----------------+---------------------+---------------------+---------+------------+----------------------+----------------------+--------------------+
| Db | Name | Definer | Time zone | Type | Execute at | Interval value | Interval field | Starts | Ends | Status | Originator | character_set_client | collation_connection | Database Collation |
+-----------+-------------------------------------------------------------+----------------+-----------+-----------+------------+----------------+----------------+---------------------+---------------------+---------+------------+----------------------+----------------------+--------------------+
| abc | SE同步刪除1個月以上的紀錄 | root@localhost | SYSTEM | RECURRING | NULL | 1 | DAY | 2019-09-15 00:00:00 | 2030-09-30 00:00:00 | ENABLED | 1 | utf8mb4 | utf8mb4_unicode_ci | utf8_general_ci |
| abc | 優化LOG型資料表 | root@localhost | SYSTEM | RECURRING | NULL | 7 | DAY | 2020-08-23 12:07:00 | NULL | ENABLED | 1 | utf8 | utf8_general_ci | utf8_general_ci |
:
===== 3. 出現異常訊息 - [ERROR] mysqld: Table './stats/stats_media' is marked as crashed and should be repaired =====
* 參考 - https://stackoverflow.com/questions/58386240/mysqld-exe-table-mysql-db-is-marked-as-crashed-and-should-be-repaired
* 對 database : stats 進行修復
mysqlcheck -r --databases stats --use-frm
會在 log 內看到如下的訊息
:
:
220803 15:11:48 [ERROR] mysqld: Table './stats/stats_media' is marked as crashed and should be repaired
220803 15:13:10 [Note] Found 6676 of 0 rows when repairing './stats/stats_access'
220803 15:13:10 [Note] Found 813 of 0 rows when repairing './stats/stats_edits'
:
220803 15:13:11 [Note] Found 517 of 0 rows when repairing './stats/stats_searchwords'
220803 15:13:11 [Note] Found 1013 of 0 rows when repairing './stats/stats_session'
{{tag>mysql tips}}