Tikejhya: Ashish Nepal

Knowledgebase

Category: Mysql (Page 1 of 5)

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.

mysql ssl replication certificate

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.

How to generate certificates for mysql replication with ssl.

Lets assume, database server are: db1 [master], db2 [slave] / master of db3, db3[slave]

openssl genrsa 2048 > ca-key.pem
openssl req -new -x509 -nodes -days 3600 -key ca-key.pem -out ca.pem
openssl req -newkey rsa:2048 -days 3600 -nodes -keyout db1-key.pem -out db1-req.pem
openssl rsa -in db1-key.pem -out db1-key.pem
openssl x509 -req -in db1-req.pem -days 3600 -CA ca.pem -CAkey ca-key.pem -set_serial 01 -out db1-cert.pem

openssl req -newkey rsa:2048 -days 3600 -nodes -keyout db2-key.pem -out db2-req.pem
openssl rsa -in db2-key.pem -out db2-key.pem
openssl x509 -req -in db2-req.pem -days 3600 -CA ca.pem -CAkey ca-key.pem -set_serial 01 -out db2-cert.pem

Test
openssl verify -CAfile ca.pem db1-cert.pem db2-cert.pem

openssl req -newkey rsa:2048 -days 3600 -nodes -keyout db3-key.pem -out db3-req.pem
openssl rsa -in db3-key.pem -out db3-key.pem
openssl x509 -req -in db3-req.pem -days 3600 -CA ca.pem -CAkey ca-key.pem -set_serial 01 -out db3-cert.pem

Test
openssl verify -CAfile ca.pem db1-cert.pem db2-cert.pem db3-cert.pem

Full setup process can be found:

MySql Replication with SSL

Mysql changing year from date

DATETIME:

UPDATE products
SET date_purchased=DATE_FORMAT(date_purchased,’2014-%m-%d’) where date_purchased like ‘2104-08%’;

DATE:
UPDATE products
SET date_purchased=DATE_FORMAT(date_purchased,’2014-%m-%d %T’) where date_purchased like ‘2104-08%’;

MySQL UPDATE with random number between x & y

UPDATE tableName SET columnName = FLOOR(x+ RAND()*y);

here you might say, update between 17 - 30.

restore a single table from a full mysql mysqldump file

restore a single table from a full mysql mysqldump file

Extract tables with Create, This is safer option
sed -n -e '/CREATE TABLE.*users/,/UNLOCK TABLES/p' all_spark.sql > users1.sql

Extract table with DROP Table
sed -n -e '/DROP TABLE.*users/,/UNLOCK TABLES/p' fulldump.sql > users.sql

# identify the first and last line numbers (n1 and n2) of desired table
grep -n "Table Structure" mydump.sql
# (e.g. sed -n 48,112p)
sed -n n1,n2p mydump.sql > mytable.sql

## This script will only get you first found table, it will not work if you have diff table with same name.

#!/bin/bash
### This script is from jasny / mysql_splitdump.sh found in githut

if [ $# -lt 1 ] ; then
echo "USAGE $0 DUMP_FILE [TABLE]"
exit
fi

if [ $# -ge 2 ] ; then
csplit -s -ftable $1 "/-- Table structure for table/" "%-- Table structure for table `$2`%" "/-- Table structure for table/" "%40103 SET TIME_ZONE=@OLD_TIME_ZONE%1"
else
csplit -s -ftable $1 "/-- Table structure for table/" {*}
fi

[ $? -eq 0 ] || exit

mv table00 head

FILE=`ls -1 table* | tail -n 1`
if [ $# -ge 2 ] ; then
mv $FILE foot
else
csplit -b '%d' -s -f$FILE $FILE "/40103 SET TIME_ZONE=@OLD_TIME_ZONE/" {*}
mv ${FILE}1 foot
fi

for FILE in `ls -1 table*`; do
NAME=`head -n1 $FILE | cut -d$'x60' -f2`
cat head $FILE foot > "$NAME.sql"
done

rm head foot table*

Fatal error: Can’t open and lock privilege tables: Table ‘mysql.host’ doesn’t exist

Fatal error: Can’t open and lock privilege tables: Table ‘mysql.host’ doesn’t exist

Solution:

mysql_install_db

mysql 5.x timezone not found.

mysql 5.5 timezone not found.

mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root mysql

ERROR 1146 (42S02): Table ‘mysql.servers’ doesn’t exist

mysql> flush privileges;
ERROR 1146 (42S02): Table ‘mysql.servers’ doesn’t exist

Solution:

CREATE TABLE `servers` (
`Server_name` char(64) NOT NULL,
`Host` char(64) NOT NULL,
`Db` char(64) NOT NULL,
`Username` char(64) NOT NULL,
`Password` char(64) NOT NULL,
`Port` int(4) DEFAULT NULL,
`Socket` char(64) DEFAULT NULL,
`Wrapper` char(64) NOT NULL,
`Owner` char(64) NOT NULL,
PRIMARY KEY (`Server_name`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8
COMMENT='MySQL Foreign Servers table';

mysqlbinlog

mysqlbinlog –start-datetime=”2013-05-07 15:00:00″ –stop-datetime=”2013-05-07 16:20:01″ db1.002428

Reading mysqlbinlog from remote server:
mysqlbinlog -t mysql-bin-changelog.000020 –read-from-remote-server -hxxxxxxxxxxxxxx.com -uroot –password=xxxxxxxxxxxx –raw –result-file=/tmp/binlog-test

mysql slave act as master

enable log-slave updates on my.cnf

log-slave-updates

Page 1 of 5

Powered by WordPress & Theme by Anders Norén