| Bug #14206 | leaking of InnoDB table space | ||
|---|---|---|---|
| Submitted: | 21 Oct 2005 10:13 | Modified: | 9 Dec 2005 10:50 |
| Reporter: | Pavel Francirek | Email Updates: | |
| Status: | No Feedback | Impact on me: | |
| Category: | MySQL Server: InnoDB storage engine | Severity: | S2 (Serious) |
| Version: | 4.0.24 | OS: | Linux (Linux Debian 3.0) |
| Assigned to: | Assigned Account | CPU Architecture: | Any |
[21 Oct 2005 10:13]
Pavel Francirek
[21 Oct 2005 14:23]
Heikki Tuuri
Hi! Please post SHOW INNODB STATUS a few days after the mysqld restart. Maybe you have an active transaction dangling there for days, and purge cannot run? Regards, Heikki Oracle/Innobase
[21 Oct 2005 16:32]
MySQL Verification Team
Hi! Thank you for writting to us. The behaviour that you describe is expected for 4.0. If you, however , use 4.1 with file-per-table option, then OPTIMIZE will reclaim space.
[25 Oct 2005 14:11]
Pavel Francirek
Hi Heikki, everything runs in autocommit mode. So there are no long-lasting transactions. Pavel
[25 Oct 2005 14:15]
Pavel Francirek
Hi Sinisa, no, as I wrote, I use 4.0.24 version. Or what do you mean by "is expected for 4.0", you already know about this behavior? I tried to find such bug report before submitting mine but I found none.
[25 Oct 2005 19:56]
Heikki Tuuri
Hi! There are no dangling transactions. But the workload that you have on the database is extremely high. You have 8 queries inside InnoDB and 15 queries in the queue. We could say that InnoDB is overloaded. Purge is lagging by some 36,000 transactions. My guess is that the growth in ibdata1 is caused by purge lagging behind, and it is lagging because the workload is too high. With 4.1.xx you could try to tune the innodb_max_purge_lag startup option. Instead of shutting down mysqld, you could test letting it lay idle until purge has run to completion. Does the space get freed that way? Regards, Heikki Trx id counter 20 2093769547 Purge done for trx's n:o < 20 2093733190 undo n:o < 0 0 ... -------------- ROW OPERATIONS -------------- 8 queries inside InnoDB, 15 queries in queue Main thread process no. 12174, id 28680, state: sleeping Number of rows inserted 89017641, updated 555125890, deleted 43052841, read 2389036463 192.64 inserts/s, 2352.59 updates/s, 0.00 deletes/s, 95997.26 reads/s
[27 Oct 2005 13:18]
Pavel Francirek
Queries per second
Attachment: innodb-queries.png (image/png, text), 6.68 KiB.
[27 Oct 2005 13:30]
Pavel Francirek
Hello Heikki, problems started at about 18:00 when there is not peek load (as you can see on graph I just uploaded). And the space was not recoveded (actually I was still decreasing) even after midnight where there is much less traffic. But thank you for tip, I try to redesing application to do less queries. There were no problem with cpu or i/o load but maybe there is some hot spot. BTW, if I tried to switch to MySQL 4.1 I met quite higher load with the same config. Graph is on my blog http://blog.lide.cz/franci/2005/09/15/79 (sorry, only in Czech, but I think that graph is clear). Some optimalization may help but there is high update/select ratio so for example query cache will not be so effective. Regards, Pavel
[27 Oct 2005 14:50]
Heikki Tuuri
Pavel, what did SHOW INNODB STATUS say at midnight? Regards, Heikki
[31 Oct 2005 10:55]
Pavel Francirek
Sorry, I have no INNODB STATUS at midnight of the day when there was table space leak. But "midnight" was just example. What I wanted to say was that there is less than 60% of queries on peek load and space wasn't still released. Space was still draining util I restarted server (it was at about 1 a.m. when load is really much smaller). Or do I really stop _all_ traffic to database to reclaim space?
[31 Oct 2005 19:23]
Heikki Tuuri
Pavel, please post several SHOW INNODB STATUSes from the quiet period. Regards, Heikki
[9 Nov 2005 10:09]
Pavel Francirek
Hello, I noticed that status of bug report isn't changed if I only upload file. So it already happend :) Pavel
[9 Nov 2005 10:13]
Pavel Francirek
Sorry, wrong extension :-/ Last one (innodb_status_idle.txt) is right. Pavel
[9 Nov 2005 10:50]
Heikki Tuuri
Pavel, also the 'quiet' period that you posted is rather busy: about 1000 inserts and updates per second! And purge is lagging behind by about 20,000 transactions: Trx id counter 21 885397048 Purge done for trx's n:o < 21 885375367 undo n:o < 0 0 You should test innodb_max_purge_lag in 4.1.xx. Regards, Heikki
[10 Dec 2005 0:00]
Bugs System
No feedback was provided for this bug for over a month, so it is being suspended automatically. If you are able to provide the information that was originally requested, please do so and change the status of the bug back to "Open".
