Thursday, August 28, 2008

Variable's Day Out #16: innodb_log_file_size


Applicable To InnoDB
Server Startup Option --innodb_log_file_size=<value>
Scope Global
Dynamic Yes
Possible Values Integer: Range: 1M - 4G
<1M will be adjusted to 1M
Default Value 5M
Category Performance, Maintenance


This variable defines the size of each log file in a log group. While setting this variable it should be noted that combined size of all log files should be less than 4GB.

InnoDB requires these logs for recovery in case of a crash. So how come the size of these logs effect server performance? As stated in MySQL manual "The larger the value, the less checkpoint flush activity is needed in the buffer pool, saving disk I/O.", these logs help InnoDB in running more confidently as it knows that even if data is not written to the persistent storage often it can still have it.

Best Value:

A larger value helps you in performance but only up to some point. After a certain value, the performance gain will be minimal or it can be negative. Another issue to be considered is that a value too large will slow down recovery as there will be more and more logs to be scanned. But definitely the default is too small.

My usual recommendation is to set it to 256M or if you feel its big (because maybe you have too many crashes and of course crash recoveries) then 128M. Anything beyond this range should be tested properly and justified.

How to set?

If you just change the size of this variable, MySQL will crib about the changed log file size and start without the InnoDB engine. The safe way of re-setting this value is:
  1. Stop the MySQL server
  2. Backup your data and log files
  3. Delete log files
  4. Set the new value for innodb_log_file_size in my.cnf
  5. Start mysql server
Read More:

Tuesday, August 12, 2008

Translation is Fun!!

Morning, I saw Monty's post asking for contribution to drizzle's i18n efforts. I did checked out Hindi language and well I must say translation is a fun activity. 

If you think that will be as easy as using some online translation tool (I tried Google Translate), you may be wrong. Many sentences that make direct sense in English get completely screwed when translated word by word. Sometimes they are translated into a perfect meaningful sentence and that is when you can laugh out loudly.

As of now I'm doing Hindi (already 80 translations down) and next I'm gonna pick Punjabi. Wow! I know languages.