目录
mysql 目录结构
-rw-r-----. 1 mysql mysql 56 Oct 24 22:36 auto.cnf
-rw-r----- 1 mysql mysql 352 Oct 24 22:39 ib_buffer_pool
-rw-r-----. 1 mysql mysql 12582912 Oct 24 22:39 ibdata1
-rw-r-----. 1 mysql mysql 50331648 Oct 24 22:39 ib_logfile0
-rw-r-----. 1 mysql mysql 50331648 Oct 24 22:36 ib_logfile1
-rw-r----- 1 mysql mysql 12582912 Oct 24 22:39 ibtmp1
drwxr-x---. 2 mysql mysql 4096 Oct 24 22:36 mysql
drwxr-x---. 2 mysql mysql 4096 Oct 24 22:36 performance_schema
drwxr-x---. 2 mysql mysql 12288 Oct 24 22:36 sys
redo重做日志文件:ib_logfile0,ib_logfile1 是InnoDB引擎持有的,用于记录InnoDB引擎下的事务的日志,它记录每页更改的物理情况。
一、Mysql相关优化参数有哪些?
1.查询独立表空间是否开启
mysql> show variables like 'innodb_file_per_table%';
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| innodb_file_per_table | ON |
+-----------------------+-------+
1 row in set (0.01 sec)
2.查询共享表空间的文件信息
mysql> show variables like 'innodb_data%';
+-----------------------+------------------------+
| Variable_name | Value |
+-----------------------+------------------------+
| innodb_data_file_path | ibdata1:12M:autoextend |
| innodb_data_home_dir | |
+-----------------------+------------------------+
2 rows in set (0.00 sec)
3.查询undo log相关信息。
mysql> show variables like '%undo%';
+--------------------------+------------+
| Variable_name | Value |
+--------------------------+------------+
| innodb_max_undo_log_size | 1073741824 |
| innodb_undo_directory | ./ |
| innodb_undo_log_truncate | OFF |
| innodb_undo_logs | 128 |
| innodb_undo_tablespaces | 0 |
+--------------------------+------------+
5 rows in set (0.00 sec)
4.临时表空间
mysql> show variables like '%innodb_temp_data_file_path%';
+----------------------------+-----------------------+
| Variable_name | Value |
+----------------------------+-----------------------+
| innodb_temp_data_file_path | ibtmp1:12M:autoextend |
+----------------------------+-----------------------+
1 row in set (0.00 sec)
5.slow.log 慢查询日志
mysql> show variables like '%slow_query_log%';
+---------------------+-----------------------------------------------+
| Variable_name | Value |
+---------------------+-----------------------------------------------+
| slow_query_log | OFF |
| slow_query_log_file | /datah/data/mysqldata/mysql/master01-slow.log |
+---------------------+-----------------------------------------------+
2 rows in set (0.00 sec)mysql> show variables like '%long_query_time%';
+-----------------+-----------+
| Variable_name | Value |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
1 row in set (0.00 sec)
6.记录客户端连接和运行的语句。
mysql> show variables like '%general%';
+------------------+------------------------------------------+
| Variable_name | Value |
+------------------+------------------------------------------+
| general_log | OFF |
| general_log_file | /datah/data/mysqldata/mysql/master01.log |
+------------------+------------------------------------------+
2 rows in set (0.00 sec)mysql>
7.查看当前的事务隔离级别
提示:READ-UNCOMMITTED(读取未提交),READ-COMMITTED(读取可提交), REPEATABLE-READ(可重复读取),serializable(序列化)
mysql> show variables like 'tx_isolation';
+---------------+-----------------+
| Variable_name | Value |
+---------------+-----------------+
| tx_isolation | REPEATABLE-READ |
+---------------+-----------------+
1 row in set (0.00 sec)mysql>
8.查看等待锁的超时时间
mysql> show variables like '%innodb_lock_wait%';
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| innodb_lock_wait_timeout | 50 |
+--------------------------+-------+
1 row in set (0.00 sec)mysql>
9.Innodb 临时表优化
mysql> show variables like '%innodb_temp%';
+----------------------------+-----------------------+
| Variable_name | Value |
+----------------------------+-----------------------+
| innodb_temp_data_file_path | ibtmp1:12M:autoextend |
+----------------------------+-----------------------+
1 row in set (0.00 sec)mysql>
二、服务器篇
1.设置内核参数 vm.swappiness=1
后期会持续更新中.....