Why Redirecting MySQL Imports Beats Piping by Up to 3× – A SystemTap Performance Study

The author investigates which of two typical ways to load a huge .sql dump into MySQL is faster: piping the file through cat (e.g., cat huge_dump.sql | mysql -uroot) or using input redirection (e.g., mysql -uroot < huge_dump.sql).

Experimental Setup

A small C program b.out is compiled to simulate MySQL’s consumption of data by continuously reading from stdin:

int main(int argc, char *argv[])
{
    while (fread(buf, sizeof(buf), 1, stdin) > 0);
    return 0;
}

The program is built with: gcc -o b.out b.c A systemtap script ( test.stp) is prepared to log the sequence of system calls for the processes involved:

function should_log(){
    return (execname() == "cat" || execname() == "b.out" || execname() == "bash");
}
probe syscall.open, syscall.close, syscall.read, syscall.write, syscall.pipe, syscall.fork, syscall.execve, syscall.dup, syscall.wait4 {
    if (!should_log()) next;
    printf("%s -> %s
", thread_indent(0), probefunc());
}
probe kernel.function("pipe_read"), kernel.function("pipe_readv"), kernel.function("pipe_write"), kernel.function("pipe_writev") {
    if (!should_log()) next;
    printf("%s -> %s: file ino %d
", thread_indent(0), probefunc(), __file_ino($filp));
}
probe begin { println(":~") }

A 419 MiB file is generated to act as the dump:

sudo dd if=/dev/urandom of=huge_dump.sql bs=4096 count=102400

Benchmark Results

Using the time command, the two import methods produce the following timings (real/user/sys):

# Pipe method
$ time (cat huge_dump.sql | ./b.out)
real    0m0.596s
user    0m0.001s
sys     0m0.919s

# Redirection method
$ time (./b.out < huge_dump.sql)
real    0m0.151s
user    0m0.000s
sys     0m0.147s

The redirection approach is roughly three times faster.

SystemTap Trace Analysis – Pipe

bash forks two processes.

One child execve’s cat, the other execve’s b.out; they communicate via a pipe. cat reads the file, writes to the pipe, and b.out reads from the pipe, resulting in two reads of the data and an extra context switch.

SystemTap Trace Analysis – Redirection

bash forks a single process, opens the dump file, and dup’s the file descriptor to stdin.

The process execve’s b.out, which reads the data directly from the file descriptor.

Only one read operation occurs, and no pipe is involved, eliminating the extra syscalls and context switch.

Conclusion

On Linux, redirecting a large file into MySQL ( mysql < huge_dump.sql) is significantly more efficient than piping the file through cat, because the pipe version incurs double reads and an additional process switch. The measured speedup is about threefold for a 419 MiB file.