MySQL: Why is DELETE more CPU intensive than INSERT?

Question

I am currently taking a course "Evaluating Performance" at the university, and now we are completing a task in which we test CPU usage on a PHP and MySQL server. We use httperf to create custom traffic and vmstat to track server load. We run 3,000 connections to the PHP server for both INSERT and DELETE (run separately).

Numbers show that DELETE is much more processor intensive than INSERT - and I'm just wondering why?

Initially, I thought that INSERT requires more CPU usage, since indexes will need to be recreated, data must be written to disk, etc. But obviously I'm wrong, and I wonder if anyone can tell me the technical reason for this.

Answer 1

At least with InnoDB (and I hope you have them), you have more operations , even without foreign keys . The insert is something like this:

• Insert row
• Mark in binary log buffer
• Mark commit

Deletions do the following:

• Mark the deleted row (with the same hit as the insert - the page has been rewritten)
• Mark in binary log buffer
• Mark
• Actually, delete the line, (rewriting the same hit as the insert page)
• Clear thread deletion in binary log buffer.

For this, you have doubled the work to delete, not insert. Deletion requires these two entries, because they must be marked as deleted for all versions that go forward, but can only be deleted when the transactions do not remain, which they will see. Since InnoDB only writes complete blocks to disk, the change penalty for a block is constant.

Answer 2

DELETE also requires that data be written to disk, as well as recounting indices and, in addition, a set of logical comparisons to find the records (s) that you are trying to delete in the first place.

Answer 3

Delete requires more logic than you think; how much it depends on the structure of the circuit.

In almost all cases, when a record is deleted, the server must check any dependencies on this record as foreign key references. In a nutshell, this is a query of system tables looking for definitions of tables with the foreign key ref for this table, and then selecting each of these tables for records that reference the record being written. Right there, you increased the computation time by a couple of orders, regardless of whether the server cascades the deletion or simply returns an error.

It will also be necessary to reorganize the internal balance structures with internal balancing, and the indices must be updated to remove all branches of the tree branches with the index, but they will have copies in Insert operations.