mysql myISAM vs Innodb storage engines

By OK -

InnoDB and MYISAM, are storage engines for MySQL.
These two differ on their locking implementation: InnoDB locks the particular row in the table, and MyISAM locks the entire MySQL table.
You can specify the type by giving MYISAM OR InnoDB while creating a table in DB.
The major deficiency of MyISAM is the absence of transactions support. Versions of MySQL 5.5 and greater have switched to the InnoDB engine to ensure referential integrity constraints, and higher concurrency.

MyISAMInnodb
Not ACID-compliant and non-transactionalACID-compliant and hence fully transactional with ROLLBACK and COMMIT and support for Foreign Keys
MySQL 5.0 default engineRackspace Cloud default engine
Offers compressionOffers compression
Requires full repair and rebuild of indexes and tablesProvides automatic recovery from crashes via the replay of logs
Changed database pages written to disk instantlyDirty pages converted from random to sequential before commit and flush to disk
No ordering in storage of dataRow data stored in pages in PK order
Table-level lockingRow-level locking

When MyISAM tables are mostly useful

There can be several other reasons that fit your requirement for choosing the MyISAM engine. For example, reads can be faster on MyISAM, despite the general claims in the MySQL documentation, when a MyISAM table has a fixed (not dynamic) row size, for example, when it uses more CHARs then VARCHARs. Another reason why you might choose MyISAM over Innodb is because Innodb must perform additional checks because of ACID compliancy. For example, a FK check must be performed, which could potentially cause an operational overhead.

Comments

Post a Comment

Popular posts from this blog

HAproxy logging

By OK -
ubuntu@ubuntu:/etc/haproxy$ cat /etc/rsyslog.d/haproxy.conf # Create an additional socket in haproxy's chroot in order to allow logging via # /dev/log to chroot'ed HAProxy processes $ModLoad imudp $UDPServerRun 514 local0.* -/var/log/haproxy-0.log local1.* -/var/log/haproxy-1.log ### keep logs in localhost ## $AddUnixListenSocket /var/lib/haproxy/dev/log ------------------- ubuntu@ubuntu:/etc/haproxy$ cat /etc/haproxy/haproxy.cfg global chroot /var/lib/haproxy user haproxy group haproxy log 127.0.0.1   local0         log 127.0.0.1   local1 notice daemon frontend www     bind 192.168.40.128:8282    # haproxy public IP     default_backend as-backend    # backend used backend as-backend    balance leastconn    mode http  server as1 192.168.40.128:8082 check    # application srv 1 defaults log ...
Read more

teamcity Automatic Agent Start under Linux

By OK -
Automatic Agent Start under Linux To run agent automatically on the machine boot under Linux, configure daemon process with the  agent.sh start  command to start it and  agent.sh stop  command to stop it. Refer to an example procedure below: 1. Navigate to the services start/stop services scripts directory: cd /etc/init.d/ 2. Open the build agent service script: sudo vim buildAgent 3. Paste the following into the file : #!/bin/sh ### BEGIN INIT INFO # Provides: TeamCity Build Agent # Required-Start: $remote_fs $syslog # Required-Stop: $remote_fs $syslog # Default-Start: 2 3 4 5 # Default-Stop: 0 1 6 # Short-Description: Start build agent daemon at boot time # Description: Enable service provided by daemon. ### END INIT INFO #Provide the correct user name: USER="agentuser" case "$1" in start) su - $USER -c "cd BuildAgent/bin ; ./agent.sh start" ;; stop) su - $USER -c "cd BuildAgent/bin ; ./agent....
Read more

NFS mount add in fstab _netdev instead of default | firewall-cmd --list-all

By OK -
Redhat recommends us to add  _netdev  option for NFS file system in /etc/fstab. So that they can be mounted after starting the networking service. Alternative ways to refer to partitions: Label : LABEL=label Network ID Samba : //server/share NFS : server:/share SSHFS : sshfs#user@server:/share Device : /dev/sdxy (not recommended) Example #1 //192.168.1.134/share_name /mnt/share_name cifs username=server_user,password=server_password,_netdev 0 0 ^ Path to your share ^ Mountpoint ^ Username ^ Password ^ See note A Note A: The  _netdev  options makes sure the mount is only attempted AFTER a network connection has been established. Example #2 10.10.10.1:/vol1/fs1 /data nfs defaults,_netdev 0 0 ============================ [root@localhost ~]# firewall-cmd --list-all public   target: default   icmp-block-inversion: no   interfaces:   sources:   services: dhcpv6-client high-availabi...
Read more