May 2016 – Tikejhya: Ashish Nepal

Why is innodb inserts slower than MyISAM

By admin

On 16th May 2016

In Mysql

Why is innodb inserts slower than MyISAM
Innodb VS MyISAM inserts and bulk loads

mysql> use testdb;

Database changed

mysql> set profiling=1; INSERT INTO `myisam` (`myisam`) VALUES (2); show profile;
Query OK, 0 rows affected (0.00 sec)

Query OK, 1 row affected (0.00 sec)

+———————-+———-+
| Status | Duration |
+———————-+———-+
| starting | 0.000012 |
| checking permissions | 0.000002 |
| Opening tables | 0.000004 |
| System lock | 0.000002 |
| Table lock | 0.000001 |
| init | 0.000004 |
| update | 0.000028 |
| end | 0.000002 |
| query end | 0.000001 |
| freeing items | 0.000011 |
| logging slow query | 0.000001 |
| cleaning up | 0.000001 |
+———————-+———-+
12 rows in set (0.00 sec)

mysql> set profiling=1; INSERT INTO `innodb` (`innodb`) VALUES (2); show profile;
Query OK, 0 rows affected (0.00 sec)

Query OK, 1 row affected (0.03 sec)

+———————-+———-+
| Status | Duration |
+———————-+———-+
| starting | 0.000019 |
| checking permissions | 0.000003 |
| Opening tables | 0.000019 |
| System lock | 0.000003 |
| Table lock | 0.000002 |
| init | 0.000008 |
| update | 0.000031 |
| end | 0.000001 |
| query end | 0.000002 |
| freeing items | 0.026714 |
| logging slow query | 0.000003 |
| cleaning up | 0.000001 |
+———————-+———-+
12 rows in set (0.00 sec)

# Lets disable ACID compatibility feature on Innodb OFF
mysql> set global innodb_flush_log_at_trx_commit = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> set profiling=1; INSERT INTO `myisam` (`myisam`) VALUES (2); show profile;
Query OK, 0 rows affected (0.00 sec)

Query OK, 1 row affected (0.00 sec)

+———————-+———-+
| Status | Duration |
+———————-+———-+
| starting | 0.000019 |
| checking permissions | 0.000003 |
| Opening tables | 0.000023 |
| System lock | 0.000003 |
| Table lock | 0.000002 |
| init | 0.000009 |
| update | 0.000027 |
| end | 0.000001 |
| query end | 0.000001 |
| freeing items | 0.000013 |
| logging slow query | 0.000001 |
| cleaning up | 0.000001 |
+———————-+———-+
12 rows in set (0.00 sec)

mysql> set profiling=1; INSERT INTO `innodb` (`innodb`) VALUES (2); show profile;
Query OK, 0 rows affected (0.00 sec)

Query OK, 1 row affected (0.00 sec)

+———————-+———-+
| Status | Duration |
+———————-+———-+
| starting | 0.000019 |
| checking permissions | 0.000004 |
| Opening tables | 0.000008 |
| System lock | 0.000002 |
| Table lock | 0.000002 |
| init | 0.000009 |
| update | 0.000032 |
| end | 0.000002 |
| query end | 0.000001 |
| freeing items | 0.000017 |
| logging slow query | 0.000001 |
| cleaning up | 0.000001 |
+———————-+———-+
12 rows in set (0.00 sec)

# Lets turn that back again to leave system as it is:
mysql> set global innodb_flush_log_at_trx_commit = 1;
Query OK, 0 rows affected (0.00 sec)

So, We could make certain insert’s, updates or inload, bulk load faster in innodb upto x4 times doing this little tweak.

In certain condition it would be even better doing this as permanent solution while compared to MyISAM since this innodb would give other good features
such as row based lock, RDBMS features such as fk’s.

+15

0

pstool psexec remote command

By admin

On 15th May 2016

In Windows

PSTOOL PsExec:

#open command prompt of remote machine
PsExec.exe \\local-PCCCC cmd

# Uninstall antivirus program
wmic product where name=”ESET Remote Administrator Agent” call uninstall

Or
#Run uninstallation remotely
PsExec.exe \\local-PCCCC cmd ‘wmic product where name=”ESET Remote Administrator Agent” call uninstall’

Find who is logged in:
query user /server:server-a

or

query user /server:localhost

+39

0

sftp download using username password

By admin

On 15th May 2016

In Linux

curl -o test.csv -u ‘username’:’password’ sftp://sftpdownloadlocation.com/test.csv

And load file into mysql
mysql -utest -ppass -hlocalhost –database dbname -ss -e ‘LOAD DATA LOCAL INFILE “test.csv” INTO TABLE some_table FIELDS TERMINATED BY “,” OPTIONALLY ENCLOSED BY “””” LINES TERMINATED BY “\n”;’

+21

0

Bash: occurence count

By admin

On 15th May 2016

In BASH

This is simple bash onliner to count number of same digit match:

I needed to count 1,10,100,1000 occurrence in a file

for num in {1..10}; do echo “$num Digit”; cat /tmp/tt-28 | grep -E -w [0-9]{$num}

cat /tmp/tt-28
1
2
223
4
5
5

so in the given example you find, output as 5 1 Digit and 1 3 digit rest will be shown as 0.

[root@76 ~]# for num in {1..10}; do echo “$num Digit”; cat /tmp/tt-28 | grep -E -w [0-9]{$num} | wc -l ; done
1 Digit
5
2 Digit
0
3 Digit
1

0

tail with color

By admin

On 15th May 2016

In SED

multitail -c /path/to/log

multitail configuration are located at ~/.multitailrc

colorscheme:logname
cs_re:green:TEST
cs_re:red:ERROR

multitail -cS logname /path/to/log

AWK Method:
tail -f /path/to/log | awk ‘/TEST/ {print “\033[32m” $0 “\033[39m”} /ERROR/ {print “\033[31m” $0 “\033[39m”}’

SED Method:
tail -f /path/to/log | sed -e ‘s/$.*TEST.*$/\o033[32m\1\o033[39m/’ -e ‘s/$.*ERROR.*$/\o033[31m\1\o033[39m/’

0

kibana readonly

By admin

On 15th May 2016

In ELK

This isnt great way of doing it but without using shield or new v5 x-packs, this is best way of making kibana readonly.

curl -XPUT ‘localhost:9200/.kibana/_settings’ -d ‘{ “index.blocks.read_only” : true }’

toggle to false to make it read/write

curl -XPUT ‘localhost:9200/.kibana/_settings’ -d ‘{ “index.blocks.read_only” : false }’

0

YAML break a string over multiple lines

By admin

On 15th May 2016

In puppet

#where each line break is replaced by a space
>
This is a very long sentence
that spans several lines in the YAML
but which will be rendered as a string
without carriage returns.

#to indicate that the string will span several lines
|
This is a very long sentence
that spans several lines in the YAML
but which will be rendered as a string
without carriage returns.

0

puppet agent example

By admin

On 15th May 2016

In puppet

# puppet agent debug
puppet agent -t –debug

# puppet agent dry run
puppet agent –test –noop

# check certificate to be signed
puppet cert –list

#check all certificate
puppet cert –list –all

# Removing certificates
puppet cert –clean {node certname}

# Remove the entire SSL directory of the client machine
rm -r /etc/puppet/ssl; rm -r /var/lib/puppet/ssl

#register client with puppetserver
#Client
puppet agent --server puppetmaster.ashishnepal.net --waitforcert 60 --test

#Master
puppet cert --sign web1.tikejhya.net

#Config example

[main] logdir = /var/log/puppet rundir = /var/run/puppet ssldir = $vardir/ssl


[agent]

    classfile = $vardir/classes.txt

    localconfig = $vardir/localconfig

    server=puppetmaster.ashishnepal.net

    environment = development

    runinterval = 5y

[master] ssl_client_header = SSL_CLIENT_S_DN ssl_client_verify_header = SSL_CLIENT_VERIFY

# There can be always fun with puppet agent and its hostname: (give it a go and see)

# hostname
foobar
# uname -n
foobar
# hostname -f
foobar.example.com
# hostname -a
foobar localhost.localdomain localhost

0

EC2 Adding new volume

By admin

On 15th May 2016

In amazon, aws

fdisk -l

[ec2-user ~]$ lsblk
NAME MAJ:MIN RM SIZE RO TYPE MOUNTPOINT
xvdf 202:80 0 100G 0 disk
xvda1 202:1 0 8G 0 disk /

[ec2-user ~]$ file -s /dev/xvdf
/dev/xvdf: data

[ec2-user ~]$ mkfs -t ext4 device_name

[ec2-user ~]$ mkdir /some_folder
[ec2-user ~]$ mount /dev/xvdf /some_folder

FSTAB entry example:
/dev/xvdf /some_folder ext4 defaults,nofail 0 2

[ec2-user ~]$ mount -a

0

zabbix time based triggers

By admin

On 15th May 2016

In Zabbix

Zabbix time based triggers:

Flapping proof and time based triggers on zabbix

Server {HOSTNAME} is unreachable ({ashishnepal.com:agent.ping.nodata(480)}=1|({TRIGGER.VALUE}=1)&({ashishnepal.com:agent.ping.count(480)}<4))&({ashishnepal.com:agent.ping.time(0)}>073000&{ashishnepal.com:agent.ping.time(0)}<180000) In above example: trigger would only fire if: trigger value is not in trigger & ping responded with no data for 4 Minutes, ping count was less than 4 for same period of time and if time is between 7:30 to 18:00 hour. e.g 2: If server was restarted but not between certain time: {HOSTNAME} has just been restarted: {ashishnepal.com:system.uptime.last(0)}<600&({ashishnepal.com:system.uptime.time(0)}<180000&{ashishnepal.com:system.uptime.time(0)}>073000)

0

Tikejhya: Ashish Nepal

Knowledgebase

Month: May 2016

Why is innodb inserts slower than MyISAM

pstool psexec remote command

sftp download using username password

Bash: occurence count

tail with color

kibana readonly

YAML break a string over multiple lines

puppet agent example

EC2 Adding new volume

zabbix time based triggers

Recent Posts

Categories

Archives