mysql delete删除数据磁盘空间不减少

某天公司腾讯云CDB服务器磁盘空间占用很高,而我们自己查看数据+索引则不多。原来我们日常定时删除操作delete并末释放磁盘空间。

   使用delete删除的时候,mysql并没有把数据文件删除,而是将数据文件的标识位删除,没有整理文件,因此不会彻底释放空间。被删除的数据将会被保存在一个链接清单中,当有新数据写入的时候,mysql会利用这些已删除的空间再写入。即,删除操作会带来一些数据碎片,正是这些碎片在占用硬盘空间。(BTW:看官方文档上好像是innodb引擎的可以利用操作系统来帮忙回收这些碎片,MyISam的表没有办法自己回收,这里待定,后续再看下)

查看数据库上所有表的大小,大概能统计出所占空间大小。

mysql> use information_schema;
Database changed
mysql> SELECT TABLE_NAME,concat(round((DATA_LENGTH/1024/1024),2),'MB'),concat(round((INDEX_LENGTH/1024/1024),2),'MB') FROM `TABLES` ORDER BY DATA_LENGTH DESC;
+----------------------------------------------------+-----------------------------------------------+------------------------------------------------+
| TABLE_NAME                                         | concat(round((DATA_LENGTH/1024/1024),2),'MB') | concat(round((INDEX_LENGTH/1024/1024),2),'MB') |
+----------------------------------------------------+-----------------------------------------------+------------------------------------------------+
| report_copy                                   | 14729.00MB                                    | 18130.00MB                                     |
| s_num                                    | 11392.00MB                                    | 17689.91MB                                     |
| stat                                     | 5452.00MB                                     | 4741.80MB                                      |
| game_log                                      | 3522.00MB                                     | 6235.95MB                                      |

官方推荐使用 OPTIMIZE TABLE命令来优化表,该命令会重新利用未使用的空间,并整理数据文件的碎片。

语法如下:

OPTIMIZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name [, tbl_name] …

注:该命令将会整理表数据和相关的索引数据的物理存储空间,用来减少占用的磁盘空间,并提高访问表时候的IO性能。但是,具体对表产生的影响是依赖于表使用的存储引擎的。该命令对视图无效。

该命令目前只对MyISAM、InnoDB,ARCHIVE的表起作用,其余引擎的不起作用

OPTIMIZE 在操作过程中会锁表,50多G数据表锁了将近1个小时,所以这点要特别注意。执行完毕释放了20G左右的空间,还是非常有效果的


踩过的坑: optimize 本质是alter table

mysql 5.5 的改表过程如下

1.创建一张新的临时表 tmp

2.把旧表锁住,禁止插入删除,只允许读写 (这就是为什么上面的insert语句都停留在waiting for table metadata lock)

3.把数据不断的从旧表,拷贝到新的临时表,(这就是上面报copy to tmp table)

4.等表拷贝完后,进行瞬间的rename操作

5.旧表删除掉

所以optimize最大的问题是锁表,锁表会导致insert,delete,update语句堵住,确保操作时业务切走。

alter table会复制一个表出来,磁盘空间会增加,操作完成合删除旧表。 这一过程可能会爆磁盘
————————————————
版权声明:本文为CSDN博主「qq13650793239」的原创文章,遵循 CC 4.0 BY-SA 版权协议,转载请附上原文出处链接及本声明。
原文链接:https://blog.csdn.net/qq13650793239/java/article/details/84889975

zabbix数据库占用磁盘空间较大的处理方法

du -h /* |sort -nr  使用此命令一步步排查发现/var/lib/mysql/zabbix/这个目录占用磁盘空间较大

发现history_log.ibd这个文件最大,达到了38G,此文件对应的是zabbix库里的history_log表

找到问题原因后就好解决,进入zabbix,删除history_log表较早的数据即可

进入MySQL的zabbix库

delete from history_log where clock < 1540483200;   删除小于这个时间戳的数据

optimize table  history_log     delete操作以后使用optimize table table_name 会立刻释放磁盘空间

df -h   最后再验证一下磁盘空间是否有变化

先删除 history_log.

delete from history_log where clock < 1540483200;   删除小于这个时间戳的数据

optimize table  history_log 

依然空间占用很大。使用以下命令查询tab占用:

MariaDB [zabbix]> SELECT table_name AS “Tables”, round(((data_length + index_length) / 1024 / 1024), 2) “Size in MB” FROM information_schema.TABLES WHERE table_schema = ‘zabbix’ ORDER BY (data_length + index_length) DESC;
————————————–+

Tables                     Size in MB

————————————–+

history_text                 776627.66
history_uint                   7623.14
history                        1819.95
trends_uint                     541.81
history_str                     404.22
trends                          246.09
events                          190.95
alerts                          139.75
auditlog                         36.09
sessions                         30.14
event_recovery                   16.55
history_log                      16.39
items                             7.11
items_applications                2.80

可以看到,history_text占据了绝对空间。

使用以下命令删除7天前的history_text:

delete from history_text where clock <1586361600;

执行时间较长。等待中


参考来源:https://www.cnblogs.com/leon2659/p/9924627.html

Zabbix api获取某 item的最新值

参考下面函数:

def getValueFromHost(host,itemName):
    '''
    :param host: host name,usually ip,like '10.189.66.131',but need to be string type.
    :param itemName: like "*cpu_model*", need to be string type.
    :return: The last value of itemName for specific 'host'. String type.
    '''
    for h in zapi.item.get(output=["itemid", "name", "hostid", "value_type", "lastvalue"], host=host,
                           search={"name": '*'+itemName+'*'}, searchWildcardsEnabled='1'):
        return (h['lastvalue'])

def getValueFromItem(host,item):
    '''
    :param host:
    :param item:
    :return:  return 数组
    '''
    data=[]
    for eachItem in item:
        data.append(getValueFromHost(host,eachItem))
    return data

另外,获取某主机资产清单值,使用以下函数:


def getInventorydataFromHostName(hostname,item):
    for h in zapi.host.get(output=['host'], filter={'host': hostname},
                           selectInventory=[item]):
        return h['inventory'][item]

另外,预先导入:

from pyzabbix import ZabbixAPI, ZabbixAPIException
from datetime import datetime
import time
import warnings

记录zabbix低级自动发现的json格式,以及相应脚本

json格式如下:

{
“data”: [
{
“{#HOSTIP}”: “/var/log/audit”
},
{
“{#HOSTIP}”: “/var/log/chrony”
},
{
“{#HOSTIP}”: “/var/log/cups”
},
{
“{#HOSTIP}”: “/var/log/gdm”
},
{
“{#HOSTIP}”: “/var/log/glusterfs”
},
{
“{#HOSTIP}”: “/var/log/httpd”
},
{
“{#HOSTIP}”: “/var/log/mariadb”
},
{
“{#HOSTIP}”: “/var/log/ntpstats”
},
{
“{#HOSTIP}”: “/var/log/php-fpm”
},
{
“{#HOSTIP}”: “/var/log/pluto”
},
{
“{#HOSTIP}”: “/var/log/ppp”
},
{
“{#HOSTIP}”: “/var/log/qemu-ga”
},
{
“{#HOSTIP}”: “/var/log/sa”
},
{
“{#HOSTIP}”: “/var/log/samba”
},
{
“{#HOSTIP}”: “/var/log/speech-dispatcher”
},
{
“{#HOSTIP}”: “/var/log/sssd”
},
{
“{#HOSTIP}”: “/var/log/tuned”
}
]
}

产生该json格式的python代码参考:

#!/usr/bin/env python3
#encoding=utf-8
import os
import sys
import json

FileDir=’/var/log/’

def CheckHostip(filedir):
output = os.popen(‘find ‘+filedir+’* -maxdepth 0 -type d’)
hostip = str(output.read())
hostip = hostip[:hostip.find(‘\r’)]
hostdirlist = hostip.split(‘\n’)
return (hostdirlist)

def returnjson(fileDir):

thislist=[]
for i in CheckHostip(fileDir):
thislist.append({
“{#HOSTIP}”:i

})
a = {
“data”: thislist
}
print(json.dumps(a, sort_keys=True, indent=2))
return 1

if __name__==”__main__”:
returnjson(FileDir)

记录使用Python监控服务器日志。

后续直接使用了宏,因此这个Python脚本没用了,但是还是觉得有意义,所以记下来。

#!/usr/bin/env python3
#encoding=utf-8
import os
import socket
from pyzabbix import ZabbixAPI, ZabbixAPIException
import sys
import warnings
import time

ServerName=”ServerNameHere”
Username=’usernamehere’
Passwd=’passwdhere’
Url=’https://zabbix.mytlu.cn’
FileDir=’/var/log/’
MonitorFilename=’/log’

def CheckHostip(filedir):
output = os.popen(‘find ‘+filedir+’* -maxdepth 0 -type d’)
hostip = str(output.read())
hostip = hostip[:hostip.find(‘\r’)]
hostdirlist = hostip.split(‘\n’)
return (hostdirlist)

def CheckZabbixItems(LogDir):
warnings.filterwarnings(‘ignore’)
ZABBIX_SERVER = Url
zapi = ZabbixAPI(ZABBIX_SERVER)
zapi.session.verify = False
zapi.login(Username, Passwd)
hostid=zapi.host.get(
filter={
“host”:ServerName
}
)
hostid=hostid[0][‘hostid’]
pre_interfaceid = zapi.hostinterface.get(
hostids=hostid
)
interfaceid = pre_interfaceid[0][‘interfaceid’]
try:
itemget = zapi.item.get(
hostid=hostid,
search={
“key_”: “vfs.file.md5sum[“+LogDir+MonitorFilename+”]”
},
sortfield=”name”
)
print(itemget)
if itemget == []:
print(‘zabbix里没有该监控项,添加中监控项和触发器中…’)
CreateItems(LogDir, interfaceid, hostid)
CreateTrigger(LogDir)
return 1
else:
print(‘zabbix里有该监控项,尝试添加中触发器中…’)
try:
CreateTrigger(LogDir)
except:
return 0
return 1
except Exception as e:
print(‘zabbix里没有该监控项,添加中监控项和触发器中…’)
CreateItems(LogDir,interfaceid,hostid)
CreateTrigger(LogDir)
return 1

def CreateItems(LogDir,interfaceid,hostid):
warnings.filterwarnings(‘ignore’)
ZABBIX_SERVER = Url
zapi = ZabbixAPI(ZABBIX_SERVER)
zapi.session.verify = False
zapi.login(Username,Passwd)
item = zapi.item.create(name=”Monitor_Log:”+LogDir+MonitorFilename,
key_=”vfs.file.md5sum[“+LogDir+MonitorFilename+”]”,
hostid=hostid,
type=0,
value_type=1,
delay=”10s”,
interfaceid=interfaceid
)
print(item)
return 1

def CreateTrigger(LogDir):
warnings.filterwarnings(‘ignore’)
ZABBIX_SERVER = Url
zapi = ZabbixAPI(ZABBIX_SERVER)
zapi.session.verify = False
zapi.login(Username, Passwd)
trigger = zapi.trigger.create(
description=”[“+LogDir+MonitorFilename+”]:Log not changed in 1s”,
expression=”{“+ServerName+”:vfs.file.md5sum[“+LogDir+MonitorFilename+”].change()}=0″,
priority=’4′,
manual_close=’1′

)
print(trigger)
return 1

if __name__==’__main__’:
hostdirlist=CheckHostip(FileDir)
print(hostdirlist)
for i in hostdirlist:
CheckZabbixItems(i)

Zabbix 监控Mysql Slave

  1. userparameter_mysql_slave_status.conf 放置到 /etc/zabbix/zabbix_agent.d/下。

详细配置内容:

# For all the following commands HOME should be set to the directory that has .my.cnf file with password information.

# Flexible parameter to grab global variables. On the frontend side, use keys like mysql.status[Com_insert].
# Key syntax is mysql.status[variable].
UserParameter=mysql.slave.status[*], echo ‘show slave status\G;’ | mysql | awk ‘$$1 ~ /$1/ {print $$2}’

# Flexible parameter to determine database or table size. On the frontend side, use keys like mysql.size[zabbix,history,data].
# Key syntax is mysql.size[,

,].
# Database may be a database name or “all”. Default is “all”.
# Table may be a table name or “all”. Default is “all”.
# Type may be “data”, “index”, “free” or “both”. Both is a sum of data and index. Default is “both”.
# Database is mandatory if a table is specified. Type may be specified always.
# Returns value in bytes.
# ‘sum’ on data_length or index_length alone needed when we are getting this information for whole database instead of a single table

2. 修改上述配置文件中 UserParameter=mysql.slave.status[*], echo ‘show slave status\G;’ | mysql | awk ‘$$1 ~ /$1/ {print $$2}’

具体来说,如果.my.cnf在 /etc/zabbix下。那么改为:

UserParameter=mysql.slave.status[*], echo ‘show slave status\G;’ |HOME=/etc/zabbix mysql | awk ‘$$1 ~ /$1/ {print $$2}’

参考文档