CODEWITHSUNDEEP
mysql
kadamb
kadamb

Reputation: 1748

Too many unknown writes in MySQL

I have a MySQL database in my production environment.Which had about 430 million row, of which 190 million rows were not of any use, so I started deleting these rows range by range, in night, as it would have affected my apps performance in daytime.

Now when I am seeing in my monitoring app, I am seeing 100%IO, of which maximum is write (12-30MB/s). (400-500 writes/sec) But when I am checking process list I don't find any INSERT or UPDATE query or any rollback.

What can be the possible issue or how can I find any hidden query which may be writing in MySQL.

(In IOTP, I found that write operations are being done by mysqld only) enter image description here

One more thing, I can see write with 80MB/s in IOTOP , but when I am checking directory size in / , I don't see any rise in any directory size.

Upvotes: 0

Views: 265

Answers (2)

Michael - sqlbot
Michael - sqlbot

Reputation: 179442

Back away slowly... and wait.

InnoDB doesn't change the actual data in the tablespace files with each DML query.

It writes the changes to memory, of course, and then the redo log, at which point they are effectively "live" and safely persisted to disk... but they are not yet applied to the actual data (tablespace) files. InnoDB then syncs the changes to the data files in the background but in the mean time, other queries use a combination of the tablespace and log contents to determine what the "official" table data currently contains. This is, of course, an oversimplification, but MVCC necessarily means the physical data is a superset, though not necessarily a proper superset, of the logical data.

That's very likely to be the explanation for what you are seeing now.

It makes sense that free/used disk space isn't changing, because finalizing the deletion of those rows will only really be marking the space inside the tablespace files as unused. They shouldn't grow or shrink.

Resist the temptation to try to "fix" it and whatever you do, don't restart the server... because the best possible outcome is that it will pick up where it left off because it still has work to do.

SHOW ENGINE INNODB STATUS takes some practice to interpret but will likely be another key to the puzzle.

Upvotes: 1

obe
obe

Reputation: 7806

Is the delete operation still undergoing? DELETE can be extremely slow and generate a lot of writes. It is often better to create a new identical table and copy the rows you want to KEEP over to it and then switch it with the production table instead of delete stuff in the production table directly.

If the DELETE has already finished and you suspect that there are other queries running, you can enable query log for a few seconds and see which queries are executed:

TRUNCATE TABLE mysql.general_log;
SET GLOBAL log_output = 'TABLE';
SET GLOBAL general_log = 'ON';
SELECT SLEEP(10);
SET GLOBAL general_log = 'OFF';

Then SELECT from mysql.general_log to see which queries executed during the 10 seconds sleep.

Upvotes: 1

Related Questions