Simple life, Complicated mind

Sunday, September 23, 2018

Golang, mysql: Error 1040: Too many connections too many open files

Golang, mysql: Error 1040: Too many connections too many open files

  • Opening and closing databases can cause exhaustion of resources.
  • Failing to read all rows or use rows.Close() reserves connections from the pool.
  • Using Query() for a statement that doesn’t return rows will reserve a connection from the pool.
  • Failing to be aware of how prepared statements work can lead to a lot of extra database activity.

Check MySQL connections:


mysql> SHOW STATUS LIKE '%connections%';

mysql> SHOW STATUS LIKE '%threads%';

Check network connection files:

# lsof -i

Set some connection limits in Go code:


Release the resource after used:

rows, err := db.Query("select name from beehives")

if err != nil {
defer rows.Close()


Saturday, September 22, 2018

Add Swap to a Amazon EC2 instance with an EBS (Elastic Block Store) volume

The Amazon EC2 instance does not come with the swap partition by default. You will need to add the swap or paging space manually.

Swap space are useful for systems having less memory (RAM). If your system facing problem of lack of memory continuously and you don’t want to increase memory on server, Then it can be helpful to enable swap in your system. Swap is comparatively much slower than physical memory but operating system uses swap space in case system goes out of memory. To know more about working of swap visit here.

Creating a swap file in current file system:

# dd if=/dev/zero of=/myswap bs=1M count=4096

Note: if - input file.
Note: of - output file.
Note: bs - block size.

# mkswap /myswap
# chown root:root /myswap
# chmod 0600 /myswap

# swapon /myswap

# free -h

              total        used        free      shared  buff/cache   available
Mem:           3.8G        940M        115M        5.6M        2.8G        2.6G
Swap:          4.0G          0B        4.0G

# swapon -s

Filename                                Type            Size    Used    Priority
/myswap                                 file    4194300 0       -1

To make the swap enable on system boot, run the following command:

# sh -c "echo /myswap swap swap defaults 0 0 >> /etc/fstab"

Or edit the /etc/fstab:

# vim /etc/fstab

/myswap   swap   swap   defaults  0 0

To verify the swap:

# cat /etc/fstab | grep -i swap

/myswap swap swap defaults 0 0

# cat /proc/meminfo | grep -i swap

SwapCached:            0 kB
SwapTotal:       4194300 kB
SwapFree:        4194300 kB

To check the current system's swappiness:

Swappiness is a ratio of how often the system will write to the swapfile: if set to zero, the system will only swap to avoid running out of memory (the error above); if set to 100, the system will attempt to swap all the time. The default is set at 60. Since we want to utilize the swap only when necessary.

The Linux kernel provides a tweakable setting that controls how often the swap file is used, called swappiness.

A swappiness setting of zero means that the disk will be avoided unless absolutely necessary (you run out of memory), while a swappiness setting of 100 means that programs will be swapped to disk almost instantly.

Ubuntu system comes with a default of 60, meaning that the swap file will be used fairly often if the memory usage is around half of my RAM.

# cat /proc/sys/vm/swappiness


To configure swappiness:

# sh -c "echo vm.swappiness = 0 >> /etc/sysctl.conf && sysctl -p"

Add the swap space to a second disk instead of the current disk:

If you would like to add the swap space to a second disk, first we need to add extra disk in our system first. In my case new disk mounted as /dev/xvdd (It may change in your case). Then, run the following commands:

# mkswap -f /dev/xvdd
# swapon /dev/xvdd

# vim /etc/fstab

dev/xvdd   swap   swap   defaults  0 0

If at all possible, I'd advise not to use swap on EC2 unless you're 99% certain you won't have to use it (I.E. it's only there for emergency). When we disabled swap on some of our EC2 instances our monthly EBS IO costs probably halved.

You are right, the Ubuntu EC2 EBS images don't come with swap space configured (for 11.04 at least). The "regular" instance-type images do have a swap partition, albeit only 896 MB on the one I tested.

If some process blows up and you don't have swap space, your server could come to a crawling halt for a good while before the OOM killer kicks in, whereas with swap, it merely gets slow. For that reason, I always like to have swap space around, even with enough RAM. Here's your options:

Create an EBS volume (2-4 times the size of your RAM), attach it to your instance (I like calling it /dev/xvdm for "memory"), sudo mkswap /dev/xvdm, add it to fstab, sudo swapon -a, and you're good to go. I have done this before and it works fine, and it is probably a bit faster than using a swap file, but for a server that doesn't normally depend on swap performance, I personally think the minor performance improvement is not worth the added complexity of having to attach a volume. (Update: It's probably not faster than a swap file on instance storage, since EBS has become known for lousy and unpredictable performance.)

Or you might be able to repartition your disk to add a swap partition, though this might require creating a new AMI. I have not been able to do this in a running instance, because I cannot unmount the root file system, and I do not even have access to the disk device (/dev/xvda), only the partition (xvda1).

Or you can create a swap file. This is my preferred solution right now.

# dd if=/dev/zero of=/var/swapfile bs=1M count=2048 &&
# chmod 600 /var/swapfile &&
# mkswap /var/swapfile &&
# echo /var/swapfile none swap defaults 0 0 | tee -a /etc/fstab &&
# swapon -a

Done. :) I know a lot of people feel icky about using files instead of partitions, but it certainly works well enough as emergency swap space.


How to extract deb package

How to extract deb package

$ dpkg -x mysql-cluster-community-server_7.6.7-1ubuntu16.04_amd64.deb ~/tmp/out


$ ar -xv mysql-cluster-community-server_7.6.7-1ubuntu16.04_amd64.deb

Increase MySQL maximum connection limit

Increase MySQL maximum connection limit

2018-09-22T15:00:42.130042Z 0 [Warning] Changed limits: max_open_files: 1024 (requested 5000)
2018-09-22T15:00:42.130087Z 0 [Warning] Changed limits: max_connections: 214 (requested 500)
2018-09-22T15:00:42.130091Z 0 [Warning] Changed limits: table_open_cache: 400 (requested 2000)

Note: Ubuntu has moved from Upstart to Systemd in version 15.04 and no longer respects the limits in /etc/security/limits.conf for system services. These limits now apply only to user sessions.

# mkdir /etc/systemd/system/mysql.service.d
# vim /etc/systemd/system/mysql.service.d/override.conf


# systemctl daemon-reload
# systemctl restart mysql


Sunday, September 16, 2018

To let other different users login to Amazon's EC2 instance

Solution 1:

On the local machine, get public key for later use:

$ test -f ~/.ssh/ && cat ~/.ssh/ || ssh-keygen -t rsa -C "" && cat ~/.ssh/

On the remote EC2 instance, create a new user and add the new user to sudo group:

# useradd USER_NAME -m -s /bin/bash -c 'admin user' && usermod -aG sudo USER_NAME

# visudo


# sudo su - USER_NAME

$ mkdir ~/.ssh \
&& chmod 700 ~/.ssh \
&& touch ~/.ssh/authorized_keys \
&& chmod 600 ~/.ssh/authorized_keys \
&& vim ~/.ssh/authorized_keys

On the local machine:

$ ssh -i ~/.ssh/id_rsa -p 22 USER_NAME@
$ mosh --ssh="ssh -i ~/.ssh/id_rsa -p 22" USER_NAME@

Solution 2:

# vim /etc/ssh/sshd_config

PasswordAuthentication = yes

# systemctl restart sshd.service

Solution 3:

Add a new user:

# useradd testuser -m -c 'test user'

Switch to the new account so that newly created files have the proper ownership:

# sudo su - testuser

$ mkdir ~/.ssh

$ chmod 700 ~/.ssh

Note: this step is very important; without these exact file permissions, you will not be able to log into this account using SSH.

$ touch ~/.ssh/authorized_keys

$ chmod 600 ~/.ssh/authorized_keys

Login to Amazon Web Services console. Then, go to EC2 and create a new key pair: machineName_userName.

It will generate a machineName_userName.pem file for you to download.

Upload machineName_userName.pem to your Linux instance.

Change the permission of the machineName_userName.pem:

# chmod 400 machineName_userName.pem

Retrieving the Public Key for Your Key Pair on Linux:

# ssh-keygen -y

When prompted to enter the file in which the key is, specify the path to your .pem file; for example:


The command returns the public key:

ssh-rsa AAAAB3NzaC1yc2EAAAADAQABAAABAQClKsfkNkuSevGj3eYhCe53pcjqP3maAhDFcvBS7O6V

Edit the authorized_keys file with your favorite text editor and paste the public key for your key pair into the file:

# sudo su - testuser

$ vim ~/.ssh/authorized_keys

ssh-rsa AAAAB3NzaC1yc2EAAAADAQABAAABAQClKsfkNkuSevGj3eYhCe53pcjqP3maAhDFcvBS7O6V

Remove the private key from the server if you do not need it anymore:

# rm /path_to_key_pair/machineName_userName.pem


Saturday, September 15, 2018

Building MySQL from Source Code

Building MySQL from Source Code

# apt-get update && apt-get install build-essential cmake bison -y

# cd /usr/local/src \
&& git clone --depth 1 \
&& mkdir bld \
&& cd bld \
&& cmake ../mysql-server \
-DWITH_BOOST=/usr/local/src/bld \
&& make

Install MySQL 5.7, Apache 2.4, PHP 7.1 on Ubuntu 16.04

Install MySQL 5.7, Apache 2.4, PHP 7.1 on Ubuntu 16.04

Install VMware tools:

VM > Guest > Install/Upgrade VMware Tools

# su -
# df -h
# cd /media/jun/VMware\ Tools/
# ls -la
# tar zxvf VMwareTools-9.4.0-1280544.tar.gz -C /tmp/
# cd /tmp
# ls
# cd vmware-tools-distrib/
# ls
# ./ -d
# reboot

Note: For more info

Move Ubuntu launcher to the bottom:

# gsettings set com.canonical.Unity.Launcher launcher-position Bottom

Update the package repository:

# apt-get update

Upgrades packages with auto-handling of dependencies:

# apt-get dist-upgrade


# apt full-upgrade

Install SSH server:

# apt-get install openssh-server
# systemctl status sshd.service
# systemctl restart sshd.service

Compile and install the latest Git 2.19.0 from source code:

# apt-get install dh-autoreconf libcurl4-gnutls-dev libexpat1-dev gettext libz-dev libssl-dev \
&& apt-get install curl \
&& cd /usr/local/src/ \
&& curl -L -o git.tar.gz \
&& tar zxvf git.tar.gz \
&& cd git-2.19.0/ \
&& make configure \
&& ./configure --prefix=/usr \
&& make all \
&& make install

# git --version

git version 2.19.0

Install Git from ppa:

# add-apt-repository ppa:git-core/ppa
# apt-get update

# apt-cache policy git
# apt-cache madison git

# apt-get install git=1:2.11.0-2~ppa0~ubuntu16.04.1

# git --version

Compile and install the latest Vim 8:

# apt-get install libncurses5-dev python-dev ruby-dev libperl-dev ruby-dev liblua5.3-dev exuberant-ctags cscope

// Fix liblua paths
# ln -s /usr/include/lua5.3 /usr/include/lua \
&& ln -s /usr/lib/x86_64-linux-gnu/ /usr/local/lib/

# cd /usr/local/src \
&& git clone --depth 1 \
&& cd vim \
&& ./configure \
--prefix=/usr --with-features=huge --enable-multibyte --enable-pythoninterp \
--enable-rubyinterp --enable-perlinterp --enable-luainterp --enable-cscope \
&& make \
&& make install

# hash -r
# vim --version | head

Install MTA mail server:

# apt-get install postfix

Note: select "Internet site".

Note: If you need to reconfigure the postfix setting, run either one of the following:

# dpkg-reconfigure -plow postfix


# apt-get purge postfix

For other mail related packages:

# apt-get install mailutils

Install mail client:

# apt-get install bsd-mailx
# echo "test message" | mailx -s 'test subject'

For hexdump command:

# apt-get install bsdmainutils

# hexdump -c test.log

Install MySQL5.7:

# apt-cache policy mysql-server
# apt-cache search mysql-server
# apt-cache show mysql-server | less
# apt show mysql-server

# apt-get install mysql-server

# vim /etc/mysql/mysql.conf.d/mysqld.cnf
bind-address =

# vim ~/.my.cnf
host = localhost
port = 3306
user = root
password = MyPassword

# chmod 400 ~/.my.cnf

# mysql -e "SHOW variables WHERE variable_name REGEXP 'open_files_limit|table_open_cache|max_connections';"
| Variable_name              | Value |
| max_connections            | 151   |
| open_files_limit           | 1024  |
| table_open_cache           | 431   |
| table_open_cache_instances | 16    |

Note: You will see the following error message in the error.log file if you did not change the open files limit:
[Warning] Changed limits: max_open_files: 1024 (requested 5000)
[Warning] Changed limits: table_open_cache: 431 (requested 2000)

# mkdir /etc/systemd/system/mysql.service.d
# vim /etc/systemd/system/mysql.service.d/override.conf



# systemctl daemon-reload
# systemctl restart mysql

# mysql -e "SHOW variables WHERE variable_name REGEXP 'open_files_limit|table_open_cache|max_connections';"
| Variable_name              | Value |
| max_connections            | 151   |
| open_files_limit           | 5000  |
| table_open_cache           | 2000  |
| table_open_cache_instances | 16    |

To check MySQL process's limit:

# cat /proc/$(pgrep mysqld$)/limits
Limit                     Soft Limit           Hard Limit           Units
Max cpu time              unlimited            unlimited            seconds
Max file size             unlimited            unlimited            bytes
Max data size             unlimited            unlimited            bytes
Max stack size            8388608              unlimited            bytes
Max core file size        0                    unlimited            bytes
Max resident set          unlimited            unlimited            bytes
Max processes             15614                15614                processes
Max open files            5000                 5000                 files
Max locked memory         65536                65536                bytes
Max address space         unlimited            unlimited            bytes
Max file locks            unlimited            unlimited            locks
Max pending signals       15614                15614                signals
Max msgqueue size         819200               819200               bytes
Max nice priority         0                    0
Max realtime priority     0                    0
Max realtime timeout      unlimited            unlimited            us




To move a MySQL data directory to another directory:

# mysql -e "SELECT @@datadir;"
| @@datadir       |
| /var/lib/mysql/ |

# systemctl stop mysql
# systemctl status mysql

# vim /etc/mysql/mysql.conf.d/mysqld.cnf
datadir         = /home/mysql

# vim /etc/apparmor.d/tunables/alias
alias /var/lib/mysql/ -> /home/mysql/,

Note: We need to tell AppArmor to let MySQL write to the new directory by creating an alias between the default directory and the new location.

Note: If you skipped the AppArmor configuration step, you would see the following error message:

Job for mysql.service failed because the control process 
exited with error code. See "systemctl status mysql.service" 
and "journalctl -xe" for details.

# systemctl restart apparmor
# systemctl restart mysql

To move the existing to MySQL directory to /home:

# rsync -av /var/lib/mysql /home

Or, you can run the following commands to initialize the MySQL data directory:

# mkdir /home/mysql \
&& chown mysql:mysql /home/mysql \
&& chmod 700 /home/mysql \
&& mysqld --initialize-insecure

Note: This option is used to initialize a MySQL installation by creating the data directory and populating the tables in the mysql system database.

Note: If you use --initialize, the random initial password is stored at: tail -n 1 /var/log/mysql/error.log.

Note: You can also start mysqld with --skip-grant-tables to access the database and change the password.

# systemctl start mysql && systemctl status mysql

Login MySQL with the above commands if you initialized MySQL data directory with --initialize-insecure option.
# mysql -u root --skip-password
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'new_password';

Check the current MySQL data directory:

# mysql -e "SELECT @@datadir;"
| @@datadir    |
| /home/mysql/ |

To change the root password if you did not know the current root password:

# vim /root/tmp/mysql-init.txt
GRANT SUPER ON *.* TO 'root'@'localhost';

# mysqld --init-file=/root/tmp/mysql-init.txt

Use RAM-DISK for tmpdir:

# mysql -e "SHOW GLOBAL STATUS LIKE 'Created_tmp%tables';"
# mysql -e "SHOW GLOBAL VARIABLES LIKE '%table_size';"

# mkdir -p /mnt/ramdisk
# mount -t tmpfs -o size=512M tmpfs /mnt/ramdisk
# chown mysql:mysql /mnt/ramdisk

# id mysql

uid=123(mysql) gid=130(mysql) groups=130(mysql)

# vim /etc/fstab

tmpfs           /mnt/ramdisk     tmpfs   rw,uid=123,gid=130,mode=1770,size=512M    0       0

Note: You need to change the uid and gid of MySQL.

# mysql -e "SHOW GLOBAL VARIABLES LIKE 'tmpdir';"

# vim /etc/apparmor.d/local/usr.sbin.mysqld

/mnt/ramdisk rw,
owner /mnt/ramdisk/** rwkl,

Note: The first line gives read and write access to the directory, the second line gives read, write, lock(k) and link(l) access to all the files and the directories inside the directory owned by the mysql user.

# vim /etc/mysql/mysql.conf.d/mysqld.cnf

tmpdir      = /mnt/ramdisk

# systemctl restart apparmor.service
# systemctl restart mysql.service

Install PHP7.1:

# command -v add-apt-repository >/dev/null 2>&1 \
|| { echo >&2 "add-apt-repository is not installed. I will install it for you"; apt-get install python-software-properties; }

# add-apt-repository -y ppa:ondrej/php
# apt-get update

# apt-cache policy php7.1

# apt-get install php7.1-fpm
# apt-get install php7.1-xml php7.1-curl php7.1-zip php7.1-gd php7.1-bcmath php7.1-intl php7.1-mbstring php7.1-mcrypt php7.1-mysql
# apt-get install php7.1-json php7.1-opcache
# apt-get install php-xdebug

# php -v
PHP (cli) (built: Sep 29 2017 17:04:25) ( NTS )
Copyright (c) 1997-2017 The PHP Group
Zend Engine v3.1.0, Copyright (c) 1998-2017 Zend Technologies
    with Zend OPcache, Copyright (c) 1999-2017, by Zend Technologies
    with Xdebug v2.5.5, Copyright (c) 2002-2017, by Derick Rethans

# vim /etc/php/7.1/fpm/pool.d/www.conf

;listen = /run/php/php7.1-fpm.sock
listen =

Note: You can choose to use either a Unix socket (for local access only) or TCP socket (for the other server on the network to access).

# systemctl restart php7.1-fpm.service && systemctl status php7.1-fpm.service

# ss -an | grep :9000
tcp    LISTEN     0      128                  *:*

# vim /etc/php/7.1/fpm/php.ini

date.timezone = America/Vancouver
display_errors = On
display_startup_errors = On
error_reporting = E_ALL
error_log = /var/log/php_errors.log

List all the installed PHP packages:

# dpkg -l | grep php| awk '{print $2}' |tr "\n" " "

Show the available package version:

# apt-cache search php
# apt-cache policy php

Install the specific package version:

# apt-get install php7=7.0+35ubuntu6

Note: You can look up old versions of packages at their site

Install older version of PHP (PHP5.6):

# add-apt-repository ppa:ondrej/php

# apt-get update

# apt-get install php5.6-fpm

# apt-get install php5.6-gd php5.6-intl php5.6-json php5.6-mbstring php5.6-mcrypt php5.6-mysql php5.6-opcache php5.6-xml

# a2disconf php7.0-fpm.conf
# a2enconf php5.6-fpm.conf

# systemctl restart apache2.service

Install Apache2.4:

# command -v add-apt-repository >/dev/null 2>&1 \
|| { echo >&2 "add-apt-repository is not installed. I will install it for you"; apt-get install python-software-properties; }

# add-apt-repository -y ppa:ondrej/apache2
# apt-get update

# apt-cache policy apache2

# apt-get install apache2

Enable the following modules to talk to PHP:

# cat /etc/apache2/conf-available/php7.1-fpm.conf

# a2enmod proxy proxy_fcgi rewrite setenvif ssl
# a2enconf php7.1-fpm.conf

If your apache is talking to PHP through a TCP socket ( instead of a Unix socket (/run/php/php7.1-fpm.sock), you will need to modify the following line:

# vim /etc/apache2/conf-available/php7.1-fpm.conf
    <FilesMatch ".+\.ph(ar|p|tml)$">
        #SetHandler "proxy:unix:/run/php/php7.1-fpm.sock|fcgi://localhost"
        SetHandler "proxy:fcgi://"

# apache2ctl configtest
# systemctl restart apache2 && systemctl status apache2

Install and enable the following Apache modules if you are connecting to PHP through a TCP socket (

# apt-get install libapache2-mod-fastcgi
# a2enmod fastcgi rewrite setenvif

Edit apache2.conf:

# vim /etc/apache2/apache2.conf

AllowOverride All

Set up a virtual host:

# cd /etc/apache2/sites-available
# cp 000-default.conf mag2.local.conf
# vim mag2.local

Check the configuration:

# apache2ctl -V
# apache2ctl -t
# apache2ctl -M
# apache2ctl configtest

Enable the site:

# a2ensite mag2.local

Start MySQL, PHP, and Apache:

# systemctl restart mysql.service
# systemctl restart php7.0-fpm.service
# systemctl restart apache2.service

# ps auxww | grep -i mysql
# ps auxww | grep -i php-fpm
# ps auxww | grep -i apache2

Install PHPStorm:

# cd ~jun/Downloads/
# tar xf PhpStorm-*.tar.gz -C /opt/
# cd /opt/PhpStorm-163.10504.2/
# ./bin/

Generate a self-signed SSL certificate:

# openssl req -x509 -nodes -days 365 -newkey rsa:2048 -subj "/C=CA/ST=British Columbia/L=Vancouver/O=My Company Name/CN=erp.local" -keyout /etc/ssl/private/test.local.key -out /etc/ssl/certs/test.local.crt

Install node:

$ curl -o- | bash

$ command -v nvm


$ nvm ls-remote
$ nvm install 8.9.3
$ nvm use 8.9.3
$ node -v
$ nvm ls

$ echo '{}' > package.json
$ npm install webpack eslint js-beautify --save-dev