mysql数据库空间的influxDB增量统计

业务系统中RDS的mysql的空间捉襟见肘时就会想起来需要做一下容量的统计和趋势监控 实施的方案是:

定时用python从mysql的information_schema库中的TABLES表中抽取DATA_LENGTH+INDEX_LENGTH的数据以及DATA_FREE的数据来做统计项 将数据抽取到influxDB中备查

核心的查询语句:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
        # 1048576 字节 等于 1MB
        # 监控门槛:
        # DATA_FREE>= 6MB (可优化空间大于6MB)
        # TABLE_ROWS>= 100k (10万行以上)
        # DATA_LENGTH>= 20MB (数据空间占用大于20MB)
        # INDEX_LENGTH>= 20MB (索引空间占用大于20MB)
        # `TABLE_SCHEMA` IN ('{"','".join(self.check_dbs)}')
        sql = f""" SELECT `TABLE_SCHEMA`,`TABLE_NAME`, CONCAT_WS('.',`TABLE_SCHEMA`,`TABLE_NAME`) AS DBTABLENAME,
(`DATA_FREE`/1048576) AS free_len_mb,
(`DATA_LENGTH`+ `INDEX_LENGTH`)/1048576 AS row_len_mb,
(`TABLE_ROWS`/1000) AS rows_kb ,
(`DATA_LENGTH`/1048576) AS dat_len_mb,
(`INDEX_LENGTH`/1048576) AS idx_len_mb
FROM `information_schema`.`TABLES` WHERE
`TABLE_SCHEMA` IN ('{"','".join(self.check_dbs)}')
AND (`DATA_FREE`>=6291456 OR TABLE_ROWS >= 100000 OR DATA_LENGTH>=20971520 OR INDEX_LENGTH>=15728640 )
ORDER BY free_len_mb DESC,row_len_mb DESC,dat_len_mb DESC,idx_len_mb DESC, `TABLE_SCHEMA` ASC,`TABLE_NAME` ASC
"""

然后单行的influxdb的表结构:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
    tsdb_item_record = {
        'measurement': 'dbtable_stats',
        'tags': {
            'db': db,
            'table':table,
            'dbtable':dbtable,
        },
        'time': f'{utcnow.strftime("%Y-%m-%dT%H:%M:%S")}Z',
        'fields': {
            'free_len_mb':free_len_mb,
            'row_len_mb':row_len_mb,
            'rows':rows_kb,
            'dat_len_mb':dat_len_mb,
            'idx_len_mb':idx_len_mb
        }
    }

这样在influxdb中我们就有了每个埋点间隔下统计的数据信息,接下来在grafana中可以用InfluxDB的查询函数统计出对应的信息并展示图表

按时间查询各个表的空间占用(数据+索引)的增长量:

1
SELECT cumulative_sum(difference(mean("row_len_mb"))) FROM "dbspace_watches_rp"."dbtable_stats" WHERE $timeFilter GROUP BY time(5m), "dbtable" fill(null)

再加上一下查询起始时的值就是当下的总数据量的表数据空间排名:

1
SELECT first("row_len_mb") + cumulative_sum(difference(mean("row_len_mb"))) FROM "dbspace_watches_rp"."dbtable_stats" WHERE $timeFilter GROUP BY time(5m), "dbtable" fill(null)

image-20200826173247724

同理也可以得到库表的DATA_FREE也就是可优化空间的增长量和排名在此不再赘述 另外提一句,mysql在做optmize释放空间的时候会锁表,所以还是在业务空闲期做这些释放空间的活比较好

另外可以顺便求一下倒数得到查询时间区间内的各表的增长率,方便发现特别高增长的表做对应的优化

1
SELECT derivative(cumulative_sum(difference(mean("row_len_mb"))), 10m) FROM "dbspace_watches_rp"."dbtable_stats" WHERE $timeFilter GROUP BY time(1h), "dbtable" fill(null)

OpenCv开发中所遇到的问题备忘

1.qt库冲突的问题:

1
2
3
You might be loading two sets of Qt binaries into the same process. Check that all plugins are compiled against the right Qt binaries. Export DYLD_PRINT_LIBRARIES=1 and check that only one set of binaries are being loaded.
QObject::moveToThread: Current thread (0x7ff56d74feb0) is not the object's thread (0x7ff570dfec60).
Cannot move to target thread (0x7ff56d74feb0)

运行conda环境下opencv时遇到的,查了一下大概是需要卸载了opencv-python后再安装一下opencv-python-headless的版本

1
2
pip uninstall opencv-python
pip install opencv-python-headless

安装时长不够就用:

1
pip install --default-timeout=50000  opencv-python-headless

参考: https://www.pythonheidong.com/blog/article/410902/

FastApi开发中所遇到的问题备忘

1. 需要fastapi的服务端提供静态文件:

1
app.mount("/static", StaticFiles(directory=os.path.join(myapp.env().get_data_path(), 'static')), name="static")

其中第一个static是网站访问路径,第二个static是磁盘路径,第三个static是FastApi代码内部引用名称

2. 文件上传处理:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
async def file_upload(
         upfile: UploadFile = File(...)
):
    dest_file_path = 'uploaded/userfile.ext'
    destination = Path(dest_file_path)
    try:
        with destination.open("wb") as buffer:
            shutil.copyfileobj(upfile.file, buffer)
    finally:
        upload_file.file.close()

3. cors问题

服务端配置cros:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
origins = [
    #  "*"
    "https://localhost"
    , "http://localhost"
    , "http://localhost:8000"
    , "https://www.kumaxiong.com"
]
app.add_middleware(
    CORSMiddleware,
    allow_origins=origins,
    allow_credentials=True,
    allow_methods=["*"],
    # allow_methods=["DELETE", "GET", "POST", "PUT"],
    allow_headers=["*"],
)

客户端处理的时候不要with-credentials带上cookie等信息:

参考:https://blog.csdn.net/liyuling52011/article/details/80013725