Monthly Archives: January 2016

Fixing “Write failed: Broken pipe” on Yosemite

After latest update to my macbook pro I noticed that my SSH connections started dropping if I kept them idle for few minutes. Each time the session terminated with the text “Write failed: Broken pipe”. I have observed that the connection used to hang for a long while before this error message was shown.

This was not the behavior before so I suspected that recent updates might have changed some configuration and hence I started looking around. I found an article which explained how to configure my Macbook.

Based on the inputs provided in the article I edited the file /etc/ssh_config using the following command:
sudo vi /etc/ssh_config

And changed/uncommented the following lines:
Host *
ServerAliveInterval 60
TCPKeepAlive yes

I learnt another point in this article that during the SSH session if I press “~” followed by “.” then the connection terminates immediately. If it doesn’t then pressing enter before doing this helps.

MySQL Slowdown With Large Inserts

On a plain vanilla windows system with approximately 6 GB of RAM and a XAMPP based MySQL installation I found out that as the number of inserts increased the MySQL inserts became slower and slower. Ultimately it came down to one insert in 2 seconds!

This was totally unacceptable so I looked around for some solution to this problem and I found one here.

Based on the article above I started looking into the MySQL configuration and I found the following entries:

#innodb_log_arch_dir = "D:/xampp/mysql/data"
## You can set .._buffer_pool_size up to 50 - 80 %
## of RAM but beware of setting memory usage too high
innodb_buffer_pool_size = 16M
innodb_additional_mem_pool_size = 2M
## Set .._log_file_size to 25 % of buffer pool size
innodb_log_file_size = 5M
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 50

So my database is using innodb file system but the buffer pool size is only 16 MB with additional increase of 2 MB. This in my opinion is too less keeping in mind the comments provided in the file.

## You can set .._buffer_pool_size up to 50 - 80 %
## of RAM but beware of setting memory usage too high

So in a 6 GB RAM system I could easily set 5 GB as the buffer pool size but it is a development system and I wanted the developer to have decent development performance as well. So I tweaked the configuration a little bit and now it looks like this:

#innodb_log_arch_dir = "D:/xampp/mysql/data"
## You can set .._buffer_pool_size up to 50 - 80 %
## of RAM but beware of setting memory usage too high
innodb_buffer_pool_size = 1000M
innodb_additional_mem_pool_size = 250M
## Set .._log_file_size to 25 % of buffer pool size
innodb_log_file_size = 50M
innodb_log_buffer_size = 80M
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 50

At the moment the innodb_buffer_pool_size is 1000 MB with an additional innodb_additional_mem_pool_size of 250 MB.

I also increased the innodb_log_file_size to 50 MB and innodb_log_buffer_size to 80 MB (I simply multiplied the default value with 10).

I have started the inserts again into the same table using the same program with no other changes and I see a marked improvement in performance. The insert operation has not yet slowed down. I am able to insert 10-14 records within one second.

Update after 3 hours =====
I observed that after inserting close to 110000 records the inserts slowed down to 4-5 inserts per second. I have read an article which summarizes how InnoDB actually works. I think I will tweaking the configuration a little bit more.