Re: ibdata1 and undo log
Using a trend graphing tool like Munin or Cacti. You should have that in place anyway, as it allows you to both predict future growth and analyze the circumstances of past issues. You can't have too much information on your systems. - Original Message - From: Kay Rozeboom [DAS] kay.rozeb...@iowa.gov To: mysql@lists.mysql.com Sent: Friday, 18 November, 2011 5:06:44 PM Subject: RE: ibdata1 and undo log Thanks for the response, Johan. It would really help if I could determine when the ballooning is occurring. Do you know of any way to do that? -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: ibdata1 and undo log
Thanks for the response, Johan. It would really help if I could determine when the ballooning is occurring. Do you know of any way to do that? -Original Message- From: Johan De Meersman [mailto:vegiv...@tuxera.be] Sent: Thursday, November 17, 2011 1:28 AM To: Rozeboom, Kay [DAS] Cc: mysql@lists.mysql.com Subject: Re: ibdata1 and undo log - Original Message - From: Kay Rozeboom [DAS] kay.rozeb...@iowa.gov 1) Can anyone verify that the additional (presently unused) space was allocated for the undo log? 2) Are the many 1-page segments a leftover from a large undo log? I'm not too hot on the InnoDB internals, but yes, the undo log is one possibility for the ballooning of your tablespace. If you have huge transactions (or huge amounts of concurrent ones), that's the likely culprit. Cutting down transaction size (if possible) will help. Another possibility is automated maintenance jobs from your application. I found that Cacti, for instance, has the rather annoying tendency to optimize it's tables every night, which is a bit of a bugger if you have a few multi-gigabyte tables. You might benefit from innodb-file-per-table; that way your actual tablespace gets separated from the metadata and undo logs, giving you a much clearer view of what exactly is ballooning. Obviously, as discussed many times before, that's going to require a full export/import to be useful, though. -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel
ibdata1 and undo log
We are running MySQL 5.0.77, and implemented our first production InnoDB database a few months ago. The database is on a dedicated server and is composed of 14 tables. No tables have been added or dropped since creation. The ibdata1 file is growing much faster than the database itself. The ibdata1 file is currently 3.8G. The database takes up 533M. 3.2G of the file is composed of free extents. This matches the InnoDB free value. Here is my understanding of what is stored in the ibdata1 file: 1) User data 2) Tablespace header 3) Data dictionary 4) Double-write buffer 5) Insert buffer 6) Rollback segment, AKA undo log. I believe that the double-write buffer is always 2M. The insert buffer has been up 6.1M. By a process of elimination, I think that the undo log (which can expand and contract) must account for the additional space, most of which is currently not in use. One other mysterious clue: The InnoBD tablespace monitor shows 145 segments of 1 page each. I believe that a handful of these are used for system stuff like the data dictionary because I see them in every ibdata1 file. But I do not normally see so many. Two questions: 1) Can anyone verify that the additional (presently unused) space was allocated for the undo log? 2) Are the many 1-page segments a leftover from a large undo log? Please note that I am NOT asking how to reclaim the space, which has already been discussed many times. I don't think there is any point in doing that until I understand what is happening here. Kay Rozeboom Information Technology Enterprise Iowa Department of Administrative Services Telephone: 515.281.6139 Fax: 515.281.6137 Email: kay.rozeb...@iowa.gov
Re: ibdata1 and undo log
- Original Message - From: Kay Rozeboom [DAS] kay.rozeb...@iowa.gov 1) Can anyone verify that the additional (presently unused) space was allocated for the undo log? 2) Are the many 1-page segments a leftover from a large undo log? I'm not too hot on the InnoDB internals, but yes, the undo log is one possibility for the ballooning of your tablespace. If you have huge transactions (or huge amounts of concurrent ones), that's the likely culprit. Cutting down transaction size (if possible) will help. Another possibility is automated maintenance jobs from your application. I found that Cacti, for instance, has the rather annoying tendency to optimize it's tables every night, which is a bit of a bugger if you have a few multi-gigabyte tables. You might benefit from innodb-file-per-table; that way your actual tablespace gets separated from the metadata and undo logs, giving you a much clearer view of what exactly is ballooning. Obviously, as discussed many times before, that's going to require a full export/import to be useful, though. -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
