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?
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 columns only.
A shortcut for specifying --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.
You can also use --dry-run to check how the query is formed.
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.
--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.
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=InnoDBafter you delete the rows.
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.