RE: Reducing ibdata1 file size
Despite the conventional wisdom, converting to innodb_file_per_table will not necessarily help you. It depends on your situation. If most of your growth is in a single table, you will only have transferred the problem from the ibdata1 file to a new file. The ibdata1 file may also continue to grow, since innodb uses it for several kinds of temporary storage such as the insert buffer and the undo logs (AKA "rollback segment"). Kay Rozeboom Information Technology Enterprise Iowa Department of Administrative Services Telephone: 515.281.6139 Fax: 515.281.6137 Email: kay.rozeb...@iowa.gov -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Commit commands with SELECT
We have an application with blocks of code that begin with setting autocommit off, and end with a commit. The code in between does only selects, no updating. 1) Am I correct in thinking that the autocommit and commit statements don't really accomplish anything useful? 2) If the autocommit and commit statements are unneeded, do they add enough additional overhead that I should be concerned about them? Kay Rozeboom Information Technology Enterprise Iowa Department of Administrative Services Telephone: 515.281.6139 Fax: 515.281.6137 Email: kay.rozeb...@iowa.gov
RE: Reusing "ibdata1" space
Johan, I think you are right about this. The problem does not appear to be with the database at all, but with the undo log. Thanks for pointing me in the right direction. -Original Message- From: Johan De Meersman [mailto:vegiv...@tuxera.be] Sent: Tuesday, November 01, 2011 10:01 AM To: Rozeboom, Kay [DAS] Cc: mysql@lists.mysql.com Subject: Re: Reusing "ibdata1" space - Original Message - > From: "Kay Rozeboom [DAS]" > > I realize that this would not return the unused space to the operating > system. But would it return it to MySQL so that it could be re-used > for subsequent inserts, instead of extending "ibdata1" > further? That should normally already happen. An occasional optimize table might help defragment the tablespace, but space from properly deleted records should be reused anyway (save for really small fragments). I recommend you keep track of the innodb free tablespace for a while, and see how that evolves - it should go relatively low before the tablespace expands on disk. -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel
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]" > > 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
"InnoDB free" - What does it really mean?
In the "show table status" output, there is comment field labeled "InnoDB free". Can someone explain what kind of free space is counted in this figure? Is it space that is not currently part of any segment? Does it include empty pages within segments? Does it include unused space within pages? Kay Rozeboom Information Technology Enterprise Iowa Department of Administrative Services Telephone: 515.281.6139 Fax: 515.281.6137 Email: kay.rozeb...@iowa.gov
Reusing "ibdata1" space
Thanks to everyone who replied to my question. Kay Rozeboom Information Technology Enterprise Iowa Department of Administrative Services Telephone: 515.281.6139 Fax: 515.281.6137 Email: kay.rozeb...@iowa.gov -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Reusing "ibdata1" space
We are running MySQL 5.0.77, and using INNODB in production for the first time. The production database has a lot of inserts and deletes, and the shared "ibdata1" file is continually growing. I understand that to return the unused space to the operating system, we must delete and recreate "ibdata1" and its associated ".frm" files. I am wondering if we could do the following instead: 1) Let "ibdata1" grow for a while. 2) Rebuild the tables periodically using this syntax: ALTER TABLE t1 ENGINE = InnoDB; I realize that this would not return the unused space to the operating system. But would it return it to MySQL so that it could be re-used for subsequent inserts, instead of extending "ibdata1" further? Kay Rozeboom Information Technology Enterprise Iowa Department of Administrative Services Telephone: 515.281.6139 Fax: 515.281.6137 Email: kay.rozeb...@iowa.gov
RE: mysql listed as "attach page" by google?
Does anyone know if this has been fixed yet? -Original Message- From: Jigal van Hemert [mailto:ji...@xs4all.nl] Sent: Monday, September 26, 2011 2:02 PM To: mysql@lists.mysql.com Subject: Re: mysql listed as "attach page" by google? Hi, On 26-9-2011 20:30, Michael Albert wrote: > I don't suppose I am the first to notice this, but most of > the pages on dev.mysql.com have been listed by google > as "attack pages", e.g http://dev.mysql.com/downloads/. > Has there been a problem, or is google being overzealous? I fear Google is right. http://www.net-security.org/malware_news.php?id=1853 -- Kind regards / met vriendelijke groet, Jigal van Hemert. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=kay.rozeb...@iowa.gov -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: very large import
Hi, I am new to MySQL and this listserv. I have a couple of questions about the recommendations at the link given below. 1) If autocommit is turned off during the large load, could this cause the underlying InnoDB log to grow too big? 2) If the unique checks and foreign key checks are turned off during the large load, are the checks run for the newly loaded data when they are turned back on? If not, what kind of errors would occur later if the loaded data contained duplicate keys or non-existent foreign keys? Kay Rozeboom Information Technology Enterprise Iowa Department of Administrative Services Telephone: 515.281.6139 Fax: 515.281.6137 Email: kay.rozeb...@iowa.gov -Original Message- From: a.sm...@ukgrid.net [mailto:a.sm...@ukgrid.net] Sent: Wednesday, August 03, 2011 3:25 PM To: supr_star Cc: mysql@lists.mysql.com Subject: Re: very large import Quoting supr_star : > Is there any way to speed up this process? by disabling indexes or > something? I can't afford to be down for 3 more days... First stop, the mysql documentation: http://dev.mysql.com/doc/refman/5.1/en/innodb-tuning.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
