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.

MySQL command line pager & mysmartpager

Few days back, Baron re-introduced MySQL's command line pager command and described some cool tricks with maatkit's mk-visual-explain (one of my favorite tools). Soon after reading it, I wished if it was possible to describe regex based (on query) paging. I have written a small hack, christened mysmartpager, that can actually do regex based paging for you. The idea is simple, write a relay that will redirect the output based on to the desired pager. The problem was complex, there was no direct way of getting to know the original query. There are a couple of indirect ways of doing so, but of course with hurdles:

• Run mysql client with --xml option: This will print the output of each command in xml and the command itself is included in the xml. The downside was not many pagers (including mk-visual-explain) understand the xml format and parsing the xml to create a output like string was going to be costly. This will also include writing a lot of code that I don't do for a hack. :)
• Use the tee command: MySQL allows you to tee output to a file that contains everything from standard output including mysql> command... We could have used and easily take the last command using a simple grep. The problem is output is tee'd after pager exits.

Well, that was easy. Now since we know that MySQL will tee after the pager exits we will make the pager exit while still working. And introduce exit if fork; in our code. :) It's working fine for me with small display problems like the time spent is now displayed above the query result. That's fine for me. Files: I don't have an exact way of uploading files into blogger so got it on scribd.

• mysmartpager
• mysmartpager.cnf
• .mysmartpager

Install:

• Copy mysmartpager file to your /path/to/bin directory
• Copy .mysmartpager file to your $HOME directory
• Copy mysmartpager.cnf file to your MySQL's config directory and make sure it is included.
• Make appropriate changes in mysmartpager.cnf file
• More regexs (perl regex's can be written in .mysmartpager)

I have listed three regex's in .mysmartpager file. Would like to hear more ideas flowing.

Reading "High Performance MySQL, 2nd Edition"

I haven't received my copy of the book yet, but being unable to control my temptation I have started reading it over Safari while waiting for my own very personal copy. :)

Already a fan of the first edition, you can feel the same charisma being carried over in this book also. The best part of the book is the simplicity by which you are set sailing over MySQL.

Without doubt, it is one of the best books MySQL can ask for. Certainly, I would recommend this book to anyone who is associated with the word MySQL. Or otherwise if you answer yes to any of these questions below, then go and grab a copy.

• Are you a developer working/struggling with MySQL?
• Are you a DBA working/struggling with MySQL?
• Do you intend to learn MySQL?
• Are you fascinated by databases and open-source?
• Do you work with some other RDBMS and have an open mind?
• Are you an Internet entrepreneur worried about your one fine day?
• ...
• ...
• ...
• Do you love dolphins?

Well, there are some blanks left for you to fill in your own reasons. :)

As for me, I would take this opportunity to thanks Baron, Peter, Vadim, and Arjen for a wonderful book and resort back to reading.