Variable's Day Out #16: innodb_log_file_size

Properties: 

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

Description:

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:

• MySQL manual entry on innodb_log_file_size 
• Choosing proper innodb_log_file_size @mysqlperformanceblog.com 
• Eric Bergen on innodb_log_file_size 
• Increase innodb_log_file_size: the proper way

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.

MySQL camp with Kaj (29th july)

I was there in the meetup and my feeling was a mix about the same. It was nice to have Kaj here (for the first time) and listen to him about Sun's acquisition. On the other hand it was disheartening to see so few people from corporates turning up. It was almost negligible. I'm still positive on this and do expect many more people to turn up. There were a total of three talks in the meet and then we had some chit chat with people. In his first talk, Kaj first greeted everybody in Hindi, Tamil and Kannada and many were delighted. Kaj touched various aspects of Sun's acquisition and also their on-boarding struggle. He also mentioned about MySQL considering Sun's liberal SCA in place of their stricter CLA. (I haven't gone through SCA to actually comment on it's benefits) Second talk by Thava was on how to contribute code to MySQL. It was a nice talk and showed various resource points and steps of doing so in a right manner. Third talk, again by Kaj, was on the different ways of contributing to MySQL and MySQL community other than code. He showed screenshots of forums, forge, planetmysql, few blogs (he forgot mine ;) ), bugs.mysql and how to use them. Interestingly, my name appeared on the planetmysql page :) . Overall it was nice that it started and we need to find out ways for more participation.

Variable's Day Out #15: bulk_insert_buffer_size

Properties:

Applicable To	MyISAM
Server Startup Option	--bulk_insert_buffer_size=<value>
Scope	Both
Dynamic	Yes
Possible Values	Integer: Range: 0 - 4294967295 (4G)
Default Value	8388608 (8M)
Category	Performance

Description:

This cache is used by MyISAM to optimize bulk inserts. This cache is a special tree-like structure. Bulk inserts include statements like LOAD DATA INFILE..., INSERT ... SELECT, INSERT ... VALUES (...), (...), ..., (...)

Bulk inserts are often used to minimize disk writes etc and are very common in applications inserting lots of data. I often use bulk inserting using the following technique: (pseudocode)

• CREATE TEMPORARY TABLE tmp_table LIKE main_table
• LOOP:
  • INSERT ROWS INTO TEMPORARY TABLE
• INSERT INTO main_table SELECT * FROM tmp_table

If you are using any technique for bulk insertion, you should be carefully setting this parameter.

Optimal Value:

This depends on the way your bulk inserts are structured. If you are doing bulk inserts with data being close to or more than 10k, this comes really handy.

Read more:

• MySQL manual entry on bulk_insert_buffer_size
• LOAD DATA INFILE - performance case study (Venu Anuganti)

Hope you enjoyed reading this.