Tikejhya: Ashish Nepal

Knowledgebase

Month: May 2016

Why is innodb inserts slower than MyISAM

Your ads will be inserted here by

Easy Plugin for AdSense.

Please go to the plugin admin page to
Paste your ad code OR
Suppress this ad slot.

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)

Your ads will be inserted here by

Easy Plugin for AdSense.

Please go to the plugin admin page to
Paste your ad code OR
Suppress this ad slot.

# 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.

pstool psexec remote command

Your ads will be inserted here by

Easy Plugin for AdSense.

Please go to the plugin admin page to
Paste your ad code OR
Suppress this ad slot.

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

sftp download using username password

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”;’

Bash: occurence count

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

tail with color

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/’

kibana readonly

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 }’

YAML break a string over multiple lines

#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.

puppet agent example

# 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

EC2 Adding new volume

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

zabbix time based triggers

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)

Powered by WordPress & Theme by Anders Norén