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.
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.
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.
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.
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.
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 ?
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”.
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.