# \[Closed\] Optimizing pt-archiver for row deletion ( 5.7.44-48 Percona Server)

**URL:** <https://forums.percona.com/t/closed-optimizing-pt-archiver-for-row-deletion-5-7-44-48-percona-server/36149>\
**Category:** Percona Server for MySQL 5.7\
**Created:** [January 15, 2025, 7:00am UTC](https://forums.percona.com/t/closed-optimizing-pt-archiver-for-row-deletion-5-7-44-48-percona-server/36149 "2025-01-15T07:00:40Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![Jean\_Siebrits](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/jean_siebrits/32/18715_2.png) [@Jean\_Siebrits](https://forums.percona.com/u/Jean_Siebrits)\
**Post date:** [January 15, 2025, 7:00am UTC](https://forums.percona.com/t/closed-optimizing-pt-archiver-for-row-deletion-5-7-44-48-percona-server/36149/1 "2025-01-15T07:00:40Z")

</div>

Good day

We are running a MySQL version 5.7.44-48 Percona Server.

We have a Zabbix database which has overgrown and I would like to purge all rows out of a table called “history\_uint” that is older than 90 days.

Unfortunately I don’t have much of an index for that table

```auto
+--------------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+--------------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| history_uint | 1 | history_uint_1 | 1 | itemid | A | 47773492 | NULL | NULL | | BTREE | | |
| history_uint | 1 | history_uint_1 | 2 | clock | A | 28728586240 | NULL | NULL | | BTREE | | |
+--------------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

```

I’ve done some research and trial & error, and so far this formulated command is running okay:

```auto
pt-archiver --source h="127.0.0.1",P="3306",D="zabbix",t="history_uint",u="XXXXXXXX",p="XXXXXXXX",i="history_uint_1" --commit-each --ignore --limit "5000" --no-check-charset --nocheck-columns --nosafe-auto-increment --progress "5000" --purge --retries 3 --statistics --where "itemid != 0 AND clock <= UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 91 DAY))" --run-time 23h

```

I would like to find out from the community if there is any more optimization I can do with the command to get even more performance out of purging the rows from the table?

Changing the `--limit` value to something higher or lower, seems to give me slower deletion results.

Adding the `--bulk-delete` flag, in my experience, is causing too many locks, and even some deadlocks, which forced me to kill the process, and restart without the flag.  
I might not have the correct additional flags to properly use it?

Any insight will be appreciated.

Thank you in advance!

---

<div class="post-metadata">

**Author:** ![Yunus](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/yunus/32/6430_2.png) [@Yunus](https://forums.percona.com/u/Yunus)\
**Post date:** [January 15, 2025, 9:01am UTC](https://forums.percona.com/t/closed-optimizing-pt-archiver-for-row-deletion-5-7-44-48-percona-server/36149/2 "2025-01-15T09:01:48Z")

</div>

> [@Jean\_Siebrits](#):
>
> ```auto
> pt-archiver --source h="127.0.0.1",P="3306",D="zabbix",t="history_uint",u="XXXXXXXX",p="XXXXXXXX",i="history_uint_1" --commit-each --ignore --limit "5000" --no-check-charset --nocheck-columns --nosafe-auto-increment --progress "5000" --purge --retries 3 --statistics --where "itemid != 0 AND clock <= UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 91 DAY))" --run-time 23h
> 
> ```

You should try with a lower limit set and with --bulk-delete.  
The bulk delete option will delete the rows in single statement and will be more faster.  
The delete will take sometime and should be fine as it is history unit.

–primary-key-only is also a good option to try with the delete statements.

–primary-key-only[¶](https://docs.percona.com/percona-toolkit/pt-archiver.html#cmdoption-pt-archiver-primary-key-only)

Primary key columns only.  
A shortcut for specifying [`--columns`](https://docs.percona.com/percona-toolkit/pt-archiver.html#cmdoption-pt-archiver-columns) with the primary key columns. This is an efficiency if you just want to purge rows; it avoids fetching the entire row, when only the primary key columns are needed for `DELETE` statements. See also [`--purge`](https://docs.percona.com/percona-toolkit/pt-archiver.html#cmdoption-pt-archiver-purge).

You can also use --dry-run to check how the query is formed.

---

<div class="post-metadata">

**Author:** ![Jean\_Siebrits](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/jean_siebrits/32/18715_2.png) [@Jean\_Siebrits](https://forums.percona.com/u/Jean_Siebrits)\
**Post date:** [January 15, 2025, 9:58am UTC](https://forums.percona.com/t/closed-optimizing-pt-archiver-for-row-deletion-5-7-44-48-percona-server/36149/3 "2025-01-15T09:58:10Z")

</div>

Hi Yunus,

Thank you for the reply

Unfortunately the table has no primary index (I don’t know why Zabbix did not add one), and we are so bloated, that I can’t add any.

```auto
--primary-key-only was specified by the --source table `zabbix`.`history_uint` does not have a PRIMARY KEY at /usr/bin/pt-archiver line 6427.

```

The `--bulk-delete` option locks up the table completely, and seems to stop pt-archiver as well as nothing happens.  
This then causes Zabbix to stop adding values to itself (no history \ graphing).

I take then that the command is as optimized as I can get it.

---

<div class="post-metadata">

**Author:** ![matthewb](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/matthewb/32/34_2.png) [@matthewb](https://forums.percona.com/u/matthewb)\
**Post date:** [January 15, 2025, 8:58pm UTC](https://forums.percona.com/t/closed-optimizing-pt-archiver-for-row-deletion-5-7-44-48-percona-server/36149/4 "2025-01-15T20:58:18Z")

</div>

> [@Jean\_Siebrits](#):
>
> and we are so bloated, that I can’t add any.

What does this mean exactly? Are you out of disk space? You should know that simply deleting rows **will not** reclaim disk space. Any deleted rows will simply be marked “free” and used again by the next inserts. To truly reclaim disk space, you must `ALTER TABLE history_uint ENGINE=InnoDB` _after_ you delete the rows.

---

<div class="post-metadata">

**Author:** ![Jean\_Siebrits](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/jean_siebrits/32/18715_2.png) [@Jean\_Siebrits](https://forums.percona.com/u/Jean_Siebrits)\
**Post date:** [January 16, 2025, 4:28am UTC](https://forums.percona.com/t/closed-optimizing-pt-archiver-for-row-deletion-5-7-44-48-percona-server/36149/5 "2025-01-16T04:28:05Z")

</div>

Good day, Matthew

Yes, we are nearing filling the disk, and I am very aware it won’t reclaim actual disk space, but the MySQL engine will re-use space that was cleared out of the database.

I’m trying to get the server to a position where it will at least not eat any more additional “real” disk space, while we are working on a re-build of the environment.  
So trying to buy time.

---

<div class="post-metadata">

**Author:** ![matthewb](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/matthewb/32/34_2.png) [@matthewb](https://forums.percona.com/u/matthewb)\
**Post date:** [January 16, 2025, 5:00am UTC](https://forums.percona.com/t/closed-optimizing-pt-archiver-for-row-deletion-5-7-44-48-percona-server/36149/6 "2025-01-16T05:00:18Z")

</div>

> [@Jean\_Siebrits](#):
>
> but the MySQL engine will re-use space

You are correct on the space re-use, as I mentioned that above. Just to clarify, MySQL isn’t an engine; InnoDB is the engine.

> [@Jean\_Siebrits](#):
>
> I take then that the command is as optimized as I can get it.

Since you don’t have PK to utilize, yea, this will be as good as you can get.