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 PROCESSLIST;

mysql> SHOW STATUS LIKE '%connections%';

mysql> SHOW STATUS LIKE '%threads%';

Check network connection files:

# lsof -i

Set some connection limits in Go code:

db.SetMaxOpenConns(1000)
db.SetMaxIdleConns(300)
db.SetConnMaxLifetime(0)

Release the resource after used:

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

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

Reference:

https://stackoverflow.com/questions/28135580/golang-mysql-error-1040-too-many-connections

http://go-database-sql.org/surprises.html

http://go-database-sql.org/prepared.html

https://access.redhat.com/solutions/1160343

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

30

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.

Reference:

http://tecadmin.net/add-swap-partition-on-ec2-linux-instance/
http://stackoverflow.com/questions/17173972/how-do-you-add-swap-to-an-ec2-instance
http://askubuntu.com/questions/103915/how-do-i-configure-swappiness
http://serverfault.com/questions/218750/why-dont-ec2-ubuntu-images-have-swap
http://danielgriff.in/2014/add-swap-space-to-ec2-to-increase-performance-and-mitigate-failure/

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

or

$ 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

[Service]
LimitNOFILE=infinity
LimitMEMLOCK=infinity

# systemctl daemon-reload
# systemctl restart mysql

Reference:

https://stackoverflow.com/questions/30901041/can-not-increase-max-open-files-for-mysql-max-connections-in-ubuntu-15

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/id_rsa.pub && cat ~/.ssh/id_rsa.pub || ssh-keygen -t rsa -C "me@example.com" && cat ~/.ssh/id_rsa.pub

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 ALL=(ALL:ALL) NOPASSWD: ALL
or
USER_NAME ALL=(ALL) NOPASSWD: ALL

# 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@1.2.3.4
or
$ mosh --ssh="ssh -i ~/.ssh/id_rsa -p 22" USER_NAME@1.2.3.4

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:

/path_to_key_pair/machineName_userName.pem

The command returns the public key:

ssh-rsa AAAAB3NzaC1yc2EAAAADAQABAAABAQClKsfkNkuSevGj3eYhCe53pcjqP3maAhDFcvBS7O6V
hz2ItxCih+PnDSUaw+WNQn/mZphTk/a/gU8jEzoOWbkM4yxyb/wB96xbiFveSFJuOp/d6RJhJOI0iBXr
lsLnBItntckiJ7FbtxJMXLvvwJryDUilBMTjYtwB+QhYXUMOzce5Pjz5/i8SeJtjnV3iAoG/cQk+0FzZ
qaeJAAHco+CY/5WrUBkrHmFJr6HcXkvJdWPkYQS3xqC0+FmUZofz221CBt5IMucxXPkX4rWi+z7wB3Rb
BQoQzd8v7yeb7OzlPnWOyN0qFU0XA246RA8QFYiCNYwI3f05p6KLxEXAMPLE

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
hz2ItxCih+PnDSUaw+WNQn/mZphTk/a/gU8jEzoOWbkM4yxyb/wB96xbiFveSFJuOp/d6RJhJOI0iBXr
lsLnBItntckiJ7FbtxJMXLvvwJryDUilBMTjYtwB+QhYXUMOzce5Pjz5/i8SeJtjnV3iAoG/cQk+0FzZ
qaeJAAHco+CY/5WrUBkrHmFJr6HcXkvJdWPkYQS3xqC0+FmUZofz221CBt5IMucxXPkX4rWi+z7wB3Rb
BQoQzd8v7yeb7OzlPnWOyN0qFU0XA246RA8QFYiCNYwI3f05p6KLxEXAMPLE

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

# rm /path_to_key_pair/machineName_userName.pem

Reference:

http://docs.aws.amazon.com/AWSEC2/latest/UserGuide/managing-users.html
http://docs.aws.amazon.com/AWSEC2/latest/UserGuide/ec2-key-pairs.html#how-to-generate-your-own-key-and-import-it-to-aws

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 https://github.com/mysql/mysql-server.git --depth 1 \
&& mkdir bld \
&& cd bld \
&& cmake ../mysql-server \
-DDOWNLOAD_BOOST=1 \
-DWITH_BOOST=/usr/local/src/bld \
&& make

Persistent key-value database written in Go

Persistent key-value database written in Go

badger - https://github.com/dgraph-io/badger/

Note: https://blog.dgraph.io/post/badger/

bbolt - https://github.com/etcd-io/bbolt

MySQL backup and replication tools

MySQL backup and replication tools

mydumper

https://github.com/maxbube/mydumper

Percona XtraBackup

https://www.percona.com/software/mysql-database/percona-xtrabackup

Percona MySQL-AutoXtraBackup

https://github.com/Percona-Lab/MySQL-AutoXtraBackup

https://www.percona.com/blog/2017/11/27/perconalab-autoxtrabackup-v1-5-0-release/

mysqlbinlog

# mysqlbinlog --result-file=test.log /var/log/mysql/mysql-bin.000001

Note: You may edit the result file and delete any statements you don't want to execut. Then, do mysql -u root -p < test.log

# mysqlbinlog --result-file=test.log --base64-output=DECODE-ROWS /var/log/mysql/mysql-bin.000001
# mysqlbinlog --result-file=test.log -v /var/log/mysql/mysql-bin.000001
# mysqlbinlog --result-file=test.log -vv /var/log/mysql/mysql-bin.000001

Note: Do more research on global transaction identifier (GTID) vs binary log position

Get binary log from the remote server:

# mysqlbinlog --read-from-remote-server --host=192.168.101.2 -p mysqld-bin.000001

Note: https://www.percona.com/blog/2012/01/18/backing-up-binary-log-files-with-mysqlbinlog/

The output of mysqlbinlog can be used as the input of the mysql client to redo the statements contained in the binary log:

# cd /var/log/mysql
# mysqlbinlog mysql-bin.000001 | mysql -u root -p

canal

https://github.com/alibaba/canal

Pingcap syncer:

https://github.com/pingcap/docs/blob/master/tools/syncer.md

To Embed Static Assets in Go

To Embed Static Assets in Go

Requirements:

Compression
Optional decompression
Loading from the local file system
Reproducible builds
Config file
http.FileSystem Interface

vfsgen - https://github.com/shurcooL/vfsgen
fileb0x - https://github.com/UnnoTed/fileb0x
go-assets - https://github.com/jessevdk/go-assets

Reference:

https://tech.townsourced.com/post/embedding-static-files-in-go/

Saturday, September 8, 2018

Convert string to hexadecimal on command line

Convert string to hexadecimal on command line

$ echo -n "Hello" | od -A n -t x1

48 65 6c 6c 6f

Thursday, September 6, 2018

How to avoid Go gotchas

a gotcha is a valid construct in a system, program or programming language that works as documented but is counter-intuitive and almost invites mistakes because it is both easy to invoke and unexpected or unreasonable in its outcome (source: wikipedia)

How to avoid Go gotchas
https://divan.github.io/posts/avoid_gotchas/

Understand Go pointers in less than 800 words or your money back
https://dave.cheney.net/2017/04/26/understand-go-pointers-in-less-than-800-words-or-your-money-back

There is no pass-by-reference in Go
https://dave.cheney.net/2017/04/29/there-is-no-pass-by-reference-in-go

Pointers in Go
https://dave.cheney.net/2014/03/17/pointers-in-go

Should methods be declared on T or *T
https://dave.cheney.net/2016/03/19/should-methods-be-declared-on-t-or-t

HOW EVERYTHING IN GO IS PASSED BY VALUE ?
https://techgita.com/2018/08/25/how-everything-in-go-is-passed-by-value/

50 Shades of Go: Traps, Gotchas, and Common Mistakes for New Golang Devs
http://devs.cloudimmunity.com/gotchas-and-common-mistakes-in-go-golang/

Go traps
https://go-traps.appspot.com/

Go Data Structures
http://research.swtch.com/godata

Go Data Structures: Interfaces
http://research.swtch.com/interfaces

Go Slices: usage and internals
https://blog.golang.org/go-slices-usage-and-internals

Gopher Puzzlers
http://talks.godoc.org/github.com/davecheney/presentations/gopher-puzzlers.slide

Don't use Go's default HTTP client (in production)
https://medium.com/@nate510/don-t-use-go-s-default-http-client-4804cb19f779

The complete guide to Go net/http timeouts
https://blog.cloudflare.com/the-complete-guide-to-golang-net-http-timeouts/

How to organize the go struct, in order to save memory
https://medium.com/@felipedutratine/how-to-organize-the-go-struct-in-order-to-save-memory-c78afcf59ec2

Wheresoever you Go, Go with all your heart

That's the whole reason why Confucius said "Wheresoever you Go, Go with all your heart"

Save the matched values into variables in golang regular expression

Save the matched values into variables in golang regular expression

package main

import (
 "fmt"
 "regexp"
)

func mapSubexpNames(m, n []string) map[string]string {
 m, n = m[1:], n[1:]
 r := make(map[string]string, len(m))
 for i, _ := range n {
  r[n[i]] = m[i]
 }
 return r
}

func main() {
 r := regexp.MustCompile(`(?P<Year>\d{4})-(?P<Month>\d{2})-(?P<Day>\d{2})`)
 m := r.FindStringSubmatch(`2015-05-27`)
 n := r.SubexpNames()
 fmt.Println(mapSubexpNames(m, n))
}

Reference:

https://stackoverflow.com/questions/30483652/how-to-get-capturing-group-functionality-in-golang-regular-expressions