关于千万级数据的删除写入问题

millerrch

现在有一个表，用来存放设备的状态日志，状态日志是20秒写入一次一般，当状态日志数据有千万条左右时，如果想删除某个时刻以前的数据，比如删个几百万条，删除数据的脚本就会一直执行没反应，而且此时无法写入新的状态日志数据也
如果只删除几十万条数据，则可以写入新的数据，但是删除数据的操作没法进行，报错Lock wait timeout exceeded; try restarting transaction
是因为删除的数据量过大，提交删除数据时，耗时太久的缘故么？还是别的原因，望达人赐教啊，THX~~


顺便问个别的问题，我导出这个大数据的表正常，不过有600多M，但是导入另一个mysql就报错 Got a packet bigger than \max_allowed_packet\ bytes

我把/etc/my.cnf中的max-allowed-packet，改成32M了，重启mysql不管用啊，看还有人说得改my.ini，这个文件在哪儿？是这个问题么？改成多大合适，先谢谢了哈，改成800M以后报了一个其中一条数据的错误，然后再看表里现在也有数据了，导出后才200多M，数据变成200多W了···

andrefun

可见楼主还没分清my.ini是windows的配置文件，/etc/my.cnf是linux的配置文件。。。

你这个日志的设计就是不合理的。无论怎么样优化，都是这个样子。如果要想性能上去，分表吧。

导入导出的问题，我不清楚你具体怎么做的，估计也是命令使用不合理，哈哈。

eugene_jin

回复 1# millerrch


  关于修改my.cnf还是my.ini见楼上兄弟所言


阅读到的重要信息：
1.用来存放设备的状态日志，状态日志是20秒写入一次一般
2.对于需要删除大量数据，且是某个时间点之前的
----------------------------------------------------------------

分析、猜测：
我是否可以理解为只有INSERT操作每条数据的写入应该都是有写入时间，对吧...

做法有2种：

方法一：
1.存储表引擎为InnoDB引擎的表
2.时间字段上必须创建索引

方法二：
1.使用分区表
2.若是按天，删除的数据，则按天进行分区
3.若是按小时，则删除的数据，则按小时进行分区
4.时间字段必须为主键或主键的一部分，或则至少创建索引

liyihongcug

innodb_log_file_size  这个要加大

用存储过程 分段删除提交  参看oralce大表 删除

909413335

INSERT和DELETE操作，都会给表加X锁，如果删除的范围过广，或者没有用到索引，那么就会全表加X锁，这时候两个操作就只有一个能执行，另外一个等待锁释放。

比较科学的办法是在时间字段上加索引，更快的定位到要删除的记录，然后一次删除的记录可以少点，多分几次删除。

用分区表也是个很好的办法，删除历史记录，速度无与伦比。