Members Members   Search Search   Help Help   Register Register   Login Login   Home Home
Home » Performance » MySQL » Why is the iblogfile files size big in the beginning?
Why is the iblogfile files size big in the beginning? [message #2218] Mon, 19 November 2007 07:11 Go to next message
newtomysql is currently offline newtomysql
Messages: 30
Registered: February 2007
Member
Dear All,
I recently I install a mysql server with innodb storage engine as my main engine. Then I notice 2 big (320 mb each file) iblogfile immediately created when I installed my server. From what I learn this files are log files ? What logging do they do exactly? I am curious is that why the files size are so big from the start as there is no logging done at that moment right ? I am sure there is empty log in the beginning rite? Can view the contents of the files and how ? Thanks.
Report message to a moderator
Re: Why is the iblogfile files size big in the beginning? [message #2227 is a reply to message #2218 ] Tue, 20 November 2007 11:14 Go to previous messageGo to next message
sterin is currently offline sterin
Messages: 324
Registered: March 2007
Location: Sweden
Senior Member
Those files are for innodb's internal use and are created at first startup of MySQL.
They set to the size that you have set in the configuration file which should be in relation to how large innodb buffer pool that you are using.

All changes to the database are written to these log files before and after they are made to ensure that you don't loose any transactions in case of a power failure.
Report message to a moderator
Re: Why is the iblogfile files size big in the beginning? [message #2228 is a reply to message #2227 ] Tue, 20 November 2007 11:26 Go to previous messageGo to next message
newtomysql is currently offline newtomysql
Messages: 30
Registered: February 2007
Member
Dear Sterin,
I what relation should I set size of the log file in terms of the innodb buffer pool ? I mean in what percentage is the best and appropriate. We cant see the contents of the iblogfiles rite ? So you said it will be keep the changes before and after they are made but will it be there forever or will be cleaned up routinely ? Thanks for your help.
Report message to a moderator
Re: Why is the iblogfile files size big in the beginning? [message #2229 is a reply to message #2218 ] Tue, 20 November 2007 15:32 Go to previous messageGo to next message
sterin is currently offline sterin
Messages: 324
Registered: March 2007
Location: Sweden
Senior Member
Set it to 25-50% of the buffer pool when you have two (default and recommended) innodb log files.

No you never see the content of these files and they are always the same size.
The files are each one half of a cyclic buffer that is continuously written to. So when it reaches the end of one file it starts with the other and when it reaches the end of that file it starts at the beginning of the first file again, etc.

So after these files have been created at startup you never do anything with them and they will neither grow or shrink over time.
Report message to a moderator
Re: Why is the iblogfile files size big in the beginning? [message #2230 is a reply to message #2229 ] Tue, 20 November 2007 20:27 Go to previous messageGo to next message
newtomysql is currently offline newtomysql
Messages: 30
Registered: February 2007
Member
Dear Sterin,
Say my innodb buffer pool size is 1Gb so my iblogfile should be each around 125mb is it ? Actually what I am interested to learn is what is the contents in it? Is it sql statement or some other things ? When you say the contents are the same means what is exactly is the contents even I run different sql statements is it? Thanks.
Report message to a moderator
Re: Why is the iblogfile files size big in the beginning? [message #2231 is a reply to message #2229 ] Tue, 20 November 2007 20:50 Go to previous messageGo to next message
newtomysql is currently offline newtomysql
Messages: 30
Registered: February 2007
Member
Dear Sterin,
Ok below some things I took from my.ini file.

innodb_log_buffer_size=7M
innodb_buffer_pool_size=616M
innodb_log_file_size=308M

By looking at this it seems that my innodb_log_file_size is 50% of my innodb_buffer_pool_size. But the problem here is that I have 2 ib_logfiles where each is 315392Kb which a bit confusing rite. So if you look in this case the total of log file size is nearly equals to my innodb_buffer_pool_size. I have not done any manual change it is as I install only. Please guide me if this sizes are all ok or not ?
Report message to a moderator
Re: Why is the iblogfile files size big in the beginning? [message #2232 is a reply to message #2218 ] Wed, 21 November 2007 02:57 Go to previous messageGo to next message
sterin is currently offline sterin
Messages: 324
Registered: March 2007
Location: Sweden
Senior Member
The sizes are OK, the size isn't really that important. Unless you set it incredibly small for which performance will suffer or incredibly large when recover in case of a power failure will take a very long time.

It's a binary file so hence it doesn't make any sense to look in them. It's purely for InnoDB's internal use. That's what I meant with "you never do anything with them".
Report message to a moderator
Re: Why is the iblogfile files size big in the beginning? [message #2233 is a reply to message #2232 ] Wed, 21 November 2007 03:27 Go to previous messageGo to next message
newtomysql is currently offline newtomysql
Messages: 30
Registered: February 2007
Member
Dear Sterin,
Thanks for your answers. But to be specific so when your said 20-50% of buffer_pool_size does it mean each single file is 20-50% or the total of log file size is 20-50% of my buffer_pool_size ? I am guessing at each of the file is 20-50% of my buffer_pool_size rite? Thanks.
Report message to a moderator
Re: Why is the iblogfile files size big in the beginning? [message #2235 is a reply to message #2233 ] Wed, 21 November 2007 15:53 Go to previous messageGo to next message
sterin is currently offline sterin
Messages: 324
Registered: March 2007
Location: Sweden
Senior Member
newtomysql wrote on Wed, 21 November 2007 09:27

... I am guessing at each of the file is 20-50% of my buffer_pool_size rite? Thanks.

Yes that was what I meant.
But at the same time, as I said, it does not make any noticeable difference if you set them to 12.5-25% either. As long as you don't set them extremely small like maybe < 2% because you will get a lot of unnecessary flush activity of the InnoDB buffer pool.
Report message to a moderator
Re: Why is the iblogfile files size big in the beginning? [message #2237 is a reply to message #2235 ] Wed, 21 November 2007 20:27 Go to previous message
newtomysql is currently offline newtomysql
Messages: 30
Registered: February 2007
Member
Dear Sterin,
Thank you for your time and reply.
Report message to a moderator
Previous Topic:MySQL crashes when InnoDB variables uncommented in my.cnf
Next Topic:questions - transactions and engine
Goto Forum:
  

[ Generate printable PDF ] [ Syndicate this forum (XML) ] [ ]

Current Time: Fri Jul 10 06:49:41 EDT 2009

Total time taken to generate the page: 0.01437 seconds
.:: Contact :: Home ::.

Powered by: FUDforum 2.7.7.
Copyright ©2001-2007 FUD Forum Bulletin Board Software

MySQL is a trademark of Sun Microsystems.
InnoDB is a trademark of Oracle Corp.

Percona Performance Forums are a service of Percona, Inc.
Not affiliated with Sun Microsystems or Oracle Corp.