MySQL
- 查詢同時連線數上限
mysql> SHOW VARIABLES where Variable_name like 'max_connect%'; +--------------------+-------+ | Variable_name | Value | +--------------------+-------+ | max_connect_errors | 100 | | max_connections | 151 | +--------------------+-------+ 2 rows in set (0.00 sec)
- 查詢目前的同時連線數
mysql> show status where `variable_name` = 'Threads_connected'; +-------------------+-------+ | Variable_name | Value | +-------------------+-------+ | Threads_connected | 151 | +-------------------+-------+ 1 row in set (0.00 sec)
- 查詢目前連線的 process
mysql> show processlist; +----+-------+-----------+-------+---------+------+-------+------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+-------+-----------+-------+---------+------+-------+------------------+ | 1 | root | localhost | NULL | Query | 0 | init | show processlist | | 5 | cacti | localhost | cacti | Sleep | 292 | | NULL | | 6 | cacti | localhost | cacti | Sleep | 311 | | NULL | | 7 | cacti | localhost | cacti | Sleep | 7 | | NULL | | 8 | cacti | localhost | cacti | Sleep | 7 | | NULL | | 10 | cacti | localhost | cacti | Sleep | 306 | | NULL | | 11 | cacti | localhost | cacti | Sleep | 306 | | NULL | | 12 | cacti | localhost | cacti | Sleep | 306 | | NULL | | 33 | cacti | localhost | cacti | Sleep | 0 | | NULL | | 34 | cacti | localhost | cacti | Sleep | 1 | | NULL | | 35 | cacti | localhost | cacti | Sleep | 37 | | NULL | | 36 | cacti | localhost | cacti | Sleep | 4 | | NULL | | 37 | cacti | localhost | cacti | Sleep | 7 | | NULL | | 38 | cacti | localhost | cacti | Sleep | 7 | | NULL | | 39 | cacti | localhost | cacti | Sleep | 7 | | NULL | | 40 | cacti | localhost | cacti | Sleep | 7 | | NULL | | 41 | cacti | localhost | cacti | Sleep | 7 | | NULL | +----+-------+-----------+-------+---------+------+-------+------------------+ 17 rows in set (0.00 sec)
- 查詢目前的連線等待時間
- [參考] https://stackoverflow.com/questions/23399111/safely-keeping-mysql-connections-alive
- 預設值是 28800 seconds
mysql> SHOW VARIABLES where Variable_name like 'wait%'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | wait_timeout | 28800 | +---------------+-------+ 1 row in set (0.00 sec)
- 可以 runtime 修改
mysql> set global wait_timeout=60; mysql> set session wait_timeout=60; mysql> SHOW VARIABLES where Variable_name like 'wait%'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | wait_timeout | 60 | +---------------+-------+ 1 row in set (0.00 sec)
- 查詢資料庫硬碟用量 - 參考來源
SELECT table_name AS `Table`, round(((data_length + index_length) / 1024 / 1024), 2) `Size (MB)` FROM information_schema.TABLES WHERE table_schema = "$your_database";
- 查詢資料表硬碟用量 - 參考來源
SELECT sum(char_length($your_column))/1024/1024 FROM $your_table
- RENAME TABLE Syntax
- MySQL Table 換資料庫 - 從 DB_A 要搬到 DB_B
mysql> RENAME TABLE DB_A.old_table TO DB_B.new_table;
- MySQL Table 重新命名
mysql> RENAME TABLE old_table TO new_table;
- MySQL Table 互換名字
mysql> RENAME TABLE old_table TO tmp_table, new_table TO old_table, tmp_table TO new_table;
- 顯示 InnoDB 跟 MyISAM 各自儲存了多少資料量
mysql> SELECT IFNULL(B.engine,'Total') "Storage Engine", CONCAT(LPAD(REPLACE(FORMAT(B.DSize/POWER(1024,pw),3),',',''),17,' '),' ', SUBSTR(' KMGTP',pw+1,1),'B') "Data Size", CONCAT(LPAD(REPLACE( FORMAT(B.ISize/POWER(1024,pw),3),',',''),17,' '),' ', SUBSTR(' KMGTP',pw+1,1),'B') "Index Size", CONCAT(LPAD(REPLACE( FORMAT(B.TSize/POWER(1024,pw),3),',',''),17,' '),' ', SUBSTR(' KMGTP',pw+1,1),'B') "Table Size" FROM (SELECT engine,SUM(data_length) DSize,SUM(index_length) ISize, SUM(data_length+index_length) TSize FROM information_schema.tables WHERE table_schema NOT IN ('mysql','information_schema','performance_schema') AND engine IS NOT NULL GROUP BY engine WITH ROLLUP) B, (SELECT 3 pw) A ORDER BY TSize;
踩到的雷
- 用 InnoDB 會遇到 ibdata1 一直長大
- 參考:ibdata1 grows exponentially when innodb_file_per_table is configured
/var/lib/mysql$ sudo du -s $(pwd)/* | sort -n | tail -n 2 33232756 /var/lib/mysql/ibdata1 # 32G 49176852 /var/lib/mysql/dashboard # 49G
- 參考:ibdata1 grows exponentially when innodb_file_per_table is configured
- 用 InnoDB 必須下 OPTIMZE 才能完全釋出已經刪除 record 的硬碟空間,而且執行 OPTIMIZE TABLE 時,需要有足夠的硬碟空間(因為會產生一個新的資料表檔案,然後複製資料進去,再刪掉舊的資料表檔案)
Last modified 7 years ago
Last modified on Feb 26, 2018, 2:27:20 PM