(25) playing with bash autocompletion

Updated: as Blaxter notes, it’s necessary to open a new terminal after editing the bashrc file for the changes to take effect. Also, I’ve replaced the tail -n+2 with the -ss option. Thanks Baron Schwartz!

One of the things I’ve been doing lately is doing some cleanup on my laptop.

There are two things I’m constantly using on the shell:

  • Open a project folder
  • Connect to a MySQL Database

Bash Functions

I first made a bash function to help me with the projects paths:

function cdp {
case $1 in
  "foo")
    cd /Users/golo/devel/svn/foo/src ;;
  "bar")
    cd /Users/golo/devel/svn/bar/src ;;
  *)
    echo "Options:"
    echo ""
    echo "  foo"
    echo "  bar"
esac
}

I placed that on my /etc/bashrc , so I write:

$ cdp foo

And it takes me to the foo project folder.

But, as I have lot of open projects, I have to remember «foo» and «bar», or worse… ¡¡Write «foo» or «bar»!!

Bash Autocompletion

My Lazyness made me learn how to create bash autocompletions. This case is easy as executing «cdp» without parameters you get a list with all possible values. It’s easy to parse ;).

The autocompletor should be placed on the file /etc/bash_completion.d/cdp with this content:

_cdp_show()
{
        local cur opts

        cur="${COMP_WORDS[COMP_CWORD]}"
        opts=$(cdp | tail -n+2)
        COMPREPLY=( $(compgen -W "${opts}" ${cur}) )
}
complete -F _cdp_show cdp

opts stores all possible options (line per option), and cur stores the text to be autocompleted (not sure). compgen is a helper to create the autocompletion options, and complete is the command that does the autocompletion reading the COMPREPLY variable.

Now I get the following behavior:

$ cdp <tab><tab>
foo    bar
$ cdp f<tab>
$ cdp foo

Oh! Amazin!

MySQL Bash Autocompletion

¿What can be more awesome than that? Let me guess… ¡Autocompletion for mysql command!

The goal is to get autocompletion for the mysql databases. No more writing…

$ mysql information_schema

For that, we need a list of all databases on a parseable format, let’s try with SHOW DATABASES:

$ mysql -e "SHOW DATABASES"
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
$ # Let's put this on a better format:
$ mysql -B -e "SHOW DATABASES"
Database
information_schema
mysql
performance_schema
$ # That's better, let's delete the table header:
$ mysql -B -ss -e "SHOW DATABASES"
information_schema
mysql
performance_schema
$ # We are ready! :D

Disclaimer: I tried mysqlshow but doesn’t accepts the Batch format, also I have the user and password on my /etc/my.cnf so this trick is not suitable for production environments.

Let’s create an autocompletor mixing this command with what we already know. The result is the file /etc/bash_completion.d/mysql:

_mysql_show()
{
        local cur opts

        cur="${COMP_WORDS[COMP_CWORD]}"
        opts=$(mysql -B -ss -e "SHOW DATABASES;" )
        COMPREPLY=( $(compgen -W "${opts}" ${cur}) )
}
complete -F _mysql_show mysql

Now you can use <tab>:

$ mysql i<tab>
$ mysql information_schema

Hope it helps! Improvements are welcomed 😉

Importing times in MySQL

One of the ways to import data into MySQL is using the LOAD DATA INFILE. It is a faster method than recovering from a dump, as it’s raw data instead of SQL sentences.

The import time depends on the table engine, for example, MyISAM can be 40 times faster than Innodb. Let’s benchmark this:

Preparation

I’m gonna make some benchmarking using MySQL 5.1.36 (64 bits MacOS X). I’ll need a big table, so I’ll take City from the World Database and create a huge table called «city_huge»:

CREATE TABLE city_huge LIKE CITY;

INSERT INTO city_huge 
    SELECT NULL, name, CountryCode, District, Population FROM city;
# Run this sentence 100 times,
# so city_huge table will be 100 times bigger than city.
# Tip: use a script, temporary table, stored procedure...
# or tell your monkey to do so.

SELECT COUNT(*) FROM city_huge;
#   +----------+
#   | COUNT(*) |
#   +----------+
#   |   407900 | 
#   +----------+

# Make a table data backup:
SELECT * FROM city_huge INTO OUTFILE 'city_huge.bak';

# Truncate table, so we'll start with an empty table.
TRUNCATE TABLE city_huge;

Direct Import

Let’s import the backup into the city_huge table, using MyISAM, InnoDB and MEMORY:

LOAD DATA INFILE 'city_huge.bak' INTO TABLE city_huge;
#   Query OK, ... (5.85 sec)
# So, that was using MyISAM.

# Let's empty the table and change the engine to InnoDB:
TRUNCATE TABLE city_huge;
ALTER TABLE city_huge ENGINE = InnoDB;
LOAD DATA INFILE 'city_huge.bak' INTO TABLE city_huge;
#   Query OK, ... (3 min 59.53 sec)

# With Memory:
TRUNCATE TABLE city_huge;
SET @@max_heap_size= 128 * 1024 * 1024;
ALTER TABLE city_huge ENGINE = MEMORY;
LOAD DATA INFILE 'city_huge.bak' INTO TABLE city_huge;
#   Query OK, ... (2.18 sec)
MyISAM 0:5.85
InnoDB 3:59.53
MEMORY 0:2.18

Wow, MyISAM is almost 40 times faster. And MEMORY is even faster.

Alter Table

Ok, InnoDB is a bit slow, but sometimes you can’t use another storage engine. In those cases, you could import in the other engine and then change the table engine to InnoDB.

That would look like this:

TRUNCATE TABLE city_huge;
ALTER TABLE city_huge ENGINE = MyISAM;
LOAD DATA INFILE 'city_huge.bak' INTO TABLE city_huge;
#   Query OK, ... (5.85 sec)
ALTER TABLE city_huge ENGINE = InnoDB;
#   Query OK, ... (4 min 11.24 sec)

Ooops, 4 min 17 sec is more than 3 min 59 sec.

Let’s try Memory:

TRUNCATE TABLE city_huge;
SET @@max_heap_size= 128 * 1024 * 1024;
ALTER TABLE city_huge ENGINE = MEMORY;
LOAD DATA INFILE 'city_huge.bak' INTO TABLE city_huge;
#   Query OK, ... (2.18 sec)
ALTER TABLE city_huge ENGINE = InnoDB;
# Query OK, ... (3 min 28.39 sec)

Yes, 3 min 31 sec is faster than 4 min. 30 seconds are around 10% faster.

Disclaimer

This benchmark is done using the default configuration, I’m sure that tuning InnoDB will improve the results. Also, I’m not the most accurate benchmarker, so I encourage you to do your own benchmarks.

This solution is not a silver bullet, MEMORY engine needs lots of memory. And this is NOT always the best approach, InnoDB should be fast by it’s own.

Anyway, its funny to discover those not so obvious behaviours :D.

Read Spanish Comments

First Warp Talks

Warp Talks

2009 Starts quite interesantly.

This Monday took place the first Warp Talks, a project of training between employees at Warp Networks. The last Monday of the month will take place a new Warp Talk.

koke and me where the first speakers.

I made an introduction to subversion, and he did a talk about 10 things you might not know about MySQL.

Koke took a camera and recorded our talks at the same time they were being broadcasted at justin.tv. Videos are available at vimeo (spanish):

http://www.vimeo.com/tag:warptalks