调整InnoDB系统表空间大小

调整InnoDB系统表空间大小

本文介绍如何增大或缩小InnoDB system tablespace

增大InnoDB system tablespace

最简单的增大InnoDB system tablespace大小的方法是在一开始配置的时候就指定为自动扩展. 为innodb_data_file_path参数中的最后一个数据文件指定autoextend选项. InnoDB在空间不足时以64MB为单位自动增加该文件的大小. 可以通过设置innodb_autoextend_increment系统变量的值(以兆字节为单位)来更改增量大小.

您可以通过添加另一个数据文件来扩展系统表空间:

1.关闭MySQL
2.如果上一个数据文件是使用关键字autoextend定义的,则根据实际增长的大小将其定义更改为使用固定大小. 检查数据文件的大小,将其舍入到1024×1024字节(= 1MB)的最接近的倍数,并在innodb_data_file_path中显式指定舍入后的大小.
3.将新的数据文件添加到innodb_data_file_path的末尾,可以指定该文件为自动扩展. 注意,只能将innodb_data_file_path中的最后一个数据文件指定为自动扩展.
4.启动MySQL

实际例子:

初始只有一个ibdata1,现在我们想增加一个数据文件

1
2
innodb_data_home_dir =
innodb_data_file_path = /ibdata/ibdata1:10M:autoextend

假设ibdata1此时已经增长到988M,那么修改配置为

1
2
innodb_data_home_dir =
innodb_data_file_path = /ibdata/ibdata1:988M;/disk2/ibdata2:50M:autoextend

启动MySQL后,ibdata2会被初始化

1
2
3
2017-08-11T10:27:06.014446+08:00 0 [Note] InnoDB: Need to create a new innodb_system data file 'ibdata2'.
2017-08-11T10:27:06.014567+08:00 0 [Note] InnoDB: Setting file './ibdata2' size to 50 MB. Physically writing the file full; Please wait ...
2017-08-11T10:27:06.182464+08:00 0 [Note] InnoDB: File './ibdata2' size is now 50 MB.

缩小InnoDB system tablespace

您不能从系统表空间中删除数据文件. 要减少系统表空间大小,请使用以下过程:

1.使用mysqldump来转储所有的InnoDB表,包括位于MySQL数据库中的InnoDB表.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
mysql> SELECT TABLE_NAME from INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='mysql' and ENGINE='InnoDB';
+---------------------------+
| TABLE_NAME |
+---------------------------+
| engine_cost |
| gtid_executed |
| help_category |
| help_keyword |
| help_relation |
| help_topic |
| innodb_index_stats |
| innodb_table_stats |
| plugin |
| server_cost |
| servers |
| slave_master_info |
| slave_relay_log_info |
| slave_worker_info |
| time_zone |
| time_zone_leap_second |
| time_zone_name |
| time_zone_transition |
| time_zone_transition_type |
+---------------------------+

2.关闭MySQL
3.删除所有现有的表空间文件( .ibd),包括ibdata和ib_log文件. 不要忘记删除位于MySQL数据库中的表的 .ibd文件.
4.删除InnoDB表的任何.frm文件.
5.配置新的表空间.
6.重启MySQL
7.导入dump文件

Note
如果您的数据库仅使用InnoDB引擎,可能会更容易地转储所有数据库,停止服务器,删除所有数据库和InnoDB日志文件,重新启动服务器以及导入转储文件.

MySQL数据库设计规范

MySQL数据库设计规范

MySQL数据库与Oracle、sqlserver等数据库相比,有其内核上的优势与劣势。我们在使用MySQL数据库的时候需要遵循一定规范,扬长避短。本规范旨在帮助或指导RD、QA、OP等技术人员做出适合线上业务的数据库设计。在数据库变更和处理流程、数据库表设计、SQL编写等方面予以规范,从而为公司业务系统稳定、健康地运行提供保障。

数据库设计

以下所有规范会按照【高危】、【强制】、【建议】三个级别进行标注,遵守优先级从高到低。
对于不满足【高危】和【强制】两个级别的设计,DBA会强制打回要求修改。

库名

1.【强制】库的名称必须控制在32个字符以内,相关模块的表名与表名之间尽量提现join的关系,如user表和user_login表。
2.【建议】库的名称格式:业务系统名称子系统名,同一模块使用的表名尽量使用统一前缀。
**3.【强制】一般分库名称命名格式是“库通配名
编号”,编号从“0”开始递增,比如“wenda001” **
以时间进行分库的名称格式是“库通配名
时间”
4.【强制】创建数据库时必须显式指定字符集,并且字符集只能是utf8或者utf8mb4

创建数据库SQL举例:

1
Create database db1 default character set utf8;

表结构

1.【强制】表和列的名称必须控制在32个字符以内,表名只能使用字母、数字和下划线,一律小写。
2.【建议】表名要求模块名强相关,如师资系统采用”sz”作为前缀,渠道系统采用”qd”作为前缀等。
3.【强制】创建表时必须显式指定字符集为utf8或utf8mb4。
4.【强制】创建表时必须显式指定表存储引擎类型,如无特殊需求,一律为InnoDB。

当需使用除InnoDB以外的存储引擎时,必须通过DBA审核才能在生产环境中使用

因为Innodb表支持事务、行锁、宕机恢复、MVCC等关系型数据库重要特性,为业界使用最多的MySQL存储引擎。而这是其他大多数存储引擎不具备的,因此首推InnoDB

阅读全文

数据分布不均匀走HASH JOIN导致的性能问题

这个案例是JAVA开发说一个存储过程跑的很慢,之后我跑这个过程,然后通过脚本抓出了慢的SQL

表大小

1
2
3
tb_user_channel --1W
tb_channel_info --1W
base_data_login_info 19W

就是这条SQL,跑完要7分钟。base_data_login_info本来是@db_link,但是我在本地建了一个同样的表发现还是7分钟左右,所以排除了可能是由于db_link造成问题的可能性

1
2
3
4
5
6
select
count(distinct a.user_name),count(distinct a.invest_id)
from base_data_login_info a
where a.str_day <= '20160304' and a.str_day >= '20160301'
and a.channel_id in (select channel_rlat from tb_user_channel a, tb_channel_info b where a.channel_id = b.channel_id and a.user_id = 5002)
and a.platform = a.platform

看一下这个sql返回多少行,结果秒杀,瞬间就出结果了

1
2
3
4
5
6
select
count(*)
from base_data_login_info@agent a
where a.str_day <= '20160304' and a.str_day >= '20160301'
and a.channel_id in (select channel_rlat from tb_user_channel a, tb_channel_info b where a.channel_id = b.channel_id and a.user_id = 5002)
and a.platform = a.platform

45122行
之后单独跑

1
2
3
4
5
6
select
count(distinct a.user_name),count( a.invest_id)
from base_data_login_info a
where a.str_day <= '20160304' and a.str_day >= '20160301'
and a.channel_id in (select channel_rlat from tb_user_channel a, tb_channel_info b where a.channel_id = b.channel_id and a.user_id = 5002)
and a.platform = a.platform


1
2
3
4
5
6
select
count( a.user_name),count(distinct a.invest_id)
from base_data_login_info a
where a.str_day <= '20160304' and a.str_day >= '20160301'
and a.channel_id in (select channel_rlat from tb_user_channel a, tb_channel_info b where a.channel_id = b.channel_id and a.user_id = 5002)
and a.platform = a.platform

都是秒杀
单独count distinct user_name 或 invest_id 都很快 ,一起count distinct就很慢了
那么这时候其实已经可以通过改写SQL提示性能了,改写如下

阅读全文

视图合并、hash join连接列数据分布不均匀引发的惨案

表大小

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
SQL> select count(*) from agent.TB_AGENT_INFO;
COUNT(*)
----------
1751
SQL> select count(*) from TB_CHANNEL_INFO ;
COUNT(*)
----------
1807
SQL> select count(*) from TB_USER_CHANNEL;
COUNT(*)
----------
7269
SQL> select count(*) from OSS_USER_STATION;
COUNT(*)
----------
2149
SQL> select count(*) from tb_user_zgy ;
COUNT(*)
----------
43
SQL> select count(*) from act.tb_user_agent_relat;
COUNT(*)
----------
29612
SQL> select count(*) from agent.base_data_user_info ;
COUNT(*)
----------
30005
SQL> select count(*) from agent.base_data_invest_info;
COUNT(*)
----------
3530163

慢的sql

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
select a.city,
a.agent_id,
a.username,
a.real_name,
phone,
zgy_name,
login_count,
user_count,
count(distinct b.invest_id) user_invested,
sum(b.order_amount / 100) invest_amount
from (select a.city,
a.agent_id,
a.username,
a.real_name, -- 业主姓名
a.phone, -- 业主手机号
d.real_name zgy_name, -- 所属专管员
count(distinct case
when c.str_day <= '20160821' then
c.login_name
end) login_count,
count(distinct case
when c.str_day <= '20160821' then
decode(c.status, 1, c.invest_id, null)
end) user_count
from (select agent_id, city, username, real_name, phone
from agent.TB_AGENT_INFO
where agent_id in
(SELECT agent_id
FROM (SELECT distinct *
FROM TB_CHANNEL_INFO t
START WITH t.CHANNEL_ID in
(select CHANNEL_ID
from TB_USER_CHANNEL
where USER_ID = 596)
CONNECT BY PRIOR
t.CHANNEL_ID = t.PARENT_CHANNEL_ID)
WHERE agent_id IS NOT NULL)) a
left join oss_user_station e
on a.agent_id = e.agent_id
and e.user_type = 0
left join tb_user_zgy d
on e.username = d.username
left join act.tb_user_agent_relat c
on a.agent_id = c.agent_id
group by a.city,
a.username,
a.real_name,
a.phone,
d.real_name,
a.agent_id) a
left join (select invest_id, order_amount, agent_id, str_day
from agent.base_data_invest_info
where str_day >= '20150801' and str_day<='20160821') b
on a.agent_id = b.agent_id
group by a.city,
a.agent_id,
a.username,
a.real_name,
a.phone,
a.zgy_name,
a.login_count,
a.user_count
这个查询可以看成两部分,第一部分一堆小表关联的a和唯一的一个大表再做关联
man
----------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 55M| 6616M| | 3934K (1)| 13:06:52 |
| 1 | HASH GROUP BY | | 55M| 6616M| | 3934K (1)| 13:06:52 |
| 2 | VIEW | VW_DAG_1 | 55M| 6616M| | 3934K (1)| 13:06:52 |
| 3 | HASH GROUP BY | | 55M| 6301M| 7681M| 3934K (1)| 13:06:52 |
| 4 | VIEW | VM_NWVW_0 | 55M| 6301M| | 2456K (1)| 08:11:15 |
| 5 | SORT GROUP BY | | 55M| 10G| 11G| 2456K (1)| 08:11:15 |
|* 6 | HASH JOIN RIGHT OUTER | | 55M| 10G| | 21643 (2)| 00:04:20 |
| 7 | TABLE ACCESS FULL | TB_USER_AGENT_RELAT | 27937 | 1200K| | 102 (0)| 00:00:02 |
|* 8 | HASH JOIN OUTER | | 3374K| 511M| | 21392 (1)| 00:04:17 |
|* 9 | HASH JOIN SEMI | | 1712 | 188K| | 2007 (1)| 00:00:25 |
|* 10 | HASH JOIN RIGHT OUTER | | 1712 | 173K| | 32 (0)| 00:00:01 |
| 11 | TABLE ACCESS FULL | TB_USER_ZGY | 43 | 903 | | 3 (0)| 00:00:01 |
|* 12 | HASH JOIN RIGHT OUTER | | 1712 | 138K| | 29 (0)| 00:00:01 |
|* 13 | TABLE ACCESS FULL | OSS_USER_STATION | 1075 | 25800 | | 6 (0)| 00:00:01 |
| 14 | TABLE ACCESS FULL | TB_AGENT_INFO | 1712 | 98K| | 23 (0)| 00:00:01 |
| 15 | VIEW | VW_NSO_1 | 16271 | 143K| | 1975 (1)| 00:00:24 |
|* 16 | VIEW | | 16271 | 143K| | 1975 (1)| 00:00:24 |
| 17 | HASH UNIQUE | | 16271 | 8882K| 10M| 1975 (1)| 00:00:24 |
|* 18 | CONNECT BY WITHOUT FILTERING (UNIQUE)| | | | | | |
|* 19 | HASH JOIN RIGHT SEMI | | 530 | 146K| | 29 (0)| 00:00:01 |
|* 20 | TABLE ACCESS FULL | TB_USER_CHANNEL | 600 | 7800 | | 7 (0)| 00:00:01 |
| 21 | TABLE ACCESS FULL | TB_CHANNEL_INFO | 1807 | 476K| | 22 (0)| 00:00:01 |
| 22 | TABLE ACCESS FULL | TB_CHANNEL_INFO | 1807 | 476K| | 22 (0)| 00:00:01 |
|* 23 | TABLE ACCESS FULL | BASE_DATA_INVEST_INFO | 3374K| 148M| | 19375 (1)| 00:03:53 |
----------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
6 - access("AGENT_ID"="C"."AGENT_ID"(+))
8 - access("AGENT_ID"="AGENT_ID"(+))
9 - access("AGENT_ID"="AGENT_ID")
10 - access("C"."USERNAME"="D"."USERNAME"(+))
12 - access("AGENT_ID"="C"."AGENT_ID"(+))
13 - filter("C"."USER_TYPE"(+)=0)
16 - filter("AGENT_ID" IS NOT NULL)
18 - access("T"."PARENT_CHANNEL_ID"=PRIOR "T"."CHANNEL_ID")
19 - access("T"."CHANNEL_ID"="CHANNEL_ID")
20 - filter("USER_ID"=596)
23 - filter("STR_DAY"(+)>='20150801' AND "STR_DAY"(+)<='20160821')

阅读全文

抓跑得慢的SQL,查看正在跑的sql的等待事件

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
select (sysdate-a.logon_time)*24*60 minutes,
a.username,
a.BLOCKING_INSTANCE,
a.BLOCKING_SESSION,
a.program,
a.machine,
a.osuser,
a.status,
a.sid,
a.serial#,
a.event,
a.p1,
a.p2,
a.p3,
a.sql_id,
a.sql_child_number,
b.sql_text
from v$session a, v$sql b
where
a.sql_address = b.address
and a.sql_hash_value = b.hash_value
and a.sql_child_number=b.child_number
and a.event not in ('SQL*Net message from client','Space Manager: slave idle wait')
-- and a.username like '%USERNAME%'
order by 1 desc;

在查执行计划

1
Select * from table(dbms_xplan.display_cursor('91prd0dh0bs8d',0,'ALL'));

Hexo博客配置

Hexo博客配置

基于hexo-theme-hiker主题

先看myblog下的_config.yml完整配置

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
# Hexo Configuration
## Docs: https://hexo.io/docs/configuration.html
## Source: https://github.com/hexojs/hexo/
# Site
title: Fan()
subtitle:
description:
author: a dba's blog
language: zh-CN
timezone: Asia/Shanghai
# URL
## If your site is put in a subdirectory, set url as 'http://yoursite.com/child' and root as '/child/'
url: http://yoursite.com
root: /Fandb.github.io
permalink: :year/:month/:day/:title/
permalink_defaults:
# Directory
source_dir: source
public_dir: public
tag_dir: tags
archive_dir: archives
category_dir: categories
code_dir: downloads/code
i18n_dir: :lang
skip_render:
# Writing
new_post_name: :title.md # File name of new posts
default_layout: post
titlecase: false # Transform title into titlecase
external_link: true # Open external links in new tab
filename_case: 0
render_drafts: false
post_asset_folder: false
relative_link: false
future: true
highlight:
enable: true
line_number: true
auto_detect: true
tab_replace:
# Home page setting
# path: Root path for your blogs index page. (default = '')
# per_page: Posts displayed per page. (0 = disable pagination)
# order_by: Posts order. (Order by date descending by default)
index_generator:
path: ''
per_page: 6
order_by: -date
# Category & Tag
default_category: uncategorized
category_map:
tag_map:
# Date / Time format
## Hexo uses Moment.js to parse and display date
## You can customize the date format as defined in
## http://momentjs.com/docs/#/displaying/format/
date_format: YYYY-MM-DD
time_format: HH:mm:ss
# Pagination
## Set per_page to 0 to disable pagination
per_page: 10
pagination_dir: page
# Extensions
## Plugins: https://hexo.io/plugins/
## Themes: https://hexo.io/themes/
theme: hiker
# Deployment
## Docs: https://hexo.io/docs/deployment.html
deploy:
type: git
repository:
github: https://github.com/Fanduzi/Fandb.github.io.git
coding: https://git.coding.net/Fandb/Fandb.blog.git
branch: master

Note

冒号后面要空一格

阅读全文


Powered by Hexo and Hexo-theme-hiker

Copyright © 2013 - 2018 Fan() All Rights Reserved.

访客数 : | 访问量 :

#