Tuesday, September 15, 2015

Sysctl Linux server for performance enhancement.

Here are my  tips and suggestions, that I apply to a server for performance enhancement.
I find these values to be safe to run any and everywhere.

sysctl -w net.core.rmem_max=16777216
sysctl -w net.core.wmem_max=16777216
sysctl -w net.core.rmem_default=16777216
sysctl -w net.core.wmem_default=16777216
sysctl -w net.core.optmem_max=40960
sysctl -w net.ipv4.tcp_rmem='4096 87380 16777216'
sysctl -w net.ipv4.tcp_wmem='4096 65536 16777216'
sysctl -w net.ipv4.tcp_window_scaling=1
sysctl -w net.ipv4.tcp_fin_timeout=30
sysctl -w net.ipv4.tcp_keepalive_time=1800
sysctl -w net.ipv4.ip_local_port_range='1024 65000'

For those that use puppet, for your hiera configs:

 sysctl:
  net.core.rmem_max:
    value: "16777216"
  net.core.wmem_max:
    value: "16777216"
  net.core.rmem_default:
    value: "16777216"
  net.core.wmem_default:
    value: "16777216"
  net.core.optmem_max:
    value: "40960"
  net.ipv4.tcp_rmem:
    value: "4096 87380 16777216"
  net.ipv4.tcp_wmem:
    value: "4096 87380 16777216"
  net.ipv4.tcp_window_scaling:
    value: '1'
  net.ipv4.tcp_fin_timeout:
    value: '30'
  net.ipv4.tcp_keepalive_time:
    value: '1800'
 net.ipv4.ip_local_port_range:
     value: '1024 65000'

HTH
Brent

Wednesday, September 9, 2015

Issues importing with myloader on galera cluster.


I had a very interesting situation and problem whereby,  I was loading data into a Galera cluster using `myloader` (http://www.mydumper.org/).

Our 3 node cluster was correctly replicate writes when we insert
rows from the console. When I loaded our SQL dump, the table
definitions (all InnoDB) got created on all the nodes.

However, the rows from the import only showed up on the single node, I was loading onto, and did not make their way to the other nodes.

Whats was interesting, nothing was showing up in the error logs for the other nodes during the import. I even manually inserted a row into one of the new tables after the big import is done and only that single row showed up on the other nodes.

I eventually found the option ' -enable-binlog', via the man page (default is off). This specifically means that events will not be replicated.

Long story short.

On a galera cluster you need to add the option (--enable-binlog)

time myloader --database=$DATABASE --directory=/RESTORE/PATH --queries-per-transaction=50000 --threads=6  --verbose=3 -o --enable-binlog

HTH
Brent