Peter said: Hi Andreas,
I assume you're running out of space ...
Hi Peter,thank you for your answer. Sorry that I respond so late. But the notification e-mail slept through.I'm really interested in investigating this behaviour because currently my only chance is to shutdown the instance completely.A simple scenario is the following:- Table with about 9 Mio rows.- Primary key is a bigint autoincrement.- On the commandline I do awhile true
doecho "delete from sometable where id > <constantnum> order by id limit 1000" | mysql -vvdone- Autocommit is on. When I check whether the rows are deleted say seem to be deleted.- I'm the only user and the above loop is the only connection on the db. So, in my opinion there is no open transaction after 'auto commit' which need these data.
- Systemtablespace has an upper bound.
- So, I'm also sure that system table space gets filled up.
- After starting the instance, having a completely idle database instance I have the following metrics for example:Log sequence number 3103792481241
Log flushed up to 3103792481241
Pages flushed up to 3103792481241
Last checkpoint at 3103792481232
Max checkpoint age 869019772
Checkpoint age target 841862905
Modified age 0- After starting the above shell script, I can see the drift caused by deleteing faster as the purger can purge.- After a while I get this "table full" error on the delete statement. NOW I cancel the shell loop. No further statements are done on the database.- With iostat and 'show enigne innodb status' I can see that the purger is working.
- The gap between the metrics 'Log sequence number', 'Log flushed up to' and 'Pages flushed up to' shrinks.
- As soon as the gap is zero I can't see any relevant writing IO on the filesystem.- At this point, the system should accept further delete statements on the above table. But it does not. I still get 'table full'.
- Shutting down the instance (which is fast because only some few pages are written) and starting it again let me delete rows from the above table.
I don't have PMM installed. If you think it is necessary to investigate the described behaviour, I will take the effort.Best reards
Andreas